função de tabela S3

Fornece uma interface semelhante a uma tabela para selecionar/inserir arquivos no Amazon S3 e no Google Cloud Storage. Esta função de tabela é semelhante à função hdfs, mas oferece funcionalidades específicas do S3.

Se você tiver várias réplicas no seu cluster, poderá usar a função s3Cluster para paralelizar as inserções.

Ao usar a s3 table function com INSERT INTO...SELECT, os dados são lidos e inseridos em streaming. Apenas alguns blocos de dados permanecem na memória enquanto os blocos são lidos continuamente do S3 e enviados para a tabela de destino.

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

Parâmetros

A função de tabela s3 aceita os seguintes parâmetros simples:

Os argumentos também podem ser passados usando coleções nomeadas. Nesse caso, url, access_key_id, secret_access_key, format, structure, compression_method funcionam da mesma forma, e alguns parâmetros extras são compatíveis:

Uma tabela com a estrutura especificada para leitura ou gravação de dados no arquivo especificado.

Selecionando as 5 primeiras linhas da tabela a partir do arquivo S3 https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv:

SELECT *
FROM s3(
   'https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv',
   NOSIGN,
   '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 │
└────────────┴─────────┴─────────┴─────────┴─────────┴──────────┴─────────┘

Suponha que temos vários arquivos com os seguintes URIs no S3:

• 'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/some_prefix/some_file_1.csv'
• 'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/some_prefix/some_file_2.csv'
• 'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/some_prefix/some_file_3.csv'
• 'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/some_prefix/some_file_4.csv'
• 'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/another_prefix/some_file_1.csv'
• 'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/another_prefix/some_file_2.csv'
• 'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/another_prefix/some_file_3.csv'
• 'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/another_prefix/some_file_4.csv'

Conte o número de linhas em arquivos cujos nomes terminam com números de 1 a 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', NOSIGN, 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')

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

Conte o número total de linhas em todos os arquivos nestes dois diretórios:

SELECT count(*)
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/my-test-bucket-768/{some,another}_prefix/*', NOSIGN, 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')

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

Conte o número total de linhas nos arquivos chamados file-1.csv, …, file-4.csv:

SELECT count(*)
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/my-test-bucket-768/big_prefix/file-{1..4}.csv', NOSIGN, 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32');

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

Insira dados no arquivo 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);

Insira dados no arquivo test-data.csv.gz a partir de uma tabela existente:

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;

O Glob ** pode ser usado para percorrer diretórios recursivamente. Considere o exemplo abaixo: ele buscará todos os arquivos do diretório my-test-bucket-768 de forma recursiva:

SELECT * FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/**', NOSIGN, 'CSV', 'name String, value UInt32', 'gzip');

O exemplo abaixo obtém dados de todos os arquivos test-data.csv.gz em qualquer pasta dentro do diretório my-test-bucket, de forma recursiva:

SELECT * FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/**/test-data.csv.gz', NOSIGN, 'CSV', 'name String, value UInt32', 'gzip');

Nota. É possível especificar mapeadores de URL personalizados no arquivo de configuração do servidor. Exemplo:

SELECT * FROM s3('s3://clickhouse-public-datasets/my-test-bucket-768/**/test-data.csv.gz', NOSIGN, 'CSV', 'name String, value UInt32', 'gzip');

A URL 's3://clickhouse-public-datasets/my-test-bucket-768/**/test-data.csv.gz' seria substituída por 'http://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/**/test-data.csv.gz'

É possível adicionar um mapeador personalizado no 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>

Para casos de uso em produção, recomenda-se usar coleções nomeadas. Veja o exemplo:

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

Compatível apenas com consultas INSERT.

wildcard: substitui o curinga {_partition_id} no caminho do arquivo pela chave de partição correspondente. É selecionada por padrão quando o caminho contém {_partition_id}.

Quando nenhum partition_strategy é definido, um caminho com outro glob não usa estratégia de particionamento e ignora PARTITION BY. Um caminho sem glob usa hive quando file_like_engine_default_partition_strategy é hive; caso contrário, não usa estratégia de particionamento.

hive implementa o particionamento no estilo Hive para leituras & gravações. Gera arquivos usando o seguinte formato: <prefix>/<key1=val1/key2=val2...>/<snowflakeid>.<toLower(file_format)>.

Exemplo da estratégia de particionamento 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 │
   └────────────────────────────────────────────────────────────────────────────┴────┴─────────┴──────┘

Exemplos da estratégia de particionamento wildcard

1. Usar o ID da partição em uma chave gera arquivos separados:

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

Como resultado, os dados são gravados em três arquivos: file_x.csv, file_y.csv e file_z.csv.

2. Ao usar o ID da partição no nome do bucket, os arquivos são criados em buckets diferentes:

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

Como resultado, os dados são gravados em três arquivos em buckets diferentes: my_bucket_1/file.csv, my_bucket_10/file.csv e my_bucket_20/file.csv.

O ClickHouse tenta buscar credenciais de vários tipos diferentes de fontes. Às vezes, isso pode causar problemas ao acessar alguns buckets públicos, fazendo com que o cliente retorne o código de erro 403. Esse problema pode ser evitado usando a palavra-chave NOSIGN, forçando o cliente a ignorar todas as credenciais e a não assinar as requisições.

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

Para buckets não públicos, é possível informar aws_access_key_id e aws_secret_access_key à função. Por exemplo:

