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

创建GDS外表

外表中配置了数据源格式信息、GDS服务的访问信息,从而GaussDB(DWS)最终可以通过外表将数据服务器上的数据引流进数据库实表中。

操作步骤

  1. 收集数据源格式信息、GDS服务的访问信息。

    需要收集的主要数据源格式信息如下:

    • format:GDS外表导入支持CSV、TEXT和FIXED格式。请确认存放在数据服务器上待入库数据的格式。例如,假设待入库的数据为CSV格式。
    • header(仅支持CSV,FIXED格式):确认数据文件是否包含标题行。
    • delimiter:确认数据文件中,字段间的分隔符。例如,假设是以英文逗号分隔的。
    • encoding:数据源文件的数据编码格式。例如,假设为UTF-8。
    • eol:确认数据文件中,行间的换行符。例如,默认的换行符,如0x0D0A、0X0A,或者自定义的换行符,如字符串!@#。该参数仅支持TEXT格式导入。
    • 外表可识别的其他更多格式信息请参见数据格式参数。

    需要收集的GDS服务的访问信息如下:

    location:GDS服务的访问地址。例如以安装配置和启动GDS中的GDS信息为例。非SSL模式下的location为:gsfs://192.168.0.90:5000//input_data/ 。SSL模式下的location为:gsfss://192.168.0.90:5000//input_data/ 。其中,“192.168.0.90:5000”为GDS服务的IP及端口号;“input_data”为GDS服务管理的数据源文件所在的路径。请根据实际情况替换。

  2. 依据数据源文件中的数据情况,设计导入容错机制。

    GaussDB(DWS)支持如下的数据容错性处理,相当于数据入库前对数据做初步的简单清洗。
    • 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:用于记录数据格式错误信息的错误表表名。并行导入结束后查询此错误信息表,能够获取详细的错误信息。
    • remote log 'name':数据导入过程中的数据格式错误信息是否同时在GDS服务器上以文件方式保存。name为错误数据文件的文件名前缀。
    • 关于容错性参数的更多信息请参考容错性参数。

  3. 使用gsql或Data Studio连接数据库后,根据前面步骤所收集和规划的信息参数,创建GDS外表。

    示例如下:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    CREATE FOREIGN TABLE foreign_tpcds_reasons
    (
      r_reason_sk integer not null,
      r_reason_id char(16) not null,
      r_reason_desc char(100)
    )
     SERVER gsmpp_server
     OPTIONS 
    (
    LOCATION 'gsfs://192.168.0.90:5000/input_data | gsfs://192.168.0.91:5000/input_data', 
    FORMAT 'CSV' ,
    DELIMITER ',',
    ENCODING 'utf8',
    HEADER 'false',
    FILL_MISSING_FIELDS 'true',
    IGNORE_EXTRA_DATA 'true'
    )
    LOG INTO product_info_err 
    PER NODE REJECT LIMIT 'unlimited';
    

    示例中的各项说明如下:

    • 外表字段需与数据库中即将存储数据的事实表保持一致。
    • 对于GDS导入,SERVER gsmpp_server请保持不变。
    • location参数请根据1中收集的GDS服务访问信息修改。注意GDS使用SSL加密传输时,需要将“gsfs”替换为“gsfss”。
    • FORMAT、DELIMITER、ENCODING、HEADER请根据1中收集的数据源格式信息填写。
    • FILL_MISSING_FIELDS、IGNORE_EXTRA_DATA、LOG INTO及PER NODE REJECT LIMIT请根据2中设计的导入容错机制填写。注意LOG INTO是指将数据格式错误录入哪个错误表,即其取值为错误表表名。

    CREATE FOREIGN TABLE语法的更多信息,请参考CREATE FOREIGN TABLE (GDS导入导出)。

任务示例

除了以下示例,更多外表创建的示例请参考GDS导入示例

  • 示例1:创建GDS外表foreign_tpcds_reasons,数据格式为CSV。
    1
    2
    3
    4
    5
    6
    7
    CREATE FOREIGN TABLE foreign_tpcds_reasons
    (
      r_reason_sk integer not null,
      r_reason_id char(16) not null,
      r_reason_desc char(100)
    )
     SERVER gsmpp_server OPTIONS (location 'gsfs://192.168.0.90:5000/* | gsfs://192.168.0.91:5000/*', FORMAT 'CSV',MODE 'Normal', ENCODING 'utf8', DELIMITER E'\x08', QUOTE E'\x1b', NULL '');
    
  • 示例2:创建GDS导入外表foreign_tpcds_reasons_SSL,使用SSL加密传输的模式传输,数据格式为CSV。
    1
    2
    3
    4
    5
    6
    7
    CREATE FOREIGN TABLE foreign_tpcds_reasons_SSL
    (
      r_reason_sk integer not null,
      r_reason_id char(16) not null,
      r_reason_desc char(100)
    )
     SERVER gsmpp_server OPTIONS (location 'gsfss://192.168.0.90:5000/* | gsfss://192.168.0.91:5000/*', FORMAT 'CSV',MODE 'Normal', ENCODING 'utf8', DELIMITER E'\x08', QUOTE E'\x1b', NULL '');
    
  • 示例3:创建GDS外表foreign_tpcds_reasons,数据格式为TEXT。
    1
    2
    3
    4
    5
    6
    CREATE FOREIGN TABLE foreign_tpcds_reasons
    (
      r_reason_sk integer not null,
      r_reason_id char(16) not null,
      r_reason_desc char(100)
    ) SERVER gsmpp_server OPTIONS (location 'gsfs://192.168.0.90:5000/* | gsfs://192.168.0.91:5000/*', FORMAT 'TEXT', delimiter E'\x08',  null '',reject_limit '2',EOL '0x0D') WITH err_foreign_tpcds_reasons;