Como criar e configurar tabelas externas no DataWorks - DataWorks: Central de Documentação do Alibaba Cloud

Crie e configure tabelas externas no DataWorks para consultar dados armazenados no OSS ou Tablestore sem carregá-los no MaxCompute.

Visão geral

A tabela a seguir descreve os principais conceitos relacionados a tabelas externas.

Parâmetro	Descrição
Object Storage Service (OSS)	O OSS oferece as classes de armazenamento Standard, Infrequent Access e Archive para atender a diferentes cenários. Ele se integra à comunidade open-source Hadoop e a outros produtos, como E-MapReduce (EMR), Batch Compute, MaxCompute, Machine Learning Platform for AI (PAI) e Function Compute.
MaxCompute	Solução de data warehouse totalmente gerenciada e de alto desempenho. A integração do MaxCompute com o OSS permite analisar e processar grandes volumes de dados de forma eficiente e econômica.
Tabela externa do MaxCompute	Com o mecanismo de computação MaxCompute V2.0, uma tabela externa permite consultar diretamente arquivos massivos no OSS sem carregar os dados em uma tabela do MaxCompute. Isso reduz o tempo e o esforço de migração de dados, além de economizar custos de armazenamento.

A figura abaixo ilustra a arquitetura geral de processamento de uma tabela externa. 架构图

O MaxCompute cria tabelas externas para dados em serviços de armazenamento não estruturado, como OSS e Tablestore. O framework de processamento não estruturado gerencia a importação e exportação de dados para o MaxCompute. O exemplo a seguir utiliza uma tabela externa do OSS para demonstrar a lógica de processamento:

O framework não estruturado transforma os dados externos do OSS e os disponibiliza ao seu código personalizado por meio da classe Java InputStream. Implemente a lógica EXTRACT para ler, analisar, transformar e computar a entrada do InputStream e retornar os dados no formato universal Record compatível com o MaxCompute.
Esses registros podem ser processados com lógica SQL no MaxCompute. Essa computação baseia-se no mecanismo SQL estruturado integrado do MaxCompute e pode gerar novos registros.
O sistema encaminha os registros processados para sua lógica OUTPUT personalizada para transformação adicional. Use o OutputStream fornecido pelo sistema para gravar as informações necessárias dos registros no OSS.

O DataWorks integrado ao MaxCompute permite criar, pesquisar, consultar, configurar, processar e analisar tabelas externas em uma interface visual.

Rede e autorização de acesso

Como o MaxCompute e o OSS são serviços independentes, a conectividade de rede entre seus clusters pode afetar o acesso aos dados. Ao acessar dados do OSS a partir do MaxCompute, use um endpoint interno do OSS, cujo endereço de host termina em -internal.aliyuncs.com.

O MaxCompute usa o Resource Access Management (RAM) e o Security Token Service (STS) do Alibaba Cloud para acessar dados do OSS com segurança. Ao solicitar permissões, o MaxCompute as obtém do STS sob a identidade do criador da tabela. As configurações de permissão para o Tablestore seguem o mesmo padrão do OSS.

Autorização STS

O MaxCompute precisa de permissões para acessar dados no OSS. O STS é um service de gerenciamento de tokens fornecido pelo RAM que permite que serviços de cloud ou usuários RAM autorizados emitam tokens de acesso temporários com períodos de validade e permissões personalizados. Aplicações que obtêm esses tokens podem chamar APIs do Alibaba Cloud para acessar recursos.

Conceda permissões de uma das seguintes formas:

Se o projeto MaxCompute e o bucket OSS pertencerem à mesma conta Alibaba Cloud, faça a autorização com um clique após fazer login com essa conta.
1. Abra a página do editor para uma nova tabela e acesse a seção Physical Model.
2. Em Table Type, selecione External Table.
3. Ao lado de Storage Address, clique em Authorize. Em seguida, clique em Authorize novamente.
4. Na caixa de diálogo Cloud Resource Access Authorization, clique em Authorize. Na página de autorização, selecione a função AliyunODPSDefaultRole, usada pelo service ODPS para acessar recursos do OSS e Tablestore. Depois, clique em Confirm Authorization.

Use a autorização personalizada para conceder acesso do MaxCompute ao OSS no console RAM.

Faça login no console RAM.

