뉴욕 택시 데이터셋을 활용한 지리공간 분석

2009년 이후 뉴욕시에서 출발한 수십억 건의 택시 및 차량 호출 서비스(Uber, Lyft 등) 운행 데이터셋을 사용하여 ClickHouse를 살펴봅니다

이 튜토리얼에서는 ClickHouse로 대량의 데이터에 분석 쿼리를 실행하는 방법을 살펴봅니다. 또한 딕셔너리(Dictionary)를 사용해 데이터를 보강하고 조인(JOIN) 쿼리를 작성하는 방법도 알아봅니다.

​

사전 요구 사항

이 튜토리얼을 진행하려면 다음 항목이 필요합니다:

• ClickHouse Cloud 계정 (가입 시 $300 상당의 무료 크레딧 제공)
• ClickHouse Cloud 서비스

1. 테이블을 생성하세요

   이 튜토리얼에서는 뉴욕시 택시 데이터셋을 사용합니다. 이 데이터셋에는 수백만 건의 택시 운행에 대한 세부 정보가 담겨 있으며, 팁 금액, 통행료, 결제 유형 등의 컬럼이 포함되어 있습니다.

   1. 왼쪽 메뉴에서 SQL console을 선택하십시오.
   2. 홈 아이콘 옆의 + 탭을 클릭하여 새 쿼리를 만드십시오.
   3. SQL 편집기에 다음 쿼리를 입력한 다음 Run을 클릭하십시오.

   Expandable

   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에서 약 2,000,000개의 행을 trips 테이블에 삽입합니다.

   Expandable

   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
   

   데이터 삽입이 완료될 때까지 기다리십시오. 약 150MB의 데이터가 다운로드됩니다. 삽입이 완료되면 trips 테이블의 행 수를 확인하십시오:

   SELECT count() FROM trips
   

   결과로 1,999,657개의 행이 반환되어야 합니다

3. 데이터를 분석하세요

   데이터를 로드했으면 몇 가지 쿼리를 실행하여 데이터를 분석할 수 있습니다.

   • 평균 팁 금액을 계산합니다:

     SELECT round(avg(tip_amount), 2) FROM trips
     

   • 승객 수별 평균 요금을 계산합니다:

     SELECT
         passenger_count,
         ceil(avg(total_amount),2) AS average_total_amount
     FROM trips
     GROUP BY passenger_count
     

   • 지역별 일일 승차 건수를 계산합니다:

     SELECT
       pickup_date,
       pickup_ntaname,
       SUM(1) AS number_of_trips
     FROM trips
     GROUP BY pickup_date, pickup_ntaname
     ORDER BY pickup_date ASC
     

   • 각 운행의 소요 시간을 분 단위로 계산한 다음, 소요 시간별로 결과를 그룹화합니다:

     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
     

   • 지역별 승차 건수를 시간대별로 나누어 표시합니다:

     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
     
4. 딕셔너리를 생성하세요

   다음으로, 위치 ID와 NYC 자치구(borough) 이름을 매핑하는 taxi_zone_dictionary라는 딕셔너리(Dictionary, 메모리에 저장되는 key-value 쌍의 매핑)를 생성합니다. 이 딕셔너리는 뉴욕시의 모든 동네 정보가 담긴 CSV 파일을 소스로 사용합니다. 이 위치 ID는 trips 테이블의 pickup_nyct2010_gid 및 dropoff_nyct2010_gid 컬럼에 해당합니다.

   다음은 사용할 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개의 행, 즉 동네(neighborhood)마다 하나의 행을 반환해야 합니다.

   SELECT * FROM taxi_zone_dictionary
   
5. 딕셔너리를 사용해 쿼리를 실행하세요

   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)
   

   쿼리에서 자치구(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 공항에서 하차한 택시 운행 횟수를 자치구(borough)별로 합산합니다. 결과는 다음과 같으며, 승차 지역(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.)
   
6. 조인을 수행하세요

   마지막으로, 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 함수를 호출하지만, SQL 개발자에게는 JOIN 구문이 더 익숙합니다.

   이 쿼리는 팁 금액이 가장 높은 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
   

   SELECT *를 사용하기보다는 쿼리에 필요한 컬럼만 명시하는 것이 좋습니다. ClickHouse는 데이터를 컬럼 단위로 저장하므로, 선택하는 컬럼 수를 줄이면 읽기, 압축 해제, 처리 대상이 되는 데이터의 양이 그만큼 줄어듭니다.

​

다음 단계

ClickHouse에 대해 자세히 알아보려면 다음 문서를 참고하십시오:

• ClickHouse의 프라이머리 인덱스 소개: ClickHouse가 희소 프라이머리 인덱스(sparse primary index)를 사용해 쿼리 실행 시 필요한 데이터를 효율적으로 찾아내는 방식을 알아봅니다.
• 외부 데이터 소스 통합: 파일, Kafka, PostgreSQL, 데이터 파이프라인 등 다양한 데이터 소스 통합 옵션을 살펴봅니다.
• ClickHouse 데이터 시각화: 선호하는 UI/BI 도구를 ClickHouse에 연결합니다.
• SQL 참고: ClickHouse에서 데이터 변환, 처리 및 분석에 사용할 수 있는 SQL 함수를 살펴봅니다.