Skip to main content

​
개요

뉴욕시 택시 예시 데이터셋을 사용해 ClickHouse에서 데이터를 수집하고 쿼리하는 방법을 알아봅니다.

​
사전 준비 사항

이 튜토리얼을 완료하려면 실행 중인 ClickHouse 서비스에 액세스할 수 있어야 합니다. 자세한 내용은 Quick Start 가이드를 참조하십시오.
1

​
새 테이블 만들기

New York City 택시 데이터셋에는 팁 금액, 통행료, 결제 유형 등 다양한 컬럼이 포함된 수백만 건의 택시 운행 상세 정보가 들어 있습니다. 이 데이터를 저장할 테이블을 만드십시오.
  1. SQL 콘솔에 연결합니다:
    • ClickHouse Cloud의 경우 드롭다운 메뉴에서 서비스를 선택한 다음, 왼쪽 탐색 메뉴에서 SQL Console을 선택합니다.
    • 자가 관리형 ClickHouse의 경우 https://_hostname_:8443/play의 SQL 콘솔에 연결합니다. 자세한 내용은 ClickHouse 관리자에게 문의하십시오.
  2. 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 파일에서 뉴욕시 택시 데이터를 추가합니다.
  1. 다음 명령은 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
  2. INSERT가 완료될 때까지 기다리십시오. 150 MB 데이터를 다운로드하는 데 잠시 시간이 걸릴 수 있습니다.
  3. 삽입이 완료되면 정상적으로 수행되었는지 확인합니다. 
    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 │

  1. 라과디아 또는 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

​
딕셔너리 생성

딕셔너리는 메모리에 저장된 키-값 쌍의 매핑입니다. 자세한 내용은 Dictionaries를 참조하십시오.ClickHouse 서비스의 테이블과 연결된 딕셔너리를 생성합니다. 테이블과 딕셔너리는 뉴욕시의 각 동네 정보를 행으로 담고 있는 CSV 파일을 기반으로 합니다.각 지역은 뉴욕시의 5개 자치구(Bronx, Brooklyn, Manhattan, Queens, Staten Island) 이름과 Newark Airport(EWR)에 매핑됩니다.다음은 사용 중인 CSV 파일의 일부를 테이블 형식으로 나타낸 것입니다. 파일의 LocationID 컬럼은 trips 테이블의 pickup_nyct2010_gid 및 dropoff_nyct2010_gid 컬럼과 매핑됩니다.
위치 ID자치구구역서비스_구역
1EWR뉴어크 공항EWR
2퀸스자메이카 베이자치구 존
3브롱크스앨러턴/펠햄 가든스자치구 존
4맨해튼알파벳 시티옐로 존
5스태튼 아일랜드아든 하이츠보로 존
  1. 다음 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을 사용한 딕셔너리 데이터 갱신을 참조하십시오.
  1. 제대로 작동하는지 확인합니다. 다음은 265개 행, 즉 각 동네마다 1개 행을 반환해야 합니다: 
    SELECT * FROM taxi_zone_dictionary
  2. 딕셔너리에서 값을 조회하려면 dictGet 함수(또는 그 변형)를 사용합니다. 딕셔너리 이름, 조회하려는 값, 그리고 키(이 예시에서는 taxi_zone_dictionary의 LocationID 컬럼)를 전달합니다. 예를 들어, 다음 쿼리는 LocationID가 132인 Borough를 반환하는데, 이는 JFK 공항에 해당합니다): 
    SELECT dictGet('taxi_zone_dictionary', 'Borough', 132)
    JFK는 퀸스에 있습니다. 값을 가져오는 데 걸리는 시간이 사실상 0인 점을 확인하십시오: 
    ┌─dictGet('taxi_zone_dictionary', 'Borough', 132)─┐
│ Queens                                          │
└─────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.004 sec.
  3. dictHas 함수를 사용해 딕셔너리에 키가 존재하는지 확인합니다. 예를 들어, 다음 쿼리는 1을 반환합니다(ClickHouse에서 이는 “true”를 의미합니다): 
    SELECT dictHas('taxi_zone_dictionary', 132)
  4. 다음 쿼리는 4567이 딕셔너리의 LocationID에 해당하는 값이 아니므로 0을 반환합니다: 
    SELECT dictHas('taxi_zone_dictionary', 4567)
  5. 쿼리에서 borough 이름을 조회하려면 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하는 몇 가지 쿼리를 작성해 보겠습니다.
  1. 먼저, 위의 공항 쿼리와 비슷하게 동작하는 간단한 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 개발자에게는 더 익숙합니다.
  1. 이 쿼리는 팁 금액이 가장 높은 1000개의 trip에 대한 행을 반환한 다음, 각 행을 딕셔너리와 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 *를 자주 사용하지 않는 것이 좋습니다. 실제로 필요한 컬럼만 조회하십시오.

​
다음 단계

다음 문서를 통해 ClickHouse를 더 자세히 알아보십시오:
Last modified on June 10, 2026