You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@pinot.apache.org by "codewithsruthi (via GitHub)" <gi...@apache.org> on 2026/08/26 22:35:12 UTC
[PR] Add Jupyter JupySQL tutorial for querying Pinot [pinot]
codewithsruthi opened a new pull request, #19374:
URL: https://github.com/apache/pinot/pull/19374
Adds a runnable Jupyter notebook that queries the batch quickstart with JupySQL and pinotdb, covering SQL magics, plots, and pandas DataFrames for EDA.
The files live under `contrib/jupyter-jupysql/` so this does not trigger the Java CI matrix. Broker port 8000 is documented (not the older 8099 snippets). The notebook was executed against a local batch quickstart (`baseballStats`, 97889 rows).
A matching GitBook page is in pinot-contrib/pinot-docs (linked from the docs PR).
closes: #10160
---
##### Was generative AI tooling used to co-author this PR?
- [x] Yes — Cursor Grok 4.6
Generated-by: Cursor Grok 4.6
---
Drafted-by: Cursor Grok 4.6 (no human review before posting)
Made with [Cursor](https://cursor.com)
--
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@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
Re: [PR] Add Jupyter JupySQL tutorial for querying Pinot [pinot]
Posted by "Jackie-Jiang (via GitHub)" <gi...@apache.org>.
Jackie-Jiang merged PR #19374:
URL: https://github.com/apache/pinot/pull/19374
--
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@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
Re: [PR] Add Jupyter JupySQL tutorial for querying Pinot [pinot]
Posted by "codewithsruthi (via GitHub)" <gi...@apache.org>.
codewithsruthi commented on code in PR #19374:
URL: https://github.com/apache/pinot/pull/19374#discussion_r4010076640
##########
contrib/jupyter-jupysql/pinot_jupysql_eda.ipynb:
##########
@@ -0,0 +1,215 @@
+{
+ "cells": [
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "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."
+ ],
+ "id": "license"
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "# Query Apache Pinot from Jupyter with JupySQL\n",
+ "\n",
+ "This notebook queries a local Pinot **batch quickstart** from Jupyter using\n",
+ "[JupySQL](https://jupysql.ploomber.io/) SQL magics and [pinotdb](https://pypi.org/project/pinotdb/).\n",
+ "\n",
+ "It covers:\n",
+ "\n",
+ "1. Connecting to Pinot from a notebook\n",
+ "2. Running SQL (`SELECT`, `GROUP BY`, `ORDER BY`)\n",
+ "3. Plotting query results\n",
+ "4. Keeping results as a pandas DataFrame for later EDA or modeling\n",
+ "\n",
+ "**Start Pinot first** (broker on port **8000**, controller on **9000**):\n",
+ "\n",
+ "```bash\n",
+ "./build/bin/quick-start-batch.sh\n",
+ "```\n",
+ "\n",
+ "or\n",
+ "\n",
+ "```bash\n",
+ "docker run --name pinot-quickstart -p 2123:2123 -p 9000:9000 -p 8000:8000 -d apachepinot/pinot:latest QuickStart -type batch\n",
+ "```\n",
+ "\n",
+ "The quickstart loads `baseballStats`. See `README.md` in this directory for install steps."
+ ],
+ "id": "intro"
+ },
+ {
+ "cell_type": "code",
+ "metadata": {},
+ "source": [
+ "from sqlalchemy import create_engine\n",
+ "import matplotlib.pyplot as plt\n",
+ "\n",
+ "%matplotlib inline\n",
+ "%load_ext sql\n",
+ "\n",
+ "%config SqlMagic.autopandas = True\n",
+ "%config SqlMagic.feedback = False\n",
+ "%config SqlMagic.displaycon = False\n",
+ "\n",
+ "# Batch / Docker quickstart: broker 8000, controller 9000 (not 8099).\n",
+ "# Multi-stage is required for JupySQL %sqlplot, which rewrites plots as CTEs.\n",
+ "engine = create_engine(\n",
+ " \"pinot://localhost:8000/query/sql?controller=http://localhost:9000/\",\n",
+ " connect_args={\"use_multistage_engine\": \"true\"},\n",
+ ")\n",
+ "%sql engine"
+ ],
+ "execution_count": null,
+ "outputs": [],
+ "id": "imports"
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "## Query Pinot with SQL magics\n",
+ "\n",
+ "`%%sql` sends the statement to the Pinot broker (`POST /query/sql`).\n",
+ "Use `LIMIT` on exploratory scans. Aggregations on `baseballStats` are cheap."
+ ],
+ "id": "query-sql"
+ },
+ {
+ "cell_type": "code",
+ "metadata": {},
+ "source": [
+ "%%sql\n",
+ "SELECT playerName, teamID, yearID, runs, homeRuns\n",
+ "FROM baseballStats\n",
+ "LIMIT 5"
+ ],
+ "execution_count": null,
+ "outputs": [],
+ "id": "select-limit"
+ },
+ {
+ "cell_type": "code",
+ "metadata": {},
+ "source": [
+ "%%sql\n",
+ "SELECT playerName, SUM(runs) AS sum_runs\n",
+ "FROM baseballStats\n",
+ "WHERE yearID >= 2000\n",
+ "GROUP BY playerName\n",
+ "ORDER BY sum_runs DESC\n",
+ "LIMIT 10"
+ ],
+ "execution_count": null,
+ "outputs": [],
+ "id": "group-by"
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "## Plot query results\n",
+ "\n",
+ "Assign a `%sql` result to a variable. With `SqlMagic.autopandas = True` you get a\n",
+ "DataFrame you can plot with matplotlib (or pass into `%sqlplot`)."
+ ],
+ "id": "plot-md"
+ },
+ {
+ "cell_type": "code",
+ "metadata": {},
+ "source": [
+ "top_teams = %sql SELECT teamID, SUM(runs) AS total_runs FROM baseballStats GROUP BY teamID ORDER BY total_runs DESC LIMIT 10\n",
+ "\n",
+ "ax = top_teams.plot.bar(x=\"teamID\", y=\"total_runs\", legend=False)\n",
+ "ax.set_title(\"Top 10 teams by total runs (baseballStats)\")\n",
+ "ax.set_xlabel(\"teamID\")\n",
+ "ax.set_ylabel(\"total runs\")\n",
+ "plt.tight_layout()\n",
+ "plt.show()"
+ ],
+ "execution_count": null,
+ "outputs": [],
+ "id": "plot-code"
+ },
+ {
+ "cell_type": "code",
+ "metadata": {},
+ "source": [
+ "%%sql --save top_teams_sql\n",
+ "SELECT teamID, SUM(runs) AS total_runs\n",
+ "FROM baseballStats\n",
+ "GROUP BY teamID\n",
+ "ORDER BY total_runs DESC\n",
+ "LIMIT 10"
+ ],
+ "execution_count": null,
+ "outputs": [],
+ "id": "sqlplot"
+ },
+ {
+ "cell_type": "code",
+ "metadata": {},
+ "source": [
+ "%sqlplot bar --table top_teams_sql --column teamID"
Review Comment:
Fixed in 4a78f2a — `%sqlplot` now passes `--column teamID total_runs` so the bars show run totals instead of counting grouped rows.
--
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@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
Re: [PR] Add Jupyter JupySQL tutorial for querying Pinot [pinot]
Posted by "codewithsruthi (via GitHub)" <gi...@apache.org>.
codewithsruthi commented on PR #19374:
URL: https://github.com/apache/pinot/pull/19374#issuecomment-5630876786
@Jackie-Jiang could I get your review, please, for this pull request? Please let me know if you have any feedback. Thank you.
--
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@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
Re: [PR] Add Jupyter JupySQL tutorial for querying Pinot [pinot]
Posted by "xiangfu0 (via GitHub)" <gi...@apache.org>.
xiangfu0 commented on PR #19374:
URL: https://github.com/apache/pinot/pull/19374#issuecomment-5673696823
Documentation follow-up: https://github.com/pinot-contrib/pinot-docs/pull/1010 adds the Jupyter + JupySQL getting-started tutorial, links it from the Python client page and tutorial navigation, and points to the runnable notebook added here.
--
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@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
Re: [PR] Add Jupyter JupySQL tutorial for querying Pinot [pinot]
Posted by "codewithsruthi (via GitHub)" <gi...@apache.org>.
codewithsruthi commented on PR #19374:
URL: https://github.com/apache/pinot/pull/19374#issuecomment-5445355763
Requesting review from @Jackie-Jiang and @xiangfu0 (Apache Pinot PMC / committers). Pinot CONTRIBUTING asks for at least two committers; you both commented on #10160, which this PR closes.
---
Drafted-by: Cursor Grok 4.6 (no human review before posting)
--
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@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
Re: [PR] Add Jupyter JupySQL tutorial for querying Pinot [pinot]
Posted by "Jackie-Jiang (via GitHub)" <gi...@apache.org>.
Jackie-Jiang commented on code in PR #19374:
URL: https://github.com/apache/pinot/pull/19374#discussion_r3994537822
##########
contrib/jupyter-jupysql/pinot_jupysql_eda.ipynb:
##########
@@ -0,0 +1,215 @@
+{
+ "cells": [
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "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."
+ ],
+ "id": "license"
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "# Query Apache Pinot from Jupyter with JupySQL\n",
+ "\n",
+ "This notebook queries a local Pinot **batch quickstart** from Jupyter using\n",
+ "[JupySQL](https://jupysql.ploomber.io/) SQL magics and [pinotdb](https://pypi.org/project/pinotdb/).\n",
+ "\n",
+ "It covers:\n",
+ "\n",
+ "1. Connecting to Pinot from a notebook\n",
+ "2. Running SQL (`SELECT`, `GROUP BY`, `ORDER BY`)\n",
+ "3. Plotting query results\n",
+ "4. Keeping results as a pandas DataFrame for later EDA or modeling\n",
+ "\n",
+ "**Start Pinot first** (broker on port **8000**, controller on **9000**):\n",
+ "\n",
+ "```bash\n",
+ "./build/bin/quick-start-batch.sh\n",
+ "```\n",
+ "\n",
+ "or\n",
+ "\n",
+ "```bash\n",
+ "docker run --name pinot-quickstart -p 2123:2123 -p 9000:9000 -p 8000:8000 -d apachepinot/pinot:latest QuickStart -type batch\n",
+ "```\n",
+ "\n",
+ "The quickstart loads `baseballStats`. See `README.md` in this directory for install steps."
+ ],
+ "id": "intro"
+ },
+ {
+ "cell_type": "code",
+ "metadata": {},
+ "source": [
+ "from sqlalchemy import create_engine\n",
+ "import matplotlib.pyplot as plt\n",
+ "\n",
+ "%matplotlib inline\n",
+ "%load_ext sql\n",
+ "\n",
+ "%config SqlMagic.autopandas = True\n",
+ "%config SqlMagic.feedback = False\n",
+ "%config SqlMagic.displaycon = False\n",
+ "\n",
+ "# Batch / Docker quickstart: broker 8000, controller 9000 (not 8099).\n",
+ "# Multi-stage is required for JupySQL %sqlplot, which rewrites plots as CTEs.\n",
+ "engine = create_engine(\n",
+ " \"pinot://localhost:8000/query/sql?controller=http://localhost:9000/\",\n",
+ " connect_args={\"use_multistage_engine\": \"true\"},\n",
+ ")\n",
+ "%sql engine"
+ ],
+ "execution_count": null,
+ "outputs": [],
+ "id": "imports"
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "## Query Pinot with SQL magics\n",
+ "\n",
+ "`%%sql` sends the statement to the Pinot broker (`POST /query/sql`).\n",
+ "Use `LIMIT` on exploratory scans. Aggregations on `baseballStats` are cheap."
+ ],
+ "id": "query-sql"
+ },
+ {
+ "cell_type": "code",
+ "metadata": {},
+ "source": [
+ "%%sql\n",
+ "SELECT playerName, teamID, yearID, runs, homeRuns\n",
+ "FROM baseballStats\n",
+ "LIMIT 5"
+ ],
+ "execution_count": null,
+ "outputs": [],
+ "id": "select-limit"
+ },
+ {
+ "cell_type": "code",
+ "metadata": {},
+ "source": [
+ "%%sql\n",
+ "SELECT playerName, SUM(runs) AS sum_runs\n",
+ "FROM baseballStats\n",
+ "WHERE yearID >= 2000\n",
+ "GROUP BY playerName\n",
+ "ORDER BY sum_runs DESC\n",
+ "LIMIT 10"
+ ],
+ "execution_count": null,
+ "outputs": [],
+ "id": "group-by"
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "## Plot query results\n",
+ "\n",
+ "Assign a `%sql` result to a variable. With `SqlMagic.autopandas = True` you get a\n",
+ "DataFrame you can plot with matplotlib (or pass into `%sqlplot`)."
+ ],
+ "id": "plot-md"
+ },
+ {
+ "cell_type": "code",
+ "metadata": {},
+ "source": [
+ "top_teams = %sql SELECT teamID, SUM(runs) AS total_runs FROM baseballStats GROUP BY teamID ORDER BY total_runs DESC LIMIT 10\n",
+ "\n",
+ "ax = top_teams.plot.bar(x=\"teamID\", y=\"total_runs\", legend=False)\n",
+ "ax.set_title(\"Top 10 teams by total runs (baseballStats)\")\n",
+ "ax.set_xlabel(\"teamID\")\n",
+ "ax.set_ylabel(\"total runs\")\n",
+ "plt.tight_layout()\n",
+ "plt.show()"
+ ],
+ "execution_count": null,
+ "outputs": [],
+ "id": "plot-code"
+ },
+ {
+ "cell_type": "code",
+ "metadata": {},
+ "source": [
+ "%%sql --save top_teams_sql\n",
+ "SELECT teamID, SUM(runs) AS total_runs\n",
+ "FROM baseballStats\n",
+ "GROUP BY teamID\n",
+ "ORDER BY total_runs DESC\n",
+ "LIMIT 10"
+ ],
+ "execution_count": null,
+ "outputs": [],
+ "id": "sqlplot"
+ },
+ {
+ "cell_type": "code",
+ "metadata": {},
+ "source": [
+ "%sqlplot bar --table top_teams_sql --column teamID"
Review Comment:
[P2] Plot `total_runs` instead of counting grouped rows.
Passing only `teamID` selects JupySQL's frequency-count mode. Since `top_teams_sql` already groups by team, every bar has height 1 and `total_runs` is ignored. Pass both columns to plot the run totals, as in the preceding pandas example:
```suggestion
"%sqlplot bar --table top_teams_sql --column teamID total_runs"
```
Reproduced with JupySQL 0.11.1: input totals `[30, 7]` plotted as `[1, 1]`; adding `total_runs` produced `[30, 7]`.
--
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@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org