본문으로 바로가기
본문으로 바로가기

고급 튜토리얼

개요​

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

Prerequisites​

이 튜토리얼을 완료하려면 실행 중인 ClickHouse 서비스에 대한 접근 권한이 있어야 합니다. 자세한 내용은 빠른 시작 가이드를 참조하십시오.

새 테이블 생성하기​

뉴욕시 택시 데이터셋은 수백만 건의 택시 운행에 대한 세부 정보를 포함하며, 팁 금액, 통행료, 결제 유형 등의 컬럼이 포함되어 있습니다. 이 데이터를 저장할 테이블을 생성하세요.

  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 파일에서 뉴욕시 택시 데이터를 추가하세요.

  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. INSERT가 완료되면 정상적으로 완료되었는지 확인합니다:

    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 │

딕셔너리 생성하기​

딕셔너리는 메모리에 저장된 key-value 쌍의 매핑입니다. 자세한 내용은 딕셔너리를 참조하세요.

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

neighborhoods는 뉴욕시의 5개 자치구(Bronx, Brooklyn, Manhattan, Queens, Staten Island) 이름 및 Newark 공항(EWR)에 매핑됩니다.

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

LocationID행정 구역세부 지역service_zone
1EWRNewark AirportEWR
2QueensJamaica BayBoro Zone
3BronxAllerton/Pelham GardensBoro Zone
4ManhattanAlphabet CityYellow Zone
5Staten IslandArden HeightsBoro Zone
  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개의 행, 즉 각 동네마다 하나의 행을 반환해야 합니다.

    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. 다음 쿼리는 딕셔너리에서 LocationID에 4567 값이 없으므로 0을 반환합니다.

    SELECT dictHas('taxi_zone_dictionary', 4567)

  5. dictGet 함수를 사용하여 쿼리에서 borough의 이름을 조회합니다. 예를 들어:

    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 공항에서 끝나는 택시 운행 횟수를 자치구별로 합산합니다. 결과는 다음과 같으며, 승차 지역(pickup neighborhood)이 알려지지 않은 운행이 상당히 많다는 점을 확인할 수 있습니다.

    ┌─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.)

조인 수행​

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.)
    참고

    위의 JOIN 쿼리 출력 결과는 dictGetOrDefault를 사용했던 이전 쿼리와 동일합니다. 단, Unknown 값은 포함되지 않습니다. 내부적으로는 ClickHouse가 taxi_zone_dictionary 딕셔너리에 대해 dictGet 함수를 호출하지만, JOIN 구문이 SQL 개발자에게는 더 익숙합니다.

  2. 이 쿼리는 가장 높은 팁 금액을 가진 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에서는 SELECT *를 자주 사용하지 않는 것이 좋습니다. 실제로 필요한 컬럼만 조회해야 합니다.

다음 단계​

다음 문서를 통해 ClickHouse에 대해 더 자세히 알아보십시오.