> ## 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 et chDB
> Comment installer chDB pour 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) est une bibliothèque Python qui permet d’exécuter du SQL dans les notebooks Jupyter et le shell IPython.
Dans ce guide, nous allons apprendre à interroger des données avec chDB et JupySQL.
## Préparation
Créons d'abord un environnement virtuel :
```bash theme={null}
python -m venv .venv
source .venv/bin/activate
```
Nous allons ensuite installer JupySQL, IPython et Jupyter Lab :
```bash theme={null}
pip install jupysql ipython jupyterlab
```
Nous pouvons utiliser JupySQL dans IPython, que nous pouvons démarrer en exécutant :
```bash theme={null}
ipython
```
Ou, dans Jupyter Lab, en exécutant :
```bash theme={null}
jupyter lab
```
Si vous utilisez Jupyter Lab, vous devrez créer un notebook avant de poursuivre le guide.
## Téléchargement d’un jeu de données
Nous allons utiliser l’un des jeux de données [tennis\_atp de Jeff Sackmann](https://github.com/JeffSackmann/tennis_atp), qui contient des métadonnées sur les joueurs et l’évolution de leur classement au fil du temps.
Commençons par télécharger les fichiers de classement :
```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",
)
```
## Configurer chDB et JupySQL
Ensuite, importons le module `dbapi` pour chDB :
```python theme={null}
from chdb import dbapi
```
Et nous allons créer une connexion à chDB.
Toutes les données que nous conserverons seront enregistrées dans le répertoire `atp.chdb` :
```python theme={null}
conn = dbapi.connect(path="atp.chdb")
```
Chargeons maintenant la commande magique `sql` et créons une connexion à chDB :
```python theme={null}
%load_ext sql
%sql conn --alias chdb
```
Ensuite, nous allons afficher la limite d’affichage pour éviter que les résultats des requêtes ne soient tronqués :
```python theme={null}
%config SqlMagic.displaylimit = None
```
\## Interroger des données dans des fichiers CSV
Nous avons téléchargé plusieurs fichiers avec le préfixe `atp_rankings`.
Utilisons la clause `DESCRIBE` pour comprendre le schéma :
```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 |
+--------------+-------+
```
Nous pouvons également exécuter une requête `SELECT` directement sur ces fichiers pour voir à quoi ressemblent les données :
```python theme={null}
%sql SELECT * FROM file('atp_rankings*.csv') LIMIT 1
```
```text theme={null}
+--------------+------+--------+--------+
| ranking_date | rank | player | points |
+--------------+------+--------+--------+
| 20000110 | 1 | 101736 | 4135 |
+--------------+------+--------+--------+
```
Le format des données est un peu bizarre.
Nettoyons cette date et utilisons la clause `REPLACE` pour retourner le `ranking_date` nettoyé :
```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 |
+--------------+------+--------+--------+
```
## Importation de fichiers CSV dans chDB
Nous allons maintenant stocker les données de ces fichiers CSV dans une table.
La base de données par défaut ne conserve pas les données sur le disque, nous devons donc d’abord créer une autre base de données :
```python theme={null}
%sql CREATE DATABASE atp
```
Et maintenant, nous allons créer une table appelée `rankings`, dont le schéma sera déduit de la structure des données contenues dans les fichiers 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
```
Jetons un coup d’œil rapide aux données de notre table :
```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 |
+--------------+------+--------+--------+
```
Cela semble correct : le résultat est, comme prévu, le même que lors de l’interrogation directe des fichiers CSV.
Nous allons suivre le même processus pour les métadonnées des joueurs.
Cette fois, les données se trouvent dans un seul fichier CSV, alors téléchargeons ce fichier :
```python theme={null}
_ = urlretrieve(
f"{base}/atp_players.csv",
"atp_players.csv",
)
```
Puis créez une table appelée `players` à partir du contenu du fichier CSV.
Nous allons également nettoyer le champ `dob` afin qu'il soit de type `Date32`.
> Dans ClickHouse, le type `Date` ne prend en charge que les dates à partir de 1970. Comme le champ `dob` contient des dates antérieures à 1970, nous utiliserons donc le type `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
```
Une fois l’opération terminée, nous pouvons examiner les données importées :
```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 |
+-----------+------------+-----------+------+------------+-----+--------+-------------+
```
## Interroger chDB
L’ingestion des données est terminée, il est maintenant temps de passer à la partie amusante : interroger les données !
Les joueurs de tennis reçoivent des points en fonction de leurs performances dans les tournois auxquels ils participent.
Voici les points de chaque joueur sur une période glissante de 52 semaines.
Nous allons écrire une requête qui trouve le nombre maximal de points accumulés par chaque joueur, ainsi que son classement à ce moment-là :
```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 |
+------------+-----------+-----------+------+------------+
```
C'est assez intéressant de constater que certains joueurs de cette liste ont accumulé beaucoup de points sans être classés n° 1 avec un tel total.
## Enregistrement des requêtes
Vous pouvez enregistrer des requêtes en utilisant le paramètre `--save` sur la même ligne que la commande magique `%%sql`.
Le paramètre `--no-execute` indique que l’exécution de la requête sera ignorée.
```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
```
Lorsqu’une requête enregistrée est exécutée, elle est convertie en Common Table Expression (CTE) avant son exécution.
Dans la requête suivante, nous calculons le nombre maximal de points obtenus par les joueurs lorsqu’ils étaient classés au rang 1 :
```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 |
+-------------+-----------+-----------+------+------------+
```
## Requêtes avec paramètres
Nous pouvons également utiliser des paramètres dans nos requêtes.
Les paramètres sont simplement des variables normales :
```python theme={null}
rank = 10
```
On peut ensuite utiliser la syntaxe `{{variable}}` dans notre requête.
La requête suivante identifie les joueurs pour lesquels le nombre de jours entre leur première et leur dernière présence dans le top 10 était le plus faible :
```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 |
+------------+-----------+---------------+------------------+------+-------+
```
## Créer des histogrammes
JupySQL propose également des fonctionnalités de création de graphiques limitées.
Nous pouvons créer des boîtes à moustaches ou des histogrammes.
Nous allons créer un histogramme, mais commençons par écrire (et enregistrer) une requête qui calcule les classements dans le top 100 obtenus par chaque joueur.
Nous pourrons nous en servir pour créer un histogramme qui compte combien de joueurs ont obtenu chaque classement :
```python theme={null}
%%sql --save players_per_rank --no-execute
select distinct player, rank
FROM atp.rankings
WHERE rank <= 100
```
Nous pouvons ensuite créer un histogramme en exécutant la commande suivante :
```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)
)
```