KqlMagic/Getting-Started-With-KqlMagic-on-ADX.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. Introduction\n",
    "\n",
    "Jupyter supports magic functions that extends the capabilities of kernel by supporting  additional commands that are not natively supported by the kernel. \n",
    "kqlmagic helps you to extend the capabilities of Python kernel in Jupyter Notebook  and allows you to run Kusto Query Language queries natively. It supports Azure Data Explorer, Application Insights, and Log Analytics as data sources to run queries against.\n",
    "\n",
    "This tutorial demonstrates some of the key capabilities of kqlmagic querying data from Azure Data Explorer. Please refer the following sample notebooks to learn all the available commands.\n",
    "\n",
    "* [Get Started with Kqlmagic for Azure Data Explorer](https://mybinder.org/v2/gh/Microsoft/jupyter-Kqlmagic/master?filepath=notebooks%2FQuickStart.ipynb) \n",
    "* [Get Started with Kqlmagic for Application Insights](https://mybinder.org/v2/gh/Microsoft/jupyter-Kqlmagic/master?filepath=notebooks%2FQuckStartAI.ipynb) \n",
    "* [Get Started with Kqlmagic for Log Analytics](https://mybinder.org/v2/gh/Microsoft/jupyter-Kqlmagic/master?filepath=notebooks%2FQuickStartLA.ipynb) \n",
    "* [Parametrize your Kqlmagic query with Python](https://mybinder.org/v2/gh/Microsoft/jupyter-Kqlmagic/master?filepath=notebooks%2FParametrizeYourQuery.ipynb) \n",
    "* [Choose colors palette for your Kqlmagic query chart result](https://mybinder.org/v2/gh/Microsoft/jupyter-Kqlmagic/master?filepath=notebooks%2FColorYourCharts.ipynb) \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Prerequisite"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.1 Install kqlmagic library"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install Kqlmagic --no-cache-dir --upgrade -q"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2 Load kqlmagic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "reload_ext Kqlmagic"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3 Connect to the Azure Data Explorer Help cluster\n",
    "\n",
    "Following command connect to the Samples database hosted on Help cluster. For non-Microsoft AAD users, please replace the tenant name “Microsoft.com” with your AAD Tenant."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%kql AzureDataExplorer://tenant=\"Microsoft.com\";code;cluster='help';database='Samples'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3. Query and visualize\n",
    "In this section we will look at how query and visualize data using kql render command and visualize data using ploy.ly library. All with an integrated experience using native KQL [render operator.](https://docs.microsoft.com/azure/kusto/query/renderoperator) kqlmagic supports most charts except timepivot, pivotchart, and ladderchart and all render with attributes are supported except: kind, ysplit, and accumulate. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.1 Query and render piechart"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%kql \n",
    "StormEvents \n",
    "| summarize statecount=count() by State\n",
    "| sort by statecount \n",
    "| limit 10\n",
    "| render piechart title=\"My Pie Chart by State\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2 Query and render timechart\n",
    "\n",
    "Here is another example of rendering timechart. These charts are interactive, try zooming in a specific time by selecting the time range.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%kql\n",
    "StormEvents\n",
    "| summarize count() by bin(StartTime,7d)\n",
    "| render timechart"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 4. Customize the chart colors\n",
    "If you don’t like the default color plate, you can customize the charts by setting the palette options. Let’s look at all the palette available to us. To learn more, please refer this sample notebook: [Choose colors palette for your Kqlmagic query chart result](https://mybinder.org/v2/gh/Microsoft/jupyter-Kqlmagic/master?filepath=notebooks%2FColorYourCharts.ipynb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%kql --palettes -popup_window"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let’s choose “cool” color palettes and render the query again."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%kql -palette_name \"cool\"\n",
    "StormEvents \n",
    "| summarize statecount=count() by State\n",
    "| sort by statecount \n",
    "| limit 10\n",
    "| render piechart title=\"My Pie Chart by State\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 5. Parametrize the query\n",
    "\n",
    "kqlmagic makes it very easy to interchange between Kusto Query Language and Python. To learn more, please refer this sample notebook: [Parametrize your Kqlmagic query with Python](https://mybinder.org/v2/gh/Microsoft/jupyter-Kqlmagic/master?filepath=notebooks%2FParametrizeYourQuery.ipynb) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.1 User Python varialble in your KQL Query\n",
    "The following example demonstrates how you can use the value of a Python variable and in your KQL query to filter the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "statefilter = [\"TEXAS\", \"KANSAS\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%kql\n",
    "let _state = statefilter;\n",
    "StormEvents \n",
    "| where State in (_state) \n",
    "| summarize statecount=count() by bin(StartTime,1d), State\n",
    "| render timechart title = \"Trend\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.2 Convert query results to Pandas DataFrame \n",
    "\n",
    "In previous example we looked at feeding a value from Python variable into KQL query. Now let’s access the results of KQL query in a Pandas DataFrame. You can access the last executed query results by variable name “_kql_raw_result_” and easily convert the results into a Pandas DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = _kql_raw_result_.to_dataframe()\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.3 Chain up the queries using parameters \n",
    "\n",
    "In most analytics scenarios, you may want to create reusable notebooks that contains multiple queries. And feed the results from one query into the subsequent querie(s). The following example demonstrates how you can use the Python variable from the previous cell to filter the data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 5.3.1 Let’s run a query to get top 10 States by DamageProperty"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%kql\n",
    "StormEvents \n",
    "| summarize max(DamageProperty) by State\n",
    "| order by max_DamageProperty desc\n",
    "| limit 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 5.3.2 Extract the top State name and set it into a Python variable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = _kql_raw_result_.to_dataframe()\n",
    "statefilter =df.loc[0].State\n",
    "statefilter"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 5.3.3 Using \"let\" statement, set the Python variable in the query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%kql\n",
    "let _state = statefilter;\n",
    "StormEvents \n",
    "| where State in (_state) \n",
    "| summarize statecount=count() by bin(StartTime,1d), State\n",
    "| render timechart title = \"Trend\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 6. Next steps\n",
    "    \n",
    "Run the help command to know more and explore the following sample notebooks that contains all the supported features.\n",
    "* [Get Started with Kqlmagic for Azure Data Explorer](https://mybinder.org/v2/gh/Microsoft/jupyter-Kqlmagic/master?filepath=notebooks%2FQuickStart.ipynb) \n",
    "* [Get Started with Kqlmagic for Application Insights](https://mybinder.org/v2/gh/Microsoft/jupyter-Kqlmagic/master?filepath=notebooks%2FQuckStartAI.ipynb) \n",
    "* [Get Started with Kqlmagic for Log Analytics](https://mybinder.org/v2/gh/Microsoft/jupyter-Kqlmagic/master?filepath=notebooks%2FQuickStartLA.ipynb) \n",
    "* [Parametrize your Kqlmagic query with Python](https://mybinder.org/v2/gh/Microsoft/jupyter-Kqlmagic/master?filepath=notebooks%2FParametrizeYourQuery.ipynb) \n",
    "* [Choose colors palette for your Kqlmagic query chart result](https://mybinder.org/v2/gh/Microsoft/jupyter-Kqlmagic/master?filepath=notebooks%2FColorYourCharts.ipynb)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%kql --help \"help\""
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}