01 ClickHouse Cloud

택시 스키마를 만들고, 과거 데이터를 시드한 뒤, 클라이언트와 스킬, ClickHouse MCP로 검증합니다.

결과물

약 15분 안에 택시 스키마를 만들고, 공개 NYC 택시 데이터 한 달치를 로드하고, Historical 대시보드가 실제 결과를 반환하는 것을 확인합니다.

사전 조건: Module 00이 완료되어 있고 터미널이 ClickHouse_Demos/workshops/build_workshop/app에 있어야 합니다.

Step 1 — 클라이언트 연결 확인

호스트명 자리표시자를 바꾸세요. 값 없는 --password 플래그는 비밀번호를 화면에 표시하지 않고 입력을 요청하므로 셸 히스토리에 남지 않습니다:

workshop_env() { sed -n "s/^$1=//p" .env.workshop | tail -n 1; }
CLICKHOUSE_HOST=$(workshop_env CLICKHOUSE_HOST)
CLICKHOUSE_USER=$(workshop_env CLICKHOUSE_USER)
CLICKHOUSE_PASSWORD=$(workshop_env CLICKHOUSE_PASSWORD)
unset -f workshop_env

clickhouse client \
  --host "$CLICKHOUSE_HOST" \
  --port 9440 \
  --secure \
  --user "$CLICKHOUSE_USER" \
  --password "$CLICKHOUSE_PASSWORD" \
  --query "SELECT version(), currentUser()"

쿼리가 한 행을 반환할 때만 계속하세요.

Step 2 — 스키마 만들기

이것이 완전한 스키마 명령입니다. 이 페이지에서 복사하세요. 로컬 SQL 파일을 열지 마세요.

clickhouse client \
  --host "$CLICKHOUSE_HOST" \
  --port 9440 \
  --secure \
  --user "$CLICKHOUSE_USER" \
  --password "$CLICKHOUSE_PASSWORD" \
  --multiquery <<'SQL'
CREATE DATABASE IF NOT EXISTS nyc_tlc_data;

CREATE TABLE IF NOT EXISTS nyc_tlc_data.taxi_zones
(
  location_id UInt16,
  zone String,
  borough String,
  subregion String
)
ENGINE = MergeTree
ORDER BY (location_id);

CREATE TABLE IF NOT EXISTS nyc_tlc_data.fhv_trips
(
  hvfhs_license_num String,
  company String,
  dispatching_base_num Nullable(String),
  originating_base_num Nullable(String),
  request_datetime Nullable(DateTime('UTC')),
  on_scene_datetime Nullable(DateTime('UTC')),
  pickup_datetime DateTime('UTC'),
  dropoff_datetime DateTime('UTC'),
  pickup_location_id Nullable(UInt16),
  dropoff_location_id Nullable(UInt16),
  pickup_borough Nullable(String),
  dropoff_borough Nullable(String),
  trip_miles Nullable(Float64),
  trip_time Nullable(UInt32),
  base_passenger_fare Nullable(Float64),
  tolls Nullable(Float64),
  black_car_fund Nullable(Float64),
  sales_tax Nullable(Float64),
  congestion_surcharge Nullable(Float64),
  airport_fee Nullable(Float64),
  tips Nullable(Float64),
  driver_pay Nullable(Float64),
  shared_request Nullable(Bool),
  shared_match Nullable(Bool),
  access_a_ride Nullable(Bool),
  wav_request Nullable(Bool),
  wav_match Nullable(Bool),
  legacy_shared_ride Nullable(UInt16),
  filename String
)
ENGINE = MergeTree
ORDER BY (company, pickup_datetime);

CREATE TABLE IF NOT EXISTS nyc_tlc_data.taxi_trips
(
  car_type String,
  vendor_id Nullable(UInt16),
  pickup_datetime DateTime('UTC'),
  dropoff_datetime DateTime('UTC'),
  pickup_location_id Nullable(UInt16),
  dropoff_location_id Nullable(UInt16),
  pickup_borough Nullable(String),
  dropoff_borough Nullable(String),
  passenger_count Nullable(UInt16),
  trip_distance Nullable(Float64),
  rate_code_id Nullable(UInt16),
  store_and_fwd_flag Nullable(Bool),
  payment_type Nullable(UInt16),
  fare_amount Nullable(Float64),
  extra Nullable(Float64),
  mta_tax Nullable(Float64),
  tip_amount Nullable(Float64),
  tolls_amount Nullable(Float64),
  improvement_surcharge Nullable(Float64),
  total_amount Nullable(Float64),
  congestion_surcharge Nullable(Float64),
  airport_fee Nullable(Float64),
  trip_type Nullable(UInt16),
  ehail_fee Nullable(Float64),
  filename String
)
ENGINE = MergeTree
ORDER BY (car_type, pickup_datetime);

