メインコンテンツへスキップ
メインコンテンツへスキップ

s3 テーブル関数

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

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

s3 table function を 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 Table Function は、GCS XML API と HMAC キーを使用することで Google Cloud Storage と連携します。エンドポイントおよび HMAC の詳細については Google interoperability docs を参照してください。

GCS の場合、access_key_id および secret_access_key と記載されている箇所には、利用する HMAC キーおよび HMAC シークレットを指定してください。

パラメータ

s3 table function は、次のプレーンパラメータをサポートします。

ParameterDescription
urlファイルへのパスを含むバケット URL。読み取り専用モードで次のワイルドカードをサポートします: *, **, ?, {abc,def}, {N..M}。ここで N, M は数値、'abc', 'def' は文字列です。詳細は こちら を参照してください。
NOSIGNこのキーワードを認証情報の代わりに指定した場合、すべてのリクエストは署名なしで送信されます。
access_key_id and secret_access_key指定されたエンドポイントで使用する認証情報を表すキー。省略可能です。
session_token指定されたキーと一緒に使用するセッショントークン。キーを指定する場合は省略可能です。
formatファイルの 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)>

and not https://storage.cloud.google.com.

引数は named collections を使用して渡すこともできます。この場合、url、access_key_id、secret_access_key、format、structure、compression_method は同様に動作し、さらに次の追加パラメータも利用できます:

ArgumentDescription
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;

┌───────日付─┬────始値─┬────高値─┬─────安値─┬───終値─┬───出来高─┬─建玉残─┐
│ 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 拡張子で gzip 形式に圧縮されている場合、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>

本番環境での利用には、named collections の使用を推奨します。以下はその例です。


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 パーティション戦略の例

  1. キーにパーティション 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 つのファイルに書き込まれます。

  1. バケット名にパーティション 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')

これは単発のアクセスや、認証情報を容易にローテーションできる場合には適しています。しかし、繰り返し行うアクセス向けの長期的な解決策としてや、認証情報の機密性が高い場合には推奨されません。このようなケースでは、ロールベースアクセスの利用を推奨します。

ClickHouse Cloud における S3 のロールベースアクセスについてはこちらに記載されています。

設定が完了すると、extra_credentials パラメータを介して roleARN を 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 setting​

これは、読み込み時に 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 - 挿入前にファイルを truncate（内容を消去）できるようにします。デフォルトでは無効です。
  • s3_create_new_file_on_insert - フォーマットにサフィックスがある場合、挿入ごとに新しいファイルを作成できるようにします。デフォルトでは無効です。
  • s3_skip_empty_files - 読み取り時に空のファイルをスキップできるようにします。デフォルトで有効です。

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

ネストされたレコード を含み、かつファイル間で構造が異なる Avro ファイル（例: 一部のファイルではネストされたオブジェクト内に追加のフィールドがある）を読み込む際、ClickHouse は次のようなエラーを返す場合があります:

The number of leaves in record doesn't match the number of elements in 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';