Fonction de table S3

Fournit une interface de type table pour sélectionner/insérer des fichiers dans Amazon S3 et Google Cloud Storage. Cette fonction de table est semblable à la fonction hdfs, mais offre des fonctionnalités spécifiques à S3.

Si votre cluster comporte plusieurs répliques, vous pouvez utiliser la fonction s3Cluster à la place pour paralléliser les insertions.

Lors de l'utilisation de la fonction de table S3 avec INSERT INTO...SELECT, les données sont lues et insérées en flux continu. Seuls quelques blocs de données restent en mémoire pendant que les blocs sont lus en continu depuis S3 et envoyés vers la table de destination.

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

Paramètres

La fonction de table s3 prend en charge les paramètres simples suivants :

Les arguments peuvent également être transmis à l’aide de collections nommées. Dans ce cas, url, access_key_id, secret_access_key, format, structure, compression_method fonctionnent de la même manière, et certains paramètres supplémentaires sont pris en charge :

Une table de la structure spécifiée pour lire ou écrire des données dans le fichier spécifié.

Sélection des 5 premières lignes de la table issue du fichier 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 │
└────────────┴─────────┴─────────┴─────────┴─────────┴──────────┴─────────┘

Supposons que nous ayons plusieurs fichiers avec les URI suivants sur 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'

Compter le nombre de lignes dans les fichiers dont le nom se termine par un chiffre de 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', NOSIGN, 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')

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

Compter le nombre total de lignes dans tous les fichiers de ces deux répertoires :

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

Compter le nombre total de lignes dans les fichiers nommés 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 │
└─────────┘

Insérez des données dans le fichier 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);

Insérez des données dans le fichier test-data.csv.gz à partir d'une table existante :

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;

Le glob ** peut être utilisé pour parcourir les répertoires de manière récursive. Prenons l'exemple ci-dessous : il récupère tous les fichiers du répertoire my-test-bucket-768 de façon récursive :

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

Ce qui suit récupère les données de tous les fichiers test-data.csv.gz présents dans n'importe quel dossier du répertoire my-test-bucket, de façon récursive :

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

Note. Il est possible de spécifier des mappeurs d'URL personnalisés dans le fichier de configuration du serveur. Exemple :

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

L’URL 's3://clickhouse-public-datasets/my-test-bucket-768/**/test-data.csv.gz' serait remplacée par 'http://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/**/test-data.csv.gz'

Un mappeur personnalisé peut être ajouté au fichier 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>

Pour les cas d’utilisation en environnement de production, il est recommandé d’utiliser les collections nommées. Voici un exemple :

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

Pris en charge uniquement pour les requêtes INSERT.

wildcard : remplace le caractère générique {_partition_id} dans le chemin du fichier par la clé de partition réelle. Elle est sélectionnée par défaut lorsque le chemin contient {_partition_id}.

Lorsqu’aucune partition_strategy n’est définie, un chemin contenant un autre glob n’utilise aucune stratégie de partitionnement et ignore PARTITION BY. Un chemin sans glob utilise hive lorsque file_like_engine_default_partition_strategy est défini sur hive ; sinon, il n’utilise aucune stratégie de partitionnement.

hive implémente le partitionnement de style Hive pour les lectures et les écritures. Il génère des fichiers au format suivant : <prefix>/<key1=val1/key2=val2...>/<snowflakeid>.<toLower(file_format)>.

Exemple de stratégie de partitionnement 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 │
   └────────────────────────────────────────────────────────────────────────────┴────┴─────────┴──────┘

Exemples de la stratégie de partitionnement wildcard

1. L’utilisation de l’ID de partition dans une clé crée des fichiers distincts :

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

Par conséquent, les données sont écrites dans trois fichiers : file_x.csv, file_y.csv et file_z.csv.

2. L'utilisation de l'ID de partition dans le nom d'un bucket crée des fichiers dans différents buckets :

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

Par conséquent, les données sont enregistrées dans trois fichiers répartis dans différents buckets : my_bucket_1/file.csv, my_bucket_10/file.csv et my_bucket_20/file.csv.

ClickHouse tente de récupérer des identifiants à partir de nombreux types de sources. Il peut parfois en résulter des problèmes lors de l'accès à certains buckets publics, le client renvoyant alors le code d'erreur 403. Ce problème peut être évité en utilisant le mot-clé NOSIGN, qui force le client à ignorer tous les identifiants et à ne pas signer les requêtes.

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

Pour les buckets non publics, les utilisateurs peuvent fournir un aws_access_key_id et un aws_secret_access_key à la fonction. Par exemple :

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

Cela convient pour des accès ponctuels ou lorsque les identifiants peuvent être facilement renouvelés. Cependant, cette approche n’est pas recommandée comme solution à long terme pour des accès répétés ou lorsque les identifiants sont sensibles. Dans ce cas, nous recommandons aux utilisateurs de recourir à un contrôle d’accès basé sur les rôles.

