Tabelas externas do OSS no MaxCompute - criar, ler, gravar - MaxCompute: Central de Documentação do Alibaba Cloud

Observações de uso

As tabelas externas do OSS não suportam a propriedade de cluster.
O tamanho de um único arquivo não pode exceder 2 GB. Divida arquivos maiores que esse limite.
O MaxCompute e o OSS devem estar na mesma região.

Métodos de acesso

As plataformas a seguir permitem criar e usar tabelas externas do OSS.

Método	Plataforma
MaxCompute SQL	Cliente do MaxCompute Conectar usando o DataWorks Console do DataWorks - Nó ODPS SQL MaxCompute Studio - Script SQL
Visualização	MaxCompute Studio - Gerenciar tabelas externas visualmente Console do DataWorks - Tabelas externas

Pré-requisitos

Você já criou um projeto do MaxCompute.
Prepare um bucket e um diretório no OSS. Criar um bucket e Gerenciar diretórios.

O MaxCompute pode criar diretórios do OSS automaticamente. Se uma instrução SQL incluir tabelas externas e UDFs, será possível ler ou gravar na tabela externa e executar a UDF em uma única instrução. A criação manual de diretórios também é suportada.

Como o MaxCompute está implantado apenas em algumas regiões, a conectividade de rede entre regiões pode ser um problema. Mantenha seu bucket na mesma região do seu projeto do MaxCompute.
Autorização
- Obtenha permissões para acessar o OSS. Uma conta Alibaba Cloud, um usuário RAM ou uma função RAM podem acessar tabelas externas do OSS. Consulte Autorização STS para OSS.
- Obtenha a permissão CreateTable em seu projeto do MaxCompute. Consulte Permissões do MaxCompute.

Criar uma tabela externa do OSS

Tabelas particionadas e não particionadas:
- Escolha com base em como seus arquivos de dados estão armazenados no OSS. Use uma tabela particionada se os arquivos estiverem em caminhos particionados; caso contrário, use uma tabela não particionada.
- As tabelas externas do OSS suportam operações de partição.
Nome de domínio de rede: Use o nome de domínio da rede clássica para o OSS. O MaxCompute não garante conectividade de rede para nomes de domínio da rede pública.
Uma tabela externa do OSS registra apenas o mapeamento para um diretório do OSS. Excluir a tabela externa não remove os arquivos de dados no diretório mapeado.
Se um arquivo de dados do OSS for um objeto arquivado, você deve primeiro restaurar o objeto.

A sintaxe CREATE EXTERNAL TABLE varia conforme o formato do arquivo de dados. Adapte a sintaxe e os parâmetros ao formato dos seus dados. Configurações incorretas causam falhas de leitura e gravação.

Sintaxe

Criar uma tabela externa usando o analisador de dados de texto integrado

Sintaxe

Formato do arquivo de dados

Exemplo

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> 
(
<col_name> <data_type>,
...
)
[comment <table_comment>]
[partitioned BY (<col_name> <data_type>, ...)] 
stored BY '<StorageHandler>'  
WITH serdeproperties (
 ['<property_name>'='<property_value>',...]
) 
location '<oss_location>';

Formatos de arquivo de dados suportados para leitura ou gravação no OSS:

CSV
TSV
Arquivos CSV e TSV compactados com GZIP, SNAPPY ou LZO

Criar uma tabela externa usando um analisador de dados open source integrado

Sintaxe

Formato do arquivo de dados

Exemplo

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
(
<col_name> <data_type>,
...
)
[comment <table_comment>]
[partitioned BY (<col_name> <data_type>, ...)]
[row format serde '<serde_class>'
  [WITH serdeproperties (
    ['<property_name>'='<property_value>',...])
  ]
]
stored AS <file_format> 
location '<oss_location>' 
[USING '<resource_name>']
[tblproperties ('<tbproperty_name>'='<tbproperty_value>',...)];

Formatos de arquivo de dados suportados para leitura ou gravação no OSS:

PARQUET
TEXTFILE
ORC
RCFILE
AVRO
JSON
SEQUENCEFILE
Hudi (suporta apenas leitura de dados Hudi gerados pelo DLF)
Arquivos PARQUET compactados com ZSTD, SNAPPY ou GZIP
Arquivos ORC compactados com SNAPPY ou ZLIB
Arquivos TEXTFILE compactados com GZIP, SNAPPY ou LZO

Criar uma tabela externa usando um analisador personalizado

Sintaxe

Formato do arquivo de dados

Exemplo

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> 
(
<col_name> <date_type>,
...
)
[comment <table_comment>]
[partitioned BY (<col_name> <data_type>, ...)] 
stored BY '<StorageHandler>' 
WITH serdeproperties (
 ['<property_name>'='<property_value>',...]
) 
location '<oss_location>' 
USING '<jar_name>';

Formatos de arquivo de dados suportados para leitura ou gravação no OSS: Arquivos de dados em formatos diferentes dos listados acima.

Parâmetros

Os parâmetros a seguir são comuns a todos os formatos de tabela externa. Para parâmetros específicos de cada formato, consulte a documentação correspondente.

Parâmetros básicos de sintaxe

Parâmetro	Obrigatório	Descrição
mc_oss_extable_name	Sim	Nome da tabela externa do OSS a ser criada. Os nomes das tabelas não diferenciam maiúsculas de minúsculas e não podem ser forçados a um formato específico.
col_name	Sim	Nome de uma coluna na tabela externa do OSS. O esquema da tabela externa deve corresponder ao esquema do arquivo de dados do OSS. Caso contrário, a leitura dos dados falhará.
data_type	Sim	Tipo de dados de uma coluna na tabela externa do OSS. O tipo de dados de cada coluna deve corresponder à coluna equivalente no arquivo de dados do OSS. Caso contrário, a leitura dos dados falhará.
table_comment	Não	Comentário da tabela. Deve ser uma string válida com no máximo 1.024 bytes. Caso contrário, um erro será reportado.
partitioned by (col_name data_type, ...)	Não	Se os arquivos de dados no OSS estiverem armazenados em um caminho particionado, inclua este parâmetro para criar uma tabela particionada. col_name: Nome da coluna da chave de partição. data_type: Tipo de dados da coluna da chave de partição.
'<(tb)property_name>'='<(tb)property_value>'	Sim	Propriedades estendidas da tabela externa. Consulte a documentação específica do formato para obter detalhes.
oss_location	Sim	Caminho do OSS onde os arquivos de dados estão localizados. Por padrão, todos os arquivos de dados neste caminho são lidos. O formato é `oss://<oss_endpoint>/<Bucket name>/<OSS directory name>/`. oss_endpoint: Nome de domínio do OSS. Você deve usar o endpoint da rede clássica fornecido pelo OSS, que contém `-internal`. Exemplo: `oss://oss-cn-beijing-internal.aliyuncs.com/xxx`. Os nomes de domínio da rede clássica do OSS estão listados em Regiões e endpoints. Mantenha a região do OSS onde os arquivos de dados estão armazenados igual à região do seu projeto do MaxCompute. Se estiverem em regiões diferentes, poderão ocorrer problemas de conectividade de rede. Se você não especificar um endpoint, o sistema usará o endpoint da região onde o projeto atual está localizado. Este método não é recomendado, pois o armazenamento de arquivos entre regiões pode causar problemas de conectividade de rede. Bucket name: Nome do bucket do OSS. O nome do bucket deve seguir o `oss_endpoint`. Exemplo: `oss://oss-cn-beijing-internal.aliyuncs.com/your_bucket/path/`. Visualize os nomes dos buckets em Listar buckets. Directory name: Nome do diretório do OSS. Não especifique um nome de arquivo após o diretório. Exemplo: `oss://oss-cn-beijing-internal.aliyuncs.com/oss-mc-test/Demo1/`. Exemplos incorretos: `-- Conexões HTTP não são suportadas. http://oss-cn-shanghai-internal.aliyuncs.com/oss-mc-test/Demo1/ -- Conexões HTTPS não são suportadas. https://oss-cn-shanghai-internal.aliyuncs.com/oss-mc-test/Demo1/ -- Endereço de conexão incorreto. oss://oss-cn-shanghai-internal.aliyuncs.com/Demo1 -- Não especifique um nome de arquivo. oss://oss-cn-shanghai-internal.aliyuncs.com/oss-mc-test/Demo1/vehicle.csv` Especificação de permissão (RamRole): Especificar explicitamente (Recomendado): Crie uma função personalizada, anexe uma política de acesso e use seu ARN. Consulte Autorização STS. Usar Padrão (Não recomendado): Use o ARN da função `aliyunodpsdefaultrole`.

