s3 テーブル関数

Amazon S3 および Google Cloud Storage 内のファイルに対して SELECT/INSERT を行うためのテーブル形式インターフェイスを提供します。このテーブル関数は hdfs function に似ていますが、S3 固有の機能を備えています。

クラスター内に複数のレプリカがある場合は、代わりに s3Cluster function を使用して INSERT を並列化できます。

s3 table function を INSERT INTO...SELECT と併用すると、データはストリーミング方式で読み込まれ、挿入されます。S3 からブロックが継続的に読み込まれて宛先テーブルに送られるため、メモリ上に保持されるデータブロックはごく少数です。

構文

s3(url [, NOSIGN | access_key_id, secret_access_key, [session_token]] [,format] [,structure] [,compression_method],[,headers], [,extra_credentials], [,partition_strategy], [,partition_columns_in_data_file])
s3(named_collection[, option=value [,..]])

パラメーター

s3 テーブル関数は、以下の通常のパラメーターをサポートします。

パラメーター	説明
`url`	ファイルへのパスを含むバケット URL。読み取り専用モードでは、``、`*`、`?`、`{abc,def}`、`{N..M}` のワイルドカードをサポートします。ここで、`N` と `M` は数値、`'abc'` と `'def'` は文字列です。詳細はこちらを参照してください。
`NOSIGN`	認証情報の代わりにこのキーワードを指定すると、すべてのリクエストが署名なしで送信されます。
`access_key_id` and `secret_access_key`	指定した endpoint で使用する認証情報を指定するキーです。省略可能です。
`session_token`	指定したキーとともに使用するセッショントークンです。キーを渡す場合は省略可能です。
`format`	ファイルのフォーマットです。
`structure`	テーブルの構造です。形式は `'column1_name column1_type, column2_name column2_type, ...'` です。
`compression_method`	省略可能なパラメーターです。サポートされる値は `none`、`gzip` または `gz`、`brotli` または `br`、`xz` または `LZMA`、`zstd` または `zst` です。デフォルトでは、ファイル拡張子から圧縮方式を自動判別します。
`headers`	省略可能なパラメーターです。S3 リクエストにヘッダーを渡せます。形式は `headers(key=value)` です。例: `headers('x-amz-request-payer' = 'requester')`。
`partition_strategy`	省略可能なパラメーターです。サポートされる値は `wildcard` または `hive` です。`wildcard` ではパス内に `{_partition_id}` が必要で、これはパーティションキーに置き換えられます。`hive` ではワイルドカードは使用できず、パスがテーブルのルートであると見なされ、ファイル名に Snowflake ID、拡張子にファイルフォーマットを使用した Hive 形式のパーティションディレクトリが生成されます。明示的な方式を指定しない場合、`{_partition_id}` を含むパスでは `wildcard` が使用されます。別のグロブを含むパスではパーティション方式を使用せず、`PARTITION BY` は無視されます。グロブを含まないパスでは、`file_like_engine_default_partition_strategy` が `hive` の場合は `hive` を使用し、それ以外の場合はパーティション方式を使用しません。
`partition_columns_in_data_file`	省略可能なパラメーターです。`hive` パーティション方式でのみ使用されます。パーティションカラムがデータファイルに書き込まれている想定かどうかを ClickHouse に指定します。デフォルトは `false` です。
`extra_credentials`	省略可能なパラメーターです。ClickHouse Cloud のrole-based accessで `role_arn` を渡すために使用します。設定手順については Secure S3 を参照してください。
`storage_class_name`	省略可能なパラメーターです。サポートされる値は `STANDARD`、`REDUCED_REDUNDANCY`、`STANDARD_IA`、`ONEZONE_IA`、`INTELLIGENT_TIERING`、`GLACIER_IR`、`EXPRESS_ONEZONE` です。即時取得が可能な S3 storage classes のみサポートされます (`GLACIER` や `DEEP_ARCHIVE` などのアーカイブクラスはサポートされません) 。AWS S3 Intelligent Tiering を指定できます。デフォルトは `STANDARD` です。

