ClickHouseのドキュメントを雑に翻訳してみた(Formats)

以下、 https://clickhouse.yandex/docs/en/table_engines/ の雑な翻訳。Google翻訳に頼りっきりなので品質は期待しないでください。(でも、誤訳などのフィードバックは歓迎)

文章中の (?) 部分は 特に 翻訳が怪しいものになります。

Formats

フォーマットはSELECTでの出力形式と、INSERT時のデータの受け入れ方法をどのフォーマットで行うか決定します。

TabSeparated

TabSeparatedフォーマットでは、データは行毎に書き込まれます。各行には、タブで区切られた値が含まれます。各値のあとには最後を除いてタブが続き、改行が最後に来ます。厳密には、いつでもUNIXの改行(LF)が仮定されています。最後の行にも改行が含まれていなければなりません。値は、引用符を囲まずに特殊文字をエスケープしたテキスト形式で書かれます。

整数は10進形式でかかれます。数字は先頭に"+"文字を含む事が出来ます。(解析時は無視され、レコードへは記録されません)負ではない数字には負の記号を使用できません。読み込み時には、空の文字列を0として解析するか、(符号付きの型に対して)マイナス記号だけをゼロとして構成される文字列を解析する事が出来ます。(?:When reading, it is allowed to parse an empty string as a zero, or (for signed types) a string consisting of just a minus sign as a zero.) 対応するデータ型に収まらない数値は、エラーメッセージ無しで別の数値として解析されます。

浮動小数点は10進形式で書かれます。ドットが小数点記号として使用されます。exponentialエントリは inf, +inf, -inf 及び nan と同様にサポートされています。浮動小数点の入力は、小数点の始まりまたは小数点で終了する事があります。フォーマット中の浮動小数点の精度は失われる事があります。解析中、最も近い機械表現可能な数値として読み取る事は厳密には要求されていません。(保証されていません)

日付は YYYY-MM-DD 形式で記述し、解析されますが、区切り文字としては任意の文字が使用されます。時間のある日付は、 YYYY-MM-DD hh:mm:ss 形式で書かれ、解析されますが、(同様に)区切り文字は任意の文字が使用されます。これは、クライアントまたはサーバーが起動するときのシステムのタイムゾーンで発生します(どちらがデータをフォーマットするかによって異なります)。時間のある日付の場合、夏時間は指定できません。従って、ダンプに夏時間の間の時間がある場合、ダンプはデータと明確に一致しておらず、パーサーは2つのうちの1つを選択します。読み取り操作中、誤った日付と時刻があった場合、オーバーフローまたはNULLの日時として解析され、エラーメッセージは出されません。

例外として、正確に10桁の10進数で構成されている場合、Unixタイムスタンプフォーマットとして認識し解析する事が出来ます。結果はタイムゾーンに依存しません。フォーマット YYYY-MM-DD hh:mm:ss と NNNNNNNNNN は自動的に区別されます。

文字列は、特殊文字をバックスラッシュでエスケープされて出力されます。出力には、 \b, \f, \r, \n, \t, \0, \', \\ というエスケープシーケンスが使用されます。構文解析は、シーケンス \a, \v と \xHH(16進数) といくつかの \c シーケンス(cは任意の文字列)がサポートされます。従って、読み込みデータの改行は \n または \ として書かれます。(?) 例えば、スペースではなく改行を含む文字列 "Hello world" は、次のいずれかのパターンでも解析できます。

Hello\nworld

Hello\
world

2つめの例は、タブ区切りのダンプを書くときにMySQLが使用するのでサポートされています。

TabSeparated形式でデータを渡すときにエスケープする必要がある文字の最小セットは、タブ、改行(LF)、バックスラッシュです。

小さなシンボルセット(?)はエスケープされます。(これらの)文字列が出力されると、ターミナルの出力は壊れてしまいます。

