{
"metadata": {
"kernelspec": {
"name": "SQL",
"display_name": "SQL",
"language": "sql"
},
"language_info": {
"name": "sql",
"version": ""
}
},
"nbformat_minor": 2,
"nbformat": 4,
"cells": [
{
"cell_type": "markdown",
"source": [
"# Cross-cluster querying with Kusto Notebook in Azure Data Studio\n",
"\n",
"This example uses help.kusto.windows.net StormEvents data and joining it with my own private Azure Data Explorer cluster myADXcluster that is in the West Us region. \n",
"\n",
"The goal is to get the number of Storm Event Counts for the top 3 most populated State. \n",
"\n",
"This example exhibits using `cluster('cluster.region').databse('dbname')` syntax.\n",
""
],
"metadata": {
"azdata_cell_guid": "9001d8c9-6602-4938-a50e-41cfa3598f3a"
}
},
{
"cell_type": "markdown",
"source": [
"First, change the Kernel to **Kusto** and Attach to **help.kusto.windows.net** and set the database to **Samples**. Run the following query to ensure that it works. "
],
"metadata": {
"azdata_cell_guid": "4a911ec4-318d-46be-a3b5-7617def51e64"
}
},
{
"cell_type": "code",
"source": [
"StormEvents \r\n",
"| summarize EventCount = count() by State\r\n",
"| take 3"
],
"metadata": {
"azdata_cell_guid": "9842e645-5c75-4a5f-86c6-a7d3fb718880",
"tags": []
},
"outputs": [
{
"output_type": "display_data",
"data": {
"text/html": "Commands completed successfully."
},
"metadata": {}
},
{
"output_type": "display_data",
"data": {
"text/html": "Total execution time: 00:00:00.176"
},
"metadata": {}
},
{
"output_type": "execute_result",
"execution_count": 1,
"data": {
"application/vnd.dataresource+json": {
"schema": {
"fields": [
{
"name": "State"
},
{
"name": "EventCount"
}
]
},
"data": [
{
"0": "ATLANTIC SOUTH",
"1": "193"
},
{
"0": "FLORIDA",
"1": "1042"
},
{
"0": "GEORGIA",
"1": "1983"
}
]
},
"text/html": "
State | EventCount |
---|
ATLANTIC SOUTH | 193 |
FLORIDA | 1042 |
GEORGIA | 1983 |
"
},
"metadata": {
"resultSet": {
"id": 0,
"batchId": 0,
"rowCount": 3,
"complete": false,
"columnInfo": [
{
"isBytes": false,
"isChars": false,
"isSqlVariant": false,
"isUdt": false,
"isXml": false,
"isJson": false,
"sqlDbType": 0,
"isHierarchyId": false,
"isUnknownType": false,
"allowDBNull": true,
"baseCatalogName": "NewDataSet",
"baseColumnName": "State",
"baseSchemaName": null,
"baseServerName": null,
"baseTableName": "Table_0",
"columnName": "State",
"columnOrdinal": 0,
"columnSize": -1,
"isAliased": null,
"isAutoIncrement": false,
"isExpression": null,
"isHidden": null,
"isIdentity": null,
"isKey": false,
"isLong": false,
"isReadOnly": false,
"isUnique": false,
"numericPrecision": null,
"numericScale": null,
"udtAssemblyQualifiedName": null,
"dataType": "System.String, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e",
"dataTypeName": "dynamic"
},
{
"isBytes": false,
"isChars": false,
"isSqlVariant": false,
"isUdt": false,
"isXml": false,
"isJson": false,
"sqlDbType": 0,
"isHierarchyId": false,
"isUnknownType": false,
"allowDBNull": true,
"baseCatalogName": "NewDataSet",
"baseColumnName": "EventCount",
"baseSchemaName": null,
"baseServerName": null,
"baseTableName": "Table_0",
"columnName": "EventCount",
"columnOrdinal": 1,
"columnSize": -1,
"isAliased": null,
"isAutoIncrement": false,
"isExpression": null,
"isHidden": null,
"isIdentity": null,
"isKey": false,
"isLong": false,
"isReadOnly": false,
"isUnique": false,
"numericPrecision": null,
"numericScale": null,
"udtAssemblyQualifiedName": null,
"dataType": "System.Int64, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e",
"dataTypeName": "dynamic"
}
],
"specialAction": null
}
}
}
],
"execution_count": 1
},