メインコンテンツまでスキップ
メインコンテンツまでスキップ

辞書

辞書は、さまざまな種類の参照リストに便利なマッピング（key -> attributes）です。

ClickHouseは、クエリで使用できる辞書を操作するための特別な関数をサポートします。辞書を関数と共に使用する方が、参照テーブルとのJOINよりも簡単で効率的です。

ClickHouseは次のことをサポートしています：

チュートリアル

ClickHouseの辞書の使い始めとして、関連するトピックをカバーするチュートリアルがあります。 こちらをご覧ください。

さまざまなデータソースから独自の辞書を追加できます。辞書のソースは、ClickHouseのテーブル、ローカルテキストまたは実行ファイル、HTTP(s)リソース、または別のDBMSである可能性があります。詳細については、"辞書ソース"を参照してください。

ClickHouseは以下のことを行います：

  • 辞書を完全または部分的にRAMに保存します。
  • 定期的に辞書を更新し、欠落している値を動的にロードします。言い換えれば、辞書は動的にロードできます。
  • xmlファイルやDDLクエリを使用して辞書を作成することを許可します。

辞書の構成は1つ以上のxmlファイルに存在することができます。構成へのパスは、dictionaries_configパラメータで指定されます。

辞書は、サーバーの起動時または初回使用時にロードでき、これはdictionaries_lazy_load設定に依存します。

辞書システムテーブルには、サーバーで構成された辞書に関する情報が含まれています。各辞書については、以下の情報を見つけることができます：

  • 辞書のステータス。
  • 構成パラメータ。
  • 辞書が正常にロードされて以来のRAMの割り当て量やクエリの数などのメトリック。
ヒント

If you are using a dictionary with ClickHouse Cloud please use the DDL query option to create your dictionaries, and create your dictionary as user default. Also, verify the list of supported dictionary sources in the Cloud Compatibility guide.

DDLクエリで辞書を作成する​

辞書はDDLクエリを使用して作成でき、これは推奨される方法です。なぜなら、DDLで作成された辞書は以下の利点があります：

  • サーバーの構成ファイルに追加のレコードが追加されません
  • 辞書はテーブルやビューのような一級エンティティとして扱えます
  • データは辞書テーブル関数ではなく、慣れ親しんだSELECTを使用して直接読み取ることができます
  • 辞書は簡単に名前変更できます

構成ファイルで辞書を作成する​

Not supported in ClickHouse Cloud
注記

構成ファイルで辞書を作成することは、ClickHouse Cloudには適用できません。上記のDDLを使用して、ユーザーdefaultとして辞書を作成してください。

辞書の構成ファイルは次の形式を持ちます：

同じファイルで任意の数の辞書を構成できます。

注記

小さな辞書の値を変換するには、SELECTクエリでそれを説明することができます（transform関数を参照）。この機能は辞書には関連していません。

辞書を設定する​

ヒント

If you are using a dictionary with ClickHouse Cloud please use the DDL query option to create your dictionaries, and create your dictionary as user default. Also, verify the list of supported dictionary sources in the Cloud Compatibility guide.

辞書がxmlファイルを使用して構成されている場合、その構成は次のような構造を持ちます：

対応するDDLクエリは次のような構造を持ちます：

メモリに辞書を保存する​

辞書をメモリに保存するためのさまざまな方法があります。

最適な処理速度を提供するため、flat、hashed、およびcomplex_key_hashedを推奨します。

キャッシングは、パフォーマンスが低下する可能性や最適なパラメータの選択の難しさから推奨されません。cacheのセクションで詳細を読むことができます。

辞書のパフォーマンスを向上させるためのいくつかの方法があります：

  • GROUP BYの後に辞書を操作するための関数を呼び出します。
  • 抽出する属性をinjectiveとしてマークします。ある属性がinjectiveであるとは、異なるキーが異なる属性値に対応する場合を指します。したがって、GROUP BYでキーによって属性値を取得する関数を使用する際、この関数は自動的にGROUP BYから除外されます。

ClickHouseは辞書に関連するエラーに対して例外を生成します。エラーの例：

  • アクセスされている辞書を読み込むことができませんでした。
  • cached辞書に対するクエリエラー。

system.dictionariesテーブルで辞書のリストとそのステータスを表示できます。

ヒント

If you are using a dictionary with ClickHouse Cloud please use the DDL query option to create your dictionaries, and create your dictionary as user default. Also, verify the list of supported dictionary sources in the Cloud Compatibility guide.

構成は次のように見えます：

対応するDDLクエリ：

complex-key*という単語が含まれないレイアウトの辞書は、UInt64型のキーを持ち、complex-key*辞書は複合キー（複雑な、任意の型を持つ）です。

XML辞書におけるUInt64キーは<id>タグで定義されます。

例：

複合的なcomplexキーを持つXML辞書は<key>タグで定義されます。

構成の例（キーが1つのString型の要素を持つ複合キー）：

メモリに辞書を保存する方法​

flat​

辞書は完全にメモリにフラット配列の形式で保存されます。辞書が使用するメモリはどれくらいですか？その量は、最大キーのサイズ（使用される空間に対して）に比例します。

辞書のキーはUInt64型であり、値はmax_array_size（デフォルトでは500,000）に制限されています。辞書を作成するときにより大きなキーが発見された場合、ClickHouseは例外を投げて辞書を作成しません。辞書のフラット配列の初期サイズはinitial_array_size設定（デフォルトは1024）によって制御されます。

すべてのタイプのソースがサポートされています。更新時には、データ（ファイルまたはテーブルから）は完全に読み取られます。

この方法は、利用可能なすべての辞書の保存方法の中で最も優れたパフォーマンスを提供します。

構成の例：

または

hashed​

辞書は、ハッシュテーブルの形式で完全にメモリに保存されます。辞書には任意の識別子を持つ任意の数の要素を含めることができます。実際のところ、キーの数は数千万を超えることができます。

辞書のキーはUInt64型です。

すべてのタイプのソースがサポートされています。更新時には、データ（ファイルまたはテーブルから）は完全に読み取られます。

構成の例：

または

構成の例：

または

sparse_hashed​

hashedに似ていますが、より多くのCPU使用量の代わりにメモリを節約します。

辞書のキーはUInt64型です。

構成の例：

または

