Analyze file data in ApsaraDB for SelectDB - ApsaraDB for SelectDB

TVF Amazon S3

La fonction s3() lit les fichiers depuis n'importe quel système de stockage d'objets compatible S3. Formats pris en charge : CSV, csv_with_names, csv_with_names_and_types, JSON, Parquet et ORC.

Syntaxe

s3(
  "uri"           = "<uri>",
  "s3.access_key" = "<access-key>",
  "s3.secret_key" = "<secret-key>",
  "s3.region"     = "<region>",
  "format"        = "<format>"
  [, "s3.session_token" = "<session-token>"]
  [, "use_path_style"   = "true|false"]
  [, "keyn"             = "valuen" ...]
)

Les paramètres obligatoires sont listés sans crochets. Les paramètres facultatifs sont encadrés par [...].

Paramètres

Chaque paramètre est une paire clé-valeur au format "key" = "value".

Paramètres obligatoires

Paramètre	Description
`uri`	URI permettant d'accéder à Amazon S3. Schémas pris en charge : `http://`, `https://` et `s3://`. Si aucun fichier ne correspond à l'URI ou si tous les fichiers correspondants sont vides, la TVF renvoie un ensemble de résultats vide.
`s3.access_key`	ID de la clé d'accès.
`s3.secret_key`	Clé d'accès secrète.
`s3.region`	Région Amazon S3. Valeur par défaut : `us-east-1`.
`format`	Format du fichier. Valeurs valides : `csv`, `csv_with_names`, `csv_with_names_and_types`, `json`, `parquet`, `orc`.

Paramètres facultatifs

Paramètre	Valeur par défaut	Description
`s3.session_token`	—	Jeton de session temporaire. Requis lorsque l'authentification par session temporaire est activée.
`use_path_style`	`false`	Détermine s'il faut utiliser le style de chemin plutôt que le style d'hôte virtuel. Définissez cette valeur sur `true` pour les systèmes de stockage qui ne prennent pas en charge le style d'hôte virtuel (par exemple, MinIO). Remarque Si l'URI utilise le schéma `s3://`, le style d'hôte virtuel est toujours utilisé, quel que soit ce paramètre.
`column_separator`	`,`	Délimiteur de colonnes.
`line_delimiter`	`\n`	Délimiteur de lignes.
`compress_type`	`unknown`	Type de compression. La valeur `unknown` permet de déduire automatiquement le type à partir du suffixe de l'URI. Autres valeurs valides : `plain`, `gz`, `lzo`, `bz2`, `lz4frame`, `deflate`.
`read_json_by_line`	`true`	Lit les données JSON ligne par ligne.
`num_as_string`	`false`	Traite les valeurs numériques comme des chaînes de caractères.
`fuzzy_parse`	`false`	Accélère les performances d'importation JSON.
`jsonpaths`	—	Champs à extraire des données JSON. Format : `jsonpaths: ["$.k2", "$.k1"]`.
`strip_outer_array`	`false`	Considère un tableau JSON de premier niveau comme plusieurs lignes, avec un élément par ligne. Format : `strip_outer_array: true`.
`json_root`	(vide)	Nœud racine pour l'analyse JSON. ApsaraDB for SelectDB extrait et analyse uniquement les éléments situés sous ce nœud. Format : `json_root: $.RECORDS`.
`path_partition_keys`	—	Noms de colonnes de clés de partition séparés par des virgules, intégrés dans le chemin du fichier. Par exemple, pour le chemin `/path/to/city=beijing/date=2023-07-09`, définissez cette valeur sur `city,date`. Lors de l'importation des données, ApsaraDB for SelectDB lit les noms et valeurs de colonnes correspondants à partir du chemin.

Exemples

Lecture d'un fichier CSV depuis un système de stockage compatible MinIO

MinIO utilisant le style de chemin par défaut, définissez use_path_style sur true :

SELECT * FROM s3(
    "uri"           = "http://127.0.0.1:9312/test2/student1.csv",
    "s3.access_key" = "minioadmin",
    "s3.secret_key" = "minioadmin",
    "format"        = "csv",
    "use_path_style"= "true")
ORDER BY c1;

Lecture d'un fichier Parquet depuis Object Storage Service (OSS)

OSS requiert le style d'hôte virtuel. Définissez use_path_style sur false :

SELECT * FROM s3(
    "uri"           = "http://example-bucket.oss-cn-beijing.aliyuncs.com/your-folder/file.parquet",
    "s3.access_key" = "ak",
    "s3.secret_key" = "sk",
    "format"        = "parquet",
    "use_path_style"= "false");

