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

Explore o ClickHouse com um conjunto de dados sobre bilhões de viagens de táxi e de veículos de transporte por aplicativo (Uber, Lyft etc.) com origem na cidade de Nova York desde 2009

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

1. 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.

   1. Selecione SQL Console no menu à esquerda
   2. Clique na aba + ao lado do ícone de início para criar uma nova consulta
   3. 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;
   
2. 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

3. 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
     
4. 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
   
5. 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.)
   
6. 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.