> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-revert-104359-revert-104251-parquet-single.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # Tutorial avançado > Aprenda a ingerir e consultar dados no ClickHouse usando um conjunto de dados de exemplo de táxis da cidade de Nova York. { e.preventDefault(); window.location.href = (window.location.pathname.startsWith('/docs') ? '/docs' : '') + '/get-started/quickstarts/home'; }} className="inline-flex items-center gap-1.5 text-sm text-gray-500 dark:text-zinc-500 hover:text-gray-900 dark:hover:text-[#fdff75] transition-colors font-normal no-underline">All quickstarts
Analytics em tempo real Armazenamento de dados Cloud OSS
## Visão geral
Aprenda a ingerir e consultar dados no ClickHouse usando o conjunto de dados de exemplo de táxis da cidade de Nova York.
### Pré-requisitos
Você precisa ter acesso a um serviço ClickHouse em execução para concluir este tutorial. Para obter instruções, consulte o guia [Quick Start](/pt-BR/get-started/setup/install). O conjunto de dados de táxis da cidade de Nova York contém informações sobre milhões de corridas, incluindo colunas como valor da gorjeta, pedágios, tipo de pagamento e muito mais. Crie uma tabela para armazenar esses dados. 1. Conecte-se ao console SQL: * No ClickHouse Cloud, selecione um serviço no menu suspenso e, em seguida, **SQL Console** no menu de navegação à esquerda. * No ClickHouse autogerenciado, conecte-se ao console SQL em `https://_hostname_:8443/play`. Consulte o administrador do ClickHouse para obter mais detalhes. 2. Crie a seguinte tabela `trips` no banco de dados `default`: ```sql theme={null} 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; ``` Agora que você criou uma tabela, adicione os dados de táxis da cidade de Nova York a partir de arquivos CSV no S3. 1. O comando a seguir insere cerca de 2.000.000 de linhas na tabela `trips` a partir de dois arquivos diferentes no S3: `trips_1.tsv.gz` e `trips_2.tsv.gz`: ```sql theme={null} 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 ``` 2. Aguarde a conclusão do `INSERT`. O download dos 150 MB de dados pode levar alguns instantes. 3. Quando a inserção terminar, verifique se ela foi concluída com êxito: ```sql theme={null} SELECT count() FROM trips ``` Esta consulta deve retornar 1.999.657 linhas. Execute algumas consultas para analisar os dados. Explore os exemplos a seguir ou experimente sua própria consulta SQL. * Calcule o valor médio das gorjetas: ```sql theme={null} SELECT round(avg(tip_amount), 2) FROM trips ```

```response theme={null} ┌─round(avg(tip_amount), 2)─┐ │ 1.68 │ └───────────────────────────┘ ```

* Calcule o custo médio com base no número de passageiros: ```sql theme={null} SELECT passenger_count, ceil(avg(total_amount),2) AS average_total_amount FROM trips GROUP BY passenger_count ```

O valor de `passenger_count` varia de 0 a 9: ```response theme={null} ┌─passenger_count─┬─average_total_amount─┐ │ 0 │ 22.69 │ │ 1 │ 15.97 │ │ 2 │ 17.15 │ │ 3 │ 16.76 │ │ 4 │ 17.33 │ │ 5 │ 16.35 │ │ 6 │ 16.04 │ │ 7 │ 59.8 │ │ 8 │ 36.41 │ │ 9 │ 9.81 │ └─────────────────┴──────────────────────┘ ```

* Calcule o número diário de corridas iniciadas por bairro: ```sql theme={null} SELECT pickup_date, pickup_ntaname, SUM(1) AS number_of_trips FROM trips GROUP BY pickup_date, pickup_ntaname ORDER BY pickup_date ASC ```

