Análisis geoespacial con el conjunto de datos de taxis de Nueva York

En este tutorial, descubrirá cómo usar ClickHouse para ejecutar consultas analíticas sobre grandes volúmenes de datos. También verá cómo utilizar un diccionario para enriquecer los datos y escribir consultas con JOIN.

Requisitos previos

Para este tutorial, necesitará:

Una cuenta de ClickHouse Cloud (300 USD en créditos gratuitos al registrarte)
Un servicio de ClickHouse Cloud

Cree la tabla

El conjunto de datos que se utiliza en este tutorial es el de los taxis de la ciudad de Nueva York, que contiene información sobre millones de viajes en taxi, con columnas como el importe de la propina, los peajes, el tipo de pago, entre otras.

Seleccione SQL console en el menú de la izquierda
Haga clic en la pestaña + situada junto al icono de inicio para crear una nueva consulta
En el editor SQL, escriba la siguiente consulta y, a continuación, haga clic en Run:

Expandable

CREATE TABLE trips
(
    `trip_id` UInt32,
    `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
    `pickup_date` Date,
    `pickup_datetime` DateTime,
    `dropoff_date` Date,
    `dropoff_datetime` DateTime,
    `store_and_fwd_flag` UInt8,
    `rate_code_id` UInt8,
    `pickup_longitude` Float64,
    `pickup_latitude` Float64,
    `dropoff_longitude` Float64,
    `dropoff_latitude` Float64,
    `passenger_count` UInt8,
    `trip_distance` Float64,
    `fare_amount` Float32,
    `extra` Float32,
    `mta_tax` Float32,
    `tip_amount` Float32,
    `tolls_amount` Float32,
    `ehail_fee` Float32,
    `improvement_surcharge` Float32,
    `total_amount` Float32,
    `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
    `trip_type` UInt8,
    `pickup` FixedString(25),
    `dropoff` FixedString(25),
    `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
    `pickup_nyct2010_gid` Int8,
    `pickup_ctlabel` Float32,
    `pickup_borocode` Int8,
    `pickup_ct2010` String,
    `pickup_boroct2010` String,
    `pickup_cdeligibil` String,
    `pickup_ntacode` FixedString(4),
    `pickup_ntaname` String,
    `pickup_puma` UInt16,
    `dropoff_nyct2010_gid` UInt8,
    `dropoff_ctlabel` Float32,
    `dropoff_borocode` UInt8,
    `dropoff_ct2010` String,
    `dropoff_boroct2010` String,
    `dropoff_cdeligibil` String,
    `dropoff_ntacode` FixedString(4),
    `dropoff_ntaname` String,
    `dropoff_puma` UInt16
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(pickup_date)
ORDER BY pickup_datetime;

Inserte los datos

Ahora que ha creado una tabla, añada los datos de taxis de la ciudad de Nueva York a partir de archivos CSV almacenados en S3.El siguiente comando inserta aproximadamente 2.000.000 de filas en su tabla trips a partir de dos archivos distintos en S3: trips_1.tsv.gz y trips_2.tsv.gz:

Expandable

INSERT INTO trips
SELECT * FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{1..2}.gz',
    'TabSeparatedWithNames', "
    `trip_id` UInt32,
    `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
    `pickup_date` Date,
    `pickup_datetime` DateTime,
    `dropoff_date` Date,
    `dropoff_datetime` DateTime,
    `store_and_fwd_flag` UInt8,
    `rate_code_id` UInt8,
    `pickup_longitude` Float64,
    `pickup_latitude` Float64,
    `dropoff_longitude` Float64,
    `dropoff_latitude` Float64,
    `passenger_count` UInt8,
    `trip_distance` Float64,
    `fare_amount` Float32,
    `extra` Float32,
    `mta_tax` Float32,
    `tip_amount` Float32,
    `tolls_amount` Float32,
    `ehail_fee` Float32,
    `improvement_surcharge` Float32,
    `total_amount` Float32,
    `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
    `trip_type` UInt8,
    `pickup` FixedString(25),
    `dropoff` FixedString(25),
    `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
    `pickup_nyct2010_gid` Int8,
    `pickup_ctlabel` Float32,
    `pickup_borocode` Int8,
    `pickup_ct2010` String,
    `pickup_boroct2010` String,
    `pickup_cdeligibil` String,
    `pickup_ntacode` FixedString(4),
    `pickup_ntaname` String,
    `pickup_puma` UInt16,
    `dropoff_nyct2010_gid` UInt8,
    `dropoff_ctlabel` Float32,
    `dropoff_borocode` UInt8,
    `dropoff_ct2010` String,
    `dropoff_boroct2010` String,
    `dropoff_cdeligibil` String,
    `dropoff_ntacode` FixedString(4),
    `dropoff_ntaname` String,
    `dropoff_puma` UInt16
") SETTINGS input_format_try_infer_datetimes = 0

Espere a que finalice la inserción de los datos. Se descargarán alrededor de 150 MB de datos. Una vez finalizada la inserción, compruebe el número de filas de la tabla trips:

SELECT count() FROM trips

Deberías obtener como resultado 1,999,657 filas

Analice los datos

Con los datos ya cargados, puede ejecutar algunas consultas para analizarlos.

Calcule el importe medio de las propinas:

SELECT round(avg(tip_amount), 2) FROM trips

Calcule el coste medio en función del número de pasajeros:

SELECT
    passenger_count,
    ceil(avg(total_amount),2) AS average_total_amount
FROM trips
GROUP BY passenger_count

Calcule el número diario de recogidas por barrio:

SELECT
  pickup_date,
  pickup_ntaname,
  SUM(1) AS number_of_trips
FROM trips
GROUP BY pickup_date, pickup_ntaname
ORDER BY pickup_date ASC

Calcule la duración de cada viaje en minutos y agrupe los resultados según esa duración:

SELECT
  avg(tip_amount) AS avg_tip,
  avg(fare_amount) AS avg_fare,
  avg(passenger_count) AS avg_passenger,
  count() AS count,
  truncate(date_diff('second', pickup_datetime, dropoff_datetime)/60) as trip_minutes
FROM trips
WHERE trip_minutes > 0
GROUP BY trip_minutes
ORDER BY trip_minutes DESC

Muestre el número de recogidas en cada barrio, desglosado por hora del día:

SELECT
    pickup_ntaname,
    toHour(pickup_datetime) as pickup_hour,
    SUM(1) AS pickups
FROM trips
WHERE pickup_ntaname != ''
GROUP BY pickup_ntaname, pickup_hour
ORDER BY pickup_ntaname, pickup_hour

Cree un diccionario

A continuación, creará un diccionario (una correspondencia de pares clave-valor almacenada en memoria) llamado taxi_zone_dictionary que asocia los ID de ubicación con los nombres de los distritos de NYC, a partir de un archivo CSV que contiene todos los barrios de la ciudad de Nueva York. Estos ID corresponden a las columnas pickup_nyct2010_gid y dropoff_nyct2010_gid de la tabla trips.A continuación se muestra, en formato de tabla, un extracto del archivo CSV que va a utilizar. La columna LocationID del archivo se corresponde con las columnas pickup_nyct2010_gid y dropoff_nyct2010_gid de su tabla trips:

LocationID	Borough	Zone	service_zone
1	EWR	Newark Airport	EWR
2	Queens	Jamaica Bay	Boro Zone
3	Bronx	Allerton/Pelham Gardens	Boro Zone
4	Manhattan	Alphabet City	Yellow Zone
5	Staten Island	Arden Heights	Boro Zone

Ejecute el siguiente comando SQL, que crea un diccionario llamado taxi_zone_dictionary y lo puebla con los datos del archivo CSV almacenado en S3. La URL del archivo es https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv.

CREATE DICTIONARY taxi_zone_dictionary
(
  `LocationID` UInt16 DEFAULT 0,
  `Borough` String,
  `Zone` String,
  `service_zone` String
)
PRIMARY KEY LocationID
SOURCE(HTTP(URL 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv' FORMAT 'CSVWithNames'))
LIFETIME(MIN 0 MAX 0)
LAYOUT(HASHED_ARRAY())

Si se establece LIFETIME en 0, se desactivan las actualizaciones automáticas para evitar tráfico innecesario hacia nuestro bucket de S3. En otros casos, puede que le convenga configurarlo de otra forma. Para obtener más información, consulte Actualización de los datos del diccionario mediante LIFETIME.

Compruebe que ha funcionado. La siguiente consulta debería devolver 265 filas, es decir, una por cada barrio:

SELECT * FROM taxi_zone_dictionary

Ejecutar consultas con el diccionario

Puede usar la función dictGet (o sus variantes) para obtener un valor de un diccionario. Basta con indicar el nombre del diccionario, el valor que desea y la clave (que en nuestro ejemplo es la columna LocationID de taxi_zone_dictionary) para obtener el valor correspondiente. Por ejemplo, la siguiente consulta devuelve el Borough cuyo LocationID es 132 (que corresponde al aeropuerto JFK):

SELECT dictGet('taxi_zone_dictionary', 'Borough', 132)

JFK está en Queens. Observe que el tiempo necesario para obtener el valor es prácticamente 0:

┌─dictGet('taxi_zone_dictionary', 'Borough', 132)─┐
│ Queens                                          │
└─────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.004 sec.

Use la función dictHas para comprobar si una clave existe en el diccionario. Por ejemplo, la siguiente consulta devuelve 1 (que en ClickHouse equivale a “true”):

SELECT dictHas('taxi_zone_dictionary', 132)

La siguiente consulta devuelve 0 porque 4567 no es un valor de LocationID del diccionario:

SELECT dictHas('taxi_zone_dictionary', 4567)

Utilice la función dictGet para obtener el nombre de un distrito en una consulta. Por ejemplo:

SELECT
  count(1) AS total,
  dictGetOrDefault('taxi_zone_dictionary','Borough', toUInt64(pickup_nyct2010_gid), 'Unknown') AS borough_name
FROM trips
WHERE dropoff_nyct2010_gid = 132 OR dropoff_nyct2010_gid = 138
GROUP BY borough_name
ORDER BY total DESC

Esta consulta suma el número de viajes en taxi por distrito que terminan en el aeropuerto de LaGuardia o en el de JFK. El resultado es el siguiente; observe que hay bastantes viajes cuyo barrio de recogida se desconoce:

┌─total─┬─borough_name──┐
│ 23683 │ Unknown       │
│  7053 │ Manhattan     │
│  6828 │ Brooklyn      │
│  4458 │ Queens        │
│  2670 │ Bronx         │
│   554 │ Staten Island │
│    53 │ EWR           │
└───────┴───────────────┘

7 rows in set. Elapsed: 0.019 sec. Processed 2.00 million rows, 4.00 MB (105.70 million rows/s., 211.40 MB/s.)

Realizar un JOIN

Por último, escriba algunas consultas que hagan un join de taxi_zone_dictionary con su tabla trips.Comience con un JOIN sencillo que funcione de forma similar a la consulta anterior sobre aeropuertos:

SELECT
    count(1) AS total,
    Borough
FROM trips
JOIN taxi_zone_dictionary ON toUInt64(trips.pickup_nyct2010_gid) = taxi_zone_dictionary.LocationID
WHERE dropoff_nyct2010_gid = 132 OR dropoff_nyct2010_gid = 138
GROUP BY Borough
ORDER BY total DESC

La respuesta es idéntica a la de la consulta con dictGet:

┌─total─┬─Borough───────┐
│  7053 │ Manhattan     │
│  6828 │ Brooklyn      │
│  4458 │ Queens        │
│  2670 │ Bronx         │
│   554 │ Staten Island │
│    53 │ EWR           │
└───────┴───────────────┘

6 rows in set. Elapsed: 0.034 sec. Processed 2.00 million rows, 4.00 MB (59.14 million rows/s., 118.29 MB/s.)

Observe que el resultado de la consulta JOIN anterior es el mismo que el de la consulta previa que usaba dictGetOrDefault (salvo que no se incluyen los valores Unknown). Internamente, ClickHouse está llamando a la función dictGet sobre el diccionario taxi_zone_dictionary, pero la sintaxis JOIN resulta más familiar para los desarrolladores de SQL.

Esta consulta devuelve las filas de los 1000 viajes con el importe de propina más alto y, a continuación, realiza un inner join de cada fila con el diccionario:

SELECT *
FROM trips
JOIN taxi_zone_dictionary
ON trips.dropoff_nyct2010_gid = taxi_zone_dictionary.LocationID
WHERE tip_amount > 0
ORDER BY tip_amount DESC
LIMIT 1000

Es preferible enumerar solo las columnas que necesita su consulta en lugar de usar SELECT *. Como ClickHouse almacena los datos por columnas, seleccionar menos columnas reduce directamente el volumen de datos que se leen, descomprimen y procesan.

Próximos pasos

Obtenga más información sobre ClickHouse en la siguiente documentación:

Introducción a los índices primarios en ClickHouse: Descubra cómo ClickHouse utiliza índices primarios dispersos para localizar de forma eficiente los datos relevantes durante las consultas.
Integrar una fuente de datos externa: Consulte las opciones de integración de fuentes de datos, como archivos, Kafka, PostgreSQL, canalizaciones de datos y muchas más.
Visualizar datos en ClickHouse: Conecte su herramienta de UI/BI favorita a ClickHouse.
Referencia de SQL: Explore las funciones SQL disponibles en ClickHouse para transformar, procesar y analizar datos.

Primeros pasos

Configuración

Conjuntos de datos de ejemplo

Migración

Análisis geoespacial con el conjunto de datos de taxis de Nueva York

Requisitos previos

Cree la tabla

Inserte los datos

Analice los datos

Cree un diccionario

Ejecutar consultas con el diccionario

Realizar un JOIN

Próximos pasos

​Requisitos previos

Cree la tabla

Inserte los datos

Analice los datos

Cree un diccionario

Ejecutar consultas con el diccionario

Realizar un JOIN

​Próximos pasos

Requisitos previos

Próximos pasos