配列は角括弧([])とカンマ区切りの値とリストとして記述されます。配列内の数値項目は通常通りに評価されますが、日付、日時および文字列は、上記と同じエスケープルールで一重引用符(')で記述されます。

TabSeparated形式は、カスタムプログラムとスクリプトを使用してデータを処理するのに便利です。HTTPインターフェースとコマンドラインクライアントのバッチモードでは、デフォルトで使用されます。この形式では、異なるDBMS間でデータを転送する事も出来ます。例えば、MySQLからダンプを取得して、ClickHouseにアップロードしたり、その逆にする事が出来ます。

TabSeparated形式は、合計値(WITH TOTAL を使用する場合)と極値(「極値」が1に設定されている場合)の出力をサポートします。これらの場合、合計値と極値はメインデータのあとに出力されます。メインの結果、合計値、および極値は空行で分けられます。

例:

SELECT EventDate, count() AS c FROM test.hits\
 GROUP BY EventDate WITH TOTALS\
 ORDER BY EventDate FORMAT TabSeparated``

2014-03-17      1406958
2014-03-18      1383658
2014-03-19      1405797
2014-03-20      1353623
2014-03-21      1245779
2014-03-22      1031592
2014-03-23      1046491

0000-00-00      8873898

2014-03-17      1031592
2014-03-23      1406958

このフォーマットは TSV 形式でも入手できます。

TabSeparatedRaw

TabSeparated形式と違い、行がエスケープされずに書き込まれます。この形式は、問い合わせ結果の出力にのみ適しています。構文解析(表に挿入するデータの取得)には適していません。

この形式は、 TSVRaw という名前でも指定出来ます。

TabSeparatedWithNames

列名が最初の行に書き込まれるという点で TabSeparated 形式と異なります。解析中、最初の行は完全に無視されます。列名を使用してその位置を判断したり、その正確性をチェックする事は出来ません。(最初の行の解析サポートは、将来追加される可能性があります)

この形式は、 TSVWithNames という名前でも指定出来ます。

TabSeparatedWithNamesAndTypes

TabSeparated形式と異なるのは、1行目が列名、2行目が型名になっている点です。解析中、1，2行目は完全に無視されます。

この形式は、 TSVWithNamesAndTypes という名前でも指定出来ます。

CSV

いわゆるCSV形式。(RFC 4180)

書式設定時には、行は二重引用符で囲まれます。文字列中の二重引用符は、行ないの2つの2重引用符として出力されます。エスケープ文字の規則はありません。日付と日時型は二重引用符で囲みます。数値は引用符無しで出力されます。値はデリミタ(format_csv_delimiter 参照)で区切られます。行はUNIX改行(LF)を使って区切られます。配列は、次のようにCSVでシリアル化されます。まず、配列がTabSeparated形式のように文字列にシリアル化され、結果の文字列が二重引用符で囲まれます。タプルはCSV形式で別々の列として直列化されます(タプル内のネスティング情報は失われます)。

解析するときは、全ての値を引用符で囲む、囲まない場合に対応します。二重引用符と一重引用符の両方をサポートしています。行は、引用符無しに配置する事も出来ます。この場合、区切り文字または改行(CRまたはLF)まで解析されます。RFCに違反して、行を引用符無しで解析すると、先頭と末尾のスペースとタブは無視されます。改行は、UNIX(LF)、Windows(CRLF)、Mac OS Classic(CR)を全てサポートしています。

CSV形式は、TabSeparated形式と同じ方法で合計と極値の出力をサポートします。

CSVWithNames

TabSeparatedWithNames形式のようにヘッダ行に列名を出力します。

Values

全ての行をブラケットでプリント(出力)します。行はコンマで区切られます。最後の行のあとにコンマはありません。各括弧内の値もコンマで区切られています。数値は、引用符無しの10進形式で出力されます。配列は角括弧で出力されます。文字列、日付、時間のある日付は引用符で出力されます。エスケープルールと解析はTabSeparated形式に似ています。フォーマット設定中に余分なスペースは挿入されませんが、解析中は許可されずスキップします(許可されていない配列値の中のスペースを除く)。

値を渡すときにエスケープする必要のある文字列: シングルクォート、バックスラッシュ

これは、 "INSERT INTO t VALUSE ..." で使用される事を主としたものですが、クエリの結果のフォーマットとしても使用できます。

Vertical

列名が指定された別の行に各値を出力します。この形式は、各行が多数の列で構成されている場合、1行または数行だけ印刷する場合に便利です。この形式は、問い合わせ結果の出力にのみ適しています。構文解析(表に挿入するデータの取得)には適していません。

VerticalRaw

行がエスケープせずに書かれている点で、Vertical形式と異なります。この形式は、問い合わせ結果の出力にのみ適しています。構文解析には適していません。

例:

:) SHOW CREATE TABLE geonames FORMAT VerticalRaw;
Row 1:
──────
statement: CREATE TABLE default.geonames ( geonameid UInt32, date Date DEFAULT CAST('2017-12-08' AS Date)) ENGINE = MergeTree(date, geonameid, 8192)

:) SELECT 'string with \'quotes\' and \t with some special \n characters' AS test FORMAT VerticalRaw;
Row 1:
──────
test: string with 'quotes' and   with some special
 characters