このタイプの辞書でもshardsを使用することができ、sparse_hashedにとってはhashedよりも重要です。なぜなら、sparse_hashedは遅いためです。

complex_key_hashed​

このストレージタイプは、複合キーと共に使用されます。hashedに似ています。

構成の例：

または

complex_key_sparse_hashed​

このストレージタイプは、複合キーと共に使用されます。sparse_hashedに似ています。

構成の例：

または

hashed_array​

辞書は完全にメモリに保存されます。各属性は配列に保存され、キー属性はハッシュテーブルの形式で保存され、そこでは値が属性配列内のインデックスです。辞書には任意の数の要素を持たせることができ、実際のところキーの数は数千万を超えることができます。

辞書のキーはUInt64型です。

すべてのタイプのソースがサポートされています。更新時には、データ（ファイルまたはテーブルから）は完全に読み取られます。

構成の例：

または

complex_key_hashed_array​

このストレージタイプは、複合キーと共に使用されます。hashed_arrayに似ています。

構成の例：

または

range_hashed​

辞書は、範囲の順序付き配列とその対応する値のハッシュテーブルの形式でメモリに保存されます。

辞書のキーはUInt64型です。このストレージ方法は、hashedと同様に動作し、キーに加えて日付/時間（任意の数値型）の範囲も使用できます。

例：テーブルには、各広告主に対する割引が次の形式で含まれています：

日付範囲のサンプルを使用するには、structure内でrange_minとrange_max要素を定義します。これらの要素はnameとtypeの要素を含む必要があります（typeが指定されていない場合、デフォルトの型が使用されます - Date）。typeは任意の数値型（Date / DateTime / UInt64 / Int32 / その他）が使用できます。

注記

range_minおよびrange_maxの値はInt64型に収まる必要があります。

例：

または

これらの辞書を操作するには、範囲が選択されるdictGet関数に追加の引数を渡する必要があります：

クエリの例：

この関数は、指定されたidと、渡された日付を含む日付範囲に対して値を返します。

アルゴリズムの詳細：

  • idが見つからない場合や、idの範囲が見つからない場合、属性の型のデフォルト値を返します。
  • 重複する範囲があり、range_lookup_strategy=minの場合、最小のrange_minを持つ一致する範囲を返します。複数の範囲が見つかった場合、最小のrange_maxを持つ範囲を返し、さらに複数の範囲が見つかった（同じrange_minとrange_maxを持つ範囲が複数ある）場合、ランダムにその範囲の1つを返します。
  • 重複する範囲があり、range_lookup_strategy=maxの場合、最大のrange_minを持つ一致する範囲を返します。同様に、複数の範囲が見つかると、最大のrange_maxを持つ範囲を返し、さらに複数の範囲が見つかるとランダムに選ばれます。
  • range_maxがNULLの場合、範囲はオープンです。NULLは最大の可能な値として扱われます。range_minに対して、1970-01-01または0（-MAX_INT）をオープン値として使用できます。

構成の例：

または

重複範囲およびオープン範囲の構成例：

complex_key_range_hashed​

辞書は、範囲の順序付き配列とその対応する値を持つハッシュテーブルの形式でメモリに保存されます（range_hashedを参照）。このストレージタイプは、複合キーと共に使用されます。

構成の例：

cache​

辞書は、固定数のセルを持つキャッシュに保存されます。これらのセルには、頻繁に使用される要素が含まれます。

辞書のキーはUInt64型です。

辞書を検索する際、最初にキャッシュが検索されます。データの各ブロックに対して、キャッシュに見つからないか、古くなったすべてのキーが、SELECT attrs... FROM db.table WHERE id IN (k1, k2, ...)を使用してソースから要求されます。受信したデータは、次にキャッシュに書き込まれます。

キーが辞書に見つからない場合、キャッシュ更新タスクが作成され、更新キューに追加されます。更新キューのプロパティは、max_update_queue_size、update_queue_push_timeout_milliseconds、query_wait_timeout_milliseconds、max_threads_for_updates設定で制御できます。

キャッシュ辞書に対しては、キャッシュ内のデータの有効期限Lifetimeが設定できます。セルにデータがロードされてからlifetimeの時間が経過した場合、セルの値は使用されず、キーが期限切れになります。そのキーは、次回使用される必要があるときに再要求されます。この動作は、allow_read_expired_keys設定で構成できます。

これは、辞書を保存する方法の中で最も効果が低いです。キャッシュの速度は、正しい設定と使用シナリオに大きく依存します。キャッシュタイプの辞書は、ヒット率が十分に高い場合（推奨99％以上）のみ良好に機能します。ヒット率の平均をsystem.dictionariesテーブルで表示できます。

設定allow_read_expired_keysが1に設定されている場合、デフォルトは0です。その場合、辞書は非同期更新をサポートします。クライアントがキーを要求し、そのすべてがキャッシュに存在しますが、一部が期限切れの場合、辞書は期限切れのキーをクライアントに返し、それらを非同期にソースから要求します。

キャッシュのパフォーマンスを向上させるには、LIMITを使用したサブクエリを利用し、辞書を外部から呼び出す関数を使用してください。

すべてのタイプのソースがサポートされています。

設定の例：

または

十分に大きなキャッシュサイズを設定してください。セルの数を選択するために実験する必要があります：

  1. 一部の値を設定します。
  2. キャッシュが完全に満杯になるまでクエリを実行します。
  3. system.dictionariesテーブルを使用してメモリ消費量を評価します。
  4. 必要なメモリ消費量に達するまで、セルの数を増減させます。
注記

ClickHouseをソースとして使用しないでください。ランダムな読み取りを処理するクエリは遅くなります。

complex_key_cache​

このストレージタイプは、複合キーと共に使用されます。cacheに似ています。

ssd_cache​

cacheに似ていますが、データをSSDに保存し、インデックスをRAMに置きます。更新キューに関連するすべてのキャッシュ辞書の設定も、SSDキャッシュ辞書に適用できます。

辞書のキーはUInt64型です。

または

complex_key_ssd_cache​

このストレージタイプは、複合キーと共に使用されます。ssd_cacheに似ています。

direct​

辞書はメモリに保存されず、リクエストの処理中にソースに直接アクセスします。

辞書のキーはUInt64型です。

すべてのタイプのソース、ローカルファイルを除いてサポートされています。

構成の例：

または

complex_key_direct​