Nota
Se o MaxCompute e o OSS pertencerem a contas diferentes, faça login com a conta do OSS para conceder a autorização.
No painel de navegação à esquerda, escolha Identities > Role.
Clique em Create Role, selecione Alibaba Cloud Account como entidade confiável e clique em Next.
Insira um Role name e Remarks.

Nota
Defina o nome da função como AliyunODPSDefaultRole ou AliyunODPSRoleForOtherUser.
Em Select Trusted Alibaba Cloud Account, selecione Current Alibaba Cloud Account ou Another Alibaba Cloud Account.

Nota
Ao selecionar Another Alibaba Cloud Account, insira o ID da outra conta.
Clique em OK.

Configure os detalhes da função.

Na página de gerenciamento de funções, clique no nome da função RAM. Na aba Trust Policy, clique em Edit Trust Policy e insira o seguinte conteúdo de política conforme seu cenário.

-- If the MaxCompute project and OSS bucket belong to the same account.
{
"Statement": [
{
"Action": "sts:AssumeRole",
"Effect": "Allow",
"Principal": {
"Service": [
"odps.aliyuncs.com"
      ]
    }
  }
],
"Version": "1"
}

-- If the MaxCompute project and OSS bucket belong to different accounts.
{
"Statement": [
{
"Action": "sts:AssumeRole",
"Effect": "Allow",
"Principal": {
"Service": [
"ID of the Alibaba Cloud account that owns the MaxCompute project@odps.aliyuncs.com"
      ]
    }
  }
],
"Version": "1"
}

Após concluir a configuração, clique em Save trust policy.

Configure uma política de autorização para a função. Localize e anexe a política AliyunODPSRolePolicy, necessária para que a função acesse o OSS. Caso não encontre essa política na busca, crie uma política personalizada.

{
    "Version": "1",
    "Statement": [
        {
            "Action": [
                "oss:ListBuckets",
                "oss:GetObject",
                "oss:ListObjects",
                "oss:PutObject",
                "oss:DeleteObject",
                "oss:AbortMultipartUpload",
                "oss:ListParts"
            ],
            "Resource": "*",
            "Effect": "Allow"
        },
        {
            "Action": [
                "ots:ListTable",
                "ots:DescribeTable",
                "ots:GetRow",
                "ots:PutRow",
                "ots:UpdateRow",
                "ots:DeleteRow",
                "ots:GetRange",
                "ots:BatchGetRow",
                "ots:BatchWriteRow",
                "ots:ComputeSplitPointsBySize"
            ],
            "Resource": "*",
            "Effect": "Allow"
        },
        {
            "Action": [
                "pvtz:DescribeRegions",
                "pvtz:DescribeZones",
                "pvtz:DescribeZoneInfo",
                "pvtz:DescribeVpcs",
                "pvtz:DescribeZoneRecords"
            ],
            "Resource": "*",
            "Effect": "Allow"
        },
        {
            "Action": [
                "dlf:CreateFunction",
                "dlf:BatchGetPartitions",
                "dlf:ListDatabases",
                "dlf:CreateLock",
                "dlf:UpdateFunction",
                "dlf:BatchUpdateTables",
                "dlf:DeleteTableVersion",
                "dlf:UpdatePartitionColumnStatistics",
                "dlf:ListPartitions",
                "dlf:DeletePartitionColumnStatistics",
                "dlf:BatchUpdatePartitions",
                "dlf:GetPartition",
                "dlf:BatchDeleteTableVersions",
                "dlf:ListFunctions",
                "dlf:DeleteTable",
                "dlf:GetTableVersion",
                "dlf:AbortLock",
                "dlf:GetTable",
                "dlf:BatchDeleteTables",
                "dlf:RenameTable",
                "dlf:RefreshLock",
                "dlf:DeletePartition",
                "dlf:UnLock",
                "dlf:GetLock",
                "dlf:GetDatabase",
                "dlf:GetFunction",
                "dlf:BatchCreatePartitions",
                "dlf:ListPartitionNames",
                "dlf:RenamePartition",
                "dlf:CreateTable",
                "dlf:BatchCreateTables",
                "dlf:UpdateTableColumnStatistics",
                "dlf:ListTableNames",
                "dlf:UpdateDatabase",
                "dlf:GetTableColumnStatistics",
                "dlf:ListFunctionNames",
                "dlf:ListPartitionsByFilter",
                "dlf:GetPartitionColumnStatistics",
                "dlf:CreatePartition",
                "dlf:CreateDatabase",
                "dlf:DeleteTableColumnStatistics",
                "dlf:ListTableVersions",
                "dlf:BatchDeletePartitions",
                "dlf:ListCatalogs",
                "dlf:UpdateTable",
                "dlf:ListTables",
                "dlf:DeleteDatabase",
                "dlf:BatchGetTables",
                "dlf:DeleteFunction"
            ],
            "Resource": "*",
            "Effect": "Allow"
        }
    ]
}

