Cloud – measure twice, cut once

KQL and ADX – Is this really like SQL?

Hopefully, you’re aware that Azure Data Explorer can be accessed by anybody with a valid Microsoft Account Identity or MSA for short. If you need a one, then hop along to Outlook.com and pick up a free MSA.

After that head along to dataexplorer.azure.com and login with your MSA. Then, you’ll find that Azure Data Explorer is attached to a cluster that you did not create. This will be the ‘help’ cluster that Microsoft uses in order to get folks up-to-speed on Azure Data Explorer and KQL.

Your screen (browser) should look something like this.

You’ll notice that you’re attached to the ‘help’ cluster. Which has 4 databases

ContosoSales
SampleLogs
SampleMetrics
Samples

And that an initial script is in the main window.

Notice too that right at the top of the main window (the one that contains the script) you can see the name of the ADX cluster and database that you are attached to.

ADX cluster and database name

The script that’s open in the main window is designed to run on the “@help/Samples” cluster / database.

Make sure you’re attached to that and highlight the first query (if you’re not on the correct database then you’ll just get an error)

This is Kusto Query Language or KQL

This is basically the equivilent of a T-SQL query of the form

SELECT TOP(10) * FROM from table

You’ll also notice that the next query in the script looks something like this

A KQL query

And if you were to translate this into T-SQL, you’re basically asking for a computed / derived column and specifying a list of columns that you’d like back.

If you’ve worked with T-SQL for sometime, you’ll probably see some similarities but a lot of differences.

ADX and KQL have been built from the ground up with Data Analytics in mind.

Like most things Microsoft, it’s been developed internally and consequently seen great internal adoption. Hence, it’s being made available as a product to the rest of the world.

KQL is used heavily in Log Analytics, where we can join different tables of immense size in order to interrogate performance issues with our Azure systems – and get the answers really quickly, even when we have terabytes (or even petabytes) of data involved.

This is the type of interrogation that people might have used products such as Splunk for previously.

It’s worthwhile playing with this ‘help’ cluster and getting to know the KQL language, it’s looking like the adoption rate could be huge – and it’s always good to know your options, even if you find out that this is not for you.

Also, have a look at the connection string. Press the ‘Edit’ button in the bar that states the name of the ‘help’ cluster

ADX cluster properties (connection string and display name)

And we see the query string, and have the opportunity to change the display name of the cluster – if we like.

If you wish to query the cluster inside Azure Data Studio using the KQL extension then that’s possible too – see this post for details.

While KQL might be a little different to T-SQL, most database professionals will easily ‘acclimatise’ to the new language and be up and running quite quickly.

Watch this blog for more future tips.

Hope that helps somebody get up and running with ADX and KQL

Have a great day

Cheers

Marty.

ADX and KQL – Free Cluster for learning purposes

G’day,

I’ve been looking around a bit lately at Azure Data Explorer (ADX) Clusters and the Kusto Query Language (KQL).

I recently learned that Microsoft is letting people experience the wonders of ADX for free.

I assumed – wrongly as it turned out – that you would need an Azure subscription in order to do this, but in fact you do not.

In order to test this, I grabbed myself a brand new free email address from outlook.com

I then opened a brand new InPrivate window in the Edge browser and made 100% sure that none of my other Microsoft accounts were being picked up.

The next step was to navigate to http://aka.ms/kustofree in the InPrivate window

I was immediately asked to log in with my new outlook.com account, which I duly did. After that I was transported to https://dataexplorer.azure.com/freecluster

Note that this new email address has to be an MSA, although you do not have to sign up for any Azure account – free or otherwise.

After that, there’s a big button in the middle of the screen that will allow you to create a brand new free cluster – and database as it turns out.

Fill in a few details in the subsequent dialog box

Use your own cluster and database names above. Accept the terms and conditions and enjoy.

As you might expect, you get all of the core features – but you’ll get some more if you upgrade at some stage. The table on this page will outline exactly what you are getting. The free cluster lasts for one year – but the docs seem to indicate that this could be extended. However, you’ll probably have a good idea of functionality after one year.

So, what are you waiting for, try out the new free cluster.

We’ll look at getting some data in there next time, but if you can’t wait for that there’s some more details here

Have a great day

cheers

Marty.

Starting your Azure Journey – Free Trial, Tenant and Subscription

G’day,

Over the last few years I’ve had the privilege of helping people start on their Azure journey. Some come along with Azure Subscriptions, others just want to get started.

