Cómo consultar un servidor remoto de ClickHouse

En esta guía, aprenderemos a consultar un servidor remoto de ClickHouse desde chDB.

Configuración

Primero, vamos a crear un entorno virtual:

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

Y ahora instalaremos chDB. Asegúrate de tener la versión 2.0.2 o posterior:

pip install "chdb>=2.0.2"

Ahora vamos a instalar pandas e ipython:

pip install pandas ipython

Vamos a usar ipython para ejecutar los comandos del resto de la guía, que puedes iniciar con:

ipython

También puedes usar el código en un script de Python o en tu notebook preferido.

Introducción a ClickPy

El servidor remoto de ClickHouse que vamos a consultar es ClickPy. ClickPy hace un seguimiento de todas las descargas de paquetes de PyPI y te permite explorar sus estadísticas a través de una UI. La base de datos subyacente se puede consultar con el usuario play.

Puedes obtener más información sobre ClickPy en su repositorio de GitHub.

Consultas al servicio de ClickHouse de ClickPy

Importemos chDB:

import chdb

Vamos a consultar ClickPy con la función remoteSecure. Esta función requiere, como mínimo, un nombre del host, un nombre de tabla y un nombre de usuario.

Podemos escribir la siguiente consulta para obtener el número de descargas por día del paquete openai como un DataFrame de Pandas:

query = """
SELECT
    toStartOfDay(date)::Date32 AS x,
    sum(count) AS y
FROM remoteSecure(
  'clickpy-clickhouse.clickhouse.com', 
  'pypi.pypi_downloads_per_day', 
  'play'
)
WHERE project = 'openai'
GROUP BY x
ORDER BY x ASC
"""

openai_df = chdb.query(query, "DataFrame")
openai_df.sort_values(by=["x"], ascending=False).head(n=10)

               x        y
2392  2024-10-02  1793502
2391  2024-10-01  1924901
2390  2024-09-30  1749045
2389  2024-09-29  1177131
2388  2024-09-28  1157323
2387  2024-09-27  1688094
2386  2024-09-26  1862712
2385  2024-09-25  2032923
2384  2024-09-24  1901965
2383  2024-09-23  1777554

Ahora hagamos lo mismo para obtener las descargas de scikit-learn:

query = """
SELECT
    toStartOfDay(date)::Date32 AS x,
    sum(count) AS y
FROM remoteSecure(
  'clickpy-clickhouse.clickhouse.com', 
  'pypi.pypi_downloads_per_day', 
  'play'
)
WHERE project = 'scikit-learn'
GROUP BY x
ORDER BY x ASC
"""

sklearn_df = chdb.query(query, "DataFrame")
sklearn_df.sort_values(by=["x"], ascending=False).head(n=10)

               x        y
2392  2024-10-02  1793502
2391  2024-10-01  1924901
2390  2024-09-30  1749045
2389  2024-09-29  1177131
2388  2024-09-28  1157323
2387  2024-09-27  1688094
2386  2024-09-26  1862712
2385  2024-09-25  2032923
2384  2024-09-24  1901965
2383  2024-09-23  1777554

Combinar DataFrames de Pandas

Ahora tenemos dos DataFrames, que podemos combinar según la fecha (que es la columna x) de la siguiente manera:

df = openai_df.merge(
  sklearn_df, 
  on="x", 
  suffixes=("_openai", "_sklearn")
)
df.head(n=5)

            x  y_openai  y_sklearn
0  2018-02-26        83      33971
1  2018-02-27        31      25211
2  2018-02-28         8      26023
3  2018-03-01         8      20912
4  2018-03-02         5      23842

A continuación, podemos calcular la proporción entre las descargas de Open AI y las de scikit-learn así:

df['ratio'] = df['y_openai'] / df['y_sklearn']
df.head(n=5)

            x  y_openai  y_sklearn     ratio
0  2018-02-26        83      33971  0.002443
1  2018-02-27        31      25211  0.001230
2  2018-02-28         8      26023  0.000307
3  2018-03-01         8      20912  0.000383
4  2018-03-02         5      23842  0.000210

Consulta de DataFrames de Pandas

A continuación, supongamos que queremos encontrar las fechas con los mejores y peores ratios. Podemos volver a chDB y calcular esos valores:

chdb.query("""
SELECT max(ratio) AS bestRatio,
       argMax(x, ratio) AS bestDate,
       min(ratio) AS worstRatio,
       argMin(x, ratio) AS worstDate
FROM Python(df)
""", "DataFrame")

   bestRatio    bestDate  worstRatio   worstDate
0   0.693855  2024-09-19    0.000003  2020-02-09

Si quieres obtener más información sobre la consulta de DataFrames de Pandas, consulta la guía para desarrolladores sobre DataFrames de Pandas.