华为云用户手册

语法格式 1 2 CREATE GROUP group_name [ [ WITH ] option [ ... ] ] [ ENCRYPTED | UNENCRYPTED ] { PASSWORD | IDENTIFIED BY } { 'password' | DISABLE }; 其中可选项action子句语法为： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 where option can be: {SYSADMIN | NOSYSADMIN} | {AUDITADMIN | NOAUDITADMIN} | {CREATEDB | NOCREATEDB} | {USEFT | NOUSEFT} | {CREATEROLE | NOCREATEROLE} | {INHERIT | NOINHERIT} | { LOG IN | NOLOGIN} | {REPLICATION | NOREPLICATION} | {INDEPENDENT | NOINDEPENDENT} | {VCADMIN | NOVCADMIN} | CONNECTION LIMIT connlimit | VALID BEGIN 'timestamp' | VALID UNTIL 'timestamp' | RESOURCE POOL 'respool' | USER GROUP 'groupuser' | PERM SPACE 'spacelimit' | NODE GROUP logic_group_name | IN ROLE role_name [, ...] | IN GROUP role_name [, ...] | ROLE role_name [, ...] | ADMIN role_name [, ...] | USER role_name [, ...] | SYSID uid | DEFAULT TABLESPACE tablespace_name | PROFILE DEFAULT | PROFILE profile_name | PGUSER