Lecture d'un fichier CSV en style de chemin

Lorsque use_path_style est défini sur true, le nom du bucket fait partie du chemin de l'URI :

SELECT * FROM s3(
    "uri"           = "https://endpoint/bucket/file/student.csv",
    "s3.access_key" = "ak",
    "s3.secret_key" = "sk",
    "format"        = "csv",
    "use_path_style"= "true");

Lecture d'un fichier CSV en style d'hôte virtuel

Lorsque use_path_style est défini sur false, le nom du bucket fait partie du nom d'hôte :

SELECT * FROM s3(
    "uri"           = "https://bucket.endpoint/bucket/file/student.csv",
    "s3.access_key" = "ak",
    "s3.secret_key" = "sk",
    "format"        = "csv",
    "use_path_style"= "false");

TVF HDFS

La fonction hdfs() lit les fichiers depuis HDFS de la même manière que s3() lit depuis un stockage d'objets. Formats pris en charge : CSV, csv_with_names, csv_with_names_and_types, JSON, Parquet et ORC.

Syntaxe

hdfs(
  "uri"              = "<uri>",
  "fs.defaultFS"     = "<hostname:port>",
  "hadoop.username"  = "<username>",
  "format"           = "<format>"
  [, "hadoop.security.authentication" = "Simple|Kerberos"]
  [, "keyn"                           = "valuen" ...]
)

Paramètres

Paramètres obligatoires

Paramètre	Description
`uri`	URI permettant d'accéder à HDFS. Si aucun fichier ne correspond à l'URI ou si tous les fichiers correspondants sont vides, la TVF renvoie un ensemble de résultats vide.
`fs.defaultFS`	Nom d'hôte et port du NameNode HDFS.
`hadoop.username`	Nom d'utilisateur pour l'accès HDFS. Ne peut pas être vide.
`format`	Format du fichier. Valeurs valides : `csv`, `csv_with_names`, `csv_with_names_and_types`, `json`, `parquet`, `orc`.

Paramètres facultatifs

Paramètre	Valeur par défaut	Description
`hadoop.security.authentication`	—	Méthode d'authentification. Valeurs valides : `Simple`, `Kerberos`.
`hadoop.kerberos.principal`	—	Principal Kerberos. Requis lorsque l'authentification Kerberos est activée.
`hadoop.kerberos.keytab`	—	Chemin vers le fichier keytab Kerberos. Requis lorsque l'authentification Kerberos est activée.
`dfs.client.read.shortcircuit`	—	Active les lectures en court-circuit pour les données HDFS locales (BOOLEAN).
`dfs.domain.socket.path`	—	Chemin du socket de domaine UNIX pour la communication entre le DataNode et le client. La chaîne `_PORT` dans le chemin est remplacée par le port TCP du DataNode.
`dfs.nameservices`	—	Noms logiques des nameservices. Correspond à `dfs.nameservices` dans `core-site.xml`.
`dfs.ha.namenodes.<nameservice>`	—	Noms logiques des NameNodes. Requis pour les déploiements Hadoop en haute disponibilité (HA).
`dfs.namenode.rpc-address.<nameservice>.<namenode>`	—	URL HTTP sur laquelle le NameNode écoute. Requis pour les déploiements Hadoop HA.
`dfs.client.failover.proxy.provider.<nameservice>`	—	Classe d'implémentation du fournisseur de proxy de basculement pour les connexions client HA. Requis pour les déploiements Hadoop HA.
`read_json_by_line`	`true`	Lit les données JSON ligne par ligne.
`num_as_string`	`false`	Traite les valeurs numériques comme des chaînes de caractères.
`fuzzy_parse`	`false`	Accélère les performances d'importation JSON.
`jsonpaths`	—	Champs à extraire des données JSON. Format : `jsonpaths: ["$.k2", "$.k1"]`.
`strip_outer_array`	`false`	Considère un tableau JSON de premier niveau comme plusieurs lignes, avec un élément par ligne. Format : `strip_outer_array: true`.
`json_root`	(vide)	Nœud racine pour l'analyse JSON. Format : `json_root: $.RECORDS`.
`trim_double_quotes`	`false`	Supprime les guillemets doubles les plus externes de chaque champ dans les fichiers CSV.
`skip_lines`	`0`	Nombre de lignes initiales à ignorer dans les fichiers CSV. Plage : `[0–Integer.MaxValue]`. Sans effet lorsque `format` est `csv_with_names` ou `csv_with_names_and_types`.
`path_partition_keys`	—	Noms de colonnes de clés de partition séparés par des virgules, intégrés dans le chemin du fichier. Comportement identique à celui de la TVF S3.

