全部產品
Search
文件中心

:CREATE EXTERNAL TABLE

更新時間:Nov 23, 2024

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

前提條件

  • 叢集的產品系列為湖倉版

  • 叢集的核心版本為3.1.8.0及以上版本。

    說明

    查看湖倉版叢集的核心版本,請執行SELECT adb_version();。如需升級核心版本,請聯絡支援人員。

  • 已建立外部資料庫。建立外部資料庫的方法,請參見CREATE EXTERNAL DATABASE

注意事項

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

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')]

參數說明

參數

是否必填

說明

table_name (column_name column_type[, …])

定義表名和表結構。

表名和列名的命名規則,請參見命名約束

PARTITIONED BY (column_name column_type[, …])

建立分區外表時,需要配置該參數指定分區列。指定多個分區列,表示建立多級分區表。

ROW FORMAT DELIMITED FIELDS TERMINATED BY ','

指定資料行分隔符號。您可以指定任意符號,但需和檔案中的分隔字元一致。本文以英文逗號(,)為例。

重要

STORED AS TEXTFILESTORED AS JSON時,支援配置該參數。

STORED AS {TEXTFILE|ORC|PARQUET|JSON|RCFILE|HUDI}

指定檔案格式。

如果檔案是.txt或.csv格式,請配置為STORED AS TEXTFILEPARQUET格式的檔案支援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(預設值):否。

重要

該參數僅在建立分區外表時生效。

樣本

樣本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_name5個固定列。

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

參數說明

參數

是否必填

說明

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

參數說明

參數

是否必填

說明

table_name (column_name column_type[, …])

定義表名和表結構。

表名和列名的命名規則,請參見命名約束

ENGINE='MYSQL'

外表的儲存引擎。讀寫MongoDB資料時,取值為MONGODB。

TABLE_PROPERTIES

外表屬性。

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

參數說明

參數

是否必填

說明

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複雜資料類型。複雜資料類型詳情,請參見複雜資料類型

查看湖倉版叢集的核心版本,請執行SELECT adb_version();。如需升級核心版本,請聯絡支援人員。

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":"LTAILd4****",
	"endpoint":"http://service.cn-hangzhou.maxcompute.aliyun.com/api",
	"accesskey":"4A5Q7ZVzcYnWMQPysX****",
	"partition_column":"dt",
	"project_name":"test_adb",
	"table_name":"person"
}';

相關文檔