While there are multiple ways of getting Azure subscriptions, I’m going to concentrate on how to get a free subscription and how I would walk through this with somebody who was doing this for the very first time.

On the way, there are a few interesting points that come up, which I feel are beneficial to introduce at this point – and drill into in more details once more experience has been gained. After all, whenever we start something new there’s always that element of culture shock and finding your feet – at least there is with me.

The very first thing that I would advise somebody to do is to get a brand new Microsoft account (known as an MSA) – the simplest thing to do is head to outlook.com and open a brand new account. These accounts have gone by various different terms over the years (such as Passport) – but nowadays it’s just an MSA.

Once that has been set up, head to https://portal.azure.com/ and log in using the MSA credentials that you have just created.

At this point, the question normally arises as to why a new MSA is recommended. Well, my answer to that is that a brand new (outlook.com) account will have never been used with any Azure infrastructure previously.

I’d avoid a company account, event a new one as you’ll likely end up being linked to their Azure Tenant. (I’ll blog more on this later)

At the portal we should see a screen like the following, We’ll be hitting the “Free Trial” button soon – but we’ll have a look around first.

Before we provision our free trial, lets take a look around Azure Active Directory. As we progress our journey, it’s important to understand the relationship between an Azure Tenant and an Azure Subscription. An Azure Tenant is used by Azure Active Directory – at present we do not have an Azure Tenant, but we do have the ability to look around Azure Active Directory. Type “Azure Active Directory” in the blue search box at the top of the screen and lets look around.

Upon choosing the above option, you’ll see a menu on the left hand side of the screen appear. Have a look around this. You won’t be able to create anything (on the off chance that a button appears letting you, chances are you’ll get an error when you press the button as at this point you do not have an Azure Tenant)

You’ll see that here you can create Users, Groups, Administrative units and various other things. Just have a look, browse around and get curious about what’s on offer – but remember, at this stage you DO NOT have an Azure Active Directory Tenant (Azure AD). After you have finished looking around then head back to the home page by clicking the “Home” link in the top left hand corner of the browser.

“Home” – at the start of the breadcrumb trail

This will return you to the home screen, where you were Originally – now select the Free Trial button.

At this point, you’re going to need to verify your identity. You’ll do this using your credit card.

This usually is the point at which people are hesitant – and I totally understand.

However, the free trial gives $200 worth of free credit and there is a spending block that WILL NOT allow you to go over that limit. Yes, you can take steps to remove it and these are fairly involved. Removing the spending limit is unlikely to be something that you will do by accident. Also note that you can only use a credit card for 1 free trial.

After filling out your details you’ll end up back at the home screen, it’ll look different now.

Notice in the top left hand corner, your account appears.

You’ve also now got a tenant. If you used an outlook account like I did, the Azure Tenant will take the form

emailname + outlook.onmicrosoft.com

example

email address is myazureaddress@outlook.com

Azure Tenant is – myazureaddressoutlook.onmicrosoft.com

Data Thoughts – Azure and Exchange rates

G’day,

I noticed something in the Azure portal the other day that had escaped my attention previously. Normally, when in Azure accounts I’m using corporate subscription, but this particular day, I was helping somebody who had just opened a “Free Trial” account.

The “Free Trail” account gives $US200 credit. But this account was based in Australia, so the account is billed in Australian dollars.

We headed to “Cost Management and Billing”.

Notice that there’s a few links to educational content on how to interpret things.

Upon provisioning the Free Trial account, we immediately headed to “Cost Management and Billing”

Next, we headed to “Payment Methods” from the left hand side menu and selected the “Azure Credit” tab.

I wondered if this was a good exchange rate.

Today google shows the exchange rate as

And the Exchange Rate shown in the Azure Portal (for the date of this post) was

Not bad Microsoft.

Hopefully, your exchange rates are just as good – I’m sure they are.

Have a great day.

Cheers

Martin.

Azure Data Explorer – Using KQL Magic inside Azure Data Studio

One of my previous blogs looked at using the free resources that Microsoft provides to link to an Azure Data Explorer Cluster and start using Kusto Query Language.

In this post, I want to look at doing a similar thing, but this time using Azure Data Studio (ADS). The cluster that we will link too will be the same one that we previously used when we link to an ADX cluster via the Azure Portal.

Open Azure Data Studio (ADS) and install KQL addin.

Now select “New Notebook” – then select the Python Kernel

