Analyze file data in ApsaraDB for SelectDB - ApsaraDB for SelectDB: Central de Documentação do Alibaba Cloud

TVF do Amazon S3

A função s3() lê arquivos de qualquer sistema de armazenamento de objetos compatível com S3. Formatos suportados: CSV, csv_with_names, csv_with_names_and_types, JSON, Parquet e ORC.

Sintaxe

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" ...]
)

Os parâmetros obrigatórios aparecem sem colchetes. Os opcionais estão entre [...].

Parâmetros

Cada parâmetro é um par chave-valor no formato "key" = "value".

Parâmetros obrigatórios

Parâmetro	Descrição
`uri`	URI para acessar o Amazon S3. Esquemas suportados: `http://`, `https://` e `s3://`. Se nenhum arquivo corresponder à URI ou se todos os arquivos correspondentes estiverem vazios, a TVF retornará um conjunto de resultados vazio.
`s3.access_key`	ID da chave de acesso.
`s3.secret_key`	Chave de acesso secreta.
`s3.region`	Região do Amazon S3. Padrão: `us-east-1`.
`format`	Formato do arquivo. Valores válidos: `csv`, `csv_with_names`, `csv_with_names_and_types`, `json`, `parquet`, `orc`.

Parâmetros opcionais

Parâmetro	Padrão	Descrição
`s3.session_token`	—	Token de sessão temporário. Obrigatório quando a autenticação por sessão temporária está ativada.
`use_path_style`	`false`	Controla o uso do estilo de caminho em vez do estilo de host virtual. Defina como `true` para sistemas de armazenamento incompatíveis com o estilo de host virtual (por exemplo, MinIO). Nota Se a URI usar o esquema `s3://`, o estilo de host virtual será sempre utilizado, independentemente desta configuração.
`column_separator`	`,`	Delimitador de colunas.
`line_delimiter`	`\n`	Delimitador de linhas.
`compress_type`	`unknown`	Tipo de compressão. O valor `unknown` infere automaticamente o tipo com base no sufixo da URI. Outros valores válidos: `plain`, `gz`, `lzo`, `bz2`, `lz4frame`, `deflate`.
`read_json_by_line`	`true`	Lê dados JSON linha por linha.
`num_as_string`	`false`	Processa valores numéricos como strings.
`fuzzy_parse`	`false`	Acelera o desempenho da importação de JSON.
`jsonpaths`	—	Campos a extrair dos dados JSON. Formato: `jsonpaths: ["$.k2", "$.k1"]`.
`strip_outer_array`	`false`	Trata um array JSON de nível superior como várias linhas, com um elemento por linha. Formato: `strip_outer_array: true`.
`json_root`	(vazio)	Nó raiz para análise de JSON. O ApsaraDB for SelectDB extrai e analisa apenas os elementos abaixo deste nó. Formato: `json_root: $.RECORDS`.
`path_partition_keys`	—	Nomes de colunas de chave de partição separados por vírgula e incorporados ao caminho do arquivo. Por exemplo, para o caminho `/path/to/city=beijing/date=2023-07-09`, defina este valor como `city,date`. Durante a importação, o ApsaraDB for SelectDB lê os nomes e valores das colunas correspondentes diretamente do caminho.

Exemplos

Leitura de um arquivo CSV de um sistema de armazenamento compatível com MinIO

O MinIO usa o estilo de caminho por padrão. Portanto, defina use_path_style como 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;

Leitura de um arquivo Parquet do Object Storage Service (OSS)

O OSS exige o estilo de host virtual. Defina use_path_style como 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");

Leitura de um arquivo CSV usando o estilo de caminho

Quando use_path_style é true, o nome do bucket integra o caminho da 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");

Leitura de um arquivo CSV usando o estilo de host virtual

Quando use_path_style é false, o nome do bucket integra o nome do host:

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 do HDFS

A função hdfs() lê arquivos do HDFS assim como a s3() lê do armazenamento de objetos. Formatos suportados: CSV, csv_with_names, csv_with_names_and_types, JSON, Parquet e ORC.

Sintaxe

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

Parâmetros

Parâmetros obrigatórios

Parâmetro	Descrição
`uri`	URI para acessar o HDFS. Se nenhum arquivo corresponder à URI ou se todos os arquivos correspondentes estiverem vazios, a TVF retornará um conjunto de resultados vazio.
`fs.defaultFS`	Nome do host e porta do NameNode do HDFS.
`hadoop.username`	Nome de usuário para acesso ao HDFS. Não pode estar vazio.
`format`	Formato do arquivo. Valores válidos: `csv`, `csv_with_names`, `csv_with_names_and_types`, `json`, `parquet`, `orc`.

Parâmetros opcionais

