JupySQL e chDB - ClickHouse Documentation

JupySQL é uma biblioteca Python que permite executar SQL em notebooks Jupyter e no shell do IPython. Neste guia, vamos aprender a consultar dados usando o chDB e o JupySQL.

Configuração

Vamos criar primeiro um ambiente virtual:

python -m venv .venv
source .venv/bin/activate

E então, vamos instalar o JupySQL, o IPython e o Jupyter Lab:

pip install jupysql ipython jupyterlab

Podemos usar o JupySQL no IPython, que pode ser iniciado executando:

ipython

Ou, no Jupyter Lab, executando:

jupyter lab

Se você estiver usando o Jupyter Lab, precisará criar um notebook antes de seguir com o restante do guia.

Baixando um conjunto de dados

Vamos usar o conjunto de dados tennis_atp de Jeff Sackmann, que contém metadados sobre jogadores e seu ranking ao longo do tempo. Vamos começar baixando os arquivos de ranking:

from urllib.request import urlretrieve

files = ['00s', '10s', '20s', '70s', '80s', '90s', 'current']
base = "https://raw.githubusercontent.com/JeffSackmann/tennis_atp/master"
for file in files:
  _ = urlretrieve(
    f"{base}/atp_rankings_{file}.csv",
    f"atp_rankings_{file}.csv",
  )

Configurando o chDB e o JupySQL

Em seguida, vamos importar o módulo dbapi do chDB:

from chdb import dbapi

E vamos criar uma conexão com o chDB. Todos os dados que persistirmos serão salvos no diretório atp.chdb:

conn = dbapi.connect(path="atp.chdb")

Agora, vamos carregar a magic sql e criar uma conexão com o chDB:

%load_ext sql
%sql conn --alias chdb

Em seguida, vamos mostrar o limite de exibição para que os resultados das consultas não sejam truncados:

%config SqlMagic.displaylimit = None

## Consultando dados em arquivos CSV Baixamos vários arquivos com o prefixo atp_rankings. Vamos usar a cláusula DESCRIBE para entender o esquema:

%%sql
DESCRIBE file('atp_rankings*.csv')
SETTINGS describe_compact_output=1,
         schema_inference_make_columns_nullable=0

+--------------+-------+
|     name     |  type |
+--------------+-------+
| ranking_date | Int64 |
|     rank     | Int64 |
|    player    | Int64 |
|    points    | Int64 |
+--------------+-------+

Também podemos escrever uma consulta SELECT diretamente nesses arquivos para ver como são os dados:

%sql SELECT * FROM file('atp_rankings*.csv') LIMIT 1

+--------------+------+--------+--------+
| ranking_date | rank | player | points |
+--------------+------+--------+--------+
|   20000110   |  1   | 101736 |  4135  |
+--------------+------+--------+--------+

O formato dos dados está meio estranho. Vamos ajustar essa data e usar a cláusula REPLACE para retornar a ranking_date ajustada:

%%sql
SELECT * REPLACE (
  toDate(parseDateTime32BestEffort(toString(ranking_date))) AS ranking_date
)
FROM file('atp_rankings*.csv')
LIMIT 10
SETTINGS schema_inference_make_columns_nullable=0

+--------------+------+--------+--------+
| ranking_date | rank | player | points |
+--------------+------+--------+--------+
|  2000-01-10  |  1   | 101736 |  4135  |
|  2000-01-10  |  2   | 102338 |  2915  |
|  2000-01-10  |  3   | 101948 |  2419  |
|  2000-01-10  |  4   | 103017 |  2184  |
|  2000-01-10  |  5   | 102856 |  2169  |
|  2000-01-10  |  6   | 102358 |  2107  |
|  2000-01-10  |  7   | 102839 |  1966  |
|  2000-01-10  |  8   | 101774 |  1929  |
|  2000-01-10  |  9   | 102701 |  1846  |
|  2000-01-10  |  10  | 101990 |  1739  |
+--------------+------+--------+--------+

