Create OSS, RDS for MySQL, Tablestore, and MaxCompute external tables - AnalyticDB: Central de Documentação do Alibaba Cloud

AnalyticDB for MySQL permite criar diversos tipos de tabelas externas, incluindo tabelas externas do OSS, do RDS for MySQL, do ApsaraDB for MongoDB, do Tablestore e do MaxCompute.

Pré-requisitos

Crie um cluster do AnalyticDB for MySQL Enterprise Edition, Basic Edition ou Data Lakehouse Edition.
A versão secundária do cluster deve ser 3.1.8.0 ou posterior.

Nota
Para visualizar e atualizar a versão secundária, acesse a seção Configuration Information na página Cluster Information no console do AnalyticDB for MySQL.
Crie um banco de dados externo antes de prosseguir. Para mais informações, consulte CREATE EXTERNAL DATABASE.

Observações de uso

É possível criar tabelas externas do OSS entre diferentes contas da Alibaba Cloud.

Tabelas externas do OSS

Importante

O bucket do OSS deve estar na mesma região do cluster AnalyticDB for MySQL.
Para criar uma tabela externa Hudi, Iceberg ou Paimon, o cluster deve atender aos seguintes requisitos de versão secundária:
- Tabela externa Hudi: versão secundária 3.1.9.2 ou posterior.
- Tabela externa Iceberg: versão secundária 3.2.3.0 ou posterior.
- Tabela externa Paimon: versão secundária 3.2.6.1 ou posterior.
Para visualizar e atualizar a versão secundária, acesse a seção Configuration Information na página Cluster Information no console do AnalyticDB for MySQL.
Após criar uma tabela externa particionada do OSS, execute a instrução MSCK REPAIR TABLE para sincronizar as partições. Caso contrário, não será possível consultar dados na tabela externa.
Para criar uma tabela externa do OSS entre contas da Alibaba Cloud, especifique os parâmetros necessários durante a criação do banco de dados externo. Para mais detalhes, consulte CREATE EXTERNAL DATABASE.

Sintaxe

CREATE EXTERNAL TABLE [IF NOT EXISTS] table_name
(column_name column_type[, …])
[PARTITIONED BY (column_name column_type[, …])]
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS {TEXTFILE|ORC|PARQUET|JSON|RCFILE|HUDI|ICEBERG|PAIMON}
LOCATION 'OSS_LOCATION'
[TBLPROPERTIES (
 'type' = 'cow|mor',
 'auto.create.location' = 'true|false',
 'metadata_location' = 'METADATA_LOCATION'
)];

Parâmetros