参数说明 function_name 要创建的函数名字（可以用模式修饰）。 取值范围：字符串，要符合标识符的命名规范。 如果创建的函数名与系统函数同名，建议指定schema。调用自定义函数时需指定schema，否则系统会优先调用系统函数。 argname 函数参数的名字。 取值范围：字符串，要符合标识符的命名规范。 argmode 函数参数的模式。 取值范围：IN，OUT，INOUT或VARIADIC。缺省值是IN。只有OUT模式的参数后面能跟VARIADIC。并且OUT和INOUT模式的参数不能用在RETURNS TABLE的函数定义中。 VARIADIC用于声明数组类型的参数。 argtype 函数参数的类型。 expression 函数参数的默认表达式。 rettype 函数返回值的数据类型。 如果存在OUT或IN OUT参数，可以省略RETURNS子句。如果存在，该子句必须和输出参数所表示的结果类型一致：如果有多个输出参数，则为RECORD，否则与单个输出参数的类型相同。 SETOF修饰词表示该函数将返回一个集合，而不是单独一项。 DETERMINISTIC 为适配Oracle SQL语法，未实现功能，不推荐使用。 column_name 字段名称。 column_type 字段类型。 definition 一个定义函数的字符串常量，含义取决于语言。它可以是一个内部函数名字、一个指向某个目标文件的路径、一个SQL查询、一个过程语言文本。 LANGUAGE lang_name 用以实现函数的语言的名字。可以是SQL，internal，或者是用户定义的过程语言名字。为了保证向下兼容，该名字可以用单引号（包围）。若采用单引号，则引号内必须为大写。 WINDOW 表示该函数是窗口函数，替换函数定义时不能改变WINDOW属性。 自定义窗口函数只支持LANGUAGE是internal，并且引用的内部函数必须是窗口函数。 IMMUTABLE 表示该函数在给出同样的参数值时总是返回同样的结果。 如果函数的入参是常量，会在优化器阶段计算该函数的值。益处是可以尽早获取表达式的值，从而能更准确的进行代价估算，生成的执行计划也更优。 用户自定义的IMMUTABLE的函数是会被自动下推到DN执行的，但是这样可能有潜在的风险，即如果用户错误定义了函数的IMMUTABLE属性，但是函数执行的过程并不是IMMUTABLE的，那么可能会导致结果错误等严重问题。因此，用户在指定函数的属性为IMMUTABLE的时候，要特别慎重。 举例如下： 如果自定义函数中引用了表，视图等对象，那么该函数就不能定义为IMMUTABLE，因为当表的数据发生变化的时候，函数的返回值可能发生变化。 如果自定义函数中引用了STABLE/VOALATILE类型的函数，那么该函数不能定义为IMMUTABLE。 如果自定义函数中有不下推的因素，则该函数不能定义成IMMUTABLE，因为IMMUTABLE意味着要下推到DN执行，与函数内部的不下推因素相互冲突。典型场景例如，包含不下推的函数、语法等。 如果自定义函数中含有聚合运算，但聚合运算的运算需要生成STREAM计划才能完成计算的（部分结果在DN计算，部分结果在CN计算，例如listagg函数等）。 同时，为了防止这种情况下可能出现严重问题，数据库内部可以通过设置behavior_compat_options=‘check_function_conflicts’来开启对函数定义冲突的检查，目前可以识别出上述1和2场景。 STABLE 表示该函数不能修改数据库，对相同参数值，在同一次表扫描里，该函数的返回值不变，但是返回值可能在不同SQL语句之间变化。 VOLATILE 表示该函数值可以在一次表扫描内改变，因此不会做任何优化。 SHIPPABLE NOT SHIPPABLE 表示该函数是否可以下推到DN上执行。 对于IMMUTABLE类型的函数，函数始终可以下推到DN上执行。 对于STABLE/VOLATILE类型的函数，仅当函数的属性是SHIPPABLE的时候，函数可以下推到DN执行。 用户在定义函数的SHIPPABLE属性时也需特别慎重，SHIPPABLE意味着整个函数会下推到DN上执行，如果设置不当，会导致结果错误等严重问题。 与定义IMMUTABLE属性一样，SHIPPABLE属性的定义也有诸多约束，简单来说就是函数内不能有不可下推的因素，函数下推到单DN执行后，函数内部的计算逻辑仅依赖当前DN的数据集合。 举例如下： 如果函数内部引用了表，并且表为HASH分布，那么该函数通常不能定义为SHIPPABLE。 函数内部有不可下推的因素，函数，语法等，那么该函数不能定义为SHIPPABLE，可参考语句下推调优。 函数内部的计算过程可能需要跨DN数据，这种情况该函数通常不能定义为SHIPPABLE，例如一些聚合运算等。 PACKAGE 表示该函数是否支持重载。PostgreSQL风格的函数本身就支持重载，此参数主要是针对Oracle风格的函数。 不允许package函数和非package函数重载或者替换。 package函数不支持VARIADIC类型的参数。 不允许修改函数的package属性。 LEAKPROOF 指出该函数的参数只包括返回值。LEAKPROOF只能由系统管理员设置。 CALLED ON NULL INPUT 表明该函数的某些参数是NULL的时候可以按照正常的方式调用。该参数可以省略。 RETURNS NULL ON NULL INPUT STRICT STRICT用于指定如果函数的某个参数是NULL，此函数总是返回NULL。如果声明了这个参数，当有NULL值参数时该函数不会被执行；而只是自动返回一个NULL结果。 RETURNS NULL ON NULL INPUT和STRICT的功能相同。 EXTERNAL 目的是和SQL兼容，是可选的，这个特性适合于所有函数，而不仅是外部函数。 SECURITY INVOKER AUTHID CURRENT_USER 表明该函数将带着调用它的用户的权限执行。该参数可以省略。 SECURITY INVOKER和AUTHID CURRENT_USER的功能相同。 SECURITY DEFINER AUTHID DEFINER 声明该函数将以创建它的用户的权限执行。 AUTHID DEFINER和SECURITY DEFINER的功能相同。 FENCED NOT FENCED 该函数只对用户定义的C函数生效，声明函数是在保护模式还是非保护模式下执行。如果函数声明为NOT FENCED模式，则函数的执行在CN或者DN进程中进行。如果函数声明为FENCED模式，则函数在新fork的进程执行，这样函数的异常不会影响CN或者DN进程。 FENCED/NOT FENCED模式的选择： 正在开发或者调试的Function使用FENCED模式。开发测试完成，使用NOT FENCED模式执行，减少fork进程以及通信的开销。 复杂的操作系统操作，例：打开文件，信号处理，线程处理等操作，使用FENCED模式。否则可能影响 GaussDB (DWS)数据库的执行。 默认值为FENCED。 COST execution_cost 用来估计函数的执行成本。 execution_cost以cpu_operator_cost为单位。 取值范围：正数 ROWS result_rows 估计函数返回的行数。用于函数返回的是一个集合。 取值范围：正数，默认值是1000行。 configuration_parameter value 把指定的数据库会话参数值设置为给定的值。如果value是DEFAULT或者RESET，则在新的会话中使用系统的缺省设置。OFF关闭设置。 取值范围：字符串 DEFAULT OFF RESET 指定默认值。 from current 取当前会话中的值设置为configuration_parameter的值。 plsql_body PL/SQL存储过程体。 当在函数中创建用户时，日志中会记录密码的明文。因此不建议用户在函数中创建用户。

