file テーブル関数

s3 テーブル関数と同様に、ファイルに対して SELECT したりファイルへ INSERT したりできるテーブル形式インターフェイスを提供するテーブルエンジンです。ローカルファイルを扱う場合は file() を使用し、S3、GCS、MinIO などのオブジェクトストレージ内のバケットを扱う場合は s3() を使用します。

s3 テーブル関数と同様に、ファイルに対して SELECT したりファイルへ INSERT したりできるテーブル形式インターフェイスを提供するテーブルエンジンです。ローカルファイルを扱う場合は file() を使用し、S3、GCS、MinIO などのオブジェクトストレージ内のバケットを扱う場合は s3() を使用します。

file 関数は、SELECT クエリおよび INSERT クエリで使用でき、ファイルの読み取りや書き込みを行えます。

​

構文

file([path_to_archive ::] path [,format] [,structure] [,compression])

SELECT クエリでは、path に Array(String) を返す式を指定することもできます。

file(['file1.csv', 'file2.csv'], 'CSV', 'column1 UInt32, column2 UInt32')

​

引数

Parameter	Description
path	user_files_path からのファイルへの相対パス、または SELECT クエリで使用するパスの Array(String) です。読み取り専用モードでは、次の グロブ をサポートします: *, ?, {abc,def} ('abc' と 'def' は文字列) および {N..M} (N と M は数値) 。
path_to_archive	zip/tar/7z アーカイブへの相対パスです。path と同じ グロブ をサポートします。
format	ファイルの フォーマット です。
structure	テーブル構造です。形式: 'column1_name column1_type, column2_name column2_type, ...'。
compression	SELECT クエリで使用する場合は既存の圧縮タイプ、INSERT クエリで使用する場合は目的の圧縮タイプです。サポートされる圧縮タイプは gz, br, xz, zst, lz4, bz2 です。

structure 引数を省略すると、ClickHouse はフォーマット自体からスキーマを推論します。 フォーマットごとに、デフォルトのカラム名と型は異なります。 特定のフォーマットのスキーマを確認するには、format テーブル関数に対して DESC を使用します。

たとえば:

DESC format(LineAsString, 'Hello\nWorld')

┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ line │ String │              │                    │         │                  │                │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

戻り値

ファイル内のデータを読み書きするためのテーブルです。

​

File への書き込み例

​

TSVファイルに書き込む

INSERT INTO TABLE FUNCTION
file('test.tsv', 'TSV', 'column1 UInt32, column2 UInt32, column3 UInt32')
VALUES (1, 2, 3), (3, 2, 1), (1, 3, 2)

その結果、データはファイル test.tsv に書き込まれます。

# cat /var/lib/clickhouse/user_files/test.tsv
1    2    3
3    2    1
1    3    2

​

複数のTSVファイルへのパーティション別書き込み

file() 型のテーブル関数にデータを挿入する際に PARTITION BY 式を指定すると、パーティションごとに別々のファイルが作成されます。データをファイルごとに分割することで、読み取り処理のパフォーマンス向上に役立ちます。

INSERT INTO TABLE FUNCTION
file('test_{_partition_id}.tsv', 'TSV', 'column1 UInt32, column2 UInt32, column3 UInt32')
PARTITION BY column3
VALUES (1, 2, 3), (3, 2, 1), (1, 3, 2)

その結果、データは test_1.tsv、test_2.tsv、test_3.tsv の3つのファイルに書き込まれます。

# cat /var/lib/clickhouse/user_files/test_1.tsv
3    2    1

# cat /var/lib/clickhouse/user_files/test_2.tsv
1    3    2

# cat /var/lib/clickhouse/user_files/test_3.tsv
1    2    3

​

ファイルから読み込む例

​

CSVファイルからSELECT

まず、サーバー設定で user_files_path を設定し、test.csv ファイルを用意します:

$ grep user_files_path /etc/clickhouse-server/config.xml
    <user_files_path>/var/lib/clickhouse/user_files/</user_files_path>

$ cat /var/lib/clickhouse/user_files/test.csv
    1,2,3
    3,2,1
    78,43,45

次に、test.csv からテーブルにデータを読み込み、先頭の2行を取得します:

SELECT * FROM
file('test.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')
LIMIT 2;

┌─column1─┬─column2─┬─column3─┐
│       1 │       2 │       3 │
│       3 │       2 │       1 │
└─────────┴─────────┴─────────┘

​

ファイルからテーブルへデータを挿入する

INSERT INTO FUNCTION
file('test.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')
VALUES (1, 2, 3), (3, 2, 1);

SELECT * FROM
file('test.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32');