Parâmetro	Obrigatório	Descrição
`table_name (column_name column_type[, …])`	Sim	Define o nome e o schema da tabela. Para obter informações sobre as regras de nomenclatura de tabelas e colunas, consulte Convenções de nomenclatura. Importante Ao criar uma tabela externa Paimon, o nome da tabela, os nomes das colunas e os tipos de dados das colunas devem corresponder exatamente aos definidos nos arquivos Paimon. Se houver divergência no schema da tabela (como nomes ou tipos de colunas), o schema do Paimon terá precedência.
`PARTITIONED BY (column_name column_type[, …])`	Não	Especifica a coluna chave de partição. Este parâmetro é obrigatório para criar tabelas externas particionadas. Para tabelas com múltiplos níveis de partição, defina várias colunas chave.
`ROW FORMAT DELIMITED FIELDS TERMINATED BY ','`	Sim	Define o delimitador de colunas. É possível usar qualquer caractere, desde que corresponda ao delimitador presente no arquivo de dados. Este tópico utiliza a vírgula (,) como exemplo. Importante Este parâmetro tem suporte apenas quando `STORED AS TEXTFILE` ou `STORED AS JSON` for especificado.
`STORED AS {TEXTFILE\|ORC\|PARQUET\|JSON\|RCFILE\|HUDI\|ICEBERG\|PAIMON}`	Sim	Indica o formato do arquivo. Se o formato for .txt ou .csv, defina este parâmetro como `STORED AS TEXTFILE`. Arquivos no formato `PARQUET` oferecem suporte ao tipo de dado STRUCT, incluindo tipos aninhados. Importante Apenas clusters com versão secundária 3.1.8.0 ou posterior suportam arquivos `PARQUET` que utilizam o tipo de dado STRUCT.
`LOCATION`	Sim	Especifica o caminho do arquivo ou diretório no OSS. Ao definir um caminho de diretório do OSS, siga estas regras para evitar falhas na consulta ou resultados inesperados: O caminho do diretório deve terminar com uma barra (`/`). Todos os arquivos dentro do diretório devem ter o mesmo formato. Os arquivos do diretório devem possuir a mesma quantidade de campos, ordem dos campos e tipos de dados. Na criação de uma tabela externa particionada, defina LOCATION como o diretório pai das partições. Por exemplo, se o caminho de um arquivo no OSS for `oss://testBucketname/testfolder/p1=2023-06-13/data.csv`, você deve especificar `LOCATION 'oss://testBucketname/testfolder/'` para criar uma tabela externa particionada onde p1 é a coluna chave de partição. Importante Ao criar uma tabela externa Hudi, certifique-se de que o arquivo de metadados Hudi, chamado `.hoodie`, exista no caminho especificado. Se você definir `auto.create.location=true` e o caminho `LOCATION` não existir durante a criação de uma tabela externa particionada, um diretório no OSS será criado automaticamente.
`type`	Não	Tipo da tabela externa Hudi. Valores válidos: COW (padrão): Copy on Write. Ideal para cargas de trabalho com predominância de leitura. MOR: Merge on Read. Recomendado para cenários com alta frequência de escrita. Importante Este parâmetro é necessário somente quando `STORED AS HUDI` for especificado.
`auto.create.location`	Não	Determina se o caminho do arquivo ou diretório no OSS deve ser criado automaticamente. Valores válidos: true: Cria o caminho automaticamente. false (padrão): Não cria o caminho automaticamente. Importante Este parâmetro só tem efeito na criação de tabelas externas particionadas.
`metadata_location`	Não	Define o caminho do arquivo de metadados para a tabela externa Iceberg. Importante Parâmetro obrigatório apenas quando `STORED AS ICEBERG` for utilizado. Utilize sempre o arquivo de metadados mais recente para garantir a consulta dos dados mais atuais.

Exemplos

Exemplo 1: Criar uma tabela externa não particionada