示例 定义函数为SQL查询： 1 2 3 4 5 CREATE FUNCTION func_add_sql(integer, integer) RETURNS integer AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT; 利用参数名用PL/pgSQL自增一个整数： 1 2 3 4 5 CREATE OR REPLACE FUNCTION func_increment_plsql(i integer) RETURNS integer AS $$ BEGIN RETURN i + 1; END; $$ LANGUAGE plpgsql; 返回RECORD类型： 1 2 3 4 5 6 7 8 9 CREATE OR REPLACE FUNCTION compute(i int, out result_1 bigint, out result_2 bigint) returns SETOF RECORD as $$ begin result_1 = i + 1; result_2 = i * 10; return next; end; $$language plpgsql; 返回一个包含多个输出参数的记录： 1 2 3 4 CREATE FUNCTION func_dup_sql(in int, out f1 int, out f2 text) AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$ LANGUAGE SQL; SELECT * FROM func_dup_sql(42); 计算两个整数的和，并返回结果。若果输入为null，则返回null： 1 2 3 4 5 6 CREATE FUNCTION func_add_sql2(num1 integer, num2 integer) RETURN integer AS BEGIN RETURN num1 + num2; END; / 创建package属性的重载函数： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 CREATE OR REPLACE FUNCTION package_func_overload(col int, col2 int) return integer package as declare col_type text; begin col := 122; dbms_output.put_line('two int parameters ' || col2); return 0; end; / CREATE OR REPLACE FUNCTION package_func_overload(col int, col2 smallint) return integer package as declare col_type text; begin col := 122; dbms_output.put_line('two smallint parameters ' || col2); return 0; end; /

注意事项 如果创建函数时参数或返回值带有精度，不进行精度检测。 创建函数时，函数定义中对表对象的操作建议都显式指定模式，否则可能会导致函数执行异常。 在创建函数时，函数内部通过SET语句设置current_schema和search_path无效。执行完函数search_path和current_schema与执行函数前的search_path和current_schema保持一致。 如果函数参数中带有出参，SELECT调用函数必须缺省出参，CALL调用函数适配Oracle必须指定出参，对于调用重载的带有PACKAGE属性的函数，CALL调用函数可以缺省出参，具体信息参见CALL的示例。 兼容PostgreSQL风格的函数或者带有PACKAGE属性的函数支持重载。在指定REPLACE的时候，如果参数个数、类型、返回值有变化，不会替换原有函数，而是会建立新的函数。 SELECT调用可以指定不同参数来进行同名函数调用。由于语法CALL适配自Oracle，因此不支持调用不带有PACKAGE属性的同名函数。 在创建function时，不能在avg函数外面嵌套其他agg函数，或者其他系统函数。 在非逻辑集群模式下，暂不支持将返回值、参数以及变量设置为建在非系统默认安装Node Group的表，sql function内部语句暂不支持对建在非系统默认安装Node Group的表操作。 在逻辑集群模式下，如果函数返回值和参数是用户表类型，所有涉及表都必须在同一逻辑集群内；如果函数内部涉及对多个逻辑集群表操作，函数定义时不能为IMMUTABLE和SHIPPABLE类型，以避免函数被下推到DN执行。 在逻辑集群模式下，函数参数、返回值不能用%type引用表字段类型，否则会导致函数创建失败。 新创建的函数默认会给PUBLIC授予执行权限（详见GRANT）。用户可以选择收回PUBLIC默认执行权限，然后根据需要将执行权限授予其他用户，为了避免出现新函数能被所有人访问的时间窗口，应在一个事务中创建函数并且设置函数执行权限。 在多个CN的集群中，不能将函数的入参或出参设置为临时表类型，因为在非当前连接CN上执行创建函数过程中，无法单独根据表名获取到正确的临时schema，进而无法获取到准确的表类型。

