고급 튜토리얼
고급 튜토리얼
뉴욕시 택시 예시 데이터셋을 사용해서 ClickHouse에서 데이터를 수집(ingest)하고 쿼리하는 방법을 배워요. 테이블 만들기, S3에서 데이터 로드, 분석 쿼리, 사전(dictionary) 만들기, JOIN 수행까지 실습해요.
본문
개요 (Overview)
뉴욕시 택시 예시 데이터셋을 사용해서 ClickHouse에서 데이터를 수집하고 쿼리하는 방법을 배워요.
사전 요구사항 (Prerequisites)
이 튜토리얼을 완료하려면 실행 중인 ClickHouse 서비스에 접근할 수 있어야 해요. ClickHouse Cloud라면 ClickHouse Cloud 퀵 스타트를 완료하세요. 셀프 매니지드 ClickHouse라면 설치 지침을 따르세요.
1. 새 테이블 만들기
뉴욕시 택시 데이터셋에는 수백만 건의 택시 탑승에 대한 세부 정보가 있으며, 팁 금액, 통행료, 결제 유형 등의 컬럼을 포함해요. 이 데이터를 저장할 테이블을 만들어요.
- SQL 콘솔에 연결하세요:
- ClickHouse Cloud인 경우 드롭다운 메뉴에서 서비스를 선택한 뒤 왼쪽 내비게이션 메뉴에서 SQL Console을 선택하세요.
- 셀프 매니지드 ClickHouse인 경우
https://_hostname_:8443/play의 SQL 콘솔에 연결하세요. 세부 정보는 ClickHouse 관리자에게 문의하세요.
default데이터베이스에 다음trips테이블을 만드세요:
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;
2. 데이터셋 추가하기
테이블을 만들었으니 이제 S3의 CSV 파일에서 뉴욕시 택시 데이터를 추가해요.
- 다음 명령은 S3의 두 파일인
trips_1.tsv.gz와trips_2.tsv.gz에서trips테이블에 약 2,000,000행을 삽입해요:
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
INSERT가 끝날 때까지 기다리세요. 150MB의 데이터를 다운로드하는 데 시간이 걸릴 수 있어요.- 삽입이 끝나면 동작했는지 확인하세요:
SELECT count() FROM trips
이 쿼리는 1,999,657행을 반환해야 해요.
3. 데이터 분석하기
데이터를 분석하는 몇 가지 쿼리를 실행해 보세요. 다음 예시를 탐색하거나 직접 SQL 쿼리를 작성해 보세요.
- 평균 팁 금액 계산:
SELECT round(avg(tip_amount), 2) FROM trips
예상 출력:
┌─round(avg(tip_amount), 2)─┐
│ 1.68 │
└───────────────────────────┘
- 승객 수 기반 평균 비용 계산:
SELECT
passenger_count,
ceil(avg(total_amount),2) AS average_total_amount
FROM trips
GROUP BY passenger_count
예상 출력. passenger_count는 0에서 9까지 범위:
┌─passenger_count─┬─average_total_amount─┐
│ 0 │ 22.69 │
│ 1 │ 15.97 │
│ 2 │ 17.15 │
│ 3 │ 16.76 │
│ 4 │ 17.33 │
│ 5 │ 16.35 │
│ 6 │ 16.04 │
│ 7 │ 59.8 │
│ 8 │ 36.41 │
│ 9 │ 9.81 │
└─────────────────┴──────────────────────┘
- 동네별 일일 픽업 수 계산:
SELECT
pickup_date,
pickup_ntaname,
SUM(1) AS number_of_trips
FROM trips
GROUP BY pickup_date, pickup_ntaname
ORDER BY pickup_date ASC
예상 출력:
┌─pickup_date─┬─pickup_ntaname───────────────────────────────────────────┬─number_of_trips─┐
│ 2015-07-01 │ Brooklyn Heights-Cobble Hill │ 13 │
│ 2015-07-01 │ Old Astoria │ 5 │
│ 2015-07-01 │ Flushing │ 1 │
│ 2015-07-01 │ Yorkville │ 378 │
│ 2015-07-01 │ Gramercy │ 344 │
│ 2015-07-01 │ Fordham South │ 2 │
│ 2015-07-01 │ SoHo-TriBeCa-Civic Center-Little Italy │ 621 │
│ 2015-07-01 │ Park Slope-Gowanus │ 29 │
│ 2015-07-01 │ Bushwick South │ 5 │
- 각 탑승의 길이를 분으로 계산한 뒤, 탑승 길이로 결과를 그룹화:
SELECT
avg(tip_amount) AS avg_tip,
avg(fare_amount) AS avg_fare,
avg(passenger_count) AS avg_passenger,
count() AS count,
truncate(date_diff('second', pickup_datetime, dropoff_datetime)/60) as trip_minutes
FROM trips
WHERE trip_minutes > 0
GROUP BY trip_minutes
ORDER BY trip_minutes DESC
예상 출력:
┌──────────────avg_tip─┬───────────avg_fare─┬──────avg_passenger─┬──count─┬─trip_minutes─┐
│ 1.9600000381469727 │ 8 │ 1 │ 1 │ 27511 │
│ 0 │ 12 │ 2 │ 1 │ 27500 │
│ 0.542166673981895 │ 19.716666666666665 │ 1.9166666666666667 │ 60 │ 1439 │
│ 0.902499997522682 │ 11.270625001192093 │ 1.95625 │ 160 │ 1438 │
│ 0.9715789457909146 │ 13.646616541353383 │ 2.0526315789473686 │ 133 │ 1437 │
│ 0.9682692398245518 │ 14.134615384615385 │ 2.076923076923077 │ 104 │ 1436 │
│ 1.1022105210705808 │ 13.778947368421052 │ 2.042105263157895 │ 95 │ 1435 │
- 요일 시간대별로 분류된 각 동네의 픽업 수 보기:
SELECT
pickup_ntaname,
toHour(pickup_datetime) as pickup_hour,
SUM(1) AS pickups
FROM trips
WHERE pickup_ntaname != ''
GROUP BY pickup_ntaname, pickup_hour
ORDER BY pickup_ntaname, pickup_hour
예상 출력:
┌─pickup_ntaname───────────────────────────────────────────┬─pickup_hour─┬─pickups─┐
│ Airport │ 0 │ 3509 │
│ Airport │ 1 │ 1184 │
│ Airport │ 2 │ 401 │
│ Airport │ 3 │ 152 │
│ Airport │ 4 │ 213 │
│ Airport │ 5 │ 955 │
│ Airport │ 6 │ 2161 │
│ Airport │ 7 │ 3013 │
│ Airport │ 8 │ 3601 │
│ Airport │ 9 │ 3792 │
│ Airport │ 10 │ 4546 │
│ Airport │ 11 │ 4659 │
│ Airport │ 12 │ 4621 │
│ Airport │ 13 │ 5348 │
│ Airport │ 14 │ 5889 │
│ Airport │ 15 │ 6505 │
│ Airport │ 16 │ 6119 │
│ Airport │ 17 │ 6341 │
│ Airport │ 18 │ 6173 │
│ Airport │ 19 │ 6329 │
│ Airport │ 20 │ 6271 │
│ Airport │ 21 │ 6649 │
│ Airport │ 22 │ 6356 │
│ Airport │ 23 │ 6016 │
│ Allerton-Pelham Gardens │ 4 │ 1 │
│ Allerton-Pelham Gardens │ 6 │ 1 │
│ Allerton-Pelham Gardens │ 7 │ 1 │
│ Allerton-Pelham Gardens │ 9 │ 5 │
│ Allerton-Pelham Gardens │ 10 │ 3 │
│ Allerton-Pelham Gardens │ 15 │ 1 │
│ Allerton-Pelham Gardens │ 20 │ 2 │
│ Allerton-Pelham Gardens │ 23 │ 1 │
│ Annadale-Huguenot-Prince's Bay-Eltingville │ 23 │ 1 │
│ Arden Heights │ 11 │ 1 │
- LaGuardia 또는 JFK 공항으로 가는 탑승 검색:
SELECT
pickup_datetime,
dropoff_datetime,
total_amount,
pickup_nyct2010_gid,
dropoff_nyct2010_gid,
CASE
WHEN dropoff_nyct2010_gid = 138 THEN 'LGA'
WHEN dropoff_nyct2010_gid = 132 THEN 'JFK'
END AS airport_code,
EXTRACT(YEAR FROM pickup_datetime) AS year,
EXTRACT(DAY FROM pickup_datetime) AS day,
EXTRACT(HOUR FROM pickup_datetime) AS hour
FROM trips
WHERE dropoff_nyct2010_gid IN (132, 138)
ORDER BY pickup_datetime
예상 출력:
┌─────pickup_datetime─┬────dropoff_datetime─┬─total_amount─┬─pickup_nyct2010_gid─┬─dropoff_nyct2010_gid─┬─airport_code─┬─year─┬─day─┬─hour─┐
│ 2015-07-01 00:04:14 │ 2015-07-01 00:15:29 │ 13.3 │ -34 │ 132 │ JFK │ 2015 │ 1 │ 0 │
│ 2015-07-01 00:09:42 │ 2015-07-01 00:12:55 │ 6.8 │ 50 │ 138 │ LGA │ 2015 │ 1 │ 0 │
│ 2015-07-01 00:23:04 │ 2015-07-01 00:24:39 │ 4.8 │ -125 │ 132 │ JFK │ 2015 │ 1 │ 0 │
│ 2015-07-01 00:27:51 │ 2015-07-01 00:39:02 │ 14.72 │ -101 │ 138 │ LGA │ 2015 │ 1 │ 0 │
│ 2015-07-01 00:32:03 │ 2015-07-01 00:55:39 │ 39.34 │ 48 │ 138 │ LGA │ 2015 │ 1 │ 0 │
│ 2015-07-01 00:34:12 │ 2015-07-01 00:40:48 │ 9.95 │ -93 │ 132 │ JFK │ 2015 │ 1 │ 0 │
│ 2015-07-01 00:38:26 │ 2015-07-01 00:49:00 │ 13.3 │ -11 │ 138 │ LGA │ 2015 │ 1 │ 0 │
│ 2015-07-01 00:41:48 │ 2015-07-01 00:44:45 │ 6.3 │ -94 │ 132 │ JFK │ 2015 │ 1 │ 0 │
│ 2015-07-01 01:06:18 │ 2015-07-01 01:14:43 │ 11.76 │ 37 │ 132 │ JFK │ 2015 │ 1 │ 1 │
4. 사전(Dictionary) 만들기
사전은 메모리에 저장된 키-값 쌍의 매핑이에요. 자세한 내용은 Dictionaries를 참고하세요. ClickHouse 서비스의 테이블과 연결된 사전을 만들어요. 테이블과 사전은 뉴욕시의 각 동네에 대한 행을 담은 CSV 파일을 기반으로 해요. 동네는 뉴욕시 다섯 보로(Bronx, Brooklyn, Manhattan, Queens, Staten Island)와 Newark Airport(EWR)의 이름으로 매핑돼요.
다음은 테이블 형식으로 사용하는 CSV 파일의 발췌본이에요. 파일의 LocationID 컬럼은 trips 테이블의 pickup_nyct2010_gid 및 dropoff_nyct2010_gid 컬럼에 매핑돼요:
| LocationID | Borough | Zone | service_zone |
|---|---|---|---|
| 1 | EWR | Newark Airport | EWR |
| 2 | Queens | Jamaica Bay | Boro Zone |
| 3 | Bronx | Allerton/Pelham Gardens | Boro Zone |
| 4 | Manhattan | Alphabet City | Yellow Zone |
| 5 | Staten Island | Arden Heights | Boro Zone |
- 다음 SQL 명령을 실행해서
taxi_zone_dictionary라는 사전을 만들고 S3의 CSV 파일에서 사전을 채워요. 파일의 URL은https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv예요.
CREATE DICTIONARY taxi_zone_dictionary
(
`LocationID` UInt16 DEFAULT 0,
`Borough` String,
`Zone` String,
`service_zone` String
)
PRIMARY KEY LocationID
SOURCE(HTTP(URL 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv' FORMAT 'CSVWithNames'))
LIFETIME(MIN 0 MAX 0)
LAYOUT(HASHED_ARRAY())
LIFETIME을 0으로 설정하면 자동 업데이트가 비활성화되어 우리 S3 버킷에 불필요한 트래픽을 방지해요. 다른 경우에는 다르게 구성할 수 있어요. 자세한 내용은 LIFETIME으로 사전 데이터 새로고침을 참고하세요.
- 동작했는지 확인하세요. 다음은 265행, 즉 각 동네에 대해 하나씩 반환해야 해요:
SELECT * FROM taxi_zone_dictionary
dictGet함수(또는 그 변형)를 사용해 사전에서 값을 검색해요. 사전 이름, 원하는 값, 키(우리 예시에서는taxi_zone_dictionary의LocationID컬럼)를 전달해요. 예를 들어 다음 쿼리는LocationID가 132인(즉 JFK 공항에 해당하는)Borough를 반환해요:
SELECT dictGet('taxi_zone_dictionary', 'Borough', 132)
JFK는 Queens에 있어요. 값을 검색하는 시간이 사실상 0인 것에 주목하세요:
┌─dictGet('taxi_zone_dictionary', 'Borough', 132)─┐
│ Queens │
└─────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.004 sec.
dictHas함수를 사용해 키가 사전에 있는지 확인해요. 예를 들어 다음 쿼리는1(ClickHouse에서 "true")을 반환해요:
SELECT dictHas('taxi_zone_dictionary', 132)
- 다음 쿼리는
4567이 사전의LocationID값이 아니므로 0을 반환해요:
SELECT dictHas('taxi_zone_dictionary', 4567)
dictGet함수를 사용해 쿼리에서 보로 이름을 검색해요. 예를 들어:
SELECT
count(1) AS total,
dictGetOrDefault('taxi_zone_dictionary','Borough', toUInt64(pickup_nyct2010_gid), 'Unknown') AS borough_name
FROM trips
WHERE dropoff_nyct2010_gid = 132 OR dropoff_nyct2010_gid = 138
GROUP BY borough_name
ORDER BY total DESC
이 쿼리는 LaGuardia 또는 JFK 공항에서 끝나는 택시 탑승의 보로별 총 수를 합산해요. 결과는 다음과 같고, 픽업 동네를 알 수 없는 탑승이 꽤 많다는 것에 주목하세요:
┌─total─┬─borough_name──┐
│ 23683 │ Unknown │
│ 7053 │ Manhattan │
│ 6828 │ Brooklyn │
│ 4458 │ Queens │
│ 2670 │ Bronx │
│ 554 │ Staten Island │
│ 53 │ EWR │
└───────┴───────────────┘
7 rows in set. Elapsed: 0.019 sec. Processed 2.00 million rows, 4.00 MB (105.70 million rows/s., 211.40 MB/s.)
5. JOIN 수행하기
taxi_zone_dictionary를 trips 테이블과 조인하는 쿼리를 작성해요.
- 위의 이전 공항 쿼리와 유사하게 동작하는 간단한
JOIN으로 시작해요:
SELECT
count(1) AS total,
Borough
FROM trips
JOIN taxi_zone_dictionary ON toUInt64(trips.pickup_nyct2010_gid) = taxi_zone_dictionary.LocationID
WHERE dropoff_nyct2010_gid = 132 OR dropoff_nyct2010_gid = 138
GROUP BY Borough
ORDER BY total DESC
응답은 dictGet 쿼리와 동일해 보여요:
┌─total─┬─Borough───────┐
│ 7053 │ Manhattan │
│ 6828 │ Brooklyn │
│ 4458 │ Queens │
│ 2670 │ Bronx │
│ 554 │ Staten Island │
│ 53 │ EWR │
└───────┴───────────────┘
6 rows in set. Elapsed: 0.034 sec. Processed 2.00 million rows, 4.00 MB (59.14 million rows/s., 118.29 MB/s.)
위 JOIN 쿼리의 출력이 그 전에 dictGetOrDefault를 사용한 쿼리와 동일하다는 것에 주목하세요 (Unknown 값이 포함되지 않은 것만 제외). 내부적으로 ClickHouse는 taxi_zone_dictionary 사전에 대해 실제로 dictGet 함수를 호출하지만, JOIN 문법이 SQL 개발자에게 더 익숙해요.
- 이 쿼리는 가장 높은 팁을 가진 1000개 탑승에 대한 행을 반환한 다음, 각 행을 사전과 inner join해요:
SELECT *
FROM trips
JOIN taxi_zone_dictionary
ON trips.dropoff_nyct2010_gid = taxi_zone_dictionary.LocationID
WHERE tip_amount > 0
ORDER BY tip_amount DESC
LIMIT 1000
일반적으로 ClickHouse에서 SELECT *는 자주 사용하는 것을 피해요. 실제로 필요한 컬럼만 검색해야 해요.
다음 단계 (Next steps)
다음 문서로 ClickHouse를 더 자세히 배워 보세요:
- ClickHouse 기본 인덱스 소개: ClickHouse가 스파스 기본 인덱스를 사용해 쿼리 중 관련 데이터를 효율적으로 찾는 방법을 배워요.
- 외부 데이터 소스 통합: 파일, Kafka, PostgreSQL, 데이터 파이프라인 등을 포함한 데이터 소스 통합 옵션을 검토해요.
- ClickHouse 데이터 시각화: 선호하는 UI/BI 도구를 ClickHouse에 연결해요.
- SQL Reference: ClickHouse에서 사용 가능한 SQL 함수를 찾아 데이터 변환·처리·분석에 활용해요.