Info - GCS

Google XML API の endpoint は JSON API とは異なるため、GCS の URL は次の形式になります。

  https://storage.googleapis.com/<bucket>/<folder>/<filename(s)>

~~https://storage.cloud.google.com~~ ではありません。

引数は、名前付きコレクションを使用して渡すこともできます。この場合、url、access_key_id、secret_access_key、format、structure、compression_method は同じように機能し、いくつかの追加パラメータがサポートされます。

引数	説明
`filename`	指定した場合、URL に追加されます。
`use_environment_credentials`	デフォルトで有効です。環境変数 `AWS_CONTAINER_CREDENTIALS_RELATIVE_URI`、`AWS_CONTAINER_CREDENTIALS_FULL_URI`、`AWS_CONTAINER_AUTHORIZATION_TOKEN`、`AWS_EC2_METADATA_DISABLED` を使用して追加のパラメータを渡せます。
`no_sign_request`	デフォルトでは無効です。
`expiration_window_seconds`	デフォルト値は 120 です。

戻り値

指定したファイル内のデータの読み書きに使用する、指定した構造を持つテーブル。

例

S3 上のファイル https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv からテーブルの先頭 5 行を選択する例:

SELECT *
FROM s3(
   'https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv',
   NOSIGN,
   'CSVWithNames'
)
LIMIT 5;

┌───────Date─┬────Open─┬────High─┬─────Low─┬───Close─┬───Volume─┬─OpenInt─┐
│ 1984-09-07 │ 0.42388 │ 0.42902 │ 0.41874 │ 0.42388 │ 23220030 │       0 │
│ 1984-09-10 │ 0.42388 │ 0.42516 │ 0.41366 │ 0.42134 │ 18022532 │       0 │
│ 1984-09-11 │ 0.42516 │ 0.43668 │ 0.42516 │ 0.42902 │ 42498199 │       0 │
│ 1984-09-12 │ 0.42902 │ 0.43157 │ 0.41618 │ 0.41618 │ 37125801 │       0 │
│ 1984-09-13 │ 0.43927 │ 0.44052 │ 0.43927 │ 0.43927 │ 57822062 │       0 │
└────────────┴─────────┴─────────┴─────────┴─────────┴──────────┴─────────┘

Note

ClickHouse は、ファイル名の拡張子からデータのフォーマットを判別します。たとえば、前のコマンドは CSVWithNames を指定しなくても実行できました。

SELECT *
FROM s3(
   'https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv',
   NOSIGN
)
LIMIT 5;

ClickHouse はファイルの圧縮方式も判別できます。たとえば、ファイル名が .csv.gz で終わっていれば、ClickHouse が自動的に展開します。

Note

*.parquet.snappy や *.parquet.zstd のような名前の Parquet ファイルは、ClickHouse が誤って解釈し、TOO_LARGE_COMPRESSED_BLOCK や ZSTD_DECODER_FAILED エラーの原因になることがあります。これは、Parquet では実際には行グループ単位およびカラム単位で圧縮が行われているにもかかわらず、ClickHouse がファイル全体を Snappy または ZSTD でエンコードされたデータとして読み込もうとするためです。

Parquet のメタデータには、各カラムの圧縮方式がすでに記録されているため、ファイル拡張子は不要です。このような場合は、compression_method = 'none' を指定してください。

SELECT *
FROM s3(
  'https://<my-bucket>.s3.<my-region>.amazonaws.com/path/to/my-data.parquet.snappy',
  compression_format = 'none'
);

使用方法

S3上に以下のURIを持つ複数のファイルがあるとします：

1から3までの数字で終わるファイルの行数を数えます：

SELECT count(*)
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/my-test-bucket-768/{some,another}_prefix/some_file_{1..3}.csv', NOSIGN, 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')

┌─count()─┐
│      18 │
└─────────┘

これら2つのディレクトリ内のすべてのファイルの合計行数を取得します：

