01 ClickHouse Cloud

Tạo schema dữ liệu taxi, nạp dữ liệu lịch sử, và kiểm chứng bằng client, các skill, cùng ClickHouse MCP.

Kết quả

Trong khoảng 15 phút, bạn sẽ tạo schema dữ liệu taxi, nạp một tháng dữ liệu taxi NYC công khai, và thấy dashboard Historical trả về kết quả thật.

Yêu cầu tiên quyết: Module 00 đã hoàn thành và terminal của bạn đang ở ClickHouse_Demos/workshops/build_workshop/app.

Bước 1 — Kiểm chứng kết nối client

Hãy thay placeholder hostname. Cờ --password để trống sẽ hỏi mà không hiện lại password, giữ nó khỏi lịch sử shell:

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()"

Chỉ tiếp tục khi truy vấn trả về một dòng.

Bước 2 — Tạo schema

Đây là câu lệnh schema hoàn chỉnh. Hãy copy nó từ trang này; đừng mở một file SQL local.

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

Kiểm chứng các đối tượng:

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

Kết quả mong đợi: taxi_zones, taxi_trips, fhv_trips, và cả hai expanded view. Materialized view cho CDC được tạo sau một cách có chủ ý, sau khi Module 03 tạo bảng nguồn của nó.

Bước 3 — Nạp mồi dữ liệu lịch sử công khai

Câu lệnh này an toàn khi chạy lại: mỗi lệnh insert đều có một điều kiện bảo vệ theo số dòng.

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

Kiểm chứng việc nạp dữ liệu:

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
  "

Kết quả mong đợi: 265 zone và khoảng 3.2 triệu chuyến xe.

Bước 4 — Dùng các skill và ClickHouse MCP

Hãy chạy cả hai prompt trong agent đã cấu hình ở 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.

Câu trả lời đầu tiên nên bàn về car_type, pickup_datetime; câu thứ hai phải dẫn ra một kết quả truy vấn từ service của bạn. Điều này kiểm chứng tường minh cả skill đã cài lẫn kết nối MCP.

Bước 5 — Khởi động lại và truy vấn ứng dụng

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

Hãy mở dashboard Historical, rồi thử phép join theo chiều này trong Cloud SQL console hoặc trong client local của bạn:

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 là duy nhất, nên một INNER JOIN thông thường cho mỗi chuyến xe đúng một dòng zone khớp, đồng thời giữ lại mọi chuyến xe khớp trước khi tổng hợp.

Kiểm tra hoàn thành

Truy vấn kiểm chứng báo 265 zone và khoảng 3.2 triệu chuyến xe.
Phần soát của skill giải thích được sự đánh đổi của ordering key.
ClickHouse MCP trả về các kết quả có căn cứ từ service của bạn.
Dashboard Historical hiển thị dữ liệu.

Tiếp tục tới 02 Ứng dụng nền.