辞書
辞書は、さまざまなタイプの参照リストに便利なマッピング (key -> attributes) です。
ClickHouse は、クエリで使用できる辞書の操作のための特別な関数をサポートしています。参照テーブルとの JOIN よりも、関数を用いた辞書の使用が簡単で効率的です。
ClickHouse では以下をサポートしています。
ClickHouse の辞書を始めたばかりの方は、このトピックに関するチュートリアルがあります。こちらをご覧ください。
さまざまなデータソースから独自の辞書を追加できます。辞書のソースは、ClickHouse テーブル、ローカルのテキストまたは実行ファイル、HTTP(S) リソース、または別の DBMS である可能性があります。詳細については、「辞書のソース」をご覧ください。
ClickHouse は次のことを行います。
- 辞書を RAM に完全または部分的に保存します。
- 辞書を定期的に更新し、欠落している値を動的にロードします。言い換えれば、辞書は動的にロードされる可能性があります。
- xml ファイルまたはDDL クエリを使用して辞書を作成できます。
辞書の設定は、1つまたは複数の xml ファイルに配置できます。設定へのパスは、dictionaries_config パラメータで指定されます。
辞書は、サーバーの起動時または初回の使用時にロードされます。これは、dictionaries_lazy_load 設定に依存します。
dictionaries システムテーブルには、サーバーで設定された辞書に関する情報が含まれています。各辞書に対して以下が見つかります。
- 辞書のステータス。
- 設定パラメータ。
- 辞書に割り当てられた RAM の量や、辞書が正常にロードされてからのクエリ数などのメトリクス。
ClickHouse Cloudで辞書を使用している場合は、DDLクエリオプションを使用して辞書を作成し、ユーザー default として辞書を作成してください。また、Cloud Compatibility guide に記載されているサポートされている辞書ソースのリストを確認してください。
DDL クエリで辞書を作成する
辞書はDDL クエリを使用して作成でき、これは推奨される方法です。なぜなら、DDL で作成された辞書は、
- サーバー設定ファイルに追加レコードが追加されない
- 辞書はテーブルやビューと同様にファーストクラスのエンティティとして扱える
- データを辞書テーブル関数ではなく、親しみのある SELECT を用いて直接読み取ることができる
- 辞書を容易にリネームできるからです。
設定ファイルで辞書を作成する
設定ファイルを使用して辞書を作成することは ClickHouse Cloud に該当しません。上記のように DDL を使用し、ユーザー default として辞書を作成してください。
辞書の設定ファイルは次の形式です。
同じファイル内で任意の数の辞書を構成できます。
小規模な辞書の値を変換するには、SELECT クエリで説明することができます (見てみる transform 関数)。この機能は辞書には関連しません。
辞書の設定
ClickHouse Cloudで辞書を使用している場合は、DDLクエリオプションを使用して辞書を作成し、ユーザー default として辞書を作成してください。また、Cloud Compatibility guide に記載されているサポートされている辞書ソースのリストを確認してください。
辞書が xml ファイルを使用して設定された場合、辞書の設定は以下の構造を持っています。
対応するDDLクエリは以下の構造を持っています。
メモリに辞書を保存する
辞書をメモリに保存するためのさまざまな方法があります。
最適な処理速度を提供するflat、hashed、およびcomplex_key_hashedを推奨します。
キャッシングは、パフォーマンスが悪くなる可能性や最適なパラメータの選択の難しさから推奨されません。詳細はcacheセクションで説明しています。
辞書のパフォーマンスを向上させる方法はいくつかあります。
GROUP BYの後に辞書を操作するための関数を呼び出します。- 抽出する属性を単射としてマークします。異なるキーに対して異なる属性値が対応する場合、属性は単射と呼ばれます。したがって、
GROUP BYがキーによって属性値を取得する関数を使用する際、この関数は自動的にGROUP BYから除外されます。
ClickHouse は辞書のエラーに対して例外を生成します。エラーの例：
- アクセスされている辞書をロードできませんでした。
cached辞書のクエリエラー。
辞書とそのステータスのリストは、system.dictionaries テーブルで確認できます。
ClickHouse Cloudで辞書を使用している場合は、DDLクエリオプションを使用して辞書を作成し、ユーザー default として辞書を作成してください。また、Cloud Compatibility guide に記載されているサポートされている辞書ソースのリストを確認してください。
設定は次のようになります。
対応するDDLクエリ:
complex-key*という単語がレイアウトに含まれていない辞書は、UInt64型のキーを持ち、complex-key* 辞書は合成キー（複雑で、任意の型を持つ）を持っています。
UInt64 型のキーは、XML 辞書で <id> タグで定義されます。
設定例（列 key_column は UInt64 型）:
合成 complex キーの XML 辞書は <key> タグで定義されます。
合成キーの設定例（キーが String 型の要素を1つ持つ場合）:
メモリに辞書を保存する方法
- flat
- hashed
- sparse_hashed
- complex_key_hashed
- complex_key_sparse_hashed
- hashed_array
- complex_key_hashed_array
- range_hashed
- complex_key_range_hashed
- cache
- complex_key_cache
- ssd_cache
- complex_key_ssd_cache
- direct
- complex_key_direct
- ip_trie
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型です。 このストレージ方法はハッシュと同様に機能し、キーに加えて日付/時間（任意の数値型）の範囲を使うことができます。
例：テーブルには、各広告主に対する割引が次の形式で格納されています。
日付範囲のサンプルを使用するには、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を持つ場合は、その中のランダムな範囲が返されます。 - オーバーラップする範囲があり、
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 より時間が経過している場合、そのセルの値は使用されず、そのキーが期限切れとなります。次回使用する際にキーが再リクエストされます。この動作は、allow_read_expired_keys 設定で構成可能です。
これは、辞書を保存するためのすべての方法の中で最も効果的ではありません。キャッシュの速度は、正しい設定と使用シナリオに大きく依存します。キャッシュタイプの辞書は、ヒット率が十分に高い場合（推奨は 99％ 以上）にのみ良好に機能します。平均ヒット率は、system.dictionaries テーブルで確認できます。
設定 allow_read_expired_keys が 1 に設定されている場合（デフォルトは 0）、辞書は非同期更新をサポートします。クライアントがキーをリクエストし、すべてのキーがキャッシュ内にあるが、一部が期限切れの場合、辞書はクライアントに期限切れのキーを返し、それらを非同期でソースからリクエストします。
キャッシュのパフォーマンスを改善するには、LIMIT のあるサブクエリを使用し、外部で辞書を使用する関数を呼び出します。
すべてのタイプのソースがサポートされています。
設定の例：
または
十分なサイズのキャッシュを設定します。セルの数を選択するには実験が必要です：
- いくつかの値を設定します。
- クエリを実行してキャッシュが完全に満杯になるまで実行します。
system.dictionariesテーブルを使用してメモリ消費を評価します。- 必要なメモリ消費量が達成されるまで、セルの数を増減します。
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 プレフィックスを含む単一の String 型属性のみである必要があります。他の型はまだサポートされていません。
構文は次の通りです。
関数は IPv4 の UInt32 または IPv6 の FixedString(16) を受け取ります。例えば：
他の型はまだサポートされていません。この関数は、この IP アドレスに対応するプレフィックスの属性を返します。オーバーラップするプレフィックスがある場合、最も特異なものが返されます。
データは完全に RAM に収まる必要があります。
または
<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の場合、
update_fieldは最終更新時刻と比較して大なりまたは等しい追加のWHERE部分が作成されます。- デフォルトでは、この
WHERE条件はSQLクエリの最上位レベルでチェックされます。あるいは、{condition}キーワードを使用してクエリの他のWHERE句内で条件をチェックすることもできます。例：
- デフォルトでは、この
update_fieldオプションが設定されている場合、追加オプションupdate_lagを設定することもできます。update_lagオプションの値は、更新されたデータをリクエストする前に前回の更新時刻から引かれます。
設定の例：
または
辞書ソース
ClickHouse Cloudで辞書を使用している場合は、DDLクエリオプションを使用して辞書を作成し、ユーザー default として辞書を作成してください。また、Cloud Compatibility guide に記載されているサポートされている辞書ソースのリストを確認してください。
辞書は、さまざまなソースからClickHouseに接続できます。
辞書がxmlファイルを使用して設定されている場合、設定は次のようになります：
DDLクエリの場合、上記の設定は次のようになります：
ソースはsourceセクションで設定されます。
ソースタイプ Local file、Executable file、HTTP(s)、ClickHouseに対してオプション設定が可能です：
または
ソースの種類（source_type）：
ローカルファイル
設定の例：
または
設定フィールド：
path– ファイルの絶対パス。format– ファイル形式。 Formats に記載されているすべての形式がサポートされています。
FILEソースを持つ辞書がDDLコマンド（CREATE DICTIONARY ...）を介して作成されるとき、ソースファイルはuser_filesディレクトリに配置される必要があります。これにより、DBユーザーがClickHouseノードの任意のファイルにアクセスすることを防ぎます。
関連情報
実行可能ファイル
実行可能ファイルとの作業は、辞書がメモリにどのように格納されるかに依存します。辞書がcacheおよびcomplex_key_cacheを使用して格納されている場合、ClickHouseは必要なキーを実行可能ファイルのSTDINにリクエストを送信して要求します。そうでない場合、ClickHouseは実行可能ファイルを起動し、その出力を辞書データとして扱います。
設定の例：
設定フィールド：
command— 実行可能ファイルへの絶対パス、またはファイル名（コマンドのディレクトリがPATHに含まれている場合）。format— ファイル形式。 Formats に記載されているすべての形式がサポートされています。command_termination_timeout— 実行可能スクリプトはメインの読み取り/書き込みループを含むべきです。辞書が破棄された後、パイプは閉じられ、実行可能ファイルはClickHouseが子プロセスにSIGTERMシグナルを送信する前にcommand_termination_timeout秒でシャットダウンする必要があります。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からデータを読み取り、結果をSTDOUTに出力する必要があります。STDIN上で次のデータブロックを待つことができます。ClickHouseはデータブロックを処理した後、STDINを閉じることはなく、必要に応じて別のデータチャンクをパイプします。実行可能スクリプトはこのデータ処理の方法に対応できるようにする必要があります。STDINをポーリングし、早期にデータをSTDOUTにフラッシュする必要があります。
設定の例：
設定フィールド：
command— 実行可能ファイルへの絶対パス、またはファイル名（プログラムのディレクトリがPATHに書き込まれている場合）。format— ファイル形式。 Formats に記載されているすべての形式がサポートされています。pool_size— プールのサイズ。pool_sizeに0を指定すると、プールサイズの制限がなくなります。デフォルト値は16です。command_termination_timeout— 実行可能スクリプトはメインの読み取り/書き込みループを含むべきです。辞書が破棄された後、パイプは閉じられ、実行可能ファイルはClickHouseが子プロセスにSIGTERMシグナルを送信する前にcommand_termination_timeout秒でシャットダウンする必要があります。秒単位で指定します。デフォルト値は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– ファイル形式。 Formats に記載されているすべての形式がサポートされています。credentials– 基本HTTP認証。オプションパラメータ。user– 認証に必要なユーザー名。password– 認証に必要なパスワード。headers– HTTPリクエストに使用されるすべてのカスタムHTTPヘッダーエントリー。オプションパラメータ。header– 単一のHTTPヘッダーエントリー。name– リクエストで送信されるヘッダーに使用される識別子名。value– 特定の識別子名に設定される値。
DDLコマンド（CREATE DICTIONARY ...）を使用して辞書を作成するとき、HTTP辞書のリモートホストは、データベースユーザーが任意のHTTPサーバーにアクセスするのを防ぐために、設定のremote_url_allow_hostsセクションの内容に対して確認されます。
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サーバーは例外をスローする前にクエリを三回再実行します。なお、再試行は応答時間を延長する可能性があります。デフォルト値：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– 辞書の状態を確認するためのクエリ。オプションのパラメータ。詳細はセクション Refreshing dictionary data using LIFETIME を参照してください。background_reconnect– 接続に失敗した場合、バックグラウンドでレプリカに再接続します。オプションのパラメータ。query– カスタムクエリ。オプションのパラメータ。
table または where フィールドは、 query フィールドと一緒に使用することはできません。また、 table または query フィールドのいずれかは宣言する必要があります。
Null
ダミー（空の）辞書を作成するために使用できる特別なソース。このような辞書はテストや、分散テーブルを持つデータノードとクエリノードが分離されているセットアップで便利です。
辞書のキーとフィールド
ClickHouse Cloudで辞書を使用している場合は、DDLクエリオプションを使用して辞書を作成し、ユーザー default として辞書を作成してください。また、Cloud Compatibility guide に記載されているサポートされている辞書ソースのリストを確認してください。
structure 句は、辞書のキーとクエリに使用可能なフィールドを説明します。
XML記述：
属性は以下の要素で説明されています：
<id>— キーカラム<attribute>— データカラム：複数の属性を持つことができます。
DDLクエリ：
属性はクエリの本文で説明されています：
PRIMARY KEY— キーカラムAttrName AttrType— データカラム。複数の属性を持つことができます。
キー
ClickHouseは以下のタイプのキーをサポートします：
- 数値キー。
UInt64。<id>タグで定義されるか、PRIMARY KEYキーワードを使用します。 - 複合キー。異なるタイプの値のセット。
<key>タグまたはPRIMARY KEYキーワードで定義されます。
XML構造には <id> または <key> のいずれかを含むことができます。DDLクエリには単一の PRIMARY KEY を含める必要があります。
キーを属性として記述してはいけません。
数値キー
タイプ: UInt64。
設定の例：
設定フィールド：
name– キーを持つカラムの名前。
DDLクエリ用：
PRIMARY KEY– キーを持つカラムの名前。
複合キー
キーは、任意のデータ型フィールドの tuple であることができます。この場合の layout は complex_key_hashed または complex_key_cache にする必要があります。
複合キーは単一の要素で構成することができます。これにより、文字列をキーとして使用することが可能です。
キーの構造は <key> 要素で設定されます。キーのフィールドは辞書の 属性 と同じ形式で指定されます。例：
または
dictGet* 関数へのクエリでは、タプルがキーとして渡されます。例： dictGetString('dict_name', 'attr_name', tuple('string for field1', num_for_field2))。
属性
設定の例：
または
設定フィールド：
| タグ | 説明 | 必須 |
|---|---|---|
name | カラム名。 | はい |
type | ClickHouseデータ型： 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は現在、フラット、 ハッシュ化、 複雑キー・ハッシュ化、 直接、 複雑キー・直接、 範囲ハッシュ化、ポリゴン、 キャッシュ、複雑キー・キャッシュ、 SSDキャッシュ、 SSD複雑キーキャッシュ 辞書にサポートされています。 IPTrie 辞書では Nullable タイプはサポートされていません。 | はい |
null_value | 存在しない要素のデフォルト値。 この例では、空の文字列です。NULL 値は Nullable タイプにのみ使用できます（前の行のタイプ説明を参照）。 | はい |
expression | ClickHouseが値に対して実行する 式。 式はリモートSQLデータベース内のカラム名として使用できます。したがって、リモートカラムのエイリアスを作成するために使用できます。 デフォルト値：式なし。 | いいえ |
hierarchical | true の場合、属性は現在のキーの親キーの値を含みます。階層型辞書を参照してください。デフォルト値： false 。 | いいえ |
injective | id -> attribute 画像が 単射 であるかどうかを示すフラグ。true の場合、ClickHouseは自動的に GROUP BY 句の後に辞書へのリクエストを配置できます。通常、これによりそのようなリクエストの数が大幅に減少します。デフォルト値： false 。 | いいえ |
is_object_id | クエリが ObjectID によってMongoDBドキュメントに対して実行されるかどうかを示すフラグ。デフォルト値： false 。 |
階層型辞書
ClickHouseは 数値キー を持つ階層型辞書をサポートします。
以下の階層構造を見てください：
この階層は次の辞書テーブルとして表現できます。
| region_id | parent_region | region_name |
|---|---|---|
| 1 | 0 | ロシア |
| 2 | 1 | モスクワ |
| 3 | 2 | 中心 |
| 4 | 0 | グレートブリテン |
| 5 | 4 | ロンドン |
このテーブルには、要素の最近接親のキーを含む parent_region カラムが含まれています。
ClickHouseは外部辞書の属性に階層型の特性をサポートします。この特性により、上記のように階層型辞書を設定できます。
dictGetHierarchy 関数を使用すると、要素の親チェーンを取得できます。
私たちの例では、辞書の構造は次のようになります：
Polygon dictionaries
Polygon dictionariesは、指定されたポイントを含むポリゴンを効率的に検索することを可能にします。 例えば、地理座標による市の地域を定義することです。
ポリゴン辞書の設定例：
ClickHouse Cloudで辞書を使用している場合は、DDLクエリオプションを使用して辞書を作成し、ユーザー default として辞書を作成してください。また、Cloud Compatibility guide に記載されているサポートされている辞書ソースのリストを確認してください。
対応する DDL-query:
ポリゴン辞書を設定する際、キーは以下のいずれかの2つのタイプを持たなければなりません：
- 単純ポリゴン。これはポイントの配列です。
- MultiPolygon。これはポリゴンの配列です。各ポリゴンは2次元ポイントの配列であり、この配列の最初の要素はポリゴンの外境界であり、以降の要素は除外する領域を指定します。
ポイントはその座標の配列またはタプルとして指定できます。現在の実装では、2次元ポイントのみがサポートされています。
ユーザーはClickHouseがサポートするすべてのフォーマットで独自のデータをアップロードできます。
利用可能な3種類の インメモリストレージ があります：
-
POLYGON_SIMPLE。これはナイーブな実装で、クエリごとにすべてのポリゴンを線形に通過し、追加のインデックスを使用せずにそれぞれのメンバーシップを確認します。 -
POLYGON_INDEX_EACH。各ポリゴンに対して別々のインデックスが構築されており、大部分の場合に迅速にそれに属するかどうかをチェックできます（地理的地域に最適化されています）。 また、考慮対象の領域にグリッドが重ねられ、考慮すべきポリゴンの数が大幅に絞り込まれます。 グリッドはセルを16等分して再帰的に分割することによって作成され、2つのパラメータで設定されます。 再帰の深さがMAX_DEPTHに達するか、セルがMIN_INTERSECTIONSポリゴンを超えない場合に分割が停止します。 クエリに応じて、対応するセルがあり、その中に保存されたポリゴンのインデックスが交互にアクセスされます。 -
POLYGON_INDEX_CELL。この配置は、上記で説明したグリッドを作成します。同じオプションが利用可能です。各シートセルに対して、その中に入るすべてのポリゴンのパーツに関してインデックスが構築されており、迅速にリクエストに応答できます。 -
POLYGON。POLYGON_INDEX_CELLの同義語です。
辞書クエリは、辞書に対して操作するための標準 関数 を使用して実行されます。 重要な違いは、ここでのキーがポリゴンを見つけたいポイントになることです。
例
上記で定義した辞書を使用する例：
'points' テーブル内の各ポイントに対して、最後のコマンドを実行した結果、一番小さいエリアのポリゴンがそのポイントを含むものが見つかり、要求された属性が出力されます。
例
ポリゴン辞書からカラムをSELECTクエリを介して読むことができます。辞書設定または対応するDDLクエリに store_polygon_key_column = 1 をオンにするだけです。
クエリ：
結果：
Regular Expression Tree Dictionary
正規表現ツリー辞書は、キーから属性へのマッピングを正規表現のツリーを使用して表現する特殊な辞書のタイプです。いくつかのユースケース、例えば ユーザーエージェント 文字列の解析など、正規表現ツリー辞書で優雅に表現することができます。
ClickHouse Open-Sourceで正規表現ツリー辞書を使用する
正規表現ツリー辞書は、YAMLファイルを指定するYAMLRegExpTreeソースを使用してClickHouseオープンソースで定義されています。
辞書ソース YAMLRegExpTree は、正規表現ツリーの構造を表します。例えば：
この構成は、正規表現ツリーのノードのリストで構成されています。各ノードは以下の構造を持っています：
- regexp: ノードの正規表現。
- attributes: ユーザー定義の辞書属性のリスト。この例では、2つの属性があります：
nameおよびversion。最初のノードは両方の属性を定義しています。2番目のノードは属性nameのみを定義しています。属性versionは2番目のノードの子ノードによって提供されます。- 属性の値には バックリファレンス が含まれる場合があり、マッチした正規表現のキャプチャグループを参照します。例として、最初のノードの属性
versionの値は、正規表現内のキャプチャグループ(\d+[\.\d]*)へのバックリファレンス\1で構成されます。バックリファレンスの番号は1から9までの範囲で、$1または\1（番号1の場合）として記述されます。バックリファレンスはクエリ実行時にマッチしたキャプチャグループに置き換えられます。
- 属性の値には バックリファレンス が含まれる場合があり、マッチした正規表現のキャプチャグループを参照します。例として、最初のノードの属性
- 子ノード: 正規表現ツリーのノードの子ノードのリストで、各ノードは独自の属性と（潜在的に）子ノードを持ちます。文字列のマッチングは深さ優先方式で進行します。文字列が正規表現ノードにマッチすると、辞書はそれがノードの子ノードにもマッチするかどうかを確認します。そうであれば、最も深いマッチングノードの属性が割り当てられます。子ノードの属性は、親ノードの同名の属性を上書きします。YAMLファイル内の子ノードの名前は任意であり、上記の例の
versionsなども可能です。
Regexpツリー辞書は、dictGet、dictGetOrDefault、dictGetAll の関数を使用してのみアクセスできます。
例：
結果：
この場合、最初にトップレイヤーの2番目のノードで正規表現 \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 にオプションの第4引数として渡すことができます。配列は トポロジカル順序 で格納され、子ノードが親ノードの前に、兄弟ノードはソースでの順序に従います。
例：
結果：
マッチングモード
パターンマッチングの動作は、特定の辞書設定で変更できます：
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を作成できます：
Embedded Dictionaries
このページは ClickHouse Cloud には適用されません。ここで文書化されている機能は、ClickHouse Cloud サービスでは利用できません。 詳細については、ClickHouse の Cloud Compatibility ガイドを参照してください。
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識別子や検索エンジンに関する関数もありますが、それらは使用しない方が良いです。