-- the same in Vertical format:
:) SELECT 'string with \'quotes\' and \t with some special \n characters' AS test FORMAT Vertical;
Row 1:
──────
test: string with \'quotes\' and \t with some special \n characters

JSON

データをJSON形式で出力します。データテーブルのほかに、列名と型、更にいくつかの追加情報も出力されます。(出力行の合計数と、LIMITが無い場合、出力された合計の行数)

例:

SELECT SearchPhrase, count() AS c FROM test.hits\
 GROUP BY SearchPhrase WITH TOTALS\
 ORDER BY c DESC LIMIT 5 FORMAT JSON

{
        "meta":
        [
                {
                        "name": "SearchPhrase",
                        "type": "String"
                },
                {
                        "name": "c",
                        "type": "UInt64"
                }
        ],

        "data":
        [
                {
                        "SearchPhrase": "",
                        "c": "8267016"
                },
                {
                        "SearchPhrase": "bathroom interior design",
                        "c": "2166"
                },
                {
                        "SearchPhrase": "yandex",
                        "c": "1655"
                },
                {
                        "SearchPhrase": "spring 2014 fashion",
                        "c": "1549"
                },
                {
                        "SearchPhrase": "freeform photo",
                        "c": "1480"
                }
        ],

        "totals":
        {
                "SearchPhrase": "",
                "c": "8873898"
        },

        "extremes":
        {
                "min":
                {
                        "SearchPhrase": "",
                        "c": "1480"
                },
                "max":
                {
                        "SearchPhrase": "",
                        "c": "8267016"
                }
        },

        "rows": 5,

        "rows_before_limit_at_least": 141137
}

JSONはJavaScriptと互換性があります。これを確実にするために、いくつかの文字がエスケープされます。スラッシュ(/)は(\/)としてエスケープされます。改行 U+2028, U+2029 はいくつかのブラウザを壊すので、 \uXXXX としてエスケープされます。ASCII制御文字もエスケープされます。バックスペース、改ページ、改行、および水平タブ、\b, \f, \n, \r, \t および 00-1F範囲の残りのバイトは \uXXXX シーケンスを使用します。無効なUTF-8シーケンスは文字列(?)に変更されるので、出力テキストは有効なUTF-8シーケンスで構成されます。JavaScriptと互換性のため、Int64とUInt64の整数はデフォルトで二重引用符で囲まれています。引用符を削除するには、設定パラメータ output_format_json_quote_64bit_integers を0にします。

row - 出力行の総数。

rows_before_limit_at_least - LIMIT無しであった場合の最小行数。クエリにLIMITが含まれている場合にのみ出力します。クエリにGROUP BYが含まれていた場合、LIMIT無しである場合の行の正確な行数です。

totals - 極値(?)

この形式は、問い合わせの結果にのみてきしています。構文解析には適していません。 JSONEachRow 形式も参照してください。

JSONCompact

JSON形式と異なり、データ行はオブジェクトではなく配列で出力されます。

例:

{
        "meta":
        [
                {
                        "name": "SearchPhrase",
                        "type": "String"
                },
                {
                        "name": "c",
                        "type": "UInt64"
                }
        ],

        "data":
        [
                ["", "8267016"],
                ["bathroom interior design", "2166"],
                ["yandex", "1655"],
                ["spring 2014 fashion", "1549"],
                ["freeform photo", "1480"]
        ],

        "totals": ["","8873898"],

        "extremes":
        {
                "min": ["","1480"],
                "max": ["","8267016"]
        },

        "rows": 5,

        "rows_before_limit_at_least": 141137
}

この形式は、問い合わせの結果出力にのみ適しています。構文解析には適していません。JSONEachRow 形式も参照してください。

JSONEachRow

各行(改行で区切られたJSON)毎に別々のJSONオブジェクトとしてデータ出力します。

例:

{"SearchPhrase":"","count()":"8267016"}
{"SearchPhrase":"bathroom interior design","count()":"2166"}
{"SearchPhrase":"yandex","count()":"1655"}
{"SearchPhrase":"spring 2014 fashion","count()":"1549"}
{"SearchPhrase":"freeform photo","count()":"1480"}
{"SearchPhrase":"angelina jolie","count()":"1245"}
{"SearchPhrase":"omsk","count()":"1112"}
{"SearchPhrase":"photos of dog breeds","count()":"1091"}
{"SearchPhrase":"curtain design","count()":"1064"}
{"SearchPhrase":"baku","count()":"1000"}

JSON形式と異なり、無効なUTF-8シーケンスの置換はありません。任意のバイトのセットを行に出力できます。これは、情報を失う事なくデータをフォーマットできるようにするために必要です。値はJSON形式と同じ方法でエスケープされます。

