辞書
辞書は、さまざまな種類の参照リストに便利なマッピング(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を使用して直接読み取ることができます。SELECT文を介して直接辞書にアクセスする場合、キャッシュされた辞書はキャッシュデータのみを返し、非キャッシュ辞書はそれが保持するすべてのデータを返します。
- 辞書は簡単に名前を変更できます。
設定ファイルを使用して辞書を作成する
設定ファイルを使用して辞書を作成することはClickHouse Cloudには適用できません。上記のDDLを使用して、ユーザーdefault
として辞書を作成してください。
辞書の設定ファイルは、次の形式を持っています:
同じファイル内で任意の数の辞書を構成できます。
SELECT
クエリで辞書を説明することにより、小さな辞書の値を変換できます(transform関数を参照)。この機能は辞書に関連していません。
辞書を構成する
ClickHouse Cloudで辞書を使用している場合は、DDLクエリオプションを使用して辞書を作成し、ユーザー default
として辞書を作成してください。また、サポートされている辞書ソースのリストをCloud Compatibility guideで確認してください。
辞書がxmlファイルを使用して構成される場合、辞書の構成は次の構造を持っています:
対応するDDLクエリの構造は次のとおりです:
メモリに辞書を格納する
メモリに辞書を格納するさまざまな方法があります。
flat、hashed、およびcomplex_key_hashedを推奨します。これらは最適な処理速度を提供します。
パフォーマンスが悪い可能性があるため、キャッシングは推奨されません。また、最適なパラメータを選択するのが難しくなります。詳細はキャッシュセクションを参照してください。
辞書のパフォーマンスを向上させる方法はいくつかあります:
GROUP BY
の後に辞書を操作する関数を呼び出します。- 抽出する属性を注入的にマークします。異なるキーが異なる属性値に対応する場合、属性は注入的と呼ばれます。したがって、
GROUP BY
がキーによって属性値を取得する関数を使用する場合、この関数は自動的にGROUP BY
から取り除かれます。
ClickHouseは、辞書に関するエラーに対して例外を生成します。エラーの例:
- アクセスされた辞書がロードできませんでした。
cached
辞書のクエリ中にエラーが発生しました。
dictionaryシステムテーブルで辞書とそのステータスのリストを表示できます。
ClickHouse Cloudで辞書を使用している場合は、DDLクエリオプションを使用して辞書を作成し、ユーザー default
として辞書を作成してください。また、サポートされている辞書ソースのリストをCloud Compatibility guideで確認してください。
構成はこのようになります:
対応するDDLクエリ:
レイアウトにcomplex-key*
の単語が含まれていない辞書は、UInt64型のキーを持ち、complex-key*
辞書は複合キー(複雑、任意の型)を持ちます。
XML辞書のUInt64キーは<id>
タグで定義されます。
構成例(カラムkey_columnはUInt64型を持つ):
複合complex
キーXML辞書は<key>
タグで定義されています。
複合キーの構成例(キーはString型の要素を1つ持ちます):
メモリに辞書を格納する方法
メモリ内の辞書データを格納するさまざまな方法は、CPUとRAM使用のトレードオフに関連しています。辞書関連のブログ記事の「レイアウトの選択」段落に掲載されている決定木は、どのレイアウトを使用するかを決定するための良い出発点です。
- 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型を持っています。このストレージ方法は、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
を持っている場合、その中からランダムな範囲を返します。 - 範囲が重複していて
range_lookup_strategy=max
の場合、最大のrange_min
を持つ一致する範囲を返します。複数の範囲が見つかった場合は、最大のrange_max
を持つ範囲を返します。また再度複数の範囲が見つかった場合(複数の範囲が同じrange_min
およびrange_max
を持っている場合、その中からランダムな範囲を返します。 range_max
がNULL
の場合、その範囲はオープンです。NULL
は最大の可能な値として扱われます。range_min
については、1970-01-01
または0
(-MAX_INT)がオープン値として使用できます。
構成例:
または
重複範囲およびオープン範囲の構成例:
complex_key_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
のあるサブクエリを使用し、辞書を外部で呼び出します。
すべてのタイプのソースがサポートされています。
設定の例:
または
十分に大きなキャッシュサイズを設定してください。セルの数を選択するために実験が必要です:
- 値を設定します。
- キャッシュが完全にいっぱいになるまでクエリを実行します。
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アドレスの検索に対応しています。CIDR表記でIP範囲を保持し、特定のプレフィックス(例:サブネットまたはASN範囲)を持つIPがどれに属するかを迅速に判断できるため、ジオロケーションやネットワーク分類などのIPベースの検索に最適です。
例
ClickHouseに含まれるIPプレフィックスとマッピングを持つテーブルがあるとします:
このテーブルに対してip_trie
辞書を定義しましょう。ip_trie
レイアウトは複合キーを要求します。
または
キーは、許可されたIPプレフィックスを含むString
型属性を1つだけ持つ必要があります。他の型はまだサポートされていません。
構文は次のとおりです:
この関数は、IPv4の場合はUInt32
、IPv6の場合はFixedString(16)
を取ります。例えば:
他の型はまだサポートされていません。この関数は、このIPアドレスに対応するプレフィックスの属性を返します。重複するプレフィックスがある場合は、最も特定のものが返されます。
データは完全にRAMに収まる必要があります。
有効期限を使用した辞書データの更新
ClickHouseは、LIFETIME
タグ(秒単位で定義)に基づいて、定期的に辞書を更新します。LIFETIME
は、完全にダウンロードされた辞書の更新間隔とキャッシュされた辞書の無効化間隔です。
更新中は、旧バージョンの辞書もクエリを実行できます。辞書の更新(辞書の最初の使用のためにロードする場合を除く)は、クエリをブロックしません。更新中にエラーが発生した場合、エラーはサーバーログに書き込まれ、クエリは旧バージョンの辞書を使用し続けます。辞書の更新が成功すると、古い辞書のバージョンは原子的に置き換えられます。
設定例:
ClickHouse Cloudで辞書を使用している場合は、DDLクエリオプションを使用して辞書を作成し、ユーザー default
として辞書を作成してください。また、サポートされている辞書ソースのリストをCloud Compatibility guideで確認してください。
または
設定<lifetime>0</lifetime>
(LIFETIME(0)
)は、辞書の更新を防ぎます。
更新のための時間間隔を設定でき、ClickHouseはこの範囲内で均等にランダムな時間を選択します。これは、多数のサーバーでの更新時に辞書ソースの負荷を分散するために必要です。
設定例:
または
<min>0</min>
および<max>0</max>
の場合、ClickHouseはタイムアウトによって辞書の再読み込みを行いません。この場合、ClickHouseは辞書設定ファイルが変更された場合や、SYSTEM RELOAD DICTIONARY
コマンドが実行された場合に辞書を早期に再読み込みできます。
辞書の更新時、ClickHouseサーバーはソースのタイプによって異なるロジックを適用します:
- テキストファイルの場合、変更時間をチェックします。変更された時間が以前に記録された時間と異なる場合、辞書は更新されます。
- その他のソースからの辞書は、デフォルトで毎回更新されます。
その他のソース(ODBC、PostgreSQL、ClickHouseなど)については、辞書が実際に変更された場合のみ更新するクエリを設定できます。これを実現するには、次の手順を踏んでください:
- 辞書テーブルには、ソースデータが更新されるたびに必ず変更されるフィールドが必要です。
- ソースの設定には、変更フィールドを取得するクエリを指定する必要があります。ClickHouseサーバーは、クエリ結果を行として解釈し、この行が以前の状態と比較して変更されている場合、辞書が更新されます。
<invalidate_query>
フィールドにクエリを指定します。
設定の例:
または
Cache
、ComplexKeyCache
、SSDCache
、およびSSDComplexKeyCache
の辞書では、同期および非同期更新の両方がサポートされています。
Flat
、Hashed
、HashedArray
、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
— 実行可能スクリプトは、主な読み書きループを含む必要があります。辞書が破棄された後、パイプが閉じられ、実行可能ファイルはcommand_termination_timeout
秒内にシャットダウンする必要があります。デフォルト値は10。オプションのパラメータ。command_read_timeout
- コマンドのstdoutからデータを読み取るタイムアウト(ミリ秒単位)。デフォルト値10000。オプションのパラメータ。command_write_timeout
- コマンドのstdinにデータを書き込むタイムアウト(ミリ秒単位)。デフォルト値10000。オプションのパラメータ。implicit_key
— 実行可能ソースファイルは値のみを返すことができ、要求されたキーへの対応は結果の行の順序によって暗黙的に決定されます。デフォルト値はfalse。execute_direct
-execute_direct
=1
の場合、command
はuser_scripts_pathで指定されたユーザースクリプトフォルダ内で検索されます。追加のスクリプト引数はスペース区切りを使用して指定できます。例: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
— 実行可能スクリプトは主な読み書きループを含む必要があります。辞書が破棄された後、パイプが閉じられ、実行可能ファイルはcommand_termination_timeout
秒内にシャットダウンする必要があります。指定は秒単位です。デフォルト値は10。オプションのパラメータ。max_command_execution_time
— データブロックを処理するための最大実行可能スクリプトコマンド実行時間。指定は秒単位です。デフォルト値は10。オプションのパラメータ。command_read_timeout
- コマンドのstdoutからデータを読み取るタイムアウト(ミリ秒単位)。デフォルト値10000。オプションのパラメータ。command_write_timeout
- コマンドのstdinにデータを書き込むタイムアウト(ミリ秒単位)。デフォルト値10000。オプションのパラメータ。implicit_key
— 実行可能ソースファイルは値のみを返すことができ、要求されたキーへの対応は結果の行の順序によって暗黙的に決定されます。デフォルト値はfalse。オプションのパラメータ。execute_direct
-execute_direct
=1
の場合、command
はuser_scripts_pathで指定されたユーザースクリプトフォルダ内で検索されます。追加のスクリプト引数はスペース区切りを使用して指定できます。例: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辞書のリモートホストは、データベースユーザーが任意の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。
PostgreSQL用のunixODBCおよびODBCドライバーをインストールします:
/etc/odbc.ini
を構成します(ClickHouseを実行しているユーザーでサインインしている場合は ~/.odbc.ini
):
ClickHouseの辞書構成:
または
ODBCドライバーを指定するために、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
は二つのキーを持つハッシュされたソース用です。範囲ソースおよび複雑なキーのキャッシュソースはサポートされていません。省略可能で、デフォルト値は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
– 辞書のステータスを確認するためのクエリ。オプションのパラメータ。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
であることができます。この場合、レイアウトは 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は現在Flat、Hashed、ComplexKeyHashed、Direct、ComplexKeyDirect、RangeHashed、Polygon、Cache、ComplexKeyCache、SSDCache、SSDComplexKeyCache辞書でサポートされています。IPTrie辞書では Nullable タイプはサポートされていません。 | はい |
null_value | 存在しない要素のデフォルト値。 例では、空の文字列です。 NULL 値は Nullable タイプのみ(前の行のタイプの説明を参照)で使用できます。 | はい |
expression | 式がClickHouseで値に対して実行されます。 式はリモートSQLデータベース内のカラム名になることがあります。このようにして、リモートカラムのエイリアスを作成することができます。 デフォルト値:式なし。 | いいえ |
hierarchical | true の場合、属性は現在のキーの親キーの値を含みます。階層的辞書を参照してください。デフォルト値: false 。 | いいえ |
injective | id -> attribute の写像が1対1であるかどうかを示すフラグ。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 関数は、要素の親チェーンを取得することを可能にします。
私たちの例では、辞書の構造は以下のようになります:
ポリゴン辞書
この辞書は、ポイントインポリゴンクエリのために最適化されており、実質的に「逆ジオコーディング」ルックアップを行います。座標(緯度/経度)が与えられると、それを含むポリゴン/地域(国や地域の境界など、多くのポリゴンからのセット)を効率的に見つけます。この機能は、位置座標をそれを含む地域にマッピングするのに適しています。
ポリゴン辞書の設定の例:
ClickHouse Cloudで辞書を使用している場合は、DDLクエリオプションを使用して辞書を作成し、ユーザー default
として辞書を作成してください。また、サポートされている辞書ソースのリストをCloud Compatibility guideで確認してください。
対応するDDL-クエリ:
ポリゴン辞書を設定する際、キーは次の2つのタイプのいずれかである必要があります:
- 単純なポリゴン。これはポイントの配列です。
- マルチポリゴン。これはポリゴンの配列です。各ポリゴンはポイントの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
をオンにするだけです。
クエリ:
結果:
正規表現ツリー辞書
この辞書は、階層的な正規表現パターンに基づいてキーを値にマッピングします。これは、正確なキーのマッチングではなく、パターンマッチルックアップ(例:ユーザーエージェント文字列の分類)に最適化されています。
ClickHouseオープンソースで正規表現ツリー辞書を使用する
正規表現ツリー辞書は、YAMLファイル内の正規表現ツリーを含むパスが提供されているYAMLRegExpTreeソースを使用してClickHouseオープンソースで定義されています。
辞書ソース YAMLRegExpTree
は、regexpツリーの構造を表します。例えば:
この設定は、正規表現ツリーのノードのリストで構成されています。各ノードは以下の構造を持っています:
- regexp: ノードの正規表現。
- attributes: ユーザー定義の辞書属性のリスト。この例では、2つの属性:
name
とversion
が存在します。最初のノードは両方の属性を定義します。2番目のノードは属性name
のみを定義します。属性version
は、2番目のノードの子ノードによって提供されます。- 属性の値には、マッチした正規表現のキャプチャグループを参照するバックリファレンスを含めることができます。この例では、最初のノードの属性
version
の値は、正規表現内のキャプチャグループ(\d+[\.\d]*)
に対するバックリファレンス\1
で構成されています。バックリファレンスの番号は1から9までで、$1
または\1
(番号1の場合)として書かれます。バックリファレンスは、クエリ実行中に一致したキャプチャグループで置き換えられます。
- 属性の値には、マッチした正規表現のキャプチャグループを参照するバックリファレンスを含めることができます。この例では、最初のノードの属性
- 子ノード: regexpツリーノードの子のリストであり、それぞれは独自の属性と(潜在的に)子ノードを持ちます。文字列マッチングは深さ優先の方式で進行します。文字列がregexpノードと一致すると、辞書はそのノードの子ノードにも一致するかどうかを確認します。その場合、最も深く一致するノードの属性が割り当てられます。子ノードの属性は、親ノードの同名属性を上書きします。YAMLファイル内の子ノードの名前は任意で、上記の例では
versions
です。
Regexpツリ辞書へのアクセスは、dictGet
、dictGetOrDefault
、および dictGetAll
関数を使用することでのみ許可されます。
例:
結果:
この場合、最初に上層の2番目のノードで正規表現 \d+/tclwebkit(?:\d+[\.\d]*)
に一致します。辞書はさらに子ノードに進み、文字列が 3[12]/tclwebkit
にも一致することを見つけます。その結果、属性 name
の値は Android
(最初の層で定義されている)となり、属性 version
の値は 12
(子ノードで定義されている)になります。
強力なYAML設定ファイルを使用することで、regexpツリ辞書をユーザーエージェント文字列パーサーとして利用できます。私は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でregexpツリ辞書を使用するには、まずClickHouseオープンソースでYAMLファイルからregexpツリ辞書を作成し、その辞書を 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を作成できます:
組み込み辞書
このページは ClickHouse Cloud には適用されません。ここに記載されている機能は ClickHouse Cloud サービスでは利用できません。詳細については ClickHouse の Cloud Compatibility ガイドをご覧ください。
ClickHouseには、ジオベースと連携するためのビルトイン機能があります。
これにより、次のことが可能になります:
- 地域のIDを使用して、その名前を希望の言語で取得する。
- 地域のIDを使用して、都市、地域、連邦地区、国、または大陸のIDを取得する。
- 地域が別の地域の一部であるか確認する。
- 親地域のチェーンを取得する。
すべての関数は「トランスローカリティ」をサポートしており、地域所有権に関する異なる視点を同時に使用する能力があります。詳細については、「WEB分析辞書と作業するための関数」のセクションを参照してください。
内部辞書は、デフォルトパッケージでは無効になっています。
それらを有効にするには、サーバー構成ファイルで 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は100万を超えてはいけません。
辞書はサーバーを再起動せずに更新できます。ただし、利用可能な辞書のセットは更新されません。
更新のためにファイルの変更時間がチェックされます。ファイルが変更された場合、辞書が更新されます。
変更を確認する間隔は、builtin_dictionaries_reload_interval
パラメータで設定されています。
辞書の更新(初回使用時のロードを除く)はクエリをブロックしません。更新中は、旧バージョンの辞書を使用するクエリが実行されます。更新中にエラーが発生した場合、そのエラーはサーバーログに書き込まれ、クエリは旧バージョンの辞書を使用し続けます。
私たちは、ジオベースを持つ辞書の定期的な更新をお勧めします。更新中は新しいファイルを生成し、それらを別の場所に書き込んでください。すべてが準備完了したら、それらをサーバーによって使用されるファイルに名前を変更してください。
OS識別子や検索エンジンで作業するための関数もありますが、それらは使用するべきではありません。