> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.
# JupySQL e chDB
> Como instalar o chDB for Bun
export const Image = ({img, alt, size = "lg"}) => {
const normalizedSize = ["sm", "md", "lg"].includes(size) ? size : "lg";
return
;
};
[JupySQL](https://jupysql.readthedocs.io/en/latest/quick-start.html) é 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:
```bash theme={null}
python -m venv .venv
source .venv/bin/activate
```
E então, vamos instalar o JupySQL, o IPython e o Jupyter Lab:
```bash theme={null}
pip install jupysql ipython jupyterlab
```
Podemos usar o JupySQL no IPython, que pode ser iniciado executando:
```bash theme={null}
ipython
```
Ou, no Jupyter Lab, executando:
```bash theme={null}
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](https://github.com/JeffSackmann/tennis_atp), que contém metadados sobre jogadores e seu ranking ao longo do tempo.
Vamos começar baixando os arquivos de ranking:
```python theme={null}
from urllib.request import urlretrieve
```
```python theme={null}
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:
```python theme={null}
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`:
```python theme={null}
conn = dbapi.connect(path="atp.chdb")
```
Agora, vamos carregar a magic `sql` e criar uma conexão com o chDB:
```python theme={null}
%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:
```python theme={null}
%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:
```python theme={null}
%%sql
DESCRIBE file('atp_rankings*.csv')
SETTINGS describe_compact_output=1,
schema_inference_make_columns_nullable=0
```
```text theme={null}
+--------------+-------+
| 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:
```python theme={null}
%sql SELECT * FROM file('atp_rankings*.csv') LIMIT 1
```
```text theme={null}
+--------------+------+--------+--------+
| 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:
```python theme={null}
%%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
```
```text theme={null}
+--------------+------+--------+--------+
| 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:
```python theme={null}
%sql CREATE DATABASE atp
```
E agora vamos criar uma tabela chamada `rankings`, cujo esquema será derivado da estrutura dos dados nos arquivos CSV:
```python theme={null}
%%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:
```python theme={null}
%sql SELECT * FROM atp.rankings LIMIT 10
```
```text theme={null}
+--------------+------+--------+--------+
| 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:
```python theme={null}
_ = 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`.
```python theme={null}
%%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:
```python theme={null}
%sql SELECT * FROM atp.players LIMIT 10
```
```text theme={null}
+-----------+------------+-----------+------+------------+-----+--------+-------------+
| 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:
```python theme={null}
%%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
```
```text theme={null}
+------------+-----------+-----------+------+------------+
| 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.
```python theme={null}
%%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:
```python theme={null}
%sql select * FROM best_points WHERE rank=1
```
```text theme={null}
+-------------+-----------+-----------+------+------------+
| 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:
```python theme={null}
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:
```python theme={null}
%%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
```
```text theme={null}
+------------+-----------+---------------+------------------+------+-------+
| 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:
```python theme={null}
%%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:
```python theme={null}
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)
)
```