notebooks/03-query/17-table-append-operations.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "557e06e8-9b35-4b34-8322-8a8ede6de709",
   "metadata": {},
   "source": [
    "# Using TABLE(APPEND) for UNION operations to address multiple TABLEs in the same query\n",
    "\n",
    "<!--\n",
    "  ~ Licensed to the Apache Software Foundation (ASF) under one\n",
    "  ~ or more contributor license agreements.  See the NOTICE file\n",
    "  ~ distributed with this work for additional information\n",
    "  ~ regarding copyright ownership.  The ASF licenses this file\n",
    "  ~ to you under the Apache License, Version 2.0 (the\n",
    "  ~ \"License\"); you may not use this file except in compliance\n",
    "  ~ with the License.  You may obtain a copy of the License at\n",
    "  ~\n",
    "  ~   http://www.apache.org/licenses/LICENSE-2.0\n",
    "  ~\n",
    "  ~ Unless required by applicable law or agreed to in writing,\n",
    "  ~ software distributed under the License is distributed on an\n",
    "  ~ \"AS IS\" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY\n",
    "  ~ KIND, either express or implied.  See the License for the\n",
    "  ~ specific language governing permissions and limitations\n",
    "  ~ under the License.\n",
    "  -->\n",
    "  \n",
    "While working with Druid, you may need to include rows from different tables in a single result set, or to treat multiple tables as a single input to a query. This notebook introduces the TABLE(APPEND) operator, and shows you how to use it for these purposes."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf4554ae-6516-4e76-b202-d6e2fdf31603",
   "metadata": {},
   "source": [
    "## Prerequisites\n",
    "\n",
    "This tutorial works with Druid 30.0.0 or later.\n",
    "\n",
    "#### Run using Docker\n",
    "\n",
    "Launch this tutorial and all prerequisites using the `druid-jupyter` profile of the Docker Compose file for Jupyter-based Druid tutorials. For more information, see the Learn Druid repository [readme](https://github.com/implydata/learn-druid)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee0c3171-def8-4ad9-9c56-d3a67f309631",
   "metadata": {},
   "source": [
    "## Initialization\n",
    "\n",
    "Run the next cell to attempt a connection to Druid services. If successful, the output shows the Druid version number."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9fa4abfe-f878-4031-88f2-94c13e922279",
   "metadata": {},
   "outputs": [],
   "source": [
    "import druidapi\n",
    "import os\n",
    "\n",
    "if 'DRUID_HOST' not in os.environ.keys():\n",
    "    druid_host=f\"http://localhost:8888\"\n",
    "else:\n",
    "    druid_host=f\"http://{os.environ['DRUID_HOST']}:8888\"\n",
    "    \n",
    "print(f\"Opening a connection to {druid_host}.\")\n",
    "druid = druidapi.jupyter_client(druid_host)\n",
    "\n",
    "display = druid.display\n",
    "sql_client = druid.sql\n",
    "status_client = druid.status\n",
    "\n",
    "status_client.version"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc3001a0-27e5-4f41-876a-ce6eab2acd6a",
   "metadata": {},
   "source": [
    "Finally, run the following cell to import the Python JSON module."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6b058d8b-2bae-4929-ab0c-5a6df1850387",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f388633f-195b-4381-98cc-7a2f80f48690",
   "metadata": {},
   "source": [
    "## Using TABLE(APPEND) to concatenate tables\n",
    "\n",
    "As the name suggests, TABLE(APPEND) appends the contents of one table to the end of another. TABLE(APPEND) respects the schemas of the incoming result sets automatically. This makes working with TABLE(APPEND) simpler than using the [UNION](https://druid.apache.org/docs/26.0.0/querying/datasource.html#union) operator which requires you to explicitly specify the schemas for the tables.\n",
    "\n",
    "When schemas differ, TABLE(APPEND) returns NULL columns that do not exist in one table, but do in the other. Druid automatically handles the data types for both tables. When columns of the same name have different data types in different tables, Druid adjusts the data type to be the most permissive to accommodate  all the data in the result set.\n",
    "\n",
    "Run the following cell to prepare the SQL to ingest all the edits made by bots as a new datasource. Notice the static field `tableName` which will be used later to determine which table a given record has been queried from after appending."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9e44ca3e",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql='''\n",
    "REPLACE INTO \"example-wikipedia-table-append1\" OVERWRITE ALL\n",
    "WITH \"ext\" AS (SELECT *\n",
    "FROM TABLE(\n",
    "  EXTERN(\n",
    "    '{\"type\":\"http\",\"uris\":[\"https://druid.apache.org/data/wikipedia.json.gz\"]}',\n",
    "    '{\"type\":\"json\"}'\n",
    "  )\n",
    ") EXTEND (\"isRobot\" VARCHAR, \"channel\" VARCHAR, \"timestamp\" VARCHAR, \"flags\" VARCHAR, \"isUnpatrolled\" VARCHAR, \"page\" VARCHAR, \"diffUrl\" VARCHAR, \"added\" BIGINT, \"comment\" VARCHAR, \"commentLength\" BIGINT, \"isNew\" VARCHAR, \"isMinor\" VARCHAR, \"delta\" BIGINT, \"isAnonymous\" VARCHAR, \"user\" VARCHAR, \"deltaBucket\" BIGINT, \"deleted\" BIGINT, \"namespace\" VARCHAR, \"cityName\" VARCHAR, \"countryName\" VARCHAR, \"regionIsoCode\" VARCHAR, \"metroCode\" BIGINT, \"countryIsoCode\" VARCHAR, \"regionName\" VARCHAR))\n",
    "SELECT\n",
    "  TIME_PARSE(\"timestamp\") AS \"__time\",\n",
    "  'table1' AS tableName,\n",
    "  \"isRobot\",\n",
    "  \"channel\",\n",
    "  \"page\",\n",
    "  \"commentLength\",\n",
    "  \"countryName\",\n",
    "  \"user\"\n",
    "FROM \"ext\"\n",
    "WHERE \"isRobot\"=TRUE\n",
    "PARTITIONED BY DAY\n",
    "'''"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "272632fc",
   "metadata": {},
   "source": [
    "Run the following to run the ingestion and, once the ingestion is complete, query the new datasource."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "baa4f1e7-8338-487e-a1f0-101ab1c46eec",
   "metadata": {},
   "outputs": [],
   "source": [
    "display.run_task(sql)\n",
    "sql_client.wait_until_ready('example-wikipedia-table-append1')\n",
    "\n",
    "sql = '''\n",
    "SELECT *\n",
    "FROM \"example-wikipedia-table-append1\"\n",
    "WHERE TIME_IN_INTERVAL(__time, '2016-06-27/2016-06-28')\n",
    "ORDER BY __time LIMIT 5\n",
    "'''\n",
    "\n",
    "display.sql(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "895bf34b-c703-406c-8c74-71e98cc326b3",
   "metadata": {},
   "source": [
    "Run the next cell to ingest another datasource, this time with all the edits made by people rather than bots and then query it.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9e6b6154",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql='''\n",
    "REPLACE INTO \"example-wikipedia-table-append2\" OVERWRITE ALL\n",
    "WITH \"ext\" AS (SELECT *\n",
    "FROM TABLE(\n",
    "  EXTERN(\n",
    "    '{\"type\":\"http\",\"uris\":[\"https://druid.apache.org/data/wikipedia.json.gz\"]}',\n",
    "    '{\"type\":\"json\"}'\n",
    "  )\n",
    ") EXTEND (\"isRobot\" VARCHAR, \"channel\" VARCHAR, \"timestamp\" VARCHAR, \"flags\" VARCHAR, \"isUnpatrolled\" VARCHAR, \"page\" VARCHAR, \"diffUrl\" VARCHAR, \"added\" BIGINT, \"comment\" VARCHAR, \"commentLength\" BIGINT, \"isNew\" VARCHAR, \"isMinor\" VARCHAR, \"delta\" BIGINT, \"isAnonymous\" VARCHAR, \"user\" VARCHAR, \"deltaBucket\" BIGINT, \"deleted\" BIGINT, \"namespace\" VARCHAR, \"cityName\" VARCHAR, \"countryName\" VARCHAR, \"regionIsoCode\" VARCHAR, \"metroCode\" BIGINT, \"countryIsoCode\" VARCHAR, \"regionName\" VARCHAR))\n",
    "SELECT\n",
    "  TIME_PARSE(\"timestamp\") AS \"__time\",\n",
    "  'table2' AS tableName,\n",
    "  \"isRobot\",\n",
    "  \"channel\",\n",
    "  \"page\",\n",
    "  \"commentLength\",\n",
    "  \"countryName\",\n",
    "  \"user\"\n",
    "FROM \"ext\"\n",
    "WHERE \"isRobot\"=FALSE\n",
    "PARTITIONED BY DAY\n",
    "'''\n",
    "\n",
    "display.run_task(sql)\n",
    "sql_client.wait_until_ready('example-wikipedia-table-append2')\n",
    "\n",
    "sql = '''\n",
    "SELECT *\n",
    "FROM \"example-wikipedia-table-append2\"\n",
    "WHERE TIME_IN_INTERVAL(__time, '2016-06-27/2016-06-28')\n",
    "ORDER BY __time LIMIT 5\n",
    "'''\n",
    "\n",
    "display.sql(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24b47cc3-68f5-4a73-b374-94bbfa32d91d",
   "metadata": {},
   "source": [
    "You can now use TABLE(APPEND) to append results from one example table to results from the other.\n",
    "\n",
    "The first query in the following cell returns the first ten edits from both the example tables. The `tableName` column indicates the source table for each record."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b76e5184-9fe4-4f21-a471-4e15d16515c8",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = '''\n",
    "SELECT *\n",
    "FROM TABLE(APPEND('example-wikipedia-table-append1','example-wikipedia-table-append2'))\n",
    "WHERE TIME_IN_INTERVAL(__time, '2016-06-27/2016-06-28')\n",
    "ORDER BY __time LIMIT 10\n",
    "'''\n",
    "\n",
    "display.sql(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5e77fa9-a60c-4955-b763-58d970d7326d",
   "metadata": {},
   "source": [
    "Optionally, run the next cell to show the precise [EXPLAIN PLAN](https://druid.apache.org/docs/latest/querying/sql-translation#interpreting-explain-plan-output) for the query. There is one `query` execution plan in addition to Druid's optimized execution planning process of the outer query. For UNION operations, there would be two query plans. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97934da2-17d1-4c91-8ae3-926cc89185c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(json.dumps(json.loads(sql_client.explain_sql(sql)['PLAN']), indent=2))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f23a2e8-8a31-4ae0-8c95-fa071de6afe8",
   "metadata": {},
   "source": [
    "Run the below to get the total number of edits made by humans and the total number of edits made by bots. Without TABLE(APPEND) you would have to use GROUP BY AGAINST both tables and JOIN the results\n",
    "but with TABLE(APPEND) you can use a single GROUP BY query to get the total number of each type of edit."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a4537807-3a44-4611-9f6c-b41b2ab2a227",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = '''\n",
    "SELECT \n",
    "    \"isRobot\",\n",
    "    \"tableName\",\n",
    "    COUNT(\"tableName\") AS \"edits\"\n",
    "FROM TABLE(APPEND('example-wikipedia-table-append1','example-wikipedia-table-append2'))\n",
    "WHERE TIME_IN_INTERVAL(__time, '2016-06-27/2016-06-28')\n",
    "GROUP BY 1, 2\n",
    "ORDER BY 2 DESC\n",
    "'''\n",
    "\n",
    "display.sql(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b1da682-feb1-4833-a32a-3dfa9d3ea16b",
   "metadata": {},
   "source": [
    "## Handling disparate column names for the same data\n",
    "\n",
    "Use TABLE(APPEND) can be used to append one table to another where columns have different names but the same data i.e. \"countryName\" and \"Country\" for the country of origin of an edit.\n",
    "\n",
    "Run the following cell to ingest the wikipedia data example again as two new tables, now with the country of origin under a separate column name in each."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "949636f1-7dee-4c25-8f66-47dcb7b33def",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql='''\n",
    "REPLACE INTO \"example-wikipedia-table-append-country\" OVERWRITE ALL\n",
    "WITH \"ext\" AS (SELECT *\n",
    "FROM TABLE(\n",
    "  EXTERN(\n",
    "    '{\"type\":\"http\",\"uris\":[\"https://druid.apache.org/data/wikipedia.json.gz\"]}',\n",
    "    '{\"type\":\"json\"}'\n",
    "  )\n",
    ") EXTEND (\"isRobot\" VARCHAR, \"channel\" VARCHAR, \"timestamp\" VARCHAR, \"flags\" VARCHAR, \"isUnpatrolled\" VARCHAR, \"page\" VARCHAR, \"diffUrl\" VARCHAR, \"added\" BIGINT, \"comment\" VARCHAR, \"commentLength\" BIGINT, \"isNew\" VARCHAR, \"isMinor\" VARCHAR, \"delta\" BIGINT, \"isAnonymous\" VARCHAR, \"user\" VARCHAR, \"deltaBucket\" BIGINT, \"deleted\" BIGINT, \"namespace\" VARCHAR, \"cityName\" VARCHAR, \"countryName\" VARCHAR, \"regionIsoCode\" VARCHAR, \"metroCode\" BIGINT, \"countryIsoCode\" VARCHAR, \"regionName\" VARCHAR))\n",
    "SELECT\n",
    "  TIME_PARSE(\"timestamp\") AS \"__time\",\n",
    "  'country' AS tableName,\n",
    "  \"isRobot\",\n",
    "  \"channel\",\n",
    "  \"page\",\n",
    "  \"commentLength\",\n",
    "  \"countryName\" AS \"country\",\n",
    "  \"user\"\n",
    "FROM \"ext\"\n",
    "WHERE \"isRobot\"=TRUE\n",
    "PARTITIONED BY DAY\n",
    "'''\n",
    "\n",
    "display.run_task(sql)\n",
    "sql_client.wait_until_ready('example-wikipedia-table-append-country')\n",
    "display.table('example-wikipedia-table-append-country')\n",
    "\n",
    "sql='''\n",
    "REPLACE INTO \"example-wikipedia-table-append-country-name\" OVERWRITE ALL\n",
    "WITH \"ext\" AS (SELECT *\n",
    "FROM TABLE(\n",
    "  EXTERN(\n",
    "    '{\"type\":\"http\",\"uris\":[\"https://druid.apache.org/data/wikipedia.json.gz\"]}',\n",
    "    '{\"type\":\"json\"}'\n",
    "  )\n",
    ") EXTEND (\"isRobot\" VARCHAR, \"channel\" VARCHAR, \"timestamp\" VARCHAR, \"flags\" VARCHAR, \"isUnpatrolled\" VARCHAR, \"page\" VARCHAR, \"diffUrl\" VARCHAR, \"added\" BIGINT, \"comment\" VARCHAR, \"commentLength\" BIGINT, \"isNew\" VARCHAR, \"isMinor\" VARCHAR, \"delta\" BIGINT, \"isAnonymous\" VARCHAR, \"user\" VARCHAR, \"deltaBucket\" BIGINT, \"deleted\" BIGINT, \"namespace\" VARCHAR, \"cityName\" VARCHAR, \"countryName\" VARCHAR, \"regionIsoCode\" VARCHAR, \"metroCode\" BIGINT, \"countryIsoCode\" VARCHAR, \"regionName\" VARCHAR))\n",
    "SELECT\n",
    "  TIME_PARSE(\"timestamp\") AS \"__time\",\n",
    "  'countryName' AS tableName,\n",
    "  \"isRobot\",\n",
    "  \"channel\",\n",
    "  \"page\",\n",
    "  \"commentLength\",\n",
    "  \"countryName\",\n",
    "  \"user\"\n",
    "FROM \"ext\"\n",
    "WHERE \"isRobot\"=FALSE\n",
    "PARTITIONED BY DAY\n",
    "'''\n",
    "\n",
    "display.run_task(sql)\n",
    "sql_client.wait_until_ready('example-wikipedia-table-append-country-name')\n",
    "display.table('example-wikipedia-table-append-country-name')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "451a5d68-2f5f-4b17-b461-0405db2f0b20",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = '''\n",
    "SELECT\n",
    "    *\n",
    "FROM TABLE(APPEND('example-wikipedia-table-append-country','example-wikipedia-table-append-country-name'))\n",
    "WHERE \"countryName\" IS NOT NULL AND TIME_IN_INTERVAL(__time, '2016-06-27/2016-06-28')\n",
    "ORDER BY __time LIMIT 10\n",
    "'''\n",
    "\n",
    "display.sql(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57bef2cf-c12e-4133-9f9e-7d13ca8addd4",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "Run the cell below to use [COALESCE](https://druid.apache.org/docs/latest/querying/math-expr/#:~:text=still%20be%20null\\).-,coalesce,-coalesce(exprs)%20returns) to combine the two columns with different names into a single column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8403d81a-7d78-428f-bb13-a8215edb8a4d",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = '''\n",
    "SELECT\n",
    "    \"tableName\",\n",
    "    COALESCE(\"countryName\",\"country\") AS \"countryName\"\n",
    "FROM TABLE(APPEND('example-wikipedia-table-append-country','example-wikipedia-table-append-country-name'))\n",
    "WHERE \"countryName\" IS NOT NULL AND TIME_IN_INTERVAL(__time, '2016-06-27/2016-06-28')\n",
    "ORDER BY __time LIMIT 10\n",
    "'''\n",
    "\n",
    "display.sql(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d92e1fe6-5c43-4cba-9877-0ba039ff549d",
   "metadata": {},
   "source": [
    "## Clean up\n",
    "\n",
    "Run the following cell to remove tables created for this exercise."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "484c6e73-98bc-4457-b7b2-9a0d9c06b82a",
   "metadata": {},
   "outputs": [],
   "source": [
    "druid.datasources.drop(\"example-wikipedia-table-append1\")\n",
    "druid.datasources.drop(\"example-wikipedia-table-append2\")\n",
    "druid.datasources.drop(\"example-wikipedia-table-append-country\")\n",
    "druid.datasources.drop(\"example-wikipedia-table-append-country-name\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f58a1846-5072-4495-b840-a620de3c0442",
   "metadata": {},
   "source": [
    "## Conclusion\n",
    "\n",
    "* `TABLE(APPEND)` is an alternative to `UNION` in Druid for joining the contents of tables\n",
    "* `UNION` creates two queries in native JSON whereas 'TABLE(APPEND)` only creates one\n",
    "* `TABLE(APPEND)` automatically respects incoming schemas whereas for a `UNION` the schema of the joined table must be manually specified\n",
    "\n",
    "## Learn more\n",
    "* Read about [union](https://druid.apache.org/docs/26.0.0/querying/datasource.html#union) datasources in the documentation"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}