このストレージタイプは、複合キーと共に使用されます。directに似ています。

ip_trie​

このストレージタイプは、ネットワークプレフィックス（IPアドレス）をASNなどのメタデータにマッピングするために使用されます。

例

ClickHouseにIPプレフィックスとマッピングを含むテーブルがあるとしましょう：

このテーブルのためにip_trie辞書を定義しましょう。ip_trieレイアウトは複合キーを必要とします：

または

キーには、許可されたIPプレフィックスを含む文字列型の属性を1つだけ持つ必要があります。他の型は現在サポートされていません。

構文は次のとおりです：

この関数は、IPv4用のUInt32またはIPv6用のFixedString(16)を受け取ります。例えば：

他の型は現在サポートされていません。この関数は、指定されたIPアドレスに対応するプレフィックスの属性を返します。重複するプレフィックスがある場合、最も特定的なものが返されます。

データは完全にRAMに収まる必要があります。

辞書データの更新におけるLIFETIME​

ClickHouseは、LIFETIMEタグ（秒単位で定義）に基づいて辞書を定期的に更新します。 LIFETIMEは、完全にダウンロードされた辞書の更新間隔と、キャッシュされた辞書の無効化間隔を示します。

更新中は、辞書の旧バージョンをクエリすることができます。 辞書の更新（辞書を初めて使用するためにロードする場合を除く）は、クエリをブロックしません。 更新中にエラーが発生した場合、エラーはサーバーログに書き込まれ、クエリは旧バージョンの辞書を使用し続けることができます。 辞書の更新が成功した場合、旧バージョンの辞書は原子的に置き換えられます。

設定の例：

ヒント

If you are using a dictionary with ClickHouse Cloud please use the DDL query option to create your dictionaries, and create your dictionary as user default. Also, verify the list of supported dictionary sources in the Cloud Compatibility guide.

または

<lifetime>0</lifetime>（LIFETIME(0)）を設定すると、辞書の更新が防止されます。

更新のための時間間隔を設定でき、ClickHouseはこの範囲内で均等にランダムな時刻を選択します。 これは、多数のサーバーで同時に更新する際に辞書ソースへの負荷を分散するために必要です。

設定の例：

または

<min>0</min>および<max>0</max>の場合、ClickHouseはタイムアウトによる辞書の再ロードを行いません。 この場合、辞書構成ファイルが変更された場合やSYSTEM RELOAD DICTIONARYコマンドが実行された場合には、ClickHouseは辞書を早めに再ロードできます。

辞書を更新する際、ClickHouseサーバーはソースのタイプに応じて異なる論理を適用します：

  • テキストファイルの場合、変更時間を確認します。 変更時間が以前に記録された時間と異なる場合、辞書が更新されます。
  • 他のソースからの辞書は、デフォルトで毎回更新されます。

他のソース（ODBC、PostgreSQL、ClickHouseなど）では、実際に変更された場合にのみ辞書が更新されるようにクエリをセットアップできます。 そのためには、次の手順に従います：

  • 辞書テーブルには、ソースデータが更新されると常に変更されるフィールドを含める必要があります。
  • ソースの設定には、変更フィールドを取得するクエリを指定する必要があります。 ClickHouseサーバーはクエリ結果を行として解釈し、この行が以前の状態と比較して変更された場合に、辞書が更新されます。 スタイル設定のソース内の<invalidate_query>フィールドにクエリを指定してください。

設定の例：

または

Cache、ComplexKeyCache、SSDCache、およびSSDComplexKeyCache辞書では、同期および非同期の更新がサポートされています。

Flat、Hashed、ComplexKeyHashed辞書においても、前回の更新後に変更されたデータのみを要求することができます。 辞書ソース設定の一部としてupdate_fieldが指定されている場合は、データ要求に前回の更新時間の秒数が追加されます。 ソースのタイプ（Executable、HTTP、MySQL、PostgreSQL、ClickHouse、またはODBC）によって、リクエストする前にupdate_fieldに異なる論理が適用されます。

  • ソースがHTTPの場合、update_fieldはクエリパラメータとして追加され、最後の更新時間がパラメータ値として使用されます。
  • ソースがExecutableの場合、update_fieldは実行可能ファイルの引数として追加され、最後の更新時間が引数値として使用されます。
  • ソースがClickHouse、MySQL、PostgreSQL、ODBCの場合、追加のWHERE部分があり、ここでupdate_fieldが最後の更新時間と比較されます。
    • デフォルトでは、このWHERE条件はSQLクエリの最上位でチェックされます。 代わりに、{condition}キーワードを使用してクエリ内の他のWHERE句で条件をチェックできます。 例：

update_fieldオプションが設定されている場合、追加のオプションupdate_lagを設定することができます。 update_lagオプションの値は、更新されたデータをリクエストする前に前回の更新時間から減算されます。

設定の例：

または

辞書ソース​

ヒント

If you are using a dictionary with ClickHouse Cloud please use the DDL query option to create your dictionaries, and create your dictionary as user default. Also, verify the list of supported dictionary sources in the Cloud Compatibility guide.

辞書は、さまざまなソースからClickHouseに接続できます。

辞書がxmlファイルを使用して構成されている場合、構成は次のようになります：

DDLクエリの場合、上記の構成は次のようになります：

ソースはsourceセクションで構成されています。

ローカルファイル、実行可能ファイル、HTTP(s)、ClickHouseのソースタイプでは、オプション設定が利用可能です：

または

ソースタイプ（source_type）：

ローカルファイル​

設定の例：

または

設定フィールド：

  • path – ファイルの絶対パス。
  • format – ファイル形式。 フォーマットで説明されているすべての形式がサポートされています。

FILEソースを使用して辞書がDDLコマンド（CREATE DICTIONARY ...）経由で作成される場合、ソースファイルはuser_filesディレクトリに配置する必要があります。これは、DBユーザーがClickHouseノード上の任意のファイルにアクセスできないようにするためです。

関連情報

実行可能ファイル​

実行可能ファイルとの作業は、辞書がメモリにどのように格納されているかに依存します。 辞書がcacheとcomplex_key_cacheを使用してストレージされている場合、ClickHouseは実行可能ファイルのSTDINにリクエストを送信して必要なキーを要求します。そうでない場合、ClickHouseは実行可能ファイルを起動し、その出力を辞書データとして扱います。

