CREATE FOREIGN TABLE (SQL on OBS or Hadoop)
功能描述
在当前数据库创建一个HDFS或OBS外表，用来访问存储在HDFS或者OBS分布式集群文件系统上的结构化数据。也可以导出ORC格式数据到HDFS或者OBS上。
实时数仓（单机部署）暂不支持OBS和HDFS外表导入导出功能。
注意事项
- HDFS外表与OBS外表分为只读外表和只写外表，只读外表用于查询操作，只写外表可以将GaussDB(DWS)中的数据导出到分布式文件系统中。
- 此方式支持orc、carbondata、text和csv格式的导入查询，以及orc格式的导出。
- 该方式需要用户手动创建外部服务器，具体请参见CREATE SERVER。
- 若手动创建Server时指定foreign data wrapper为HDFS_FDW或者DFS_FDW，创建只读外表时需DISTRIBUTE BY子句指定分布方式。
语法格式
创建外表。
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [ { column_name type_name [ { [CONSTRAINT constraint_name] NULL | [CONSTRAINT constraint_name] NOT NULL | column_constraint [...]} ] | table_constraint [, ...]} [, ...] ] ) SERVER server_name OPTIONS ( { option_name ' value ' } [, ...] ) [ {WRITE ONLY | READ ONLY}] DISTRIBUTE BY {ROUNDROBIN | REPLICATION} [ PARTITION BY ( column_name ) [ AUTOMAPPED ] ] ; |
- 其中column_constraint为：
1 2 3
[CONSTRAINT constraint_name] {PRIMARY KEY | UNIQUE} [NOT ENFORCED [ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION] | ENFORCED]
- 其中table_constraint为：
1 2 3
[CONSTRAINT constraint_name] {PRIMARY KEY | UNIQUE} (column_name) [NOT ENFORCED [ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION] | ENFORCED]
参数说明
- IF NOT EXISTS
如果已经存在相同名称的表，不会抛出一个错误，而会发出一个通知，告知表关系已存在。
- table_name
外表的表名。
取值范围：字符串，要符合标识符的命名规范。
- column_name
外表中的字段名。可以选择多个字段名，中间用“,”隔开。
取值范围：字符串，要符合标识符的命名规范。
- type_name
字段的数据类型。
orc表支持的数据类型。
txt表支持的数据类型与行存表保持一致。
- constraint_name
外表的表约束名。
- { NULL | NOT NULL }
标识此列是否允许NULL值。
在创建表时，对于列的约束NULL/NOT NULL，并不能保证该表在HDFS系统中的数据为NULL或者NOT NULL，数据的一致性由用户保证。所以需要由用户判断该列是否一定不为空或者一定为空，在建表的时候选用NULL或NOT NULL。(优化器对列为NULL/NOT NULL做了优化处理，会产生更优的计划。)
- SERVER server_name
外表的server名字。允许用户自定义名字。
取值范围：字符串，要符合标识符的命名规范，并且这个server必须存在。
- OPTIONS ( { option_name ' value ' } [, ...] )
用于指定外表数据的各类参数，参数类型如下所示。
- header
指定导出数据文件是否包含标题行，标题行一般用来描述表中每个字段的信息。header只能用于CSV格式的文件中。
如果header选项为on，则数据文件第一行会被识别为标题行，导出时会忽略此行。如果header为off，而数据文件中第一行会被识别为数据。
取值范围：true/on，false/off。缺省值为false/off。
- quote
quote参数不能和分隔符、null参数相同。
quote参数只能是单字节的字符。
推荐不可见字符作为quote，例如0x07，0x08，0x1b等。
- escape
CSV格式下，用来指定逃逸字符，逃逸字符只能指定为单字节字符。
缺省值为双引号。当与quote值相同时，会被替换为'\0'。
- location
OBS外表参数，指定存储在OBS上的文件路径，多个桶的数据源数据之间使用分隔符‘|’进行分割，例如：LOCATION 'obs://bucket1/folder/ | obs://bucket2/'，数据库将会扫描指定路径文件夹下面的所有对象。
当访问DLI多版本表时，无需指定location参数。
- format：外表中数据源文件的格式。
- HDFS外表READ ONLY外表支持ORC、TEXT、CSV、PARQUET文件格式，而WRITE ONLY外表只支持ORC文件格式。
- OBS外表READ ONLY外表支持ORC、TEXT、CSV、CARBONDATA文件格式，而WRITE ONLY外表只支持ORC文件格式。
- foldername：外表中数据源文件目录，即表数据目录在HDFS文件系统和OBS上对应的文件目录。此选项对WRITE ONLY外表为必选项，对READ ONLY外表为可选项。
- encoding：外表中数据源文件的编码格式名称，缺省为utf8。此选项为可选参数。
- totalrows：可选参数，估计表的行数，仅OBS外表使用。由于OBS上文件可能很多，做analyze可能会很慢，通过此参数让用户设置一个预估的值，使优化器能通过这个值做大小表的估计。一般预估值和实际值相近时，查询效率较高。
- filenames：外表中数据源文件，以","间隔。
- 推荐通过使用foldername参数指定数据源的位置，对于只读外表filenames参数与foldername参数两者必有其一，而只写外表只能通过foldername指定。
- foldername为绝对目录时，前后必须有'/', 多个路径用', '分隔。
- 查询分区表时，会先根据分区信息进行剪枝，然后查询满足条件的数据文件。由于剪枝操作会涉及多次扫描HDFS分区目录内容，不建议使用重复度非常小的列作为分区列，因为这可能导致分区目录非常的多，增加对HDFS的查询压力。
- OBS只读外表不支持。
- delimiter
指定数据文件行数据的字段分隔符，不指定则使用默认分隔符，TEXT格式的默认分隔符是水平制表符（tab）。
- 分隔符不能是\r和\n。
- 分隔符不能和null参数相同。
- 分隔符不能包含： \.abcdefghijklmnopqrstuvwxyz0123456789。
- 数据文件中单行数据长度需<1GB，如果分隔符较长且数据列较多的情况下，会影响导出有效数据的长度。
- 分隔符推荐使用多字符（例如'$^&'）和不可见字符（例如0x07、0x08、0x1b等）。
- delimiter参数只在TEXT和CSV格式下有效。
取值范围：
支持多字符分隔符，但分隔符不能超过10个字节。
- eol
取值范围：支持多字符换行符，但换行符不能超过10个字节。常见的换行符，如\r、\n、\r\n（设成0x0D、0x0A、0x0D0A效果是相同的），其他字符或字符串，如$、#。
- eol参数只能用于TEXT格式的导入。
- eol参数不能和分隔符、null参数相同。
- eol参数不能包含：数字，字母和符号“.”。
- null
取值范围：
在TEXT格式下缺省值是\N。
- noescaping
noescaping参数只在TEXT格式下有效。
取值范围：true/on，false/off。缺省值为false/off。
- fill_missing_fields
当数据加载时，若数据源文件中一行的最后一个字段缺失时的处理方式。
取值范围：true/on，false/off。缺省值为false/off。
- 参数为true/on，当数据加载时，若数据源文件中一行数据的最后一个字段缺失，则把最后一个字段的值设置为NULL，不报错。
- 参数为false/off，如果最后一个字段缺失会显示如下错误信息。
missing data for column "tt"
- TEXT格式下执行SELECT COUNT(*) 不会去解析具体字段，因此不会对字段缺失情况报错。
- fill_missing_fields参数只在TEXT和CSV格式下有效。
- ignore_extra_data
若数据源文件比外表定义列数多，是否会忽略多出的列。该参数只在数据导入过程中使
取值范围：true/on，false/off。缺省值为false/off。
- 参数为true/on，若数据源文件比外表定义列数多，则忽略行尾多出来的列。
- 参数为false/off，若数据源文件比外表定义列数多，会显示如下错误信息。
extra data after last expected column
- 如果行尾换行符丢失，使两行变成一行时，设置此参数为true将导致后一行数据被忽略掉。
- TEXT格式下执行SELECT COUNT(*) 不会去解析具体字段，因此不会对多余的情况报错。
- ignore_extra_data参数只在TEXT和CSV格式下有效。
- date_format
导入对于DATE类型指定格式。此语法仅对READ ONLY的外表有效。
取值范围：合法DATE格式。可参考时间、日期处理函数和操作符。
- 对于指定为ORACLE兼容类型的数据库，则DATE类型内建为TIMESTAMP类型。在导入的时候，若需指定格式，可以参考下面的timestamp_format参数。
- date_format参数只在TEXT和CSV格式下有效。
- time_format
导入对于TIME类型指定格式。此语法仅对READ ONLY的外表有效。
取值范围：合法TIME格式，不支持时区。可参考时间、日期处理函数和操作符。
time_format参数只在TEXT和CSV格式下有效。
- timestamp_format
导入对于TIMESTAMP类型指定格式。此语法仅对READ ONLY的外表有效。
取值范围：合法TIMESTAMP格式，不支持时区。可参考时间、日期处理函数和操作符。
timestamp_format参数只在TEXT和CSV格式下有效。
- smalldatetime_format
导入对于SMALLDATETIME类型指定格式。此语法仅对READ ONLY的外表有效。
取值范围：合法SMALLDATETIME格式。可参考时间、日期处理函数和操作符。
smalldatetime_format参数只在TEXT和CSV格式下有效。
- dataencoding
在数据库编码与数据表的数据编码不一致时，该参数用于指定导出数据表的数据编码。比如数据库编码为Latin-1，而导出的数据表中的数据为UTF-8编码。此选项为可选项，如果不指定该选项，默认采用数据库编码。此语法仅对HDFS的WRITE ONLY外表有效。
取值范围：该数据库编码支持转换的数据编码。
dataencoding参数只对ORC格式的WRITE ONLY的HDFS外表有效。
- filesize
指定WRITE ONLY外表的文件大小。此选项为可选项，不指定该选项默认分布式文件系统配置中文件大小的配置值。此语法仅对WRITE ONLY的外表有效。
取值范围：[1, 1024]的整数。
filesize参数只对ORC格式的WRITE ONLY的HDFS外表有效。
- compression
指定ORC格式文件的压缩方式，此选项为可选项。此语法仅对WRITE ONLY的外表有效。
取值范围：zlib，snappy，lz4。缺省值为snappy。
- version
指定ORC格式的版本号，此选项为可选项。此语法仅对WRITE ONLY的外表有效。
取值范围：目前仅支持0.12。缺省值为0.12。
- dli_project_id
DLI服务对应的项目编号，可在管理控制台上获取项目ID，该参数仅支持server类型为DLI时设置。该参数仅8.1.1及以上版本支持。
- dli_database_name
- dli_table_name
- checkencoding
取值范围：low，high 。缺省值为low。
TEXT格式下，导入非法字符容错规则如下：
- 对于'\0'，容错后转换为空格；
- 对于其他非法字符，容错后转换为问号；
- 若checkencoding为low标识，导入时对于非法字符进行容错处理，则若NULL、DELIMITER设置为空格或问号则会通过如"illegal chars conversion may confuse null 0x20"等报错信息提示用户修改可能引起混淆的参数以避免导入错误。
ORC格式下，导入非法字符容错规则如下：
- checkencoding为low标识，若导入时检查到某个字段中包含非法字符，则自动将当前列当前行的字段整体替换为同样长度的‘？’字符；
- checkencoding为high标识，若导入时检查到某个字段中包含非法字符，则报错退出。
表1 text、csv、orc、carbondata、parquet格式对option支持说明 参数名称
OBS
HDFS
-
TEXT
CSV
ORC
CARBONDATA
TEXT
CSV
ORC
PARQUET
READ ONLY
READ ONLY
READ ONLY
WRITE ONLY
READ ONLY
READ ONLY
READ ONLY
READ ONLY
WRITE ONLY
READ ONLY
location
√
√
√
×
×
×
×
×
×
×
format
√
√
√
√
√
√
√
√
√
√
header
×
√
×
×
×
×
√
×
×
×
delimiter
√
√
×
×
×
√
√
×
×
×
quote
×
√
×
×
×
×
√
×
×
×
escape
×
√
×
×
×
×
√
×
×
×
null
√
√
×
×
×
√
√
×
×
×
noescaping
√
×
×
×
×
√
×
×
×
×
encoding
√
√
√
√
√
√
√
√
√
√
fill_missing_fields
√
√
×
×
×
√
√
×
×
×
ignore_extra_data
√
√
×
×
×
√
√
×
×
×
date_format
√
√
×
×
×
√
√
×
×
×
time_format
√
√
×
×
×
√
√
×
×
×
timestamp_format
√
√
×
×
×
√
√
×
×
×
smalldatetime_format
√
√
×
×
×
√
√
×
×
×
chunksize
√
√
×
×
×
√
√
×
×
×
filenames
×
×
×
×
√
√
√
√
×
√
foldername
√
√
√
√
√
√
√
√
√
√
dataencoding
×
×
×
×
×
×
×
×
√
×
filesize
×
×
×
×
×
×
×
×
√
×
compression
×
×
×
√
×
×
×
×
√
×
version
×
×
×
√
×
×
×
×
√
×
checkencoding
√
√
√
×
√
√
√
√
√
√
totalrows
√
√
√
×
×
×
×
×
×
×
- header
- WRITE ONLY | READ ONLY
READ ONLY 指定创建HDFS/OBS的只读外表。
如果不指定创建的外表的类型，默认为只读外表。
- DISTRIBUTE BY ROUNDROBIN
指定HDFS/OBS外表为ROUNDROBIN分布方式。
- DISTRIBUTE BY REPLICATION
指定HDFS/OBS外表为REPLICATION分布方式。
- PARTITION BY ( column_name ) AUTOMAPPED
column_name指定分区列。对于分区表，AUTOMAPPED表示HDFS分区外表指定的分区列会和HDFS数据中的分区目录信息自动对应，前提是必须保证HDFS分区外表指定分区列的顺序和HDFS数据中分区目录定义的顺序一致，该功能只适用于只读外表，只写外表不支持。
- HDFS的只读和只写外表都支持分区表，但是只写外表只支持一级分区，不支持多级分区。
- OBS只读外表支持分区表，OBS只写外表不支持分区表。
- CONSTRAINT constraint_name
用于指定外表所建立的信息约束（Informational Constraint）的名字。
取值范围：字符串，要符合标识符的命名规范。
- PRIMARY KEY
主键约束，表示表里的一个或者一些字段只能包含唯一（不重复）的非NULL值。一个表只能声明一个主键。
- UNIQUE
唯一约束，表示表里的一个或者多个字段的组合必须在全表范围内唯一。对于唯一约束，NULL被认为是互相不等的。
- NOT ENFORCED
指定所建立的约束为信息约束，该约束不由数据库来保证，而由用户来保证。
- ENFORCED
ENFORCED为默认值。预留参数，目前对于ENFORCED不支持。
- PRIMARY KEY (column_name)
指定所建立的信息约束位于column_name列上。
取值范围：字符串，要符合标识符的命名规范，并且这个column_name必须存在。
- ENABLE QUERY OPTIMIZATION
利用信息约束对执行计划进行优化。
- DISABLE QUERY OPTIMIZATION
禁止利用信息约束对执行计划优化。
信息约束（Informational Constraint）
在GaussDB(DWS)中，数据的约束完全由使用者保证，数据源数据能够严格遵守某种信息约束条件，能够加速对已经具有这种约束特征数据的查询。目前外表不支持索引，所以采取使用Informational Constraint信息优化Plan，提搞查询性能。
建立外表信息约束的约束条件：
- 只有用户保证表中的其中一列的非空值具有唯一性时才可以建立Informational Constraint，否则查询结果将与期望值不同。
- GaussDB(DWS)的Informational Constraint只支持PRIMARY KEY和UNIQUE两种约束。
- GaussDB(DWS)的Informational Constraint支持NOT ENFORCED属性，不支持ENFORCED属性。
- 一个表上的多列可以分别建立UNIQUE类型的Informational Constraint，但是PRIMARY KEY一个表中只能建立一个。
- 一个表的一列上可以建立多个Informational Constraint（由于一个列上有多个约束和一个的作用一致，所以不建议一个列上建立多个Informational Constraint），但是Primary Key类型只能建立一个。
- 不支持COMMENT。
- 不支持多列组合约束。
- ORC格式只写外表不支持同一个集群不同CN向同一外表并发导出。
- ORC格式只写外表的目录，只能用于GaussDB(DWS)的单个外表的导出目录，不能用于多个外表，并且其他组件不能向此目录写入其他文件。
示例1
在HDFS通过HIVE导入TPC-H benchmark测试数据表part表及region表。part表的文件路径为/user/hive/warehouse/partition.db/part_4，region表的文件路径为/user/hive/warehouse/mppdb.db/region_orc11_64stripe/。
- 创建HDFS_Server，对应的foreign data wrapper为HDFS_FDW或者DFS_FDW。
1CREATE SERVER hdfs_server FOREIGN DATA WRAPPER HDFS_FDW OPTIONS (address '10.10.0.100:25000,10.10.0.101:25000',hdfscfgpath '/opt/hadoop_client/HDFS/hadoop/etc/hadoop',type'HDFS');
- 在可选项options里面写入了HDFS集群对应的NameNode的IP地址及端口号。具体端口号请在MRS-HDFS服务配置中搜索参数“dfs.namenode.rpc.port”查看。本示例假设端口号为25000。
- ‘10.10.0.100:25000,10.10.0.101:25000’中列出了两组NameNode的地址及端口号，分别表示HDFS的主NameNode及备NameNode，这里推荐使用该种主备方式填写。两组参量中间使用“,”进行分割。
- 创建HDFS外表。表关联的HDFS server为hdfs_server，表ft_region对应的HDFS服务器上的文件格式为‘orc’，在HDFS文件系统上对应的文件目录为'/user/hive/warehouse/mppdb.db/region_orc11_64stripe/'。
- 创建不包含分区列的HDFS外表：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
CREATE FOREIGN TABLE ft_region ( R_REGIONKEY INT4, R_NAME TEXT, R_COMMENT TEXT ) SERVER hdfs_server OPTIONS ( FORMAT 'orc', encoding 'utf8', FOLDERNAME '/user/hive/warehouse/mppdb.db/region_orc11_64stripe/' ) DISTRIBUTE BY roundrobin;
- 创建包含分区列的HDFS外表：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
CREATE FOREIGN TABLE ft_part ( p_partkey int, p_name text, p_mfgr text, p_brand text, p_type text, p_size int, p_container text, p_retailprice float8, p_comment text ) SERVER hdfs_server OPTIONS ( FORMAT 'orc', encoding 'utf8', FOLDERNAME '/user/hive/warehouse/partition.db/part_4' ) DISTRIBUTE BY roundrobin PARTITION BY (p_mfgr) AUTOMAPPED;
GaussDB(DWS)支持2种文件指定方式：通过关键字filenames指定和通过foldername指定。推荐通过使用foldername进行指定。关键字distribute指定了表ft_region的存储分布方式。
- 查看创建的外表：
1 2 3 4 5 6 7 8 9 10 11
SELECT * FROM pg_foreign_table WHERE ftrelid='ft_region'::regclass; ftrelid | ftserver | ftwriteonly | ftoptions ---------+----------+-------------+------------------------------------------------------------------------------ 16510 | 16509 | f | {format=orc,foldername=/user/hive/warehouse/mppdb.db/region_orc11_64stripe/} (1 row) SELECT * FROM pg_foreign_table WHERE ftrelid='ft_part'::regclass; ftrelid | ftserver | ftwriteonly | ftoptions ---------+----------+-------------+------------------------------------------------------------------ 16513 | 16509 | f | {format=orc,foldername=/user/hive/warehouse/partition.db/part_4} (1 row)
示例2
通过HDFS只写外表将TPC-H benchmark测试数据表region表中的数据导出HDFS文件系统的/user/hive/warehouse/mppdb.db/regin_orc/目录下。
- 创建HDFS外表，对应的foreign data wrapper为HDFS_FDW或者DFS_FDW，同示例1。
- 创建HDFS只写外表。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
CREATE FOREIGN TABLE ft_wo_region ( R_REGIONKEY INT4, R_NAME TEXT, R_COMMENT TEXT ) SERVER hdfs_server OPTIONS ( FORMAT 'orc', encoding 'utf8', FOLDERNAME '/user/hive/warehouse/mppdb.db/regin_orc/' ) WRITE ONLY;
- 通过只写外表向HDFS文件系统写入数据。
1INSERT INTO ft_wo_regin SELECT * FROM region;
示例3
关于包含信息约束（Informational Constraint）HDFS外表的相关操作。
- 创建含有信息约束（Informational Constraint）的HDFS外表：
1 2 3 4 5 6 7 8 9 10
CREATE FOREIGN TABLE ft_region ( R_REGIONKEY int, R_NAME TEXT, R_COMMENT TEXT , primary key (R_REGIONKEY) not enforced) SERVER hdfs_server OPTIONS(format 'orc', encoding 'utf8', foldername '/user/hive/warehouse/mppdb.db/region_orc11_64stripe') DISTRIBUTE BY roundrobin;
- 查看region表是否有信息约束索引：
1 2 3 4 5 6 7 8 9 10 11
SELECT relname,relhasindex FROM pg_class WHERE oid='ft_region'::regclass; relname | relhasindex ------------------------+------------- ft_region | f (1 row) SELECT conname, contype, consoft, conopt, conindid, conkey FROM pg_constraint WHERE conname ='region_pkey'; conname | contype | consoft | conopt | conindid | conkey -------------+---------+---------+--------+----------+-------- region_pkey | p | t | t | 0 | {1} (1 row)
- 删除信息约束：
1 2 3 4 5 6
ALTER FOREIGN TABLE ft_region DROP CONSTRAINT region_pkey RESTRICT; SELECT conname, contype, consoft, conindid, conkey FROM pg_constraint WHERE conname ='region_pkey'; conname | contype | consoft | conindid | conkey ---------+---------+---------+----------+-------- (0 rows)
- 添加一个唯一信息约束：
1ALTER FOREIGN TABLE ft_region ADD CONSTRAINT constr_unique UNIQUE(R_REGIONKEY) NOT ENFORCED;
删除信息约束：1 2 3 4 5 6
ALTER FOREIGN TABLE ft_region DROP CONSTRAINT constr_unique RESTRICT; SELECT conname, contype, consoft, conindid, conkey FROM pg_constraint WHERE conname ='constr_unique'; conname | contype | consoft | conindid | conkey ---------+---------+---------+----------+-------- (0 rows)
- 为外表添加一个唯一信息约束：
1 2 3 4 5 6 7
ALTER FOREIGN TABLE ft_region ADD CONSTRAINT constr_unique UNIQUE(R_REGIONKEY) NOT ENFORCED disable query optimization; SELECT relname,relhasindex FROM pg_class WHERE oid='ft_region'::regclass; relname | relhasindex ------------------------+------------- ft_region | f (1 row)
删除信息约束：1ALTER FOREIGN TABLE ft_region DROP CONSTRAINT constr_unique CASCADE;
示例4
通过外表读取OBS上的json数据。
- OBS上有如下json文件，json对象中存在嵌套、数组，部分对象的某些字段缺失，部分对象name重复。
{"A" : "simple1", "B" : {"C" : "nesting1"}, "D" : ["array", 2, {"E" : "complicated"}]} {"A" : "simple2", "D" : ["array", 2, {"E" : "complicated"}]} {"A" : "simple3", "B" : {"C" : "nesting3"}, "D" : ["array", 2, {"E" : "complicated3"}]} {"B" : {"C" : "nesting4"},"A" : "simple4", "D" : ["array", 2, {"E" : "complicated4"}]} {"A" : "simple5", "B" : {"C" : "nesting5"}, "D" : ["array", 2, {"E" : "complicated5"}]} - 创建obs_server，对应的foreign data wrapper为DFS_FDW。
1 2 3 4 5 6
CREATE SERVER obs_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS ( ADDRESS 'obs.xxx.xxx.com', ACCESS_KEY 'xxxxxxxxx', SECRET_ACCESS_KEY 'yyyyyyyyyyyyy', TYPE 'OBS' );
- ADDRESS是OBS的终端节点（Endpoint），请根据实际替换。也是使用region参数，通过指定regionCode在region_map文件中查找对应的域名。
- ACCESS_KEY和SECRET_ACCESS_KEY 是云账号体系访问密钥。请根据实际替换。
- 认证用的AK和SK硬编码到代码中或者明文存储都有很大的安全风险，建议在配置文件或者环境变量中密文存放，使用时解密，确保安全。
- TYPE表示创建的Server为OBS Server。请保持OBS取值不变。
- 创建OBS外表json_f ，定义字段名，以d#2_e为例，从命名可以看出该字段是数组d的第二个元素里嵌套的e对象。表关联的OBS服务器为obs_server。foldername为外表中数据源文件目录，即表数据目录在OBS上对应的文件目录。
1 2 3 4 5 6 7 8 9 10 11
CREATE FOREIGN TABLE json_f ( a VARCHAR(10), b_c TEXT, d#1 INTEGER, d#2_e VARCHAR(30) )SERVER obs_server OPTIONS ( foldername '/xxx/xxx/', format 'json', encoding 'utf8', force_mapping 'true' )distribute by roundrobin;
- 查询外表json_f。由于容错性参数force_mapping默认打开，json对象缺失的字段会填NULL；json对象name重复的以最后一次出现的name为准。
1 2 3 4 5 6 7 8 9
SELECT * FROM json_f; a | b_c | d#1 | d#2_e ---------+----------+-----+-------------- simple1 | nesting1 | 2 | complicated1 simple2 | | 2 | complicated2 simple3 | nesting3 | 2 | complicated3 simple4 | nesting4 | 2 | complicated4 repeat | nesting5 | 2 | complicated5 (5 rows)
示例5
通过外表读取DLI多版本外表。DLI多版本外表示例仅8.1.1及以上版本支持。
- 创建dli_server，对应的foreign data wrapper为DFS_FDW。
1 2 3 4 5 6 7 8 9
CREATE SERVER dli_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS ( ADDRESS 'obs.xxx.xxx.com', ACCESS_KEY 'xxxxxxxxx', SECRET_ACCESS_KEY 'yyyyyyyyyyyyy', TYPE 'DLI', DLI_ADDRESS 'dli.xxx.xxx.com', DLI_ACCESS_KEY 'xxxxxxxxx', DLI_SECRET_ACCESS_KEY 'yyyyyyyyyyyyy' );
- ADDRESS是OBS的终端节点（Endpoint）。DLI_ADDRESS是DLI的终端节点（Endpoint），请根据实际替换。
- ACCESS_KEY和SECRET_ACCESS_KEY 是云账号体系访问OBS服务的密钥。请根据实际替换。
- 认证用的AK和SK硬编码到代码中或者明文存储都有很大的安全风险，建议在配置文件或者环境变量中密文存放，使用时解密，确保安全。
- DLI_ACCESS_KEY和DLI_SECRET_ACCESS_KEY是云账号体系访问DLI服务的密钥。请根据实际替换。
- TYPE表示创建的Server为DLI Server。请保持DLI取值不变。
- 创建访问DLI多版本的OBS外表customer_address，不包含分区列，表关联的DLI服务器为dli_server。其中dli_project_id为xxxxxxxxxxxxxxx，dli_database_name为database123，需要访问的dli_table_name为table456，根据实际替换。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
CREATE FOREIGN TABLE customer_address ( ca_address_sk integer not null, ca_address_id char(16) not null, ca_street_number char(10) , ca_street_name varchar(60) , ca_street_type char(15) , ca_suite_number char(10) , ca_city varchar(60) , ca_county varchar(30) , ca_state char(2) , ca_zip char(10) , ca_country varchar(20) , ca_gmt_offset decimal(36,33) , ca_location_type char(20) ) SERVER dli_server OPTIONS ( FORMAT 'ORC', ENCODING 'utf8', DLI_PROJECT_ID 'xxxxxxxxxxxxxxx', DLI_DATABASE_NAME 'database123', DLI_TABLE_NAME 'table456' ) DISTRIBUTE BY roundrobin;
- 通过外表查询DLI多版本表的数据。
1 2 3 4 5
SELECT COUNT(*) FROM customer_address; count ------- 20 (1 row)
