メインコンテンツまでスキップ
メインコンテンツまでスキップ

s3 テーブル関数

AWS 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])
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')）です。
GCS

GCS URL は、このフォーマットであり、Google XML API のエンドポイントは JSON API とは異なります:

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

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

引数は named collections を使用しても渡すことができます。この場合、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 は自動的にファイルを解凍します。

使用方法​

次の URI を持ついくつかのファイルが S3 にあるとします:

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>

本番の使用ケースには、named collections の使用を推奨します。以下は例です:


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

パーティション化された書き込み​

S3 テーブルにデータを挿入する際に PARTITION BY 式を指定すると、各パーティション値ごとに個別のファイルが作成されます。データを個別のファイルに分割することで、読み取り操作の効率が向上します。

例

  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 に対する役割に基づくアクセスは こちら でドキュメント化されています。

構成が完了すると、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 になります。

Hive スタイルのパーティショニング​

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

例

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

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 ヘッダーを渡す必要があります。これは、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 行がセットされました。経過時間: 3.089 秒。処理された行: 11.09 億行、0.00 B (353.55 万行/s、0.00 B/s)
最大メモリ使用量: 192.27 KiB。

ストレージ設定​

  • s3_truncate_on_insert - 挿入前にファイルをトランケートすることを許可します。デフォルトでは無効です。
  • s3_create_new_file_on_insert - フォーマットにサフィックスがある場合、各挿入時に新しいファイルを作成することを許可します。デフォルトでは無効です。
  • s3_skip_empty_files - 読み取り中に空のファイルをスキップすることを許可します。デフォルトでは有効です。