設定の例：

設定フィールド：

  • command — 実行可能ファイルへの絶対パス、またはファイル名（コマンドのディレクトリがPATHに含まれている場合）。
  • format — ファイル形式。 フォーマットで説明されているすべての形式がサポートされています。
  • command_termination_timeout — 実行可能なスクリプトは、メインの読み書きループを含む必要があります。 辞書が削除された後、パイプが閉じられ、実行可能ファイルはcommand_termination_timeout秒以内にシャットダウンする必要があります。そうしないと、ClickHouseは子プロセスにSIGTERM信号を送信します。 command_termination_timeoutは秒単位で指定されます。 デフォルト値は10です。 オプションのパラメータです。
  • command_read_timeout - コマンドの標準出力からデータを読み取るためのタイムアウト（ミリ秒）。 デフォルト値10000。 オプションのパラメータです。
  • command_write_timeout - コマンドの標準入力にデータを書き込むためのタイムアウト（ミリ秒）。 デフォルト値10000。 オプションのパラメータです。
  • implicit_key — 実行可能ソースファイルは値のみを返すことができ、要求されるキーとの対応は暗黙的に決定されます。結果の行の順序によって決まります。 デフォルト値はfalseです。
  • execute_direct - execute_direct = 1の場合、commandはuser_scripts_pathで指定されたuser_scriptsフォルダ内で検索されます。 追加のスクリプト引数を空白区切りで指定できます。例：script_name arg1 arg2。 execute_direct = 0の場合、commandはbin/sh -cの引数として渡されます。 デフォルト値は0です。 オプションのパラメータです。
  • send_chunk_header - データのチャンクを処理するために送信する前に行数を送信するかどうかを制御します。 オプションです。 デフォルト値はfalseです。

この辞書ソースはXML構成経由でのみ構成できます。 DDLを介して実行可能ソースを持つ辞書を作成することは無効になっており、そうでない場合、DBユーザーはClickHouseノード上で任意のバイナリを実行できるようになります。

実行可能プール​

実行可能プールは、プロセスのプールからデータを読み込むことを可能にします。 このソースは、ソースからすべてのデータを読み込む必要がある辞書レイアウトでは機能しません。 実行可能プールは、辞書がcache、complex_key_cache、ssd_cache、complex_key_ssd_cache、direct、またはcomplex_key_directレイアウトを使用して保存されている場合に機能します。

実行可能プールは、指定されたコマンドでプロセスのプールを生成し、終了するまでそれらを実行し続けます。 プログラムは、STDINからデータを読み取る必要があります。 ClickHouseはデータブロックを処理した後、STDINを閉じずに必要に応じて別のデータチャンクをパイプします。 実行可能なスクリプトは、このデータ処理方法に対応する必要があり、STDINをポーリングしてデータを早期にSTDOUTにフラッシュする必要があります。

設定の例：

設定フィールド：

  • command — 実行可能ファイルへの絶対パス、またはファイル名（プログラムのディレクトリがPATHに記載されている場合）。
  • format — ファイル形式。 フォーマットで説明されているすべての形式がサポートされています。
  • pool_size — プールのサイズ。 pool_sizeとして0を指定する場合、プールサイズに制限はありません。 デフォルト値は16です。
  • command_termination_timeout — 実行可能なスクリプトは、メインの読み書きループを含む必要があります。 辞書が削除された後、パイプが閉じられ、実行可能ファイルはcommand_termination_timeout秒以内にシャットダウンする必要があります。そうしないと、ClickHouseはSIGTERM信号を子プロセスに送信します。 秒で指定します。 デフォルト値は10です。 オプションのパラメータです。
  • max_command_execution_time — データブロックを処理するための実行可能スクリプトコマンドの最大実行時間。 秒で指定します。 デフォルト値は10です。 オプションのパラメータです。
  • command_read_timeout - コマンドの標準出力からデータを読み取るためのタイムアウト（ミリ秒）。 デフォルト値10000。 オプションのパラメータです。
  • command_write_timeout - コマンドの標準入力にデータを書き込むためのタイムアウト（ミリ秒）。 デフォルト値10000。 オプションのパラメータです。
  • implicit_key — 実行可能ソースファイルは値のみを返すことができ、要求されるキーとの対応は暗黙的に決定されます。結果の行の順序によって決まります。 デフォルト値はfalseです。 オプションのパラメータです。
  • execute_direct - execute_direct = 1の場合、 commandはuser_scripts_pathのuser_scriptsフォルダ内で検索されます。 追加のスクリプト引数を空白区切りで指定できます。 例：script_name arg1 arg2。 execute_direct = 0の場合，commandはbin/sh -cの引数として渡されます。 デフォルト値は1です。 オプションパラメータです。
  • send_chunk_header - データ処理のためにチャンクを送信する前に行数を送信するかどうかを制御します。 オプション。 デフォルト値はfalseです。

この辞書ソースはXML構成経由でのみ構成できます。 実行可能ソースを持つ辞書をDDL経由で作成することは無効にされており、そうでない場合、DBユーザーはClickHouseノード上で任意のバイナリを実行できるようになります。

HTTP(S)​

HTTP(S)サーバーとの作業は、辞書がメモリにどのように格納されているかに依存します。 辞書がcacheおよびcomplex_key_cacheを使用してストレージされている場合、ClickHouseはPOSTメソッドを介してリクエストを送信して必要なキーを要求します。

設定の例：

または

ClickHouseがHTTPSリソースにアクセスするためには、サーバー構成でopenSSLを設定する必要があります。

設定フィールド：

  • url – ソースURL。
  • format – ファイル形式。 フォーマットで説明されているすべての形式がサポートされています。
  • credentials – 基本的なHTTP認証。 オプションのパラメータです。
  • user – 認証に必要なユーザー名。
  • password – 認証に必要なパスワード。
  • headers – HTTPリクエストに使用されるすべてのカスタムHTTPヘッダーエントリ。 オプションのパラメータです。
  • header – 単一のHTTPヘッダーエントリ。
  • name – リクエストで送信されるヘッダーに使用される識別子名。
  • value – 特定の識別子名に設定される値。

DDLコマンド（CREATE DICTIONARY ...）を使用して辞書を作成すると、HTTP辞書用のリモートホストは構成からremote_url_allow_hostsセクションの内容に対して確認され、データベースユーザーが任意のHTTPサーバーにアクセスできないようにします。

