esql-812-tutorial.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "<a target=\"_blank\" href=\"https://colab.research.google.com/github/leemthompo/notebook-tests/blob/main/esql-812-tutorial.ipynb\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "vG5NxAmpa-ZF",
        "outputId": "fb00d7b9-f67b-4bad-e84b-53ae9b915616"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Collecting elasticsearch\n",
            "  Downloading elasticsearch-8.12.0-py3-none-any.whl (431 kB)\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m431.9/431.9 kB\u001b[0m \u001b[31m2.6 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hCollecting elastic-transport<9,>=8 (from elasticsearch)\n",
            "  Downloading elastic_transport-8.12.0-py3-none-any.whl (59 kB)\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m59.9/59.9 kB\u001b[0m \u001b[31m2.1 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hRequirement already satisfied: urllib3<3,>=1.26.2 in /usr/local/lib/python3.10/dist-packages (from elastic-transport<9,>=8->elasticsearch) (2.0.7)\n",
            "Requirement already satisfied: certifi in /usr/local/lib/python3.10/dist-packages (from elastic-transport<9,>=8->elasticsearch) (2023.11.17)\n",
            "Installing collected packages: elastic-transport, elasticsearch\n",
            "Successfully installed elastic-transport-8.12.0 elasticsearch-8.12.0\n"
          ]
        }
      ],
      "source": [
        "# Install packages\n",
        "\n",
        "!pip install elasticsearch"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "b2vOwH2lbFFF"
      },
      "outputs": [],
      "source": [
        "# Import packages\n",
        "\n",
        "from elasticsearch import Elasticsearch\n",
        "from elasticsearch.client import EsqlClient\n",
        "from getpass import getpass"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "rBbdkhAIbtym",
        "outputId": "d6b7d359-8a6a-49dd-df41-85ecfc6360cc"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Elastic Cloud ID: ··········\n",
            "Elastic Api Key: ··········\n"
          ]
        }
      ],
      "source": [
        "# Connect to Elasticsearch and create client instance\n",
        "\n",
        "# https://www.elastic.co/search-labs/tutorials/install-elasticsearch/elastic-cloud#finding-your-cloud-id\n",
        "ELASTIC_CLOUD_ID = getpass(\"Elastic Cloud ID: \")\n",
        "\n",
        "# https://www.elastic.co/search-labs/tutorials/install-elasticsearch/elastic-cloud#creating-an-api-key\n",
        "ELASTIC_API_KEY = getpass(\"Elastic API Key: \")\n",
        "\n",
        "# Create the client instance\n",
        "client = Elasticsearch(\n",
        "    # For local development\n",
        "    # hosts=[\"http://localhost:9200\"]\n",
        "    cloud_id=ELASTIC_CLOUD_ID,\n",
        "    api_key=ELASTIC_API_KEY,\n",
        ")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Tht3ghy8dlkX"
      },
      "outputs": [],
      "source": [
        "# Create ES|QL client instance\n",
        "\n",
        "esql = EsqlClient(client)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "nkFLjdQzcPjQ"
      },
      "outputs": [],
      "source": [
        "# Suppress specific Elasticsearch warnings about default limit of [500] that pollute responses\n",
        "\n",
        "import warnings\n",
        "from elasticsearch import ElasticsearchWarning\n",
        "\n",
        "warnings.filterwarnings('ignore', category=ElasticsearchWarning)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "UC4KZFRYjzkc"
      },
      "outputs": [],
      "source": [
        "# Format response to return human-readable tables\n",
        "\n",
        "def format_response(response_data):\n",
        "    column_names = [col['name'] for col in response_data['columns']]\n",
        "    column_widths = [max(len(name), max((len(str(row[i])) for row in response_data['values']), default=0)) for i, name in enumerate(column_names)]\n",
        "    row_format = \" | \".join([\"{:<\" + str(width) + \"}\" for width in column_widths])\n",
        "    print(row_format.format(*column_names))\n",
        "    print(\"-\" * sum(column_widths) + \"-\" * (len(column_widths) - 1) * 3)\n",
        "    for row in response_data['values']:\n",
        "      print(row_format.format(*row))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Jyb40LkJh6sN",
        "outputId": "c1fbf62f-3d85-4d86-bf2f-3177de176f49"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "@timestamp               | client_ip    | event_duration | message              \n",
            "--------------------------------------------------------------------------------\n",
            "2023-10-23T12:15:03.360Z | 172.21.2.162 | 3450233        | Connected to 10.1.0.3\n",
            "2023-10-23T12:27:28.948Z | 172.21.2.113 | 2764889        | Connected to 10.1.0.2\n",
            "2023-10-23T13:33:34.937Z | 172.21.0.5   | 1232382        | Disconnected         \n",
            "2023-10-23T13:51:54.732Z | 172.21.3.15  | 725448         | Connection error     \n",
            "2023-10-23T13:52:55.015Z | 172.21.3.15  | 8268153        | Connection error     \n",
            "2023-10-23T13:53:55.832Z | 172.21.3.15  | 5033755        | Connection error     \n",
            "2023-10-23T13:55:01.543Z | 172.21.3.15  | 1756467        | Connected to 10.1.0.1\n"
          ]
        }
      ],
      "source": [
        "# Your first ES|QL query!\n",
        "\n",
        "esql_query = \"FROM sample_data\"\n",
        "\n",
        "response = esql.query(query=esql_query)\n",
        "format_response(response)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "B1P44sOfmehL"
      },
      "source": [
        "## Processing commands\n",
        "\n",
        "A source command can be followed by one or more [processing commands](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-commands.html#esql-processing-commands), separated by a pipe character: `|`. Processing commands change an input table by adding, removing, or changing rows and columns. Processing commands can perform filtering, projection, aggregation, and more.\n",
        "\n",
        "For example, you can use the `LIMIT` command to limit the number of rows that are returned, up to a maximum of 10,000 rows:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "VGhogxc4mEUW",
        "outputId": "68dd59ae-c4ee-43ab-d3a2-5c8a0a8fdc0b"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "@timestamp               | client_ip    | event_duration | message              \n",
            "--------------------------------------------------------------------------------\n",
            "2023-10-23T12:15:03.360Z | 172.21.2.162 | 3450233        | Connected to 10.1.0.3\n",
            "2023-10-23T12:27:28.948Z | 172.21.2.113 | 2764889        | Connected to 10.1.0.2\n",
            "2023-10-23T13:33:34.937Z | 172.21.0.5   | 1232382        | Disconnected         \n"
          ]
        }
      ],
      "source": [
        "esql_query = \"\"\"\n",
        "FROM sample_data\n",
        "| LIMIT 3\n",
        "\"\"\"\n",
        "\n",
        "response = esql.query(query=esql_query)\n",
        "format_response(response)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RLPlQtYem4fc"
      },
      "source": [
        "### Sort a table\n",
        "\n",
        "Another processing command is the [`SORT`](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-commands.html#esql-sort) command. By default, the rows returned by `FROM` don’t have a defined sort order. Use the `SORT` command to sort rows on one or more columns:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "m5LOqw1kmGkb",
        "outputId": "08282100-7141-4088-ffde-d200e6a6e3cc"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "@timestamp               | client_ip    | event_duration | message              \n",
            "--------------------------------------------------------------------------------\n",
            "2023-10-23T12:15:03.360Z | 172.21.2.162 | 3450233        | Connected to 10.1.0.3\n",
            "2023-10-23T12:27:28.948Z | 172.21.2.113 | 2764889        | Connected to 10.1.0.2\n",
            "2023-10-23T13:33:34.937Z | 172.21.0.5   | 1232382        | Disconnected         \n",
            "2023-10-23T13:51:54.732Z | 172.21.3.15  | 725448         | Connection error     \n",
            "2023-10-23T13:52:55.015Z | 172.21.3.15  | 8268153        | Connection error     \n",
            "2023-10-23T13:53:55.832Z | 172.21.3.15  | 5033755        | Connection error     \n",
            "2023-10-23T13:55:01.543Z | 172.21.3.15  | 1756467        | Connected to 10.1.0.1\n"
          ]
        }
      ],
      "source": [
        "esql_query = \"\"\"\n",
        "FROM sample_data\n",
        "| SORT @timestamp DESC\n",
        "\"\"\"\n",
        "\n",
        "response = esql.query(query=esql_query)\n",
        "format_response(response)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9YBqc9ftoNkb"
      },
      "source": [
        "### Query the data\n",
        "\n",
        "Use the [`WHERE`](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-commands.html#esql-where) command to query the data. For example, to find all events with a duration longer than 5ms:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "wR4Pt165oZ6i",
        "outputId": "3939b6dd-dbd0-4ce4-cd54-7d9a275f03d9"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "@timestamp               | client_ip   | event_duration | message         \n",
            "--------------------------------------------------------------------------\n",
            "2023-10-23T13:52:55.015Z | 172.21.3.15 | 8268153        | Connection error\n",
            "2023-10-23T13:53:55.832Z | 172.21.3.15 | 5033755        | Connection error\n"
          ]
        }
      ],
      "source": [
        "esql_query = \"\"\"\n",
        "FROM sample_data\n",
        "| WHERE event_duration > 5000000\n",
        "\"\"\"\n",
        "\n",
        "response = esql.query(query=esql_query)\n",
        "format_response(response)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "C_6-0a4ZopKn"
      },
      "source": [
        "`WHERE` supports several [operators](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-functions-operators.html#esql-operators).\n",
        "\n",
        "For example, you can use [`LIKE`](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-functions-operators.html#esql-like-operator) to run a wildcard query against the message column:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "SsU1Nsh1ouFs",
        "outputId": "7f2f058c-42ba-482f-b0e5-be0a5bec07d6"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "@timestamp               | client_ip    | event_duration | message              \n",
            "--------------------------------------------------------------------------------\n",
            "2023-10-23T12:15:03.360Z | 172.21.2.162 | 3450233        | Connected to 10.1.0.3\n",
            "2023-10-23T12:27:28.948Z | 172.21.2.113 | 2764889        | Connected to 10.1.0.2\n",
            "2023-10-23T13:55:01.543Z | 172.21.3.15  | 1756467        | Connected to 10.1.0.1\n"
          ]
        }
      ],
      "source": [
        "esql_query = \"\"\"\n",
        "FROM sample_data\n",
        "| WHERE message LIKE \"Connected*\"\n",
        "\"\"\"\n",
        "\n",
        "response = esql.query(query=esql_query)\n",
        "format_response(response)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HjuCP76BEsDz"
      },
      "source": [
        "### More processing commands\n",
        "\n",
        "There are many other processing commands, like [`KEEP`](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-commands.html#esql-keep \"KEEP\") and [`DROP`](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-commands.html#esql-drop \"DROP\") to keep or drop columns, [`ENRICH`](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-commands.html#esql-enrich \"ENRICH\") to enrich a table with data from indices in Elasticsearch, and [`DISSECT`](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-commands.html#esql-dissect \"DISSECT\") and [`GROK`](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-commands.html#esql-grok \"GROK\") to process data. Refer to [Processing commands](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-commands.html#esql-processing-commands \"Processing commands\") for an overview."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ESaKzB8HFMXj"
      },
      "source": [
        "## Chain processing commands\n",
        "\n",
        "You can chain processing commands, separated by a pipe character: `|`. Each\n",
        "processing command works on the output table of the previous command. The result\n",
        "of a query is the table produced by the final processing command.\n",
        "\n",
        "The following example first sorts the table on `@timestamp`, and next limits the\n",
        "result set to 3 rows:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ztm9lRiIFTnS"
      },
      "outputs": [],
      "source": [
        "esql_query = \"\"\"\n",
        "FROM sample_data\n",
        "| SORT @timestamp DESC\n",
        "| LIMIT 3\n",
        "\"\"\"\n",
        "\n",
        "response = esql.query(query=esql_query)\n",
        "format_response(response)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cogdcusMFgB3"
      },
      "source": [
        "> ℹ️ The order of processing commands is important. First limiting the result set to 3 rows before sorting those 3 rows would most likely return a result that is different than this example, where the sorting comes before the limit."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vsJ5wFitFlow"
      },
      "source": [
        "## Compute values\n",
        "\n",
        "Use the [`EVAL`](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-commands.html#esql-eval \"EVAL\") command to append columns to a table, with calculated values. For example, the following query appends a `duration_ms` column. The values in the column are computed by dividing `event_duration` by 1,000,000. In other words: `event_duration` converted from nanoseconds to milliseconds."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "bxCjCNwxFx4e"
      },
      "outputs": [],
      "source": [
        "esql_query = \"\"\"\n",
        "FROM sample_data\n",
        "| EVAL duration_ms = event_duration/1000000.0\n",
        "\"\"\"\n",
        "\n",
        "response = esql.query(query=esql_query)\n",
        "format_response(response)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "OY6HN6PtF8zy"
      },
      "source": [
        "`EVAL` supports several [functions](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-functions-operators.html#esql-functions). For example, to round a number to the closest number with the specified number of digits, use the [`ROUND`](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-functions-operators.html#esql-round \"ROUND\") function:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "RTx1xw2ZF__8"
      },
      "outputs": [],
      "source": [
        "esql_query = \"\"\"\n",
        "FROM sample_data\n",
        "| EVAL duration_ms = ROUND(event_duration/1000000.0, 1)\n",
        "\"\"\"\n",
        "\n",
        "response = esql.query(query=esql_query)\n",
        "format_response(response)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NW3NYqMLGI_O"
      },
      "source": [
        "## Calculate statistics\n",
        "\n",
        "You can also use ES|QL to aggregate your data. Use the [`STATS ... BY`](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-commands.html#esql-stats-by \"STATS ... BY\") command to calculate statistics.\n",
        "\n",
        "For example, to calculate the median duration:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "v-vamaNVGTwS"
      },
      "outputs": [],
      "source": [
        "esql_query = \"\"\"\n",
        "FROM sample_data\n",
        "| STATS median_duration = MEDIAN(event_duration)\n",
        "\"\"\"\n",
        "\n",
        "response = esql.query(query=esql_query)\n",
        "format_response(response)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Tyr8TURjGXZm"
      },
      "source": [
        "You can calculate multiple stats with one command:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "QLGT8GTvGZIg"
      },
      "outputs": [],
      "source": [
        "esql_query = \"\"\"\n",
        "FROM sample_data\n",
        "| STATS median_duration = MEDIAN(event_duration), max_duration = MAX(event_duration)\n",
        "\"\"\"\n",
        "\n",
        "response = esql.query(query=esql_query)\n",
        "format_response(response)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZeLti0ByGc9S"
      },
      "source": [
        "Use BY to group calculated stats by one or more columns. For example, to calculate the median duration per client IP:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "EWGMRhZrHH-c"
      },
      "outputs": [],
      "source": [
        "esql_query = \"\"\"\n",
        "FROM sample_data\n",
        "| STATS median_duration = MEDIAN(event_duration) BY client_ip\n",
        "\"\"\"\n",
        "\n",
        "response = esql.query(query=esql_query)\n",
        "format_response(response)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "y3zS4-zLHO5I"
      },
      "source": [
        "## Access columns\n",
        "\n",
        "You can access columns by their name. If a name contains special characters, [it needs to be quoted](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-syntax.html#esql-identifiers \"Identifiers\") with backticks (`` ` ``).\n",
        "\n",
        "Assigning an explicit name to a column created by `EVAL` or `STATS` is optional. If you don’t provide a name, the new column name is equal to the function expression. For example:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "V62P4GEgHojF"
      },
      "outputs": [],
      "source": [
        "esql_query = \"\"\"\n",
        "FROM sample_data\n",
        "| EVAL event_duration/1000000.0\n",
        "\"\"\"\n",
        "\n",
        "response = esql.query(query=esql_query)\n",
        "format_response(response)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Al_cDCIIHpsR"
      },
      "source": [
        "In this query, `EVAL` adds a new column named `event_duration/1000000.0`. Because its name contains special characters, to access this column, quote it with backticks:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Jv6y2SjLHtBA"
      },
      "outputs": [],
      "source": [
        "esql_query = \"\"\"\n",
        "FROM sample_data\n",
        "| EVAL event_duration/1000000.0\n",
        "| STATS MEDIAN(`event_duration/1000000.0`)\n",
        "\"\"\"\n",
        "response = esql.query(query=esql_query)\n",
        "format_response(response)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZKB3miTcH1St"
      },
      "source": [
        "## Create a histogram\n",
        "\n",
        "To track statistics over time, ES|QL enables you to create histograms using the [`AUTO_BUCKET`](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-functions-operators.html#esql-auto_bucket \"AUTO_BUCKET\") function. `AUTO_BUCKET` creates human-friendly bucket sizes and returns a value for each row that corresponds to the resulting bucket the row falls into.\n",
        "\n",
        "For example, to create hourly buckets for the data on October 23rd:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "WtKXFOfyH4zt"
      },
      "outputs": [],
      "source": [
        "esql_query = \"\"\"\n",
        "FROM sample_data\n",
        "| KEEP @timestamp\n",
        "| EVAL bucket = AUTO_BUCKET (@timestamp, 24, \"2023-10-23T00:00:00Z\", \"2023-10-23T23:59:59Z\")\n",
        "\"\"\"\n",
        "response = esql.query(query=esql_query)\n",
        "format_response(response)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4lXgoIGDH8ZK"
      },
      "source": [
        "Combine `AUTO_BUCKET` with [`STATS ... BY`](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-commands.html#esql-stats-by \"STATS ... BY\") to create a histogram. For example, to count the number of events per hour:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "FgJHT0BpIEqj"
      },
      "outputs": [],
      "source": [
        "esql_query = \"\"\"\n",
        "FROM sample_data\n",
        "| KEEP @timestamp, event_duration\n",
        "| EVAL bucket = AUTO_BUCKET (@timestamp, 24, \"2023-10-23T00:00:00Z\", \"2023-10-23T23:59:59Z\")\n",
        "| STATS COUNT(*) BY bucket\n",
        "\"\"\"\n",
        "response = esql.query(query=esql_query)\n",
        "format_response(response)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LKmuoDfRIH67"
      },
      "source": [
        "Or the median duration per hour:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "gDHT5BHNIHQM"
      },
      "outputs": [],
      "source": [
        "esql_query = \"\"\"\n",
        "FROM sample_data\n",
        "| KEEP @timestamp, event_duration\n",
        "| EVAL bucket = AUTO_BUCKET (@timestamp, 24, \"2023-10-23T00:00:00Z\", \"2023-10-23T23:59:59Z\")\n",
        "| STATS median_duration = MEDIAN(event_duration) BY bucket\n",
        "\"\"\"\n",
        "response = esql.query(query=esql_query)\n",
        "format_response(response)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ETZb10tIIKyO"
      },
      "source": [
        "## Enrich data\n",
        "\n",
        "ES|QL enables you to [enrich](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-enrich-data.html \"Data enrichment\") a table with data from indices in Elasticsearch, using the [`ENRICH`](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-commands.html#esql-enrich \"ENRICH\") command.\n",
        "\n",
        "> ℹ️ Before you can use `ENRICH`, you first need to [create](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-enrich-data.html#esql-create-enrich-policy \"Create an enrich policy\") and [execute](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-enrich-data.html#esql-execute-enrich-policy \"Execute the enrich policy\") an [enrich policy](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-enrich-data.html#esql-enrich-policy). We provide a demo environment at [ela.st/ql](https://ela.st/ql/), where an enrich policy called `clientip_policy` has already been created and executed, if you just want to see how it works.\n",
        "\n",
        "The following requests create and execute a policy called `clientip_policy`. The policy links an IP address to an environment (\"Development\", \"QA\", or \"Production\").\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "2QNKgM--IQtJ"
      },
      "outputs": [],
      "source": [
        "# Define the mapping\n",
        "mapping = {\n",
        "    \"mappings\": {\n",
        "        \"properties\": {\n",
        "            \"client_ip\": {\"type\": \"keyword\"},\n",
        "            \"env\": {\"type\": \"keyword\"}\n",
        "        }\n",
        "    }\n",
        "}\n",
        "\n",
        "# Create the index with the mapping\n",
        "client.indices.create(index=\"clientips\", body=mapping)\n",
        "\n",
        "# Prepare bulk data\n",
        "bulk_data = [\n",
        "    {\"index\": {}},\n",
        "    {\"client_ip\": \"172.21.0.5\", \"env\": \"Development\"},\n",
        "    {\"index\": {}},\n",
        "    {\"client_ip\": \"172.21.2.113\", \"env\": \"QA\"},\n",
        "    {\"index\": {}},\n",
        "    {\"client_ip\": \"172.21.2.162\", \"env\": \"QA\"},\n",
        "    {\"index\": {}},\n",
        "    {\"client_ip\": \"172.21.3.15\", \"env\": \"Production\"},\n",
        "    {\"index\": {}},\n",
        "    {\"client_ip\": \"172.21.3.16\", \"env\": \"Production\"}\n",
        "]\n",
        "\n",
        "# Bulk index the data\n",
        "client.bulk(index=\"clientips\", body=bulk_data)\n",
        "\n",
        "# Define the enrich policy\n",
        "policy = {\n",
        "    \"match\": {\n",
        "        \"indices\": \"clientips\",\n",
        "        \"match_field\": \"client_ip\",\n",
        "        \"enrich_fields\": [\"env\"]\n",
        "    }\n",
        "}\n",
        "\n",
        "# Put the enrich policy\n",
        "client.enrich.put_policy(name='clientip_policy', body=policy)\n",
        "\n",
        "# Execute the enrich policy without waiting for completion\n",
        "client.enrich.execute_policy(name='clientip_policy', wait_for_completion=False)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GtauD2gHJ683"
      },
      "source": [
        "After creating and executing a policy, you can use it with the `ENRICH` command:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "IHER1VM2KsqV"
      },
      "outputs": [],
      "source": [
        "esql_query = \"\"\"\n",
        "FROM sample_data\n",
        "| KEEP @timestamp, client_ip, event_duration\n",
        "| EVAL client_ip = TO_STRING(client_ip)\n",
        "| ENRICH clientip_policy ON client_ip WITH env\n",
        "\"\"\"\n",
        "response = esql.query(query=esql_query)\n",
        "format_response(response)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rWrJ99ReKvRT"
      },
      "source": [
        "You can use the new `env` column that’s added by the `ENRICH` command in subsequent commands. For example, to calculate the median duration per environment:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "9Fyz2JkZK47P"
      },
      "outputs": [],
      "source": [
        "esql_query = \"\"\"\n",
        "FROM sample_data\n",
        "| KEEP @timestamp, client_ip, event_duration\n",
        "| EVAL client_ip = TO_STRING(client_ip)\n",
        "| ENRICH clientip_policy ON client_ip WITH env\n",
        "| STATS median_duration = MEDIAN(event_duration) BY env\n",
        "\"\"\"\n",
        "response = esql.query(query=esql_query)\n",
        "format_response(response)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YonYg0UhK8Rk"
      },
      "source": [
        "For more about data enrichment with ES|QL, refer to [Data enrichment](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-enrich-data.html \"Data enrichment\")."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Fsjoqo-eK9qX"
      },
      "source": [
        "## Process data\n",
        "\n",
        "Your data may contain unstructured strings that you want to [structure](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-process-data-with-dissect-and-grok.html \"Data processing with DISSECT and GROK\") to make it easier to analyze the data. For example, the sample data contains log messages like:\n",
        "\n",
        "```\n",
        "\"Connected to 10.1.0.3\"\n",
        "```\n",
        "\n",
        "By extracting the IP address from these messages, you can determine which IP has accepted the most client connections.\n",
        "\n",
        "To structure unstructured strings at query time, you can use the ES|QL [`DISSECT`](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-commands.html#esql-dissect \"DISSECT\") and [`GROK`](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-commands.html#esql-grok \"GROK\") commands. `DISSECT` works by breaking up a string using a delimiter-based pattern. `GROK` works similarly, but uses regular expressions. This makes `GROK` more powerful, but generally also slower.\n",
        "\n",
        "In this case, no regular expressions are needed, as the `message` is straightforward: \"Connected to \", followed by the server IP. To match this string, you can use the following `DISSECT` command:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "kwX_AFgeLEvQ"
      },
      "outputs": [],
      "source": [
        "esql_query = \"\"\"\n",
        "FROM sample_data\n",
        "| DISSECT message \"Connected to %{server_ip}\"\n",
        "\"\"\"\n",
        "response = esql.query(query=esql_query)\n",
        "format_response(response)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1llLaCrWLTk_"
      },
      "source": [
        "This adds a `server_ip` column to those rows that have a `message` that matches this pattern. For other rows, the value of `server_ip` is `null`.\n",
        "\n",
        "You can use the new `server_ip` column that’s added by the `DISSECT` command in subsequent commands. For example, to determine how many connections each server has accepted:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "8_uEN-H5LaXH"
      },
      "outputs": [],
      "source": [
        "esql_query = \"\"\"\n",
        "FROM sample_data\n",
        "| WHERE STARTS_WITH(message, \"Connected to\")\n",
        "| DISSECT message \"Connected to %{server_ip}\"\n",
        "| STATS COUNT(*) BY server_ip\n",
        "\"\"\"\n",
        "response = esql.query(query=esql_query)\n",
        "format_response(response)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MtyvTCjyL6aC"
      },
      "source": [
        "> ℹ️ To learn more about data processing with ES|QL, refer to [Data processing with DISSECT and GROK](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-process-data-with-dissect-and-grok.html \"Data processing with DISSECT and GROK\")."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HRE5HF_SLdqY"
      },
      "source": [
        "## Learn more\n",
        "\n",
        "To learn more about ES|QL, refer to:\n",
        "- [_Learning ES|QL_](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-language.html \"Learning ES|QL\")\n",
        "- [_Using ES|QL_](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-using.html \"Using ES|QL\")"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}