Atributos WITH serdeproperties

property_name

Cenário

property_value

Valor padrão

odps.properties.rolearn

Adicione esta propriedade ao usar autorização STS.

Especifique o ARN da função RAM que tem permissões para acessar o OSS.

Obtenha o ARN nos detalhes da função no Console RAM. Exemplo: acs:ram::xxxxxx:role/aliyunodpsdefaultrole.

Se os proprietários do MaxCompute e do OSS forem a mesma conta:
- Se você não especificar odps.properties.rolearn na instrução de criação da tabela, o ARN da função aliyunodpsdefaultrole será usado por padrão. Você deve primeiro criar a função aliyunodpsdefaultrole usando autorização STS.
- Para usar um ARN de função personalizada, crie primeiro a função personalizada. Consulte Autorização STS para OSS (autorização personalizada).
Se os proprietários do MaxCompute e do OSS forem contas diferentes, você deve especificar o ARN de uma função personalizada. Para mais informações, consulte Autorização STS para OSS (autorização personalizada).

Ler dados do OSS

Observações

Após criar uma tabela externa do OSS, leia dados do OSS através dela. Para tipos de arquivos de dados suportados e sintaxe de criação, consulte Sintaxe.
Se uma instrução SQL envolver tipos de dados complexos, adicione set odps.sql.type.system.odps2=true; antes e envie-as juntas. Consulte Versões de tipos de dados.
Para tabelas externas do OSS que mapeiam dados open source, defina set odps.sql.hive.compatible=true; no nível de sessão antes de ler dados do OSS. Caso contrário, um erro será reportado.
O OSS possui limites de largura de banda. Se o tráfego de leitura/gravação exceder o limite de largura de banda da instância em um curto período, o desempenho da tabela externa será degradado. Consulte Limites e métricas de desempenho.

Sintaxe

<select_statement> FROM <from_statement>;

select_statement: A cláusula SELECT, que consulta os dados a serem inseridos na tabela de destino a partir da tabela de origem.
from_statement: A cláusula FROM, que especifica a fonte de dados, como o nome da tabela externa.

Dados não particionados

Após criar uma tabela externa do OSS não particionada, leia dados do OSS usando um dos seguintes métodos:

Método 1 (Recomendado): Importe os dados de formato open source do OSS para uma tabela interna do MaxCompute e, em seguida, leia os dados.

Ideal para cálculos repetidos ou cenários de alto desempenho. Crie uma tabela interna com o mesmo esquema da tabela externa, importe os dados e execute consultas complexas. O armazenamento interno se beneficia da otimização do MaxCompute. Comando de exemplo:
```
CREATE TABLE <table_internal> LIKE <mc_oss_extable_name>;
INSERT OVERWRITE TABLE <table_internal> SELECT * FROM <mc_oss_extable_name>;
```
Método 2: Leia dados diretamente do OSS, de forma semelhante às operações em tabelas internas do MaxCompute.

Indicado para cenários de baixo desempenho. Cada consulta lê dados diretamente do OSS em vez do armazenamento interno.

Dados particionados

O MaxCompute realiza uma varredura completa de todos os dados no diretório do OSS, incluindo subdiretórios. Para grandes conjuntos de dados, isso causa E/S desnecessária e aumenta o tempo de processamento. Duas soluções estão disponíveis.

Método 1 (Recomendado): Armazene dados no OSS usando um caminho particionado padrão ou um caminho particionado personalizado.