Exemples

Lecture d'un fichier CSV depuis HDFS

SELECT * FROM hdfs(
    "uri"              = "hdfs://127.0.0.1:842/user/doris/csv_format_test/student.csv",
    "fs.defaultFS"     = "hdfs://127.0.0.1:8424",
    "hadoop.username"  = "doris",
    "format"           = "csv");

-- Sample response
+------+---------+------+
| c1   | c2      | c3   |
+------+---------+------+
| 1    | alice   | 18   |
| 2    | bob     | 20   |
| 3    | jack    | 24   |
| 4    | jackson | 19   |
| 5    | liming  | 18   |
+------+---------+------+

Lecture d'un fichier CSV depuis HDFS en mode HA

Pour les déploiements Hadoop HA, ajoutez les trois paramètres spécifiques à la haute disponibilité :

SELECT * FROM hdfs(
    "uri"                                         = "hdfs://127.0.0.1:842/user/doris/csv_format_test/student.csv",
    "fs.defaultFS"                                = "hdfs://127.0.0.1:8424",
    "hadoop.username"                             = "doris",
    "format"                                      = "csv",
    "dfs.nameservices"                            = "my_hdfs",
    "dfs.ha.namenodes.my_hdfs"                   = "nn1,nn2",
    "dfs.namenode.rpc-address.my_hdfs.nn1"       = "nanmenode01:8020",
    "dfs.namenode.rpc-address.my_hdfs.nn2"       = "nanmenode02:8020",
    "dfs.client.failover.proxy.provider.my_hdfs" = "org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider");

-- Sample response
+------+---------+------+
| c1   | c2      | c3   |
+------+---------+------+
| 1    | alice   | 18   |
| 2    | bob     | 20   |
| 3    | jack    | 24   |
| 4    | jackson | 19   |
| 5    | liming  | 18   |
+------+---------+------+

Interrogation et analyse de fichiers

Tous les exemples de cette section utilisent la TVF Amazon S3. Les mêmes modèles s'appliquent à la TVF HDFS.

Inspection du schéma de fichier

Utilisez DESC FUNCTION pour inspecter le schéma d'un fichier avant de l'interroger. ApsaraDB for SelectDB déduit automatiquement les types de colonnes pour les fichiers Parquet, ORC, CSV et JSON.

DESC FUNCTION s3(
    "uri"            = "http://127.0.0.1:9312/test2/test.snappy.parquet",
    "s3.access_key"  = "ak",
    "s3.secret_key"  = "sk",
    "format"         = "parquet",
    "use_path_style" = "true");

-- Sample response
+---------------+--------------+------+-------+---------+-------+
| Field         | Type         | Null | Key   | Default | Extra |
+---------------+--------------+------+-------+---------+-------+
| p_partkey     | INT          | Yes  | false | NULL    | NONE  |
| p_name        | TEXT         | Yes  | false | NULL    | NONE  |
| p_mfgr        | TEXT         | Yes  | false | NULL    | NONE  |
| p_brand       | TEXT         | Yes  | false | NULL    | NONE  |
| p_type        | TEXT         | Yes  | false | NULL    | NONE  |
| p_size        | INT          | Yes  | false | NULL    | NONE  |
| p_container   | TEXT         | Yes  | false | NULL    | NONE  |
| p_retailprice | DECIMAL(9,0) | Yes  | false | NULL    | NONE  |
| p_comment     | TEXT         | Yes  | false | NULL    | NONE  |
+---------------+--------------+------+-------+---------+-------+

Pour les fichiers CSV, toutes les colonnes sont déduites comme STRING par défaut. Pour spécifier explicitement les noms et types de colonnes, utilisez le paramètre csv_schema avec le format name1:type1;name2:type2;... :

SELECT * FROM s3(
    "uri"              = "https://bucket1/inventory.dat",
    "s3.access_key"    = "ak",
    "s3.secret_key"    = "sk",
    "format"           = "csv",
    "column_separator" = "|",
    "csv_schema"       = "k1:int;k2:int;k3:int;k4:decimal(38,10)",
    "use_path_style"   = "true");

Si un type spécifié ne correspond pas aux données réelles, ou si vous spécifiez plus de colonnes que le fichier n'en contient, ApsaraDB for SelectDB renvoie NULL pour ces colonnes.

Les types de colonnes suivants sont pris en charge dans csv_schema :

