> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> JupySQL es una herramienta de bases de datos multiplataforma para Jupyter.

# Uso de JupySQL con ClickHouse

export const CommunityMaintainedBadge = () => {
  return <div className="CommunityMaintainedBadge">
            <div className="CommunityMaintainedIcon">
            <svg xmlns="http://www.w3.org/2000/svg" width="20" height="20" fill="currentColor" viewBox="0 0 256 256">
                <path d="M244.8,150.4a8,8,0,0,1-11.2-1.6A51.6,51.6,0,0,0,192,128a8,8,0,0,1-7.37-4.89,8,8,0,0,1,0-6.22A8,8,0,0,1,192,112a24,24,0,1,0-23.24-30,8,8,0,1,1-15.5-4A40,40,0,1,1,219,117.51a67.94,67.94,0,0,1,27.43,21.68A8,8,0,0,1,244.8,150.4ZM190.92,212a8,8,0,1,1-13.84,8,57,57,0,0,0-98.16,0,8,8,0,1,1-13.84-8,72.06,72.06,0,0,1,33.74-29.92,48,48,0,1,1,58.36,0A72.06,72.06,0,0,1,190.92,212ZM128,176a32,32,0,1,0-32-32A32,32,0,0,0,128,176ZM72,120a8,8,0,0,0-8-8A24,24,0,1,1,87.24,82a8,8,0,1,0,15.5-4A40,40,0,1,0,37,117.51,67.94,67.94,0,0,0,9.6,139.19a8,8,0,1,0,12.8,9.61A51.6,51.6,0,0,1,64,128,8,8,0,0,0,72,120Z"></path>
            </svg>
        </div>
            Community Maintained
        </div>;
};

export const Image = ({img, alt, size}) => {
  return <Frame>
      <img src={img} alt={alt} />
    </Frame>;
};

En esta guía mostraremos cómo integrar ClickHouse.

Usaremos JupySQL para ejecutar consultas en ClickHouse.
Una vez cargados los datos, los visualizaremos mediante gráficos en SQL.

La integración entre JupySQL y ClickHouse es posible gracias al uso de la biblioteca clickhouse\_sqlalchemy. Esta biblioteca facilita la comunicación entre ambos sistemas y le permite conectarse a ClickHouse y especificar el dialecto SQL. Una vez establecida la conexión, puede ejecutar consultas SQL directamente desde la UI nativa de ClickHouse o desde el Jupyter notebook.

```python theme={null}
# Instalar los paquetes necesarios
%pip install --quiet jupysql clickhouse_sqlalchemy
```

Nota: es posible que debas reiniciar el kernel para usar los paquetes actualizados.

```python theme={null}
import pandas as pd
from sklearn_evaluation import plot

# Importar la extensión de Jupyter jupysql para crear celdas SQL
%load_ext sql
%config SqlMagic.autocommit=False
```

**Debes asegurarte de que tu ClickHouse esté en funcionamiento y sea accesible para los siguientes pasos. Puedes usar la versión local o la versión en la nube.**

**Nota:** tendrás que ajustar la cadena de conexión según el tipo de instancia al que intentes conectarte (URL, usuario, contraseña). En el ejemplo de abajo hemos usado una instancia local. Para obtener más información, consulta [esta guía](/es/get-started/setup/install).

```python theme={null}
%sql clickhouse://default:@localhost:8123/default
```

