01 ClickHouse Cloud

创建出租车表结构、导入历史数据，并用客户端、skills 和 ClickHouse MCP 验证它。

成果

在约 15 分钟 内，你将创建出租车表结构、加载一个月的公开纽约出租车数据，并看到 Historical 看板返回真实结果。

前置条件：模块 00 已完成，并且你的终端位于 ClickHouse_Demos/workshops/build_workshop/app。

第 1 步：验证客户端连接

替换主机名占位符。不带值的 --password 标志会提示输入密码而不回显，从而避免它进入 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()"

只有当查询返回一行时才继续往下。

第 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 是有意留到后面才创建的，要等模块 03 创建它的源表之后。

第 3 步：导入公开历史数据

这条命令可以安全地重复运行：每个插入都带有行数保护条件。

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
  "

预期输出：265 个 zone，以及大约 320 万 条行程。

第 4 步：使用 skills 和 ClickHouse MCP

在模块 00 中配置好的 agent 里运行这两个提示。

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；第二个回答必须引用来自你的服务的查询结果。这一步明确地同时验证了已安装的 skill 和 MCP 连接。

第 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 控制台或你的本地客户端中试试这个维度 join：

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 记录，同时在聚合之前保留所有能匹配上的行程。

完成检查

验证查询报告 265 个 zone 和约 320 万条行程。
skill 的审查解释了排序键的权衡取舍。
ClickHouse MCP 返回的结果基于你自己的服务。
Historical 看板能渲染出数据。

继续前往 02 基础应用。

成果