Parâmetro	Padrão	Descrição
`hadoop.security.authentication`	—	Método de autenticação. Valores válidos: `Simple`, `Kerberos`.
`hadoop.kerberos.principal`	—	Principal do Kerberos. Obrigatório quando a autenticação Kerberos está ativada.
`hadoop.kerberos.keytab`	—	Caminho para o arquivo keytab do Kerberos. Obrigatório quando a autenticação Kerberos está ativada.
`dfs.client.read.shortcircuit`	—	Ativa leituras de curto-circuito para dados locais do HDFS (BOOLEAN).
`dfs.domain.socket.path`	—	Caminho do socket de domínio UNIX para comunicação entre DataNode e cliente. A string `_PORT` no caminho é substituída pela porta TCP do DataNode.
`dfs.nameservices`	—	Nomes lógicos dos nameservices. Corresponde a `dfs.nameservices` em `core-site.xml`.
`dfs.ha.namenodes.<nameservice>`	—	Nomes lógicos dos NameNodes. Obrigatório para implantações de alta disponibilidade (HA) do Hadoop.
`dfs.namenode.rpc-address.<nameservice>.<namenode>`	—	URL HTTP na qual o NameNode escuta. Obrigatório para implantações HA do Hadoop.
`dfs.client.failover.proxy.provider.<nameservice>`	—	Classe de implementação do provedor de proxy de failover para conexões de cliente HA. Obrigatório para implantações HA do Hadoop.
`read_json_by_line`	`true`	Lê dados JSON linha por linha.
`num_as_string`	`false`	Processa valores numéricos como strings.
`fuzzy_parse`	`false`	Acelera o desempenho da importação de JSON.
`jsonpaths`	—	Campos a extrair dos dados JSON. Formato: `jsonpaths: ["$.k2", "$.k1"]`.
`strip_outer_array`	`false`	Trata um array JSON de nível superior como várias linhas, com um elemento por linha. Formato: `strip_outer_array: true`.
`json_root`	(vazio)	Nó raiz para análise de JSON. Formato: `json_root: $.RECORDS`.
`trim_double_quotes`	`false`	Remove as aspas duplas mais externas de cada campo em arquivos CSV.
`skip_lines`	`0`	Número de linhas iniciais a ignorar em arquivos CSV. Intervalo: `[0–Integer.MaxValue]`. Não tem efeito quando `format` é `csv_with_names` ou `csv_with_names_and_types`.
`path_partition_keys`	—	Nomes de colunas de chave de partição separados por vírgula e incorporados ao caminho do arquivo. Mesmo comportamento da TVF do S3.

Exemplos

Leitura de um arquivo CSV do 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   |
+------+---------+------+

Leitura de um arquivo CSV do HDFS no modo HA

Para implantações HA do Hadoop, adicione os três parâmetros específicos de HA:

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

Consulta e análise de arquivos

Todos os exemplos nesta seção usam a TVF do Amazon S3. Os mesmos padrões se aplicam à TVF do HDFS.

Inspeção do esquema do arquivo

Use DESC FUNCTION para inspecionar o esquema de um arquivo antes de consultá-lo. O ApsaraDB for SelectDB infere automaticamente os tipos de coluna para arquivos Parquet, ORC, CSV e 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  |
+---------------+--------------+------+-------+---------+-------+

Para arquivos CSV, todas as colunas são inferidas como STRING por padrão. Para especificar explicitamente nomes e tipos de colunas, use o parâmetro csv_schema com o formato 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");

Se um tipo especificado não corresponder aos dados reais, ou se você especificar mais colunas do que o arquivo contém, o ApsaraDB for SelectDB retornará NULL para essas colunas.

Os seguintes tipos de coluna são suportados em csv_schema:

Tipo especificado	Tipo mapeado
`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

Execução de consultas SQL

Use a TVF em qualquer lugar onde um nome de tabela seja válido em SQL, incluindo cláusulas FROM e expressões de tabela comuns (CTEs):

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

Criação de uma view

Crie uma view sobre uma TVF para compartilhar acesso e gerenciar permissões sem expor credenciais em cada consulta:

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;

Importação de dados de arquivo para uma tabela

Use INSERT INTO SELECT com uma TVF para carregar dados de arquivo em uma tabela do 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");

Observações de uso

URI vazia ou ausência de arquivos correspondentes: Se a URI não existir ou se todos os arquivos correspondentes estiverem vazios, a TVF retornará um conjunto de resultados vazio. Nesse caso, executar DESC FUNCTION retorna uma coluna fictícia __dummy_col, que pode ser ignorada.
Primeira linha vazia em arquivos CSV: Se o formato do arquivo for CSV e o arquivo não estiver vazio, mas a primeira linha estiver, o sistema retornará o seguinte erro: The first line is empty, can not parse column numbers. Certifique-se de que a primeira linha do seu arquivo CSV não esteja vazia.
Incompatibilidade de tipos no esquema CSV: Ao especificar tipos de coluna com csv_schema e um valor não corresponder ao tipo declarado — por exemplo, um valor string em uma coluna declarada como INT — o ApsaraDB for SelectDB retornará NULL para esse valor em vez de gerar um erro.