SELECT count() FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/mta/*.tsv', '<KEY>', '<SECRET>','TSVWithNames')

Isso é apropriado para acessos pontuais ou em casos em que as credenciais possam ser facilmente rotacionadas. No entanto, isso não é recomendado como solução de longo prazo para acessos recorrentes ou quando as credenciais são sensíveis. Nesse caso, recomendamos que os usuários utilizem controle de acesso baseado em função.

O controle de acesso baseado em função para S3 no ClickHouse Cloud está documentado aqui.

Depois de configurado, um roleARN pode ser passado para a função s3 por meio do parâmetro extra_credentials. Por exemplo:

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

Um external_id opcional também pode ser informado junto com role_arn. Ele é passado como o parâmetro ExternalId na chamada AssumeRole do AWS STS e permite que a política de confiança da role exija um segredo compartilhado, o que mitiga o problema do confused deputy. Por exemplo:

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', external_id = 'my-external-id'))

Outros exemplos podem ser encontrados aqui

Suponha que temos vários arquivos compactados com os seguintes URIs no S3:

• 'https://s3-us-west-1.amazonaws.com/umbrella-static/top-1m-2018-01-10.csv.zip'
• 'https://s3-us-west-1.amazonaws.com/umbrella-static/top-1m-2018-01-11.csv.zip'
• 'https://s3-us-west-1.amazonaws.com/umbrella-static/top-1m-2018-01-12.csv.zip'

É possível extrair dados desses arquivos compactados usando ::. É possível usar globs tanto na parte da URL quanto na parte após :: (responsável pelo nome de um arquivo dentro do arquivo compactado).

SELECT *
FROM s3(
   'https://s3-us-west-1.amazonaws.com/umbrella-static/top-1m-2018-01-1{0..2}.csv.zip :: *.csv',
   NOSIGN
);

Observe que as linhas só podem ser inseridas em arquivos novos. Não há ciclos de merge nem operações de divisão de arquivos. Depois que um arquivo é gravado, inserções subsequentes falharão. Veja mais detalhes aqui.

• _path — Caminho para o arquivo. Tipo: LowCardinality(String). Em caso de arquivo compactado, mostra o caminho no formato: "{path_to_archive}::{path_to_file_inside_archive}"
• _file — Nome do arquivo. Tipo: LowCardinality(String). Em caso de arquivo compactado, mostra o nome do arquivo dentro do arquivo compactado.
• _size — Tamanho do arquivo em bytes. Tipo: Nullable(UInt64). Se o tamanho do arquivo for desconhecido, o valor será NULL. Em caso de arquivo compactado, mostra o tamanho descompactado do arquivo dentro do arquivo compactado.
• _time — Data e hora da última modificação do arquivo. Tipo: Nullable(DateTime). Se a data e hora forem desconhecidas, o valor será NULL.

Esta é uma indicação para o ClickHouse interpretar arquivos particionados no estilo Hive durante a leitura. Isso não tem efeito na escrita. Para manter simetria entre leitura e escrita, use o argumento partition_strategy.

Quando a configuração use_hive_partitioning é definida como 1, o ClickHouse detecta o particionamento no estilo Hive no caminho (/name=value/) e permite usar colunas de partição como colunas virtuais na consulta. Essas colunas virtuais terão os mesmos nomes do caminho particionado.

Exemplo

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

Para acessar um bucket requester-pays, é preciso enviar o header x-amz-request-payer = requester em qualquer solicitação. Isso é feito passando o parâmetro headers('x-amz-request-payer' = 'requester') para a função s3. Por exemplo:

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

┌

A configuração s3_base permite fornecer uma URL relativa à função s3. Quando s3_base está definido e o argumento da função não inclui um esquema, ele é resolvido em relação à URL base, de acordo com a RFC 3986, usando as mesmas regras da configuração url_base da função url. URLs absolutas são transmitidas sem alteração.

A configuração também se aplica ao mecanismo de tabela S3 e às funções de tabela que compartilham a configuração s3 (s3Cluster, gcs, oss). Para o mecanismo de tabela S3, a URL resolvida é materializada na definição da tabela armazenada, de modo que a tabela não depende do valor de s3_base após sua criação.

Exemplo

SET s3_base = 's3://clickhouse-public-datasets/';
SELECT count() FROM s3('hits_compatible/hits.csv', NOSIGN);

• s3_truncate_on_insert - permite truncar o arquivo antes de inserir dados nele. Desativado por padrão.
• s3_create_new_file_on_insert - permite criar um novo arquivo a cada inserção se o formato tiver um sufixo. Desativado por padrão.
• s3_skip_empty_files - permite ignorar arquivos vazios durante a leitura. Ativado por padrão.
• s3_base - URL base para resolver URLs relativas passadas à função s3. Vazio (desativado) por padrão.

Ao ler arquivos Avro que contêm registros aninhados com diferenças entre si (por exemplo, alguns arquivos têm um campo extra dentro de um objeto aninhado), o ClickHouse pode retornar um erro como:

The number of leaves in record doesn't match the number of elements in tuple…

Isso acontece porque o ClickHouse espera que todas as estruturas de registros aninhados sigam o mesmo esquema. Para lidar com esse cenário, você pode:

• Usar schema_inference_mode='union' para mesclar diferentes esquemas de registros aninhados, ou
• Alinhar manualmente suas estruturas aninhadas e habilitar use_structure_from_insertion_table_in_table_functions=1.

Exemplo

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

• S3 engine
• Integrating S3 with ClickHouse