Análise geoespacial com o conjunto de dados de táxis de Nova York

Neste tutorial, você vai explorar como o ClickHouse pode ser usado para executar consultas analíticas em grandes volumes de dados. Você também verá como usar um dicionário para enriquecer os dados e escrever consultas com junções.

Pré-requisitos

Para este tutorial, você vai precisar de:

Uma conta no ClickHouse Cloud (US$ 300 em créditos gratuitos ao se cadastrar)
Um serviço do ClickHouse Cloud

Crie a tabela

O conjunto de dados usado neste tutorial é o de táxis da cidade de Nova York, que contém detalhes sobre milhões de corridas de táxi, com colunas como valor da gorjeta, pedágios, tipo de pagamento e muito mais.

Selecione SQL Console no menu à esquerda
Clique na aba + ao lado do ícone de início para criar uma nova consulta
No editor SQL, digite a consulta abaixo e clique em 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;

Insira os dados

Agora que você criou uma tabela, adicione os dados de táxi da cidade de Nova York a partir de arquivos CSV no S3.O comando a seguir insere cerca de 2.000.000 de linhas na sua tabela trips a partir de dois arquivos diferentes no S3: trips_1.tsv.gz e 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

Aguarde até que a inserção dos dados seja concluída. Cerca de 150 MB de dados serão baixados. Quando a inserção terminar, verifique o número de linhas na tabela trips:

SELECT count() FROM trips

O resultado deve ter 1.999.657 linhas

Analise os dados

Com os dados carregados, você pode executar algumas consultas para analisá-los.

Calcule o valor médio das gorjetas:

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

Calcule o custo médio com base no número de passageiros:

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

Calcule o número diário de embarques por bairro:

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 a duração de cada corrida em minutos e, em seguida, agrupe os resultados por duração:

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

Mostre o número de embarques em cada bairro, discriminado por hora do dia:

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

Criar um dicionário

Em seguida, você criará um dicionário (um mapeamento de pares chave-valor armazenado em memória) chamado taxi_zone_dictionary para mapear IDs de localização para nomes de distritos (boroughs) de NYC, com base em um arquivo CSV que contém todos os bairros da cidade de Nova York. Esses IDs correspondem às colunas pickup_nyct2010_gid e dropoff_nyct2010_gid na tabela de corridas.Veja abaixo um trecho do arquivo CSV que você está usando, em formato de tabela. A coluna LocationID do arquivo corresponde às colunas pickup_nyct2010_gid e dropoff_nyct2010_gid da sua tabela 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

Execute o comando SQL a seguir, que cria um dicionário chamado taxi_zone_dictionary e o preenche com os dados do arquivo CSV no S3. A URL do arquivo é 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())

Definir LIFETIME como 0 desabilita as atualizações automáticas, evitando tráfego desnecessário para o nosso bucket do S3. Em outros cenários, talvez seja conveniente configurá-lo de outra forma. Para mais detalhes, consulte Atualização de dados de dicionário usando LIFETIME.

Verifique se funcionou. A consulta a seguir deve retornar 265 linhas, ou seja, uma linha para cada bairro:

SELECT * FROM taxi_zone_dictionary

Execute consultas usando o dicionário

Você pode usar a função dictGet (ou suas variações) para obter um valor de um dicionário. Basta informar o nome do dicionário, o valor desejado e a chave (que, no nosso exemplo, é a coluna LocationID de taxi_zone_dictionary) para receber o valor correspondente. Por exemplo, a consulta a seguir retorna o Borough cujo LocationID é 132 (que corresponde ao aeroporto JFK):

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

O JFK fica no Queens. Observe que o tempo para recuperar o valor é praticamente zero:

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

1 rows in set. Elapsed: 0.004 sec.

Use a função dictHas para verificar se uma chave existe no dicionário. Por exemplo, a consulta a seguir retorna 1 (que equivale a “true” no ClickHouse):

SELECT dictHas('taxi_zone_dictionary', 132)

A consulta a seguir retorna 0 porque 4567 não é um valor de LocationID no dicionário:

SELECT dictHas('taxi_zone_dictionary', 4567)

Use a função dictGet para obter o nome de um distrito em uma consulta. Por exemplo:

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 soma o número de corridas de táxi por distrito que terminam no aeroporto LaGuardia ou no JFK. O resultado é semelhante ao seguinte. Observe que há muitas corridas em que o bairro de embarque é desconhecido:

┌─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.)

Faça uma junção

Por fim, escreva algumas consultas que façam a junção do taxi_zone_dictionary com a sua tabela trips.Comece com um JOIN simples que funcione de forma semelhante à consulta sobre aeroportos mostrada anteriormente:

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

A resposta é idêntica à da consulta com 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 o resultado da consulta JOIN acima é o mesmo da consulta anterior, que usava dictGetOrDefault (exceto pelo fato de que os valores Unknown não são incluídos). Nos bastidores, o ClickHouse está, na verdade, chamando a função dictGet para o dicionário taxi_zone_dictionary, mas a sintaxe JOIN é mais familiar para desenvolvedores SQL.

Esta consulta retorna as linhas das 1000 corridas com os maiores valores de gorjeta e, em seguida, realiza uma junção interna de cada linha com o dicionário:

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

Prefira listar apenas as colunas de que sua consulta precisa, em vez de usar SELECT *. Como o ClickHouse armazena os dados por coluna, selecionar menos colunas reduz diretamente o volume de dados lidos, descomprimidos e processados.

Próximos passos

Saiba mais sobre o ClickHouse na documentação a seguir:

Introdução aos índices primários no ClickHouse: Saiba como o ClickHouse usa índices primários esparsos para localizar com eficiência os dados relevantes durante as consultas.
Integre uma fonte de dados externa: Conheça as opções de integração de fontes de dados, incluindo arquivos, Kafka, PostgreSQL, pipelines de dados e muitas outras.
Visualize dados no ClickHouse: Conecte sua ferramenta de UI/BI preferida ao ClickHouse.
Referência SQL: Explore as funções SQL disponíveis no ClickHouse para transformar, processar e analisar dados.

Primeiros passos

Configuração

Conjuntos de dados de exemplo

Migrar

Análise geoespacial com o conjunto de dados de táxis de Nova York

Pré-requisitos

Crie a tabela

Insira os dados

Analise os dados

Criar um dicionário

Execute consultas usando o dicionário

Faça uma junção

Próximos passos

​Pré-requisitos

Crie a tabela

Insira os dados

Analise os dados

Criar um dicionário

Execute consultas usando o dicionário

Faça uma junção

​Próximos passos

Pré-requisitos

Próximos passos