esql_811_test.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyNZRY6uim7ShbW2frPXXqk7",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/leemthompo/notebook-tests/blob/main/esql_811_test.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# ES|QL with Python client\n",
        "\n",
        "Uses Cloud 8.11.0 SNAPSHOT deployment."
      ],
      "metadata": {
        "id": "vMRlZKgyNfUt"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Install client, import libraries"
      ],
      "metadata": {
        "id": "sHJgdpzPafgJ"
      }
    },
    {
      "cell_type": "code",
      "execution_count": 38,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "XGsCXq6lW8q1",
        "outputId": "7251d1e9-8254-45c9-a27e-f6136ba2961b"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Requirement already satisfied: elasticsearch in /usr/local/lib/python3.10/dist-packages (8.10.1)\n",
            "Requirement already satisfied: elastic-transport<9,>=8 in /usr/local/lib/python3.10/dist-packages (from elasticsearch) (8.4.1)\n",
            "Requirement already satisfied: urllib3<2,>=1.26.2 in /usr/local/lib/python3.10/dist-packages (from elastic-transport<9,>=8->elasticsearch) (1.26.18)\n",
            "Requirement already satisfied: certifi in /usr/local/lib/python3.10/dist-packages (from elastic-transport<9,>=8->elasticsearch) (2023.7.22)\n"
          ]
        }
      ],
      "source": [
        "!pip install elasticsearch\n",
        "from elasticsearch import Elasticsearch\n",
        "import getpass"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Create client instance\n"
      ],
      "metadata": {
        "id": "gUSOsWgmNqjc"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "CLOUD_ID = getpass.getpass(prompt=\"Enter CLOUD ID:\")\n",
        "API_KEY= getpass.getpass(prompt=\"Enter API key\")\n",
        "\n",
        "\n",
        "client = Elasticsearch(\n",
        "  CLOUD_ID,\n",
        "  api_key=API_KEY\n",
        ")\n",
        "\n",
        "client.info()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "rW8NKg8L84ds",
        "outputId": "a892d7fc-558a-43d6-8d6e-31a695091299"
      },
      "execution_count": 40,
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Enter CLOUD ID:··········\n",
            "Enter password··········\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "ObjectApiResponse({'name': 'instance-0000000000', 'cluster_name': '3eec50b2ab254ac1b027c6f0be6bad16', 'cluster_uuid': '4HPZ8CJvRm6FQ2RwAFo_Cw', 'version': {'number': '8.11.0-SNAPSHOT', 'build_flavor': 'default', 'build_type': 'docker', 'build_hash': '454cd35d33aafc161b4f7238d63777e71814d834', 'build_date': '2023-10-16T15:48:25.683294483Z', 'build_snapshot': True, 'lucene_version': '9.8.0', 'minimum_wire_compatibility_version': '7.17.0', 'minimum_index_compatibility_version': '7.0.0'}, 'tagline': 'You Know, for Search'})"
            ]
          },
          "metadata": {},
          "execution_count": 40
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Create sample index"
      ],
      "metadata": {
        "id": "pj_fiPtYNyuG"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Create the 'library' index (assuming default settings and mappings)\n",
        "if not client.indices.exists(index='library'):\n",
        "    client.indices.create(index='library')\n",
        "\n",
        "# Data to be indexed\n",
        "books = [\n",
        "    {\n",
        "        \"author\": \"Peter F. Hamilton\",\n",
        "        \"name\": \"Pandora's Star\",\n",
        "        \"page_count\": 768,\n",
        "        \"release_date\": \"2004-03-02T00:00:00.000Z\"\n",
        "    },\n",
        "    {\n",
        "        \"author\": \"Vernor Vinge\",\n",
        "        \"name\": \"A Fire Upon the Deep\",\n",
        "        \"page_count\": 613,\n",
        "        \"release_date\": \"1992-06-01T00:00:00.000Z\"\n",
        "    },\n",
        "    {\n",
        "        \"author\": \"Frank Herbert\",\n",
        "        \"name\": \"Dune\",\n",
        "        \"page_count\": 604,\n",
        "        \"release_date\": \"1965-06-01T00:00:00.000Z\"\n",
        "    },\n",
        "    {\n",
        "        \"author\": \"Alastair Reynolds\",\n",
        "        \"name\": \"Revelation Space\",\n",
        "        \"page_count\": 585,\n",
        "        \"release_date\": \"2000-03-15T00:00:00.000Z\"\n",
        "    },\n",
        "    {\n",
        "        \"author\": \"James S.A. Corey\",\n",
        "        \"name\": \"Leviathan Wakes\",\n",
        "        \"page_count\": 561,\n",
        "        \"release_date\": \"2011-06-02T00:00:00.000Z\"\n",
        "    }\n",
        "]\n",
        "\n",
        "# Index the data\n",
        "for book in books:\n",
        "    client.index(index='library', body=book)\n",
        "\n",
        "print(\"Data indexed successfully!\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "aTfAPusyF2zC",
        "outputId": "60c92ca9-8a5c-45fe-fc34-2a3cad599744"
      },
      "execution_count": 42,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Data indexed successfully!\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Test Query 1"
      ],
      "metadata": {
        "id": "lyrzCo3bN4s4"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Your query\n",
        "data = {\n",
        "    \"query\": \"FROM library | KEEP author, name, page_count, release_date | SORT page_count DESC | LIMIT 5\"\n",
        "}\n",
        "\n",
        "headers = {\"Content-Type\": \"application/json\", \"Accept\": \"application/json\"}\n",
        "\n",
        "# Execute the query\n",
        "response = client.perform_request('POST', '/_query?format=txt', body=data, headers=headers)\n",
        "\n",
        "print(response.body)\n",
        "\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "tu2Dy7o9_EOI",
        "outputId": "45fdc0bc-e944-4638-a1cd-9ff96168d83c"
      },
      "execution_count": 48,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "     author      |        name        |  page_count   |      release_date      \n",
            "-----------------+--------------------+---------------+------------------------\n",
            "Peter F. Hamilton|Pandora's Star      |768            |2004-03-02T00:00:00.000Z\n",
            "Peter F. Hamilton|Pandora's Star      |768            |2004-03-02T00:00:00.000Z\n",
            "Peter F. Hamilton|Pandora's Star      |768            |2004-03-02T00:00:00.000Z\n",
            "Vernor Vinge     |A Fire Upon the Deep|613            |1992-06-01T00:00:00.000Z\n",
            "Vernor Vinge     |A Fire Upon the Deep|613            |1992-06-01T00:00:00.000Z\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "Kn-8Bgu1N_MX"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Test query 2"
      ],
      "metadata": {
        "id": "IUdtN1upN3ua"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Your query\n",
        "data = {\n",
        "    \"query\": \"\"\"\n",
        "        FROM library\n",
        "        | EVAL year = DATE_TRUNC(1 YEARS, release_date)\n",
        "        | STATS MAX(page_count) BY year\n",
        "        | SORT year\n",
        "        | LIMIT 5\n",
        "    \"\"\"\n",
        "}\n",
        "\n",
        "\n",
        "headers = {\"Content-Type\": \"application/json\", \"Accept\": \"application/json\"}\n",
        "\n",
        "\n",
        "# Execute the query\n",
        "response = client.perform_request('POST', '/_query?format=txt', body=data, headers=headers)\n",
        "\n",
        "print(response.body)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "527qIKVj9Mbj",
        "outputId": "c7c30766-531a-4be0-ebf5-c67392a73642"
      },
      "execution_count": 45,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['MAX(page_count)|          year          ',\n",
              " '---------------+------------------------',\n",
              " '604            |1965-01-01T00:00:00.000Z',\n",
              " '613            |1992-01-01T00:00:00.000Z',\n",
              " '585            |2000-01-01T00:00:00.000Z',\n",
              " '768            |2004-01-01T00:00:00.000Z',\n",
              " '561            |2011-01-01T00:00:00.000Z']"
            ]
          },
          "metadata": {},
          "execution_count": 45
        }
      ]
    }
  ]
}