如何建立OSS外表、RDS MySQL外表、Tablestore外表、MaxCompute外表 - AnalyticDB

AnalyticDB for MySQL支援建立多種外表，包括：OSS外表、RDS MySQL外表、MongoDB外表、Tablestore外表、MaxCompute外表。

前提條件

叢集的產品系列為企業版、基礎版或湖倉版。
叢集的核心版本為3.1.8.0及以上版本。
說明
請在雲原生資料倉儲AnalyticDB MySQL控制台集群資訊頁面的配寘資訊地區，查看和升級核心版本。
已建立外部資料庫。建立外部資料庫的方法，請參見CREATE EXTERNAL DATABASE。

注意事項

僅支援跨帳號建立OSS外表。

OSS外表

重要

OSS Bucket需要與AnalyticDB for MySQL叢集位於同一地區。
建立Hudi外表和Iceberg外表、Paimon外表時，叢集核心版本需滿足如下要求：
- Hudi外表：叢集核心版本需為3.1.9.2及以上。
- Iceberg外表：叢集核心版本需為3.2.3.0及以上。
- Paimon外表：叢集核心版本需為3.2.4.0及以上。
請在雲原生資料倉儲AnalyticDB MySQL控制台集群資訊頁面的配寘資訊地區，查看和升級核心版本。
建立OSS分區外表後，請執行MSCK REPAIR TABLE語句同步外表的分區，否則將無法查詢到外表資料。
如果您需要跨帳號建立OSS外表，請在建立外部資料庫時，添加對應參數。詳細資料，請參見CREATE EXTERNAL DATABASE。

文法

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|RCFIL|HUDI|ICEBERG|PAIMON}
LOCATION 'OSS_LOCATION';
[TBLPROPERTIES (
 'type' = 'cow|mor'
 'auto.create.location' = 'true|false')
 'metadata_location' = 'METADATA_LOCATION')]

參數說明

參數	是否必填	說明
`table_name (column_name column_type[, …])`	是	定義表名和表結構。表名和列名的命名規則，請參見命名約束。重要建立Paimon外表時，表名、列名、欄欄位類型需要和Paimon檔案相同。且表名、列名必須為小寫字母。
`PARTITIONED BY (column_name column_type[, …])`	否	建立分區外表時，需要配置該參數指定分區列。指定多個分區列，表示建立多級分區表。
`ROW FORMAT DELIMITED FIELDS TERMINATED BY ','`	是	指定資料行分隔符號。您可以指定任意符號，但需和檔案中的分隔字元一致。本文以英文逗號（,）為例。重要僅`STORED AS TEXTFILE`和`STORED AS JSON`時，支援配置該參數。
`STORED AS {TEXTFILE\|ORC\|PARQUET\|JSON\|RCFILE\|HUDI\|ICEBERG}`	是	指定檔案格式。如果檔案是.txt或.csv格式，請配置為`STORED AS TEXTFILE`。 `PARQUET`格式的檔案支援STRUCT資料類型，且支援嵌套。重要僅3.1.8.0及以上核心版本的叢集支援STRUCT資料類型的`PARQUET`格式檔案。
`LOCATION`	是	指定OSS檔案或目錄所在的路徑。指定OSS目錄的路徑時，請遵循以下規則，否則可能導致查詢失敗或結果異常。目錄的路徑以`/`結尾。目錄中所有檔案的檔案格式相同。目錄中所有檔案的欄位數量、欄位順序、欄位類型相同。建立分區外表時，請指定LOCATION為分區的上一級目錄。例如，OSS檔案的路徑為`oss://testBucketname/testfolder/p1=2023-06-13/data.csv`。此時需指定`LOCATION 'oss://testBucketname/testfolder/'`，才能建立出p1為分區列的分區外表。重要建立Hudi外表時，需確保該路徑下存在Hudi中繼資料檔案，即`.hoodies`檔案。如果您已配置了`auto.create.location=true`，當建立分區外表所指定的`LOCATION`路徑不存在時，會自動建立OSS目錄。
`type`	否	Hudi外表的類型，取值： COW（預設值）：適用於對讀取效率要求高的情境。 MOR：適用於對寫入效率要求高的情境。重要僅當`STORED AS HUDI`時，需要填寫該參數。
`auto.create.location`	否	是否自動建立OSS檔案或目錄所在的路徑。取值： true：是。 false（預設值）：否。重要該參數僅在建立分區外表時生效。
`metadata_location`	否	指定Iceberg外表的Metadata檔案的路徑。重要僅當`STORED AS ICEBERG`時，需要填寫該參數。請使用最新的Metadata檔案，以確保查詢到的是最新資料。

樣本

樣本1：建立非分區外表

指定檔案儲存體格式為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';

指定檔案儲存體格式為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');

指定檔案儲存體格式為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';

指定檔案儲存體格式為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');

指定檔案儲存體格式為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/';

樣本2：建立分區外表

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/';

樣本3：建立多級分區外表

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/';

RDS MySQL外表

重要

建立RDS MySQL外表，請提前在AnalyticDB MySQL控制台的集群信息頁面開啟ENI開關。開啟和關閉ENI網路會導致資料庫連接中斷大約2分鐘，無法讀寫。請謹慎評估影響後再開啟或關閉ENI網路。
RDS MySQL執行個體需要AnalyticDB for MySQL叢集位於同一VPC。

