> ## 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은 Jupyter에서 사용하는 멀티플랫폼 데이터베이스 도구입니다.

# ClickHouse에서 JupySQL 사용하기

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>;
};

이 가이드에서는 ClickHouse 통합 방법을 보여드립니다.

JupySQL을 사용해 ClickHouse에서 쿼리를 실행합니다.
데이터가 로드되면 SQL 플로팅을 통해 시각화합니다.

JupySQL과 ClickHouse의 통합은 clickhouse\_sqlalchemy 라이브러리를 통해 가능합니다. 이 라이브러리를 사용하면 두 시스템이 쉽게 통신할 수 있으며, ClickHouse에 연결하고 SQL 방언을 지정할 수 있습니다. 연결이 완료되면 ClickHouse 네이티브 UI에서 직접 SQL 쿼리를 실행하거나 Jupyter 노트북에서 바로 실행할 수 있습니다.

```python theme={null}
# 필수 패키지 설치
%pip install --quiet jupysql clickhouse_sqlalchemy
```

참고: 업데이트된 패키지를 사용하려면 커널을 다시 시작해야 할 수 있습니다.

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

# SQL 셀을 생성하기 위해 jupysql Jupyter 확장 기능을 가져옵니다
%load_ext sql
%config SqlMagic.autocommit=False
```

**다음 단계로 진행하려면 ClickHouse가 실행 중이며 접근할 수 있어야 합니다. 로컬 버전이나 Cloud 버전 중 하나를 사용할 수 있습니다.**

**참고:** 연결하려는 인스턴스 유형에 맞게 연결 문자열(connection string)(url, user, password)을 조정해야 합니다. 아래 예시에서는 로컬 인스턴스를 사용했습니다. 자세한 내용은 [이 가이드](/ko/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
  완료되었습니다.

<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
  완료되었습니다.

<table>
  <tr />
</table>

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

* clickhouse://default:\*\*\*@localhost:8123/default
  완료.

<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
  완료되었습니다.

<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
  완료되었습니다.

<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
  완료.

<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
  완료되었습니다.

<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
  실행을 생략합니다...

```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="short-trips 데이터셋의 이동 거리 분포를 10개 bin으로 나타낸 히스토그램" 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="이동 거리 분포를 보여주는 히스토그램으로, 50개의 구간과 격자가 표시되어 있으며 제목은 'Trip distance from trips < 6.3'" border width="597" height="455" data-path="images/integrations/sql-clients/jupysql-plot-2.png" />