┌─column1─┬─column2─┬─column3─┐
│       1 │       2 │       3 │
│       3 │       2 │       1 │
└─────────┴─────────┴─────────┘

archive1.zip または archive2.zip に含まれる table.csv からデータを読み取る:

SELECT * FROM file('user_files/archives/archive{1..2}.zip :: table.csv');

​

パス内のグロブ

パスではグロブを使用できます。ファイルは、接尾辞やプレフィックスだけでなく、パスパターン全体に一致する必要があります。例外が 1 つあり、パスが既存の ディレクトリを指していてグロブを使用していない場合は、* が暗黙的にパスに追加されるため、 ディレクトリ内のすべてのファイルが選択されます。

• * — 空文字列を含み、/ を除く任意の長さの文字列を表します。
• ? — 任意の 1 文字を表します。
• {some_string,another_string,yet_another_one} — 文字列 'some_string'、'another_string'、'yet_another_one' のいずれかに置き換えられます。これらの文字列には / 記号を含めることができます。
• {N..M} — >= N かつ <= M の任意の数を表します。
• ** - フォルダ内のすべてのファイルを再帰的に表します。

{} を使った構文は、remote および hdfs テーブル関数と似ています。

​

例

例

次の相対パスを持つファイルがあるとします。

• some_dir/some_file_1
• some_dir/some_file_2
• some_dir/some_file_3
• another_dir/some_file_1
• another_dir/some_file_2
• another_dir/some_file_3

すべてのファイルに含まれる行数の合計を取得するクエリ:

SELECT count(*) FROM file('{some,another}_dir/some_file_{1..3}', 'TSV', 'name String, value UInt32');

同じことを実現できる別のパス式:

SELECT count(*) FROM file('{some,another}_dir/*', 'TSV', 'name String, value UInt32');

暗黙の * を使用して、some_dir の総行数をクエリします。

SELECT count(*) FROM file('some_dir', 'TSV', 'name String, value UInt32');

ファイルの一覧に先頭ゼロ付きの数値範囲が含まれる場合は、各桁ごとに波かっこを使った構文を使用するか、? を使用してください。

例

file000、file001、…、file999 という名前のファイルの行数の合計をクエリします:

SELECT count(*) FROM file('big_dir/file{0..9}{0..9}{0..9}', 'CSV', 'name String, value UInt32');

例

big_dir/ ディレクトリ内のすべてのファイルを再帰的に対象として、合計行数をクエリします:

SELECT count(*) FROM file('big_dir/**', 'CSV', 'name String, value UInt32');

例

ディレクトリ big_dir/ 内の任意のフォルダにある file002 ファイルすべてを再帰的に対象にして、合計行数をクエリします:

SELECT count(*) FROM file('big_dir/**/file002', 'CSV', 'name String, value UInt32');

​

仮想カラム

• _path — ファイルのパス。型: LowCardinality(String).
• _file — ファイル名。型: LowCardinality(String).
• _size — ファイルサイズ (バイト単位) 。型: Nullable(UInt64). ファイルサイズが不明な場合、値は NULL です。
• _time — ファイルの最終更新時刻。型: Nullable(DateTime). 時刻が不明な場合、値は NULL です。

​

use_hive_partitioning 設定

use_hive_partitioning 設定が 1 に設定されている場合、ClickHouse はパス (/name=value/) 内の Hive スタイルのパーティション化を検出し、クエリでパーティションカラムを仮想カラムとして使用できるようになります。これらの仮想カラムは、パーティション化されたパス内の名前と同じ名前になります。

例

Hive スタイルのパーティション化で作成された仮想カラムを使用する

SELECT * FROM file('data/path/date=*/country=*/code=*/*.parquet') WHERE date > '2020-01-01' AND country = 'Netherlands' AND code = 42;

​

設定

Setting	Description
engine_file_empty_if_not_exists	存在しないファイルを空データとして扱えるようにします。デフォルトでは無効です。
engine_file_truncate_on_insert	ファイルへの insert の前に、そのファイルを切り詰められるようにします。デフォルトでは無効です。
engine_file_allow_create_multiple_files	フォーマットに接尾辞がある場合、insert のたびに新しいファイルを作成できるようにします。デフォルトでは無効です。
engine_file_skip_empty_files	読み取り時に空のファイルをスキップできるようにします。デフォルトでは無効です。
storage_file_read_method	ストレージファイルからデータを読み取る method です。read、pread、mmap のいずれかを指定します (clickhouse-local のみ) 。デフォルト値: clickhouse-server では pread、clickhouse-local では mmap。

​

関連

• 仮想カラム
• 処理後にファイル名を変更する