DBMS​

ODBC​

ODBCドライバーを持つ任意のデータベースに接続するためのこの方法を使用できます。

設定の例：

または

設定フィールド：

  • db – データベース名。 <connection_string>のパラメータでデータベース名が設定されている場合は省略します。
  • table – テーブル名およびスキーマ名（存在する場合）。
  • connection_string – 接続文字列。
  • invalidate_query – 辞書の状態を確認するためのクエリ。 オプションのパラメータです。 辞書データの更新に関するセクション辞書データの更新におけるLIFETIMEで詳しく説明しています。
  • background_reconnect – 接続が失敗した場合にバックグラウンドでレプリカに再接続します。 オプションのパラメータです。
  • query – カスタムクエリ。 オプションのパラメータです。
注記

tableとqueryフィールドは一緒に使用できません。 どちらか一方のtableまたはqueryフィールドを宣言する必要があります。

ClickHouseはODBCドライバーから引用シンボルを受け取り、ドライバーのクエリで設定をすべて引用します。 そのため、テーブル名はデータベース内のテーブル名のケースに従って正しく設定する必要があります。

Oracleを使用する際にエンコーディングに関する問題がある場合は、該当するFAQ項目を参照してください。

ODBC辞書機能の既知の脆弱性​
注記

ODBCドライバーを介してデータベースに接続する際、接続パラメータServernameが置き換えられる可能性があります。 この場合、odbc.iniのUSERNAMEおよびPASSWORDの値がリモートサーバーに送信され、漏洩する可能性があります。

安全でない使用の例

PostgreSQL用にunixODBCを設定してみましょう。 /etc/odbc.iniの内容：

次に、次のようなクエリを実行すると：

ODBCドライバーはodbc.iniのUSERNAMEおよびPASSWORDの値をsome-server.comに送信します。

PostgreSQLに接続する例​

Ubuntu OS。

unixODBCとPostgreSQL用ODBCドライバーをインストールする：

/etc/odbc.ini（またはClickHouseを実行するユーザーでログインしている場合の~/.odbc.ini）を設定する：

ClickHouseでの辞書構成：

または

ドライバーのライブラリへのフルパスを指定するためにodbc.iniを編集する必要があるかもしれません。 DRIVER=/usr/local/lib/psqlodbcw.so。

MS SQL Serverに接続する例​

Ubuntu OS。

MS SQLに接続するためのODBCドライバーをインストール：

ドライバーの設定：

備考：

  • 特定のSQL Serverバージョンがサポートする最も古いTDSバージョンを特定するには、製品文書を参照するか、MS-TDS製品の動作を見る必要があります。

ClickHouseでの辞書の設定：

または

MySQL​

設定の例：

または

設定フィールド：

  • port – MySQLサーバーのポート。 これはすべてのレプリカに対して指定することも、各レプリカごとに個別に指定することもできます（<replica>内）。

  • user – MySQLユーザーの名前。 これはすべてのレプリカに対して指定することも、各レプリカごとに個別に指定することもできます（<replica>内）。

  • password – MySQLユーザーのパスワード。 これはすべてのレプリカに対して指定することも、各レプリカごとに個別に指定することもできます（<replica>内）。

  • replica – レプリカ設定セクション。 複数のセクションを作成できます。

    • replica/host – MySQLホスト。
    • replica/priority – レプリカの優先度。 接続を試みるとき、ClickHouseは優先度の順にレプリカを遍歴します。数が小さいほど優先度が高くなります。

  • db – データベース名。

  • table – テーブル名。

  • where – 選択基準。 条件の構文はMySQLのWHERE句と同じで、例えばid > 10 AND id < 20のようになります。 オプションのパラメータです。

  • invalidate_query – 辞書の状態を確認するためのクエリ。 オプションのパラメータです。 辞書データの更新に関するセクション辞書データの更新におけるLIFETIMEで詳しく説明しています。

  • fail_on_connection_loss – 接続が失われたときのサーバーの動作を制御する設定パラメータ。 trueの場合、クライアントとサーバーの接続が失われるとすぐに例外がスローされます。 falseの場合、ClickHouseサーバーは、例外をスローする前にクエリを3回再実行します。 リトライにより応答時間が増加することに注意してください。 デフォルト値: false。

  • query – カスタムクエリ。 オプションのパラメータです。

注記

tableまたはwhereフィールドはqueryフィールドと一緒には使用できません。 どちらか一方のtableまたはqueryフィールドを宣言する必要があります。

注記

明示的なパラメータsecureはありません。 SSL接続を確立するときはセキュリティが必須です。

MySQLにはソケットを介してローカルホストへの接続ができます。 これを行うには、hostとsocketを設定します。

設定の例：

または

ClickHouse​

設定の例：

または

設定フィールド：

  • host – ClickHouseホスト。 ローカルホストの場合、クエリはネットワークアクティビティなしで処理されます。 障害耐性を向上させるために、分散テーブルを作成し、次の構成に追加することができます。
  • port – ClickHouseサーバーのポート。
  • user – ClickHouseユーザーの名前。
  • password – ClickHouseユーザーのパスワード。
  • db – データベース名。
  • table – テーブル名。
  • where – 選択基準。 省略することができます。
  • invalidate_query – 辞書の状態を確認するためのクエリ。 オプションのパラメータです。 辞書データの更新に関するセクション辞書データの更新におけるLIFETIMEで詳しく説明しています。
  • secure - 接続にSSLを使用します。
  • query – カスタムクエリ。 オプションのパラメータです。
注記

tableまたはwhereフィールドはqueryフィールドと一緒には使用できません。 どちらか一方のtableまたはqueryフィールドを宣言する必要があります。

MongoDB​

設定の例：

または

または

設定フィールド：

  • host – MongoDBホスト。
  • port – MongoDBサーバーのポート。
  • user – MongoDBユーザーの名前。
  • password – MongoDBユーザーのパスワード。
  • db – データベース名。
  • collection – コレクション名。
  • options - MongoDB接続文字列オプション（オプションのパラメータ）。

または

設定フィールド：

  • uri - 接続を確立するためのURI。
  • collection – コレクション名。

エンジンに関する詳細

Redis​

設定の例:

または