Use uma source de dados OSS

Caso já tenha criado e salvo uma source de dados OSS, localize seu workspace na página Workspace list, clique em Operation na coluna Management e visualize e use a source de dados na página Data Source.

Criar uma tabela externa

Crie uma tabela usando uma instrução DDL

Acesse a página Data Studio. Para mais informações sobre como criar uma tabela com instrução DDL, consulte Create and use MaxCompute tables. Basta seguir a sintaxe padrão do MaxCompute. Se a autorização do service STS tiver sido concluída com sucesso, não será necessário definir a propriedade odps.properties.rolearn.

A instrução DDL a seguir cria uma tabela externa. A palavra-chave EXTERNAL especifica que se trata de uma tabela externa.

CREATE EXTERNAL TABLE IF NOT EXISTS ambulance_data_csv_external(
vehicleId int,
recordId int,
patientId int,
calls int,
locationLatitute double,
locationLongtitue double,
recordTime string,
direction string
)
STORED BY 'com.aliyun.odps.udf.example.text.TextStorageHandler' -- Required. The STORED BY clause specifies the name of a custom StorageHandler class or another file format for the external table.
with SERDEPROPERTIES (
'delimiter'='\\|', -- Optional. The SERDEPROPERTIES clause specifies the parameters used for data serialization and deserialization. These parameters can be passed to the Extractor logic through DataAttributes.
'odps.properties.rolearn'='acs:ram::xxxxxxxxxxxxx:role/aliyunodpsdefaultrole'
)
LOCATION 'oss://oss-cn-shanghai-internal.aliyuncs.com/oss-odps-test/Demo/SampleData/CustomTxt/AmbulanceData/'     -- Required. The LOCATION clause specifies the storage location of the external table.
USING 'odps-udf-example.jar'; -- Specifies the JAR package that contains the class definition when you use a custom format. This clause is not required if you do not use a custom format.

A cláusula STORED BY também pode especificar um StorageHandler integrado para arquivos CSV ou TSV:

CSV: com.aliyun.odps.CsvStorageHandler. Este handler define como ler e gravar dados formatados em CSV. Por convenção, o delimitador de coluna é vírgula (,) e a quebra de linha é o caractere de nova linha (\n). Exemplo: STORED BY 'com.aliyun.odps.CsvStorageHandler'.
TSV: com.aliyun.odps.TsvStorageHandler. Este handler define como ler e gravar dados formatados em TSV. Por convenção, o delimitador de coluna é tabulação (\t) e a quebra de linha é o caractere de nova linha (\n).

A cláusula STORED BY também suporta external tables for open-source formats, como ORC, PARQUET, SEQUENCEFILE, RCFILE, AVRO e TEXTFILE. Para TEXTFILE, especifique uma classe de serialização, como org.apache.hive.hcatalog.data.JsonSerDe.

org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe -> stored as textfile
org.apache.hadoop.hive.ql.io.orc.OrcSerde -> stored as orc
org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe -> stored as parquet
org.apache.hadoop.hive.serde2.avro.AvroSerDe -> stored as avro
org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe -> stored as sequencefile

Use a seguinte instrução DDL para criar uma tabela externa para formatos open-source.

  CREATE EXTERNAL TABLE [IF NOT EXISTS] (<column schemas>)
  [PARTITIONED BY (partition column schemas)]
  [ROW FORMAT SERDE '']
  STORED AS 
  [WITH SERDEPROPERTIES ( 'odps.properties.rolearn'='${roleran}'
  [,'name2'='value2',...]
  ) ]
  LOCATION 'oss://${endpoint}/${bucket}/${userfilePath}/';

A tabela a seguir descreve as propriedades SerDe.

