Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

고급 튜토리얼

View as MarkdownEdit this pageReport an issueSet up docs MCP
All quickstarts
실시간 분석데이터 웨어하우징CloudOSS

개요

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

사전 요구 사항

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

새 테이블 생성

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;

데이터셋 추가

테이블을 만들었으니 이제 S3의 CSV 파일에서 New York City 택시 데이터를 추가합니다.

  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개의 행을 반환해야 합니다.

데이터 분석

몇 가지 쿼리를 실행해 데이터를 분석해 보십시오. 다음 예시를 살펴보거나 직접 작성한 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. 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 │

딕셔너리 생성

딕셔너리(Dictionary)는 메모리에 저장되는 key-value 쌍의 매핑입니다. 자세한 내용은 Dictionaries를 참조하십시오

ClickHouse 서비스의 테이블과 연결된 딕셔너리를 생성합니다. 이 테이블과 딕셔너리는 뉴욕시의 각 지역(neighborhood)마다 하나의 행을 포함하는 CSV 파일을 기반으로 합니다.

각 지역(neighborhood)은 뉴욕시의 5개 자치구(borough) 이름(Bronx, Brooklyn, Manhattan, Queens, Staten Island)과 Newark Airport(EWR)에 매핑됩니다.

다음은 사용 중인 CSV 파일의 일부를 테이블 형식으로 나타낸 것입니다. 파일의 LocationID 컬럼은 trips 테이블의 pickup_nyct2010_gid 및 dropoff_nyct2010_gid 컬럼에 매핑됩니다:

위치 ID 자치구 구역 서비스_구역
1 EWR 뉴어크 공항 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())

  1. 제대로 작동하는지 확인합니다. 다음 쿼리는 265개의 행, 즉 각 neighborhood마다 1개의 행을 반환해야 합니다:

    SELECT * FROM taxi_zone_dictionary

  2. 딕셔너리에서 값을 조회하려면 dictGet 함수(또는 그 변형)를 사용합니다. 딕셔너리 이름, 조회할 값, 키를 전달합니다(이 예시에서 키는 taxi_zone_dictionary의 LocationID 컬럼입니다).

    예를 들어, 다음 쿼리는 JFK 공항에 해당하는 LocationID가 132인 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.

  3. dictHas 함수를 사용하여 딕셔너리에 키가 있는지 확인합니다. 예를 들어, 다음 쿼리는 1을 반환합니다(ClickHouse에서 "true"를 의미합니다):

    SELECT dictHas('taxi_zone_dictionary', 132)

  4. 다음 쿼리는 4567이 딕셔너리에 있는 LocationID 값이 아니므로 0을 반환합니다:

    SELECT dictHas('taxi_zone_dictionary', 4567)

  5. 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.)

JOIN 수행

taxi_zone_dictionary를 trips 테이블과 조인하는 몇 가지 쿼리를 작성합니다.

  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.)
  1. 이 쿼리는 팁 금액이 가장 높은 1000개 운행에 대한 행을 반환한 다음, 각 행을 딕셔너리와 내부 조인합니다. 
    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에 관해 자세히 알아보십시오.

All quickstarts
Navigation