設定項目:

  • host – Redisホスト。
  • port – Redisサーバのポート。
  • storage_type – キーとの作業に使用する内部Redisストレージの構造。simple はシンプルなソースおよびハッシュ化された単一キーソース用、hash_map は2つのキーを持つハッシュ化されたソース用です。範囲ソースおよび複雑なキーを持つキャッシュソースはサポートされていません。省略可能で、デフォルト値は simple です。
  • db_index – Redis論理データベースの特定の数値インデックス。省略可能で、デフォルト値は0です。

Cassandra​

設定の例:

設定項目:

  • host – Cassandraホストまたはカンマ区切りのホストリスト。
  • port – Cassandraサーバのポート。指定しない場合、デフォルトのポート 9042 が使用されます。
  • user – Cassandraユーザ名。
  • password – Cassandraユーザのパスワード。
  • keyspace – キースペース（データベース）の名前。
  • column_family – カラムファミリ（テーブル）の名前。
  • allow_filtering – クラスタキー列に対する潜在的に高コストの条件を許可するフラグ。デフォルト値は1です。
  • partition_key_prefix – Cassandraテーブルの主キーにおけるパーティションキー列の数。構成キー辞書に必要です。辞書定義におけるキー列の順序はCassandraと同じでなければなりません。デフォルト値は1（最初のキー列はパーティションキーで、他のキー列はクラスタキー）。
  • consistency – 一貫性レベル。可能な値: One, Two, Three, All, EachQuorum, Quorum, LocalQuorum, LocalOne, Serial, LocalSerial。デフォルト値は One。
  • where – オプションの選択基準。
  • max_threads – 複数のパーティションからデータを読み込むために使用する最大スレッド数。
  • query – カスタムクエリ。オプションのパラメータ。
注記

column_family または where フィールドは、query フィールドと一緒に使用できません。また、column_family または query フィールドのいずれかを宣言する必要があります。

PostgreSQL​

設定の例:

または

設定項目:

  • host – PostgreSQLサーバのホスト。すべてのレプリカに対して指定することも、各レプリカに個別に指定することもできます（<replica> 内）。
  • port – PostgreSQLサーバのポート。すべてのレプリカに対して指定することも、各レプリカに個別に指定することもできます（<replica> 内）。
  • user – PostgreSQLユーザ名。すべてのレプリカに対して指定することも、各レプリカに個別に指定することもできます（<replica> 内）。
  • password – PostgreSQLユーザのパスワード。すべてのレプリカに対して指定することも、各レプリカに個別に指定することもできます（<replica> 内）。
  • replica – レプリカ設定のセクション。複数のセクションが可能です:
    • replica/host – PostgreSQLホスト。
    • replica/port – PostgreSQLポート。
    • replica/priority – レプリカの優先度。接続を試みる際、ClickHouseは優先度の順にレプリカを辿ります。数が小さいほど優先度が高くなります。
  • db – データベースの名前。
  • table – テーブルの名前。
  • where – 選択基準。条件の構文はPostgreSQLのWHERE句と同じです。例えば、id > 10 AND id < 20。オプションのパラメータです。
  • invalidate_query – 辞書の状態をチェックするためのクエリ。オプションのパラメータです。詳しくは有効期限を使用した辞書データの更新のセクションを参照してください。
  • background_reconnect – 接続が失敗した場合にバックグラウンドでレプリカに再接続します。オプションのパラメータです。
  • query – カスタムクエリ。オプションのパラメータです。
注記

table または where フィールドは、query フィールドと一緒に使用できません。また、table または query フィールドのいずれかを宣言する必要があります。

Null​

ダミー（空）辞書を作成するために使用できる特別なソース。このような辞書はテストや、分散テーブルでのデータとクエリノードが分離されているセットアップで役立ちます。

Dictionary Key and Fields​

ヒント

If you are using a dictionary with ClickHouse Cloud please use the DDL query option to create your dictionaries, and create your dictionary as user default. Also, verify the list of supported dictionary sources in the Cloud Compatibility guide.

structure句は、辞書キーおよびクエリで使用可能なフィールドを記述します。

XML説明:

属性は以下の要素で説明されます:

  • <id> — キーカラム
  • <attribute> — データカラム: 複数の属性がある場合があります。

DDLクエリ:

属性はクエリ本体で説明されます:

  • PRIMARY KEY — キーカラム
  • AttrName AttrType — データカラム。複数の属性がある場合があります。

Key​

ClickHouseは以下のタイプのキーをサポートしています:

  • 数値キー。UInt64。<id>タグで定義されるか、PRIMARY KEYキーワードを使用して定義されます。
  • 複合キー。異なるタイプの値のセット。<key>タグまたはPRIMARY KEYキーワードで定義されます。

XML構造には<id>または<key>のいずれかを含むことができます。DDLクエリには単一のPRIMARY KEYを含める必要があります。

注記

キーを属性として記述してはいけません。

Numeric Key​

タイプ: UInt64。

構成の例:

構成項目:

  • name – キーを持つカラムの名前。

DDLクエリ用:

  • PRIMARY KEY – キーを持つカラムの名前。

Composite Key​

キーは任意のタイプのフィールドのtupleであることができます。この場合、レイアウトはcomplex_key_hashedまたはcomplex_key_cacheでなければなりません。

ヒント

複合キーは単一の要素から構成されることができます。これにより、文字列をキーとして使用することが可能になります。

キー構造は<key>要素で設定されます。キー項目は辞書の属性と同じ形式で指定されます。例:

または

dictGet*関数へのクエリでは、タプルをキーとして渡します。例: dictGetString('dict_name', 'attr_name', tuple('string for field1', num_for_field2))。

Attributes​

構成の例:

または

構成項目:

タグ説明必須
nameカラム名。はい
typeClickHouseデータ型: UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64, Float32, Float64, UUID, Decimal32, Decimal64, Decimal128, Decimal256,Date, Date32, DateTime, DateTime64, String, Array。
ClickHouseは辞書から指定されたデータ型への値のキャストを試みます。例えば、MySQLの場合、フィールドはMySQLソーステーブルでTEXT、VARCHAR、またはBLOBであるかもしれませんが、ClickHouseではStringとしてアップロードできます。
Nullableは、Flat、Hashed、ComplexKeyHashed、Direct、ComplexKeyDirect、RangeHashed、Polygon、Cache、ComplexKeyCache、SSDCache、SSDComplexKeyCache辞書で現在サポートされています。IPTrie辞書ではNullableタイプはサポートされていません。		はい
null_value存在しない要素のデフォルト値。
例では、空文字列です。NULL値はNullableタイプにのみ使用できます（前述のタイプ説明を参照）。		はい
expressionClickHouseが値に対して実行する式。
式はリモートSQLデータベース内のカラム名である場合があります。これにより、リモートカラムのエイリアスを作成することができます。