语法格式 兼容PostgreSQL风格的创建自定义函数语法。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 CREATE [ OR REPLACE ] FUNCTION function_name ( [ { argname [ argmode ] argtype [ { DEFAULT | := | = } expression ]} [, ...] ] ) [ RETURNS rettype [ DETERMINISTIC ] | RETURNS TABLE ( { column_name column_type } [, ...] )] LANGUAGE lang_name [ {IMMUTABLE | STABLE | VOLATILE } | {SHIPPABLE | NOT SHIPPABLE} | WINDOW | [ NOT ] LEAKPROOF | {CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT } | {[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | AUTHID DEFINER | AUTHID CURRENT_USER} | {FENCED | NOT FENCED} | {PACKAGE} | COST execution_cost | ROWS result_rows | SET configuration_parameter { {TO | =} value | FROM CURRENT }} ][...] { AS 'definition' | AS 'obj_file', 'link_symbol' } Oracle风格的创建自定义函数的语法。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 CREATE [ OR REPLACE ] FUNCTION function_name ( [ { argname [ argmode ] argtype [ { DEFAULT | := | = } expression ] } [, ...] ] ) RETURN rettype [ DETERMINISTIC ] [ {IMMUTABLE | STABLE | VOLATILE } | {SHIPPABLE | NOT SHIPPABLE} | {PACKAGE} | {FENCED | NOT FENCED} | [ NOT ] LEAKPROOF | {CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT } | {[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | AUTHID DEFINER | AUTHID CURRENT_USER } | COST execution_cost | ROWS result_rows | SET configuration_parameter { {TO | =} value | FROM CURRENT ][...] { IS | AS } plsql_body /

示例 创建名称为server_remote的外部服务器，对应的foreign data wrapper为GC_FDW。 1 CREATE SERVER server_remote FOREIGN DATA WRAPPER GC_FDW OPTIONS (address '10.10.0.100:25000,10.10.0.101:25000',dbname 'test', username 'test', password '{password}'); 在可选项options里面写入了关联集群CN对应的IP地址及端口号。这里推荐使用填写一个LVS的地址或者多个CN的地址。 创建名称为region的外表： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 CREATE FOREIGN TABLE region ( R_REGIONKEY INT4, R_NAME TEXT, R_COMMENT TEXT ) SERVER server_remote OPTIONS ( schema_name 'test', table_name 'region', encoding 'gbk' ); 查看创建的外表region： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 \d+ region Foreign table "public.region" Column | Type | Modifiers | FDW Options | Storage | Stats target | Description -------------+---------+-----------+-------------+----------+--------------+------------- r_regionkey | integer | | | plain | | r_name | text | | | extended | | r_comment | text | | | extended | | Server: server_remote FDW Options: (schema_name 'test', table_name 'region', encoding 'gbk') FDW permission: read only Has OIDs: no Distribute By: ROUND ROBIN Location Nodes: ALL DATANODES

参数说明 IF NOT EXISTS 如果已经存在相同名称的表，不会抛出一个错误，而会发出一个通知，告知表关系已存在。 table_name 外表的表名。 取值范围：字符串，要符合标识符的命名规范。 column_name 外表中的字段名。可以选择多个字段名，中间用“,”隔开。 取值范围：字符串，要符合标识符的命名规范。 不允许在列上创建约束，也不允许创建索引。 type_name 字段的数据类型。 不允许使用序列当作类型和使用自定义类型。 SERVER server_name server名称。允许用户自定义名称。 取值范围：字符串，要符合标识符的命名规范，并且该server必须存在。 OPTIONS ( { option_name ' value ' } [, ...] ) 用于指定外表数据的各类参数，参数类型如下所示。 table_name：对应关联集群的表名，如果省略此option则认为该值和外表名字一样。 schema_name：对应关联集群的schema，如果省略此option则认为该值和外表所在的schema一样。 encoding：对应关联集群的编码，如果省略此option则使用关联集群的数据库编码。 dataencoding：用于实现GDS互联互通时两个latin1库中GBK数据和UTF8数据的同步。该参数仅8.2.0及以上集群版本支持。 当设置该参数时，encoding表示远端集群数据的真实编码，dataencoding表示为本地执行端的集群数据的真实编码。 假如本地执行端和远端两个数据库分别为latin1_local_db和latin1_remote_db，latin1_remote_db中存储着GBK的数据，当把latin1_remote_db中GBK的数据以UTF8的编码迁移到latin1_local_db时，需要在本地端的集群设置外表的dataencoding为UTF8，encoding为GBK。 gds_compress GDS互联互通为了降低网络传输带宽，提供了压缩参数，默认不压缩，当前仅支持设置值为snappy。该参数仅8.2.0及以上集群版本支持。 使用该功能时需要保证本地集群，远端集群，GDS的版本至少在同一版本。 gds_compress与file_type配合使用，当file_type的参数为interconn时，gds需要升级到8.2.0及以上版本，否则会出现ERROR: un-support format报错。 READ ONLY 外表只读。 DISTRIBUTE BY ROUNDROBIN 显示指定外表为ROUNDROBIN分布方式。 TO { GROUP groupname | NODE ( nodename [, ... ] ) } TO GROUP目前不支持使用。TO NODE主要供内部扩容工具使用，一般用户不应使用。

语法格式 1 2 3 4 5 6 7 CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [ { column_name type_name | LIKE source_table } [, ...] ] ) SERVER server_name OPTIONS ( { option_name ' value ' } [, ...] ) [ READ ONLY ] [ DISTRIBUTE BY {ROUNDROBIN} ] [ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ];

示例6 通过外表读取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.cn-north-1.myhuaweicloud.com', AC CES S_KEY 'xxxxxxxxx', SECRET_ACCESS_KEY 'yyyyyyyyyyyyy', TYPE 'OBS' ); ADDRESS是OBS的终端节点（Endpoint），请根据实际替换。也是使用region参数，通过指定regionCode在region_map文件中查找对应的 域名 。 ACCESS_KEY和SECRET_ACCESS_KEY是云账号体系访问密钥。请根据实际替换。 TYPE表示创建的Server为OBS Server。请保持OBS取值不变。 创建OBS外表json_f ，定义字段名，以d#2_e为例，从命名可以看出该字段是数组d的第二个元素里嵌套的e对象。表关联的OBS服务器为obs_server。foldername为外表中数据源文件目录，即表数据目录在OBS上对应的文件目录。 认证用的AK和SK硬编码到代码中或者明文存储都有很大的安全风险，建议在配置文件或者环境变量中密文存放，使用时解密，确保安全。 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)

示例7 通过外表读取 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.cn-north-1.myhuaweicloud.com', ACCESS_KEY 'xxxxxxxxx', SECRET_ACCESS_KEY 'yyyyyyyyyyyyy', TYPE 'DLI', DLI_ADDRESS 'dli.example.com', DLI_ACCESS_KEY 'xxxxxxxxx', DLI_SECRET_ACCESS_KEY 'yyyyyyyyyyyyy' ); ADDRESS是OBS的终端节点（Endpoint）。DLI_ADDRESS是DLI的终端节点（Endpoint），请根据实际替换。 ACCESS_KEY和SECRET_ACCESS_KEY是云账号体系访问OBS服务的密钥。请根据实际替换。 DLI_ACCESS_KEY和DLI_SECRET_ACCESS_KEY是云账号体系访问DLI服务的密钥。请根据实际替换。 TYPE表示创建的Server为DLI Server。请保持DLI取值不变。 创建访问DLI多版本的OBS外表customer_address，不包含分区列，表关联的DLI服务器为dli_server。其中project_id为xxxxxxxxxxxxxxx，dli上的database_name为database123，需要访问的table_name为table456，根据实际替换。 认证用的AK和SK硬编码到代码中或者明文存储都有很大的安全风险，建议在配置文件或者环境变量中密文存放，使用时解密，确保安全。 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)

示例2 通过HDFS只写外表，将TPC-H benchmark测试数据表region中的数据导出至HDFS文件系统的/user/hive/warehouse/mppdb.db/regin_orc/目录下。 创建HDFS SERVER，对应的foreign data wrapper为HDFS_FDW或者DFS_FDW，同示例一。 创建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文件系统写入数据。 1 INSERT INTO ft_wo_region SELECT * FROM region;

示例3（仅存算分离3.0版本支持） 通过HDFS多级分区只写外表将TPC-H benchmark测试数据表region表中的数据导出HDFS文件系统的/user/hive/warehouse/mppdb.db/region_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 16 CREATE FOREIGN TABLE ft_wo_region_partition ( R_REGIONKEY INT4, R_NAME TEXT, R_COMMENT TEXT ) SERVER hdfs_server OPTIONS ( FORMAT 'orc', encoding 'utf8', FOLDERNAME '/user/hive/warehouse/mppdb.db/region_orc/' ) WRITE ONLY PARTITION BY (R_REGIONKEY, R_NAME); 通过只写外表向HDFS文件系统写入数据。 1 INSERT INTO ft_wo_region_partition SELECT * FROM region;

示例4（仅存算分离3.0版本支持） 通过partsupp外表往obs文件系统读取数据。 创建obs server。 认证用的AK和SK硬编码到代码中或者明文存储都有很大的安全风险，建议在配置文件或者环境变量中密文存放，使用时解密，确保安全。 1 2 3 4 5 6 CREATE SERVER obs_srv FOREIGN DATA WRAPPER dfs_fdw OPTIONS ( address 'xxx', type 'obs', access_key 'xxx', secret_access_key 'xxx' ); 创建obs外表partsupp，使用disk cache中的冷缓存。 1 2 3 4 5 6 7 8 9 10 11 12 13 CREATE FOREIGN TABLE PARTSUPP ( PS_PARTKEY BIGINT NOT NULL, PS_SUPPKEY BIGINT NOT NULL, PS_AVAILQTY BIGINT NOT NULL, PS_SUPPLYCOST DECIMAL(15,2) NOT NULL, PS_COMMENT VARCHAR(199) NOT NULL ) SERVER obs_srv options ( encoding 'utf-8', format 'parquet', foldername 'xxx', cache_policy 'NONE' ) READ ONLY DISTRIBUTE BY ROUNDROBIN; 通过外表partsupp往obs上读取数据。 1 SELECT * FROM PARTSUPP;

示例5 关于包含信息约束（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 ='ft_region_pkey'; conname | contype | consoft | conopt | conindid | conkey ----------------+---------+---------+--------+----------+-------- ft_region_pkey | p | t | t | 0 | {1} (1 row) 删除信息约束： 1 2 3 4 5 6 ALTER FOREIGN TABLE ft_region DROP CONSTRAINT ft_region_pkey RESTRICT; SELECT conname, contype, consoft, conindid, conkey FROM pg_constraint WHERE conname ='ft_region_pkey'; conname | contype | consoft | conindid | conkey ---------+---------+---------+----------+-------- (0 rows) 添加一个唯一信息约束： 1 ALTER 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) 删除唯一信息约束： 1 ALTER FOREIGN TABLE ft_region DROP CONSTRAINT constr_unique CASCADE;

