s3 テーブル関数 - ClickHouse Documentation

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 [,..]])

GCSS3 テーブル関数は、GCS XML API と HMAC キーを使用して Google Cloud Storage と連携できます。エンドポイントと HMAC の詳細については、Google の相互運用性に関するドキュメントを参照してください。GCS を使用する場合は、access_key_id と secret_access_key の箇所を、それぞれお使いの HMAC キーと HMAC シークレットに置き換えてください。

パラメーター 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 形式のパーティションディレクトリが生成されます。デフォルトは `WILDCARD` です。
`partition_columns_in_data_file`	省略可能なパラメーターです。`HIVE` パーティション方式でのみ使用されます。パーティションカラムがデータファイルに書き込まれている想定かどうかを ClickHouse に指定します。デフォルトは `false` です。
`extra_credentials`	省略可能なパラメーターです。ClickHouse Cloud のロールベースアクセスで `role_arn` を渡すために使用します。設定手順については Secure S3 を参照してください。
`storage_class_name`	省略可能なパラメーターです。サポートされる値は `STANDARD` または `INTELLIGENT_TIERING` です。AWS S3 Intelligent Tiering を指定できます。デフォルトは `STANDARD` です。

GCSGoogle 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',
   '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 │
└────────────┴─────────┴─────────┴─────────┴─────────┴──────────┴─────────┘

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

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

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

*.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', '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/*', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')

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

ファイル一覧に先頭がゼロの数値範囲が含まれている場合は、各桁を個別に波括弧で指定するか、? を使用してください。

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

SELECT count(*)
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/my-test-bucket-768/big_prefix/file-{000..999}.csv', '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/**', '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', 'CSV', 'name String, value UInt32', 'gzip');

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

SELECT * FROM s3('s3://clickhouse-public-datasets/my-test-bucket-768/**/test-data.csv.gz', '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}ワイルドカードを実際のパーティションキーに置き換えます。 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 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 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 function に渡せます。例:

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'))

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

アーカイブの扱い

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'
);

ClickHouse は、次の 3 つのアーカイブフォーマットをサポートしています。 ZIP TAR 7Z ZIP アーカイブと TAR アーカイブはサポート対象の任意のストレージからアクセスできますが、7Z アーカイブは ClickHouse がインストールされているローカルのファイルシステムからのみ読み取れます。

データの挿入

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

仮想カラム

_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'))

┌───num_rows─┬─num_files─┐
│ 1110000000 │       111 │
└────────────┴───────────┘

1 row in set. Elapsed: 3.089 sec. Processed 1.09 billion rows, 0.00 B (353.55 million rows/s., 0.00 B/s.)
Peak memory usage: 192.27 KiB.

ストレージ設定

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

ネストされた Avro スキーマ

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

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

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

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

パフォーマンスに関する注意schema_inference_mode='union' は、スキーマを推論するために各ファイルをスキャンする必要があるため、非常に大規模な S3 データセットでは時間がかかる場合があります。

例

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

## 関連項目           

- [S3 エンジン](/reference/engines/table-engines/integrations/s3)
- [S3 と ClickHouse のインテグレーション](/integrations/connectors/data-ingestion/AWS/integrating-s3-with-clickhouse)

​構文

​戻り値

​例

​使用方法

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

​パーティション方式

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

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

​アーカイブの扱い

​データの挿入

​仮想カラム

​use_hive_partitioning 設定

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

​ストレージ設定

​ネストされた Avro スキーマ

構文

戻り値

例

使用方法

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

パーティション方式

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

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

アーカイブの扱い

データの挿入

仮想カラム

use_hive_partitioning 設定

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

ストレージ設定

ネストされた Avro スキーマ