SELECT count(*)
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/my-test-bucket-768/{some,another}_prefix/*', NOSIGN, 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')

┌─count()─┐
│      24 │
└─────────┘

file-1.csv、…、file-4.csv という名前のファイル内の総行数をカウントします：

SELECT count(*)
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/my-test-bucket-768/big_prefix/file-{1..4}.csv', NOSIGN, 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32');

┌─count()─┐
│      12 │
└─────────┘

test-data.csv.gz ファイルにデータを挿入します：

INSERT INTO FUNCTION s3('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/test-data.csv.gz', 'CSV', 'name String, value UInt32', 'gzip')
VALUES ('test-data', 1), ('test-data-2', 2);

既存のテーブルからファイル test-data.csv.gz にデータを挿入する:

INSERT INTO FUNCTION s3('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/test-data.csv.gz', 'CSV', 'name String, value UInt32', 'gzip')
SELECT name, value FROM existing_table;

Glob ** を使用すると、ディレクトリを再帰的にトラバースできます。次の例では、my-test-bucket-768 ディレクトリからすべてのファイルを再帰的にフェッチします。

SELECT * FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/**', NOSIGN, 'CSV', 'name String, value UInt32', 'gzip');

以下は、my-test-bucket ディレクトリ内の任意のフォルダにあるすべての test-data.csv.gz ファイルからデータを再帰的に取得します：

SELECT * FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/**/test-data.csv.gz', NOSIGN, 'CSV', 'name String, value UInt32', 'gzip');

注意: サーバー設定ファイルにカスタムURLマッパーを指定できます。例:

SELECT * FROM s3('s3://clickhouse-public-datasets/my-test-bucket-768/**/test-data.csv.gz', NOSIGN, 'CSV', 'name String, value UInt32', 'gzip');

URL 's3://clickhouse-public-datasets/my-test-bucket-768/**/test-data.csv.gz' は 'http://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/**/test-data.csv.gz' に置き換えられます

カスタムマッパーは config.xml に追加できます:

<url_scheme_mappers>
   <s3>
      <to>https://{bucket}.s3.amazonaws.com</to>
   </s3>
   <gs>
      <to>https://{bucket}.storage.googleapis.com</to>
   </gs>
   <oss>
      <to>https://{bucket}.oss.aliyuncs.com</to>
   </oss>
</url_scheme_mappers>

本番環境で利用する場合は、名前付きコレクションの使用を推奨します。以下に例を示します。


CREATE NAMED COLLECTION creds AS
        access_key_id = '***',
        secret_access_key = '***';
SELECT count(*)
FROM s3(creds, url='https://s3-object-url.csv')

パーティション分割書き込み

パーティション方式

INSERTクエリでのみサポートされます。

wildcard: ファイルパス内の{_partition_id}ワイルドカードを実際のパーティションキーに置き換えます。パスに{_partition_id}が含まれる場合はデフォルトで選択されます。

partition_strategyが設定されていない場合、他のグロブを含むパスではパーティション方式を使用せず、PARTITION BYを無視します。グロブを含まないパスでは、file_like_engine_default_partition_strategyがhiveの場合はhiveを使用し、それ以外の場合はパーティション方式を使用しません。

hiveは、読み取りと書き込みで Hive スタイルのパーティション化を行います。ファイルは次のフォーマットで生成されます: <prefix>/<key1=val1/key2=val2...>/<snowflakeid>.<toLower(file_format)>。

hiveパーティション方式の例

INSERT INTO FUNCTION s3(s3_conn, filename='t_03363_function', format=Parquet, partition_strategy='hive') PARTITION BY (year, country) SELECT 2020 as year, 'Russia' as country, 1 as id;

SELECT _path, * FROM s3(s3_conn, filename='t_03363_function/**.parquet');

   ┌─_path──────────────────────────────────────────────────────────────────────┬─id─┬─country─┬─year─┐