```sql theme={null}
%%sql
CREATE TABLE trips
(
    `trip_id` UInt32,
    `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
    `pickup_date` Date,
    `pickup_datetime` DateTime,
    `dropoff_date` Date,
    `dropoff_datetime` DateTime,
    `store_and_fwd_flag` UInt8,
    `rate_code_id` UInt8,
    `pickup_longitude` Float64,
    `pickup_latitude` Float64,
    `dropoff_longitude` Float64,
    `dropoff_latitude` Float64,
    `passenger_count` UInt8,
    `trip_distance` Float64,
    `fare_amount` Float32,
    `extra` Float32,
    `mta_tax` Float32,
    `tip_amount` Float32,
    `tolls_amount` Float32,
    `ehail_fee` Float32,
    `improvement_surcharge` Float32,
    `total_amount` Float32,
    `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
    `trip_type` UInt8,
    `pickup` FixedString(25),
    `dropoff` FixedString(25),
    `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
    `pickup_nyct2010_gid` Int8,
    `pickup_ctlabel` Float32,
    `pickup_borocode` Int8,
    `pickup_ct2010` String,
    `pickup_boroct2010` String,
    `pickup_cdeligibil` String,
    `pickup_ntacode` FixedString(4),
    `pickup_ntaname` String,
    `pickup_puma` UInt16,
    `dropoff_nyct2010_gid` UInt8,
    `dropoff_ctlabel` Float32,
    `dropoff_borocode` UInt8,
    `dropoff_ct2010` String,
    `dropoff_boroct2010` String,
    `dropoff_cdeligibil` String,
    `dropoff_ntacode` FixedString(4),
    `dropoff_ntaname` String,
    `dropoff_puma` UInt16
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(pickup_date)
ORDER BY pickup_datetime;
```

* clickhouse://default:\*\*\*@localhost:8123/default
  Hecho.

<table>
  <tr />
</table>

```sql theme={null}
%%sql
INSERT INTO trips
SELECT * FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{1..2}.gz',
    'TabSeparatedWithNames', "
    `trip_id` UInt32,
    `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
    `pickup_date` Date,
    `pickup_datetime` DateTime,
    `dropoff_date` Date,
    `dropoff_datetime` DateTime,
    `store_and_fwd_flag` UInt8,
    `rate_code_id` UInt8,
    `pickup_longitude` Float64,
    `pickup_latitude` Float64,
    `dropoff_longitude` Float64,
    `dropoff_latitude` Float64,
    `passenger_count` UInt8,
    `trip_distance` Float64,
    `fare_amount` Float32,
    `extra` Float32,
    `mta_tax` Float32,
    `tip_amount` Float32,
    `tolls_amount` Float32,
    `ehail_fee` Float32,
    `improvement_surcharge` Float32,
    `total_amount` Float32,
    `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
    `trip_type` UInt8,
    `pickup` FixedString(25),
    `dropoff` FixedString(25),
    `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
    `pickup_nyct2010_gid` Int8,
    `pickup_ctlabel` Float32,
    `pickup_borocode` Int8,
    `pickup_ct2010` String,
    `pickup_boroct2010` String,
    `pickup_cdeligibil` String,
    `pickup_ntacode` FixedString(4),
    `pickup_ntaname` String,
    `pickup_puma` UInt16,
    `dropoff_nyct2010_gid` UInt8,
    `dropoff_ctlabel` Float32,
    `dropoff_borocode` UInt8,
    `dropoff_ct2010` String,
    `dropoff_boroct2010` String,
    `dropoff_cdeligibil` String,
    `dropoff_ntacode` FixedString(4),
    `dropoff_ntaname` String,
    `dropoff_puma` UInt16
") SETTINGS input_format_try_infer_datetimes = 0
```

* clickhouse://default:\*\*\*@localhost:8123/default
  Hecho.

<table>
  <tr />
</table>

```python theme={null}
%sql SELECT count() FROM trips limit 5;
```

* clickhouse://default:\*\*\*@localhost:8123/default
  Hecho.

<table>
  <tr>
    <th>count()</th>
  </tr>

  <tr>
    <td>1999657</td>
  </tr>
</table>

```python theme={null}
%sql SELECT DISTINCT(pickup_ntaname) FROM trips limit 5;
```

* clickhouse://default:\*\*\*@localhost:8123/default
  Hecho.

<table>
  <tr>
    <th>pickup\_ntaname</th>
  </tr>

  <tr>
    <td>Morningside Heights</td>
  </tr>

  <tr>
    <td>Hudson Yards-Chelsea-Flatiron-Union Square</td>
  </tr>

  <tr>
    <td>Midtown-Midtown South</td>
  </tr>

  <tr>
    <td>SoHo-Tribeca-Civic Center-Little Italy</td>
  </tr>

  <tr>
    <td>Murray Hill-Kips Bay</td>
  </tr>
</table>

```python theme={null}
%sql SELECT round(avg(tip_amount), 2) FROM trips
```

* clickhouse://default:\*\*\*@localhost:8123/default
  Listo.

<table>
  <tr>
    <th>round(avg(tip\_amount), 2)</th>
  </tr>

  <tr>
    <td>1.68</td>
  </tr>
</table>

```sql theme={null}
%%sql
SELECT
    passenger_count,
    ceil(avg(total_amount),2) AS average_total_amount
FROM trips
GROUP BY passenger_count
```