CREATE OR REPLACE VIEW nyc_tlc_data.fhv_trips_expanded AS
SELECT
  *,
  trip_time / 60 AS trip_minutes,
  trip_miles / trip_time * 3600 AS mph,
  (
    trip_miles >= 0.2
    AND trip_miles < 100
    AND trip_time >= 60
    AND trip_time < 60 * 60 * 4
    AND mph >= 1
    AND mph < 100
    AND base_passenger_fare >= 2
    AND base_passenger_fare < 2000
    AND driver_pay >= 1
    AND driver_pay < 2000
  ) AS reasonable_time_distance_fare,
  (
    shared_request = false
    AND access_a_ride = false
    AND wav_request = false
  ) AS solo_non_special_request,
  coalesce(tolls, 0) +
    coalesce(black_car_fund, 0) +
    coalesce(sales_tax, 0) +
    coalesce(congestion_surcharge, 0) +
    coalesce(airport_fee, 0) AS extra_charges
FROM nyc_tlc_data.fhv_trips;

CREATE OR REPLACE VIEW nyc_tlc_data.taxi_trips_expanded AS
SELECT
  *,
  (dropoff_datetime - pickup_datetime) / 60 AS trip_minutes,
  trip_distance / (dropoff_datetime - pickup_datetime) * 3600 AS mph,
  (
    trip_distance >= 0.2
    AND trip_distance < 100
    AND trip_minutes >= 1
    AND trip_minutes < 240
    AND mph >= 1
    AND mph < 100
    AND fare_amount >= 2
    AND fare_amount < 2000
    AND total_amount >= 2
    AND total_amount < 2000
  ) AS reasonable_time_distance_fare,
  coalesce(extra, 0) +
    coalesce(mta_tax, 0) +
    coalesce(tolls_amount, 0) +
    coalesce(improvement_surcharge, 0) +
    coalesce(congestion_surcharge, 0) +
    coalesce(airport_fee, 0) +
    coalesce(ehail_fee, 0) AS extra_charges
FROM nyc_tlc_data.taxi_trips;
SQL

객체를 확인하세요:

clickhouse client \
  --host "$CLICKHOUSE_HOST" \
  --port 9440 \
  --secure \
  --user "$CLICKHOUSE_USER" \
  --password "$CLICKHOUSE_PASSWORD" \
  --query "SHOW TABLES FROM nyc_tlc_data"

예상 결과: taxi_zones, taxi_trips, fhv_trips, 그리고 두 개의 expanded 뷰. CDC용 materialized view는 Module 03에서 소스 테이블을 만든 뒤에 생성하도록 의도적으로 미뤄둡니다.

Step 3 — 공개 과거 데이터 시드

이 명령은 다시 실행해도 안전합니다. 각 insert에는 카운트 가드가 있습니다.

clickhouse client \
  --host "$CLICKHOUSE_HOST" \
  --port 9440 \
  --secure \
  --user "$CLICKHOUSE_USER" \
  --password "$CLICKHOUSE_PASSWORD" \
  --multiquery <<'SQL'
INSERT INTO nyc_tlc_data.taxi_zones (location_id, zone, borough, subregion)
SELECT LocationID, Zone, Borough, service_zone
FROM url(
  'https://d37ci6vzurychx.cloudfront.net/misc/taxi_zone_lookup.csv',
  'CSVWithNames',
  'LocationID UInt16, Borough String, Zone String, service_zone String'
)
WHERE (SELECT count() FROM nyc_tlc_data.taxi_zones) = 0;

