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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
