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

> JupySQL is a multi-platform database tool for Jupyter.

# Using JupySQL with 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>;
};

In this guide we'll show an integration with ClickHouse.

We will use JupySQL to run queries on top of ClickHouse.
Once the data is loaded, we'll visualize it via SQL plotting.

The integration between JupySQL and ClickHouse is made possible by the use of the clickhouse\_sqlalchemy library. This library allows for easy communication between the two systems, and enables you to connect to ClickHouse and pass the SQL dialect. Once connected, you can run SQL queries directly from the Clickhouse native UI, or from the Jupyter notebook directly.

```python theme={null}
# Install required packages
%pip install --quiet jupysql clickhouse_sqlalchemy
```

Note: you may need to restart the kernel to use updated packages.

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

# Import jupysql Jupyter extension to create SQL cells
%load_ext sql
%config SqlMagic.autocommit=False
```

**You'd need to make sure your Clickhouse is up and reachable for the next stages. You can use either the local or the cloud version.**

**Note:** you will need to adjust the connection string according to the instance type you're trying to connect to (url, user, password). In the example below we've used a local instance. To learn more about it, check out [this guide](/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
  Done.

<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
  Done.

<table>
  <tr />
</table>

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

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

<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
  Done.

<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
  Done.

<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
  Done.

<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
  Done.

<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
  Skipping execution...

```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-mintlify-fbfa8bee/amY-JDMREAaO7mx6/images/integrations/sql-clients/jupysql-plot-1.png?fit=max&auto=format&n=amY-JDMREAaO7mx6&q=85&s=f3924ba9bdb734674879987b5c9b20d0" size="md" alt="Histogram showing distribution of trip distances with 10 bins from the short-trips dataset" 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-mintlify-fbfa8bee/amY-JDMREAaO7mx6/images/integrations/sql-clients/jupysql-plot-2.png?fit=max&auto=format&n=amY-JDMREAaO7mx6&q=85&s=72629de6610136783ecdb759d8077e4c" size="md" alt="Histogram showing distribution of trip distances with 50 bins and grid, titled 'Trip distance from trips < 6.3'" border width="597" height="455" data-path="images/integrations/sql-clients/jupysql-plot-2.png" />