1. │ test/t_03363_function/year=2020/country=Russia/7351295896279887872.parquet │  1 │ Russia  │ 2020 │
   └────────────────────────────────────────────────────────────────────────────┴────┴─────────┴──────┘

wildcard パーティション方式の例

キーにパーティション ID を使用すると、ファイルが個別に作成されます。

INSERT INTO TABLE FUNCTION
    s3('http://bucket.amazonaws.com/my_bucket/file_{_partition_id}.csv', 'CSV', 'a String, b UInt32, c UInt32', partition_strategy='wildcard')
    PARTITION BY a VALUES ('x', 2, 3), ('x', 4, 5), ('y', 11, 12), ('y', 13, 14), ('z', 21, 22), ('z', 23, 24);

その結果、データは file_x.csv、file_y.csv、file_z.csv の3つのファイルに書き込まれます。

バケット名にパーティション ID を含めると、別々のバケットにファイルが作成されます。

INSERT INTO TABLE FUNCTION
    s3('http://bucket.amazonaws.com/my_bucket_{_partition_id}/file.csv', 'CSV', 'a UInt32, b UInt32, c UInt32', partition_strategy='wildcard')
    PARTITION BY a VALUES (1, 2, 3), (1, 4, 5), (10, 11, 12), (10, 13, 14), (20, 21, 22), (20, 23, 24);

その結果、データは異なるバケット内の3つのファイル、my_bucket_1/file.csv、my_bucket_10/file.csv、my_bucket_20/file.csv に書き込まれます。

公開バケットへのアクセス

ClickHouse は、さまざまなソースから認証情報を取得しようとします。そのため、公開バケットによってはアクセス時に問題が発生し、クライアントが 403 エラーコードを返すことがあります。この問題は、NOSIGN キーワードを使用してクライアントがすべての認証情報を無視し、リクエストに署名しないようにすることで回避できます。

SELECT *
FROM s3(
   'https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv',
   NOSIGN,
   'CSVWithNames'
)
LIMIT 5;

S3認証情報の使用 (ClickHouse Cloud)

公開されていないバケットでは、aws_access_key_id と aws_secret_access_key を関数に渡すことができます。例:

