更新时间:2024-10-25 GMT+08:00

CREATE FOREIGN TABLE (SQL on OBS or Hadoop)

功能描述

在当前数据库创建一个HDFS或OBS外表,用来访问存储在HDFS或者OBS分布式集群文件系统上的结构化数据。也可以导出ORC格式数据到HDFS或者OBS上。

数据存储在OBS:数据存储和计算分离,集群存储成本低,存储量不受限制,并且集群可以随时删除,但计算性能取决于OBS访问性能,相对HDFS有所下降,建议在数据计算不频繁场景下使用。

数据存储在HDFS:数据存储和计算不分离,集群成本较高,计算性能高,但存储量受磁盘空间限制,删除集群前需将数据导出保存,建议在数据计算频繁场景下使用。

实时数仓(单机部署)暂不支持OBS和HDFS外表导入导出功能。

注意事项

  • HDFS外表与OBS外表分为只读外表和只写外表,只读外表用于查询操作,只写外表可以将GaussDB(DWS)中的数据导出到分布式文件系统中。
  • 支持ORC、TEXT、CSV、CARBONDATA、PARQUET和JSON格式的导入查询,OBS外表支持ORC、CSV和TEXT格式的导出。HDFS外表仅支持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

    外表中的字段名。可以选择多个字段名,中间用“,”隔开。

    取值范围:字符串,要符合标识符的命名规范。

    JSON对象由嵌套或并列的name-value对组成,具有顺序无关性,当导入JSON格式数据时,需要通过字段名与name的自动对应来确定字段与value的对应关系。用户需要定义恰当的字段名,否则可能导致导入结果不符合预期。字段名与name的自动对应规则如下:

    • 无嵌套无数组的情况下,字段名应当与name一致,不区分大小写。
    • 字段名使用‘_’字符拼接两个name,标识嵌套关系。
    • 字段名使用‘#’字符加十进制非负整数‘n’标识数组的第n个元素(从0开始)。

    例如,要导入JSON对象{"A" : "simple", "B" : {"C" : "nesting"}, "D" : ["array", 2, {"E" : "complicated"}]}中的每个元素,外表字段名应当分别定义为a、b、b_c、d、d#0、d#1、d#2、d#2_e,字段的定义顺序不会影响导入结果的正确性。

  • type_name

    字段的数据类型。

  • 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'。

    • location

      OBS外表参数,指定存储在OBS上的文件路径,多个桶的数据源数据之间使用分隔符‘|’进行分割,例如:LOCATION 'obs://bucket1/folder/ | obs://bucket2/',数据库将会扫描指定路径文件夹下面的所有对象。

      当访问DLI多版本表时,无需指定location参数。

    • format:外表中数据源文件的格式。
      • HDFS外表READ ONLY外表支持ORC、TEXT、JSON、CSV、PARQUET文件格式,而WRITE ONLY外表只支持ORC文件格式。
      • OBS外表READ ONLY外表支持ORC、TEXT、JSON、CSV、CARBONDATA、PARQUET文件格式,而WRITE ONLY外表只支持ORC文件格式。

      对于JSON格式数据,仅支持JSON对象(object,最外层由{}构造)导入,不支持JSON数组(array,最外层由[]构造)导入,但支持JSON对象内部数组的导入。

    • foldername:外表中数据源文件目录,即表数据目录在HDFS文件系统和OBS上对应的文件目录。此选项对WRITE ONLY外表为必选项,对READ ONLY外表为可选项。

      当访问DLI多版本表时,无需指定foldername参数。

    • encoding:外表中数据源文件的编码格式名称,缺省为utf8。此选项为可选参数。
    • totalrows:可选参数,估计表的行数,仅OBS外表使用。由于OBS上文件可能很多,执行analyze可能会很慢,通过此参数让用户设置一个预估的值,使优化器能通过这个值做大小表的估计。一般预估值和实际值相近时,查询效率较高。
    • filenames:外表中数据源文件,以","间隔。
      • 推荐通过使用foldername参数指定数据源的位置,对于只读外表filenames参数与foldername参数两者必有其一,而只写外表只能通过foldername指定。
      • foldername为绝对目录时,前后必须有'/', 多个路径用', '分隔。
      • 查询分区表时,会先根据分区信息进行剪枝,然后查询满足条件的数据文件。由于剪枝操作会涉及多次扫描HDFS分区目录内容,不建议使用重复度非常小的列作为分区列,因为这可能导致分区目录非常的多,增加对HDFS的查询压力。
      • OBS只读外表不支持。
    • delimiter

      指定数据文件行数据的字段分隔符,不指定则使用默认分隔符,TEXT格式的默认分隔符是水平制表符(tab)。

      • 分隔符不能是\r和\n。
      • 分隔符不能和null参数相同。
      • 分隔符不能包含“\”、“.”、数字和字母。
      • 数据文件中单行数据长度需<1GB,如果分隔符较长且数据列较多的情况下,会影响导出有效数据的长度。
      • 分隔符推荐使用多字符(例如'$^&')和不可见字符(例如0x07、0x08、0x1b等)。
      • delimiter参数只在TEXT和CSV格式下有效。

      取值范围:

      支持多字符分隔符,但分隔符不能超过10个字节。

    • eol

      指定导入数据文件换行符样式。

      取值范围:支持多字符换行符,但换行符不能超过10个字节。常见的换行符,如\r、\n、\r\n(设成0x0D、0x0A、0x0D0A效果是相同的),其他字符或字符串,如$、#。

      • eol参数只能用于TEXT格式的导入。
      • eol参数不能和分隔符、null参数相同。
      • eol参数不能包含:数字,字母和符号“.”。
    • 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格式下有效。

    • 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多版本表所在的数据库名称,该参数仅支持server类型为DLI时设置。该参数仅8.1.1及以上版本支持。

    • dli_table_name

      待访问的DLI多版本表的名称,该参数仅支持server类型为DLI时设置。该参数仅8.1.1及以上版本支持。

    • checkencoding

      是否检查字符编码

      取值范围:low,high 。缺省值为low。

      TEXT格式下,导入非法字符容错规则如下:

      • 对于'\0',容错后转换为空格;
      • 对于其他非法字符,容错后转换为问号;
      • 若checkencoding为low标识,导入时对于非法字符进行容错处理,则若NULL、DELIMITER设置为空格或问号则会通过如"illegal chars conversion may confuse null 0x20"等报错信息提示用户修改可能引起混淆的参数以避免导入错误。

      ORC格式下,导入非法字符容错规则如下:

      • checkencoding为low标识,若导入时检查到某个字段中包含非法字符,则自动将当前列当前行的字段整体替换为同样长度的‘?’字符;
      • checkencoding为high标识,若导入时检查到某个字段中包含非法字符,则报错退出。
    • force_mapping

      JSON格式下,外表列无法匹配到正确的name-value键值对时的处理方式。

      取值范围:true,false。缺省值为true。

      • force_mapping为true,相应的列填null,该null与JSON定义中的null含义相同。
      • force_mapping为false,查询报错,提示不存在这样的列。

      由于对JSON对象没有限制,但外表字段定义需要符合GaussDB(DWS)的标识符规范(例如长度、字符等限制),因此这种导入方式可能导致异常:例如,字段无法正确标识JSON name、字段需重复定义等。建议使用容错性选项force_mapping或json操作符(可参考JSON/JSONB函数和操作符)来规避。

      JSON格式下执行SELECT COUNT(*) 不会去解析具体字段,因此不会对字段缺失、格式错误等情况报错。

    表1 text、csv、json、orc、carbondata、parquet格式对OBS外表的option支持说明

    参数名称

    OBS

    -

    TEXT

    CSV

    JSON

    ORC

    CARBONDATA

    PARQUET

    READ ONLY

    READ ONLY

    READ ONLY

    READ ONLY

    WRITE ONLY

    READ 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

    ×

    ×

    ×

    force_mapping

    ×

    ×

    ×

    ×

    ×

    ×

    表2 text、csv、json、orc、parquet格式对HDFS外表的option支持说明

    参数名称

    HDFS

    -

    TEXT

    CSV

    JSON

    ORC

    PARQUET

    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

    ×

    ×

    ×

    ×

    ×

    ×

    force_mapping

    ×

    ×

    ×

    ×

    ×

  • WRITE ONLY | READ ONLY

    WRITE ONLY指定创建HDFS/OBS的只写外表。

    READ ONLY指定创建HDFS/OBS的只读外表。

    如果不指定创建的外表的类型,默认为只读外表。

  • DISTRIBUTE BY ROUNDROBIN

    指定HDFS/OBS外表为ROUNDROBIN分布方式。

  • DISTRIBUTE BY REPLICATION

    指定HDFS外表为REPLICATION分布方式。

  • PARTITION BY ( column_name ) AUTOMAPPED

    column_name指定分区列。对于分区表,AUTOMAPPED表示HDFS分区外表指定的分区列会和HDFS数据中的分区目录信息自动对应,前提是必须保证HDFS分区外表指定分区列的顺序和HDFS数据中分区目录定义的顺序一致,该功能只适用于只读外表,只写外表不支持。

    • HDFS的只读和只写外表都支持分区表,但是只写外表只支持一级分区,不支持多级分区。
    • OBS只读外表支持分区表,OBS只写外表不支持分区表。
    • 不支持浮点类型和布尔类型的列作为分区列。
    • 分区字段长度限制可通过guc参数 "dfs_partition_directory_length" 调整。
    • 分区目录名称由 "分区列名=分区列值" 组成。此名称内如果包含特殊字符还会经过转义,所以转义前推荐长度不要超过 (dfs_partition_directory_length + 1) / 3 ,以此保证其转义后总长度不会因为超过dfs_partition_directory_length而出现报错。
    • 不推荐包含过长中文字符的列作为分区列。因为一个中文字符和一个英文字符所占的空间大小并不一致,用户不易计算最终的分区目录名称长度,更容易触发超过dfs_partition_directory_length长度限制的报错。
  • 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_4region表的文件路径为/user/hive/warehouse/gauss.db/region_orc11_64stripe/

  1. 创建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,这里推荐使用该种主备方式填写。两组参量中间使用“,”进行分割
  2. 创建HDFS外表。表关联的HDFS server为hdfs_server,表ft_region对应的HDFS服务器上的文件格式为‘orc’,在HDFS文件系统上对应的文件目录为'/user/hive/warehouse/gauss.db/region_orc11_64stripe/'。
    • 创建不包含分区列的HDFS外表:
       1
       2
       3
       4
       5
       6
       7
       8
       9
      10
      11
      12
      13
      14
      15
      16
      17
      DROP FOREIGN TABLE IF EXISTS ft_region;
      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/gauss.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的存储分布方式。

  3. 查看创建的外表:
    1
    2
    SELECT * FROM pg_foreign_table WHERE ftrelid='ft_region'::regclass;
    SELECT * FROM pg_foreign_table WHERE ftrelid='ft_part'::regclass;
    