Parâmetro	Valor	Padrão	Descrição
odps.text.option.gzip.input.enabled	true/false	false	Ativa ou desativa a compressão de leitura.
odps.text.option.gzip.output.enabled	true/false	false	Ativa ou desativa a compressão de gravação.
odps.text.option.header.lines.count	Um inteiro não negativo	0	Ignora as primeiras N linhas do cabeçalho do arquivo de texto.
odps.text.option.null.indicator	Uma string	Uma string vazia	String que representa um valor NULL durante análise ou gravação.
odps.text.option.ignore.empty.lines	true/false	true	Define se linhas vazias devem ser ignoradas.
odps.text.option.encoding	UTF-8/UTF-16/US-ASCII	UTF-8	Especifica a codificação de caracteres do texto.

Nota

O extrator integrado do MaxCompute suporta apenas a leitura de dados CSV ou TSV compactados com gzip a partir do OSS. A necessidade de definir propriedades depende se o arquivo está ou não compactado com gzip.

O parâmetro LOCATION segue o formato: oss://oss-cn-shanghai-internal.aliyuncs.com/BucketName/DirectoryName. Selecione o endereço do diretório OSS em uma caixa de diálogo GUI. Não é necessário adicionar um nome de arquivo após o diretório.

Tabelas criadas no modo DDL aparecem na árvore de nós de tabela sob o gerenciamento de tabelas. Altere sua posição de exibição modificando os temas de nível 1 e nível 2.

Tabela externa do Tablestore

Use a seguinte instrução para criar uma tabela externa do Tablestore.

CREATE EXTERNAL TABLE IF NOT EXISTS ots_table_external(
odps_orderkey bigint,
odps_orderdate string,
odps_custkey bigint,
odps_orderstatus string,
odps_totalprice double
)
STORED BY 'com.aliyun.odps.TableStoreStorageHandler' 
WITH SERDEPROPERTIES (
'tablestore.columns.mapping'=':o_orderkey,:o_orderdate,o_custkey, o_orderstatus,o_totalprice', -- (3)
'tablestore.table.name'='ots_tpch_orders'
'odps.properties.rolearn'='acs:ram::xxxxx:role/aliyunodpsdefaultrole'
)
LOCATION 'tablestore://odps-ots-dev.cn-shanghai.ots-internal.aliyuncs.com';

Parâmetros:

com.aliyun.odps.TableStoreStorageHandler é o StorageHandler integrado do MaxCompute para processar dados do Tablestore.
A cláusula SERDEPROPERTIES especifica opções de parâmetros. Ao usar TableStoreStorageHandler, defina duas opções obrigatórias: tablestore.columns.mapping e tablestore.table.name.
- tablestore.columns.mapping: Opção obrigatória que descreve as colunas da tabela Tablestore acessadas pelo MaxCompute, incluindo chaves primárias e atributos. O prefixo dois pontos (:) indica uma chave primária do Tablestore. Nesta instrução, por exemplo, :o_orderkey e :o_orderdate são chaves primárias, enquanto as demais são colunas de atributo.
  
  O Tablestore suporta de uma a quatro chaves primárias dos tipos STRING, INTEGER ou BINARY. A primeira chave primária atua como chave de partição. Ao definir o mapeamento, forneça todas as chaves primárias da tabela Tablestore especificada. Para colunas de atributo, informe apenas aquelas que o MaxCompute precisa acessar.
- tablestore.table.name: Nome da tabela Tablestore a ser acessada. Se o nome especificado estiver incorreto ou não existir, o MaxCompute retornará um erro. O MaxCompute não cria automaticamente uma tabela no Tablestore.
LOCATION: Especifica o nome da instância do Tablestore, endpoint e outros detalhes.

Crie uma tabela pela interface gráfica

Acesse a página Data Studio e crie uma tabela pela interface gráfica. Para mais informações, consulte Create and use MaxCompute tables. Uma tabela externa possui as seguintes propriedades:

General
- Nome da tabela em inglês (inserido durante Create Table)
- Display Name
- Level-1 Theme e Level-2 Theme
- Description
Physical Model
- Table Type: Selecione External Table.
- Partition Type: Tabelas externas do Tablestore não suportam particionamento.
- Storage Address: Corresponde ao parâmetro LOCATION. Defina o parâmetro LOCATION na seção Physical Model. Clique em Select para escolher um endereço de armazenamento. Após a seleção, clique em Authorize.
- File Format: Escolha um formato conforme suas necessidades de negócio. Os formatos suportados incluem CSV, TSV, ORC, PARQUET, SEQUENCEFILE, RCFILE, AVRO, TEXTFILE e formatos de arquivo personalizados. Ao selecionar um formato personalizado, escolha um recurso personalizado. Durante o envio do recurso, os nomes de classe incluídos são analisados automaticamente e disponibilizados para seleção.
- rolearn: Deixe este parâmetro vazio se a autorização STS já estiver concluída.

