SQL语法-CREATE FOREIGN TABLE (SQL on Hadoop )
功能描述
在当前数据库创建一个HDFS外表，用来访问存储在HDFS分布式集群文件系统上的Hadoop结构化数据。HDFS外表是只读的，只能用于查询操作，可直接使用SELECT查询其数据。
语法格式
创建HDFS外表。
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 ' } [, ...] )
DISTRIBUTE BY {ROUNDROBIN | REPLICATION}
[ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ]
[ PARTITION BY ( column_name ) [ AUTOMAPPED ] ] ;
- 其中column_constraint为：
[CONSTRAINT constraint_name]
{PRIMARY KEY | UNIQUE}
[NOT ENFORCED [ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION] | ENFORCED] - 其中table_constraint为：
[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表支持的数据类型有：SMALLINT、INTEGER、BIGINT、FLOAT4 (REAL)、FLOAT8 (DOUBLE PRECISION)、DECIMAL[p(,s)] 最大支持38位精度、DATE、TIMESTAMP、BOOLEAN、CHAR (n)、VARCHAR (n)、TEXT (CLOB)。
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
CSV格式文件下的引号字符，缺省值为双引号。
说明：quote参数不能和分隔符、null参数相同。
quote参数只能是单字节的字符。
推荐不可见字符作为quote，例如0x07，0x08，0x1b等。
- escape
CSV格式下，用来指定逃逸字符，逃逸字符只能指定为单字节字符。
缺省值为双引号。当与quote值相同时，会被替换为'\0'。
- format：外表中数据源文件的格式。支持ORC，TEXT，CSV文件格式。
- foldername：外表中数据源文件目录，即表数据目录在HDFS文件系统上对应的文件目录。
- encoding：外表中数据源文件的编码格式名称，缺省为utf8。此选项为可选参数。
- filenames：外表中数据源文件，以","间隔。 说明：
- 推荐通过使用foldername参数指定数据源的位置。
- foldername必须为绝对路径，不能有多个路径。
- 查询分区表时，会先根据分区信息进行剪枝，然后查询满足条件的数据文件。由于剪枝操作会涉及多次扫描HDFS分区目录内容，不建议使用重复度非常小的列作为分区列，因为这可能导致分区目录非常的多，增加对HDFS的查询压力。
- delimiter
指定数据文件行数据的字段分隔符，不指定则使用默认分隔符，TEXT格式的默认分隔符是水平制表符（tab）。
说明：- 分隔符不能是\r和\n。
- 分隔符不能和null参数相同。
- 分隔符不能包含： \.abcdefghijklmnopqrstuvwxyz0123456789。
- 数据文件中单行数据长度需<1GB，如果分隔符较长且数据列较多的情况下，会影响导出有效数据的长度。
- 分隔符推荐使用多字符（例如'$^&'）和不可见字符（例如0x07、0x08、0x1b等）。
- delimiter参数只在TEXT和CSV格式下有效。
取值范围：
支持多字符分隔符，但分隔符不能超过10个字节。
- null用来指定数据文件中空值的表示。 说明：
- null值不能是\r和\n，最大为100个字符。
- null值不能是分隔符。
- null参数只在TEXT和CSV格式下有效。
取值范围：
在TEXT格式下缺省值是\N。
- noescaping
TEXT格式下，不对'\'和后面的字符进行转义。
说明：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格式下有效。
- 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格式对option支持说明 参数名称
OBS
HDFS
-
text
csv
orc
text
csv
orc
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
支持
支持
支持
支持
支持
支持
checkencoding
支持
支持
支持
支持
支持
支持
totalrows
支持
支持
支持
不支持
不支持
不支持
- header
- DISTRIBUTE BY ROUNDROBIN
指定HDFS外表为ROUNDROBIN分布方式。
- DISTRIBUTE BY REPLICATION
指定HDFS外表为REPLICATION分布方式。
- TO { GROUP groupname | NODE ( nodename [, ... ] ) }
TO GROUP目前不支持使用。TO NODE主要供内部扩容工具使用，一般用户不应使用。
- PARTITION BY ( column_name ) AUTOMAPPED
column_name指定分区列。对于分区表，AUTOMAPPED表示HDFS分区外表指定的分区列会和HDFS数据中的分区目录信息自动对应，前提是必须保证HDFS分区外表指定分区列的顺序和HDFS数据中分区目录定义的顺序一致。
说明：HDFS外表支持分区表，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）
背景信息
数据库系统对数据的约束共分五种，分别为：Not Null约束(非空约束)、Unique约束(唯一约束)、Primary Key约束(主键约束)、Foreign Key约束(外键约束)以及Check约束(检查约束)，数据库在执行数据插入或更新时由数据库强制执行的五种约束可能会产生大量的系统开销，影响数据导入或变更时的性能。
如果装入的数据已经提前完成某种形式的约束，而数据库系统也遵守这些约束，称为信息约束（Informational Constraint）。信息约束并不是一种数据库系统对数据的约束，数据库中的编译器可以使用这个约束来改善访问数据和算子操作时的效率，信息约束不是数据库在数据库插入数据或更改数据时强制执行，不用于数据的附加验证，其主要目的是提高查询性能。
在GaussDB 200中，数据本身存储在HDFS中，GaussDB 200并不提供向HDFS写数据的功能，数据的约束完全由使用者保证，数据源数据能够严格遵守某种信息约束条件，能够加速对已经具有这种约束特征数据的查询。
数据库系统中Unique和Primary Key都是在创建的时候就直接建立了Index，所以在优化器阶段扫描算子就可以考虑使用Index Scan，用以提升数据的查询效率。目前HDFS外表不支持索引Index，所以采取使用Informational Constraint信息优化Plan，提搞查询能力。
建立HDFS外表信息约束的约束条件：
- 只有用户保证表中的其中一列的非空值具有唯一性时才可以建立Informational Constraint，否则查询结果将与期望值不同。
- Informational Constraint目前只支持PRIMARY KEY和UNIQUE两种约束。
- Informational Constraint目前支持NOT ENFORCED属性，ENFORCED属性不支持，创建时直接报错。
- HDFS外表和HDFS分区外表都支持Informational Constraint（分区列上同样可以建立约束）。
- 一个表上的多列可以分别建立UNIQUE类型的Informational Constraint，但是PRIMARY KEY一个表中只能建立一个。
- 一个表的一列上可以建立多个Informational Constraint（由于一个列上有多个约束和一个的作用一致，所以不建议一个列上建立多个Informational Constraint），但是Primary Key类型只能建立一个。
- 不支持多列组合约束。
示例
示例一：在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。复制
--创建HDFS_Server。 CREATE SERVER hdfs_server FOREIGN DATA WRAPPER HDFS_FDW OPTIONS (address ,hdfscfgpath '/opt/hadoop_client/HDFS/hadoop/etc/hadoop',type'HDFS');
说明：在可选项options里面写入了HDFS集群对应的NameNode的IP地址及端口号。‘10.10.0.100:25000,10.10.0.101:25000’中列出了两组NameNode的地址及端口号，分别表示HDFS的主NameNode及备NameNode，这里推荐使用该种主备方式填写。两组参量中间使用“,”进行分割，以第一组参量‘10.10.0.100:25000’为例，其IP地址为10.10.0.100，端口号为25000。
- 建立HDFS外表。
--建立不包含分区列的HDFS外表，表关联的HDFS server为hdfs_server，表region对应的HDFS服务器上的文件格式为‘orc’，在HDFS文件系统上对应的文件目录为'/user/hive/warehouse/mppdb.db/region_orc11_64stripe/'。
CREATE FOREIGN TABLE 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外表。
CREATE FOREIGN TABLE 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 200支持2种文件指定方式：通过关键字filenames指定和通过foldername指定。推荐通过使用foldername进行指定。关键字distribute指定了表region的存储分布方式。
- 查看建立的外表。
--查看外表。
SELECT * FROM pg_foreign_table WHERE ftrelid='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='part'::regclass;
ftrelid | ftserver | ftwriteonly | ftoptions
---------+----------+-------------+------------------------------------------------------------------
16513 | 16509 | f | {format=orc,foldername=/user/hive/warehouse/partition.db/part_4}
(1 row) - 修改、删除外表。
--修改外表。
ALTER FOREIGN TABLE region ALTER r_name TYPE TEXT;
ALTER FOREIGN TABLE
ALTER FOREIGN TABLE region ALTER r_name SET NOT NULL;
ALTER FOREIGN TABLE
--删除外表。
DROP FOREIGN TABLE region;
DROP FOREIGN TABLE
示例二：关于包含信息约束（Informational Constraint）HDFS外表的相关操作。
--创建含有信息约束（Informational Constraint）的HDFS外表。
CREATE FOREIGN TABLE 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表是否有信息约束索引。
SELECT relname,relhasindex FROM pg_class WHERE oid='region'::regclass;
relname | relhasindex
------------------------+-------------
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)
--删除信息约束。
ALTER FOREIGN TABLE region DROP CONSTRAINT region_pkey RESTRICT;
SELECT conname, contype, consoft, conindid, conkey FROM pg_constraint WHERE conname ='region_pkey';
conname | contype | consoft | conindid | conkey
---------+---------+---------+----------+--------
( rows)
--添加一个唯一信息约束。
ALTER FOREIGN TABLE region ADD CONSTRAINT constr_unique UNIQUE(R_REGIONKEY) NOT ENFORCED;
--删除信息约束。
ALTER FOREIGN TABLE region DROP CONSTRAINT constr_unique RESTRICT;
SELECT conname, contype, consoft, conindid, conkey FROM pg_constraint WHERE conname ='constr_unique';
conname | contype | consoft | conindid | conkey
---------+---------+---------+----------+--------
( rows)
--为外表添加一个唯一信息约束。
ALTER FOREIGN TABLE region ADD CONSTRAINT constr_unique UNIQUE(R_REGIONKEY) NOT ENFORCED disable query optimization;
SELECT relname,relhasindex FROM pg_class WHERE oid='region'::regclass;
relname | relhasindex
------------------------+-------------
region | f
(1 row)
--删除信息约束。
ALTER FOREIGN TABLE region DROP CONSTRAINT constr_unique CASCADE;
--删除region表。
DROP FOREIGN TABLE region;
--删除hdfs_server
DROP SERVER hdfs_server;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69959250/viewspace-3169994/ ，如需转载，请注明出处，否则将追究法律责任。
