This is an automated email from the ASF dual-hosted git repository.
Jackie-Jiang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/pinot.git
The following commit(s) were added to refs/heads/master by this push:
new 810d7985b7e Add Jupyter JupySQL tutorial for querying Pinot (#19374)
810d7985b7e is described below
commit 810d7985b7ec485a455d443310a7bce83205ebd5
Author: Sruthi <[email protected]>
AuthorDate: Mon Sep 14 18:36:29 2026 -0700
Add Jupyter JupySQL tutorial for querying Pinot (#19374)
---
contrib/jupyter-jupysql/README.md | 76 +++++++++
contrib/jupyter-jupysql/pinot_jupysql_eda.ipynb | 215 ++++++++++++++++++++++++
contrib/jupyter-jupysql/requirements.txt | 25 +++
3 files changed, 316 insertions(+)
diff --git a/contrib/jupyter-jupysql/README.md
b/contrib/jupyter-jupysql/README.md
new file mode 100644
index 00000000000..1626b799bb2
--- /dev/null
+++ b/contrib/jupyter-jupysql/README.md
@@ -0,0 +1,76 @@
+<!--
+
+ Licensed to the Apache Software Foundation (ASF) under one
+ or more contributor license agreements. See the NOTICE file
+ distributed with this work for additional information
+ regarding copyright ownership. The ASF licenses this file
+ to you under the Apache License, Version 2.0 (the
+ "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing,
+ software distributed under the License is distributed on an
+ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ KIND, either express or implied. See the License for the
+ specific language governing permissions and limitations
+ under the License.
+
+-->
+
+# Query Pinot from Jupyter with JupySQL
+
+This example shows how to query Apache Pinot from a Jupyter notebook using
+[JupySQL](https://jupysql.ploomber.io/) and the
[pinotdb](https://pypi.org/project/pinotdb/)
+Python client. It is meant for local EDA: SQL magics, pandas DataFrames, and
simple plots.
+
+Related issue: https://github.com/apache/pinot/issues/10160
+
+## Prerequisites
+
+A running batch quickstart (loads the `baseballStats` table). The broker SQL
+endpoint is **port 8000** (not 8099, which appears in some older client
snippets).
+The controller UI is port 9000.
+
+### Option A — local binary (this checkout)
+
+From the Pinot repo root, after `./mvnw clean install -DskipTests -Pbin-dist`:
+
+```bash
+./build/bin/quick-start-batch.sh
+```
+
+### Option B — Docker
+
+```bash
+docker run --name pinot-quickstart \
+ -p 2123:2123 -p 9000:9000 -p 8000:8000 \
+ -d apachepinot/pinot:latest QuickStart -type batch
+```
+
+Wait until the controller UI at http://localhost:9000 is up.
+
+## Run the notebook
+
+```bash
+cd contrib/jupyter-jupysql
+python3 -m venv .venv
+source .venv/bin/activate
+pip install -r requirements.txt
+jupyter notebook pinot_jupysql_eda.ipynb
+```
+
+Connection string used in the notebook (broker **8000**, controller **9000**).
+The engine is created with `use_multistage_engine=true` so JupySQL `%sqlplot`
+CTEs are accepted:
+
+```text
+pinot://localhost:8000/query/sql?controller=http://localhost:9000/
+```
+
+To execute all cells headlessly (quickstart must already be running):
+
+```bash
+jupyter nbconvert --to notebook --execute pinot_jupysql_eda.ipynb --output
pinot_jupysql_eda.executed.ipynb
+```
diff --git a/contrib/jupyter-jupysql/pinot_jupysql_eda.ipynb
b/contrib/jupyter-jupysql/pinot_jupysql_eda.ipynb
new file mode 100644
index 00000000000..34a2c97b0b4
--- /dev/null
+++ b/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 total_runs"
+ ],
+ "execution_count": null,
+ "outputs": [],
+ "id": "sqlplot-bar"
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "## Keep results for EDA or modeling\n",
+ "\n",
+ "The DataFrame is a normal pandas object. Use it for further EDA or as
features\n",
+ "for a model — training a model is out of scope for this tutorial."
+ ],
+ "id": "eda-md"
+ },
+ {
+ "cell_type": "code",
+ "metadata": {},
+ "source": [
+ "player_runs = %sql SELECT playerName, SUM(runs) AS sum_runs,
SUM(homeRuns) AS sum_hr FROM baseballStats WHERE yearID >= 2000 GROUP BY
playerName ORDER BY sum_runs DESC LIMIT 20\n",
+ "\n",
+ "print(player_runs.dtypes)\n",
+ "player_runs.head()"
+ ],
+ "execution_count": null,
+ "outputs": [],
+ "id": "eda-code"
+ }
+ ],
+ "metadata": {
+ "kernelspec": {
+ "display_name": "Python 3",
+ "language": "python",
+ "name": "python3"
+ },
+ "language_info": {
+ "name": "python",
+ "pygments_lexer": "ipython3"
+ }
+ },
+ "nbformat": 4,
+ "nbformat_minor": 5
+}
\ No newline at end of file
diff --git a/contrib/jupyter-jupysql/requirements.txt
b/contrib/jupyter-jupysql/requirements.txt
new file mode 100644
index 00000000000..06d783b68de
--- /dev/null
+++ b/contrib/jupyter-jupysql/requirements.txt
@@ -0,0 +1,25 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+pinotdb>=9.1.0
+jupysql>=0.10.0
+pandas>=2.0
+matplotlib>=3.8
+jupyter>=1.0
+sqlalchemy>=2.0
+ipykernel>=6.0
+nbconvert>=7.0
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