语法格式 创建外表。 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 | READ WRITE} ] 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]

信息约束（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。 1 CREATE 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)

功能描述 在当前数据库创建一个HDFS或OBS外表，用来访问存储在HDFS或者OBS分布式集群文件系统上的结构化数据。也可以导出ORC和PARQUET格式数据到HDFS或者OBS上。 数据存储在OBS：数据存储和计算分离，集群存储成本低，存储量不受限制，并且集群可以随时删除，但计算性能取决于OBS访问性能，相对HDFS有所下降，建议在数据计算不频繁场景下使用。 数据存储在HDFS：数据存储和计算不分离，集群成本较高，计算性能高，但存储量受磁盘空间限制，删除集群前需将数据导出保存，建议在数据计算频繁场景下使用。 实时数仓（单机部署）暂不支持HDFS外表。 实时数仓（单机部署）8.2.0及以上集群版本支持OBS外表，但需要指定Server的foreign data wrapper为DFS_FDW。 以下描述的读写外表，仅存算分离3.0版本支持。

注意事项 HDFS外表与OBS外表分为只读外表、只写外表和读写外表，只读外表用于查询操作，只写外表可以将GaussDB(DWS)中的数据导出到分布式文件系统中，读写外表可以查询和数据导出，其中读写外表仅9.1.0.100及以上版本支持。 此方式支持ORC、TEXT、 CS V、CARBONDATA、PARQUET和JSON格式的导入查询，OBS外表支持ORC、PARQUET（仅存算分离3.0版本支持）、CSV和TEXT格式的导出。HDFS外表支持ORC、PARQUET格式的导出。 该方式需要用户手动创建外部服务器，具体请参见CREATE SERVER。 若手动创建Server时指定foreign data wrapper为HDFS_FDW或者DFS_FDW，创建只读外表时需DISTRIBUTE BY子句指定分布方式。