If you don’t have a python installation then don’t worry, as soon as we execute our first command then ADS will prompt you to install one.

I’m going to summarize the instructions in this post and add my take to them, if you want to switch to that post (or use the two combined) feel free.

The first two code cells, just set up KQL for us

Here’s the actual code

!pip install Kqlmagic –no-cache-dir –upgrade

%reload_ext Kqlmagic

The next part require authentication – but all you require is a Microsoft account (outlook , hotmail ect)

here’s the code cell

%kql AzureDataExplorer://tenant=”Microsoft.com”;code;cluster=’help’;database=’Samples’

When you run the cell, you’ll be given a code and a link to a Microsoft site.

Simply navigate to the link, authenticate using a Microsoft account and type in the code.

You’ll get a message stating everything is fine and then you’ll be able to head back to ADS and carry on with the final statements they will query the database.

Next, let’s run some code

And the results are

Here’s another KQL statement

The %%kql is a “Magic” command that signals to the kernel that what we are about to run is not Python – but Kusto Query Language!

Hence KQL Magic!

Hope this helps somebody

Cheers

Marty

Azure Data Explorer – A great way to learn Kusto Query Language

Log Analytics is a great resource for storing our monitoring data from Azure.

But in order to get the best from your nicely acquired monitoring data, you’ll need a way to investigate it. You can do that in the Azure Portal by clicking around in the UI.

Kusto Query Language is the way to investigate data that is stored in Log Analytics.

We can either experiment in our own log analytics workspace – or we can use the workspace that Microsoft provide for such things.

The Log Analytics Playground

You can find that here

You’ll need to authenticate with a Microsoft account (eg outlook, hotmail)

Personally, I think a great resource for learning Kusto Query Language (KQL) is the Azure Data Explorer (ADX) cluster that Microsoft provides.

You can find that here

There’s a bit more real estate – but you choose 🙂

The ADX cluster is clean and easy to find your way around

There’s enough data in there to run some KQL queries – and get to grips with the language.

If you’re looking to learn Kusto Query language then I’d suggest heading along to this resource.

Hope this helps somebody.

Cheers

Marty

Azure Data Explorer – Setting up a Cluster

I’ve worked with Log Analytics quite a bit over the years and that’s taken me down the road of the Kusto Query Language – or KQL

So, using Azure Data Explorer Clusters seemed the next logical step on that journey.

Log Analytics is great for storing and querying diagnostic data from Azure (or any other infrastructure) – but I decided that I might want to investigate my own data.

So, with that in mind – I decided to set up my own Azure Data Explorer Cluster and start looking around.

It’s easy enough to find in the Azure Portal – I chose to favorite it as I expect to be using it a fair bit over the next little while

Find “Azure Data Explorer Clusters” via the Azure Portal search bar

You can also get to it via a shortcut

Select “Azure Data Explorer Clusters” – optionally favorite

Next, I select “Create” and then filled in the minimum amount of information needed to get up and running.

Just a few things to focus on here – firstly, the combination of the cluster name and the region is going to form part of a URI – so this has to be unique, and there are certain formats that the name has to meet, but you’ll soon know if you have not met them as the screen will call them out instantly.

Additionally, I’m just creating a Dev cluster here – note that there is no SLA.

Also, the region that I chose has Availability Zones – yours might not, or you might choose not to take advantage of them. I chose to include all three.

You’ll then notice that you can step through all of the remain setup.

Additional setup (can be skipped – I accepted the defaults)

I simply accepted the defaults, but it’s great to know you can define additional settings at this stage.

It’ll take a little time to set the cluster up – but you’ll soon be up and running.

And once we are up and running we can see that the URI is made up of the cluster name and region

In order to create a database on the cluster head to the resource, click on “Databases” in the left hand side menu (scrolling may be needed), click “Add database” near the top of the screen and then enter a name for the database in the fly-out pane.

Create a new database on out ADX cluster

That’s it – we now have our first Azure Data Explorer Cluster created with a new shiny database.

Hope this help somebody.

Cheers

Marty.

Azure Monitoring Diagrams

Recently, I’ve been digging into Monitoring in Azure.

I was really pleased to find that a visual representation of an Azure VNET can be produced from inside the Azure Portal.

Here’s an example

An Azure VNET diagram from the Azure Portal

It’s an SVG image.

You can import these into your documentation.

If you select a VNET in the Azure Portal and then locate the “Monitoring” section, you’ll see a menu item called “Diagram”

Just select it and you end up with a downloadable image like above