Type spécifié	Type mappé
`tinyint`	tinyint
`smallint`	smallint
`int`	int
`bigint`	bigint
`largeint`	largeint
`float`	float
`double`	double
`decimal(p,s)`	decimalv3(p,s)
`date`	datev2
`datetime`	datetimev2
`char`	string
`varchar`	string
`string`	string
`boolean`	boolean

Exécution de requêtes SQL

Utilisez la TVF partout où un nom de table est valide en SQL, y compris dans les clauses FROM et les expressions de table communes (CTE) :

SELECT * FROM s3(
    "uri"            = "http://127.0.0.1:9312/test2/test.snappy.parquet",
    "s3.access_key"  = "ak",
    "s3.secret_key"  = "sk",
    "format"         = "parquet",
    "use_path_style" = "true")
LIMIT 5;

-- Sample response
+-----------+------------------------------------------+----------------+----------+-------------------------+--------+-------------+---------------+---------------------+
| p_partkey | p_name                                   | p_mfgr         | p_brand  | p_type                  | p_size | p_container | p_retailprice | p_comment           |
+-----------+------------------------------------------+----------------+----------+-------------------------+--------+-------------+---------------+---------------------+
|         1 | goldenrod lavender spring chocolate lace | Manufacturer#1 | Brand#13 | PROMO BURNISHED COPPER  |      7 | JUMBO PKG   |           901 | ly. slyly ironi     |
|         2 | blush thistle blue yellow saddle         | Manufacturer#1 | Brand#13 | LARGE BRUSHED BRASS     |      1 | LG CASE     |           902 | lar accounts amo    |
|         3 | spring green yellow purple cornsilk      | Manufacturer#4 | Brand#42 | STANDARD POLISHED BRASS |     21 | WRAP CASE   |           903 | egular deposits hag |
|         4 | cornflower chocolate smoke green pink    | Manufacturer#3 | Brand#34 | SMALL PLATED BRASS      |     14 | MED DRUM    |           904 | p furiously r       |
|         5 | forest brown coral puff cream            | Manufacturer#3 | Brand#32 | STANDARD POLISHED TIN   |     15 | SM PKG      |           905 |  wake carefully     |
+-----------+------------------------------------------+----------------+----------+-------------------------+--------+-------------+---------------+---------------------+

Création d'une vue

Créez une vue sur une TVF pour partager l'accès et gérer les permissions sans exposer les identifiants dans chaque requête :

CREATE VIEW v1 AS
SELECT * FROM s3(
    "uri"            = "http://127.0.0.1:9312/test2/test.snappy.parquet",
    "s3.access_key"  = "ak",
    "s3.secret_key"  = "sk",
    "format"         = "parquet",
    "use_path_style" = "true");

DESC v1;

SELECT * FROM v1;

GRANT SELECT_PRIV ON db1.v1 TO user1;

Importation de données de fichier dans une table

Utilisez INSERT INTO SELECT avec une TVF pour charger des données de fichier dans une table ApsaraDB for SelectDB :

-- Step 1: Create a target table.
CREATE TABLE IF NOT EXISTS test_table
(
    id   INT,
    name VARCHAR(50),
    age  INT
)
DISTRIBUTED BY HASH(id) BUCKETS 4
PROPERTIES("replication_num" = "1");

-- Step 2: Insert data from the S3 file.
INSERT INTO test_table (id, name, age)
SELECT CAST(id AS INT) AS id, name, CAST(age AS INT) AS age
FROM s3(
    "uri"            = "http://127.0.0.1:9312/test2/test.snappy.parquet",
    "s3.access_key"  = "ak",
    "s3.secret_key"  = "sk",
    "format"         = "parquet",
    "use_path_style" = "true");

Remarques sur l'utilisation

URI vide ou absence de fichiers correspondants : Si l'URI n'existe pas ou si tous les fichiers correspondants sont vides, la TVF renvoie un ensemble de résultats vide. L'exécution de DESC FUNCTION dans ce cas renvoie une colonne factice __dummy_col, qui peut être ignorée.
Première ligne vide dans les fichiers CSV : Si le format du fichier est CSV et que le fichier n'est pas vide mais que la première ligne l'est, l'erreur suivante est renvoyée : The first line is empty, can not parse column numbers. Assurez-vous que la première ligne de votre fichier CSV n'est pas vide.
Incompatibilités de type dans le schéma CSV : Lorsque vous spécifiez des types de colonnes avec csv_schema et qu'une valeur ne correspond pas au type déclaré (par exemple, une valeur chaîne dans une colonne déclarée comme INT), ApsaraDB for SelectDB renvoie NULL pour cette valeur au lieu de générer une erreur.