
{
"cell_type": "markdown",
"source": [
"The AAD account that I used earlier to connect to **help.kusto.windows.net**, should also allow me to connect to my **myADXcluster** cluster. \r\n",
"\r\n",
"> **Tip**: since I have multiple accounts, I need to ensure that I'm logging in to the right AAD account :)\r\n",
"\r\n",
"\r\n",
"The following query allows me to perform cross cluster querying with joins. This retrieves the number of Storm Event Counts for the top 3 most populated State. "
],
"metadata": {
"azdata_cell_guid": "ec635ab5-a4b7-4c2d-afb6-844429f2b218"
}
},
{
"cell_type": "code",
"source": [
"let stormSummarized = StormEvents \r\n",
"| summarize EventCount = count() by State = toupper(State);\r\n",
"cluster('myADXcluster.westus').database('mykustodb').StatePopulation\r\n",
"| project State = toupper(State), Population\r\n",
"| join (stormSummarized) on State\r\n",
"| project State, Population, EventCount\r\n",
"| sort by Population \r\n",
"| take 3\r\n",
"\r\n",
""
],
"metadata": {
"azdata_cell_guid": "6e716161-996b-45de-af18-2c79fc6984c5"
},
"outputs": [
{
"output_type": "display_data",
"data": {
"text/html": "Commands completed successfully."
},
"metadata": {}
},
{
"output_type": "display_data",
"data": {
"text/html": "Total execution time: 00:00:00.961"
},
"metadata": {}
},
{
"output_type": "execute_result",
"execution_count": 2,
"data": {
"application/vnd.dataresource+json": {
"schema": {
"fields": [
{
"name": "State"
},
{
"name": "Population"
},
{
"name": "EventCount"
}
]
},
"data": [
{
"0": "CALIFORNIA",
"1": "39512223",
"2": "898"
},
{
"0": "TEXAS",
"1": "28995881",
"2": "4701"
},
{
"0": "FLORIDA",
"1": "21477737",
"2": "1042"
}
]
},
"text/html": "State | Population | EventCount |
---|
CALIFORNIA | 39512223 | 898 |
TEXAS | 28995881 | 4701 |
FLORIDA | 21477737 | 1042 |
"
},
"metadata": {
"resultSet": {
"id": 0,
"batchId": 0,
"rowCount": 3,
"complete": true,
"columnInfo": [
{
"isBytes": false,
"isChars": false,
"isSqlVariant": false,
"isUdt": false,
"isXml": false,
"isJson": false,
"sqlDbType": 0,
"isHierarchyId": false,
"isUnknownType": false,
"allowDBNull": true,
"baseCatalogName": "NewDataSet",
"baseColumnName": "State",
"baseSchemaName": null,
"baseServerName": null,
"baseTableName": "Table_0",
"columnName": "State",
"columnOrdinal": 0,
"columnSize": -1,
"isAliased": null,
"isAutoIncrement": false,
"isExpression": null,
"isHidden": null,
"isIdentity": null,
"isKey": false,
"isLong": false,
"isReadOnly": false,
"isUnique": false,
"numericPrecision": null,
"numericScale": null,
"udtAssemblyQualifiedName": null,
"dataType": "System.String, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e",
"dataTypeName": "dynamic"
},
{
"isBytes": false,
"isChars": false,
"isSqlVariant": false,
"isUdt": false,
"isXml": false,
"isJson": false,
"sqlDbType": 0,
"isHierarchyId": false,
"isUnknownType": false,
"allowDBNull": true,
"baseCatalogName": "NewDataSet",
"baseColumnName": "Population",
"baseSchemaName": null,
"baseServerName": null,
"baseTableName": "Table_0",
"columnName": "Population",
"columnOrdinal": 1,
"columnSize": -1,
"isAliased": null,
"isAutoIncrement": false,
"isExpression": null,
"isHidden": null,
"isIdentity": null,
"isKey": false,
"isLong": false,
"isReadOnly": false,
"isUnique": false,
"numericPrecision": null,
"numericScale": null,
"udtAssemblyQualifiedName": null,
"dataType": "System.Int32, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e",
"dataTypeName": "dynamic"
},
{
"isBytes": false,
"isChars": false,
"isSqlVariant": false,
"isUdt": false,
"isXml": false,
"isJson": false,
"sqlDbType": 0,
"isHierarchyId": false,
"isUnknownType": false,
"allowDBNull": true,
"baseCatalogName": "NewDataSet",
"baseColumnName": "EventCount",
"baseSchemaName": null,
"baseServerName": null,
"baseTableName": "Table_0",
"columnName": "EventCount",
"columnOrdinal": 2,
"columnSize": -1,
"isAliased": null,
"isAutoIncrement": false,
"isExpression": null,
"isHidden": null,
"isIdentity": null,
"isKey": false,
"isLong": false,
"isReadOnly": false,
"isUnique": false,
"numericPrecision": null,
"numericScale": null,
"udtAssemblyQualifiedName": null,
"dataType": "System.Int64, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e",
"dataTypeName": "dynamic"
}
],
"specialAction": {
"none": true,
"expectYukonXMLShowPlan": false
}
}
}
}
],
"execution_count": 2
},