更新时间:2022-06-13 GMT+08:00

创建外表

当完成创建外部服务器后,在GaussDB(DWS)数据库中创建一个OBS/HDFS只写外表,用来访问存储在OBS/HDFS上的数据。此外表是只写的,只能用于导出操作。

创建外表的语法格式如下,详细的描述请参见CREATE FOREIGN TABLE (SQL on Hadoop or OBS)。
 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 dfs_server 
    OPTIONS ( { option_name ' value ' } [, ...] ) 
    [ {WRITE ONLY }]
    DISTRIBUTE BY {ROUNDROBIN | REPLICATION}
    [ PARTITION BY ( column_name ) [ AUTOMAPPED ] ] ;

例如,创建一个名为"product_info_ext_obs"的外表,对语法中的参数按如下描述进行设置:

  • table_name

    外表的表名。

  • 表字段定义
    • column_name:外表中的字段名。
    • type_name:字段的数据类型。

    多个字段用“,”隔开。

  • SERVER dfs_server

    外表的外部服务器名称,这个server必须存在。外表通过设置外部服务器连接OBS/HDFS读取数据。

    此处应填写为参照9.2.3创建外部服务器创建的外部服务器名称。

  • OPTIONS 参数

    用于指定外表数据的各类参数,关键参数如下所示。

    • “format”:表示导出的数据文件格式,支持“orc”格式。
    • “foldername”:必选参数。外表中数据源文件目录。OBS:数据源文件的OBS路径,此处仅需要填写“/桶名/文件夹目录层级/”,不包括OBS服务的终端节点(Endpoint)。HDFS:HDFS文件系统上的路径。此选项对WRITE ONLY外表为必选项。
    • “encoding”:外表中数据源文件的编码格式名称,缺省为utf8。
    • 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。

    • dataencoding

      在数据库编码与数据表的数据编码不一致时,该参数用于指定导出数据表的数据编码。比如数据库编码为Latin-1,而导出的数据表中的数据为UTF-8编码。此选项为可选项,如果不指定该选项,默认采用数据库编码。此语法仅对HDFS的WRITE ONLY外表有效。

      取值范围:该数据库编码支持转换的数据编码。

      dataencoding参数只对ORC格式的WRITE ONLY的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
    DROP FOREIGN TABLE IF EXISTS product_info_ext_obs;
    
    ---建立不包含分区列的OBS外表,表关联的外部服务器为obs_server,表对应的OBS服务上的文件格式为‘orc’,OBS上的数据存储路径为'/mybucket/data/'。
    
    CREATE FOREIGN TABLE product_info_ext_obs
    (
        product_price                integer        ,
        product_id                   char(30)       ,
        product_time                 date           ,
        product_level                char(10)       ,
        product_name                 varchar(200)   ,
        product_type1                varchar(20)    ,
        product_type2                char(10)       ,
        product_monthly_sales_cnt    integer        ,
        product_comment_time         date           ,
        product_comment_num          integer        ,
        product_comment_content      varchar(200)                      
    ) SERVER obs_server 
    OPTIONS (
    format 'orc', 
    foldername '/mybucket/demo.db/product_info_orc/',
       compression 'snappy',
        version '0.12'
    ) Write Only;