01 ClickHouse Cloud

Crea el esquema de taxis, carga datos históricos y verifícalos mediante el cliente, las skills y ClickHouse MCP.

Los comandos de esta página usan los valores guardados en .env.workshop.

Resultado

En unos 15 minutos, crearás el esquema de taxis, cargarás un mes de datos públicos de taxis de Nueva York y verás resultados reales en el panel Historical.

Requisito previo: módulo 00 completo y terminal situada en ClickHouse_Demos/workshops/build_workshop/app.

Paso 1: verifica la conexión del cliente

Sustituye el marcador del nombre de host. La opción --password sin valor solicita la contraseña sin mostrarla, por lo que no queda en el historial del 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()"

Continúa solo cuando la consulta devuelva una fila.

Paso 2: crea el esquema

Este es el comando completo del esquema. Cópialo desde esta página; no abras ningún archivo 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

Verifica los objetos:

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

Resultado esperado: taxi_zones, taxi_trips, fhv_trips y ambas vistas ampliadas. La vista materializada de CDC se crea más adelante a propósito, después de que el módulo 03 cree su tabla de origen.

Paso 3: carga datos históricos públicos

Es seguro repetir el comando: cada inserción incluye una comprobación del recuento.

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

Verifica la carga:

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
  "

Resultado esperado: 265 zonas y unos 3,2 millones de viajes.

Paso 4: usa las skills y ClickHouse MCP

Ejecuta ambos prompts en el agente configurado en el módulo 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.

La primera respuesta debe explicar car_type, pickup_datetime; la segunda debe citar el resultado de una consulta de tu servicio. Esto verifica de forma explícita tanto la skill instalada como la conexión MCP.

Paso 5: reinicia y consulta la aplicación

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

Abre el panel Historical y prueba esta unión con la dimensión en la consola SQL de Cloud o en tu cliente local:

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 es único, por lo que un INNER JOIN normal asigna a cada viaje exactamente una fila de zona y conserva todos los viajes coincidentes antes de la agregación.

Comprobación final

La consulta de verificación muestra 265 zonas y unos 3,2 millones de viajes.
La revisión de la skill explica la contrapartida de la clave de ordenación.
ClickHouse MCP devuelve resultados fundamentados en tu servicio.
El panel Historical muestra datos.

Continúa en 02 Aplicación base.