
{
"cell_type": "markdown",
"source": [
"## Cross cluster query (without joining)\r\n",
"\r\n",
"Here's another way of querying another ADX cluster while connected to ADX Help cluster.\r\n",
"> **Note**: As of Azure Data Studio v1.25.1, the Native KQL (KQL Extension) does not support connecting to Azure Log Analytics workspace natively. The same method can be used as a workaround for that. See [Demo-NativeKusto-CrossServiceQuery](./Demo-NativeKusto-CrossServiceQuery.ipynb)\r\n",
""
],
"metadata": {
"azdata_cell_guid": "fcc0ac41-cfc7-429d-a9bb-796360137d86"
}
},
{
"cell_type": "code",
"source": [
"cluster('myADXcluster.westus').database('mykustodb').StatePopulation\r\n",
"| sort by Population \r\n",
"| take 10"
],
"metadata": {
"azdata_cell_guid": "f9b0ea34-ef69-4a22-baa4-de902104424e"
},
"outputs": [
{
"output_type": "display_data",
"data": {
"text/html": "Commands completed successfully."
},
"metadata": {}
},
{
"output_type": "display_data",
"data": {
"text/html": "Total execution time: 00:00:00.172"
},
"metadata": {}
},
{
"output_type": "execute_result",
"execution_count": 3,
"data": {
"application/vnd.dataresource+json": {
"schema": {
"fields": [
{
"name": "State"
},
{
"name": "Population"
}
]
},
"data": [
{
"0": "California",
"1": "39512223"
},
{
"0": "Texas",
"1": "28995881"
},
{
"0": "Florida",
"1": "21477737"
},
{
"0": "New York",
"1": "19453561"
},
{
"0": "Pennsylvania",
"1": "12801989"
},
{
"0": "Illinois",
"1": "12671821"
},
{
"0": "Ohio",
"1": "11689100"
},
{
"0": "Georgia",
"1": "10617423"
},
{
"0": "North Carolina",
"1": "10488084"
},
{
"0": "Michigan",
"1": "9986857"
}
]
},
"text/html": "State | Population |
---|
California | 39512223 |
Texas | 28995881 |
Florida | 21477737 |
New York | 19453561 |
Pennsylvania | 12801989 |
Illinois | 12671821 |
Ohio | 11689100 |
Georgia | 10617423 |
North Carolina | 10488084 |
Michigan | 9986857 |
"
},
"metadata": {
"resultSet": {
"id": 0,
"batchId": 0,
"rowCount": 10,
"complete": true,
"columnInfo": [
{
"isBytes": false,
"isChars": false,
"isSqlVariant": false,
"isUdt": false,
"isXml": false,
"isJson": false,
"sqlDbType": 0,
"isHierarchyId": false,
"isUnknownType": false,
"allowDBNull": true,
"baseCatalogName": "NewDataSet",
"baseColumnName": "State",
"baseSchemaName": null,
"baseServerName": null,
"baseTableName": "Table_0",
"columnName": "State",
"columnOrdinal": 0,
"columnSize": -1,
"isAliased": null,
"isAutoIncrement": false,
"isExpression": null,
"isHidden": null,
"isIdentity": null,
"isKey": false,
"isLong": false,
"isReadOnly": false,
"isUnique": false,
"numericPrecision": null,
"numericScale": null,
"udtAssemblyQualifiedName": null,
"dataType": "System.String, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e",
"dataTypeName": "dynamic"
},
{
"isBytes": false,
"isChars": false,
"isSqlVariant": false,
"isUdt": false,
"isXml": false,
"isJson": false,
"sqlDbType": 0,
"isHierarchyId": false,
"isUnknownType": false,
"allowDBNull": true,
"baseCatalogName": "NewDataSet",
"baseColumnName": "Population",
"baseSchemaName": null,
"baseServerName": null,
"baseTableName": "Table_0",
"columnName": "Population",
"columnOrdinal": 1,
"columnSize": -1,
"isAliased": null,
"isAutoIncrement": false,
"isExpression": null,
"isHidden": null,
"isIdentity": null,
"isKey": false,
"isLong": false,
"isReadOnly": false,
"isUnique": false,
"numericPrecision": null,
"numericScale": null,
"udtAssemblyQualifiedName": null,
"dataType": "System.Int32, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e",
"dataTypeName": "dynamic"
}
],
"specialAction": {
"none": true,
"expectYukonXMLShowPlan": false
}
}
}
}
],
"execution_count": 3
}
]
}