Crie uma tabela externa armazenada como TEXTFILE.

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.osstest1
(id INT,
name STRING,
age INT,
city STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY  ','
STORED AS TEXTFILE
LOCATION  'oss://testBucketName/osstest/p1=hangzhou/p2=2023-06-13/data.csv';

Crie uma tabela externa armazenada como HUDI.

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.osstest2
(id INT,
name STRING,
age INT,
city STRING)
STORED AS HUDI
LOCATION  'oss://testBucketName/osstest/test'
TBLPROPERTIES ('type' = 'cow');

Crie uma tabela externa armazenada como PARQUET.

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.osstest3
(
A STRUCT < var1:STRING, var2:INT >) 
STORED AS PARQUET 
LOCATION 'oss://testBucketName/osstest/Parquet';

Crie uma tabela externa armazenada como ICEBERG.

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.osstest4
(
user_id BIGINT)  
STORED AS ICEBERG  
LOCATION 'oss://testBucketName/osstest/no_partition_table/' 
TBLPROPERTIES (metadata_location='oss://testBucketName/osstest/no_partition_table/metadata/00000-a32d6136-8490-4ad2-ada3-fe2f7204199f.metadata.json');

Crie uma tabela externa armazenada como PAIMON.

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.osstest5
(
a INT,
b BIGINT,
aCa STRING, 
d VARCHAR(1))
STORED AS PAIMON 
LOCATION 'oss://testBucketName/osstest/default.db/t1/';

Exemplo 2: Criar uma tabela externa particionada

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.osstest6
(id int,
name string,
age int,
city string)
PARTITIONED BY (p2 string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY  ','
STORED AS TEXTFILE
LOCATION  'oss://testBucketName/osstest/p1=hangzhou/';

Exemplo 3: Criar uma tabela externa com múltiplos níveis de partição

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.osstest7
(id int,
name string,
age int,
city string)
PARTITIONED BY (p1 string,p2 string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY  ','
STORED AS TEXTFILE
LOCATION  'oss://testBucketName/osstest/';

Tabelas externas do RDS for MySQL

Importante

Para criar uma tabela externa do RDS for MySQL, ative primeiro o ENI na página Cluster Information no console do AnalyticDB for MySQL. A ativação ou desativação do ENI causa uma interrupção na conexão com o banco de dados por cerca de dois minutos, período em que operações de leitura e escrita falharão. Avalie cuidadosamente o impacto potencial antes de habilitar ou desabilitar o ENI.
A instância do RDS for MySQL deve estar na mesma VPC do cluster AnalyticDB for MySQL.

Sintaxe

CREATE EXTERNAL TABLE [IF NOT EXISTS] table_name
(column_name column_type[, …])
ENGINE='MYSQL'
TABLE_PROPERTIES='{  
	"url":"mysql_vpc_address",  
	"tablename":"mysql_table_name",  
	"username":"mysql_user_name",  
	"password":"mysql_user_password"
	[,"charset":"{gbk|utf8|utf8mb4}"]
  }';

Parâmetros

Parâmetro	Obrigatório	Descrição
`table_name (column_name column_type[, …])`	Sim	Define o nome e o schema da tabela. Para obter informações sobre as regras de nomenclatura de tabelas e colunas, consulte Convenções de nomenclatura.
`ENGINE='MYSQL'`	Sim	Motor de armazenamento da tabela externa. Para ler e gravar dados no RDS for MySQL, defina este valor como MYSQL.
`TABLE_PROPERTIES`	Sim	Propriedades da tabela externa.
`url`	Sim	Endpoint da VPC, número da porta e nome do banco de dados da instância RDS for MySQL. Para saber como obter o endpoint da VPC, consulte Visualizar e alterar endpoints e números de porta de uma instância ApsaraDB RDS for MySQL.
`tablename`	Sim	Nome da tabela no banco de dados RDS for MySQL.
`username`	Sim	Conta de acesso ao banco de dados RDS for MySQL.
`password`	Sim	Senha da conta do banco de dados RDS for MySQL.
`charset`	Não	Conjunto de caracteres da tabela externa MySQL. Valores válidos: gbk UTF8 (padrão) utf8mb4

Exemplo

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.mysqltest (
	id int,
	name varchar(1023),
	age int
 ) ENGINE = 'MYSQL'
 TABLE_PROPERTIES = '{
   "url":"jdbc:mysql://rm-bp1gx6********.mysql.rds.aliyuncs.com:3306/test_adb",
   "tablename":"person",
   "username":"testUserName",
   "password":"testUserPassword",
   "charset":"utf8"
}';

Tabelas externas do ApsaraDB for MongoDB

Importante

Para criar uma tabela externa do ApsaraDB for MongoDB, ative primeiro o ENI na página Cluster Information no console do AnalyticDB for MySQL. A ativação ou desativação do ENI causa uma interrupção na conexão com o banco de dados por cerca de dois minutos, período em que operações de leitura e escrita falharão. Avalie cuidadosamente o impacto potencial antes de habilitar ou desabilitar o ENI.
A instância do ApsaraDB for MongoDB deve estar na mesma VPC do cluster AnalyticDB for MySQL.

Sintaxe

CREATE EXTERNAL TABLE [IF NOT EXISTS] table_name
(column_name column_type[, …])
ENGINE='MONGODB'
TABLE_PROPERTIES = '{
	"mapped_name":"table",
  "location":"location",
  "username":"user",
  "password":"password",
}';