示例 创建外表OBS_ft，用来以txt格式导入OBS上指定的对象数据到row_tbl表中： 认证用的AK和SK硬编码到代码中或者明文存储都有很大的安全风险，建议在配置文件或者环境变量中密文存放，使用时解密，确保安全。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 DROP FOREIGN TABLE IF EXISTS OBS_ft; NOTICE: foreign table "obs_ft" does not exist, skipping DROP FOREIGN TABLE CREATE FOREIGN TABLE OBS_ft( a int, b int)SERVER gsmpp_server OPTIONS (location 'obs://gaussdbcheck/obs_ddl/test_case_data/txt_obs_informatonal_test001',format 'text',encoding 'utf8',chunksize '32', encrypt 'on',ACCESS_KEY 'access_key_value_to_be_replaced',SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',delimiter E'\x08') read only; CREATE FOREIGN TABLE DROP TABLE row_tbl; DROP TABLE CREATE TABLE row_tbl( a int, b int); NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default. HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE INSERT INTO row_tbl select * from OBS_ft; INSERT 0 3

参数概览 创建外表语法提供了多个参数，常用参数分类如下： 必需参数 table_name column_name type_name SERVER gsmpp_server access_key secret_access_key OPTIONS参数 外表的数据源位置参数 location 临时安全凭证参数（8.2.0及以上集群版本支持） security_token 数据格式参数 format header（仅支持CSV格式） delimiter quote（仅支持CSV格式） escape （仅支持CSV格式） null noescaping（仅支持TEXT格式） encoding eol bom（仅支持CSV格式） 容错性参数 fill_missing_fields ignore_extra_data compatible_illegal_chars obs_null_file PER NODE REJECT LIMIT 'val... LOG INTO error_table_name WITH error_table_name 性能优化参数 file_split_threshold（仅支持TEXT格式）

注意事项 这种方式仅支持TEXT和CSV格式，并且需要额外指定OBS连接信息。对于OBS上的ORC、CARBONDATA、PARQUET等格式数据，不适用这种方式，请参考CREATE FOREIGN TABLE (SQL on OBS or Hadoop)。 创建的外表分为只读外表（READ ONLY）和只写外表（WRITE ONLY）。缺省为只读外表。数据导入集群时，请将外表设为READ ONLY；导出时，请设为WRITE ONLY。 外表由命令执行者所有； OBS外表不需要显式指定分布方式，默认支持ROUNDROBIN分布方式； 所创建外表只对信息约束（Informational Constraint）约束生效。 OBS导入导出数据时，不支持中文路径。

语法格式 1 2 3 4 5 6 7 8 CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( { column_name type_name [column_constraint ] | LIKE source_table | table_constraint [, ...]} [, ...] ) SERVER server_name OPTIONS ( { option_name ' value ' } [, ...] ) [ { WRITE ONLY | READ ONLY }] [ WITH error_table_name | LOG INTO error_table_name] [PER NODE REJECT LIMIT 'value'] ;

参数概览 创建外表语法提供了多个参数，常用参数分类如下。 必需参数 table_name column_name type_name SERVER gsmpp_server OPTIONS可选参数 外表的数据源位置参数location 数据格式参数 format header（仅支持CSV，FIXED格式） fileheader（仅支持CSV，FIXED格式） out_filename_prefix delimiter quote（仅支持CSV格式） escape（仅支持CSV格式） null noescaping（仅支持TEXT格式） encoding dataencoding （仅支持latin1数据库） eol conflict_delimiter file_type auto_create_pipe del_pipe gds_compress preserve_blanks（仅支持FIXED格式) blank_number_str_to_null 容错性参数 fill_missing_fields ignore_extra_data reject_limit compatible_illegal_chars replace_illegal_chars 性能参数 file_sequence 可选参数 WITH error_table_name LOG INTO error_table_name REMOTE LOG 'name' PER NODE REJECT LIMIT 'value'