デフォルト値: 式なし。		いいえ
hierarchicaltrueであれば、その属性は現在のキーの親キーの値を含みます。階層辞書を参照してください。

デフォルト値: false。		いいえ
injectiveid -> attributeの画像が単射であるかどうかを示すフラグ。
trueであれば、ClickHouseは辞書に対する要求をGROUP BY句の後に自動的に配置することができます。通常、これによりそのような要求の数が大幅に減少します。

デフォルト値: false。		いいえ
is_object_idクエリがObjectIDによってMongoDBドキュメントに対して実行されるかどうかを示すフラグ。

デフォルト値: false。		いいえ

Hierarchical Dictionaries​

ClickHouseは数値キーを持つ階層辞書をサポートしています。

次の階層構造を見てください:

この階層は次の辞書テーブルとして表現できます。

region_idparent_regionregion_name
10ロシア
21モスクワ
32センター
40イギリス
54ロンドン

このテーブルには、要素の最も近い親のキーを含むカラムparent_regionがあります。

ClickHouseは外部辞書属性に対して階層的な性質をサポートしています。この属性を使用すると、前述のように階層辞書を構成することができます。

dictGetHierarchy関数を使用すると、要素の親チェーンを取得することができます。

私たちの例では、辞書の構造は次のようになる可能性があります:

ポリゴン辞書​

ポリゴン辞書は、指定された点を含むポリゴンを効率的に検索することを可能にします。
例えば、地理的座標によって都市の領域を定義することです。

ポリゴン辞書の設定の例：

ヒント

If you are using a dictionary with ClickHouse Cloud please use the DDL query option to create your dictionaries, and create your dictionary as user default. Also, verify the list of supported dictionary sources in the Cloud Compatibility guide.

対応する DDL-query:

ポリゴン辞書を設定する際、キーは二つのタイプのいずれかでなければなりません：

  • 単純ポリゴン。これは点の配列です。
  • MultiPolygon。これはポリゴンの配列です。各ポリゴンは点の二次元配列です。この配列の最初の要素がポリゴンの外境、以降の要素はその中から除外する領域を指定します。

点は配列またはその座標のタプルとして指定できます。現在の実装では、二次元の点のみがサポートされています。

ユーザーは、ClickHouseがサポートするすべてのフォーマットで自分のデータをアップロードできます。

利用可能な インメモリストレージ のタイプは3つあります：

  • POLYGON_SIMPLE。これは簡単な実装で、各クエリごとにすべてのポリゴンを線形に走査し、それぞれでメンバーシップをチェックしますが、追加のインデックスを使用しません。

  • POLYGON_INDEX_EACH。各ポリゴンに対して個別のインデックスが構築され、ほとんどの場合、どのポリゴンに属するかを迅速にチェックできます（地理的地域に最適化されています）。また、考慮中の地域にグリッドが重ねられ、考慮すべきポリゴンの数が大幅に絞り込まれます。このグリッドは、セルを16等分する形で再帰的に作成され、二つのパラメータで設定されます。再帰の深さがMAX_DEPTHに達するか、セルがMIN_INTERSECTIONSポリゴンを越えない場合に分割は停止します。クエリに応じて対応するセルがあり、そこに格納されたポリゴンのインデックスに交互にアクセスします。

  • POLYGON_INDEX_CELL。この配置でも、上記のようなグリッドが作成されます。同じオプションが利用可能です。各シートセルに対して、その中に入るすべてのポリゴンのピースに対してインデックスが構築され、迅速に応答が可能です。

  • POLYGON。POLYGON_INDEX_CELLの同義語です。

辞書クエリは、辞書を操作するための標準 関数 を使用して実行されます。重要な違いは、ここでのキーは、それらを含むポリゴンを見つけたい点です。

例

上記で定義された辞書を使用する例：

points テーブルの各点に対して最後のコマンドを実行した結果、最小の領域ポリゴンが見つかり、要求された属性が出力されます。

例

ポリゴン辞書からのカラムは、SELECTクエリを通じて読み取ることができ、辞書設定または対応するDDLクエリで store_polygon_key_column = 1 を有効にするだけで済みます。

クエリ：

結果：

正規表現ツリー辞書​

正規表現ツリー辞書は、キーと属性のマッピングを正規表現のツリーを使用して表現する特別なタイプの辞書です。例として、ユーザーエージェント 文字列の解析があり、これは正規表現ツリー辞書を使用して優雅に表現できます。

ClickHouseオープンソースでの正規表現ツリー辞書の使用​

正規表現ツリー辞書は、正規表現ツリーを含むYAMLファイルへのパスを提供するYAMLRegExpTreeソースを使用してClickHouseオープンソースで定義されます。

辞書ソース YAMLRegExpTree は、regexpツリーの構造を表します。例えば：

この設定は、正規表現ツリーのノードのリストで構成されます。各ノードは次の構造を持ちます：

  • regexp: ノードの正規表現。
  • attributes: ユーザー定義辞書属性のリスト。この例では二つの属性があり、name と version です。最初のノードが両方の属性を定義し、二番目のノードは属性nameのみを定義します。属性versionは二番目のノードの子ノードによって提供されます。
    • 属性の値は、マッチした正規表現のキャプチャグループを参照するバックリファレンスを含む場合があります。この例では、最初のノードの属性versionの値は、正規表現内のキャプチャグループ(\d+[\.\d]*)のバックリファレンス\1で構成されています。バックリファレンス番号は1から9までで、$1または\1（番号1の場合）として書かれます。バックリファレンスは、クエリ実行中にマッチしたキャプチャグループによって置き換えられます。
  • child nodes: 正規表現ツリーノードの子のリストで、それぞれ独自の属性と（潜在的に）子ノードを持っています。文字列のマッチングは深さ優先で進行します。文字列が正規表現ノードにマッチする場合、辞書はノードの子ノードにもマッチするかどうかを確認します。もしマッチすれば、最も深くマッチしたノードの属性が割り当てられます。子ノードの属性は、親ノードの同名の属性を上書きします。YAMLファイル内の子ノードの名前は任意で、例えば上の例でのversionsなどです。