Parâmetros

Parâmetro	Obrigatório	Descrição
`table_name (column_name column_type[, …])`	Sim	Define o nome e o schema da tabela. Para obter informações sobre as regras de nomenclatura de tabelas e colunas, consulte Convenções de nomenclatura.
`ENGINE='MONGODB'`	Sim	Motor de armazenamento da tabela externa. Para ler e gravar dados no ApsaraDB for MongoDB, defina este valor como MONGODB.
`TABLE_PROPERTIES`	Sim	Propriedades da tabela externa.
mapped_name	Sim	Nome da coleção no MongoDB.
location	Sim	O endpoint da VPC da instância ApsaraDB for MongoDB.
username	Sim	A conta do banco de dados ApsaraDB for MongoDB. Nota O ApsaraDB for MongoDB valida a conta e a senha no banco de dados de destino. Utilize a conta do banco de dados especificado no endpoint da VPC. Em caso de problemas, entre em contato com o suporte técnico.
password	Sim	Senha da conta do banco de dados ApsaraDB for MongoDB.

Exemplo

CREATE EXTERNAL TABLE adb_external_demo.mongodbtest (
  id int,
  name string,
  age int
) ENGINE = 'MONGODB' TABLE_PROPERTIES ='{
"mapped_name":"person",
"location":"mongodb://testuser:****@dds-bp113d414bca8****.mongodb.rds.aliyuncs.com:3717,dds-bp113d414bca8****.mongodb.rds.aliyuncs.com:3717/test_mongodb",
"username":"testuser",
"password":"password",
}';

Tabelas externas do Tablestore

Importante

Se a instância do Tablestore utilizar uma VPC, ela deverá ser a mesma VPC do seu cluster AnalyticDB for MySQL.

Sintaxe

CREATE EXTERNAL TABLE [IF NOT EXISTS] table_name
(column_name column_type[, …])
ENGINE='OTS'
TABLE_PROPERTIES = '{
	"mapped_name":"table_name",
	"location":"tablestore_vpc_address"
}';

Parâmetros

Parâmetro	Obrigatório	Descrição
`table_name (column_name column_type[, …])`	Sim	Define o nome e o schema da tabela. Para obter informações sobre as regras de nomenclatura de tabelas e colunas, consulte Convenções de nomenclatura.
`ENGINE='OTS'`	Sim	Motor de armazenamento da tabela externa. Para ler e gravar dados no Tablestore, defina este valor como OTS.
`mapped_name`	Sim	Nome da tabela na instância do Tablestore. Faça login no console do Tablestore e localize o nome da tabela na página Instance Management.
`location`	Sim	Endpoint da VPC da instância do Tablestore. Faça login no console do Tablestore e encontre o endpoint da VPC da instância na página Instance Management.

Exemplo

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.otstest (
	id int,
	name string,
	age int
) ENGINE = 'OTS' 
TABLE_PROPERTIES = '{
	"mapped_name":"person",
	"location":"https://w0****la.cn-hangzhou.vpc.tablestore.aliyuncs.com"
}';

Tabelas externas do MaxCompute

Importante

O projeto do MaxCompute deve estar na mesma região do cluster AnalyticDB for MySQL.
Para criar tabelas externas do MaxCompute em lote, consulte IMPORT FOREIGN SCHEMA.

Sintaxe

CREATE EXTERNAL TABLE [IF NOT EXISTS] table_name
(column_name column_type[, …])
ENGINE='ODPS'
TABLE_PROPERTIES='{
	"endpoint":"endpoint",
	"accessid":"accesskey_id",
	"accesskey":"accesskey_secret",
	["partition_column":"partition_column"],
	"project_name":"project_name",
	"table_name":"table_name",
	["auto_refresh":"true|false"],
	["auto_refresh_mode":"add_only|full"]
}';