```response theme={null} ┌─pickup_date─┬─pickup_ntaname───────────────────────────────────────────┬─number_of_trips─┐ │ 2015-07-01 │ Brooklyn Heights-Cobble Hill │ 13 │ │ 2015-07-01 │ Old Astoria │ 5 │ │ 2015-07-01 │ Flushing │ 1 │ │ 2015-07-01 │ Yorkville │ 378 │ │ 2015-07-01 │ Gramercy │ 344 │ │ 2015-07-01 │ Fordham South │ 2 │ │ 2015-07-01 │ SoHo-TriBeCa-Civic Center-Little Italy │ 621 │ │ 2015-07-01 │ Park Slope-Gowanus │ 29 │ │ 2015-07-01 │ Bushwick South │ 5 │ ```

* Calcule a duração de cada viagem em minutos e agrupe os resultados por duração da viagem: ```sql theme={null} 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 ```

```response theme={null} ┌──────────────avg_tip─┬───────────avg_fare─┬──────avg_passenger─┬──count─┬─trip_minutes─┐ │ 1.9600000381469727 │ 8 │ 1 │ 1 │ 27511 │ │ 0 │ 12 │ 2 │ 1 │ 27500 │ │ 0.542166673981895 │ 19.716666666666665 │ 1.9166666666666667 │ 60 │ 1439 │ │ 0.902499997522682 │ 11.270625001192093 │ 1.95625 │ 160 │ 1438 │ │ 0.9715789457909146 │ 13.646616541353383 │ 2.0526315789473686 │ 133 │ 1437 │ │ 0.9682692398245518 │ 14.134615384615385 │ 2.076923076923077 │ 104 │ 1436 │ │ 1.1022105210705808 │ 13.778947368421052 │ 2.042105263157895 │ 95 │ 1435 │ ```

* Mostre o número de embarques em cada bairro, detalhado por hora do dia: ```sql theme={null} 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 ```

```response theme={null} ┌─pickup_ntaname───────────────────────────────────────────┬─pickup_hour─┬─pickups─┐ │ Airport │ 0 │ 3509 │ │ Airport │ 1 │ 1184 │ │ Airport │ 2 │ 401 │ │ Airport │ 3 │ 152 │ │ Airport │ 4 │ 213 │ │ Airport │ 5 │ 955 │ │ Airport │ 6 │ 2161 │ │ Airport │ 7 │ 3013 │ │ Airport │ 8 │ 3601 │ │ Airport │ 9 │ 3792 │ │ Airport │ 10 │ 4546 │ │ Airport │ 11 │ 4659 │ │ Airport │ 12 │ 4621 │ │ Airport │ 13 │ 5348 │ │ Airport │ 14 │ 5889 │ │ Airport │ 15 │ 6505 │ │ Airport │ 16 │ 6119 │ │ Airport │ 17 │ 6341 │ │ Airport │ 18 │ 6173 │ │ Airport │ 19 │ 6329 │ │ Airport │ 20 │ 6271 │ │ Airport │ 21 │ 6649 │ │ Airport │ 22 │ 6356 │ │ Airport │ 23 │ 6016 │ │ Allerton-Pelham Gardens │ 4 │ 1 │ │ Allerton-Pelham Gardens │ 6 │ 1 │ │ Allerton-Pelham Gardens │ 7 │ 1 │ │ Allerton-Pelham Gardens │ 9 │ 5 │ │ Allerton-Pelham Gardens │ 10 │ 3 │ │ Allerton-Pelham Gardens │ 15 │ 1 │ │ Allerton-Pelham Gardens │ 20 │ 2 │ │ Allerton-Pelham Gardens │ 23 │ 1 │ │ Annadale-Huguenot-Prince's Bay-Eltingville │ 23 │ 1 │ │ Arden Heights │ 11 │ 1 │ ```