示例 创建外表customer_ft，用来以TEXT格式导入GDS服务器10.10.123.234上的数据： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 CREATE FOREIGN TABLE customer_ft ( c_customer_sk integer , c_customer_id char(16) , c_current_cdemo_sk integer , c_current_hdemo_sk integer , c_current_addr_sk integer , c_first_shipto_date_sk integer , c_first_sales_date_sk integer , c_salutation char(10) , c_first_name char(20) , c_last_name char(30) , c_preferred_cust_flag char(1) , c_birth_day integer , c_birth_month integer , c_birth_year integer , c_birth_country varchar(20) , c_login char(13) , c_email_address char(50) , c_last_review_date char(10) ) SERVER gsmpp_server OPTIONS ( location 'gsfs://10.10.123.234:5000/customer1*.dat', FORMAT 'TEXT' , DELIMITER '|', encoding 'utf8', mode 'Normal') READ ONLY; 创建外表foreign_HR_staffS_ft，用来以TEXT格式导入GDS服务器192.168.0.90和192.168.0.91上的数据，导入过程错误信息将记录到err_HR_staffS中。本次数据导入允许出现的数据格式错误个数为2。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 CREATE FOREIGN TABLE foreign_HR_staffS_ft ( staff_ID NUMBER(6) , FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(25), EMAIL VARCHAR2(25), PHONE_NUMBER VARCHAR2(20), HIRE_DATE DATE, employment_ID VARCHAR2(10), SALARY NUMBER(8,2), COMMISSION_PCT NUMBER(2,2), MANAGER_ID NUMBER(6), section_ID NUMBER(4) ) SERVER gsmpp_server OPTIONS (location 'gsfs://192.168.0.90:5000/* | gsfs://192.168.0.91:5000/*', format 'TEXT', delimiter E'\x08', null '',reject_limit '2') WITH err_HR_staffS_ft; 建立外表，用来以CSV格式导入input_data目录下存放在各个节点名文件下的所有文件。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 CREATE FOREIGN TABLE foreign_HR_staffS_ft1 ( staff_ID NUMBER(6) , FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(25), EMAIL VARCHAR2(25), PHONE_NUMBER VARCHAR2(20), HIRE_DATE DATE, employment_ID VARCHAR2(10), SALARY NUMBER(8,2), COMMISSION_PCT NUMBER(2,2), MANAGER_ID NUMBER(6), section_ID NUMBER(4) ) SERVER gsmpp_server OPTIONS (location 'file:///input_data/*', format 'csv', quote E'\x08', mode 'private', delimiter ',') WITH err_HR_staffS_ft1; 建立外表，用来以CSV格式导出数据到output_data目录下。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 CREATE FOREIGN TABLE foreign_HR_staffS_ft2 ( staff_ID NUMBER(6) , FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(25), EMAIL VARCHAR2(25), PHONE_NUMBER VARCHAR2(20), HIRE_DATE DATE, employment_ID VARCHAR2(10), SALARY NUMBER(8,2), COMMISSION_PCT NUMBER(2,2), MANAGER_ID NUMBER(6), section_ID NUMBER(4) ) SERVER gsmpp_server OPTIONS (location 'file:///output_data/', format 'csv', quote E'\x08', delimiter '|', header 'on') WRITE ONLY;