示例2

通过HDFS只写外表,将TPC-H benchmark测试数据表region中的数据导出至HDFS文件系统的/user/hive/warehouse/gauss.db/regin_orc/目录下。

  1. 创建HDFS外表,对应的foreign data wrapper为HDFS_FDW或者DFS_FDW,同示例一。
  2. 创建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/gauss.db/regin_orc/'
    )
    WRITE ONLY;
    
  3. 通过只写外表向HDFS文件系统写入数据。
    1
    INSERT INTO ft_wo_region 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/gauss.db/region_orc11_64stripe')
    DISTRIBUTE BY roundrobin;
    
  • 查看region表是否有信息约束索引:
    1
    SELECT relname,relhasindex FROM pg_class WHERE oid='ft_region'::regclass;
    
    图1 查看relname
    1
    SELECT conname, contype, consoft, conopt, conindid, conkey FROM pg_constraint WHERE conname ='ft_region_pkey';
    
    图2 查看信息约束索引
  • 删除信息约束:
    1
    2
    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';
    
    图3 删除信息约束
  • 添加一个唯一信息约束:
    1
    ALTER FOREIGN TABLE ft_region ADD CONSTRAINT constr_unique UNIQUE(R_REGIONKEY) NOT ENFORCED;
    
    删除唯一信息约束:
    1
    2
    ALTER FOREIGN TABLE ft_region DROP CONSTRAINT constr_unique RESTRICT;
    SELECT conname, contype, consoft, conindid, conkey FROM pg_constraint WHERE conname ='constr_unique';
    
  • 添加一个唯一信息约束:
    1
    2
    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;
    
    删除唯一信息约束:
    1
    ALTER FOREIGN TABLE ft_region DROP CONSTRAINT constr_unique CASCADE;
    

示例4

通过外表读取OBS上的json数据。

  1. 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"}]}
  2. 创建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.example.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取值不变。
  3. 创建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;
    
  4. 查询外表json_f。由于容错性参数force_mapping默认打开,json对象缺失的字段会填NULL;json对象name重复的以最后一次出现的name为准。
    1
    SELECT * FROM json_f;
    
    图4 查看外表json_f结果

示例5

通过外表读取DLI多版本外表。DLI多版本外表示例仅8.1.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.example.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服务的密钥。请根据实际替换。
    • 认证用的AK和SK硬编码到代码中或者明文存储都有很大的安全风险,建议在配置文件或者环境变量中密文存放,使用时解密,确保安全。
    • TYPE表示创建的Server为DLI Server。请保持DLI取值不变。
  2. 创建访问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;
    
  3. 通过外表查询DLI多版本表的数据。
    1
    SELECT COUNT(*) FROM customer_address;
    
    图5 查询结果