> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-8a08bda2.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Tutorial avanzado

> Aprende a ingestar y consultar datos en ClickHouse con un conjunto de datos de ejemplo de taxis de la ciudad de Nueva York.

<div id="overview">
  ## Descripción general
</div>

Aprenda a ingestar y consultar datos en ClickHouse con el conjunto de datos de ejemplo de taxis de la ciudad de Nueva York.

<div id="prerequisites">
  ### Requisitos previos
</div>

Necesita acceso a un servicio de ClickHouse en funcionamiento para completar este tutorial. Para obtener instrucciones, consulte la guía de [Inicio rápido](/es/get-started/setup/install).

<Steps>
  <Step>
    ## Crear una tabla nueva

    El conjunto de datos de taxis de la ciudad de Nueva York contiene información sobre millones de trayectos en taxi, con columnas como el importe de la propina, los peajes, el tipo de pago y más. Cree una tabla para almacenar estos datos.

    1. Conéctese a la consola SQL:
       * En ClickHouse Cloud, seleccione un servicio en el menú desplegable y, a continuación, **SQL Console** en el menú de navegación de la izquierda.
       * En ClickHouse autogestionado, conéctese a la consola SQL en `https://_hostname_:8443/play`. Consulte los detalles con su administrador de ClickHouse.

    2. Cree la siguiente tabla `trips` en la base de datos `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;
       ```
  </Step>

  <Step>
    ## Añade el conjunto de datos

    Ahora que has creado una tabla, añade los datos de taxis de la ciudad de Nueva York desde archivos CSV en S3.

    1. El siguiente comando inserta \~2,000,000 filas en tu tabla `trips` desde dos archivos diferentes en S3: `trips_1.tsv.gz` y `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. Espera a que termine el `INSERT`. La descarga de los 150 MB de datos puede tardar un momento.

    3. Cuando finalice la inserción, verifica que haya funcionado:

       ```sql theme={null}
       SELECT count() FROM trips
       ```

       Esta consulta debería devolver 1,999,657 filas.
  </Step>

  <Step>
    ## Analizar los datos

    Ejecute algunas consultas para analizar los datos. Explore los siguientes ejemplos o pruebe su propia consulta SQL.

    * Calcula el promedio de la propina:

      ```sql theme={null}
      SELECT round(avg(tip_amount), 2) FROM trips
      ```

          <Accordion title="Resultado esperado">
            <p>
              ```response theme={null}
              ┌─round(avg(tip_amount), 2)─┐
              │                      1.68 │
              └───────────────────────────┘
              ```
            </p>
          </Accordion>

    * Calcula el costo promedio según el número de pasajeros:

      ```sql theme={null}
      SELECT
          passenger_count,
          ceil(avg(total_amount),2) AS average_total_amount
      FROM trips
      GROUP BY passenger_count
      ```

          <Accordion title="Resultado esperado">
            <p>
              El valor de `passenger_count` va 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 │
              └─────────────────┴──────────────────────┘
              ```
            </p>
          </Accordion>

    * Calcula el número diario de recogidas por barrio:

      ```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
      ```

          <Accordion title="Resultado esperado">
            <p>
              ```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 │
              ```
            </p>
          </Accordion>

    * Calcula la duración de cada viaje en minutos y, a continuación, agrupa los resultados por la duración del viaje:

      ```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
      ```

          <Accordion title="Resultado esperado">
            <p>
              ```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 │
              ```
            </p>
          </Accordion>

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

      ```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
      ```

          <Accordion title="Resultado esperado">
            <p>
              ```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 │
              ```
            </p>
          </Accordion>

    7. Recupere  los viajes a los aeropuertos LaGuardia o 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
       ```

           <Accordion title="Salida esperada">
             <p>
               ```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 │
               ```
             </p>
           </Accordion>
  </Step>

  <Step>
    ## Crear un diccionario

    Un diccionario es una correspondencia de pares clave-valor almacenados en memoria. Para más detalles, consulte [Dictionaries](/es/reference/statements/create/dictionary)

    Cree un Diccionario asociado a una tabla en su servicio de ClickHouse.
    La tabla y el Diccionario se basan en un archivo CSV que contiene una fila por cada barrio de la ciudad de Nueva York.

    Los barrios están asociados a los nombres de los cinco distritos de la ciudad de Nueva York (Bronx, Brooklyn, Manhattan, Queens y Staten Island), así como al aeropuerto de Newark (EWR).

    A continuación se muestra un fragmento del archivo CSV que estás utilizando en formato de tabla. La columna `LocationID` del archivo se corresponde con las columnas `pickup_nyct2010_gid` y `dropoff_nyct2010_gid` de tu tabla `trips`:

    | LocationID | Distrito      | Zona                    | service\_zone    |
    | ---------- | ------------- | ----------------------- | ---------------- |
    | 1          | EWR           | Aeropuerto de Newark    | EWR              |
    | 2          | Queens        | Jamaica Bay             | Zona de distrito |
    | 3          | Bronx         | Allerton/Pelham Gardens | Zona de distrito |
    | 4          | Manhattan     | Alphabet City           | Yellow Zone      |
    | 5          | Staten Island | Arden Heights           | Boro Zone        |

    1. Ejecute el siguiente comando SQL, que crea un Diccionario llamado `taxi_zone_dictionary` y lo carga desde el archivo CSV en S3. La URL del archivo es `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())
    ```

    <Note>
      Configurar `LIFETIME` en 0 desactiva las actualizaciones automáticas para evitar tráfico innecesario hacia nuestro bucket de S3. En otros casos, puede que lo configure de otra manera. Para obtener más información, consulte [Actualización de los datos del diccionario con LIFETIME](/es/reference/statements/create/dictionary/lifetime).
    </Note>

    3. Verifique que haya funcionado. Lo siguiente debería devolver 265 filas, o una fila por cada barrio:
       ```sql theme={null}
       SELECT * FROM taxi_zone_dictionary
       ```

    4. Usa la función `dictGet` ([o sus variantes](/es/reference/functions/regular-functions/ext-dict-functions)) para obtener un valor de un diccionario. Debes indicar el nombre del diccionario, el valor que quieres obtener y la clave (que, en nuestro ejemplo, es la columna `LocationID` de `taxi_zone_dictionary`).

       Por ejemplo, la siguiente consulta devuelve el `Borough` cuyo `LocationID` es 132, que corresponde al aeropuerto JFK):

       ```sql theme={null}
       SELECT dictGet('taxi_zone_dictionary', 'Borough', 132)
       ```

       JFK está en Queens. Observa que el tiempo para recuperar el valor es prácticamente 0:

       ```response theme={null}
       ┌─dictGet('taxi_zone_dictionary', 'Borough', 132)─┐
       │ Queens                                          │
       └─────────────────────────────────────────────────┘

       1 rows in set. Elapsed: 0.004 sec.
       ```

    5. Utiliza la función `dictHas` para comprobar si una clave está presente en el diccionario. Por ejemplo, la siguiente consulta devuelve `1` (que equivale a "true" en ClickHouse):
       ```sql theme={null}
       SELECT dictHas('taxi_zone_dictionary', 132)
       ```

    6. La siguiente consulta devuelve 0 porque 4567 no es un valor de `LocationID` del diccionario:
       ```sql theme={null}
       SELECT dictHas('taxi_zone_dictionary', 4567)
       ```

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

       ```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 suma el número de viajes en taxi por distrito que terminan en los aeropuertos de LaGuardia o JFK. El resultado es como el siguiente, y fíjate en que hay bastantes viajes en los que se desconoce el barrio de recogida:

       ```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.)
       ```
  </Step>

  <Step>
    ## Realizar un join

    Escribe algunas consultas que hagan `JOIN` entre `taxi_zone_dictionary` y tu tabla `trips`.

    1. Empieza con un `JOIN` simple que funciona de forma similar a la consulta anterior sobre aeropuertos:

       ```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
       ```

       La respuesta es idéntica a la consulta `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.)
       ```

    <Note>
      Ten en cuenta que la salida de la consulta `JOIN` anterior es la misma que la de la consulta previa que usaba `dictGetOrDefault` (salvo que no se incluyen los valores `Unknown`). Internamente, ClickHouse en realidad llama a la función `dictGet` para el diccionario `taxi_zone_dictionary`, pero la sintaxis `JOIN` resulta más familiar para los desarrolladores de SQL.
    </Note>

    2. Esta consulta devuelve las filas correspondientes a 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:
       ```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
       ```

    <Note>
      En general, evitamos usar `SELECT *` con frecuencia en ClickHouse. Debes recuperar solo las columnas que realmente necesitas.
    </Note>
  </Step>
</Steps>

<div id="next-steps">
  ## Siguientes pasos
</div>

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

* [Introducción a los índices primarios en ClickHouse](/es/guides/clickhouse/data-modelling/sparse-primary-indexes): Aprenda cómo ClickHouse usa índices primarios dispersos para localizar de forma eficiente los datos relevantes durante las consultas.
* [Integre una fuente de datos externa](/es/integrations/home): Revise las opciones de integración de fuentes de datos, incluidos archivos, Kafka, PostgreSQL, canalizaciones de datos y muchas más.
* [Visualice datos en ClickHouse](/es/integrations/connectors/data-visualization): Conecte su herramienta de UI/BI favorita a ClickHouse.
* [Referencia SQL](/es/reference/home): Explore las funciones SQL disponibles en ClickHouse para transformar, procesar y analizar datos.