pg_clickhouse Tutorial

Time: 30.978 ms
  ```
- Calculate the length of each trip in minutes, then group the results by
  trip length:

  ```
  taxi=# SELECT
      avg(tip_amount) AS avg_tip,
      avg(fare_amount) AS avg_fare,
      avg(passenger_count) AS avg_passenger,
      count(*) AS count,
      round((date_part('epoch', dropoff_datetime) - date_part('epoch', pickup_datetime)) / 60) as trip_minutes
  FROM taxi.trips
  WHERE round((date_part('epoch', dropoff_datetime) - date_part('epoch', pickup_datetime)) / 60) > 0
  GROUP BY trip_minutes
  ORDER BY trip_minutes DESC
  LIMIT 5;
        avg_tip      |     avg_fare     |  avg_passenger   | count | trip_minutes
  -------------------+------------------+------------------+-------+--------------
                1.96 |                8 |                1 |     1 |        27512
                   0 |               12 |                2 |     1 |        27500
   0.562727272727273 | 17.4545454545455 | 2.45454545454545 |    11 |         1440
   0.716564885496183 | 14.2786259541985 | 1.94656488549618 |   131 |         1439
    1.00945205479452 | 12.8787671232877 | 1.98630136986301 |   146 |         1438
  (5 rows)

  Time: 45.477 ms
  ```
- Show the number of pickups in each neighborhood broken down by hour of the day:

  ```
  taxi=# SELECT
      pickup_ntaname,
      date_part('hour', pickup_datetime) as pickup_hour,
      SUM(1) AS pickups
  FROM taxi.trips
  WHERE pickup_ntaname != ''
  GROUP BY pickup_ntaname, pickup_hour
  ORDER BY pickup_ntaname, date_part('hour', pickup_datetime)
  LIMIT 5;
   pickup_ntaname | pickup_hour | pickups
  ----------------+-------------+---------
   Airport        |           0 |    3509
   Airport        |           1 |    1184
   Airport        |           2 |     401
   Airport        |           3 |     152
   Airport        |           4 |     213
  (5 rows)

  Time: 36.895 ms
  ```
- Set display time zone for New York and retrieve rides to LaGuardia or JFK
  airports:

  ```
  taxi=# SET timezone = 'America/New_York';
  SET
  taxi=# 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 taxi.trips
  WHERE dropoff_nyct2010_gid IN (132, 138)
  ORDER BY pickup_datetime
  LIMIT 5;
      pickup_datetime     |    dropoff_datetime    | total_amount | pickup_nyct2010_gid | dropoff_nyct2010_gid | airport_code | year | day | hour
  ------------------------+------------------------+--------------+---------------------+----------------------+--------------+------+-----+------
   2015-06-30 20:04:14-04 | 2015-06-30 20:15:29-04 |        13.30 |                 -34 |                  132 | JFK          | 2015 |  30 |   20
   2015-06-30 20:09:42-04 | 2015-06-30 20:12:55-04 |         6.80 |                  50 |                  138 | LGA          | 2015 |  30 |   20
   2015-06-30 20:23:04-04 | 2015-06-30 20:24:39-04 |         4.80 |                -125 |                  132 | JFK          | 2015 |  30 |   20
   2015-06-30 20:27:51-04 | 2015-06-30 20:39:02-04 |        14.72 |                -101 |                  138 | LGA          | 2015 |  30 |   20
   2015-06-30 20:32:03-04 | 2015-06-30 20:55:39-04 |        39.34 |                  48 |                  138 | LGA          | 2015 |  30 |   20
  (5 rows)

  Time: 17.450 ms
  ```

## Create a Dictionary[​](#create-a-dictionary "Direct link to Create a Dictionary")

Create a dictionary associated with a table in your ClickHouse service. The
table and dictionary are based on a CSV file that contains a row for each
neighborhood in New York City.

The neighborhoods are mapped to the names of the five New York City boroughs
(Bronx, Brooklyn, Manhattan, Queens and Staten Island), as well as Newark
Airport (EWR).

Here's an excerpt from the CSV file you're using in table format. The
`LocationID` column in the file maps to the `pickup_nyct2010_gid` and
`dropoff_nyct2010_gid` columns in your trips table:

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

1. Still in Postgres, use the `clickhouse_raw_query` function to create a
   ClickHouse [dictionary](/docs/sql-reference/statements/create/dictionary) named `taxi_zone_dictionary` and populate the
   dictionary from the CSV file in S3:

   ```
   SELECT clickhouse_raw_query($$
       CREATE DICTIONARY taxi.taxi_zone_dictionary (
           LocationID Int64 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())
   $$, 'host=localhost dbname=taxi');
   ```

   Note

   Setting `LIFETIME` to 0 disables automatic updates to avoid unnecessary
   traffic to our S3 bucket. In other cases, you might configure it
   differently. For details, see [Refreshing dictionary data using
   LIFETIME](/docs/sql-reference/statements/create/dictionary/lifetime).

   2. Now import it:

   ```
   IMPORT FOREIGN SCHEMA taxi LIMIT TO (taxi_zone_dictionary)
   FROM SERVER taxi_srv INTO taxi;
   ```

   3. Confirm we can query it:

   ```
   taxi=# SELECT * FROM taxi.taxi_zone_dictionary limit 3;
    LocationID |  Borough  |                     Zone                      | service_zone
   ------------+-----------+-----------------------------------------------+--------------
            77 | Brooklyn  | East New York/Pennsylvania Avenue             | Boro Zone
           106 | Brooklyn  | Gowanus                                       | Boro Zone
           103 | Manhattan | Governor's Island/Ellis Island/Liberty Island | Yellow Zone
   (3 rows)
   ```

   4. Excellent. Now use the `dictGet` function unction to retrieve a
      borough's name in a query. For this query sums up the number of taxi
      rides per borough that end at either the LaGuardia or JFK airport:

   ```
   taxi=# SELECT
           count(1) AS total,
           COALESCE(NULLIF(dictGet(
               'taxi.taxi_zone_dictionary', 'Borough',
               toUInt64(pickup_nyct2010_gid)
           ), ''), 'Unknown') AS borough_name
       FROM taxi.trips
       WHERE dropoff_nyct2010_gid = 132 OR dropoff_nyct2010_gid = 138
       GROUP BY borough_name
       ORDER BY total DESC;
    total | borough_name
   -------+---------------
    23683 | Unknown
     7053 | Manhattan
     6828 | Brooklyn
     4458 | Queens
     2670 | Bronx
      554 | Staten Island
       53 | EWR
   (7 rows)

   Time: 66.245 ms
   ```

   This query sums up the number of taxi rides per borough that end at either
   the LaGuardia or JFK airport. Notice there are quite a few trips where the
   pickup neighborhood is unknown.

## Perform a join[​](#perform-a-join "Direct link to Perform a join")