{ "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": "
StateEventCount
ATLANTIC SOUTH193
FLORIDA1042
GEORGIA1983
" }, "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": "
StatePopulationEventCount
CALIFORNIA39512223898
TEXAS289958814701
FLORIDA214777371042
" }, "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": "
StatePopulation
California39512223
Texas28995881
Florida21477737
New York19453561
Pennsylvania12801989
Illinois12671821
Ohio11689100
Georgia10617423
North Carolina10488084
Michigan9986857
" }, "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 } ] }