INSERT INTO nyc_tlc_data.taxi_trips (
  car_type, vendor_id, pickup_datetime, dropoff_datetime, pickup_location_id,
  dropoff_location_id, pickup_borough, dropoff_borough, passenger_count,
  trip_distance, rate_code_id, store_and_fwd_flag, payment_type, fare_amount,
  extra, mta_tax, tip_amount, tolls_amount, improvement_surcharge,
  total_amount, congestion_surcharge, airport_fee, filename
)
SELECT
  'yellow',
  VendorID,
  tpep_pickup_datetime,
  tpep_dropoff_datetime,
  PULocationID,
  DOLocationID,
  multiIf(
    PULocationID IN (SELECT location_id FROM nyc_tlc_data.taxi_zones WHERE borough = 'Bronx'), 'Bronx',
    PULocationID IN (SELECT location_id FROM nyc_tlc_data.taxi_zones WHERE borough = 'Brooklyn'), 'Brooklyn',
    PULocationID IN (SELECT location_id FROM nyc_tlc_data.taxi_zones WHERE borough = 'Manhattan'), 'Manhattan',
    PULocationID IN (SELECT location_id FROM nyc_tlc_data.taxi_zones WHERE borough = 'Queens'), 'Queens',
    PULocationID IN (SELECT location_id FROM nyc_tlc_data.taxi_zones WHERE borough = 'Staten Island'), 'Staten Island',
    PULocationID IN (SELECT location_id FROM nyc_tlc_data.taxi_zones WHERE borough = 'EWR'), 'EWR',
    null
  ),
  multiIf(
    DOLocationID IN (SELECT location_id FROM nyc_tlc_data.taxi_zones WHERE borough = 'Bronx'), 'Bronx',
    DOLocationID IN (SELECT location_id FROM nyc_tlc_data.taxi_zones WHERE borough = 'Brooklyn'), 'Brooklyn',
    DOLocationID IN (SELECT location_id FROM nyc_tlc_data.taxi_zones WHERE borough = 'Manhattan'), 'Manhattan',
    DOLocationID IN (SELECT location_id FROM nyc_tlc_data.taxi_zones WHERE borough = 'Queens'), 'Queens',
    DOLocationID IN (SELECT location_id FROM nyc_tlc_data.taxi_zones WHERE borough = 'Staten Island'), 'Staten Island',
    DOLocationID IN (SELECT location_id FROM nyc_tlc_data.taxi_zones WHERE borough = 'EWR'), 'EWR',
    null
  ),
  passenger_count,
  trip_distance,
  RatecodeID,
  multiIf(store_and_fwd_flag = 'Y', true, store_and_fwd_flag = 'N', false, null),
  payment_type,
  fare_amount,
  extra,
  mta_tax,
  tip_amount,
  tolls_amount,
  improvement_surcharge,
  total_amount,
  congestion_surcharge,
  airport_fee,
  'yellow_tripdata_2022-07.parquet'
FROM url(
  'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2022-07.parquet',
  'Parquet'
)
WHERE (
  SELECT count() FROM nyc_tlc_data.taxi_trips
  WHERE filename = 'yellow_tripdata_2022-07.parquet'
) = 0;
SQL

로드 결과를 확인하세요:

clickhouse client \
  --host "$CLICKHOUSE_HOST" \
  --port 9440 \
  --secure \
  --user "$CLICKHOUSE_USER" \
  --password "$CLICKHOUSE_PASSWORD" \
  --query "
    SELECT 'taxi_zones' AS table, count() AS rows FROM nyc_tlc_data.taxi_zones
    UNION ALL
    SELECT 'taxi_trips', count() FROM nyc_tlc_data.taxi_trips
  "

예상 결과: zone 265개, 운행 약 320만 건.

Step 4 — 스킬과 ClickHouse MCP 사용하기

Module 00에서 설정한 에이전트에서 두 프롬프트를 모두 실행하세요.

Use the ClickHouse best-practices skill to review the taxi_trips ORDER BY key.
Explain which workshop filters it supports and one production tradeoff. Do not change the schema.

Use the clickhouse-cloud MCP, with read-only queries, to verify the taxi_trips row count
and report the busiest pickup hour.

첫 번째 답변은 car_type, pickup_datetime을 다뤄야 하고, 두 번째는 여러분 서비스의 쿼리 결과를 인용해야 합니다. 이는 설치된 스킬과 MCP 연결을 모두 명확히 검증합니다.

Step 5 — 앱 재시작과 쿼리

cd "$(git rev-parse --show-toplevel)/workshops/build_workshop/app"
docker compose --env-file .env.workshop -f docker-compose.workshop.yml up -d
docker compose --env-file .env.workshop -f docker-compose.workshop.yml ps

Historical 대시보드를 열고, Cloud SQL 콘솔이나 로컬 클라이언트에서 이 차원 조인을 실행해 보세요:

SELECT
  z.zone AS pickup_zone,
  z.borough,
  count() AS trips,
  round(avg(t.fare_amount), 2) AS avg_fare
FROM nyc_tlc_data.taxi_trips AS t
INNER JOIN nyc_tlc_data.taxi_zones AS z
  ON t.pickup_location_id = z.location_id
GROUP BY pickup_zone, z.borough
ORDER BY trips DESC
LIMIT 10;

location_id는 유일하므로 일반 INNER JOIN은 각 운행에 정확히 하나의 zone 행을 매칭시키면서, 집계 전에 매칭되는 모든 운행을 보존합니다.

완료 확인

검증 쿼리가 zone 265개와 운행 약 320만 건을 보고합니다.
스킬 검토가 정렬 키의 트레이드오프를 설명합니다.
ClickHouse MCP가 여러분 서비스에 근거한 결과를 반환합니다.
Historical 대시보드가 데이터를 렌더링합니다.

02 기본 앱으로 계속하세요.

결과물