You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@druid.apache.org by "writer-jill (via GitHub)" <gi...@apache.org> on 2023/01/27 17:52:35 UTC
[GitHub] [druid] writer-jill opened a new pull request, #13718: Nested columns tutorial
writer-jill opened a new pull request, #13718:
URL: https://github.com/apache/druid/pull/13718
Jupyter notebook tutorial on working with nested columns
This PR has:
- [x] been self-reviewed.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] techdocsmith commented on pull request #13718: Nested columns tutorial
Posted by "techdocsmith (via GitHub)" <gi...@apache.org>.
techdocsmith commented on PR #13718:
URL: https://github.com/apache/druid/pull/13718#issuecomment-1555090548
@writer-jill we can use [the Kafka notebook](https://github.com/apache/druid/blob/master/examples/quickstart/jupyter-notebooks/kafka-tutorial.ipynb) as a template to get this one fixed & into the docs
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] writer-jill commented on a diff in pull request #13718: Nested columns tutorial
Posted by "writer-jill (via GitHub)" <gi...@apache.org>.
writer-jill commented on code in PR #13718:
URL: https://github.com/apache/druid/pull/13718#discussion_r1090533694
##########
examples/quickstart/jupyter-notebooks/nested-columns-tutorial.ipynb:
##########
@@ -0,0 +1,517 @@
+{
+ "cells": [
+ {
+ "cell_type": "markdown",
+ "id": "ad4e60b6",
+ "metadata": {
+ "tags": []
+ },
+ "source": [
+ "# Working with nested columns\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",
+ "This tutorial demonstrates how to work with [nested columns](https://druid.apache.org/docs/latest/querying/nested-columns.html) in Apache Druid.\n",
+ "\n",
+ "Druid stores nested data structures in `COMPLEX<json>` columns. In this tutorial you perform the following tasks:\n",
+ "\n",
+ "- Ingest nested JSON data using SQL-based ingestion.\n",
+ "- Transform nested data during ingestion using SQL JSON functions.\n",
+ "- Perform queries to display, filter, and aggregate nested data.\n",
+ "\n",
+ "Druid supports directly ingesting nested data with the following formats: JSON, Parquet, Avro, ORC, Protobuf.\n",
+ "\n",
+ "\n",
+ "## Table of contents\n",
+ "\n",
+ "- [Prerequisites](#Prerequisites)\n",
+ "- [Ingest data](#Ingest-data)\n",
+ "- [Transform data](#Transform-data)\n",
+ "- [Query nested data](#Query-nested-data)\n",
+ "- [Learn more](#Learn-more)\n",
+ "\n",
+ "For the best experience, use Jupyter Lab so that you can always access the table of contents."
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "e4638f98",
+ "metadata": {},
+ "source": [
+ "## Prerequisites\n",
+ "\n",
+ "You'll need install the Requests library for Python before you start. For example:\n",
+ "\n",
+ "```bash\n",
+ "pip3 install requests\n",
+ "```\n",
+ "\n",
+ "Next, you'll need a Druid cluster. This tutorial uses the `micro-quickstart` config described in the [Druid quickstart](https://druid.apache.org/docs/latest/tutorials/index.html). So download that and start it if you haven't already. In the root of the Druid folder, run the following command to start Druid:\n",
+ "\n",
+ "```bash\n",
+ "./bin/start-druid\n",
+ "```\n",
+ "\n",
+ "Finally, you'll need either Jupyter lab (recommended) or Jupyter notebook. Both the quickstart Druid cluster and Jupyter notebook are deployed at `localhost:8888` by default, so you'll need to change the port for Jupyter. To do so, stop Jupyter and start it again with the `port` parameter included. For example, you can use the following command to start Jupyter on port `3001`:\n",
+ "\n",
+ "```bash\n",
+ "# If you're using Jupyter lab\n",
+ "jupyter lab --port 3001\n",
+ "# If you're using Jupyter notebook\n",
+ "jupyter notebook --port 3001 \n",
+ "```\n",
+ "\n",
+ "To start this tutorial, run the next cell. It imports the Python packages you'll need and defines a variable for the the Druid host the tutorial uses. The quickstart deployment configures Druid to listen on port `8888` by default, so you'll be making API calls against `http://localhost:8888`."
Review Comment:
Updated.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] writer-jill commented on a diff in pull request #13718: Nested columns tutorial
Posted by "writer-jill (via GitHub)" <gi...@apache.org>.
writer-jill commented on code in PR #13718:
URL: https://github.com/apache/druid/pull/13718#discussion_r1093187233
##########
examples/quickstart/jupyter-notebooks/nested-columns-tutorial.ipynb:
##########
@@ -57,28 +57,30 @@
"source": [
"## Prerequisites\n",
"\n",
- "You'll need install the Requests library for Python before you start. For example:\n",
+ "You need to install the requests library for Python before you start—for example:\n",
Review Comment:
Updated
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] Nested columns tutorial (druid)
Posted by "writer-jill (via GitHub)" <gi...@apache.org>.
writer-jill commented on PR #13718:
URL: https://github.com/apache/druid/pull/13718#issuecomment-1621603830
Stale PR - closing & will open a new one for this tutorial.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] 317brian commented on a diff in pull request #13718: Nested columns tutorial
Posted by "317brian (via GitHub)" <gi...@apache.org>.
317brian commented on code in PR #13718:
URL: https://github.com/apache/druid/pull/13718#discussion_r1092398018
##########
examples/quickstart/jupyter-notebooks/nested-columns-tutorial.ipynb:
##########
@@ -0,0 +1,556 @@
+{
+ "cells": [
+ {
+ "cell_type": "markdown",
+ "id": "ad4e60b6",
+ "metadata": {
+ "tags": []
+ },
+ "source": [
+ "# Working with nested columns\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",
+ "This tutorial demonstrates how to work with [nested columns](https://druid.apache.org/docs/latest/querying/nested-columns.html) in Apache Druid.\n",
+ "\n",
+ "Druid stores nested data structures in `COMPLEX<json>` columns. In this tutorial, you perform the following tasks:\n",
+ "\n",
+ "- Ingest nested JSON data using SQL-based ingestion.\n",
+ "- Transform nested data during ingestion using SQL JSON functions.\n",
+ "- Perform queries to display, filter, and aggregate nested data.\n",
+ "\n",
+ "Druid supports directly ingesting nested data with the following formats: JSON, Parquet, Avro, ORC, Protobuf.\n",
+ "\n",
+ "\n",
+ "## Table of contents\n",
+ "\n",
+ "- [Prerequisites](#Prerequisites)\n",
+ "- [Ingest nested data](#Ingest-nested-data)\n",
+ "- [Transform nested data](#Transform-nested-data)\n",
+ "- [Query nested data](#Query-nested-data)\n",
+ "- [Learn more](#Learn-more)\n",
+ "\n",
+ "For the best experience, use JupyterLab so that you can always access the table of contents."
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "e4638f98",
+ "metadata": {},
+ "source": [
+ "## Prerequisites\n",
+ "\n",
+ "You need to install the requests library for Python before you start—for example:\n",
+ "\n",
+ "```bash\n",
+ "pip3 install requests\n",
+ "```\n",
+ "\n",
+ "Next, you need a Druid cluster. This tutorial uses the configuration described in the Druid [Quickstart (local)](https://druid.apache.org/docs/latest/tutorials/index.html). Download Druid from the quickstart page. In the root of the Druid folder, run the following command to start Druid:\n",
+ "\n",
+ "```bash\n",
+ "./bin/start-druid\n",
+ "```\n",
+ "\n",
+ "Finally, you need either JupyterLab (recommended) or Jupyter Notebook. Visit the [Jupyter site](https://jupyter.org/) if you want to learn more about these interfaces.\n",
+ "\n",
+ "Both the quickstart Druid cluster and Jupyter deploy at `localhost:8888` by default, so you need to change the port for Jupyter. To do this, stop Jupyter if it's running and start it with the `port` parameter included. For example, you can use the following command to start Jupyter on port `3001`:\n",
+ "\n",
+ "```bash\n",
+ "# If you're using JupyterLab\n",
+ "jupyter lab --port 3001\n",
+ "# If you're using Jupyter Notebook\n",
+ "jupyter notebook --port 3001 \n",
+ "```\n",
+ "\n",
+ "To start this tutorial, run the next cell. It imports the Python packages you need and defines variables for two datasources and the Druid host the tutorial uses. The quickstart deployment configures Druid to listen on port `8888` by default, so you'll make API calls against `http://localhost:8888`."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "b7f08a52",
+ "metadata": {
+ "tags": []
+ },
+ "outputs": [],
+ "source": [
+ "import requests\n",
+ "import json\n",
+ "\n",
+ "# druid_host is the hostname and port for your Druid deployment. \n",
+ "# In a distributed environment, use the Router service as the `druid_host`. \n",
+ "druid_host = \"http://localhost:8888\"\n",
+ "dataSource1 = \"kttm\"\n",
+ "dataSource2 = \"kttm_transform\"\n",
+ "print(f\"\\033[1mDruid host\\033[0m: {druid_host}\")"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "e893ef7d-7136-442f-8bd9-31b5a5276518",
+ "metadata": {},
+ "source": [
+ "In the rest of the tutorial, the `endpoint`, `http_method`, and `payload` variables are updated to accomplish different tasks."
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "ebd8c7db-c39f-4ef7-86ec-81f405e02550",
+ "metadata": {},
+ "source": [
+ "## Ingest nested data\n",
+ "\n",
+ "Run the following cell to ingest sample clickstream data from the [Koalas to the Max](https://www.koalastothemax.com/) game."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "75a177f1",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "endpoint = \"/druid/v2/sql/task\"\n",
+ "print(druid_host+endpoint)\n",
+ "http_method = \"POST\"\n",
+ "\n",
+ "payload = json.dumps({\n",
+ "\"query\": \"INSERT INTO \\\"kttm\\\" \\\n",
+ " WITH \\\"source\\\" AS \\\n",
+ " (SELECT * FROM TABLE(EXTERN('{\\\"type\\\":\\\"http\\\",\\\"uris\\\":[\\\"https://static.imply.io/example-data/kttm-nested-v2/kttm-nested-v2-2019-08-25.json.gz\\\"]}', \\\n",
+ " '{\\\"type\\\":\\\"json\\\"}','[{\\\"name\\\":\\\"timestamp\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"client_ip\\\",\\\"type\\\":\\\"string\\\"}, \\\n",
+ " {\\\"name\\\":\\\"session\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"session_length\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"event\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"}, \\\n",
+ " {\\\"name\\\":\\\"agent\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"},{\\\"name\\\":\\\"geo_ip\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"}]'))) \\\n",
+ " SELECT TIME_PARSE(\\\"timestamp\\\") AS \\\"__time\\\", \\\"client_ip\\\", \\\"session\\\", \\\"session_length\\\", \\\"event\\\", \\\"agent\\\", \\\"geo_ip\\\"FROM \\\"source\\\" \\\n",
+ " PARTITIONED BY DAY\"\n",
+ "})\n",
+ " \n",
+ "headers = {'Content-Type': 'application/json'}\n",
+ "\n",
+ "response = requests.request(http_method, druid_host+endpoint, headers=headers, data=payload)\n",
+ "ingestion_taskId_response = response\n",
+ "ingestion_taskId = json.loads(ingestion_taskId_response.text)['taskId']\n",
+ "\n",
+ "print(f\"\\nInserting data into the table named {dataSource1}.\")\n",
+ "print(\"\\nThe response includes the task ID and the status: \" + response.text + \".\")"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "17c19a74",
+ "metadata": {},
+ "source": [
+ "Run the following cell to get the status of the ingestion task."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "eeb78251",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "import time\n",
+ "\n",
+ "endpoint = f\"/druid/indexer/v1/task/{ingestion_taskId}/status\"\n",
+ "print(f\"\\033[1mQuery endpoint\\033[0m: {druid_host+endpoint}\")\n",
+ "http_method = \"GET\"\n",
+ "\n",
+ "payload = {}\n",
+ "headers = {}\n",
+ "\n",
+ "response = requests.request(http_method, druid_host+endpoint, headers=headers, data=payload)\n",
+ "ingestion_status = json.loads(response.text)['status']['status']\n",
+ "\n",
+ "if ingestion_status == \"RUNNING\":\n",
+ " print(\"The ingestion is running...\")\n",
+ "\n",
+ "while ingestion_status != \"SUCCESS\":\n",
+ " response = requests.request(http_method, druid_host+endpoint, headers=headers, data=payload)\n",
+ " ingestion_status = json.loads(response.text)['status']['status']\n",
+ " time.sleep(15) \n",
+ " \n",
+ "if ingestion_status == \"SUCCESS\": \n",
+ " print(\"The ingestion is complete:\")\n",
+ " print(json.dumps(response.json(), indent=4))"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "9a2284f3",
+ "metadata": {},
+ "source": [
+ "When the ingestion task status shows `SUCCESS`, run the following cell to query the data and return selected columns from 3 rows. Note the nested structure of the `event`, `agent`, and `geo_ip` columns."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "e801999d",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "endpoint = \"/druid/v2/sql\"\n",
+ "print(druid_host+endpoint)\n",
+ "http_method = \"POST\"\n",
+ "\n",
+ "payload = json.dumps({\n",
+ " \"query\": \"SELECT session, event, agent, geo_ip FROM kttm LIMIT 3\"\n",
+ "})\n",
+ "headers = {'Content-Type': 'application/json'}\n",
+ "\n",
+ "response = requests.request(http_method, druid_host+endpoint, headers=headers, data=payload)\n",
+ "\n",
+ "print(json.dumps(json.loads(response.text), indent=4))"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "240b0ad5-48f2-4737-b12b-5fd5f98da300",
+ "metadata": {},
+ "source": [
+ "## Transform nested data\n",
+ "\n",
+ "You can use Druid's [SQL JSON functions](https://druid.apache.org/docs/latest/querying/sql-json-functions.html) to transform nested data in your ingestion query.\n",
+ "\n",
+ "Run the following cell to insert sample data into a new datasource named `kttm_transform`. The SELECT query extracts the `country` and `city` elements from the nested `geo_ip` column and creates a composite object `sessionDetails` containing `session` and `session_length`."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "a1f21fa8",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "endpoint = \"/druid/v2/sql/task\"\n",
+ "print(druid_host+endpoint)\n",
+ "http_method = \"POST\"\n",
+ "\n",
+ "payload = json.dumps({\n",
+ "\"query\": \"INSERT INTO \\\"kttm_transform\\\" \\\n",
+ " WITH \\\"source\\\" AS \\\n",
+ " (SELECT * FROM TABLE(EXTERN('{\\\"type\\\":\\\"http\\\",\\\"uris\\\":[\\\"https://static.imply.io/example-data/kttm-nested-v2/kttm-nested-v2-2019-08-25.json.gz\\\"]}', \\\n",
+ " '{\\\"type\\\":\\\"json\\\"}','[{\\\"name\\\":\\\"timestamp\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"session\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"session_length\\\",\\\"type\\\":\\\"string\\\"}, \\\n",
+ " {\\\"name\\\":\\\"event\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"},{\\\"name\\\":\\\"agent\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"},{\\\"name\\\":\\\"geo_ip\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"}]'))) \\\n",
+ " SELECT TIME_PARSE(\\\"timestamp\\\") AS \\\"__time\\\", \\\n",
+ " JSON_QUERY(geo_ip, '$.country') as country, \\\n",
+ " JSON_QUERY(geo_ip, '$.city') as city, \\\n",
+ " JSON_OBJECT('session':session, 'session_length':session_length) as sessionDetails \\\n",
+ " FROM \\\"source\\\" \\\n",
+ " PARTITIONED BY DAY\"\n",
+ "})\n",
+ "\n",
+ "headers = {'Content-Type': 'application/json'}\n",
+ "\n",
+ "response = requests.request(http_method, druid_host+endpoint, headers=headers, data=payload)\n",
+ "ingestion_taskId_response = response\n",
+ "ingestion_taskId = json.loads(ingestion_taskId_response.text)['taskId']\n",
+ "\n",
+ "print(f\"\\nInserting data into the table named {dataSource2}\")\n",
+ "print(\"\\nThe response includes the task ID and the status: \" + response.text + \".\")"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "4a72f7a9-d7f3-4987-bce5-cb482369b1c3",
+ "metadata": {},
+ "source": [
+ "Run the following cell to get the status of the ingestion task."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "fd275148-b930-4626-a433-e34a1c308f03",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "import time\n",
Review Comment:
You don't need to import time a second time. The cell that also had the sleep loop already imported it.
##########
examples/quickstart/jupyter-notebooks/nested-columns-tutorial.ipynb:
##########
@@ -57,28 +57,30 @@
"source": [
"## Prerequisites\n",
"\n",
- "You'll need install the Requests library for Python before you start. For example:\n",
+ "You need to install the requests library for Python before you start—for example:\n",
Review Comment:
The official name of the library uses `R` not `r`. It's kind of hard to notice on t heir docs/repo cause they keep putting it at the start of a sentence
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] Nested columns tutorial (druid)
Posted by "writer-jill (via GitHub)" <gi...@apache.org>.
writer-jill closed pull request #13718: Nested columns tutorial
URL: https://github.com/apache/druid/pull/13718
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] 317brian commented on a diff in pull request #13718: Nested columns tutorial
Posted by "317brian (via GitHub)" <gi...@apache.org>.
317brian commented on code in PR #13718:
URL: https://github.com/apache/druid/pull/13718#discussion_r1093901497
##########
examples/quickstart/jupyter-notebooks/nested-columns-tutorial.ipynb:
##########
@@ -1,63 +1,13 @@
{
"cells": [
- {
Review Comment:
This whole section got deleted for some reason
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] 317brian commented on a diff in pull request #13718: Nested columns tutorial
Posted by "317brian (via GitHub)" <gi...@apache.org>.
317brian commented on code in PR #13718:
URL: https://github.com/apache/druid/pull/13718#discussion_r1093901497
##########
examples/quickstart/jupyter-notebooks/nested-columns-tutorial.ipynb:
##########
@@ -1,63 +1,13 @@
{
"cells": [
- {
Review Comment:
This got deleted for some reason
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] 317brian commented on a diff in pull request #13718: Nested columns tutorial
Posted by "317brian (via GitHub)" <gi...@apache.org>.
317brian commented on code in PR #13718:
URL: https://github.com/apache/druid/pull/13718#discussion_r1089335439
##########
examples/quickstart/jupyter-notebooks/nested-columns-tutorial.ipynb:
##########
@@ -0,0 +1,517 @@
+{
+ "cells": [
+ {
+ "cell_type": "markdown",
+ "id": "ad4e60b6",
+ "metadata": {
+ "tags": []
+ },
+ "source": [
+ "# Working with nested columns\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",
+ "This tutorial demonstrates how to work with [nested columns](https://druid.apache.org/docs/latest/querying/nested-columns.html) in Apache Druid.\n",
+ "\n",
+ "Druid stores nested data structures in `COMPLEX<json>` columns. In this tutorial you perform the following tasks:\n",
Review Comment:
```suggestion
"Druid stores nested data structures in `COMPLEX<json>` columns. In this tutorial, you perform the following tasks:\n",
```
##########
examples/quickstart/jupyter-notebooks/nested-columns-tutorial.ipynb:
##########
@@ -0,0 +1,517 @@
+{
+ "cells": [
+ {
+ "cell_type": "markdown",
+ "id": "ad4e60b6",
+ "metadata": {
+ "tags": []
+ },
+ "source": [
+ "# Working with nested columns\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",
+ "This tutorial demonstrates how to work with [nested columns](https://druid.apache.org/docs/latest/querying/nested-columns.html) in Apache Druid.\n",
+ "\n",
+ "Druid stores nested data structures in `COMPLEX<json>` columns. In this tutorial you perform the following tasks:\n",
+ "\n",
+ "- Ingest nested JSON data using SQL-based ingestion.\n",
+ "- Transform nested data during ingestion using SQL JSON functions.\n",
+ "- Perform queries to display, filter, and aggregate nested data.\n",
+ "\n",
+ "Druid supports directly ingesting nested data with the following formats: JSON, Parquet, Avro, ORC, Protobuf.\n",
+ "\n",
+ "\n",
+ "## Table of contents\n",
+ "\n",
+ "- [Prerequisites](#Prerequisites)\n",
+ "- [Ingest data](#Ingest-data)\n",
+ "- [Transform data](#Transform-data)\n",
+ "- [Query nested data](#Query-nested-data)\n",
+ "- [Learn more](#Learn-more)\n",
+ "\n",
+ "For the best experience, use Jupyter Lab so that you can always access the table of contents."
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "e4638f98",
+ "metadata": {},
+ "source": [
+ "## Prerequisites\n",
+ "\n",
+ "You'll need install the Requests library for Python before you start. For example:\n",
+ "\n",
+ "```bash\n",
+ "pip3 install requests\n",
+ "```\n",
+ "\n",
+ "Next, you'll need a Druid cluster. This tutorial uses the `micro-quickstart` config described in the [Druid quickstart](https://druid.apache.org/docs/latest/tutorials/index.html). So download that and start it if you haven't already. In the root of the Druid folder, run the following command to start Druid:\n",
Review Comment:
The microquickstart config doesn't exist anymore after the most recent Druid release.
##########
examples/quickstart/jupyter-notebooks/nested-columns-tutorial.ipynb:
##########
@@ -0,0 +1,517 @@
+{
+ "cells": [
+ {
+ "cell_type": "markdown",
+ "id": "ad4e60b6",
+ "metadata": {
+ "tags": []
+ },
+ "source": [
+ "# Working with nested columns\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",
+ "This tutorial demonstrates how to work with [nested columns](https://druid.apache.org/docs/latest/querying/nested-columns.html) in Apache Druid.\n",
+ "\n",
+ "Druid stores nested data structures in `COMPLEX<json>` columns. In this tutorial you perform the following tasks:\n",
+ "\n",
+ "- Ingest nested JSON data using SQL-based ingestion.\n",
+ "- Transform nested data during ingestion using SQL JSON functions.\n",
+ "- Perform queries to display, filter, and aggregate nested data.\n",
+ "\n",
+ "Druid supports directly ingesting nested data with the following formats: JSON, Parquet, Avro, ORC, Protobuf.\n",
+ "\n",
+ "\n",
+ "## Table of contents\n",
+ "\n",
+ "- [Prerequisites](#Prerequisites)\n",
+ "- [Ingest data](#Ingest-data)\n",
+ "- [Transform data](#Transform-data)\n",
+ "- [Query nested data](#Query-nested-data)\n",
+ "- [Learn more](#Learn-more)\n",
+ "\n",
+ "For the best experience, use Jupyter Lab so that you can always access the table of contents."
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "e4638f98",
+ "metadata": {},
+ "source": [
+ "## Prerequisites\n",
+ "\n",
+ "You'll need install the Requests library for Python before you start. For example:\n",
+ "\n",
+ "```bash\n",
+ "pip3 install requests\n",
+ "```\n",
+ "\n",
+ "Next, you'll need a Druid cluster. This tutorial uses the `micro-quickstart` config described in the [Druid quickstart](https://druid.apache.org/docs/latest/tutorials/index.html). So download that and start it if you haven't already. In the root of the Druid folder, run the following command to start Druid:\n",
+ "\n",
+ "```bash\n",
+ "./bin/start-druid\n",
+ "```\n",
+ "\n",
+ "Finally, you'll need either Jupyter lab (recommended) or Jupyter notebook. Both the quickstart Druid cluster and Jupyter notebook are deployed at `localhost:8888` by default, so you'll need to change the port for Jupyter. To do so, stop Jupyter and start it again with the `port` parameter included. For example, you can use the following command to start Jupyter on port `3001`:\n",
+ "\n",
+ "```bash\n",
+ "# If you're using Jupyter lab\n",
+ "jupyter lab --port 3001\n",
+ "# If you're using Jupyter notebook\n",
+ "jupyter notebook --port 3001 \n",
+ "```\n",
+ "\n",
+ "To start this tutorial, run the next cell. It imports the Python packages you'll need and defines a variable for the the Druid host the tutorial uses. The quickstart deployment configures Druid to listen on port `8888` by default, so you'll be making API calls against `http://localhost:8888`."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "b7f08a52",
+ "metadata": {
+ "tags": []
+ },
+ "outputs": [],
+ "source": [
+ "import requests\n",
+ "import json\n",
+ "\n",
+ "# druid_host is the hostname and port for your Druid deployment. \n",
+ "# In a distributed environment, use the Router service as the `druid_host`. \n",
+ "druid_host = \"http://localhost:8888\"\n",
+ "dataSource1 = \"kttm_one\"\n",
+ "dataSource2 = \"kttm_transform\"\n",
+ "print(f\"\\033[1mDruid host\\033[0m: {druid_host}\")"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "e893ef7d-7136-442f-8bd9-31b5a5276518",
+ "metadata": {},
+ "source": [
+ "In the rest of the tutorial, the `endpoint`, `http_method`, and `payload` variables are updated to accomplish different tasks."
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "ebd8c7db-c39f-4ef7-86ec-81f405e02550",
+ "metadata": {},
+ "source": [
+ "## Ingest nested data\n",
+ "\n",
+ "Run the following cell to ingest sample clickstream data from the [Koalas to the Max](https://www.koalastothemax.com/) game."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "75a177f1",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "endpoint = \"/druid/v2/sql/task\"\n",
+ "print(druid_host+endpoint)\n",
+ "http_method = \"POST\"\n",
+ "\n",
+ "payload = json.dumps({\n",
+ "\"query\": \"INSERT INTO \\\"kttm_one\\\" \\\n",
+ " WITH \\\"source\\\" AS \\\n",
+ " (SELECT * FROM TABLE(EXTERN('{\\\"type\\\":\\\"http\\\",\\\"uris\\\":[\\\"https://static.imply.io/example-data/kttm-nested-v2/kttm-nested-v2-2019-08-25.json.gz\\\"]}', \\\n",
+ " '{\\\"type\\\":\\\"json\\\"}','[{\\\"name\\\":\\\"timestamp\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"client_ip\\\",\\\"type\\\":\\\"string\\\"}, \\\n",
+ " {\\\"name\\\":\\\"session\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"session_length\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"event\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"}, \\\n",
+ " {\\\"name\\\":\\\"agent\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"},{\\\"name\\\":\\\"geo_ip\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"}]'))) \\\n",
+ " SELECT TIME_PARSE(\\\"timestamp\\\") AS \\\"__time\\\", \\\"client_ip\\\", \\\"session\\\", \\\"session_length\\\", \\\"event\\\", \\\"agent\\\", \\\"geo_ip\\\"FROM \\\"source\\\" \\\n",
+ " PARTITIONED BY DAY\"\n",
+ "})\n",
+ " \n",
+ "headers = {'Content-Type': 'application/json'}\n",
+ "\n",
+ "response = requests.request(http_method, druid_host+endpoint, headers=headers, data=payload)\n",
+ "ingestion_taskId_response = response\n",
+ "ingestion_taskId = json.loads(ingestion_taskId_response.text)['taskId']\n",
+ "\n",
+ "print(f\"\\nInserting data into the table named {dataSource1}\")\n",
Review Comment:
Missing period
##########
examples/quickstart/jupyter-notebooks/nested-columns-tutorial.ipynb:
##########
@@ -0,0 +1,517 @@
+{
+ "cells": [
+ {
+ "cell_type": "markdown",
+ "id": "ad4e60b6",
+ "metadata": {
+ "tags": []
+ },
+ "source": [
+ "# Working with nested columns\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",
+ "This tutorial demonstrates how to work with [nested columns](https://druid.apache.org/docs/latest/querying/nested-columns.html) in Apache Druid.\n",
+ "\n",
+ "Druid stores nested data structures in `COMPLEX<json>` columns. In this tutorial you perform the following tasks:\n",
+ "\n",
+ "- Ingest nested JSON data using SQL-based ingestion.\n",
+ "- Transform nested data during ingestion using SQL JSON functions.\n",
+ "- Perform queries to display, filter, and aggregate nested data.\n",
+ "\n",
+ "Druid supports directly ingesting nested data with the following formats: JSON, Parquet, Avro, ORC, Protobuf.\n",
+ "\n",
+ "\n",
+ "## Table of contents\n",
+ "\n",
+ "- [Prerequisites](#Prerequisites)\n",
+ "- [Ingest data](#Ingest-data)\n",
+ "- [Transform data](#Transform-data)\n",
+ "- [Query nested data](#Query-nested-data)\n",
+ "- [Learn more](#Learn-more)\n",
+ "\n",
+ "For the best experience, use Jupyter Lab so that you can always access the table of contents."
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "e4638f98",
+ "metadata": {},
+ "source": [
+ "## Prerequisites\n",
+ "\n",
+ "You'll need install the Requests library for Python before you start. For example:\n",
+ "\n",
+ "```bash\n",
+ "pip3 install requests\n",
+ "```\n",
+ "\n",
+ "Next, you'll need a Druid cluster. This tutorial uses the `micro-quickstart` config described in the [Druid quickstart](https://druid.apache.org/docs/latest/tutorials/index.html). So download that and start it if you haven't already. In the root of the Druid folder, run the following command to start Druid:\n",
+ "\n",
+ "```bash\n",
+ "./bin/start-druid\n",
+ "```\n",
+ "\n",
+ "Finally, you'll need either Jupyter lab (recommended) or Jupyter notebook. Both the quickstart Druid cluster and Jupyter notebook are deployed at `localhost:8888` by default, so you'll need to change the port for Jupyter. To do so, stop Jupyter and start it again with the `port` parameter included. For example, you can use the following command to start Jupyter on port `3001`:\n",
Review Comment:
```suggestion
"Finally, you'll need either JupyterLab (recommended) or Jupyter Notebook. Both the quickstart Druid cluster and Jupyter are deployed at `localhost:8888` by default, so you'll need to change the port for Jupyter. To do so, stop Jupyter and start it again with the `port` parameter included. For example, you can use the following command to start Jupyter on port `3001`:\n",
```
##########
examples/quickstart/jupyter-notebooks/nested-columns-tutorial.ipynb:
##########
@@ -0,0 +1,517 @@
+{
+ "cells": [
+ {
+ "cell_type": "markdown",
+ "id": "ad4e60b6",
+ "metadata": {
+ "tags": []
+ },
+ "source": [
+ "# Working with nested columns\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",
+ "This tutorial demonstrates how to work with [nested columns](https://druid.apache.org/docs/latest/querying/nested-columns.html) in Apache Druid.\n",
+ "\n",
+ "Druid stores nested data structures in `COMPLEX<json>` columns. In this tutorial you perform the following tasks:\n",
+ "\n",
+ "- Ingest nested JSON data using SQL-based ingestion.\n",
+ "- Transform nested data during ingestion using SQL JSON functions.\n",
+ "- Perform queries to display, filter, and aggregate nested data.\n",
+ "\n",
+ "Druid supports directly ingesting nested data with the following formats: JSON, Parquet, Avro, ORC, Protobuf.\n",
+ "\n",
+ "\n",
+ "## Table of contents\n",
+ "\n",
+ "- [Prerequisites](#Prerequisites)\n",
+ "- [Ingest data](#Ingest-data)\n",
+ "- [Transform data](#Transform-data)\n",
+ "- [Query nested data](#Query-nested-data)\n",
+ "- [Learn more](#Learn-more)\n",
+ "\n",
+ "For the best experience, use Jupyter Lab so that you can always access the table of contents."
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "e4638f98",
+ "metadata": {},
+ "source": [
+ "## Prerequisites\n",
+ "\n",
+ "You'll need install the Requests library for Python before you start. For example:\n",
+ "\n",
+ "```bash\n",
+ "pip3 install requests\n",
+ "```\n",
+ "\n",
+ "Next, you'll need a Druid cluster. This tutorial uses the `micro-quickstart` config described in the [Druid quickstart](https://druid.apache.org/docs/latest/tutorials/index.html). So download that and start it if you haven't already. In the root of the Druid folder, run the following command to start Druid:\n",
+ "\n",
+ "```bash\n",
+ "./bin/start-druid\n",
+ "```\n",
+ "\n",
+ "Finally, you'll need either Jupyter lab (recommended) or Jupyter notebook. Both the quickstart Druid cluster and Jupyter notebook are deployed at `localhost:8888` by default, so you'll need to change the port for Jupyter. To do so, stop Jupyter and start it again with the `port` parameter included. For example, you can use the following command to start Jupyter on port `3001`:\n",
Review Comment:
The official names of the products are `JupyterLab` and `Jupyter Notebook`
##########
examples/quickstart/jupyter-notebooks/nested-columns-tutorial.ipynb:
##########
@@ -0,0 +1,517 @@
+{
+ "cells": [
+ {
+ "cell_type": "markdown",
+ "id": "ad4e60b6",
+ "metadata": {
+ "tags": []
+ },
+ "source": [
+ "# Working with nested columns\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",
+ "This tutorial demonstrates how to work with [nested columns](https://druid.apache.org/docs/latest/querying/nested-columns.html) in Apache Druid.\n",
+ "\n",
+ "Druid stores nested data structures in `COMPLEX<json>` columns. In this tutorial you perform the following tasks:\n",
+ "\n",
+ "- Ingest nested JSON data using SQL-based ingestion.\n",
+ "- Transform nested data during ingestion using SQL JSON functions.\n",
+ "- Perform queries to display, filter, and aggregate nested data.\n",
+ "\n",
+ "Druid supports directly ingesting nested data with the following formats: JSON, Parquet, Avro, ORC, Protobuf.\n",
+ "\n",
+ "\n",
+ "## Table of contents\n",
+ "\n",
+ "- [Prerequisites](#Prerequisites)\n",
+ "- [Ingest data](#Ingest-data)\n",
+ "- [Transform data](#Transform-data)\n",
+ "- [Query nested data](#Query-nested-data)\n",
+ "- [Learn more](#Learn-more)\n",
+ "\n",
+ "For the best experience, use Jupyter Lab so that you can always access the table of contents."
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "e4638f98",
+ "metadata": {},
+ "source": [
+ "## Prerequisites\n",
+ "\n",
+ "You'll need install the Requests library for Python before you start. For example:\n",
+ "\n",
+ "```bash\n",
+ "pip3 install requests\n",
+ "```\n",
+ "\n",
+ "Next, you'll need a Druid cluster. This tutorial uses the `micro-quickstart` config described in the [Druid quickstart](https://druid.apache.org/docs/latest/tutorials/index.html). So download that and start it if you haven't already. In the root of the Druid folder, run the following command to start Druid:\n",
+ "\n",
+ "```bash\n",
+ "./bin/start-druid\n",
+ "```\n",
+ "\n",
+ "Finally, you'll need either Jupyter lab (recommended) or Jupyter notebook. Both the quickstart Druid cluster and Jupyter notebook are deployed at `localhost:8888` by default, so you'll need to change the port for Jupyter. To do so, stop Jupyter and start it again with the `port` parameter included. For example, you can use the following command to start Jupyter on port `3001`:\n",
+ "\n",
+ "```bash\n",
+ "# If you're using Jupyter lab\n",
+ "jupyter lab --port 3001\n",
+ "# If you're using Jupyter notebook\n",
+ "jupyter notebook --port 3001 \n",
+ "```\n",
+ "\n",
+ "To start this tutorial, run the next cell. It imports the Python packages you'll need and defines a variable for the the Druid host the tutorial uses. The quickstart deployment configures Druid to listen on port `8888` by default, so you'll be making API calls against `http://localhost:8888`."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "b7f08a52",
+ "metadata": {
+ "tags": []
+ },
+ "outputs": [],
+ "source": [
+ "import requests\n",
+ "import json\n",
+ "\n",
+ "# druid_host is the hostname and port for your Druid deployment. \n",
+ "# In a distributed environment, use the Router service as the `druid_host`. \n",
+ "druid_host = \"http://localhost:8888\"\n",
+ "dataSource1 = \"kttm_one\"\n",
+ "dataSource2 = \"kttm_transform\"\n",
+ "print(f\"\\033[1mDruid host\\033[0m: {druid_host}\")"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "e893ef7d-7136-442f-8bd9-31b5a5276518",
+ "metadata": {},
+ "source": [
+ "In the rest of the tutorial, the `endpoint`, `http_method`, and `payload` variables are updated to accomplish different tasks."
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "ebd8c7db-c39f-4ef7-86ec-81f405e02550",
+ "metadata": {},
+ "source": [
+ "## Ingest nested data\n",
+ "\n",
+ "Run the following cell to ingest sample clickstream data from the [Koalas to the Max](https://www.koalastothemax.com/) game."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "75a177f1",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "endpoint = \"/druid/v2/sql/task\"\n",
+ "print(druid_host+endpoint)\n",
+ "http_method = \"POST\"\n",
+ "\n",
+ "payload = json.dumps({\n",
+ "\"query\": \"INSERT INTO \\\"kttm_one\\\" \\\n",
+ " WITH \\\"source\\\" AS \\\n",
+ " (SELECT * FROM TABLE(EXTERN('{\\\"type\\\":\\\"http\\\",\\\"uris\\\":[\\\"https://static.imply.io/example-data/kttm-nested-v2/kttm-nested-v2-2019-08-25.json.gz\\\"]}', \\\n",
+ " '{\\\"type\\\":\\\"json\\\"}','[{\\\"name\\\":\\\"timestamp\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"client_ip\\\",\\\"type\\\":\\\"string\\\"}, \\\n",
+ " {\\\"name\\\":\\\"session\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"session_length\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"event\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"}, \\\n",
+ " {\\\"name\\\":\\\"agent\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"},{\\\"name\\\":\\\"geo_ip\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"}]'))) \\\n",
+ " SELECT TIME_PARSE(\\\"timestamp\\\") AS \\\"__time\\\", \\\"client_ip\\\", \\\"session\\\", \\\"session_length\\\", \\\"event\\\", \\\"agent\\\", \\\"geo_ip\\\"FROM \\\"source\\\" \\\n",
+ " PARTITIONED BY DAY\"\n",
+ "})\n",
+ " \n",
+ "headers = {'Content-Type': 'application/json'}\n",
+ "\n",
+ "response = requests.request(http_method, druid_host+endpoint, headers=headers, data=payload)\n",
+ "ingestion_taskId_response = response\n",
+ "ingestion_taskId = json.loads(ingestion_taskId_response.text)['taskId']\n",
+ "\n",
+ "print(f\"\\nInserting data into the table named {dataSource1}\")\n",
+ "print(\"\\nThe response includes the task ID and the status: \" + response.text + \".\")"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "17c19a74",
+ "metadata": {},
+ "source": [
+ "Run the following cell to get the status of the ingestion task."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "eeb78251",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "import time\n",
+ "\n",
+ "endpoint = f\"/druid/indexer/v1/task/{ingestion_taskId}/status\"\n",
+ "print(f\"\\033[1mQuery endpoint\\033[0m: {druid_host+endpoint}\")\n",
+ "http_method = \"GET\"\n",
+ "\n",
+ "payload = {}\n",
+ "headers = {}\n",
+ "\n",
+ "response = requests.request(http_method, druid_host+endpoint, headers=headers, data=payload)\n",
+ "ingestion_status = json.loads(response.text)['status']['status']\n",
+ "# If you only want to fetch the status once and print it, \n",
+ "# uncomment the print statement and comment out the if and while loops\n",
+ "# print(json.dumps(response.json(), indent=4))\n",
+ "\n",
+ "if ingestion_status == \"RUNNING\":\n",
+ " print(\"The ingestion is running...\")\n",
+ "\n",
+ "while ingestion_status != \"SUCCESS\":\n",
+ " response = requests.request(http_method, druid_host+endpoint, headers=headers, data=payload)\n",
+ " ingestion_status = json.loads(response.text)['status']['status']\n",
+ " time.sleep(15) \n",
+ " \n",
+ "if ingestion_status == \"SUCCESS\": \n",
+ " print(\"The ingestion is complete:\")\n",
+ " print(json.dumps(response.json(), indent=4))"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "9a2284f3",
+ "metadata": {},
+ "source": [
+ "When the ingestion task status shows `SUCCESS`, run the following cell to query the data and return selected columns from 3 rows. Note the nested structure of the `event`, `agent`, and `geo_ip` columns."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "e801999d",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "endpoint = \"/druid/v2/sql\"\n",
+ "print(druid_host+endpoint)\n",
+ "http_method = \"POST\"\n",
+ "\n",
+ "payload = json.dumps({\n",
+ " \"query\": \"SELECT session, event, agent, geo_ip FROM kttm_one LIMIT 3\"\n",
+ "})\n",
+ "headers = {'Content-Type': 'application/json'}\n",
+ "\n",
+ "response = requests.request(http_method, druid_host+endpoint, headers=headers, data=payload)\n",
+ "\n",
+ "print(json.dumps(json.loads(response.text), indent=4))"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "240b0ad5-48f2-4737-b12b-5fd5f98da300",
+ "metadata": {},
+ "source": [
+ "## Transform nested data\n",
+ "\n",
+ "You can use Druid's [SQL JSON functions](https://druid.apache.org/docs/latest/querying/sql-json-functions.html) to transform nested data in your ingestion query.\n",
+ "\n",
+ "Run the following cell to insert sample data into a new datasource named `kttm_transform`. The SELECT query extracts the `country` and `city` elements from the nested `geo_ip` column and creates a composite object `sessionDetails` containing `session` and `session_length`."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "a1f21fa8",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "endpoint = \"/druid/v2/sql/task\"\n",
+ "print(druid_host+endpoint)\n",
+ "http_method = \"POST\"\n",
+ "\n",
+ "payload = json.dumps({\n",
+ "\"query\": \"INSERT INTO \\\"kttm_transform\\\" \\\n",
+ " WITH \\\"source\\\" AS \\\n",
+ " (SELECT * FROM TABLE(EXTERN('{\\\"type\\\":\\\"http\\\",\\\"uris\\\":[\\\"https://static.imply.io/example-data/kttm-nested-v2/kttm-nested-v2-2019-08-25.json.gz\\\"]}', \\\n",
+ " '{\\\"type\\\":\\\"json\\\"}','[{\\\"name\\\":\\\"timestamp\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"session\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"session_length\\\",\\\"type\\\":\\\"string\\\"}, \\\n",
+ " {\\\"name\\\":\\\"event\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"},{\\\"name\\\":\\\"agent\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"},{\\\"name\\\":\\\"geo_ip\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"}]'))) \\\n",
+ " SELECT TIME_PARSE(\\\"timestamp\\\") AS \\\"__time\\\", \\\n",
+ " JSON_QUERY(geo_ip, '$.country') as country, \\\n",
+ " JSON_QUERY(geo_ip, '$.city') as city, \\\n",
+ " JSON_OBJECT('session':session, 'session_length':session_length) as sessionDetails \\\n",
+ " FROM \\\"source\\\" \\\n",
+ " PARTITIONED BY DAY\"\n",
+ "})\n",
+ "\n",
+ "headers = {'Content-Type': 'application/json'}\n",
+ "\n",
+ "response = requests.request(http_method, druid_host+endpoint, headers=headers, data=payload)\n",
+ "ingestion_taskId_response = response\n",
+ "ingestion_taskId = json.loads(ingestion_taskId_response.text)['taskId']\n",
+ "\n",
+ "print(f\"\\nInserting data into the table named {dataSource2}\")\n",
+ "print(\"\\nThe response includes the task ID and the status: \" + response.text + \".\")"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "bb22bd78",
+ "metadata": {},
+ "source": [
+ "When the ingestion task is complete, run the following cell to query the data and return `country`, `city`, and `sessionDetails` from 3 rows:"
Review Comment:
You probably want to include a similar while + sleep loop like from above so that they get feedback for when it's complete. Without it, there's no response/indicaiton for when it's done unless they manually run an API call themselves or if they're monitoring it in the Druid console
##########
examples/quickstart/jupyter-notebooks/nested-columns-tutorial.ipynb:
##########
@@ -0,0 +1,517 @@
+{
+ "cells": [
+ {
+ "cell_type": "markdown",
+ "id": "ad4e60b6",
+ "metadata": {
+ "tags": []
+ },
+ "source": [
+ "# Working with nested columns\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",
+ "This tutorial demonstrates how to work with [nested columns](https://druid.apache.org/docs/latest/querying/nested-columns.html) in Apache Druid.\n",
+ "\n",
+ "Druid stores nested data structures in `COMPLEX<json>` columns. In this tutorial you perform the following tasks:\n",
+ "\n",
+ "- Ingest nested JSON data using SQL-based ingestion.\n",
+ "- Transform nested data during ingestion using SQL JSON functions.\n",
+ "- Perform queries to display, filter, and aggregate nested data.\n",
+ "\n",
+ "Druid supports directly ingesting nested data with the following formats: JSON, Parquet, Avro, ORC, Protobuf.\n",
+ "\n",
+ "\n",
+ "## Table of contents\n",
+ "\n",
+ "- [Prerequisites](#Prerequisites)\n",
+ "- [Ingest data](#Ingest-data)\n",
+ "- [Transform data](#Transform-data)\n",
+ "- [Query nested data](#Query-nested-data)\n",
+ "- [Learn more](#Learn-more)\n",
+ "\n",
+ "For the best experience, use Jupyter Lab so that you can always access the table of contents."
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "e4638f98",
+ "metadata": {},
+ "source": [
+ "## Prerequisites\n",
+ "\n",
+ "You'll need install the Requests library for Python before you start. For example:\n",
+ "\n",
+ "```bash\n",
+ "pip3 install requests\n",
+ "```\n",
+ "\n",
+ "Next, you'll need a Druid cluster. This tutorial uses the `micro-quickstart` config described in the [Druid quickstart](https://druid.apache.org/docs/latest/tutorials/index.html). So download that and start it if you haven't already. In the root of the Druid folder, run the following command to start Druid:\n",
+ "\n",
+ "```bash\n",
+ "./bin/start-druid\n",
+ "```\n",
+ "\n",
+ "Finally, you'll need either Jupyter lab (recommended) or Jupyter notebook. Both the quickstart Druid cluster and Jupyter notebook are deployed at `localhost:8888` by default, so you'll need to change the port for Jupyter. To do so, stop Jupyter and start it again with the `port` parameter included. For example, you can use the following command to start Jupyter on port `3001`:\n",
+ "\n",
+ "```bash\n",
+ "# If you're using Jupyter lab\n",
+ "jupyter lab --port 3001\n",
+ "# If you're using Jupyter notebook\n",
+ "jupyter notebook --port 3001 \n",
+ "```\n",
+ "\n",
+ "To start this tutorial, run the next cell. It imports the Python packages you'll need and defines a variable for the the Druid host the tutorial uses. The quickstart deployment configures Druid to listen on port `8888` by default, so you'll be making API calls against `http://localhost:8888`."
Review Comment:
The cell also defines 2 variables for datasources
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] github-actions[bot] commented on pull request #13718: Nested columns tutorial
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #13718:
URL: https://github.com/apache/druid/pull/13718#issuecomment-1544921496
This pull request has been marked as stale due to 60 days of inactivity.
It will be closed in 4 weeks if no further activity occurs. If you think
that's incorrect or this pull request should instead be reviewed, please simply
write any comment. Even if closed, you can still revive the PR at any time or
discuss it on the dev@druid.apache.org list.
Thank you for your contributions.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] writer-jill commented on a diff in pull request #13718: Nested columns tutorial
Posted by "writer-jill (via GitHub)" <gi...@apache.org>.
writer-jill commented on code in PR #13718:
URL: https://github.com/apache/druid/pull/13718#discussion_r1090532551
##########
examples/quickstart/jupyter-notebooks/nested-columns-tutorial.ipynb:
##########
@@ -0,0 +1,517 @@
+{
+ "cells": [
+ {
+ "cell_type": "markdown",
+ "id": "ad4e60b6",
+ "metadata": {
+ "tags": []
+ },
+ "source": [
+ "# Working with nested columns\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",
+ "This tutorial demonstrates how to work with [nested columns](https://druid.apache.org/docs/latest/querying/nested-columns.html) in Apache Druid.\n",
+ "\n",
+ "Druid stores nested data structures in `COMPLEX<json>` columns. In this tutorial you perform the following tasks:\n",
+ "\n",
+ "- Ingest nested JSON data using SQL-based ingestion.\n",
+ "- Transform nested data during ingestion using SQL JSON functions.\n",
+ "- Perform queries to display, filter, and aggregate nested data.\n",
+ "\n",
+ "Druid supports directly ingesting nested data with the following formats: JSON, Parquet, Avro, ORC, Protobuf.\n",
+ "\n",
+ "\n",
+ "## Table of contents\n",
+ "\n",
+ "- [Prerequisites](#Prerequisites)\n",
+ "- [Ingest data](#Ingest-data)\n",
+ "- [Transform data](#Transform-data)\n",
+ "- [Query nested data](#Query-nested-data)\n",
+ "- [Learn more](#Learn-more)\n",
+ "\n",
+ "For the best experience, use Jupyter Lab so that you can always access the table of contents."
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "e4638f98",
+ "metadata": {},
+ "source": [
+ "## Prerequisites\n",
+ "\n",
+ "You'll need install the Requests library for Python before you start. For example:\n",
+ "\n",
+ "```bash\n",
+ "pip3 install requests\n",
+ "```\n",
+ "\n",
+ "Next, you'll need a Druid cluster. This tutorial uses the `micro-quickstart` config described in the [Druid quickstart](https://druid.apache.org/docs/latest/tutorials/index.html). So download that and start it if you haven't already. In the root of the Druid folder, run the following command to start Druid:\n",
Review Comment:
There was an old version of the prerequisites in this file for some reason - my mistake. Updated.
##########
examples/quickstart/jupyter-notebooks/nested-columns-tutorial.ipynb:
##########
@@ -0,0 +1,517 @@
+{
+ "cells": [
+ {
+ "cell_type": "markdown",
+ "id": "ad4e60b6",
+ "metadata": {
+ "tags": []
+ },
+ "source": [
+ "# Working with nested columns\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",
+ "This tutorial demonstrates how to work with [nested columns](https://druid.apache.org/docs/latest/querying/nested-columns.html) in Apache Druid.\n",
+ "\n",
+ "Druid stores nested data structures in `COMPLEX<json>` columns. In this tutorial you perform the following tasks:\n",
+ "\n",
+ "- Ingest nested JSON data using SQL-based ingestion.\n",
+ "- Transform nested data during ingestion using SQL JSON functions.\n",
+ "- Perform queries to display, filter, and aggregate nested data.\n",
+ "\n",
+ "Druid supports directly ingesting nested data with the following formats: JSON, Parquet, Avro, ORC, Protobuf.\n",
+ "\n",
+ "\n",
+ "## Table of contents\n",
+ "\n",
+ "- [Prerequisites](#Prerequisites)\n",
+ "- [Ingest data](#Ingest-data)\n",
+ "- [Transform data](#Transform-data)\n",
+ "- [Query nested data](#Query-nested-data)\n",
+ "- [Learn more](#Learn-more)\n",
+ "\n",
+ "For the best experience, use Jupyter Lab so that you can always access the table of contents."
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "e4638f98",
+ "metadata": {},
+ "source": [
+ "## Prerequisites\n",
+ "\n",
+ "You'll need install the Requests library for Python before you start. For example:\n",
+ "\n",
+ "```bash\n",
+ "pip3 install requests\n",
+ "```\n",
+ "\n",
+ "Next, you'll need a Druid cluster. This tutorial uses the `micro-quickstart` config described in the [Druid quickstart](https://druid.apache.org/docs/latest/tutorials/index.html). So download that and start it if you haven't already. In the root of the Druid folder, run the following command to start Druid:\n",
+ "\n",
+ "```bash\n",
+ "./bin/start-druid\n",
+ "```\n",
+ "\n",
+ "Finally, you'll need either Jupyter lab (recommended) or Jupyter notebook. Both the quickstart Druid cluster and Jupyter notebook are deployed at `localhost:8888` by default, so you'll need to change the port for Jupyter. To do so, stop Jupyter and start it again with the `port` parameter included. For example, you can use the following command to start Jupyter on port `3001`:\n",
Review Comment:
Updated.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] writer-jill commented on a diff in pull request #13718: Nested columns tutorial
Posted by "writer-jill (via GitHub)" <gi...@apache.org>.
writer-jill commented on code in PR #13718:
URL: https://github.com/apache/druid/pull/13718#discussion_r1094662197
##########
examples/quickstart/jupyter-notebooks/nested-columns-tutorial.ipynb:
##########
@@ -1,63 +1,13 @@
{
"cells": [
- {
Review Comment:
Weird - fixed!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] writer-jill commented on a diff in pull request #13718: Nested columns tutorial
Posted by "writer-jill (via GitHub)" <gi...@apache.org>.
writer-jill commented on code in PR #13718:
URL: https://github.com/apache/druid/pull/13718#discussion_r1093198195
##########
examples/quickstart/jupyter-notebooks/nested-columns-tutorial.ipynb:
##########
@@ -0,0 +1,556 @@
+{
+ "cells": [
+ {
+ "cell_type": "markdown",
+ "id": "ad4e60b6",
+ "metadata": {
+ "tags": []
+ },
+ "source": [
+ "# Working with nested columns\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",
+ "This tutorial demonstrates how to work with [nested columns](https://druid.apache.org/docs/latest/querying/nested-columns.html) in Apache Druid.\n",
+ "\n",
+ "Druid stores nested data structures in `COMPLEX<json>` columns. In this tutorial, you perform the following tasks:\n",
+ "\n",
+ "- Ingest nested JSON data using SQL-based ingestion.\n",
+ "- Transform nested data during ingestion using SQL JSON functions.\n",
+ "- Perform queries to display, filter, and aggregate nested data.\n",
+ "\n",
+ "Druid supports directly ingesting nested data with the following formats: JSON, Parquet, Avro, ORC, Protobuf.\n",
+ "\n",
+ "\n",
+ "## Table of contents\n",
+ "\n",
+ "- [Prerequisites](#Prerequisites)\n",
+ "- [Ingest nested data](#Ingest-nested-data)\n",
+ "- [Transform nested data](#Transform-nested-data)\n",
+ "- [Query nested data](#Query-nested-data)\n",
+ "- [Learn more](#Learn-more)\n",
+ "\n",
+ "For the best experience, use JupyterLab so that you can always access the table of contents."
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "e4638f98",
+ "metadata": {},
+ "source": [
+ "## Prerequisites\n",
+ "\n",
+ "You need to install the requests library for Python before you start—for example:\n",
+ "\n",
+ "```bash\n",
+ "pip3 install requests\n",
+ "```\n",
+ "\n",
+ "Next, you need a Druid cluster. This tutorial uses the configuration described in the Druid [Quickstart (local)](https://druid.apache.org/docs/latest/tutorials/index.html). Download Druid from the quickstart page. In the root of the Druid folder, run the following command to start Druid:\n",
+ "\n",
+ "```bash\n",
+ "./bin/start-druid\n",
+ "```\n",
+ "\n",
+ "Finally, you need either JupyterLab (recommended) or Jupyter Notebook. Visit the [Jupyter site](https://jupyter.org/) if you want to learn more about these interfaces.\n",
+ "\n",
+ "Both the quickstart Druid cluster and Jupyter deploy at `localhost:8888` by default, so you need to change the port for Jupyter. To do this, stop Jupyter if it's running and start it with the `port` parameter included. For example, you can use the following command to start Jupyter on port `3001`:\n",
+ "\n",
+ "```bash\n",
+ "# If you're using JupyterLab\n",
+ "jupyter lab --port 3001\n",
+ "# If you're using Jupyter Notebook\n",
+ "jupyter notebook --port 3001 \n",
+ "```\n",
+ "\n",
+ "To start this tutorial, run the next cell. It imports the Python packages you need and defines variables for two datasources and the Druid host the tutorial uses. The quickstart deployment configures Druid to listen on port `8888` by default, so you'll make API calls against `http://localhost:8888`."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "b7f08a52",
+ "metadata": {
+ "tags": []
+ },
+ "outputs": [],
+ "source": [
+ "import requests\n",
+ "import json\n",
+ "\n",
+ "# druid_host is the hostname and port for your Druid deployment. \n",
+ "# In a distributed environment, use the Router service as the `druid_host`. \n",
+ "druid_host = \"http://localhost:8888\"\n",
+ "dataSource1 = \"kttm\"\n",
+ "dataSource2 = \"kttm_transform\"\n",
+ "print(f\"\\033[1mDruid host\\033[0m: {druid_host}\")"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "e893ef7d-7136-442f-8bd9-31b5a5276518",
+ "metadata": {},
+ "source": [
+ "In the rest of the tutorial, the `endpoint`, `http_method`, and `payload` variables are updated to accomplish different tasks."
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "ebd8c7db-c39f-4ef7-86ec-81f405e02550",
+ "metadata": {},
+ "source": [
+ "## Ingest nested data\n",
+ "\n",
+ "Run the following cell to ingest sample clickstream data from the [Koalas to the Max](https://www.koalastothemax.com/) game."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "75a177f1",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "endpoint = \"/druid/v2/sql/task\"\n",
+ "print(druid_host+endpoint)\n",
+ "http_method = \"POST\"\n",
+ "\n",
+ "payload = json.dumps({\n",
+ "\"query\": \"INSERT INTO \\\"kttm\\\" \\\n",
+ " WITH \\\"source\\\" AS \\\n",
+ " (SELECT * FROM TABLE(EXTERN('{\\\"type\\\":\\\"http\\\",\\\"uris\\\":[\\\"https://static.imply.io/example-data/kttm-nested-v2/kttm-nested-v2-2019-08-25.json.gz\\\"]}', \\\n",
+ " '{\\\"type\\\":\\\"json\\\"}','[{\\\"name\\\":\\\"timestamp\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"client_ip\\\",\\\"type\\\":\\\"string\\\"}, \\\n",
+ " {\\\"name\\\":\\\"session\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"session_length\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"event\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"}, \\\n",
+ " {\\\"name\\\":\\\"agent\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"},{\\\"name\\\":\\\"geo_ip\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"}]'))) \\\n",
+ " SELECT TIME_PARSE(\\\"timestamp\\\") AS \\\"__time\\\", \\\"client_ip\\\", \\\"session\\\", \\\"session_length\\\", \\\"event\\\", \\\"agent\\\", \\\"geo_ip\\\"FROM \\\"source\\\" \\\n",
+ " PARTITIONED BY DAY\"\n",
+ "})\n",
+ " \n",
+ "headers = {'Content-Type': 'application/json'}\n",
+ "\n",
+ "response = requests.request(http_method, druid_host+endpoint, headers=headers, data=payload)\n",
+ "ingestion_taskId_response = response\n",
+ "ingestion_taskId = json.loads(ingestion_taskId_response.text)['taskId']\n",
+ "\n",
+ "print(f\"\\nInserting data into the table named {dataSource1}.\")\n",
+ "print(\"\\nThe response includes the task ID and the status: \" + response.text + \".\")"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "17c19a74",
+ "metadata": {},
+ "source": [
+ "Run the following cell to get the status of the ingestion task."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "eeb78251",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "import time\n",
+ "\n",
+ "endpoint = f\"/druid/indexer/v1/task/{ingestion_taskId}/status\"\n",
+ "print(f\"\\033[1mQuery endpoint\\033[0m: {druid_host+endpoint}\")\n",
+ "http_method = \"GET\"\n",
+ "\n",
+ "payload = {}\n",
+ "headers = {}\n",
+ "\n",
+ "response = requests.request(http_method, druid_host+endpoint, headers=headers, data=payload)\n",
+ "ingestion_status = json.loads(response.text)['status']['status']\n",
+ "\n",
+ "if ingestion_status == \"RUNNING\":\n",
+ " print(\"The ingestion is running...\")\n",
+ "\n",
+ "while ingestion_status != \"SUCCESS\":\n",
+ " response = requests.request(http_method, druid_host+endpoint, headers=headers, data=payload)\n",
+ " ingestion_status = json.loads(response.text)['status']['status']\n",
+ " time.sleep(15) \n",
+ " \n",
+ "if ingestion_status == \"SUCCESS\": \n",
+ " print(\"The ingestion is complete:\")\n",
+ " print(json.dumps(response.json(), indent=4))"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "9a2284f3",
+ "metadata": {},
+ "source": [
+ "When the ingestion task status shows `SUCCESS`, run the following cell to query the data and return selected columns from 3 rows. Note the nested structure of the `event`, `agent`, and `geo_ip` columns."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "e801999d",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "endpoint = \"/druid/v2/sql\"\n",
+ "print(druid_host+endpoint)\n",
+ "http_method = \"POST\"\n",
+ "\n",
+ "payload = json.dumps({\n",
+ " \"query\": \"SELECT session, event, agent, geo_ip FROM kttm LIMIT 3\"\n",
+ "})\n",
+ "headers = {'Content-Type': 'application/json'}\n",
+ "\n",
+ "response = requests.request(http_method, druid_host+endpoint, headers=headers, data=payload)\n",
+ "\n",
+ "print(json.dumps(json.loads(response.text), indent=4))"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "240b0ad5-48f2-4737-b12b-5fd5f98da300",
+ "metadata": {},
+ "source": [
+ "## Transform nested data\n",
+ "\n",
+ "You can use Druid's [SQL JSON functions](https://druid.apache.org/docs/latest/querying/sql-json-functions.html) to transform nested data in your ingestion query.\n",
+ "\n",
+ "Run the following cell to insert sample data into a new datasource named `kttm_transform`. The SELECT query extracts the `country` and `city` elements from the nested `geo_ip` column and creates a composite object `sessionDetails` containing `session` and `session_length`."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "a1f21fa8",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "endpoint = \"/druid/v2/sql/task\"\n",
+ "print(druid_host+endpoint)\n",
+ "http_method = \"POST\"\n",
+ "\n",
+ "payload = json.dumps({\n",
+ "\"query\": \"INSERT INTO \\\"kttm_transform\\\" \\\n",
+ " WITH \\\"source\\\" AS \\\n",
+ " (SELECT * FROM TABLE(EXTERN('{\\\"type\\\":\\\"http\\\",\\\"uris\\\":[\\\"https://static.imply.io/example-data/kttm-nested-v2/kttm-nested-v2-2019-08-25.json.gz\\\"]}', \\\n",
+ " '{\\\"type\\\":\\\"json\\\"}','[{\\\"name\\\":\\\"timestamp\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"session\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"session_length\\\",\\\"type\\\":\\\"string\\\"}, \\\n",
+ " {\\\"name\\\":\\\"event\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"},{\\\"name\\\":\\\"agent\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"},{\\\"name\\\":\\\"geo_ip\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"}]'))) \\\n",
+ " SELECT TIME_PARSE(\\\"timestamp\\\") AS \\\"__time\\\", \\\n",
+ " JSON_QUERY(geo_ip, '$.country') as country, \\\n",
+ " JSON_QUERY(geo_ip, '$.city') as city, \\\n",
+ " JSON_OBJECT('session':session, 'session_length':session_length) as sessionDetails \\\n",
+ " FROM \\\"source\\\" \\\n",
+ " PARTITIONED BY DAY\"\n",
+ "})\n",
+ "\n",
+ "headers = {'Content-Type': 'application/json'}\n",
+ "\n",
+ "response = requests.request(http_method, druid_host+endpoint, headers=headers, data=payload)\n",
+ "ingestion_taskId_response = response\n",
+ "ingestion_taskId = json.loads(ingestion_taskId_response.text)['taskId']\n",
+ "\n",
+ "print(f\"\\nInserting data into the table named {dataSource2}\")\n",
+ "print(\"\\nThe response includes the task ID and the status: \" + response.text + \".\")"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "4a72f7a9-d7f3-4987-bce5-cb482369b1c3",
+ "metadata": {},
+ "source": [
+ "Run the following cell to get the status of the ingestion task."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "fd275148-b930-4626-a433-e34a1c308f03",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "import time\n",
Review Comment:
Updated
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] writer-jill commented on a diff in pull request #13718: Nested columns tutorial
Posted by "writer-jill (via GitHub)" <gi...@apache.org>.
writer-jill commented on code in PR #13718:
URL: https://github.com/apache/druid/pull/13718#discussion_r1090662121
##########
examples/quickstart/jupyter-notebooks/nested-columns-tutorial.ipynb:
##########
@@ -0,0 +1,517 @@
+{
+ "cells": [
+ {
+ "cell_type": "markdown",
+ "id": "ad4e60b6",
+ "metadata": {
+ "tags": []
+ },
+ "source": [
+ "# Working with nested columns\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",
+ "This tutorial demonstrates how to work with [nested columns](https://druid.apache.org/docs/latest/querying/nested-columns.html) in Apache Druid.\n",
+ "\n",
+ "Druid stores nested data structures in `COMPLEX<json>` columns. In this tutorial you perform the following tasks:\n",
+ "\n",
+ "- Ingest nested JSON data using SQL-based ingestion.\n",
+ "- Transform nested data during ingestion using SQL JSON functions.\n",
+ "- Perform queries to display, filter, and aggregate nested data.\n",
+ "\n",
+ "Druid supports directly ingesting nested data with the following formats: JSON, Parquet, Avro, ORC, Protobuf.\n",
+ "\n",
+ "\n",
+ "## Table of contents\n",
+ "\n",
+ "- [Prerequisites](#Prerequisites)\n",
+ "- [Ingest data](#Ingest-data)\n",
+ "- [Transform data](#Transform-data)\n",
+ "- [Query nested data](#Query-nested-data)\n",
+ "- [Learn more](#Learn-more)\n",
+ "\n",
+ "For the best experience, use Jupyter Lab so that you can always access the table of contents."
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "e4638f98",
+ "metadata": {},
+ "source": [
+ "## Prerequisites\n",
+ "\n",
+ "You'll need install the Requests library for Python before you start. For example:\n",
+ "\n",
+ "```bash\n",
+ "pip3 install requests\n",
+ "```\n",
+ "\n",
+ "Next, you'll need a Druid cluster. This tutorial uses the `micro-quickstart` config described in the [Druid quickstart](https://druid.apache.org/docs/latest/tutorials/index.html). So download that and start it if you haven't already. In the root of the Druid folder, run the following command to start Druid:\n",
+ "\n",
+ "```bash\n",
+ "./bin/start-druid\n",
+ "```\n",
+ "\n",
+ "Finally, you'll need either Jupyter lab (recommended) or Jupyter notebook. Both the quickstart Druid cluster and Jupyter notebook are deployed at `localhost:8888` by default, so you'll need to change the port for Jupyter. To do so, stop Jupyter and start it again with the `port` parameter included. For example, you can use the following command to start Jupyter on port `3001`:\n",
+ "\n",
+ "```bash\n",
+ "# If you're using Jupyter lab\n",
+ "jupyter lab --port 3001\n",
+ "# If you're using Jupyter notebook\n",
+ "jupyter notebook --port 3001 \n",
+ "```\n",
+ "\n",
+ "To start this tutorial, run the next cell. It imports the Python packages you'll need and defines a variable for the the Druid host the tutorial uses. The quickstart deployment configures Druid to listen on port `8888` by default, so you'll be making API calls against `http://localhost:8888`."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "b7f08a52",
+ "metadata": {
+ "tags": []
+ },
+ "outputs": [],
+ "source": [
+ "import requests\n",
+ "import json\n",
+ "\n",
+ "# druid_host is the hostname and port for your Druid deployment. \n",
+ "# In a distributed environment, use the Router service as the `druid_host`. \n",
+ "druid_host = \"http://localhost:8888\"\n",
+ "dataSource1 = \"kttm_one\"\n",
+ "dataSource2 = \"kttm_transform\"\n",
+ "print(f\"\\033[1mDruid host\\033[0m: {druid_host}\")"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "e893ef7d-7136-442f-8bd9-31b5a5276518",
+ "metadata": {},
+ "source": [
+ "In the rest of the tutorial, the `endpoint`, `http_method`, and `payload` variables are updated to accomplish different tasks."
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "ebd8c7db-c39f-4ef7-86ec-81f405e02550",
+ "metadata": {},
+ "source": [
+ "## Ingest nested data\n",
+ "\n",
+ "Run the following cell to ingest sample clickstream data from the [Koalas to the Max](https://www.koalastothemax.com/) game."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "75a177f1",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "endpoint = \"/druid/v2/sql/task\"\n",
+ "print(druid_host+endpoint)\n",
+ "http_method = \"POST\"\n",
+ "\n",
+ "payload = json.dumps({\n",
+ "\"query\": \"INSERT INTO \\\"kttm_one\\\" \\\n",
+ " WITH \\\"source\\\" AS \\\n",
+ " (SELECT * FROM TABLE(EXTERN('{\\\"type\\\":\\\"http\\\",\\\"uris\\\":[\\\"https://static.imply.io/example-data/kttm-nested-v2/kttm-nested-v2-2019-08-25.json.gz\\\"]}', \\\n",
+ " '{\\\"type\\\":\\\"json\\\"}','[{\\\"name\\\":\\\"timestamp\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"client_ip\\\",\\\"type\\\":\\\"string\\\"}, \\\n",
+ " {\\\"name\\\":\\\"session\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"session_length\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"event\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"}, \\\n",
+ " {\\\"name\\\":\\\"agent\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"},{\\\"name\\\":\\\"geo_ip\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"}]'))) \\\n",
+ " SELECT TIME_PARSE(\\\"timestamp\\\") AS \\\"__time\\\", \\\"client_ip\\\", \\\"session\\\", \\\"session_length\\\", \\\"event\\\", \\\"agent\\\", \\\"geo_ip\\\"FROM \\\"source\\\" \\\n",
+ " PARTITIONED BY DAY\"\n",
+ "})\n",
+ " \n",
+ "headers = {'Content-Type': 'application/json'}\n",
+ "\n",
+ "response = requests.request(http_method, druid_host+endpoint, headers=headers, data=payload)\n",
+ "ingestion_taskId_response = response\n",
+ "ingestion_taskId = json.loads(ingestion_taskId_response.text)['taskId']\n",
+ "\n",
+ "print(f\"\\nInserting data into the table named {dataSource1}\")\n",
+ "print(\"\\nThe response includes the task ID and the status: \" + response.text + \".\")"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "17c19a74",
+ "metadata": {},
+ "source": [
+ "Run the following cell to get the status of the ingestion task."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "eeb78251",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "import time\n",
+ "\n",
+ "endpoint = f\"/druid/indexer/v1/task/{ingestion_taskId}/status\"\n",
+ "print(f\"\\033[1mQuery endpoint\\033[0m: {druid_host+endpoint}\")\n",
+ "http_method = \"GET\"\n",
+ "\n",
+ "payload = {}\n",
+ "headers = {}\n",
+ "\n",
+ "response = requests.request(http_method, druid_host+endpoint, headers=headers, data=payload)\n",
+ "ingestion_status = json.loads(response.text)['status']['status']\n",
+ "# If you only want to fetch the status once and print it, \n",
+ "# uncomment the print statement and comment out the if and while loops\n",
+ "# print(json.dumps(response.json(), indent=4))\n",
+ "\n",
+ "if ingestion_status == \"RUNNING\":\n",
+ " print(\"The ingestion is running...\")\n",
+ "\n",
+ "while ingestion_status != \"SUCCESS\":\n",
+ " response = requests.request(http_method, druid_host+endpoint, headers=headers, data=payload)\n",
+ " ingestion_status = json.loads(response.text)['status']['status']\n",
+ " time.sleep(15) \n",
+ " \n",
+ "if ingestion_status == \"SUCCESS\": \n",
+ " print(\"The ingestion is complete:\")\n",
+ " print(json.dumps(response.json(), indent=4))"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "9a2284f3",
+ "metadata": {},
+ "source": [
+ "When the ingestion task status shows `SUCCESS`, run the following cell to query the data and return selected columns from 3 rows. Note the nested structure of the `event`, `agent`, and `geo_ip` columns."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "e801999d",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "endpoint = \"/druid/v2/sql\"\n",
+ "print(druid_host+endpoint)\n",
+ "http_method = \"POST\"\n",
+ "\n",
+ "payload = json.dumps({\n",
+ " \"query\": \"SELECT session, event, agent, geo_ip FROM kttm_one LIMIT 3\"\n",
+ "})\n",
+ "headers = {'Content-Type': 'application/json'}\n",
+ "\n",
+ "response = requests.request(http_method, druid_host+endpoint, headers=headers, data=payload)\n",
+ "\n",
+ "print(json.dumps(json.loads(response.text), indent=4))"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "240b0ad5-48f2-4737-b12b-5fd5f98da300",
+ "metadata": {},
+ "source": [
+ "## Transform nested data\n",
+ "\n",
+ "You can use Druid's [SQL JSON functions](https://druid.apache.org/docs/latest/querying/sql-json-functions.html) to transform nested data in your ingestion query.\n",
+ "\n",
+ "Run the following cell to insert sample data into a new datasource named `kttm_transform`. The SELECT query extracts the `country` and `city` elements from the nested `geo_ip` column and creates a composite object `sessionDetails` containing `session` and `session_length`."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "a1f21fa8",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "endpoint = \"/druid/v2/sql/task\"\n",
+ "print(druid_host+endpoint)\n",
+ "http_method = \"POST\"\n",
+ "\n",
+ "payload = json.dumps({\n",
+ "\"query\": \"INSERT INTO \\\"kttm_transform\\\" \\\n",
+ " WITH \\\"source\\\" AS \\\n",
+ " (SELECT * FROM TABLE(EXTERN('{\\\"type\\\":\\\"http\\\",\\\"uris\\\":[\\\"https://static.imply.io/example-data/kttm-nested-v2/kttm-nested-v2-2019-08-25.json.gz\\\"]}', \\\n",
+ " '{\\\"type\\\":\\\"json\\\"}','[{\\\"name\\\":\\\"timestamp\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"session\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"session_length\\\",\\\"type\\\":\\\"string\\\"}, \\\n",
+ " {\\\"name\\\":\\\"event\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"},{\\\"name\\\":\\\"agent\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"},{\\\"name\\\":\\\"geo_ip\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"}]'))) \\\n",
+ " SELECT TIME_PARSE(\\\"timestamp\\\") AS \\\"__time\\\", \\\n",
+ " JSON_QUERY(geo_ip, '$.country') as country, \\\n",
+ " JSON_QUERY(geo_ip, '$.city') as city, \\\n",
+ " JSON_OBJECT('session':session, 'session_length':session_length) as sessionDetails \\\n",
+ " FROM \\\"source\\\" \\\n",
+ " PARTITIONED BY DAY\"\n",
+ "})\n",
+ "\n",
+ "headers = {'Content-Type': 'application/json'}\n",
+ "\n",
+ "response = requests.request(http_method, druid_host+endpoint, headers=headers, data=payload)\n",
+ "ingestion_taskId_response = response\n",
+ "ingestion_taskId = json.loads(ingestion_taskId_response.text)['taskId']\n",
+ "\n",
+ "print(f\"\\nInserting data into the table named {dataSource2}\")\n",
+ "print(\"\\nThe response includes the task ID and the status: \" + response.text + \".\")"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "bb22bd78",
+ "metadata": {},
+ "source": [
+ "When the ingestion task is complete, run the following cell to query the data and return `country`, `city`, and `sessionDetails` from 3 rows:"
Review Comment:
Updated.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] writer-jill commented on a diff in pull request #13718: Nested columns tutorial
Posted by "writer-jill (via GitHub)" <gi...@apache.org>.
writer-jill commented on code in PR #13718:
URL: https://github.com/apache/druid/pull/13718#discussion_r1090534459
##########
examples/quickstart/jupyter-notebooks/nested-columns-tutorial.ipynb:
##########
@@ -0,0 +1,517 @@
+{
+ "cells": [
+ {
+ "cell_type": "markdown",
+ "id": "ad4e60b6",
+ "metadata": {
+ "tags": []
+ },
+ "source": [
+ "# Working with nested columns\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",
+ "This tutorial demonstrates how to work with [nested columns](https://druid.apache.org/docs/latest/querying/nested-columns.html) in Apache Druid.\n",
+ "\n",
+ "Druid stores nested data structures in `COMPLEX<json>` columns. In this tutorial you perform the following tasks:\n",
+ "\n",
+ "- Ingest nested JSON data using SQL-based ingestion.\n",
+ "- Transform nested data during ingestion using SQL JSON functions.\n",
+ "- Perform queries to display, filter, and aggregate nested data.\n",
+ "\n",
+ "Druid supports directly ingesting nested data with the following formats: JSON, Parquet, Avro, ORC, Protobuf.\n",
+ "\n",
+ "\n",
+ "## Table of contents\n",
+ "\n",
+ "- [Prerequisites](#Prerequisites)\n",
+ "- [Ingest data](#Ingest-data)\n",
+ "- [Transform data](#Transform-data)\n",
+ "- [Query nested data](#Query-nested-data)\n",
+ "- [Learn more](#Learn-more)\n",
+ "\n",
+ "For the best experience, use Jupyter Lab so that you can always access the table of contents."
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "e4638f98",
+ "metadata": {},
+ "source": [
+ "## Prerequisites\n",
+ "\n",
+ "You'll need install the Requests library for Python before you start. For example:\n",
+ "\n",
+ "```bash\n",
+ "pip3 install requests\n",
+ "```\n",
+ "\n",
+ "Next, you'll need a Druid cluster. This tutorial uses the `micro-quickstart` config described in the [Druid quickstart](https://druid.apache.org/docs/latest/tutorials/index.html). So download that and start it if you haven't already. In the root of the Druid folder, run the following command to start Druid:\n",
+ "\n",
+ "```bash\n",
+ "./bin/start-druid\n",
+ "```\n",
+ "\n",
+ "Finally, you'll need either Jupyter lab (recommended) or Jupyter notebook. Both the quickstart Druid cluster and Jupyter notebook are deployed at `localhost:8888` by default, so you'll need to change the port for Jupyter. To do so, stop Jupyter and start it again with the `port` parameter included. For example, you can use the following command to start Jupyter on port `3001`:\n",
+ "\n",
+ "```bash\n",
+ "# If you're using Jupyter lab\n",
+ "jupyter lab --port 3001\n",
+ "# If you're using Jupyter notebook\n",
+ "jupyter notebook --port 3001 \n",
+ "```\n",
+ "\n",
+ "To start this tutorial, run the next cell. It imports the Python packages you'll need and defines a variable for the the Druid host the tutorial uses. The quickstart deployment configures Druid to listen on port `8888` by default, so you'll be making API calls against `http://localhost:8888`."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "b7f08a52",
+ "metadata": {
+ "tags": []
+ },
+ "outputs": [],
+ "source": [
+ "import requests\n",
+ "import json\n",
+ "\n",
+ "# druid_host is the hostname and port for your Druid deployment. \n",
+ "# In a distributed environment, use the Router service as the `druid_host`. \n",
+ "druid_host = \"http://localhost:8888\"\n",
+ "dataSource1 = \"kttm_one\"\n",
+ "dataSource2 = \"kttm_transform\"\n",
+ "print(f\"\\033[1mDruid host\\033[0m: {druid_host}\")"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "e893ef7d-7136-442f-8bd9-31b5a5276518",
+ "metadata": {},
+ "source": [
+ "In the rest of the tutorial, the `endpoint`, `http_method`, and `payload` variables are updated to accomplish different tasks."
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "ebd8c7db-c39f-4ef7-86ec-81f405e02550",
+ "metadata": {},
+ "source": [
+ "## Ingest nested data\n",
+ "\n",
+ "Run the following cell to ingest sample clickstream data from the [Koalas to the Max](https://www.koalastothemax.com/) game."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "75a177f1",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "endpoint = \"/druid/v2/sql/task\"\n",
+ "print(druid_host+endpoint)\n",
+ "http_method = \"POST\"\n",
+ "\n",
+ "payload = json.dumps({\n",
+ "\"query\": \"INSERT INTO \\\"kttm_one\\\" \\\n",
+ " WITH \\\"source\\\" AS \\\n",
+ " (SELECT * FROM TABLE(EXTERN('{\\\"type\\\":\\\"http\\\",\\\"uris\\\":[\\\"https://static.imply.io/example-data/kttm-nested-v2/kttm-nested-v2-2019-08-25.json.gz\\\"]}', \\\n",
+ " '{\\\"type\\\":\\\"json\\\"}','[{\\\"name\\\":\\\"timestamp\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"client_ip\\\",\\\"type\\\":\\\"string\\\"}, \\\n",
+ " {\\\"name\\\":\\\"session\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"session_length\\\",\\\"type\\\":\\\"string\\\"},{\\\"name\\\":\\\"event\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"}, \\\n",
+ " {\\\"name\\\":\\\"agent\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"},{\\\"name\\\":\\\"geo_ip\\\",\\\"type\\\":\\\"COMPLEX<json>\\\"}]'))) \\\n",
+ " SELECT TIME_PARSE(\\\"timestamp\\\") AS \\\"__time\\\", \\\"client_ip\\\", \\\"session\\\", \\\"session_length\\\", \\\"event\\\", \\\"agent\\\", \\\"geo_ip\\\"FROM \\\"source\\\" \\\n",
+ " PARTITIONED BY DAY\"\n",
+ "})\n",
+ " \n",
+ "headers = {'Content-Type': 'application/json'}\n",
+ "\n",
+ "response = requests.request(http_method, druid_host+endpoint, headers=headers, data=payload)\n",
+ "ingestion_taskId_response = response\n",
+ "ingestion_taskId = json.loads(ingestion_taskId_response.text)['taskId']\n",
+ "\n",
+ "print(f\"\\nInserting data into the table named {dataSource1}\")\n",
Review Comment:
Updated.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] writer-jill commented on a diff in pull request #13718: Nested columns tutorial
Posted by "writer-jill (via GitHub)" <gi...@apache.org>.
writer-jill commented on code in PR #13718:
URL: https://github.com/apache/druid/pull/13718#discussion_r1090537754
##########
examples/quickstart/jupyter-notebooks/nested-columns-tutorial.ipynb:
##########
@@ -0,0 +1,517 @@
+{
+ "cells": [
+ {
+ "cell_type": "markdown",
+ "id": "ad4e60b6",
+ "metadata": {
+ "tags": []
+ },
+ "source": [
+ "# Working with nested columns\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",
+ "This tutorial demonstrates how to work with [nested columns](https://druid.apache.org/docs/latest/querying/nested-columns.html) in Apache Druid.\n",
+ "\n",
+ "Druid stores nested data structures in `COMPLEX<json>` columns. In this tutorial you perform the following tasks:\n",
Review Comment:
Updated.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org