s3 テーブル関数

ファイルを選択/挿入するためのテーブルのようなインターフェースを提供します。対象は Amazon S3 および Google Cloud Storage です。このテーブル関数は hdfs 関数に似ていますが、S3 特有の機能を提供します。

クラスターに複数のレプリカがある場合は、挿入を並列化するために s3Cluster 関数を使用できます。

s3 テーブル関数 を INSERT INTO...SELECT と共に使用する場合、データはストリーミング形式で読み込まれ、挿入されます。メモリには数ブロックのデータしか保持されず、ブロックは S3 から継続的に読み込まれ、宛先テーブルにプッシュされます。

構文

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

GCS

S3 テーブル関数は、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` および `secret_access_key`	指定されたエンドポイントで使用する資格情報を指定するキー。オプションです。
`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` です。
`storage_class_name`	パラメーターはオプションです。サポートされる値: `STANDARD` または `INTELLIGENT_TIERING`。AWS S3 Intelligent Tiering を指定できます。こちらを参照してください。デフォルトは `STANDARD` です。

GCS

GCS URL はこの形式です。Google XML API のエンドポイントは JSON API と異なります:

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はファイルを自動的に解凍します。

使用法

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 │
└─────────┘

これら二つのディレクトリ内のすべてのファイルの総行数をカウントする:

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;

グロブ ** は、再帰的なディレクトリのトラバースに使用できます。以下の例では、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);

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

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

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

これは一時的なアクセスに適していますが、資格情報が簡単にローテーションできる場合には推奨されません。繰り返しのアクセスや資格情報が敏感な場合には、ロールベースのアクセスを利用することをお勧めします。

ClickHouse Cloud における S3 のロールベースのアクセスについては、こちらを参照してください。

構成後、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'))

更なる例はこちらで見つけることができます。

アーカイブの操作

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 — ファイルへのパス。タイプ: LowCardinality(String)。アーカイブの場合、次の形式で表示されます: "{path_to_archive}::{path_to_file_inside_archive}"
_file — ファイル名。タイプ: LowCardinality(String)。アーカイブの場合、アーカイブ内のファイル名が表示されます。
_size — ファイルのサイズ（バイト）。タイプ: Nullable(UInt64)。ファイルサイズが不明な場合、値は NULL です。アーカイブの場合、アーカイブ内のファイルの未圧縮のファイルサイズが表示されます。
_time — ファイルの最終変更時間。タイプ: Nullable(DateTime)。時間が不明な場合、値は NULL です。

use_hive_partitioning 設定

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

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

例

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

請求者負担バケットへのアクセス

請求者負担バケットにアクセスするには、リクエストに x-amz-request-payer = requester ヘッダーを渡す必要があります。これは、headers('x-amz-request-payer' = 'requester') パラメーターを s3 関数に渡すことで実現されます。例えば:

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 - 挿入する前にファイルを切り詰めることができます。デフォルトは無効です。
s3_create_new_file_on_insert - フォーマットにサフィックスがある場合に、各挿入で新しいファイルを作成することを許可します。デフォルトは無効です。
s3_skip_empty_files - 読み取り中に空のファイルをスキップすることを許可します。デフォルトは有効です。

構文​

戻り値​

例​

使用法​

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

パーティション戦略​

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

S3 資格情報を使用する (ClickHouse Cloud)​

アーカイブの操作​

データの挿入​

仮想カラム​

use_hive_partitioning 設定​

請求者負担バケットへのアクセス​

ストレージ設定​

関連​

構文

戻り値

例

使用法