SELECT count() FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/mta/*.tsv', '<KEY>', '<SECRET>','TSVWithNames')

これは、一時的なアクセスや、認証情報を容易にローテーションできる場合に適しています。ただし、長期にわたって繰り返しアクセスする用途や、認証情報の機密性が高い場合には推奨されません。そのような場合は、role-based access を使用することを推奨します。

ClickHouse Cloud での S3 の role-based access については、こちらを参照してください。

設定後は、roleARN を extra_credentials パラメータ経由で s3関数に渡せます。例:

SELECT count() FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/mta/*.tsv','CSVWithNames',extra_credentials(role_arn = 'arn:aws:iam::111111111111:role/ClickHouseAccessRole-001'))

任意で、role_arn とあわせて external_id を指定することもできます。これは AWS STS の AssumeRole 呼び出しの ExternalId パラメータとして渡され、ロールの信頼ポリシーで共有シークレットを必須にできるため、confused deputy problem の軽減に役立ちます。例:

SELECT count() FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/mta/*.tsv','CSVWithNames',extra_credentials(role_arn = 'arn:aws:iam::111111111111:role/ClickHouseAccessRole-001', external_id = 'my-external-id'))

さらに詳しい例はこちらをご覧ください

アーカイブの扱い

S3 上に、次の URI を持つ複数のアーカイブファイルがあるとします。

:: を使うと、これらのアーカイブからデータを抽出できます。グロブは URL 部分と、:: の後の部分 (アーカイブ内のファイル名を指定する部分) の両方で使用できます。

SELECT *
FROM s3(
   'https://s3-us-west-1.amazonaws.com/umbrella-static/top-1m-2018-01-1{0..2}.csv.zip :: *.csv',
   NOSIGN
);

データの挿入

行を挿入できるのは新しいファイルに対してのみである点に注意してください。マージサイクルやファイルの分割処理はありません。いったんファイルが書き込まれると、その後の挿入は失敗します。詳細はこちらを参照してください。

仮想カラム

_path — ファイルのパス。Type: LowCardinality(String)。アーカイブの場合は、"{path_to_archive}::{path_to_file_inside_archive}" 形式のパスを表示します。
_file — ファイル名。Type: LowCardinality(String)。アーカイブの場合は、アーカイブ内のファイル名を表示します。
_size — ファイルサイズ (バイト単位) 。Type: Nullable(UInt64)。ファイルサイズが不明な場合、値は NULL です。アーカイブの場合は、アーカイブ内のファイルの非圧縮サイズを表示します。
_time — ファイルの最終更新時刻。Type: Nullable(DateTime)。時刻が不明な場合、値は NULL です。

use_hive_partitioning 設定

これは、読み取り時に ClickHouse が Hive スタイルでパーティション化されたファイルを解析するためのヒントです。書き込みには影響しません。読み取りと書き込みを対称的に行うには、partition_strategy 引数を使用してください。

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

例

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

requester-pays バケットへのアクセス

requester-pays バケットにアクセスするには、すべてのリクエストにヘッダー x-amz-request-payer = requester を含める必要があります。これを行うには、s3関数にパラメーター headers('x-amz-request-payer' = 'requester') を渡します。例:

SELECT
    count() AS num_rows,
    uniqExact(_file) AS num_files
FROM s3('https://coiled-datasets-rp.s3.us-east-1.amazonaws.com/1trc/measurements-100*.parquet', 'AWS_ACCESS_KEY_ID', 'AWS_SECRET_ACCESS_KEY', headers('x-amz-request-payer' = 'requester'))

┌

相対 URL の解決

s3_base 設定を使用すると、s3 関数に相対 URL を渡すことができます。s3_base が設定されており、関数の引数にスキームが指定されていない場合、RFC 3986 に従い、ベース URL を基準に解決されます。これは、url 関数の url_base 設定と同じルールです。絶対 URL は変更されずにそのまま渡されます。

この設定は、S3 テーブルエンジン、および s3 設定を共有するテーブル関数 (s3Cluster、gcs、oss) にも適用されます。S3 テーブルエンジンでは、解決された URL が保存されるテーブル定義にマテリアライズされるため、テーブルの作成後は s3_base の値に依存しません。

例

SET s3_base = 's3://clickhouse-public-datasets/';
SELECT count() FROM s3('hits_compatible/hits.csv', NOSIGN);

ストレージ設定

s3_truncate_on_insert - ファイルに insert する前に、そのファイルを切り詰めることを許可します。デフォルトでは無効です。
s3_create_new_file_on_insert - フォーマットに接尾辞がある場合、insert のたびに新しいファイルを作成することを許可します。デフォルトでは無効です。
s3_skip_empty_files - 読み取り時に空のファイルをスキップすることを許可します。デフォルトでは有効です。
s3_base - s3 関数に渡された相対 URL を解決するためのベース URL。デフォルトでは空 (無効) です。

ネストされた Avro スキーマ

ファイルごとに構造が異なるネストされたレコードを含む Avro ファイルを読み込むと (たとえば、一部のファイルではネストされたオブジェクト内に追加のフィールドがある場合) 、ClickHouse は次のようなエラーを返すことがあります。

record の leaf 数が tuple の要素数と一致しません…

これは、ClickHouse がすべてのネストされたレコード構造に同一のスキーマを期待しているためです。このような場合は、次のいずれかの方法で対応できます。

schema_inference_mode='union' を使用して、異なるネストされたレコードのスキーマをマージする、または
ネストされた構造を手動で揃えたうえで、 use_structure_from_insertion_table_in_table_functions=1 を有効にする。

例

INSERT INTO data_stage
SELECT
    id,
    data
FROM s3('https://bucket-name/*.avro', 'Avro')
SETTINGS schema_inference_mode='union';