7. Recupere as corridas para os aeroportos LaGuardia ou JFK: ```sql theme={null} SELECT pickup_datetime, dropoff_datetime, total_amount, pickup_nyct2010_gid, dropoff_nyct2010_gid, CASE WHEN dropoff_nyct2010_gid = 138 THEN 'LGA' WHEN dropoff_nyct2010_gid = 132 THEN 'JFK' END AS airport_code, EXTRACT(YEAR FROM pickup_datetime) AS year, EXTRACT(DAY FROM pickup_datetime) AS day, EXTRACT(HOUR FROM pickup_datetime) AS hour FROM trips WHERE dropoff_nyct2010_gid IN (132, 138) ORDER BY pickup_datetime ```

```response theme={null} ┌─────pickup_datetime─┬────dropoff_datetime─┬─total_amount─┬─pickup_nyct2010_gid─┬─dropoff_nyct2010_gid─┬─airport_code─┬─year─┬─day─┬─hour─┐ │ 2015-07-01 00:04:14 │ 2015-07-01 00:15:29 │ 13.3 │ -34 │ 132 │ JFK │ 2015 │ 1 │ 0 │ │ 2015-07-01 00:09:42 │ 2015-07-01 00:12:55 │ 6.8 │ 50 │ 138 │ LGA │ 2015 │ 1 │ 0 │ │ 2015-07-01 00:23:04 │ 2015-07-01 00:24:39 │ 4.8 │ -125 │ 132 │ JFK │ 2015 │ 1 │ 0 │ │ 2015-07-01 00:27:51 │ 2015-07-01 00:39:02 │ 14.72 │ -101 │ 138 │ LGA │ 2015 │ 1 │ 0 │ │ 2015-07-01 00:32:03 │ 2015-07-01 00:55:39 │ 39.34 │ 48 │ 138 │ LGA │ 2015 │ 1 │ 0 │ │ 2015-07-01 00:34:12 │ 2015-07-01 00:40:48 │ 9.95 │ -93 │ 132 │ JFK │ 2015 │ 1 │ 0 │ │ 2015-07-01 00:38:26 │ 2015-07-01 00:49:00 │ 13.3 │ -11 │ 138 │ LGA │ 2015 │ 1 │ 0 │ │ 2015-07-01 00:41:48 │ 2015-07-01 00:44:45 │ 6.3 │ -94 │ 132 │ JFK │ 2015 │ 1 │ 0 │ │ 2015-07-01 01:06:18 │ 2015-07-01 01:14:43 │ 11.76 │ 37 │ 132 │ JFK │ 2015 │ 1 │ 1 │ ```