解析のために、異なる列の値に対しては任意の順序がサポートされています。いくつかの値は省略してもかまいません。デフォルト値として扱われます。この亜場合、ゼロ行と空行がデフォルト値として使用されます。テーブルに指定できる複雑な値は、デフォルトとしてサポートされていません。要素間の空白は無視されます。コンマがオブジェクトのあとに置かれている場合は、コンマは無視されます。オブジェクトは必ずしも新しい行で区切られている必要はありません。

TSKV

TabSeparated形式と似ていますが、 name=value 形式で値を出力します。 name はTabSeparated形式と同じ方法でエスケープされ、 = 記号のエスケープされます。

例:

SearchPhrase=   count()=8267016
SearchPhrase=bathroom interior design    count()=2166
SearchPhrase=yandex     count()=1655
SearchPhrase=spring 2014 fashion    count()=1549
SearchPhrase=freeform photos       count()=1480
SearchPhrase=angelina jolia    count()=1245
SearchPhrase=omsk       count()=1112
SearchPhrase=photos of dog breeds    count()=1091
SearchPhrase=curtain design        count()=1064
SearchPhrase=baku       count()=1000

多数の小さな列がある場合、この形式は効果がなく、通常は使用する必要がありません。 Yandexの一部門で使用されています。

データの出力と解析の両方がこの形式をサポートしています。解析のために、異なる列の値に対しては任意の順序がサポートされています。いくつかの値は省略してもかまいません。デフォルト値として処理されます。この場合、ゼロと空行がデフォルト値として使用されます。テーブルに指定できる複雑な値は、デフォルトとしてサポートされていません。

構文解析は、等号または値なしのキー値を許可します。が、このフィールドは無視します。

Pretty

出力をUnicode-artテーブルとして出力します、またANSIエスケープシーケンスで色づけされます。表はフルグリッドで描かれ、各行は端末内で2行にわたります。各結果ブロックは別々の表として出力されます。これは、結果をバッファリングせずにブロック出力出来るようにするために必要な措置です(全ての値の出力幅を事前に計算するためにバファリングが必要です)。 端末に多くのデータをダンプする事を避けるために、最初の10,000行だけが出力されます。行数が10,000以上の場合、 "Showed first 10 000" というメッセージが出力されます。この形式は、問い合わせの結果の出力にのみ適しています。構文解析には適していません。

Pretty形式は、合計値(WITH TOTALS を使用する場合)と極値(「極値」が1に設定されている場合)の出力をサポートします。これらの場合、合計値と極値は、メインデータのあとに個別のテーブルで出力されます。例(PrettyCompact形式の場合):

SELECT EventDate, count() AS c FROM test.hits\
 GROUP BY EventDate WITH TOTALS\
 ORDER BY EventDate FORMAT PrettyCompact

┌──EventDate─┬───────c─┐
│ 2014-03-17 │ 1406958 │
│ 2014-03-18 │ 1383658 │
│ 2014-03-19 │ 1405797 │
│ 2014-03-20 │ 1353623 │
│ 2014-03-21 │ 1245779 │
│ 2014-03-22 │ 1031592 │
│ 2014-03-23 │ 1046491 │
└────────────┴─────────┘

Totals:
┌──EventDate─┬───────c─┐
│ 0000-00-00 │ 8873898 │
└────────────┴─────────┘

Extremes:
┌──EventDate─┬───────c─┐
│ 2014-03-17 │ 1031592 │
│ 2014-03-23 │ 1406958 │
└────────────┴─────────┘

PrettyCompact

グリッドが行間に描画され、結果がよりコンパクトであるという点でPrettyと異なります。この形式は、対話型モードの clickhouse-client でデフォルトで使用されます。

PrettyCompactMonoBlock

PrettyCompact形式と異なり、10,000行までバッファリングされ、ブロック単位ではなく単一のテーブルとして出力されます。

PrettyNoEscapes

ANSIエスケープシーケンスは使用されない点でPretty形式とは異なります。これはブラウザでのフォーマットを表示するため、また watch コマンドラインユーティリティを使用するために必要です。

例:

watch -n1 "clickhouse-client --query='SELECT * FROM system.events FORMAT PrettyCompactNoEscapes'"

HTTPインターフェースを使用してブラウザに表示する事が出来ます。

PrettyCompactNoEscapes

PrettyNoEscapesと同じ。

PrettySpaceNoEscapes

PrettyNoEscapesと同じ。

PrettySpace

PrettyCompactと異なり、グリッドの代わりに空白(スペース)が使用されます。

RowBinary