Parâmetros

Parâmetro	Obrigatório	Descrição
`table_name (column_name column_type[, …])`	Sim	Define o nome e o schema da tabela. O schema da tabela deve incluir a coluna chave de partição. `table_name` e `column_name` especificam o nome da tabela e da coluna. Para obter informações sobre as regras de nomenclatura de tabelas e colunas, consulte Convenções de nomenclatura. `column_type` suporta tipos de dados básicos e complexos do MaxCompute, como ARRAY, MAP e STRUCT. Nota A versão secundária 3.2.1.0 e posteriores oferece suporte a tipos de dados complexos do MaxCompute. Para mais detalhes sobre tipos de dados complexos, consulte Tipos de dados complexos. Para visualizar e atualizar a versão secundária de um cluster AnalyticDB for MySQL, faça login no console do AnalyticDB for MySQL e acesse a seção Configuration Information na página Cluster Information.
`ENGINE='ODPS'`	Sim	Motor de armazenamento da tabela externa. Para ler e gravar dados no MaxCompute, defina este valor como ODPS.
`endpoint`	Sim	Endpoint do service MaxCompute. Nota O acesso ao MaxCompute ocorre exclusivamente via endpoint de VPC. Para visualizar o endpoint do MaxCompute, consulte Endpoints.
`accessid`	Sim	AccessKey ID de uma conta Alibaba Cloud ou usuário RAM com permissões para acessar o MaxCompute. Para obter um AccessKey ID e um AccessKey secret, consulte Obter um par de AccessKey.
`accesskey`	Sim	AccessKey secret da conta Alibaba Cloud ou usuário RAM. Para obter um AccessKey ID e um AccessKey secret, consulte Obter um par de AccessKey.
`partition_column`	Não	Coluna chave de partição. Este parâmetro é obrigatório se a tabela do MaxCompute for particionada.
`project_name`	Sim	Nome do projeto no MaxCompute.
`table_name`	Sim	Nome da tabela no projeto MaxCompute.
`auto_refresh`	Não	Define se a atualização automática de schema deve ser ativada para a tabela externa atual do MaxCompute. Valores válidos: true: ativa o recurso. false (padrão): desativa o recurso. Importante Suportado apenas em clusters com versão secundária 3.2.7.0 ou posterior. Antes de ativar este recurso, configure a chave de nível de cluster `external_table_schema_refresh_enabled`. Para mais informações, consulte Configuração de nível de cluster neste tópico.
`auto_refresh_mode`	Não	Modo de atualização do schema. Este parâmetro só tem efeito quando `auto_refresh=true`. Valores válidos: add_only (padrão): sincroniza automaticamente apenas as colunas adicionadas à tabela de source no MaxCompute. Se colunas forem removidas ou seus tipos alterados na source, apenas um aviso será registrado e os metadados da tabela externa não serão modificados automaticamente. full: sincroniza automaticamente adições, remoções e alterações de tipo de colunas da tabela de source do MaxCompute, mantendo o schema da tabela externa consistente com a source. Importante Suportado apenas em clusters com versão secundária 3.2.7.0 ou posterior.

Exemplo

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.mctest (
	id int,
	name varchar(1023),
	age int,
	dt string
) ENGINE='ODPS'
TABLE_PROPERTIES='{
	"accessid":"LTAI****************",
	"endpoint":"http://service.cn-hangzhou.maxcompute.aliyun.com/api",
	"accesskey":"yourAccessKeySecret",
	"partition_column":"dt",
	"project_name":"test_adb",
	"table_name":"person"
}';

Atualização automática de schema (auto refresh)

As tabelas externas do MaxCompute suportam atualização automática de schema. Quando ativada, as alterações de schema na tabela de source do MaxCompute são sincronizadas automaticamente para a tabela externa a cada ciclo de atualização, sem necessidade de recriar a tabela externa ou atualizá-la manualmente. A ativação e o escopo da atualização são controlados pelos parâmetros auto_refresh e auto_refresh_mode, definidos durante a criação da tabela. Para mais detalhes, consulte Parâmetros acima.