Especifique a partição e o oss_location na instrução de criação da tabela. Caminhos particionados padrão são recomendados.
Método 2: Planeje vários caminhos de armazenamento de dados.

Crie várias tabelas externas, cada uma apontando para um subconjunto de dados do OSS. Este método é trabalhoso e não recomendado.

Formato de caminho particionado padrão

oss://<oss_endpoint>/<Bucket name>/<directory name>/<partitionKey1=value1>/<partitionKey2=value2>/...

Exemplo: Uma empresa armazena arquivos de log diários em formato CSV no OSS e processa os dados diariamente usando o MaxCompute. O caminho particionado padrão para armazenar dados do OSS deve ser definido da seguinte forma.

oss://oss-odps-test/log_data/year=2016/month=06/day=01/logfile
oss://oss-odps-test/log_data/year=2016/month=06/day=02/logfile
oss://oss-odps-test/log_data/year=2016/month=07/day=10/logfile
oss://oss-odps-test/log_data/year=2016/month=08/day=08/logfile
...

Formato de caminho particionado personalizado

Um formato de caminho particionado personalizado contém apenas valores de colunas de partição, não nomes de colunas de partição. Exemplo:

oss://oss-odps-test/log_data_customized/2016/06/01/logfile
oss://oss-odps-test/log_data_customized/2016/06/02/logfile
oss://oss-odps-test/log_data_customized/2016/07/10/logfile
oss://oss-odps-test/log_data_customized/2016/08/08/logfile
...

Se os dados do OSS usarem um caminho particionado não padrão, vincule subdiretórios a partições manualmente.

Após criar a tabela externa, use alter table ... add partition ... location ... para vincular subdiretórios a partições. Exemplo:

ALTER TABLE log_table_external ADD PARTITION (year = '2016', month = '06', day = '01')
location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/bucket_name/oss-odps-test/log_data_customized/2016/06/01/';
ALTER TABLE log_table_external ADD PARTITION (year = '2016', month = '06', day = '02')
location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/bucket_name/oss-odps-test/log_data_customized/2016/06/02/';
ALTER TABLE log_table_external ADD PARTITION (year = '2016', month = '07', day = '10')
location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/bucket_name/oss-odps-test/log_data_customized/2016/07/10/';
ALTER TABLE log_table_external ADD PARTITION (year = '2016', month = '08', day = '08')
location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/bucket_name/oss-odps-test/log_data_customized/2016/08/08/';

Otimização de consulta

Coleta dinâmica de estatísticas

Dados externos carecem de estatísticas pré-existentes, fazendo com que o otimizador de consultas use uma estratégia conservadora e de baixa eficiência. A coleta dinâmica de estatísticas permite que o otimizador colete estatísticas da tabela durante a execução da consulta para identificar tabelas pequenas, habilitando Hash Join, ordem de junção otimizada, menos shuffles e pipelines de execução mais curtos.

Os parâmetros a seguir não se aplicam a tabelas externas Paimon, Hudi ou Delta Lake.

SET odps.meta.exttable.stats.onlinecollect=true;
SELECT * FROM <tablename>;

Otimização de divisão de tabela externa

Ajuste o tamanho da divisão para controlar a quantidade de dados que cada tarefa concorrente processa.

Se o volume de dados for grande e o tamanho da divisão for muito pequeno, divisões excessivas causam alto paralelismo e a instância passa a maior parte do tempo aguardando recursos.
Se o volume de dados for pequeno e o tamanho da divisão for muito grande, poucas divisões causam concorrência insuficiente e recursos ociosos.

-- You can use either of the following parameters.

-- Unit: MiB. Default value: 256 MiB. Applies to internal or external tables.
SET odps.stage.mapper.split.size=<value>;
SELECT * FROM <tablename>;

-- Unit: MiB. Default value: 256 MiB. Applies only to external tables.
SET odps.sql.unstructured.data.split.size=<value>;
SELECT * FROM <tablename>;

Controle de DOP

Controlar paralelismo com DOP

Defina o parâmetro odps.sql.split.dop para ajustar o grau de paralelismo ao ler dados. Este parâmetro tem prioridade maior que odps.sql.mapper.split.size.

