01 ClickHouse Cloud

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

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"

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
  "

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.

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

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;