Yandex.Cloud
  • Services
  • Why Yandex.Cloud
  • Pricing
  • Documentation
  • Contact us
Get started
Yandex Managed Service for ClickHouse
  • Getting started
  • Step-by-step instructions
    • All instructions
    • Information about existing clusters
    • Creating clusters
    • Connecting to databases
    • Stop and start the cluster
    • SQL queries in the management console
    • Changing cluster and database settings
    • Connecting to DataLens
    • Connecting external dictionaries
    • Adding your own geobase
    • Enabling machine learning models
    • Changing ClickHouse versions
    • Managing ClickHouse hosts
    • Adding ZooKeeper hosts
    • Database management
    • Managing database users
    • Managing backups
    • How to manage shards
    • Deleting clusters
  • Solutions
    • Adding data to the database
    • Migrating ClickHouse data
    • Sharding tables
  • Concepts
    • Relationship between service resources
    • Host classes
    • Network in Yandex Managed Service for ClickHouse
    • Quotas and limits
    • Storage types
    • Backups
    • Replication
    • Dictionaries
    • Sharding
    • Supported clients
  • Access management
  • Pricing policy
    • Current pricing policy
    • Archive
      • Before January 1, 2019
      • From January 1 to March 1, 2019
      • From March 1, 2019 to February 1, 2020
  • API reference
    • Authentication in the API
    • gRPC
      • Overview
      • BackupService
      • ClusterService
      • DatabaseService
      • FormatSchemaService
      • MlModelService
      • ResourcePresetService
      • UserService
      • VersionsService
      • OperationService
    • REST
      • Overview
      • Backup
        • Overview
        • get
        • list
      • Cluster
        • Overview
        • addHosts
        • addShard
        • addZookeeper
        • backup
        • create
        • createExternalDictionary
        • createShardGroup
        • delete
        • deleteExternalDictionary
        • deleteHosts
        • deleteShard
        • deleteShardGroup
        • get
        • getShard
        • getShardGroup
        • list
        • listBackups
        • listHosts
        • listLogs
        • listOperations
        • listShardGroups
        • listShards
        • move
        • rescheduleMaintenance
        • restore
        • start
        • stop
        • streamLogs
        • update
        • updateShard
        • updateShardGroup
      • Database
        • Overview
        • create
        • delete
        • get
        • list
      • FormatSchema
        • Overview
        • create
        • delete
        • get
        • list
        • update
      • MlModel
        • Overview
        • create
        • delete
        • get
        • list
        • update
      • ResourcePreset
        • Overview
        • get
        • list
      • User
        • Overview
        • create
        • delete
        • get
        • grantPermission
        • list
        • revokePermission
        • update
      • Versions
        • Overview
        • list
      • Operation
        • Overview
        • get
  • Questions and answers
    • General questions
    • Questions about ClickHouse
    • All questions on the same page
  1. Concepts
  2. Dictionaries

Dictionaries

  • Internal dictionaries
  • External dictionaries

Dictionaries in ClickHouse are key-value data stores that are fully or partially loaded to the ClickHouse server memory.
Dictionary data is accessed using a set of functions that are faster than standard SQL queries. Access to dictionaries is read-only. You can't write data to a dictionary using ClickHouse.

The main advantage of dictionaries is that they're faster compared to JOIN operations. Dictionaries are useful when you have to frequently access reference lists to get a set of values from a key.

As their data sources, dictionaries can use ClickHouse internal dictionaries or external sources, including HTTP(s) resources or other DBMS (MySQL, ClickHouse, MongoDB, PostgreSQL).

Internal dictionaries

Managed Service for ClickHouse contains a built-in geobase dictionary and a set of functions to work with it. The dictionary lets you:

  • Get the name of a region in the required language by its ID.
  • Get the ID of a city, region, federal district, country, and continent by the region ID.
  • Check whether one region is part of another.
  • Get a chain of parent regions.

Learn more about the built-in dictionary features in the ClickHouse documentation.

If you're not satisfied with the built-in geobase, you can add your own to ClickHouse: Learn how to do this in Adding your own geobase.

External dictionaries

For external dictionaries, HTTP(s) resources and other DBMS (MySQL, ClickHouse, MongoDB, PostgreSQL) can be used as data sources.

Depending on the dictionary settings, ClickHouse fully or partially stores dictionaries in RAM, periodically updates them, and dynamically loads the missing values.

To learn how to add and configure a dictionary using Yandex Managed Service for ClickHouse, see Adding external dictionaries.

In this article:
  • Internal dictionaries
  • External dictionaries
Language
Careers
Privacy policy
Terms of use
© 2021 Yandex.Cloud LLC