正規表現ツリー辞書には、dictGet、dictGetOrDefault、および dictGetAll 関数でのみアクセスできます。

例：

結果：

この場合、最初に正規表現\d+/tclwebkit(?:\d+[\.\d]*)が最上層の第二ノードでマッチします。辞書はその後、子ノードを調べ続け、文字列が3[12]/tclwebkitにもマッチすることを発見します。その結果、属性nameの値はAndroid（最初のレイヤーで定義された）であり、属性versionの値は12（子ノードで定義された）となります。

強力なYAML設定ファイルを使用することで、正規表現ツリー辞書をユーザーエージェント文字列パーサーとして利用できます。私たちは uap-core をサポートしており、機能テスト 02504_regexp_dictionary_ua_parser での使用方法を示しています。

属性値の収集​

時には、葉ノードの値だけでなく、マッチした複数の正規表現からの値を返すことが有用です。このような場合、特化した dictGetAll 関数を使用できます。ノードに属性値の型Tがある場合、dictGetAll はゼロまたはそれ以上の値を含む Array(T) を返します。

デフォルトでは、キーごとに返されるマッチの数に制限はありません。制限は、dictGetAll にオプションの第四引数として渡すことができます。配列は トポロジカル順序 でポピュレートされ、つまり子ノードが親ノードの前に来て、兄弟ノードはソースの順序に従います。

例：

結果：

マッチモード​

パターンマッチングの動作は、特定の辞書設定によって変更できます：

  • regexp_dict_flag_case_insensitive: 大文字と小文字を区別しないマッチングを使用します（デフォルトはfalse）。個々の表現で(?i)および(?-i)で上書きできます。
  • regexp_dict_flag_dotall: .が改行文字と一致することを許可します（デフォルトはfalse）。

ClickHouse Cloudでの正規表現ツリー辞書の使用​

上記で使用した YAMLRegExpTree ソースは ClickHouseオープンソースで動作しますが、ClickHouse Cloudでは動作しません。ClickHouse Cloudで正規表現ツリー辞書を使用するには、初めにClickHouseオープンソースでYAMLファイルから正規表現ツリー辞書を作成し、次に dictionary テーブル関数および INTO OUTFILE 句を使用してこの辞書をCSVファイルにダンプします。

CSVファイルの内容は次の通りです：

ダンプされたファイルのスキーマは次の通りです：

  • id UInt64: RegexpTreeノードのID。
  • parent_id UInt64: ノードの親のID。
  • regexp String: 正規表現の文字列。
  • keys Array(String): ユーザー定義の属性の名前。
  • values Array(String): ユーザー定義の属性の値。

ClickHouse Cloudに辞書を作成するには、まず次のテーブル構造で regexp_dictionary_source_table を作成します：

次に、ローカルCSVを以下のように更新します。

詳細については、ローカルファイルを挿入 を参照できます。ソーステーブルを初期化したら、テーブルソースからRegexpTreeを作成できます：

組み込み辞書​

Not supported in ClickHouse Cloud
注記

This page is not applicable to ClickHouse Cloud. The feature documented here is not available in ClickHouse Cloud services. See the ClickHouse Cloud Compatibility guide for more information.

ClickHouseには、ジオベースで作業するための組み込み機能があります。

これにより、次のことが可能になります：

  • 地域のIDを使用して、希望の言語でその名前を取得する。
  • 地域のIDを使用して、都市、地域、連邦地区、国、または大陸のIDを取得する。
  • 地域が別の地域の一部であるかどうかを確認する。
  • 親地域の連鎖を取得する。

すべての関数は「トランスローカリティ」をサポートしており、地域の所有権に関する異なる視点を同時に使用する能力を持っています。詳細については、「ウェブ分析辞書を操作するための関数」のセクションを参照してください。

内部辞書はデフォルトパッケージで無効になっています。これらを有効にするには、サーバー設定ファイルで path_to_regions_hierarchy_file および path_to_regions_names_files パラメータのコメントを外します。

ジオベースはテキストファイルからロードされます。

regions_hierarchy*.txt ファイルを path_to_regions_hierarchy_file ディレクトリに配置します。この構成パラメータには regions_hierarchy.txt ファイル（デフォルトの地域階層）へのパスが含まれている必要があり、他のファイル（regions_hierarchy_ua.txt）も同じディレクトリに配置する必要があります。

regions_names_*.txt ファイルを path_to_regions_names_files ディレクトリに配置します。

これらのファイルは自分で作成することもできます。ファイルフォーマットは次の通りです：

regions_hierarchy*.txt: タブ区切り（ヘッダーなし）、カラム：

  • 地域ID (UInt32)
  • 親地域ID (UInt32)
  • 地域タイプ (UInt8): 1 - 大陸、3 - 国、4 - 連邦地区、5 - 地域、6 - 市; 他のタイプには値がない
  • 人口 (UInt32) — 任意のカラム

regions_names_*.txt: タブ区切り（ヘッダーなし）、カラム：

  • 地域ID (UInt32)
  • 地域名 (String) — タブや改行を含めることはできない（エスケープされたものも含まれない）。

RAMに保存するためにフラットな配列が使用されます。このため、IDは百万を超えないようにしてください。

辞書はサーバーを再起動せずに更新できます。ただし、使用可能な辞書のセットは更新されません。更新時にはファイルの変更時刻が確認されます。ファイルが変更されている場合、辞書が更新されます。変更を確認する間隔は builtin_dictionaries_reload_interval パラメータで設定できます。最初の使用時のロードを除き、辞書の更新はクエリをブロックしません。更新中は、クエリは古いバージョンの辞書を使用します。更新中にエラーが発生した場合、エラーはサーバーログに書き込まれ、クエリは古いバージョンの辞書を使用し続けます。

私たちは、ジオベースで辞書を定期的に更新することを推奨します。更新中は新しいファイルを生成し、それらを別の場所に書き込みます。すべての準備が整ったら、サーバーが使用するファイルに名前を変更します。

OS識別子や検索エンジンを操作するための関数もありますが、それらは使用しないことを推奨します。