JupySQL: lepší SQL v Jupyteru

Mít SQL a Python pěkně pohromadě v jednom notebooku je sen mnoha datových analytiků. Naštěstí to už dávno není sen. Protože možností, jak pohodlně pracovat s SQL v Jupyteru je více.

Můžete si vyzkoušet např. tento.

V tomto článku se podíváme na rozšíření (jupyter extension) pro Jupyter Notebook a Jupyter Lab. Jmenuje se JupySQL. Sami autoři pro něj používají krátký a výstižný claim Lepší SQL v Jupyteru.

Proč SQL

SQL syntaxe je pro mnoho uživatelů příjemnější, než syntaxe Pythonu a Pandas.

Často je také efektivnější zpracovat dotaz přímo v databázi, než data stahovat do dataframu a pak nad ním provádět paměťově náročné operace.

Úplně nejlepší je, když můžete využít přednosti SQL a Pythonu na jednom místě, v Jupyter Notebooku. A přesně to nabízí JupySQL.

Hlavní výhody JupySQL

• Snadná práce s SQL v Jupyteru
• Podpora databází - všechny, které podporují Python Database API
• Podpora Pandas dataframe
• Základní vizualizace - využívá knihovnu Matplotlib
• Možnost využití parametrů - díky Jinja templates

Instalace

Potřebné knihovny nainstalujete přes pip.

Následující příklady využívají lokální databázi DuckDB. Proto si nainstalujte potřebnou knihovnu.

pip install jupysql duckdb-engine matplotlib

Pokud využíváte distribuci Anaconda, použijte conda.

conda install -c conda-forge jupysql duckdb-engine matplotlib

Aktivace Jupyter rozšíření

Vytvořte nový Jupyter notebook. Jednotlivé příklady pište do samostatných buněk.

1. Nahrajte rozšíření JupySQL,
2. nastavte automatický výstup do Pandas dataframe
3. a vytvořte místní DuckDB databázi. Soubor se vytvoří v adresáři, ve kterém jste spustili Jupyter.

%load_ext sql
%config SqlMagic.autopandas = True
%sql duckdb:///./zoo.ddb

Načtení dat

DuckDB umí načítat data z lokálního a vzdáleného úložiště.

V následujícím příkladu načtete vzorová data o sponzoringu zvířat v pražské zoo, která jsou ve formátu CSV. Z načtených dat se vytvoří tabulka zoo.

Všimněte si 2 magických příkazů:

1. %%time - Ukáže, jak dlouho trvalo vykonání příkazu.
2. %%sql - Zbytek celé Jupyter buňky je chápán jako SQL a proto bude zpracován pomocí JupySQL.

%%time
%%sql
CREATE OR REPLACE TABLE zoo
AS
SELECT * FROM "https://www.lovelydata.cz/media/files/zoo.csv";

Data Analytics Pass

Staňte se datovým analytikem. Výhodný balík 7 kurzů od profíků z oboru. SQL, Python, Pandas, R, Tableau, Power BI a Strojové učení.

Prozkoumat

Zobrazení tabulek v databázi

JupySQL má několik šikovných příkazů. Například ten, který vypíše seznam tabulek v databázi.

%sqlcmd tables

Zobrazení sloupců tabulky

Následující příkaz zobrazí strukturu (sloupce) tabulky zoo.

%sqlcmd columns --table zoo

Zobrazení souhrných informací o datech

Tento příkaz poskytne základní přehled o datech v tabulce zoo.

%sqlcmd profile --table zoo

Funguje podobně jako pandas df.describe().

Zobrazení dat

Díky JupySQL můžete v Jupyteru používat SQL úplně stejně, jako v něm používáte např. Python.

Zobrazte vzorek pěti záznamů z právě načtených dat.

%%sql
FROM zoo
USING SAMPLE(5);

Všimněte si použití SQL friendly syntaxe. Pockud nezadáte SELECT, DuckDB to chápe jako SELECT *.

Python pro každý den

