Fonction de table S3 - ClickHouse Documentation

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.

Syntaxe

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

GCSLa fonction de table S3 s’intègre à Google Cloud Storage via l’API XML GCS et des clés HMAC. Consultez la documentation sur l’interopérabilité de Google pour plus de détails sur l’endpoint et le HMAC.Pour GCS, remplacez votre clé HMAC et votre secret HMAC là où apparaissent access_key_id et secret_access_key.

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

Paramètre	Description
`url`	URL du bucket avec le chemin vers le fichier. Prend en charge les caractères génériques suivants en mode `readonly` : ``, `*`, `?`, `{abc,def}` et `{N..M}`, où `N`, `M` sont des nombres et `'abc'`, `'def'` des chaînes. Pour plus d’informations, voir ici.
`NOSIGN`	Si ce mot-clé est fourni à la place des informations d’identification, aucune des requêtes ne sera signée.
`access_key_id` et `secret_access_key`	Clés qui spécifient les informations d’identification à utiliser avec l’endpoint indiqué. Facultatif.
`session_token`	Jeton de session à utiliser avec les clés fournies. Facultatif lorsque des clés sont transmises.
`format`	Le format du fichier.
`structure`	Structure de la table. Format : `'column1_name column1_type, column2_name column2_type, ...'`.
`compression_method`	Le paramètre est facultatif. Valeurs prises en charge : `none`, `gzip` ou `gz`, `brotli` ou `br`, `xz` ou `LZMA`, `zstd` ou `zst`. Par défaut, la méthode de compression est détectée automatiquement à partir de l’extension du fichier.
`headers`	Le paramètre est facultatif. Permet de transmettre des en-têtes dans la requête S3. Utilisez le format `headers(key=value)`, par exemple `headers('x-amz-request-payer' = 'requester')`.
`partition_strategy`	Le paramètre est facultatif. Valeurs prises en charge : `wildcard` ou `hive`. `wildcard` nécessite un `{_partition_id}` dans le chemin, qui est remplacé par la clé de partition. `hive` n’autorise pas les caractères génériques, suppose que le chemin est la racine de la table et génère des répertoires partitionnés au format Hive, avec des Snowflake IDs comme noms de fichiers et le format de fichier comme extension. La valeur par défaut est le paramètre `file_like_engine_default_partition_strategy` (`wildcard` dans les paramètres `compatibility` antérieurs à `26.6`, `hive` sinon).
`partition_columns_in_data_file`	Le paramètre est facultatif. Utilisé uniquement avec la stratégie de partition `hive`. Indique à ClickHouse s’il doit s’attendre à ce que les colonnes de partition soient écrites dans le fichier de données. Valeur par défaut : `false`.
`extra_credentials`	Le paramètre est facultatif. Utilisé pour transmettre un `role_arn` pour l’accès basé sur les rôles dans ClickHouse Cloud. Consultez Secure S3 pour les étapes de configuration.
`storage_class_name`	Le paramètre est facultatif. Valeurs prises en charge : `STANDARD` ou `INTELLIGENT_TIERING`. Permet de spécifier AWS S3 Intelligent Tiering. La valeur par défaut est `STANDARD`.

GCSL’URL GCS se présente sous ce format, car l’endpoint de l’API XML de Google est différent de celui de l’API JSON :

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

et non ~~https://storage.cloud.google.com~~.

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

Argument	Description
`filename`	ajouté à l’URL s’il est spécifié.
`use_environment_credentials`	activé par défaut, permet de transmettre des paramètres supplémentaires à l’aide des variables d’environnement `AWS_CONTAINER_CREDENTIALS_RELATIVE_URI`, `AWS_CONTAINER_CREDENTIALS_FULL_URI`, `AWS_CONTAINER_AUTHORIZATION_TOKEN`, `AWS_EC2_METADATA_DISABLED`.
`no_sign_request`	désactivé par défaut.
`expiration_window_seconds`	la valeur par défaut est de 120.

