{ "cells": [ { "cell_type": "markdown", "id": "91edcf7b", "metadata": {}, "source": [ "# %%bqsql cell magics\n", "\n", "The BigQuery DataFrames (aka BigFrames) package provides a `%%bqsql` cell magics for Jupyter environments.\n", "\n", "To use it, first activate the extension:" ] }, { "cell_type": "code", "execution_count": 1, "id": "98cd0489", "metadata": {}, "outputs": [], "source": [ "%load_ext bigframes" ] }, { "cell_type": "markdown", "id": "f18fdc63", "metadata": {}, "source": [ "Now, use the magics by including SQL in the body." ] }, { "cell_type": "code", "execution_count": 2, "id": "269c5862", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " Query processed 0 Bytes. [Job bigframes-dev:US.job_UVe7FsupxF3CbYuLcLT7fpw9dozg details]\n", " " ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "application/vnd.jupyter.widget-view+json": { "model_id": "1e2fb7b019754d31b11323a054f97f47", "version_major": 2, "version_minor": 1 }, "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
stategenderyearnamenumber
0HIF1999Ariana10
1HIF2002Jordyn10
2HIF2006Mya10
3HIF2010Jordyn10
4HIM1921Nobuo10
5HIM1925Ralph10
6HIM1926Hisao10
7HIM1927Moses10
8HIM1933Larry10
9HIM1933Alfredo10
\n", "

10 rows × 5 columns

\n", "
[5552452 rows x 5 columns in total]" ], "text/plain": [ "state gender year name number\n", " HI F 1999 Ariana 10\n", " HI F 2002 Jordyn 10\n", " HI F 2006 Mya 10\n", " HI F 2010 Jordyn 10\n", " HI M 1921 Nobuo 10\n", " HI M 1925 Ralph 10\n", " HI M 1926 Hisao 10\n", " HI M 1927 Moses 10\n", " HI M 1933 Larry 10\n", " HI M 1933 Alfredo 10\n", "...\n", "\n", "[5552452 rows x 5 columns]" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%%bqsql\n", "SELECT * FROM `bigquery-public-data.usa_names.usa_1910_2013`" ] }, { "cell_type": "markdown", "id": "8771e10f", "metadata": {}, "source": [ "The output DataFrame can be saved to a variable." ] }, { "cell_type": "code", "execution_count": 3, "id": "30bb6327", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " Query processed 0 Bytes. [Job bigframes-dev:US.c142adf3-cd95-42da-bbdc-c176b36b934f details]\n", " " ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%%bqsql mydf\n", "SELECT * FROM `bigquery-public-data.usa_names.usa_1910_2013`" ] }, { "cell_type": "markdown", "id": "533e2e9e", "metadata": {}, "source": [ "You can chain cells together using format strings. DataFrame objects are automatically turned into table expressions." ] }, { "cell_type": "code", "execution_count": 4, "id": "6a8a8123", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " Query processed 88.1 MB in a moment of slot time.\n", " " ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "application/vnd.jupyter.widget-view+json": { "model_id": "c4889de9296440428de90defb5c58070", "version_major": 2, "version_minor": 1 }, "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
total_countname
0304036Tracy
1293876Travis
2203784Troy
3150127Trevor
496397Tristan
589996Tracey
665546Trinity
750112Traci
849657Trenton
945692Trent
\n", "

10 rows × 2 columns

\n", "
[238 rows x 2 columns in total]" ], "text/plain": [ " total_count name\n", "0 304036 Tracy\n", "1 293876 Travis\n", "2 203784 Troy\n", "3 150127 Trevor\n", "4 96397 Tristan\n", "5 89996 Tracey\n", "6 65546 Trinity\n", "7 50112 Traci\n", "8 49657 Trenton\n", "9 45692 Trent\n", "...\n", "\n", "[238 rows x 2 columns]" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%%bqsql\n", "SELECT sum(number) as total_count, name\n", "FROM {mydf}\n", "WHERE name LIKE 'Tr%'\n", "GROUP BY name\n", "ORDER BY total_count DESC" ] }, { "cell_type": "code", "execution_count": null, "id": "d2a17078", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "venv", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.10.18" } }, "nbformat": 4, "nbformat_minor": 5 }