メインコンテンツへスキップ
カスタムのテキスト形式データを扱う必要が生じることはよくあります。たとえば、非標準の形式、無効な JSON、壊れた CSV などです。このようなケースでは、CSV や JSON のような標準的なパーサーでは対応できません。しかし、ClickHouse には強力な Template フォーマットと Regex フォーマットがあります。

​
Template に基づくインポート

次のログファイルからデータをインポートする場合を考えます。 
head error.log

2023/01/15 14:51:17 [error]  client: 7.2.8.1, server: example.com "GET /apple-touch-icon-120x120.png HTTP/1.1"
2023/01/16 06:02:09 [error]  client: 8.4.2.7, server: example.com "GET /apple-touch-icon-120x120.png HTTP/1.1"
2023/01/15 13:46:13 [error]  client: 6.9.3.7, server: example.com "GET /apple-touch-icon.png HTTP/1.1"
2023/01/16 05:34:55 [error]  client: 9.9.7.6, server: example.com "GET /h5/static/cert/icon_yanzhengma.png HTTP/1.1"
このデータをインポートするには、Template フォーマットを使用できます。入力データの各行について、値のプレースホルダーを含むテンプレート文字列を定義する必要があります。 
<time> [error] client: <ip>, server: <host> "<request>"
データのインポート先となるテーブルを作成しましょう。 
CREATE TABLE error_log
(
    `time` DateTime,
    `ip` String,
    `host` String,
    `request` String
)
ENGINE = MergeTree
ORDER BY (host, request, time)
指定したTemplateを使ってデータをインポートするには、テンプレート文字列をファイルに保存する必要があります (この例では row.template) : 
${time:Escaped} [error]  client: ${ip:CSV}, server: ${host:CSV} ${request:JSON}
${name:escaping} フォーマットで、カラム名とエスケープ規則を指定します。ここでは CSV、JSON、Escaped、Quoted など複数のオプションを利用でき、それぞれ 対応するエスケープ規則 に従います。 これで、データのインポート時に、指定したファイルを format_template_row 設定オプションの引数として使用できます (なお、template ファイルと data ファイルの末尾に余分な \n 記号を 付けてはいけません) : 
INSERT INTO error_log FROM INFILE 'error.log'
SETTINGS format_template_row = 'row.template'
FORMAT Template
データがテーブルに読み込まれたことを確認できます。 
SELECT
    request,
    count(*)
FROM error_log
GROUP BY request

┌─request──────────────────────────────────────────┬─count()─┐
│ GET /img/close.png HTTP/1.1                      │     176 │
│ GET /h5/static/cert/icon_yanzhengma.png HTTP/1.1 │     172 │
│ GET /phone/images/icon_01.png HTTP/1.1           │     139 │
│ GET /apple-touch-icon-precomposed.png HTTP/1.1   │     161 │
│ GET /apple-touch-icon.png HTTP/1.1               │     162 │
│ GET /apple-touch-icon-120x120.png HTTP/1.1       │     190 │
└──────────────────────────────────────────────────┴─────────┘

​
空白文字のスキップ

テンプレート内で区切り文字の間にある空白文字をスキップできる TemplateIgnoreSpaces の使用を検討してください： 
Template:               -->  "p1: ${p1:CSV}, p2: ${p2:CSV}"
TemplateIgnoreSpaces    -->  "p1:${p1:CSV}, p2:${p2:CSV}"

​
Templateを使用したデータのエクスポート

Templateを使って、任意のテキスト形式にデータをエクスポートすることもできます。この場合は、2 つのファイルを作成する必要があります。 結果セットテンプレート：結果セット全体のレイアウトを定義します。 
== Top 10 IPs ==
${data}
--- ${rows_read:XML} rows read in ${time:XML} ---
ここで、rows_read と time は各リクエストで利用できるシステムメトリクスです。一方、data は生成された行を表します (このファイルでは ${data} が常に最初のプレースホルダーである必要があります) 。これは、行テンプレートファイル で定義されたテンプレートに基づくものです。 
${ip:Escaped} generated ${total:Escaped} requests
それでは、これらのTemplateを使って次のクエリをエクスポートしてみましょう： 
SELECT
    ip,
    count() AS total
FROM error_log GROUP BY ip ORDER BY total DESC LIMIT 10
FORMAT Template SETTINGS format_template_resultset = 'output.results',
                         format_template_row = 'output.rows';

== Top 10 IPs ==

