更新时间:2022-07-29 GMT+08:00

创建OBS外表

操作步骤

  1. 根据上传数据到OBS中规划的路径,由此确定创建外表时使用的参数loaction的值。
  2. 用户获取OBS访问协议对应的AK值和SK值。获取访问密钥,请登录管理控制台,单击右上角的用户名并选择菜单“我的凭证”,然后在左侧导航树单击“访问密钥”。在访问密钥页面,可以查看已有的访问密钥ID(即AK),如果要同时获取AK和SK,可以单击“新增访问密钥”创建并下载访问密钥。
  3. 梳理待导入数据的格式信息,确定创建外表时使用的数据格式参数的值。需要收集的主要数据源格式信息如下:

    • format:外表中数据源文件的格式。OBS外表导入支持CSV、TEXT格式。缺省值为TEXT。
    • header:指定导出数据文件是否包含标题行,header只能用于CSV格式的文件中。
    • delimiter:指定数据文件行数据的字段分隔符,不指定则使用默认分隔符。
    • 外表可以识别的更多参数,详细使用请参见数据格式参数 。

  4. 规划并行导入容错性,以控制导入过程中处理错误的方式。

    • fill_missing_fields:数据入库时,数据源文件中某行的最后一个字段缺失时,请选择是直接将字段设为Null,还是在错误表中报错提示。
    • ignore_extra_data:数据源文件中的字段比外表定义列数多时,请选择是忽略多出的列,还是在错误表中报错提示。
    • per node reject limit:本次数据导入过程中每个DN实例上允许出现的数据格式错误的数量。如果有一个DN实例上录入错误表中的错误数量超过设定值时,本次导入失败,报错退出。可以选择不做限制,也可以根据所能容忍的错误数量选择一个上限值。
    • compatible_illegal_chars:导入时遇到非法字符,选择如何处理。是将非法字符按照转换规则转换后入库,还是报错中止导入。

      非法字符容错转换规则如下:

      • 对于'\0',容错后转换为空格。
      • 对于其他非法字符,容错后转换为问号。
      • 对非法字符进行容错转换时,如遇NULL、DELIMITER、QUOTE、ESCAPE也设置成了空格或问号,GaussDB(DWS)会通过如"illegal chars conversion may confuse COPY escape 0x20"等报错信息提示用户修改可能引起混淆的参数以避免导入错误。
    • error_table_name:用于记录数据格式错误信息的错误表表名。并行导入结束后查询此错误信息表,能够获取详细的错误信息。
    • 更多参数,详细使用请参见容错性参数。

  5. 根据前面步骤确定的参数,创建OBS外表。外表的创建语法以及详细使用,请参考CREATE FOREIGN TABLE (OBS导入导出)。

示例

GaussDB(DWS)数据库中,创建一个外表。参数信息如下所示:

  • 数据格式参数访问秘钥(AK和SK)
    • 用户获取OBS访问协议对应的AK值(access_key)。
    • 用户获取OBS访问协议对应的SK值 (secret_access_key)。

    请根据用户实际获取的access_key和secret_access_key的密钥替换示例中的内容。

  • 设置数据格式参数
    • 数据源文件格式(format)为CSV。
    • 编码格式(encoding)为UTF-8。
    • 是否使用加密(encrypt),默认为 'off'。
    • 字段分隔符(delimiter)为','。
    • 引号字符(quote)使用默认值双引号。
    • null(数据文件中空值的表示)为“一个没有引号的空字符串”。
    • header(指定导出数据文件是否包含标题行)为false,当数据文件第一行不是标题行(即表头),不需要设置。

      OBS导出数据时不支持该参数为ture,使用缺省值false。

  • 设置导入时的容错性参数
    • PER NODE REJECT LIMIT 'value' 为unlimited,即接受导入过程中所有数据格式错误。
    • LOG INTO error_table_name 指定为product_info_err,将数据导入过程中出现的数据格式错误信息写入表product_info_err。
    • fill_missing_fields 为true,即当数据加载时,若数据源文件中一行数据的最后一个字段缺失,则把最后一个字段的值设置为NULL,不报错。
    • ignore_extra_data为true,当数据加载时,若数据源文件比外表定义列数多,则忽略行尾多出来的列,不报错。

根据以上信息,创建的外表如下所示:

 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
DROP FOREIGN TABLE product_info_ext;

CREATE FOREIGN TABLE product_info_ext
(
    product_price                integer        not null,
    product_id                   char(30)       not null,
    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 gsmpp_server 
OPTIONS(

LOCATION 'obs://mybucket/input_data/product_info | obs://mybucket02/input_data/product_info',
FORMAT 'CSV' ,
DELIMITER ',',
encoding 'utf8',
header 'false',
ACCESS_KEY 'access_key_value_to_be_replaced',
SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
fill_missing_fields 'true',
ignore_extra_data 'true'
)
READ ONLY 
LOG INTO product_info_err 
PER NODE REJECT LIMIT 'unlimited';

返回如下信息表示创建成功:

CREATE FOREIGN TABLE