行毎にバイナリ形式でデータをフォーマットして解析します。行と値はセパレータ無しで連続して表示されます。この形式は、行ベースであるため、 Native 形式より効率が悪いです。

整数は、固定長のリトルエンディアンを使用します。例えば、UInt64は8バイトを使用します。DateTimeはUnixタイムスタンプ値としてUInt32として表されます。Dateは、1970-01-01以降の日数をUInt16の値として表されます。文字列はvariant長さ(符号無し LFB128) で表され、その後に文字列バイトが続きます。 FixedString は単純に一連のバイトとして表されます。

配列はvariant長さ (符号無し LFB128) で表され、配列の連続した要素が続きます。

Native

最も効率的なフォーマット。データはバイナリ形式のブロックによって書き込まれ、読み取られます。各ブロックについては、このブロック内の行数、列数、列名および型、および列の部分が順番に記録されます。言い換えれば、この形式は「列」です。列を行に変換しません。これは、サーバー間の相互作用、コマンドラインクライアントの使用、およびC++クライアントのネティブインターフェースとして使用される形式です。

このフォーマットを使用すると、ClickHouse DBMSによってのみ読み取る事が出来るダンプを迅速に生成できます。この形式で自分で作業するのは意味がありません。

Null

何も出力されません。ただし、クエリが処理され、コマンドラインクライアントで使用すると、データがクライアントに送信されます。これは、プロダクトテストを含むテストに使用されます。明らかに、この形式は出力にのみ適しており、解析には向いていません。

XML

XML形式は、出力にのみ適しており、解析には適していません。

例:

<?xml version='1.0' encoding='UTF-8' ?>
<result>
        <meta>
                <columns>
                        <column>
                                <name>SearchPhrase</name>
                                <type>String</type>
                        </column>
                        <column>
                                <name>count()</name>
                                <type>UInt64</type>
                        </column>
                </columns>
        </meta>
        <data>
                <row>
                        <SearchPhrase></SearchPhrase>
                        <field>8267016</field>
                </row>
                <row>
                        <SearchPhrase>bathroom interior design</SearchPhrase>
                        <field>2166</field>
                </row>
                <row>
                        <SearchPhrase>yandex</SearchPhrase>
                        <field>1655</field>
                </row>
                <row>
                        <SearchPhrase>spring 2014 fashion</SearchPhrase>
                        <field>1549</field>
                </row>
                <row>
                        <SearchPhrase>freeform photo</SearchPhrase>
                        <field>1480</field>
                </row>
                <row>
                        <SearchPhrase>angelina jolie</SearchPhrase>
                        <field>1245</field>
                </row>
                <row>
                        <SearchPhrase>omsk</SearchPhrase>
                        <field>1112</field>
                </row>
                <row>
                        <SearchPhrase>photos of dog breeds</SearchPhrase>
                        <field>1091</field>
                </row>
                <row>
                        <SearchPhrase>curtain design</SearchPhrase>
                        <field>1064</field>
                </row>
                <row>
                        <SearchPhrase>baku</SearchPhrase>
                        <field>1000</field>
                </row>
        </data>
        <rows>10</rows>
        <rows_before_limit_at_least>141137</rows_before_limit_at_least>
</result>

列名に許容されるフォーマットが無い場合、要素名として field だけが使用されます。一般に、XML構造はJSON構造に従います。JSONの場合と同様に、無効なUTF-8シーケンスは置換文字列(?)に変更されるので、出力テキストは有効なUTF-8シーケンスで構成されます。

文字列値では、 "<" と "&" は "<" と "&." とエスケープされます。(?)

配列の出力は <array><elem>Hello</elem><elem>World</elem>...</array>, タプルの出力は <tuple><elem>Hello</elem><elem>World</elem>...</tuple> となります。

CapnProto

Cap'n Proto は、 Protocol Buffer と Thrift に似たバイナリメッセージフォーマットです。 JSON や MessagePack とは異なります。

Cap'n Proto メッセージでは厳密に型指定され、自己記述的では無い為、外部スキーマが必要です。スキーマはオンザフライで適用され、クエリ毎にキャッシュされます。

SELECT SearchPhrase, count() AS c FROM test.hits\
 GROUP BY SearchPhrase FORMAT CapnProto SETTINGS schema = 'schema:Message'

スキーマファイル schema.capnp は次のようになります。:

struct Message {
  SearchPhrase @0 :Text;
  c @1 :Uint64;
}

スキーマ・ファイルは、サーバー設定の format_schema_path で指定したディレクトリーに置かれる必要があります。
逆シリアルかは効果的で、通常はシステム負荷を増加させません。