Have a great day

cheers

Marty

Azure dependent resources

G’day

They say you learn by your mistakes.

And today (amongst other days) – I did just that.

But in a good way.

I wanted to create a simple Network Interface Card (NIC) for one of my Azure VM’s.

Unfortunately (at least partly), I picked the wrong subscription.

The subscription just happened to be absolutely empty – not a single resource (even a resource group) in the subscription.

Ordinarily, I’d have just gone ahead and created the resources (in this case a NIC) and then realised my mistake at another point down the road – but something caught my eye.

so, exactly like the message says – I had no vnet in my subscription.

I’m sort of pleased the Azure Portal flagged this to me and it left me wondering if it’d flag other resources that were dependent on things that didn’t exist.

Now that’s another thing I have on my (ever going) TODO list – to identity resources that absolutely rely on other resources.

Have a great day.

Cheers

Marty.

What exactly is a modern data warehouse?

G’day,

I was asked recently what a modern data warehouse is – and that’s a vey thought provoking question.

First, there’s the tag “Modern” – I think what’s modern now may not be quite so modern in a year or twos time – maybe sooner, judging by the speed of some technology changes at present.

My initial response – jokingly – was ‘it depends who you ask’, which is a pretty useless – but true – answer.

Firstly let me put some context around my answer. I’ve built my career around Microsoft technologies – so my answer is going to centre around their latest technologies – which are going to be all cloud based, and essentially incorporate Platform-as-a-Service (PaaS) functionality. Your experience may be different, you might be in a different cloud, you might have different ideas – but for me it’s PaaS Services in the Azure cloud. I’ll expand on this shortly.

But let’s (quickly) look back on how I’ve seen data warehouses develop – in my Microsoft world – over the years. They used to be simply built on SQL Server, incorporating fact and dimension tables. There’d be debate about star and snowflake schemas, about kimball or Inmon. More recently people talk about Data Vault. Essentially, people pulled data from their operational (transactional) system into their data warehouse (decision support system or Enterprise data warehouse or whatever they chose to call it).

They did this using some Extract, Transform and Load (ETL) tool (commonly called a data integration tool) like SQL Server integration services.

They might have had a single data source (one transactional system) or they may have had more data sources – so firstly staging the data made sense, then cleaning it and then putting it into the main data warehouse.

We’d have to deal with things like slowly changing dimensions and very often we may not have modelled data exactly as it is in the real world.

We might put analytical models over the data warehouse, initially these were multi-dimensional (MDX) but later Tabular. Then we’d either report straight off the data warehouse or the analytical model – using tools like SQL Server Reporting Services or – lately – Power BI.

For me, things have moved to the cloud and our options have expanded (or should I say exploded)

We can take data from literally any data source and land it in our Azure data lake. This area can be segregated, as it likely contains our raw data. we can now keep this – untouched – as space is literally no longer an impediment (ok, it still costs money, but it’s cheap, and we can have different degrees of storage (hot / cold /archive) and we have to trade off access times with cost. We also don’t have to worry about upgrading and migrating data at any time – this is all part of the service provide by a modern cloud.

We can then start segregating the data into different areas (of the data lake), say a staging area, a clean area, and maybe an area full of curated data sets.

We can pull in different types of data in different formats – CSV, JSON, AVRO, Parquet, and more.

Once we have the data in out desired area we can then pull it into a traditional data warehouse – say in Azure SQL Database for instance (although there maybe better cloud solution). We can use Polybase to virtualise the data.

We could also use Synapse, CosmosDB or maybe databricks.

We can build Machine Learning Models and enable Artificial Intelligence services.

Basically, I think the ‘modern’ data warehouse is about cloud based data services and simply adds to the traditional models of data warehousing.

Whereas the traditional data warehouse is about historical reporting, such as diagnostic and descriptive analytics – the new tools enable predictive and prescriptive analytics along with cognitive capabilities.

So, basically the answer to ‘what is the modern data warehouse?’ could be a lot of things, pieced together in many different ways. and we’re only going to get more as time marches on.

So maybe we should start talking about the changing data warehouse, or the evolving data warehouse or the cloud data warehouse – but not the modern data warehouse, as what’s modern today is sure to change tomorrow.

Anyway, these are just my thoughts. Some will agree, some won’t and they’ll be plenty of diverse ideas – that’s great for innovation. Um, maybe the innovative data warehouse is a better name 🙂

Anyway, have a great day

Cheers

Martin.