Se o valor dop for maior que o número de arquivos no diretório do OSS, a concorrência real poderá diferir significativamente do valor dop configurado.
Se o valor dop for muito pequeno, ele não terá efeito. Use o parâmetro odps.input.file.num.limit para alterar o número máximo de arquivos que uma única instância pode processar.

Sintaxe

-- Syntax for the two-tier model: set odps.sql.split.dop={"project.table": xxx};
-- Syntax for the three-tier model: set odps.sql.split.dop={"project.schema.table": xxx};

SET odps.sql.split.dop={
  "project.schema.table1": xxx, 
  "project.schema.table2": yyy
};

SET odps.sql.common.table.planner.ext.hive.bridge=FALSE;

SELECT * FROM <your_table>;

Exemplo de uso

Problema e solução para distorção de DOP causada por muitos arquivos pequenos

O MaxCompute limita uma única instância a processar no máximo 240 arquivos. Se um diretório de tabela externa do OSS contiver 3.449 arquivos, o grau mínimo de paralelismo será 3.449 / 240 ≈ 15. Se você definir o DOP com um valor menor que 15, a configuração será ignorada.

Para resolver isso, defina odps.input.file.num.limit para alterar o número máximo de arquivos que uma única instância pode processar.

SET odps.sql.split.dop = {"lakehouse47_3.tpch_1t_parquet_snappy.lineitem": 2};
SET odps.input.file.num.limit = 5000;

Uma única instância pode processar até 5.000 arquivos do OSS. Como o número de arquivos na tabela lineitem está bem abaixo desse limite, o grau real de paralelismo corresponde ao valor DOP configurado.

Gravar dados no OSS

O MaxCompute pode gravar dados de tabelas internas ou tabelas externas processadas no OSS. Para limites, consulte Escopo.

Grave dados no OSS usando um analisador de dados de texto ou open source integrado: Analisador de texto integrado, Analisador open source integrado.
Grave dados no OSS usando um analisador personalizado: Exemplo: Criar uma tabela externa do OSS usando um analisador personalizado.
Grave dados no OSS usando o recurso de upload multipart do OSS: Gravar dados no OSS usando o recurso de upload multipart do OSS.

Sintaxe

INSERT {INTO|OVERWRITE} TABLE <table_name> PARTITION (<ptcol_name>[, <ptcol_name> ...]) 
<select_statement> FROM <from_statement>;

Parâmetro	Obrigatório	Descrição
table_name	Sim	Nome da tabela externa onde os dados serão gravados.
select_statement	Sim	A cláusula `SELECT`, que consulta os dados a serem inseridos na tabela de destino a partir da tabela de origem. Se a tabela de destino tiver apenas um nível de partições dinâmicas, o valor do último campo na cláusula `SELECT` será o valor da partição dinâmica da tabela de destino. A relação entre os valores do `SELECT` da tabela de origem e os valores de partição de saída é determinada pela ordem dos campos, não pelos nomes das colunas. Se a ordem dos campos da tabela de origem for diferente da tabela de destino, especifique os campos no select_statement na ordem da tabela de destino.
from_statement	Sim	A cláusula `FROM`, que indica a fonte de dados. Por exemplo, o nome da tabela interna de onde ler.

Para inserir dados em partições dinâmicas, consulte Inserir ou sobrescrever dados em partições dinâmicas (DYNAMIC PARTITION) .

Observações

Se a operação INSERT OVERWRITE ... SELECT ... FROM ...; alocar 1.000 mappers na tabela de origem from_tablename, 1.000 arquivos TSV ou CSV serão gerados.
Controle o número de arquivos gerados usando configurações fornecidas pelo MaxCompute.
- Se o outputter estiver em um mapper: Use odps.stage.mapper.split.size para controlar o número de mappers concorrentes, ajustando assim o número de arquivos gerados.
- Se o outputter estiver em um reducer ou joiner: Use odps.stage.reducer.num e odps.stage.joiner.num respectivamente para ajustar o número de arquivos gerados.
Risco de gravações inconsistentes: Ao usar uma instrução INSERT OVERWRITE em uma tabela externa do OSS ou usar o comando UNLOAD para exportar arquivos para o OSS, os dados no subdiretório do local especificado do OSS ou no local correspondente à partição são excluídos antes que novos dados sejam gravados. Se o diretório de localização contiver dados importantes gravados diretamente no OSS por outros mecanismos externos, esses dados também serão excluídos antes da gravação dos novos dados. Portanto, garanta que os arquivos existentes no diretório de localização da tabela externa tenham backup ou que o diretório UNLOAD esteja vazio. Para outros riscos de gravações inconsistentes, consulte Escopo.