9.8.4.6 generated 3 requests
9.5.1.1 generated 3 requests
2.4.8.9 generated 3 requests
4.8.8.2 generated 3 requests
4.5.4.4 generated 3 requests
3.3.6.4 generated 2 requests
8.9.5.9 generated 2 requests
2.5.1.8 generated 2 requests
6.8.3.6 generated 2 requests
6.6.3.5 generated 2 requests

--- 1000 rows read in 0.001380604 ---

​
HTMLファイルへのエクスポート

Template ベースの結果は、INTO OUTFILE 句を使ってファイルにエクスポートすることもできます。指定された結果セットおよび行フォーマットに基づいて、HTML ファイルを生成してみましょう。 
SELECT
    ip,
    count() AS total
FROM error_log GROUP BY ip ORDER BY total DESC LIMIT 10
INTO OUTFILE 'out.html'
FORMAT Template
SETTINGS format_template_resultset = 'html.results',
         format_template_row = 'html.row'

​
XML へのエクスポート

Template フォーマットは、XML を含む、考え得るあらゆるテキスト形式のファイルを生成するために使用できます。適切なテンプレートを用意して、エクスポートするだけです。 メタデータを含む標準的な XML 出力を得るには、XML フォーマットを使用することも検討してください。 
SELECT *
FROM error_log
LIMIT 3
FORMAT XML

<?xml version='1.0' encoding='UTF-8' ?>
<result>
        <meta>
                <columns>
                        <column>
                                <name>time</name>
                                <type>DateTime</type>
                        </column>
                        ...
                </columns>
        </meta>
        <data>
                <row>
                        <time>2023-01-15 13:00:01</time>
                        <ip>3.5.9.2</ip>
                        <host>example.com</host>
                        <request>GET /apple-touch-icon-120x120.png HTTP/1.1</request>
                </row>
                ...
        </data>
        <rows>3</rows>
        <rows_before_limit_at_least>1000</rows_before_limit_at_least>
        <statistics>
                <elapsed>0.000745001</elapsed>
                <rows_read>1000</rows_read>
                <bytes_read>88184</bytes_read>
        </statistics>
</result>

​
正規表現を使ったデータのインポート

Regexp フォーマットは、入力データをより複雑な形で解析する必要がある、より高度なケースに対応しています。今回は error.log のサンプルファイルを解析し、ファイル名とプロトコルも抽出して、それぞれ別のカラムに保存してみましょう。まず、そのための新しいテーブルを用意します。 
CREATE TABLE error_log
(
    `time` DateTime,
    `ip` String,
    `host` String,
    `file` String,
    `protocol` String
)
ENGINE = MergeTree
ORDER BY (host, file, time)
これで、正規表現を基にデータをインポートできます。 
INSERT INTO error_log FROM INFILE 'error.log'
SETTINGS
  format_regexp = '(.+?) \\[error\\]  client: (.+), server: (.+?) "GET .+?([^/]+\\.[^ ]+) (.+?)"'
FORMAT Regexp
ClickHouse は、各キャプチャグループのデータを、順序に応じて対応するカラムに挿入します。データを確認してみましょう。 
SELECT * FROM error_log LIMIT 5

┌────────────────time─┬─ip──────┬─host────────┬─file─────────────────────────┬─protocol─┐
│ 2023-01-15 13:00:01 │ 3.5.9.2 │ example.com │ apple-touch-icon-120x120.png │ HTTP/1.1 │
│ 2023-01-15 13:01:40 │ 3.7.2.5 │ example.com │ apple-touch-icon-120x120.png │ HTTP/1.1 │
│ 2023-01-15 13:16:49 │ 9.2.9.2 │ example.com │ apple-touch-icon-120x120.png │ HTTP/1.1 │
│ 2023-01-15 13:21:38 │ 8.8.5.3 │ example.com │ apple-touch-icon-120x120.png │ HTTP/1.1 │
│ 2023-01-15 13:31:27 │ 9.5.8.4 │ example.com │ apple-touch-icon-120x120.png │ HTTP/1.1 │
└─────────────────────┴─────────┴─────────────┴──────────────────────────────┴──────────┘
デフォルトでは、一致しない行があると ClickHouse はエラーを返します。代わりに一致しない行をスキップするには、format_regexp_skip_unmatched オプションを有効にしてください。 
SET format_regexp_skip_unmatched = 1;

​
その他のフォーマット

ClickHouse は、さまざまなユースケースやプラットフォームに対応するため、テキスト形式とバイナリ形式の両方を含む多数のフォーマットをサポートしています。さらに多くのフォーマットとその扱い方については、以下の記事を参照してください。 また、clickhouse-local もご覧ください。これは、ClickHouse server を必要とせずにローカル/リモートファイルを扱える、ポータブルでフル機能のツールです。
最終更新日 2026年6月19日