Advanced Tutorial

Create a dictionary

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

A dictionary is a mapping of key-value pairs stored in memory. For details, see [Dictionaries](/docs/sql-reference/statements/create/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. Run the following SQL command, which creates a dictionary named `taxi_zone_dictionary` and populates the dictionary from the CSV file in S3. The URL for the file is `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())
```

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

3. Verify it worked. The following should return 265 rows, or one row for each neighborhood:

   ```
   SELECT * FROM taxi_zone_dictionary
   ```
4. Use the `dictGet` function ([or its variations](/docs/sql-reference/functions/ext-dict-functions)) to retrieve a value from a dictionary. You pass in the name of the dictionary, the value you want, and the key (which in our example is the `LocationID` column of `taxi_zone_dictionary`).

   For example, the following query returns the `Borough` whose `LocationID` is 132, which corresponds to JFK airport):

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

   JFK is in Queens. Notice the time to retrieve the value is essentially 0:

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

   1 rows in set. Elapsed: 0.004 sec.
   ```
5. Use the `dictHas` function to see if a key is present in the dictionary. For example, the following query returns `1` (which is "true" in ClickHouse):

   ```
   SELECT dictHas('taxi_zone_dictionary', 132)
   ```
6. The following query returns 0 because 4567 isn't a value of `LocationID` in the dictionary:

   ```
   SELECT dictHas('taxi_zone_dictionary', 4567)
   ```
7. Use the `dictGet` function to retrieve a borough's name in a query. For example:

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

   This query sums up the number of taxi rides per borough that end at either the LaGuardia or JFK airport. The result looks like the following, and notice there are quite a few trips where the pickup neighborhood is unknown:

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

Perform a join

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

Write some queries that join the `taxi_zone_dictionary` with your `trips` table.

1. Start with a simple `JOIN` that acts similarly to the previous airport query above:

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

   The response looks identical to the `dictGet` query:

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

   Notice the output of the above `JOIN` query is the same as the query before it that used `dictGetOrDefault` (except that the `Unknown` values aren't included). Behind the scenes, ClickHouse is actually calling the `dictGet` function for the `taxi_zone_dictionary` dictionary, but the `JOIN` syntax is more familiar for SQL developers.
2. This query returns rows for the the 1000 trips with the highest tip amount, then performs an inner join of each row with the dictionary:

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

   Generally, we avoid using `SELECT *` often in ClickHouse. You should only retrieve the columns you actually need.

## Next steps[​](#next-steps "Direct link to Next steps")

Learn more about ClickHouse with the following documentation:

- [Introduction to Primary Indexes in ClickHouse](/docs/guides/best-practices/sparse-primary-indexes): Learn how ClickHouse uses sparse primary indexes to efficiently locate relevant data during queries.
- [Integrate an external data source](/docs/integrations): Review data source integration options, including files, Kafka, PostgreSQL, data pipelines, and many others.
- [Visualize data in ClickHouse](/docs/integrations/data-visualization): Connect your favorite UI/BI tool to ClickHouse.
- [SQL Reference](/docs/sql-reference): Browse the SQL functions available in ClickHouse for transforming, processing and analyzing data.

- [Overview](#overview)
  - [Prerequisites](#prerequisites)- [Create a new table](#create-a-new-table)- [Add the dataset](#add-the-dataset)- [Analyze the data](#analyze-the-data)- [Create a dictionary](#create-a-dictionary)- [Perform a join](#perform-a-join)- [Next steps](#next-steps)

Was this page helpful?