创建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，还是在错误表中报错提示。
     

     取值范围：true/on，false/off。

     • 参数为true/on，当数据导入时，若数据源文件中一行数据的最后一个字段缺失，则把最后一个字段的值设置为NULL，不报错。
     • 参数为false/off，如果最后一个字段缺失会显示如下错误信息。

       missing data for column "tt"

     缺省值：false/off。

   • ignore_extra_data：数据源文件中的字段比外表定义列数多时，请选择是忽略多出的列，还是在错误表中报错提示。
     

     取值范围：true/on、false/off。

     • 参数为true/on，若数据源文件比外表定义列数多，则忽略行尾多出来的列。
     • 参数为false/off，若数据源文件比外表定义列数多，会显示如下错误信息。

       1	extra data after last expected column

     缺省值：false/off。

   • per node reject limit：本次数据导入过程中每个DN实例上允许出现的数据格式错误的数量。如果有一个DN实例上录入错误表中的错误数量超过设定值时，本次导入失败，报错退出。可以选择不做限制，也可以根据所能容忍的错误数量选择一个上限值。
   • compatible_illegal_chars：导入时遇到非法字符，选择如何处理。是将非法字符按照转换规则转换后入库，还是报错中止导入。
     

     取值范围：true/on，false/off。

     • 参数为true/on，则导入时遇到非法字符进行容错处理，非法字符转换后入库，不报错，不中断导入。
     • 参数为false/off，导入时遇到非法字符进行报错，中断导入。

     缺省值：false/off。

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

     • 对于'\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导出数据时不支持该参数为true，使用缺省值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”，当数据加载时，若数据源文件比外表定义列数多，则忽略行尾多出来的列，不报错。

根据以上信息，创建的外表如下所示：

认证用的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
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';

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

1

CREATE FOREIGN TABLE