> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-detect-table-modification.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# JupySQL과 chDB
> chDB for Bun 설치 방법
export const Image = ({img, alt, size = "lg"}) => {
const normalizedSize = ["sm", "md", "lg"].includes(size) ? size : "lg";
return
;
};
[JupySQL](https://jupysql.ploomber.io/en/latest/quick-start.html)은 Jupyter 노트북과 IPython 셸에서 SQL을 실행할 수 있도록 해주는 Python 라이브러리입니다.
이 가이드에서는 chDB와 JupySQL을 사용해 데이터를 쿼리하는 방법을 알아보겠습니다.
## 준비
먼저 가상 환경을 생성합니다:
```bash theme={null}
python -m venv .venv
source .venv/bin/activate
```
그런 다음 JupySQL, IPython, Jupyter Lab을 설치합니다:
```bash theme={null}
pip install jupysql ipython jupyterlab
```
다음을 실행해 시작한 IPython에서 JupySQL을 사용할 수 있습니다:
```bash theme={null}
ipython
```
또는 Jupyter Lab에서는 다음을 실행하세요:
```bash theme={null}
jupyter lab
```
Jupyter Lab을 사용하는 경우, 이 가이드의 나머지 단계를 진행하기 전에 먼저 노트북을 만들어야 합니다.
## 데이터셋 다운로드
시간에 따른 선수 정보와 랭킹 메타데이터가 포함된 [Jeff Sackmann's tennis\_atp](https://github.com/JeffSackmann/tennis_atp) 데이터셋 중 하나를 사용합니다.
먼저 랭킹 파일을 다운로드하겠습니다:
```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",
)
```
## chDB 및 JupySQL 구성하기
다음으로, chDB용 `dbapi` 모듈을 불러오겠습니다:
```python theme={null}
from chdb import dbapi
```
그리고 chDB 연결을 생성하겠습니다.
영구 저장하는 모든 데이터는 `atp.chdb` 디렉터리에 저장됩니다:
```python theme={null}
conn = dbapi.connect(path="atp.chdb")
```
이제 `sql` 매직을 불러오고 chDB 연결을 만듭니다:
```python theme={null}
%load_ext sql
%sql conn --alias chdb
```
다음으로, 쿼리 결과가 잘리지 않도록 표시 한도를 설정합니다:
```python theme={null}
%config SqlMagic.displaylimit = None
```
\## CSV 파일의 데이터 쿼리
`atp_rankings` 접두사가 붙은 파일을 여러 개 다운로드했습니다.
스키마를 파악하기 위해 `DESCRIBE` 절을 사용해 보겠습니다:
```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 |
+--------------+-------+
```
이 파일들에 직접 `SELECT` 쿼리를 작성하여 데이터 형태를 확인할 수도 있습니다:
```python theme={null}
%sql SELECT * FROM file('atp_rankings*.csv') LIMIT 1
```
```text theme={null}
+--------------+------+--------+--------+
| ranking_date | rank | player | points |
+--------------+------+--------+--------+
| 20000110 | 1 | 101736 | 4135 |
+--------------+------+--------+--------+
```
데이터 포맷이 약간 이상합니다.
날짜를 정리한 뒤 `REPLACE` 절을 사용해 정리된 `ranking_date`를 반환해 보겠습니다:
```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 |
+--------------+------+--------+--------+
```
## CSV 파일을 chDB로 가져오기
이제 이 CSV 파일의 데이터를 테이블에 저장하겠습니다.
기본 데이터베이스는 데이터를 디스크에 영구 저장하지 않으므로, 먼저 다른 데이터베이스를 생성해야 합니다:
```python theme={null}
%sql CREATE DATABASE atp
```
이제 CSV 파일의 데이터 구조를 바탕으로 스키마(schema)가 결정되는 `rankings`라는 테이블(table)을 생성합니다:
```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
```
테이블에 있는 데이터를 간단히 확인해 보겠습니다:
```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 |
+--------------+------+--------+--------+
```
좋습니다. 예상한 대로 출력 결과는 CSV 파일에 직접 쿼리했을 때와 동일합니다.
이제 플레이어 메타데이터에도 같은 과정을 적용하겠습니다.
이번에는 데이터가 모두 하나의 CSV 파일에 들어 있으므로 해당 파일을 다운로드하겠습니다:
```python theme={null}
_ = urlretrieve(
f"{base}/atp_players.csv",
"atp_players.csv",
)
```
그런 다음 CSV 파일의 내용을 바탕으로 `players`라는 테이블을 생성합니다.
또한 `dob` 필드를 정리하여 `Date32` 유형이 되도록 합니다.
> ClickHouse에서 `Date` 유형은 1970년 이후 날짜만 지원합니다. `dob` 컬럼에는 1970년 이전 날짜가 포함되어 있으므로 대신 `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
```
실행이 완료되면 수집된 데이터를 확인할 수 있습니다:
```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 |
+-----------+------------+-----------+------+------------+-----+--------+-------------+
```
## chDB 쿼리하기
데이터 수집이 완료되었으니, 이제 가장 흥미로운 단계인 데이터 쿼리 차례입니다!
테니스 선수는 출전한 토너먼트에서의 성과에 따라 포인트를 받습니다.
각 선수의 포인트는 최근 52주를 기준으로 계산됩니다.
이제 각 선수가 누적한 최대 포인트와 해당 시점의 순위를 찾는 쿼리를 작성하겠습니다:
```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 |
+------------+-----------+-----------+------+------------+
```
이 목록에 있는 일부 선수들이 해당 총점으로 1위는 아니었음에도 많은 점수를 쌓았다는 점이 꽤 흥미롭습니다.
## 쿼리 저장
`%%sql` 매직과 같은 줄에서 `--save` 매개변수를 사용하면 쿼리를 저장할 수 있습니다.
`--no-execute` 매개변수를 사용하면 쿼리 실행을 건너뜁니다.
```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
```
저장된 쿼리를 실행하면 실행 전에 공통 테이블 표현식(Common Table Expression, CTE)으로 변환됩니다.
다음 쿼리에서는 플레이어가 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 |
+-------------+-----------+-----------+------+------------+
```
## 매개변수를 사용한 쿼리
쿼리에서도 매개변수를 사용할 수 있습니다.
매개변수는 일반 변수와 같습니다:
```python theme={null}
rank = 10
```
그런 다음 쿼리에서 `{{variable}}` 구문을 사용할 수 있습니다.
다음 쿼리는 선수가 처음으로 상위 10위 안에 들었던 시점부터 마지막으로 상위 10위 안에 들었던 시점까지의 일수 차이가 가장 짧았던 선수를 찾습니다:
```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 |
+------------+-----------+---------------+------------------+------+-------+
```
## 히스토그램 그리기
JupySQL에는 제한적이지만 차트 기능도 있습니다.
상자 그림이나 히스토그램을 만들 수 있습니다.
이제 히스토그램을 만들겠습니다. 먼저 각 플레이어가 상위 100위 안에서 달성한 순위를 계산하는 쿼리를 작성해 저장하겠습니다.
이 쿼리를 사용하면 각 순위를 달성한 플레이어 수를 집계하는 히스토그램을 만들 수 있습니다:
```python theme={null}
%%sql --save players_per_rank --no-execute
select distinct player, rank
FROM atp.rankings
WHERE rank <= 100
```
그런 다음 다음을 실행해 히스토그램을 만들 수 있습니다.
```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)
)
```