Importando arquivos CSV no chDB

Agora vamos armazenar os dados desses arquivos CSV em uma tabela. O banco de dados padrão não persiste os dados em disco, então primeiro precisamos criar outro banco de dados:

%sql CREATE DATABASE atp

E agora vamos criar uma tabela chamada rankings, cujo esquema será derivado da estrutura dos dados nos arquivos CSV:

%%sql
CREATE TABLE atp.rankings
ENGINE=MergeTree
ORDER BY ranking_date AS
SELECT * REPLACE (
  toDate(parseDateTime32BestEffort(toString(ranking_date))) AS ranking_date
)
FROM file('atp_rankings*.csv')
SETTINGS schema_inference_make_columns_nullable=0

Vamos dar uma olhada rápida nos dados da nossa tabela:

%sql SELECT * FROM atp.rankings LIMIT 10

+--------------+------+--------+--------+
| ranking_date | rank | player | points |
+--------------+------+--------+--------+
|  2000-01-10  |  1   | 101736 |  4135  |
|  2000-01-10  |  2   | 102338 |  2915  |
|  2000-01-10  |  3   | 101948 |  2419  |
|  2000-01-10  |  4   | 103017 |  2184  |
|  2000-01-10  |  5   | 102856 |  2169  |
|  2000-01-10  |  6   | 102358 |  2107  |
|  2000-01-10  |  7   | 102839 |  1966  |
|  2000-01-10  |  8   | 101774 |  1929  |
|  2000-01-10  |  9   | 102701 |  1846  |
|  2000-01-10  |  10  | 101990 |  1739  |
+--------------+------+--------+--------+

Parece bom — a saída, como esperado, é a mesma de quando consultamos os arquivos CSV diretamente. Vamos seguir o mesmo processo para os metadados dos jogadores. Desta vez, os dados estão todos em um único arquivo CSV, então vamos baixá-lo:

_ = urlretrieve(
    f"{base}/atp_players.csv",
    "atp_players.csv",
)

Em seguida, crie uma tabela chamada players com base no conteúdo do arquivo CSV. Também vamos ajustar o campo dob para que ele seja do tipo Date32.

No ClickHouse, o tipo Date só oferece suporte a datas a partir de 1970. Como a coluna dob contém datas anteriores a 1970, usaremos o tipo Date32.

%%sql
CREATE TABLE atp.players
Engine=MergeTree
ORDER BY player_id AS
SELECT * REPLACE (
  makeDate32(
    toInt32OrNull(substring(toString(dob), 1, 4)),
    toInt32OrNull(substring(toString(dob), 5, 2)),
    toInt32OrNull(substring(toString(dob), 7, 2))
  )::Nullable(Date32) AS dob
)
FROM file('atp_players.csv')
SETTINGS schema_inference_make_columns_nullable=0

Quando isso terminar de executar, podemos dar uma olhada nos dados que ingerimos:

%sql SELECT * FROM atp.players LIMIT 10

+-----------+------------+-----------+------+------------+-----+--------+-------------+
| player_id | name_first | name_last | hand |    dob     | ioc | height | wikidata_id |
+-----------+------------+-----------+------+------------+-----+--------+-------------+
|   100001  |  Gardnar   |   Mulloy  |  R   | 1913-11-22 | USA |  185   |    Q54544   |
|   100002  |   Pancho   |   Segura  |  R   | 1921-06-20 | ECU |  168   |    Q54581   |
|   100003  |   Frank    |  Sedgman  |  R   | 1927-10-02 | AUS |  180   |   Q962049   |
|   100004  |  Giuseppe  |   Merlo   |  R   | 1927-10-11 | ITA |   0    |   Q1258752  |
|   100005  |  Richard   |  Gonzalez |  R   | 1928-05-09 | USA |  188   |    Q53554   |
|   100006  |   Grant    |   Golden  |  R   | 1929-08-21 | USA |  175   |   Q3115390  |
|   100007  |    Abe     |   Segal   |  L   | 1930-10-23 | RSA |   0    |   Q1258527  |
|   100008  |    Kurt    |  Nielsen  |  R   | 1930-11-19 | DEN |   0    |   Q552261   |
|   100009  |   Istvan   |   Gulyas  |  R   | 1931-10-14 | HUN |   0    |    Q51066   |
|   100010  |    Luis    |   Ayala   |  R   | 1932-09-18 | CHI |  170   |   Q1275397  |
+-----------+------------+-----------+------+------------+-----+--------+-------------+