Um dicionário é um mapeamento de pares chave-valor armazenado em memória. Para mais detalhes, consulte [Dicionários](/pt-BR/reference/statements/create/dictionary) Crie um dicionário associado a uma tabela no seu serviço ClickHouse. A tabela e o dicionário são baseados em um arquivo CSV que contém uma linha para cada bairro da cidade de Nova York. Os bairros são mapeados para os nomes dos cinco distritos de Nova York (Bronx, Brooklyn, Manhattan, Queens e Staten Island), além do Aeroporto de Newark (EWR). Veja a seguir 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`: | ID da localização | Distrito municipal | Zona | zona\_de\_serviço | | ----------------- | ------------------ | ----------------------- | ------------------- | | 1 | EWR | Aeroporto de Newark | EWR | | 2 | Queens | Jamaica Bay | Zona administrativa | | 3 | Bronx | Allerton/Pelham Gardens | Zona administrativa | | 4 | Manhattan | Alphabet City | Zona Amarela | | 5 | Staten Island | Arden Heights | Zona do Distrito | 1. Execute o comando SQL a seguir, que cria um dicionário chamado `taxi_zone_dictionary` e o preenche com dados do arquivo CSV armazenado no S3. A URL do arquivo é `https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv`. ```sql theme={null} 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 desativa as atualizações automáticas para evitar tráfego desnecessário no nosso bucket do S3. Em outros casos, você pode configurá-lo de outra forma. Para mais detalhes, consulte [Atualização de dados de dicionário usando LIFETIME](/pt-BR/reference/statements/create/dictionary/lifetime). 3. Verifique se funcionou. O comando a seguir deve retornar 265 linhas, uma para cada bairro: ```sql theme={null} SELECT * FROM taxi_zone_dictionary ``` 4. Use a função `dictGet` ([ou suas variações](/pt-BR/reference/functions/regular-functions/ext-dict-functions)) para recuperar um valor de um dicionário. Informe o nome do dicionário, o valor desejado e a chave (que, neste exemplo, é a coluna `LocationID` de `taxi_zone_dictionary`). Por exemplo, a consulta a seguir retorna o `Borough` correspondente ao `LocationID` 132, que é o aeroporto JFK: ```sql theme={null} SELECT dictGet('taxi_zone_dictionary', 'Borough', 132) ``` JFK fica em Queens. Observe que o tempo para recuperar o valor é praticamente 0: ```response theme={null} ┌─dictGet('taxi_zone_dictionary', 'Borough', 132)─┐ │ Queens │ └─────────────────────────────────────────────────┘ 1 rows in set. Elapsed: 0.004 sec. ``` 5. Use a função `dictHas` para verificar se uma chave está presente no dicionário. Por exemplo, a consulta a seguir retorna `1` (que equivale a "true" no ClickHouse): ```sql theme={null} SELECT dictHas('taxi_zone_dictionary', 132) ``` 6. A consulta a seguir retorna 0 porque 4567 não é um valor de `LocationID` no Dicionário: ```sql theme={null} SELECT dictHas('taxi_zone_dictionary', 4567) ``` 7. Use a função `dictGet` para obter o nome de um distrito em uma consulta. Por exemplo: ```sql theme={null} 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 JFK. O resultado é semelhante ao seguinte. Observe que há várias corridas em que o bairro de embarque é desconhecido: ```response theme={null} ┌─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.) ``` Escreva algumas consultas que façam uma junção entre o `taxi_zone_dictionary` e sua tabela `trips`. 1. Comece com um `JOIN` simples que funciona de modo semelhante à consulta anterior sobre aeroportos: ```sql theme={null} 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`: ```response theme={null} ┌─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 a saída da consulta `JOIN` acima é igual à da consulta anterior que usava `dictGetOrDefault` (exceto pelos valores `Unknown`, que não estão incluídos). Internamente, o ClickHouse chama a função `dictGet` para o dicionário `taxi_zone_dictionary`, mas a sintaxe `JOIN` é mais familiar para desenvolvedores SQL. 2. Esta consulta retorna as linhas das 1.000 viagens com os maiores valores de gorjeta e, em seguida, realiza uma junção interna de cada linha com o dicionário: ```sql theme={null} 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 ``` Em geral, evitamos usar `SELECT *` com frequência no ClickHouse. Recupere apenas as colunas de que você realmente precisa.
## Próximas etapas
Saiba mais sobre o ClickHouse na documentação a seguir: * [Introdução aos índices primários no ClickHouse](/pt-BR/guides/clickhouse/data-modelling/sparse-primary-indexes): Saiba como o ClickHouse usa índices primários esparsos para localizar dados relevantes com eficiência durante as consultas. * [Integre uma fonte de dados externa](/pt-BR/integrations/home): Confira as opções de integração de fontes de dados, incluindo arquivos, Kafka, PostgreSQL, pipelines de dados e muitas outras. * [Visualize dados no ClickHouse](/pt-BR/integrations/connectors/data-visualization/index): Conecte sua ferramenta favorita de UI/BI ao ClickHouse. * [Referência de SQL](/pt-BR/reference/home): Explore as funções SQL disponíveis no ClickHouse para transformar, processar e analisar dados.
{ e.preventDefault(); window.location.href = (window.location.pathname.startsWith('/docs') ? '/docs' : '') + '/get-started/quickstarts/home'; }} className="inline-flex items-center gap-1.5 text-sm text-gray-500 dark:text-zinc-500 hover:text-gray-900 dark:hover:text-[#fdff75] transition-colors font-normal no-underline">All quickstarts