gcs

Provides a table-like interface to SELECT and INSERT data from Google Cloud Storage. Requires the Storage Object User IAM role.

This is an alias of the s3 table function.

If you have multiple replicas in your cluster, you can use the s3Cluster function (which works with GCS) instead to parallelize inserts.

Syntax

gcs(url [, NOSIGN | hmac_key, hmac_secret] [,format] [,structure] [,compression_method] [,partition_strategy])
gcs(named_collection[, option=value [,..]])

Arguments

Argument	Description
`url`	Bucket path to file. Supports following wildcards in readonly mode: ``, `*`, `?`, `{abc,def}` and `{N..M}` where `N`, `M` — numbers, `'abc'`, `'def'` — strings.
`NOSIGN`	If this keyword is provided in place of credentials, all the requests will not be signed.
`hmac_key` and `hmac_secret`	Keys that specify credentials to use with given endpoint. Optional.
`format`	The format of the file.
`structure`	Structure of the table. Format `'column1_name column1_type, column2_name column2_type, ...'`.
`compression_method`	Parameter is optional. Supported values: `none`, `gzip` or `gz`, `deflate`, `brotli` or `br`, `xz` or `LZMA`, `zstd` or `zst`, `lz4`, `bz2`, `snappy`. By default, it will autodetect compression method by file extension. For `snappy`, the wire format is selected by the snappy_mode setting (`basic` by default).
`partition_strategy`	Optional. Supported values: `wildcard` or `hive`. `wildcard` requires `{_partition_id}` in the path. Without an explicit strategy, a path with `{_partition_id}` uses `wildcard`. A path with another glob uses no partition strategy and ignores `PARTITION BY`. A path without a glob uses `hive` when `file_like_engine_default_partition_strategy` is `hive`; otherwise it uses no partition strategy.

Info - GCS

The GCS path is in this format as the endpoint for the Google XML API is different than the JSON API:

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

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

Arguments can also be passed using named collections. In this case url, format, structure, compression_method, partition_strategy work in the same way, and some extra parameters are supported:

Parameter	Description
`access_key_id`	`hmac_key`, optional.
`secret_access_key`	`hmac_secret`, optional.
`filename`	Appended to the url if specified.
`use_environment_credentials`	Enabled by default, allows passing extra parameters using environment variables `AWS_CONTAINER_CREDENTIALS_RELATIVE_URI`, `AWS_CONTAINER_CREDENTIALS_FULL_URI`, `AWS_CONTAINER_AUTHORIZATION_TOKEN`, `AWS_EC2_METADATA_DISABLED`.
`no_sign_request`	Disabled by default.
`expiration_window_seconds`	Default value is 120.

Returned value

A table with the specified structure for reading or writing data in the specified file.

Examples

Selecting the first two rows from the GCS file https://storage.googleapis.com/clickhouse_public_datasets/my-test-bucket-768/data.csv.gz. The compression method is detected automatically from the .gz file extension:

SELECT *
FROM gcs('https://storage.googleapis.com/clickhouse_public_datasets/my-test-bucket-768/data.csv.gz', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')
LIMIT 2;

┌─column1─┬─column2─┬─column3─┐
│       1 │       2 │       3 │
│       3 │       2 │       1 │
└─────────┴─────────┴─────────┘

The same query as above, but with the gzip compression method specified explicitly instead of relying on autodetection:

SELECT *
FROM gcs('https://storage.googleapis.com/clickhouse_public_datasets/my-test-bucket-768/data.csv.gz', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32', 'gzip')
LIMIT 2;

┌─column1─┬─column2─┬─column3─┐
│       1 │       2 │       3 │
│       3 │       2 │       1 │
└─────────┴─────────┴─────────┘

Usage

Suppose that we have several files with following URIs on GCS:

Count the amount of rows in files ending with numbers from 1 to 3:

SELECT count(*)
FROM gcs('https://storage.googleapis.com/clickhouse_public_datasets/my-test-bucket-768/{some,another}_prefix/some_file_{1..3}.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')

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

Count the total amount of rows in all files in these two directories:

SELECT count(*)
FROM gcs('https://storage.googleapis.com/clickhouse_public_datasets/my-test-bucket-768/{some,another}_prefix/*', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')

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

Count the total amount of rows in files named file-000.csv, file-001.csv, … , file-999.csv:

SELECT count(*)
FROM gcs('https://storage.googleapis.com/clickhouse_public_datasets/my-test-bucket-768/big_prefix/file-{000..999}.csv', 'CSV', 'name String, value UInt32');

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

Insert data into file test-data.csv.gz:

INSERT INTO FUNCTION gcs('https://storage.googleapis.com/my-test-bucket-768/test-data.csv.gz', 'CSV', 'name String, value UInt32', 'gzip')
VALUES ('test-data', 1), ('test-data-2', 2);

Insert data into file test-data.csv.gz from existing table:

INSERT INTO FUNCTION gcs('https://storage.googleapis.com/my-test-bucket-768/test-data.csv.gz', 'CSV', 'name String, value UInt32', 'gzip')
SELECT name, value FROM existing_table;

Glob ** can be used for recursive directory traversal. Consider the below example, it will fetch all files from my-test-bucket-768 directory recursively:

SELECT * FROM gcs('https://storage.googleapis.com/my-test-bucket-768/**', 'CSV', 'name String, value UInt32', 'gzip');

The below get data from all test-data.csv.gz files from any folder inside my-test-bucket directory recursively:

SELECT * FROM gcs('https://storage.googleapis.com/my-test-bucket-768/**/test-data.csv.gz', 'CSV', 'name String, value UInt32', 'gzip');

For production use cases it is recommended to use named collections. Here is the example:


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

Partitioned Write

If you specify PARTITION BY expression when inserting data into GCS table, a separate file is created for each partition value. Splitting the data into separate files helps to improve reading operations efficiency.

A path containing {_partition_id} implies the wildcard partition strategy, so the explicit partition_strategy='wildcard' in the examples below is optional. A path with another glob uses no partition strategy and ignores PARTITION BY. A path without a glob uses hive when file_like_engine_default_partition_strategy is hive; otherwise it uses no partition strategy.

Examples

Using partition ID in a key creates separate files:

INSERT INTO TABLE FUNCTION
    gcs('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);

As a result, the data is written into three files: file_x.csv, file_y.csv, and file_z.csv.

Using partition ID in a bucket name creates files in different buckets:

INSERT INTO TABLE FUNCTION
    gcs('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);

As a result, the data is written into three files in different buckets: my_bucket_1/file.csv, my_bucket_10/file.csv, and my_bucket_20/file.csv.