Consultando o chDB

A ingestão de dados foi concluída; agora é hora da parte divertida: consultar os dados! Os tenistas recebem pontos com base em seu desempenho nos torneios que disputam. Os pontos de cada jogador ao longo de um período móvel de 52 semanas. Vamos escrever uma consulta que encontra o número máximo de pontos acumulados por cada jogador, junto com seu ranking naquele momento:

%%sql
SELECT name_first, name_last,
       max(points) as maxPoints,
       argMax(rank, points) as rank,
       argMax(ranking_date, points) as date
FROM atp.players
JOIN atp.rankings ON rankings.player = players.player_id
GROUP BY ALL
ORDER BY maxPoints DESC
LIMIT 10

+------------+-----------+-----------+------+------------+
| name_first | name_last | maxPoints | rank |    date    |
+------------+-----------+-----------+------+------------+
|   Novak    |  Djokovic |   16950   |  1   | 2016-06-06 |
|   Rafael   |   Nadal   |   15390   |  1   | 2009-04-20 |
|    Andy    |   Murray  |   12685   |  1   | 2016-11-21 |
|   Roger    |  Federer  |   12315   |  1   | 2012-10-29 |
|   Daniil   |  Medvedev |   10780   |  2   | 2021-09-13 |
|   Carlos   |  Alcaraz  |    9815   |  1   | 2023-08-21 |
|  Dominic   |   Thiem   |    9125   |  3   | 2021-01-18 |
|   Jannik   |   Sinner  |    8860   |  2   | 2024-05-06 |
|  Stefanos  | Tsitsipas |    8350   |  3   | 2021-09-20 |
| Alexander  |   Zverev  |    8240   |  4   | 2021-08-23 |
+------------+-----------+-----------+------+------------+

É bastante interessante que alguns dos jogadores desta lista tenham acumulado muitos pontos sem ocupar o 1º lugar com esse total de pontos.

Salvando consultas

Podemos salvar consultas usando o parâmetro --save na mesma linha da mágica %%sql. O parâmetro --no-execute significa que a execução da consulta será pulada.

%%sql --save best_points --no-execute
SELECT name_first, name_last,
       max(points) as maxPoints,
       argMax(rank, points) as rank,
       argMax(ranking_date, points) as date
FROM atp.players
JOIN atp.rankings ON rankings.player = players.player_id
GROUP BY ALL
ORDER BY maxPoints DESC

Ao executar uma consulta salva, ela é convertida em uma Common Table Expression (CTE) antes de ser executada. Na consulta a seguir, calculamos a pontuação máxima alcançada pelos jogadores quando estavam em 1º lugar no ranking:

%sql select * FROM best_points WHERE rank=1

+-------------+-----------+-----------+------+------------+
|  name_first | name_last | maxPoints | rank |    date    |
+-------------+-----------+-----------+------+------------+
|    Novak    |  Djokovic |   16950   |  1   | 2016-06-06 |
|    Rafael   |   Nadal   |   15390   |  1   | 2009-04-20 |
|     Andy    |   Murray  |   12685   |  1   | 2016-11-21 |
|    Roger    |  Federer  |   12315   |  1   | 2012-10-29 |
|    Carlos   |  Alcaraz  |    9815   |  1   | 2023-08-21 |
|     Pete    |  Sampras  |    5792   |  1   | 1997-08-11 |
|    Andre    |   Agassi  |    5652   |  1   | 1995-08-21 |
|   Lleyton   |   Hewitt  |    5205   |  1   | 2002-08-12 |
|   Gustavo   |  Kuerten  |    4750   |  1   | 2001-09-10 |
| Juan Carlos |  Ferrero  |    4570   |  1   | 2003-10-20 |
|    Stefan   |   Edberg  |    3997   |  1   | 1991-02-25 |
|     Jim     |  Courier  |    3973   |  1   | 1993-08-23 |
|     Ivan    |   Lendl   |    3420   |  1   | 1990-02-26 |
|     Ilie    |  Nastase  |     0     |  1   | 1973-08-27 |
+-------------+-----------+-----------+------+------------+