Valeur renvoyée

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

Exemples

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',
   '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 utilise les extensions de fichier pour déterminer le format des données. Par exemple, nous aurions pu exécuter la commande précédente sans CSVWithNames :

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

ClickHouse peut également déterminer la méthode de compression d’un fichier. Par exemple, si le fichier était compressé avec une extension .csv.gz, ClickHouse le décompresserait automatiquement.

Les fichiers Parquet dont le nom ressemble à *.parquet.snappy ou *.parquet.zstd peuvent induire ClickHouse en erreur et provoquer des erreurs TOO_LARGE_COMPRESSED_BLOCK ou ZSTD_DECODER_FAILED. En effet, ClickHouse essaierait de lire l’intégralité du fichier comme des données encodées en Snappy ou en ZSTD, alors qu’en réalité, Parquet applique la compression au niveau des groupes de lignes et des colonnes.Les métadonnées Parquet indiquent déjà la compression utilisée pour chaque colonne, l’extension du fichier est donc superflue. Dans ce cas, vous pouvez simplement utiliser compression_method = 'none' :

SELECT *
FROM s3(
  'https://<my-bucket>.s3.<my-region>.amazonaws.com/path/to/my-data.parquet.snappy',
  compression_format = 'none'
);

Utilisation

Supposons que nous ayons plusieurs fichiers avec les URI suivants sur S3 :

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', '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/*', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')

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

Si votre liste de fichiers contient des plages de nombres avec des zéros non significatifs, utilisez la notation avec des accolades pour chaque chiffre séparément, ou utilisez ?.

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

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/**', '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', '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', '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')

Écriture partitionnée

Stratégie de partitionnement

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. Sélectionnée par défaut uniquement avec des paramètres compatibility antérieurs à 26.6 ; sinon, la valeur par défaut est hive (voir le paramètre file_like_engine_default_partition_strategy). 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

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 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.

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 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.

Accès aux buckets publics

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;

Utilisation des identifiants S3 (ClickHouse Cloud)

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

Utilisation des archives

Supposons que nous ayons plusieurs fichiers d’archive avec les URI suivantes sur S3 :

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

ClickHouse prend en charge trois formats d’archive : ZIP TAR 7Z Si les archives ZIP et TAR sont accessibles à partir de n’importe quel emplacement de stockage pris en charge, les archives 7Z ne peuvent être lues que depuis le système de fichiers local sur lequel ClickHouse est installé.

Insertion de données

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.

Colonnes virtuelles

_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.

paramètre use_hive_partitioning

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;

Accéder aux buckets requester-pays

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

┌───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.

Paramètres de stockage

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.

Schémas Avro imbriqués

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.

Remarque sur les performancesschema_inference_mode='union' peut prendre plus de temps sur de très grands jeux de données S3, car il doit analyser chaque fichier pour inférer le schéma.

Exemple

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

## Related           

- [S3 engine](/reference/engines/table-engines/integrations/s3)
- [Integrating S3 with ClickHouse](/integrations/connectors/data-ingestion/AWS/integrating-s3-with-clickhouse)

​Syntaxe

​Valeur renvoyée

​Exemples

​Utilisation

​Écriture partitionnée

​Stratégie de partitionnement

​Accès aux buckets publics

​Utilisation des identifiants S3 (ClickHouse Cloud)

​Utilisation des archives

​Insertion de données

​Colonnes virtuelles

​paramètre use_hive_partitioning

​Accéder aux buckets requester-pays

​Paramètres de stockage

​Schémas Avro imbriqués

Syntaxe

Valeur renvoyée

Exemples

Utilisation

Écriture partitionnée

Stratégie de partitionnement

Accès aux buckets publics

Utilisation des identifiants S3 (ClickHouse Cloud)

Utilisation des archives

Insertion de données

Colonnes virtuelles

paramètre use_hive_partitioning

Accéder aux buckets requester-pays

Paramètres de stockage

Schémas Avro imbriqués