语法格式 1 2 3 4 5 6 7 8 9 CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [ { column_name type_name POSITION(offset,length) | LIKE source_table } [, ...] ] ) SERVER gsmpp_server OPTIONS ( { option_name ' value ' } [, ...] ) [ { WRITE ONLY | READ ONLY }] [ WITH error_table_name | LOG INTO error_table_name] [REMOTE LOG 'name'] [PER NODE REJECT LIMIT 'value'] [ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ];

示例 创建一个GBK编码的数据库music（本地环境的编码格式必须也为GBK）： 1 CREATE DATABASE music ENCODING 'GBK' template = template0; 创建数据库music2，并指定所有者为jim： 1 CREATE DATABASE music2 OWNER jim; 用模板template0创建数据库music3，并指定所有者为jim： 1 CREATE DATABASE music3 OWNER jim TEMPLATE template0; 创建兼容ORA格式的数据库： 1 CREATE DATABASE ora_compatible_db DBCOMPATIBILITY 'ORA';

语法格式 1 2 3 4 5 6 7 8 9 10 CREATE DATABASE database_name [ [ WITH ] { [ OWNER [=] user_name ] | [ TEMPLATE [=] template ] | [ ENCODING [=] encoding ] | [ LC_COLLATE [=] lc_collate ] | [ LC_CTYPE [=] lc_ctype ] | [ DBCOMPATIBILITY [=] compatibility_type ] | [ DBCOMPATIBILITY_BEHAVIOR [=] opt_compat_behavior] | [ CONNECTION LIMIT [=] connlimit ]}[...] ];

示例 为customer.c_customer_sk列加注释： 1 COMMENT ON COLUMN customer.c_customer_sk IS 'Primary key of customer demographics table.'; 为tpcds.customer_details_view_v2视图加注释： 1 COMMENT ON VIEW tpcds.customer_details_view_v2 IS 'View of customer detail'; 为customer表加注释： 1 COMMENT ON TABLE customer IS 'This is my table';

语法格式 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 COMMENT ON { AGGREGATE agg_name (agg_type [, ...] ) | CAST (source_type AS target_type) | COLLATION object_name | COLUMN { table_name.column_name | view_name.column_name } | CONSTRAINT constraint_name ON table_name | CONVERSION object_name | DATABASE object_name | DOMAIN object_name | EXTENSION object_name | FOREIGN DATA WRAPPER object_name | FOREIGN TABLE object_name | FUNCTION function_name ( [ {[ argmode ] [ argname ] argtype} [, ...] ] ) | INDEX object_name | LARGE OBJECT large_object_oid | OPERATOR operator_name (left_type, right_type) | OPERATOR CLASS object_name USING index_method | OPERATOR FAMILY object_name USING index_method | [ PROCEDURAL ] LANGUAGE object_name | ROLE object_name | RULE rule_name ON table_name | SCHEMA object_name | SERVER object_name | TABLE object_name | TABLESPACE object_name | TEXT SEARCH CONFIGURATION object_name | TEXT SEARCH DICTIONARY object_name | TEXT SEARCH PARSER object_name | TEXT SEARCH TEMPLATE object_name | TYPE object_name | VIEW object_name } IS 'text';