Fazendo consultas com parâmetros

Também podemos usar parâmetros em nossas consultas. Parâmetros são apenas variáveis comuns:

rank = 10

E então podemos usar a sintaxe {{variable}} na nossa consulta. A consulta a seguir identifica os jogadores que tiveram o menor intervalo de dias entre a primeira vez em que entraram no top 10 do ranking e a última vez em que ficaram no top 10 do ranking:

%%sql
SELECT name_first, name_last,
       MIN(ranking_date) AS earliest_date,
       MAX(ranking_date) AS most_recent_date,
       most_recent_date - earliest_date AS days,
       1 + (days/7) AS weeks
FROM atp.rankings
JOIN atp.players ON players.player_id = rankings.player
WHERE rank <= {{rank}}
GROUP BY ALL
ORDER BY days
LIMIT 10

+------------+-----------+---------------+------------------+------+-------+
| name_first | name_last | earliest_date | most_recent_date | days | weeks |
+------------+-----------+---------------+------------------+------+-------+
|    Alex    | Metreveli |   1974-06-03  |    1974-06-03    |  0   |   1   |
|   Mikael   |  Pernfors |   1986-09-22  |    1986-09-22    |  0   |   1   |
|   Felix    |  Mantilla |   1998-06-08  |    1998-06-08    |  0   |   1   |
|   Wojtek   |   Fibak   |   1977-07-25  |    1977-07-25    |  0   |   1   |
|  Thierry   |  Tulasne  |   1986-08-04  |    1986-08-04    |  0   |   1   |
|   Lucas    |  Pouille  |   2018-03-19  |    2018-03-19    |  0   |   1   |
|    John    | Alexander |   1975-12-15  |    1975-12-15    |  0   |   1   |
|  Nicolas   |   Massu   |   2004-09-13  |    2004-09-20    |  7   |   2   |
|   Arnaud   |  Clement  |   2001-04-02  |    2001-04-09    |  7   |   2   |
|  Ernests   |   Gulbis  |   2014-06-09  |    2014-06-23    |  14  |   3   |
+------------+-----------+---------------+------------------+------+-------+

Plotando histogramas

O JupySQL também tem recursos limitados de criação de gráficos. Podemos criar box plots ou histogramas. Vamos criar um histograma, mas primeiro vamos escrever (e salvar) uma consulta que calcula as posições no top 100 que cada jogador alcançou. Poderemos usar isso para criar um histograma que mostra quantos jogadores alcançaram cada posição:

%%sql --save players_per_rank --no-execute
select distinct player, rank
FROM atp.rankings
WHERE rank <= 100

Em seguida, podemos criar um histograma da seguinte forma:

from sql.ggplot import ggplot, geom_histogram, aes

plot = (
  ggplot(
    table="players_per_rank",
    with_="players_per_rank",
    mapping=aes(x="rank", fill="#69f0ae", color="#fff"),
  ) + geom_histogram(bins=100)
)

​Configuração

​Baixando um conjunto de dados

​Configurando o chDB e o JupySQL

​Importando arquivos CSV no chDB

​Consultando o chDB

​Salvando consultas

​Fazendo consultas com parâmetros

​Plotando histogramas

Configuração

Baixando um conjunto de dados

Configurando o chDB e o JupySQL

Importando arquivos CSV no chDB

Consultando o chDB

Salvando consultas

Fazendo consultas com parâmetros

Plotando histogramas