Jak si užít Python a nezabloudit v kódu. Usnadněte si práci a místo sebe raději zaměstnejte Python. Návody a příklady z praxe, které vám ušetří spoustu času.

Prozkoumat

Magic commands

JupySQL podporuje použití 2 typů kouzelných příkazy Jupyteru:

1. %sql - jednořádkový
2. %%sql - celá buňka

Následující 2 příklady dělají totéž:

%sql FROM zoo LIMIT 3

%%sql
FROM zoo LIMIT 3

Počty zvířat podle tříd

Kolik je zvířat v jednotlivých třídách?

%%sql
FROM zoo
SELECT trida_cz, COUNT(*) AS "Počet zvířat"
GROUP BY 1
ORDER BY 2 DESC;

Opět, používáte friendly SQL a proto může být SELECT až za klauzulí FROM.

Zobrazení sloupcového grafu

JupySQL využívá pro vizualizaci dat knihovnu matplotlib.

Nečekejte nijak bohatou nabídku grafů. Pro rychlou analýzu to ale v mnoha případech bude stačit. A hlavně, syntaxe není složitá.

Následující graf ukazuje počty zvířat v jednotlivých třídách. Stejně, jako předchozí SQL.

%sqlplot bar --table zoo --column trida_cz

Zobrazení histogramu

JupySQL nabízí zobrazení histogramu. Zkuste si přehled podle ceny rozdělený do 5 intervalů.

%sqlplot histogram --table zoo --column cena --bins 5

Vidíte, že zájemci o sponzorství zvířat se ve většině případů vejdou do deseti tisíc korun.

Zobrazení krabicového grafu

Pro lepší přehled podle ceny můžete využít krabicový graf (box plot).

%sqlplot boxplot --table zoo --column cena

Výsledek je zkreslený záznamy, které mají vysokou cenu. Tyto záznamy potřebujete odfiltrovat.

Jednou z možností je použití databázové funkce quantile_disc, díky které nebudete brát v úvahu hodnoty nad 90. percentilem.

Zobrazení krabicového grafu - 90. percentil

Přehled podle ceny - záznamy pod 90. percentilem

%%sql --save bez_outliers --no-execute
FROM zoo
WHERE cena <= (SELECT quantile_disc(cena, 0.9) FROM zoo)

Všimněte si použití parametrů --save a --no-execute.

• --save: JupySQL si zapamatuje SQL, které můžete použít později.
• --no-execute: Dotaz se ihned nespustí, ale počká, až ho zavoláte. Např. při zobrazení krabicového grafu.

%sqlplot boxplot --table bez_outliers --column cena

Kontrola

Zkontrolujte pomocí agregačních funkcí:

%%sql
SELECT
    avg(cena) AS "Průměr",
    median(cena) AS "Medián",
    mode(cena) AS "Modus (nejčastější výskyt)"
FROM zoo

Vizualizace pro analytiky

Naučte se vizualizovat a prezentovat data v Pythonu. V tomto kurzu získáte pevné základy, díky kterým si usnadníte analýzu dat. Látka je vysvětlena pomocí srozumitelných příkladů na běžně používaných typech grafů, které by měl ovládat každý analytik.

Prozkoumat

Zobrazení koláčového grafu

Koláčový graf (pie chart) je ideální pro zobrazení podílů jednotlivých kategorií.

Ale pozor - pouze v případě, že kategorií není příliš mnoho, ideálně do 5. Při vyšším počtu se stává nepřehledným.

Ne všechna zvířata si je možné prohlédnout. Jaký je jejich podíl?

%sqlplot pie --table zoo --column k_prohlidce

Uložení do Dataframe

JupySQL podporuje pandas. Výsledek SQL dotazu si tak můžete uložit do dataframu.

To otevírá široké možnosti, protože můžete snadno ve svém workflow kombinovat SQL, Python, pandas a další knihovny.

%%sql
df << FROM zoo

Export dataframe do Excelu

Z dataframu si pak data snadno uložíte, např. do Excelu.

df.to_excel("zoo.xlsx", index=False)