* clickhouse://default:\*\*\*@localhost:8123/default
  Listo.

<table>
  <tr>
    <th>passenger\_count</th>
    <th>average\_total\_amount</th>
  </tr>

  <tr>
    <td>0</td>
    <td>22.69</td>
  </tr>

  <tr>
    <td>1</td>
    <td>15.97</td>
  </tr>

  <tr>
    <td>2</td>
    <td>17.15</td>
  </tr>

  <tr>
    <td>3</td>
    <td>16.76</td>
  </tr>

  <tr>
    <td>4</td>
    <td>17.33</td>
  </tr>

  <tr>
    <td>5</td>
    <td>16.35</td>
  </tr>

  <tr>
    <td>6</td>
    <td>16.04</td>
  </tr>

  <tr>
    <td>7</td>
    <td>59.8</td>
  </tr>

  <tr>
    <td>8</td>
    <td>36.41</td>
  </tr>

  <tr>
    <td>9</td>
    <td>9.81</td>
  </tr>
</table>

```sql theme={null}
%%sql
SELECT
    pickup_date,
    pickup_ntaname,
    SUM(1) AS number_of_trips
FROM trips
GROUP BY pickup_date, pickup_ntaname
ORDER BY pickup_date ASC
limit 5;
```

* clickhouse://default:\*\*\*@localhost:8123/default
  Listo.

<table>
  <tr>
    <th>pickup\_date</th>
    <th>pickup\_ntaname</th>
    <th>number\_of\_trips</th>
  </tr>

  <tr>
    <td>2015-07-01</td>
    <td>Bushwick North</td>
    <td>2</td>
  </tr>

  <tr>
    <td>2015-07-01</td>
    <td>Brighton Beach</td>
    <td>1</td>
  </tr>

  <tr>
    <td>2015-07-01</td>
    <td>Briarwood-Jamaica Hills</td>
    <td>3</td>
  </tr>

  <tr>
    <td>2015-07-01</td>
    <td>Williamsburg</td>
    <td>1</td>
  </tr>

  <tr>
    <td>2015-07-01</td>
    <td>Queensbridge-Ravenswood-Long Island City</td>
    <td>9</td>
  </tr>
</table>

```python theme={null}
# %sql DESCRIBE trips;
```

```python theme={null}
# %sql SELECT DISTINCT(trip_distance) FROM trips limit 50;
```

```sql theme={null}
%%sql --save short-trips --no-execute
SELECT *
FROM trips
WHERE trip_distance < 6.3
```

* clickhouse://default:\*\*\*@localhost:8123/default
  Se omite la ejecución...

```python theme={null}
%sqlplot histogram --table short-trips --column trip_distance --bins 10 --with short-trips
```

```response theme={null}
<AxesSubplot: title={'center': "'trip_distance' from 'short-trips'"}, xlabel='trip_distance', ylabel='Count'>
```

<Image img="https://mintcdn.com/private-7c7dfe99-fix-nav-issues/OHCdlXRrniGWimcZ/images/integrations/sql-clients/jupysql-plot-1.png?fit=max&auto=format&n=OHCdlXRrniGWimcZ&q=85&s=8730f396f1b516b3a4c0f538ed597f9c" size="md" alt="Histograma que muestra la distribución de las distancias de los viajes en 10 intervalos del conjunto de datos short-trips" border width="597" height="455" data-path="images/integrations/sql-clients/jupysql-plot-1.png" />

```python theme={null}
ax = %sqlplot histogram --table short-trips --column trip_distance --bins 50 --with short-trips
ax.grid()
ax.set_title("Trip distance from trips < 6.3")
_ = ax.set_xlabel("Trip distance")
```

<Image img="https://mintcdn.com/private-7c7dfe99-fix-nav-issues/OHCdlXRrniGWimcZ/images/integrations/sql-clients/jupysql-plot-2.png?fit=max&auto=format&n=OHCdlXRrniGWimcZ&q=85&s=9bc664ca88e56f082e9a89de2ff36a85" size="md" alt="Histograma que muestra la distribución de las distancias de viaje con 50 intervalos y cuadrícula, titulado 'Distancia de viaje en trayectos < 6.3'" border width="597" height="455" data-path="images/integrations/sql-clients/jupysql-plot-2.png" />