文法

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}"]
  }';

參數說明

參數	是否必填	說明
`table_name (column_name column_type[, …])`	是	定義表名和表結構。表名和列名的命名規則，請參見命名約束。
`ENGINE='MYSQL'`	是	外表的儲存引擎。讀寫RDS MySQL資料時，取值為MYSQL。
`TABLE_PROPERTIES`	是	外表屬性。
`url`	是	RDS MySQL執行個體的內網地址、連接埠號碼和資料庫名。如何擷取RDS的內網地址，請參見查看或修改內外網地址和連接埠。
`tablename`	是	RDS MySQL的表名稱。
`username`	是	RDS MySQL資料庫的帳號。
`password`	是	RDS MySQL資料庫帳號的密碼。
`charset`	否	MySQL外表字元集，取值說明： gbk utf8（預設值） utf8mb4

樣本

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"
}';

MongoDB外表

重要

建立MongoDB外表，請提前在AnalyticDB MySQL控制台的集群信息頁面開啟ENI開關。開啟和關閉ENI網路會導致資料庫連接中斷大約2分鐘，無法讀寫。請謹慎評估影響後再開啟或關閉ENI網路。
MongoDB外表執行個體需要與AnalyticDB for MySQL叢集位於同一VPC。

文法

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",
}';

參數說明

參數	是否必填	說明
`table_name (column_name column_type[, …])`	是	定義表名和表結構。表名和列名的命名規則，請參見命名約束。
`ENGINE='MYSQL'`	是	外表的儲存引擎。讀寫MongoDB資料時，取值為MONGODB。
`TABLE_PROPERTIES`	是	外表屬性。
mapped_name	是	MongoDB集合的名稱。
location	是	MongoDB的專用網路地址。
username	是	MongoDB資料庫的帳號。說明 MongoDB需要在目標資料庫中校正資料庫的帳號和密碼，請使用MongoDB專用網路地址中指定資料庫的帳號，如遇問題，請聯絡支援人員。
password	是	MongoDB資料庫帳號的密碼。

樣本

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",
}';

Tablestore外表

重要

如果Tablestore執行個體綁定了VPC，則綁定的VPC需要與AnalyticDB for MySQL叢集所在的VPC相同。

文法

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

參數說明

參數	是否必填	說明
`table_name (column_name column_type[, …])`	是	定義表名和表結構。表名和列名的命名規則，請參見命名約束。
`ENGINE='OTS’`	是	外表的儲存引擎。讀寫Tablestore資料時，取值為OTS。
`mapped_name`	是	Tablestore執行個體中的表名稱。您可以登入Table Store控制台，在執行個體管理頁面查看Tablestore執行個體的表名稱。
`location`	是	Tablestore執行個體的VPC訪問地址。您可以登入Table Store控制台，在執行個體管理頁面查看執行個體的VPC訪問地址。

樣本

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"
}';

MaxCompute外表

重要

MaxCompute專案需要AnalyticDB for MySQL叢集位於同一地區。
如需大量建立MaxCompute外表，相關文法請參見IMPORT FOREIGN SCHEMA。

文法

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"
}';

參數說明

參數	是否必填	說明
`table_name (column_name column_type[, …])`	是	定義表名和表結構。其中，表結構需包含分區列。 table_name、column_name：表名和列名。表名和列名的命名規則，請參見命名約束。 column_type：支援MaxCompute基礎資料類型和複雜資料類型（ARRAY、MAP、STRUCT）。說明 3.2.1.0及以上版本支援MaxCompute複雜資料類型。複雜資料類型詳情，請參見複雜資料類型。請在雲原生資料倉儲AnalyticDB MySQL控制台集群資訊頁面的配寘資訊地區，查看和升級核心版本。
`ENGINE='ODPS'`	是	外表的儲存引擎。讀寫MaxCompute資料時，取值為ODPS。
`endpoint`	是	MaxCompute的EndPoint（網域名稱節點）。說明僅支援通過VPC網路Endpoint訪問MaxCompute。如何查看MaxCompute Endpoint，請參見Endpoint。
`accessid`	是	阿里雲帳號或具備MaxCompute存取權限的RAM使用者的AccessKey ID。如何擷取AccessKey ID和AccessKey Secret，請參見帳號與許可權。
`accesskey`	是	阿里雲帳號或具備MaxCompute存取權限的RAM使用者的AccessKey Secret。如何擷取AccessKey ID和AccessKey Secret，請參見帳號與許可權。
`partition_column`	否	分區列。MaxCompute表為分區表時，需要配置該參數。
`project_name`	是	MaxCompute專案的名稱。
`table_name`	是	MaxCompute的表名稱。

樣本

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"
}';

AnalyticDB：CREATE EXTERNAL TABLE

前提條件

注意事項

OSS外表

文法

參數說明

樣本

樣本1：建立非分區外表

樣本2：建立分區外表

樣本3：建立多級分區外表

RDS MySQL外表

文法

參數說明

樣本

MongoDB外表

文法

參數說明

樣本

Tablestore外表

文法

參數說明

樣本

MaxCompute外表

文法

參數說明

樣本

相關文檔