Gravar dados no OSS usando o recurso de upload multipart do OSS

Para gravar dados no OSS em formato open source, crie uma tabela externa com um analisador de dados open source e ative o recurso de upload multipart do OSS.

Para ativar o recurso de upload multipart do OSS, configure o seguinte:

Cenário

Comando

Definir no nível do projeto

Entra em vigor para todo o projeto.

setproject odps.sql.unstructured.oss.commit.mode =true;

Definir no nível da sessão

Entra em vigor apenas para a tarefa atual.

set odps.sql.unstructured.oss.commit.mode =true;

O valor padrão de odps.sql.unstructured.oss.commit.mode é false. Os dois modos funcionam da seguinte forma:

Valor	Princípio
false	Os dados são armazenados em uma pasta .odps sob o diretório LOCATION, com um arquivo .meta para consistência dos dados. O conteúdo de .odps só pode ser processado corretamente pelo MaxCompute. Outros mecanismos podem falhar ao analisá-lo.
true	O MaxCompute usa o recurso de upload multipart para ser compatível com outros mecanismos de processamento de dados. Ele utiliza um método de `two-phase commit` para garantir a consistência dos dados, e não haverá diretório .odps nem arquivo .meta.

Gerenciar arquivos exportados

Parâmetros

Adicione um prefixo, sufixo ou extensão aos arquivos de dados de saída usando os seguintes parâmetros.

property_name	Cenário	Descrição	property_value	Valor padrão
odps.external.data.output.prefix (Compatível com odps.external.data.prefix)	Adicione esta propriedade quando precisar adicionar um prefixo personalizado aos arquivos de saída.	Contém apenas dígitos, letras e sublinhados (a-z, A-Z, 0-9, _). O comprimento deve estar entre 1 e 10 caracteres.	Uma combinação de caracteres permitidos, como 'mc_'	Nenhum
odps.external.data.enable.extension	Adicione esta propriedade quando precisar exibir a extensão dos arquivos de saída.	True indica que a extensão do arquivo de saída é exibida. False indica que ela não é exibida.	True False	False
odps.external.data.output.suffix	Adicione esta propriedade quando precisar adicionar um sufixo personalizado aos arquivos de saída.	Contém apenas dígitos, letras e sublinhados (a-z, A-Z, 0-9, _).	Uma combinação de caracteres permitidos, como '_hangzhou'	Nenhum
odps.external.data.output.explicit.extension	Adicione esta propriedade quando precisar adicionar uma extensão personalizada aos arquivos de saída.	Contém apenas dígitos, letras e sublinhados (a-z, A-Z, 0-9, _). O comprimento deve estar entre 1 e 10 caracteres. Tem prioridade maior que o parâmetro `odps.external.data.enable.extension`.	Uma combinação de caracteres permitidos, como "jsonl"	Nenhum

Exemplos

Defina o prefixo personalizado para arquivos gravados no OSS como test06_. O DDL é o seguinte:

CREATE EXTERNAL TABLE  <mc_oss_extable_name>
(
  vehicleId INT,
  recordId INT,
  patientId INT,
  calls INT,
  locationLatitute DOUBLE,
  locationLongitude DOUBLE,
  recordTime STRING,
  direction STRING
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
WITH serdeproperties (
  'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
) 
STORED AS textfile
LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/***/'
TBLPROPERTIES (
-- Add a custom prefix.
    'odps.external.data.output.prefix'='test06_') 
;

-- Write data to the external table.
INSERT INTO  <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');