Configuração de nível de cluster

Antes de utilizar a configuração de nível de tabela auto_refresh=true para ativar a atualização automática de schema, execute as instruções abaixo para habilitar a chave de atualização automática no nível do cluster.

-- Enable the automatic schema refresh feature.
SET ADB_CONFIG external_table_schema_refresh_enabled=true;

-- The polling interval for automatic schema refresh, in milliseconds. Default value: 300000 (5 minutes). This configuration takes effect after you restart the cluster.
SET ADB_CONFIG external_table_schema_refresh_poll_interval_ms=300000;

Exemplos

O exemplo a seguir cria uma tabela externa do MaxCompute com atualização automática de schema ativada no modo add_only:

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.mctest_add_only (
	id BIGINT,
	name VARCHAR(1024),
	amount DOUBLE
) ENGINE='ODPS'
TABLE_PROPERTIES='{
	"accessid":"LTAI****************",
	"accesskey":"yourAccessKeySecret",
	"endpoint":"https://service.cn-hangzhou-vpc.maxcompute.aliyun-inc.com/api",
	"project_name":"test_adb",
	"table_name":"person",
	"auto_refresh":"true",
	"auto_refresh_mode":"add_only"
}';

Quando uma coluna for adicionada à tabela de source do MaxCompute, a tabela externa incluirá automaticamente a coluna correspondente após a próxima atualização. Colunas removidas ou alterações de tipo na source não são aplicadas automaticamente.

O exemplo abaixo cria uma tabela externa do MaxCompute com atualização automática de schema ativada no modo full:

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.mctest_full (
	id BIGINT,
	name VARCHAR(1024),
	amount DOUBLE
) ENGINE='ODPS'
TABLE_PROPERTIES='{
	"accessid":"LTAI****************",
	"accesskey":"yourAccessKeySecret",
	"endpoint":"https://service.cn-hangzhou-vpc.maxcompute.aliyun-inc.com/api",
	"project_name":"test_adb",
	"table_name":"person",
	"auto_refresh":"true",
	"auto_refresh_mode":"full"
}';

Caso colunas sejam adicionadas, removidas ou tenham seus tipos alterados na tabela de source do MaxCompute, a tabela externa sincronizará os metadados após a próxima atualização.

Observações de uso

A atualização automática de schema é suportada apenas para tabelas externas do MaxCompute que utilizam ENGINE='ODPS'.
O modo add_only é o padrão e é indicado para ambientes de produção onde apenas adições de colunas são permitidas.
O modo full sincroniza automaticamente remoções de colunas e alterações de tipo, o que pode impactar instruções SQL, aplicações, relatórios ou views dependentes das colunas antigas. Utilize este modo com cautela.
Se você criar uma view utilizando * na tabela externa do MaxCompute (por exemplo, CREATE VIEW view_name AS SELECT * FROM external_table), o * será expandido e salvo como uma lista fixa de colunas no momento da criação da view. Caso o modo full sincronize uma coluna removida ou renomeada da source e essa alteração envolva uma coluna salva na view, a consulta à view retornará um erro indicando que a view está desatualizada e precisa ser recriada. Esse comportamento é esperado. Recomendamos evitar o uso de * em views. Para mais informações, consulte CREATE VIEW.

Documentação relacionada

Tabelas externas do OSS: Importar dados do OSS usando tabelas externas.
Tabelas externas do RDS for MySQL: Importar dados do RDS for MySQL usando tabelas externas.
Tabelas externas do ApsaraDB for MongoDB: Importar dados do ApsaraDB for MongoDB usando tabelas externas.
Tabelas externas do Tablestore: Consultar e importar dados do Tablestore.
Tabelas externas do MaxCompute: Importar dados do MaxCompute usando tabelas externas.