AnalyticDB：CREATE EXTERNAL TABLE

文法

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

參數說明

樣本

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

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

文法

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

參數說明

樣本

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

文法

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

參數說明

樣本

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

文法

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

參數說明

樣本

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

文法

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

參數說明

樣本

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

Schema 自動重新整理（auto refresh）

MaxCompute外表支援Schema自動重新整理：開啟後，會按重新整理周期自動將MaxCompute源表的表結構變化同步到外表，無需重建外表或手動重新整理。是否開啟及重新整理範圍由建表時的auto_refresh、auto_refresh_mode參數控制，參見上文參數說明。

叢集級配置

在使用表級auto_refresh=true開啟Schema自動重新整理前，需先執行以下命令開啟叢集級自動重新整理開關。

-- 開啟Schema自動重新整理功能。
SET ADB_CONFIG external_table_schema_refresh_enabled=true;

-- Schema自動重新整理的輪詢間隔，單位為毫秒，預設值為300000（即5分鐘）。該配置需重啟叢集後生效。
SET ADB_CONFIG external_table_schema_refresh_poll_interval_ms=300000;

樣本

以下樣本建立一張開啟Schema自動重新整理、重新整理模式為add_only的MaxCompute外表：

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

MaxCompute源表新增列後，外表會在下一輪重新整理後自動新增對應列；源表刪除列或列類型變化不會自動應用。

以下樣本建立一張開啟Schema自動重新整理、重新整理模式為full的MaxCompute外表：

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

MaxCompute源表新增列、刪除列或列類型變化後，外表會在下一輪重新整理後同步更新中繼資料。

注意事項

• Schema自動重新整理能力僅適用於ENGINE='ODPS'的MaxCompute外表。

• add_only為預設模式，適用於生產環境中僅允許新增列的情境。

• full模式會自動同步源表的刪除列和列類型變化，可能影響依賴舊列的SQL、應用程式、報表或視圖，請謹慎使用。

• 如果基於 MaxCompute 外表建立了視圖且使用了*（例如CREATE VIEW view_name AS SELECT * FROM external_table），視圖在建立時會將其展開並儲存為固定的列列表。當 full 模式同步源表的刪除列或列名變化、且涉及視圖已儲存的列時，查詢該視圖會報錯並提示視圖失效、需重新建立，這屬於預期行為。建議避免在視圖中使用*。更多資訊，請參見注意事項。