Le contrôle d’accès basé sur les rôles pour S3 dans ClickHouse Cloud est documenté ici.

Une fois configuré, un roleARN peut être transmis à la fonction s3 via un paramètre extra_credentials. Par exemple :

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

Un external_id facultatif peut également être fourni avec role_arn. Il est transmis en tant que paramètre ExternalId dans l’appel AWS STS AssumeRole et permet à la stratégie d’approbation du rôle d’exiger un secret partagé, ce qui atténue le problème du deputy confus. Par exemple :

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

Vous trouverez d’autres exemples ici

Supposons que nous ayons plusieurs fichiers d’archive avec les URI suivantes sur 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'

Il est possible d’extraire des données de ces archives à l’aide de ::. Des globs peuvent être utilisés à la fois dans la partie URL et dans la partie située après :: (qui correspond au nom d’un fichier à l’intérieur de l’archive).

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

Notez que les lignes ne peuvent être insérées que dans de nouveaux fichiers. Il n’y a ni cycles de fusion ni opérations de fractionnement de fichiers. Une fois qu’un fichier a été écrit, les insertions suivantes échoueront. Pour plus de détails, voir ici.

• _path — Chemin du fichier. Type : LowCardinality(String). Dans le cas d'une archive, affiche le chemin au format : "{path_to_archive}::{path_to_file_inside_archive}"
• _file — Nom du fichier. Type : LowCardinality(String). Dans le cas d'une archive, affiche le nom du fichier contenu dans l'archive.
• _size — Taille du fichier en octets. Type : Nullable(UInt64). Si la taille du fichier est inconnue, la valeur est NULL. Dans le cas d'une archive, affiche la taille non compressée du fichier contenu dans l'archive.
• _time — Date et heure de la dernière modification du fichier. Type : Nullable(DateTime). Si cette information est inconnue, la valeur est NULL.

Il s’agit d’une indication permettant à ClickHouse d’analyser les fichiers partitionnés au format Hive lors de la lecture. Cela n’a aucun effet sur l’écriture. Pour des lectures et écritures symétriques, utilisez l’argument partition_strategy.

Lorsque le paramètre use_hive_partitioning est défini sur 1, ClickHouse détecte le partitionnement au format Hive dans le chemin (/name=value/) et permet d’utiliser les colonnes de partition comme colonnes virtuelles dans la requête. Ces colonnes virtuelles porteront les mêmes noms que dans le chemin partitionné.

Exemple

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

Pour accéder à un bucket requester-pays, l’en-tête x-amz-request-payer = requester doit être transmis avec toutes les requêtes. Pour ce faire, passez le paramètre headers('x-amz-request-payer' = 'requester') à la fonction s3. Par exemple :

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

┌

Le paramètre s3_base permet de transmettre une URL relative à la fonction s3. Lorsque s3_base est défini et que l’argument de la fonction ne comporte aucun schéma, l’URL est résolue par rapport à l’URL de base conformément à la RFC 3986, selon les mêmes règles que le paramètre url_base de la fonction url. Les URL absolues sont transmises sans modification.

Le paramètre s’applique également au moteur de table S3 et aux fonctions de table partageant la configuration s3 (s3Cluster, gcs, oss). Pour le moteur de table S3, l’URL résolue est matérialisée dans la définition de table stockée, de sorte que la table ne dépend plus de la valeur de s3_base après sa création.

Exemple

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

• s3_truncate_on_insert - permet de tronquer le fichier avant d’y insérer des données. Désactivé par défaut.
• s3_create_new_file_on_insert - permet de créer un nouveau fichier à chaque insertion si le format comporte un suffixe. Désactivé par défaut.
• s3_skip_empty_files - permet d’ignorer les fichiers vides lors de la lecture. Activé par défaut.

• s3_base - URL de base utilisée pour résoudre les URL relatives transmises à la fonction s3. Vide (désactivé) par défaut.

Lors de la lecture de fichiers Avro contenant des enregistrements imbriqués dont la structure varie d’un fichier à l’autre (par exemple, si certains fichiers contiennent un champ supplémentaire dans un objet imbriqué), ClickHouse peut renvoyer une erreur du type :

Le nombre de feuilles dans l’enregistrement ne correspond pas au nombre d'éléments du tuple…

Cela se produit parce que ClickHouse s’attend à ce que toutes les structures d’enregistrements imbriqués respectent le même schéma. Pour gérer ce cas, vous pouvez :

• Utiliser schema_inference_mode='union' pour fusionner différents schémas d’enregistrements imbriqués, ou
• Aligner manuellement vos structures imbriquées et activer use_structure_from_insertion_table_in_table_functions=1.

Exemple

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