JupySQL et chDB - ClickHouse Documentation

JupySQL 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 :

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

Nous allons ensuite installer JupySQL, IPython et Jupyter Lab :

pip install jupysql ipython jupyterlab

Nous pouvons utiliser JupySQL dans IPython, que nous pouvons démarrer en exécutant :

ipython

Ou, dans Jupyter Lab, en exécutant :

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, 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 :

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",
  )

Configurer chDB et JupySQL

Ensuite, importons le module dbapi pour chDB :

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 :

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

Chargeons maintenant la commande magique sql et créons une connexion à chDB :

%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 :

%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 :

%%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 |
+--------------+-------+

Nous pouvons également exécuter une requête SELECT directement sur ces fichiers pour voir à quoi ressemblent les données :

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

+--------------+------+--------+--------+
| 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é :

%%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  |
+--------------+------+--------+--------+

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 :

%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 :

%%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 :

%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  |
+--------------+------+--------+--------+

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 :

_ = 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.

%%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 :

%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  |
+-----------+------------+-----------+------+------------+-----+--------+-------------+

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à :

%%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 |
+------------+-----------+-----------+------+------------+

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.

%%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 :

%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 |
+-------------+-----------+-----------+------+------------+

Requêtes avec paramètres

Nous pouvons également utiliser des paramètres dans nos requêtes. Les paramètres sont simplement des variables normales :

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 :

%%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   |
+------------+-----------+---------------+------------------+------+-------+

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 :

%%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 :

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)
)

​Préparation

​Téléchargement d’un jeu de données

​Configurer chDB et JupySQL

​Importation de fichiers CSV dans chDB

​Interroger chDB

​Enregistrement des requêtes

​Requêtes avec paramètres

​Créer des histogrammes

Préparation

Téléchargement d’un jeu de données

Configurer chDB et JupySQL

Importation de fichiers CSV dans chDB

Interroger chDB

Enregistrement des requêtes

Requêtes avec paramètres

Créer des histogrammes