Schema: Na seção Schema, adicione os seguintes campos: age (BIGINT, idade), job (STRING, tipo de trabalho), marital (STRING, estado civil), education (STRING, nível de escolaridade) e default (STRING, status do cartão de crédito). Nenhum desses campos é chave primária.

Parâmetro	Descrição
Field Type	O MaxCompute 2.0 suporta diversos tipos de dados simples e complexos.
Operation	Permite adicionar, modificar e excluir campos.
Definition or Maximum Value Length	Para o tipo VARCHAR, defina o comprimento. Para tipos de dados complexos, insira diretamente a definição do tipo.

Tipos de dados suportados

Tabelas externas suportam os seguintes tipos de dados simples.

Tipo	Novo	Exemplo	Descrição
TINYINT	Sim	1Y, -127Y	Inteiro assinado de 8 bits. Varia de -128 a 127.
SMALLINT	Sim	32767S, -100S	Inteiro assinado de 16 bits. Varia de -32.768 a 32.767.
INT	Sim	1000, -15645787	Inteiro assinado de 32 bits. Varia de -2³¹ a 2³¹-1.
BIGINT	Não	100000000000L, -1L	Inteiro assinado de 64 bits. Varia de -2⁶³ a 2⁶³-1.
FLOAT	Sim	N/A	Número de ponto flutuante binário de 32 bits.
DOUBLE	Não	3,1415926 1E+7	Número de ponto flutuante binário de 64 bits, dupla precisão e 8 bytes.
DECIMAL	Não	3,5BD, 99999999999,9999999BD	Tipo numérico exato de base 10. A parte inteira varia de -10³⁶+1 a 10³⁶-1, e a parte fracionária tem precisão de 10 a 18 dígitos.
VARCHAR(n)	Sim	N/A	Tipo de caractere de comprimento variável, onde n é o tamanho. O valor de n varia de 1 a 65.535.
STRING	Não	"abc", 'bcd', "alibaba"	Tipo string. Comprimento máximo de 8 MB.
BINARY	Sim	N/A	Tipo de dado binário. Comprimento máximo de 8 MB.
DATETIME	Não	DATETIME '2017-11-11 00:00:00'	Tipo de data e hora que usa UTC+8 como horário padrão do sistema. Varia de 0000-01-01 a 9999-12-31, com precisão de milissegundos.
TIMESTAMP	Sim	TIMESTAMP '2017-11-11 00:00:00.123456789'	Tipo timestamp independente de fuso horário. Varia de 0000-01-01 00:00:00.000000000 a 9999-12-31 23:59:59.999999999, com precisão de nanossegundos.
BOOLEAN	Não	TRUE e FALSE	Tipo BOOLEAN, que pode ser TRUE ou FALSE.

Tabelas externas também suportam os seguintes tipos de dados complexos.

Tipo	Definição	Construtor
ARRAY	array< int >; array< struct< a:int, b:string >>	array(1, 2, 3); array(array(1, 2); array(3, 4))
MAP	map< string, string >; map< smallint, array< string>>	map("k1", "v1", "k2", "v2"); map(1S, array('a', 'b'), 2S, array('x', 'y'))
STRUCT	struct< x:int, y:int>; struct< field1:bigint, field2:array< int>, field3:map< int, int>>	named_struct('x', 1, 'y', 2); named_struct('field1', 100L, 'field2', array(1, 2), 'field3', map(1, 100, 2, 200))

Para usar os novos tipos de dados suportados pelo MaxCompute 2.0, como TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, BINARY ou tipos complexos, adicione a instrução set odps.sql.type.system.odps2=true; antes da instrução de criação da tabela. Envie a instrução set juntamente com a instrução de criação da tabela. Para compatibilidade com Hive, adicione a instrução set odps.sql.hive.compatible=true;.

Visualizar e gerenciar tabelas externas

Acesse a página Data Studio e clique em Table Management no painel de navegação à esquerda para consultar tabelas externas. Para mais informações, consulte Table management. O gerenciamento de tabelas externas segue o mesmo processo de tabelas internas.