AnalyticDB for MySQL支援建立多種外表,包括:OSS外表、RDS MySQL外表、MongoDB外表、Tablestore外表、MaxCompute外表。
前提條件
叢集的產品系列為湖倉版。
叢集的核心版本為3.1.8.0及以上版本。
說明查看湖倉版叢集的核心版本,請執行
SELECT adb_version();
。如需升級核心版本,請聯絡支援人員。已建立外部資料庫。建立外部資料庫的方法,請參見CREATE EXTERNAL DATABASE。
注意事項
不支援跨帳號建立外表。
OSS外表
OSS Bucket需要與AnalyticDB for MySQL叢集位於同一地區。
僅3.1.9.2及以上核心版本的叢集支援建立Hudi外表。
查看湖倉版叢集的核心版本,請執行
SELECT adb_version();
。如需升級核心版本,請聯絡支援人員。建立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}
LOCATION 'OSS_LOCATION';
[TBLPROPERTIES (
'type' = 'cow|mor'
'auto.create.location' = 'true|false')]
參數說明
參數 | 是否必填 | 說明 |
| 是 | 定義表名和表結構。 表名和列名的命名規則,請參見命名約束。 |
| 否 | 建立分區外表時,需要配置該參數指定分區列。指定多個分區列,表示建立多級分區表。 |
| 是 | 指定資料行分隔符號。您可以指定任意符號,但需和檔案中的分隔字元一致。本文以英文逗號(,)為例。 重要 僅 |
| 是 | 指定檔案格式。 如果檔案是.txt或.csv格式,請配置為 重要 僅3.1.8.0及以上核心版本的叢集支援STRUCT資料類型的 |
| 是 | 指定OSS檔案或目錄所在的路徑。 指定OSS目錄的路徑時,請遵循以下規則,否則可能導致查詢失敗或結果異常。
建立分區外表時,請指定LOCATION為分區的上一級目錄。例如,OSS檔案的路徑為 重要
|
| 否 | Hudi外表的類型,取值:
重要 僅當 |
| 否 | 是否自動建立OSS檔案或目錄所在的路徑。取值:
重要 該參數僅在建立分區外表時生效。 |
樣本
樣本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');
重要建立Hudi外表時,會自動建立
_hoodie_commit_time
、_hoodie_commit_seqno
、_hoodie_record_key
、_hoodie_partition_path
和_hoodie_file_name
5個固定列。指定檔案儲存體格式為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';
樣本2:建立分區外表
CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.osstest4
(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.osstest5
(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開關。
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}"]
}';
參數說明
參數 | 是否必填 | 說明 |
| 是 | 定義表名和表結構。 表名和列名的命名規則,請參見命名約束。 |
| 是 | 外表的儲存引擎。讀寫RDS MySQL資料時,取值為MYSQL。 |
| 是 | 外表屬性。 |
| 是 | RDS MySQL執行個體的內網地址、連接埠號碼和資料庫名。如何擷取RDS的內網地址,請參見查看或修改內外網地址和連接埠。 |
| 是 | RDS MySQL的表名稱。 |
| 是 | RDS MySQL資料庫的帳號。 |
| 是 | RDS MySQL資料庫帳號的密碼。 |
| 否 | MySQL外表字元集,取值說明:
|
樣本
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-bp1gx6h1tyd04****.mysql.rds.aliyuncs.com:3306/test_adb",
"tablename":"person",
"username":"testUserName",
"password":"testUserPassword",
"charset":"utf8"
}';
MongoDB外表
建立MongoDB外表,請提前在AnalyticDB MySQL控制台的集群信息頁面開啟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",
}';
參數說明
參數 | 是否必填 | 說明 |
| 是 | 定義表名和表結構。 表名和列名的命名規則,請參見命名約束。 |
| 是 | 外表的儲存引擎。讀寫MongoDB資料時,取值為MONGODB。 |
| 是 | 外表屬性。 |
mapped_name | 是 | MongoDB集合的名稱。 |
location | 是 | MongoDB的專用網路地址。如何擷取專用網路的串連地址,請參見執行個體串連地址說明。 |
username | 是 | MongoDB資料庫的帳號。如何建立資料庫帳號,請參見MongoDB資料庫帳號許可權管理。 說明 MongoDB需要在目標資料庫中校正資料庫的帳號和密碼,請使用MongoDB專用網路地址中指定資料庫的帳號,如遇問題,請聯絡支援人員。 |
password | 是 | MongoDB資料庫帳號的密碼。 |
樣本
CREATE EXTERNAL TABLE adb_external_demo.person (
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"
}';
參數說明
參數 | 是否必填 | 說明 |
| 是 | 定義表名和表結構。表名和列名的命名規則,請參見命名約束。 |
| 是 | 外表的儲存引擎。讀寫Tablestore資料時,取值為OTS。 |
| 是 | Tablestore執行個體中的表名稱。您可以登入Table Store控制台,在執行個體管理頁面查看Tablestore執行個體的表名稱。 |
| 是 | 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:支援MaxCompute基礎資料類型和複雜資料類型(ARRAY、MAP、STRUCT)。 說明 3.2.1.0及以上版本支援MaxCompute複雜資料類型。複雜資料類型詳情,請參見複雜資料類型。 查看湖倉版叢集的核心版本,請執行 |
| 是 | 外表的儲存引擎。讀寫MaxCompute資料時,取值為ODPS。 |
| 是 | MaxCompute的EndPoint(網域名稱節點)。 說明 僅支援通過VPC網路Endpoint訪問MaxCompute。如何查看MaxCompute Endpoint,請參見Endpoint。 |
| 是 | 阿里雲帳號或具備MaxCompute存取權限的RAM使用者的AccessKey ID。 如何擷取AccessKey ID和AccessKey Secret,請參見帳號與許可權。 |
| 是 | 阿里雲帳號或具備MaxCompute存取權限的RAM使用者的AccessKey Secret。 如何擷取AccessKey ID和AccessKey Secret,請參見帳號與許可權。 |
| 否 | 分區列。MaxCompute表為分區表時,需要配置該參數。 |
| 是 | MaxCompute專案的名稱。 |
| 是 | 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":"LTAILd4****",
"endpoint":"http://service.cn-hangzhou.maxcompute.aliyun.com/api",
"accesskey":"4A5Q7ZVzcYnWMQPysX****",
"partition_column":"dt",
"project_name":"test_adb",
"table_name":"person"
}';
相關文檔
OSS外表:通過外表匯入OSS資料至湖倉版。
RDS MySQL外表:通過外表匯入至湖倉版。
MongoDB外表:MongoDB資料匯入。
Tablestore外表:查詢並匯入Tablestore資料。
MaxCompute外表:通過外表匯入至湖倉版。