功能描述

在当前数据库创建一个HDFS外表，用来访问存储在HDFS分布式集群文件系统上的Hadoop结构化数据。HDFS外表是只读的，只能用于查询操作，可直接使用SELECT查询其数据。

语法格式

创建HDFS外表。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

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为：

  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表支持的数据类型有：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	支持	支持	支持	不支持	不支持	不支持

• 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

  禁止利用信息约束对执行计划优化。