> ## 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 y chDB
> Cómo instalar 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) es una biblioteca de Python que permite ejecutar SQL en notebooks de Jupyter y en el shell de IPython.
En esta guía, aprenderás a consultar datos con chDB y JupySQL.
## Preparación
Primero, vamos a crear un entorno virtual:
```bash theme={null}
python -m venv .venv
source .venv/bin/activate
```
Y, a continuación, instalaremos JupySQL, IPython y Jupyter Lab:
```bash theme={null}
pip install jupysql ipython jupyterlab
```
Podemos usar JupySQL en IPython, que podemos iniciar con:
```bash theme={null}
ipython
```
O bien en Jupyter Lab, ejecutando:
```bash theme={null}
jupyter lab
```
Si usas Jupyter Lab, tendrás que crear un notebook antes de seguir con el resto de la guía.
## Descargando un conjunto de datos
Vamos a usar uno de los conjuntos de datos [tennis\_atp de Jeff Sackmann](https://github.com/JeffSackmann/tennis_atp), que contiene metadatos sobre los jugadores y su clasificación a lo largo del tiempo.
Empecemos descargando los archivos de clasificación:
```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",
)
```
## Configuración de chDB y JupySQL
A continuación, importemos el módulo `dbapi` de chDB:
```python theme={null}
from chdb import dbapi
```
Y crearemos una conexión a chDB.
Todos los datos que persistamos se guardarán en el directorio `atp.chdb`:
```python theme={null}
conn = dbapi.connect(path="atp.chdb")
```
Carguemos ahora la magia `sql` y establezcamos una conexión con chDB:
```python theme={null}
%load_ext sql
%sql conn --alias chdb
```
A continuación, mostraremos el límite de resultados en pantalla para que los resultados de las consultas no se truncen:
```python theme={null}
%config SqlMagic.displaylimit = None
```
\## Consultar datos en archivos CSV
Hemos descargado varios archivos con el prefijo `atp_rankings`.
Usemos la cláusula `DESCRIBE` para comprender el 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 |
+--------------+-------+
```
También podemos escribir una consulta `SELECT` directamente en estos archivos para ver qué aspecto tienen los datos:
```python theme={null}
%sql SELECT * FROM file('atp_rankings*.csv') LIMIT 1
```
```text theme={null}
+--------------+------+--------+--------+
| ranking_date | rank | player | points |
+--------------+------+--------+--------+
| 20000110 | 1 | 101736 | 4135 |
+--------------+------+--------+--------+
```
El formato de los datos es un poco raro.
Limpiemos esa fecha y usemos la cláusula `REPLACE` para devolver `ranking_date` ya limpio:
```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 |
+--------------+------+--------+--------+
```
## Importar archivos CSV en chDB
Ahora vamos a almacenar los datos de estos archivos CSV en una tabla.
La base de datos predeterminada no persiste los datos en disco, así que primero debemos crear otra base de datos:
```python theme={null}
%sql CREATE DATABASE atp
```
Y ahora vamos a crear una tabla llamada `rankings`, cuyo esquema se derivará de la estructura de los datos de los archivos 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
```
Hagamos una revisión rápida de los datos de nuestra tabla:
```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 |
+--------------+------+--------+--------+
```
Se ve bien: el resultado, tal como se esperaba, es el mismo que al consultar directamente los archivos CSV.
Vamos a seguir el mismo proceso para los metadatos de los jugadores.
Esta vez, todos los datos están en un único archivo CSV, así que descarguemos ese archivo:
```python theme={null}
_ = urlretrieve(
f"{base}/atp_players.csv",
"atp_players.csv",
)
```
Luego, crea una tabla llamada `players` a partir del contenido del archivo CSV.
También ajustaremos el campo `dob` para que sea del tipo `Date32`.
> En ClickHouse, el tipo `Date` solo admite fechas a partir de 1970. Como la columna `dob` contiene fechas anteriores a 1970, usaremos en su lugar el 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
```
Una vez que termine de ejecutarse, podemos echar un vistazo a los datos ingeridos:
```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 |
+-----------+------------+-----------+------+------------+-----+--------+-------------+
```
## Consultar en chDB
La ingestión de datos ya está lista; ahora llega la parte divertida: ¡consultar los datos!
Los tenistas reciben puntos en función de lo bien que rinden en los torneos que disputan.
Los puntos de cada jugador se calculan a lo largo de un período móvil de 52 semanas.
Vamos a escribir una consulta que encuentre el máximo de puntos acumulados por cada jugador, junto con su clasificación en ese 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 |
+------------+-----------+-----------+------+------------+
```
Es bastante interesante que algunos de los jugadores de esta lista hayan acumulado muchos puntos sin haber sido nunca el número 1 con esa cantidad de puntos.
## Guardar consultas
Podemos guardar consultas con el parámetro `--save` en la misma línea que la instrucción mágica `%%sql`.
El parámetro `--no-execute` indica que se omitirá la ejecución de la consulta.
```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
```
Cuando se ejecuta una consulta guardada, se convierte en una expresión de tabla común (CTE) antes de ejecutarse.
En la siguiente consulta calculamos el máximo de puntos obtenido por los jugadores cuando ocupaban la posición 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 |
+-------------+-----------+-----------+------+------------+
```
## Consultas con parámetros
También podemos usar parámetros en nuestras consultas.
Los parámetros son simplemente variables normales:
```python theme={null}
rank = 10
```
Y luego podemos usar la sintaxis `{{variable}}` en nuestra consulta.
La siguiente consulta encuentra a los jugadores con la menor cantidad de días entre la primera vez que estuvieron clasificados entre los 10 primeros y la última vez que estuvieron clasificados entre los 10 primeros:
```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 |
+------------+-----------+---------------+------------------+------+-------+
```
## Creación de histogramas
JupySQL también tiene funciones de gráficos limitadas.
Podemos crear diagramas de caja o histogramas.
Vamos a crear un histograma, pero primero escribamos (y guardemos) una consulta que calcule las clasificaciones dentro del top 100 que ha alcanzado cada jugador.
Podremos usar esto para crear un histograma que muestre cuántos jugadores alcanzaron cada clasificación:
```python theme={null}
%%sql --save players_per_rank --no-execute
select distinct player, rank
FROM atp.rankings
WHERE rank <= 100
```
Podemos crear un histograma ejecutando lo siguiente:
```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)
)
```