网络
虚拟私有云 VPC
弹性公网IP EIP
弹性负载均衡 ELB
NAT网关 NAT
云专线 DC
虚拟专用网络 VPN
云连接 CC
VPC终端节点 VPCEP
企业路由器 ER
企业交换机 ESW
全球加速 GA
企业连接 EC
云原生应用网络 ANC
安全与合规
安全技术与应用
Web应用防火墙 WAF
企业主机安全 HSS
云防火墙 CFW
安全云脑 SecMaster
DDoS防护 AAD
数据加密服务 DEW
数据库安全服务 DBSS
云堡垒机 CBH
数据安全中心 DSC
云证书管理服务 CCM
威胁检测服务 MTD
态势感知 SA
认证测试中心 CTC
边缘安全 EdgeSec
应用中间件
微服务引擎 CSE
分布式消息服务Kafka版
分布式消息服务RabbitMQ版
分布式消息服务RocketMQ版
API网关 APIG
分布式缓存服务 DCS
多活高可用服务 MAS
事件网格 EG
管理与监管
统一身份认证服务 IAM
消息通知服务 SMN
云监控服务 CES
应用运维管理 AOM
应用性能管理 APM
云日志服务 LTS
云审计服务 CTS
标签管理服务 TMS
配置审计 Config
应用身份管理服务 OneAccess
资源访问管理 RAM
组织 Organizations
资源编排服务 RFS
优化顾问 OA
IAM 身份中心
云运维中心 COC
资源治理中心 RGC
解决方案
高性能计算 HPC
SAP
混合云灾备
开天工业工作台 MIW
Haydn解决方案工厂
数字化诊断治理专家服务
云生态
云商店
合作伙伴中心
华为云开发者学堂
华为云慧通差旅
开发与运维
软件开发生产线 CodeArts
需求管理 CodeArts Req
流水线 CodeArts Pipeline
代码检查 CodeArts Check
编译构建 CodeArts Build
部署 CodeArts Deploy
测试计划 CodeArts TestPlan
制品仓库 CodeArts Artifact
移动应用测试 MobileAPPTest
CodeArts IDE Online
开源镜像站 Mirrors
性能测试 CodeArts PerfTest
应用管理与运维平台 ServiceStage
云应用引擎 CAE
开源治理服务 CodeArts Governance
华为云Astro轻应用
CodeArts IDE
Astro工作流 AstroFlow
代码托管 CodeArts Repo
漏洞管理服务 CodeArts Inspector
联接 CodeArtsLink
软件建模 CodeArts Modeling
Astro企业应用 AstroPro
CodeArts盘古助手
华为云Astro大屏应用
计算
弹性云服务器 ECS
Flexus云服务
裸金属服务器 BMS
云手机服务器 CPH
专属主机 DeH
弹性伸缩 AS
镜像服务 IMS
函数工作流 FunctionGraph
云耀云服务器(旧版)
VR云渲游平台 CVR
Huawei Cloud EulerOS
云化数据中心 CloudDC
网络
虚拟私有云 VPC
弹性公网IP EIP
弹性负载均衡 ELB
NAT网关 NAT
云专线 DC
虚拟专用网络 VPN
云连接 CC
VPC终端节点 VPCEP
企业路由器 ER
企业交换机 ESW
全球加速 GA
企业连接 EC
云原生应用网络 ANC
CDN与智能边缘
内容分发网络 CDN
智能边缘云 IEC
智能边缘平台 IEF
CloudPond云服务
安全与合规
安全技术与应用
Web应用防火墙 WAF
企业主机安全 HSS
云防火墙 CFW
安全云脑 SecMaster
DDoS防护 AAD
数据加密服务 DEW
数据库安全服务 DBSS
云堡垒机 CBH
数据安全中心 DSC
云证书管理服务 CCM
威胁检测服务 MTD
态势感知 SA
认证测试中心 CTC
边缘安全 EdgeSec
大数据
MapReduce服务 MRS
数据湖探索 DLI
表格存储服务 CloudTable
可信智能计算服务 TICS
推荐系统 RES
云搜索服务 CSS
数据可视化 DLV
数据接入服务 DIS
数据仓库服务 GaussDB(DWS)
数据治理中心 DataArts Studio
湖仓构建 LakeFormation
智能数据洞察 DataArts Insight
应用中间件
微服务引擎 CSE
分布式消息服务Kafka版
分布式消息服务RabbitMQ版
分布式消息服务RocketMQ版
API网关 APIG
分布式缓存服务 DCS
多活高可用服务 MAS
事件网格 EG
开天aPaaS
应用平台 AppStage
开天企业工作台 MSSE
开天集成工作台 MSSI
API中心 API Hub
云消息服务 KooMessage
交换数据空间 EDS
云地图服务 KooMap
云手机服务 KooPhone
组织成员账号 OrgID
云空间服务 KooDrive
管理与监管
统一身份认证服务 IAM
消息通知服务 SMN
云监控服务 CES
应用运维管理 AOM
应用性能管理 APM
云日志服务 LTS
云审计服务 CTS
标签管理服务 TMS
配置审计 Config
应用身份管理服务 OneAccess
资源访问管理 RAM
组织 Organizations
资源编排服务 RFS
优化顾问 OA
IAM 身份中心
云运维中心 COC
资源治理中心 RGC
区块链
区块链服务 BCS
数字资产链 DAC
华为云区块链引擎服务 HBS
解决方案
高性能计算 HPC
SAP
混合云灾备
开天工业工作台 MIW
Haydn解决方案工厂
数字化诊断治理专家服务
价格
成本优化最佳实践
专属云商业逻辑
云生态
云商店
合作伙伴中心
华为云开发者学堂
华为云慧通差旅
其他
管理控制台
消息中心
产品价格详情
系统权限
客户关联华为云合作伙伴须知
公共问题
宽限期保留期
奖励推广计划
活动
云服务信任体系能力说明
开发与运维
软件开发生产线 CodeArts
需求管理 CodeArts Req
流水线 CodeArts Pipeline
代码检查 CodeArts Check
编译构建 CodeArts Build
部署 CodeArts Deploy
测试计划 CodeArts TestPlan
制品仓库 CodeArts Artifact
移动应用测试 MobileAPPTest
CodeArts IDE Online
开源镜像站 Mirrors
性能测试 CodeArts PerfTest
应用管理与运维平台 ServiceStage
云应用引擎 CAE
开源治理服务 CodeArts Governance
华为云Astro轻应用
CodeArts IDE
Astro工作流 AstroFlow
代码托管 CodeArts Repo
漏洞管理服务 CodeArts Inspector
联接 CodeArtsLink
软件建模 CodeArts Modeling
Astro企业应用 AstroPro
CodeArts盘古助手
华为云Astro大屏应用
存储
对象存储服务 OBS
云硬盘 EVS
云备份 CBR
高性能弹性文件服务 SFS Turbo
弹性文件服务 SFS
存储容灾服务 SDRS
云硬盘备份 VBS
云服务器备份 CSBS
数据快递服务 DES
云存储网关 CSG
专属分布式存储服务 DSS
数据工坊 DWR
地图数据 MapDS
键值存储服务 KVS
容器
云容器引擎 CCE
云容器实例 CCI
容器镜像服务 SWR
云原生服务中心 OSC
应用服务网格 ASM
华为云UCS
数据库
云数据库 RDS
数据复制服务 DRS
文档数据库服务 DDS
分布式数据库中间件 DDM
云数据库 GaussDB
云数据库 GeminiDB
数据管理服务 DAS
数据库和应用迁移 UGO
云数据库 TaurusDB
人工智能
AI开发平台ModelArts
华为HiLens
图引擎服务 GES
图像识别 Image
文字识别 OCR
自然语言处理 NLP
内容审核 Moderation
图像搜索 ImageSearch
医疗智能体 EIHealth
企业级AI应用开发专业套件 ModelArts Pro
人脸识别服务 FRS
对话机器人服务 CBS
语音交互服务 SIS
人证核身服务 IVS
视频智能分析服务 VIAS
城市智能体
自动驾驶云服务 Octopus
盘古大模型 PanguLargeModels
IoT物联网
设备接入 IoTDA
全球SIM联接 GSL
IoT数据分析 IoTA
路网数字化服务 DRIS
IoT边缘 IoTEdge
设备发放 IoTDP
企业应用
域名注册服务 Domains
云解析服务 DNS
企业门户 EWP
ICP备案
商标注册
华为云WeLink
华为云会议 Meeting
隐私保护通话 PrivateNumber
语音通话 VoiceCall
消息&短信 MSGSMS
云管理网络
SD-WAN 云服务
边缘数据中心管理 EDCM
云桌面 Workspace
应用与数据集成平台 ROMA Connect
ROMA资产中心 ROMA Exchange
API全生命周期管理 ROMA API
政企自服务管理 ESM
视频
实时音视频 SparkRTC
视频直播 Live
视频点播 VOD
媒体处理 MPC
视频接入服务 VIS
数字内容生产线 MetaStudio
迁移
主机迁移服务 SMS
对象存储迁移服务 OMS
云数据迁移 CDM
迁移中心 MGC
专属云
专属计算集群 DCC
开发者工具
SDK开发指南
API签名指南
DevStar
华为云命令行工具服务 KooCLI
Huawei Cloud Toolkit
CodeArts API
云化转型
云架构中心
云采用框架
用户服务
账号中心
费用中心
成本中心
资源中心
企业管理
工单管理
客户运营能力
国际站常见问题
支持计划
专业服务
合作伙伴支持计划
我的凭证
华为云公共事业服务云平台
工业软件
工业数字模型驱动引擎
硬件开发工具链平台云服务
工业数据转换引擎云服务

gs_loader

更新时间:2024-10-31 GMT+08:00
分享

概述

gs_loader工具用于进行数据导入。gs_loader将控制文件支持的语法转换为\COPY语法,然后利用已有的\COPY功能,做主要数据导入工作,同时gs_loader将\COPY结果记录到日志中。

使用gs_loader前请确保gs_loader版本与gsql版本、数据库版本保持一致。

安装部署

在存放数据源文件的服务器上,安装并配置gs_loader客户端工具,以便后续使用gs_loader工具进行数据的导入。

  1. 创建存放gs_loader工具包的目录。

    mkdir -p /opt/bin

  2. 将gsql工具包上传至上一步所创建的目录中。

    以EulerOS工具包为例,将软件安装包中的gsql工具包"GaussDB-Kernel_数据库版本号_操作系统版本号_64bit_gsql.tar.gz"上传至上一步所创建的目录中。

  3. 在工具包所在目录下,解压工具包。

    cd /opt/bin
    tar -zxvf GaussDB-Kernel_数据库版本号_操作系统版本号_64bit_gsql.tar.gz
    source gsql_env.sh

  4. 验证工具位置及版本信息。

    which gs_loader

  5. 验证客户端版本信息。

    gs_loader工具版本与gsql工具版本相对应,直接查询gsql客户端版本即可。
    gsql -V

  6. 验证数据库版本信息,确保与客户端工具版本保持一致。

    使用gsql工具连接到数据库后输入:
    select version();

日志等级配置

设置日志级别,可以供开发者查看。设置后会在控制台打印工具运行的相应信息。

export gs_loader_log_level=debug
export gs_loader_log_level=info
export gs_loader_log_level=warning
export gs_loader_log_level=error

使用权限

使用场景分为三权分立场景下及非三权分立场景下的使用。可以通过将GUC参数enableSeparationOfDuty设置为on或者off,控制三权分立功能的开启或关闭。

GUC参数enable_copy_error_log是控制是否使用错误表pgxc_copy_error_log的参数,默认为off,即不使用错误表,错误数据直接记录到gs_loader的bad文件中。如果该参数设置为on,则会使用错误表pgxc_copy_error_log,将错误数据同时写入bad文件和错误表中。

  • 默认场景,关闭三权分立(即enableSeparationOfDuty=off)时,使用者可以是数据库普通用户或管理员用户。当使用者为普通用户的时候,需要管理员用户对普通用户赋权。管理员账户可以直接使用。
    1. (在管理员用户下)创建用户。
      CREATE USER load_user WITH PASSWORD '************';
    2. (在管理员用户下)将public schema权限给用户。
      GRANT ALL ON SCHEMA public TO load_user;
    3. (在管理员用户下)创建并给用户授权gs_copy_summary表。
      说明:

      gs_copy_summary表中不能含有RULE、TRIGGER、索引函数、行级访问控制、CHECK约束、GENERATED列、DEFAULT列、ON UPDATE列等可能导致提权的对象,否则将认为是恶意用户创建而报错退出。

      SELECT copy_summary_create() WHERE NOT EXISTS(SELECT * FROM pg_tables WHERE schemaname='public' AND tablename='gs_copy_summary');
      GRANT INSERT,SELECT ON  public.gs_copy_summary To load_user;
    4. (可选,在管理员用户下)创建并给用户授权错误表pgxc_copy_error_log。
      说明:
      • 如果guc参数enable_copy_error_log未设置(默认为off),或者设置为off,则无需使用错误表,无需创建。否则需要创建该错误表。
      • pgxc_copy_error_log表中不能含有RULE、TRIGGER、索引函数、行级访问控制、CHECK约束、GENERATED列、DEFAULT列、ON UPDATE列等可能导致提权的对象,否则将认为是恶意用户创建而报错退出。
      SELECT copy_error_log_create() WHERE NOT EXISTS(SELECT * FROM pg_tables WHERE schemaname='public' AND tablename='pgxc_copy_error_log');
      GRANT INSERT,SELECT,DELETE ON  public.pgxc_copy_error_log To load_user;
  • 开启三权分立(即enableSeparationOfDuty=on)时,使用者可以是数据库普通用户或管理员用户。使用前需要到各自的Schema下创建pgxc_copy_error_log表以及gs_copy_summary这两张表并添加索引,不需要再进行授权。
    1. (在初始用户下)创建用户。
      CREATE USER load_user WITH PASSWORD '********';
    2. (在初始用户下)切换为load_user用户
      \c - load_user
    3. (在自建用户下)创建gs_copy_summary表并添加索引。
      CREATE TABLE load_user.gs_copy_summary(relname varchar, begintime timestamptz, endtime timestamptz, id bigint, pid bigint, readrows bigint, skiprows bigint, loadrows bigint, errorrows bigint, whenrows bigint, allnullrows bigint, detail text);
      CREATE INDEX gs_copy_summary_idx ON load_user.gs_copy_summary(id);
    4. (可选,在自建用户下)创建pgxc_copy_error_log表并添加索引。
      说明:

      如果guc参数enable_copy_error_log未设置(默认为off),或者设置为off,则无需使用错误表,无需创建。否则需要创建该错误表。

      CREATE TABLE load_user.pgxc_copy_error_log (relname varchar, begintime timestamptz, filename varchar, lineno int8, rawrecord text, detail text);
      CREATE INDEX copy_error_log_relname_idx ON load_user.pgxc_copy_error_log(relname);

使用环境

由用户自己将工具路径添加到PATH中。gs_loader支持SSL加密通信,使用方式同gsql方式。

新增系统表

新增gs_copy_summary表,记录COPY执行结果汇总,包括成功行数,出错行数,忽略行数,空行数。

新增函数:copy_summary_create用于创建gs_copy_summary表。

gs_copy_summary表格式如下:

relname     | public.sqlldr_tbl
begintime   | 2021-09-03 16:00:11.7129-04
endtime     | 2021-09-03 16:00:15.259908-04
id          | 21870
pid         | 47582725060352
readrows    | 100000
skiprows    | 0
loadrows    | 111
errorrows   | 0
whenrows    | 99889
allnullrows | 0
detail      | 111 Rows successfully loaded.
            | 0 Rows not loaded due to data errors.
            | 99889 Rows not loaded because all WHEN clauses were failed.
            | 0 Rows not loaded because all fields were null.
            | 

系统表gs_copy_summary字段说明

表1 gs_copy_summary字段说明

字段名称

描述

relname

本次导入的目标表名。

begintime

导入任务开始时间。

endtime

导入任务结束时间。

id

本次导入的事务id。

pid

本次导入工作线程id。

readrows

本次导入任务共计读取的数据行数。

skiprows

本次导入任务共计跳过的数据行数。

loadrows

本次导入任务成功导入的数据行数。

errorrows

本次导入任务的错误数据行数。

whenrows

本次导入任务违反when过滤条件的数据行数。

allnullrows

全部字段都为空的数据行数。

detail

本次导入任务的导入情况总结。主要包括成功导入行数、错误数据行数、违反when条件行数和字段全空行数等。

使用指导

  1. (非三权分立)仅对于普通用户

    1. (在管理员用户下)创建用户。
      CREATE USER load_user WITH PASSWORD '************';
    2. (在管理员用户下)将public schema权限赋给普通用户。
      GRANT ALL ON SCHEMA public TO load_user;
    3. (在管理员用户下)创建并给用户授权gs_copy_summary表。
      SELECT copy_summary_create() WHERE NOT EXISTS(SELECT * FROM pg_tables WHERE schemaname='public' AND tablename='gs_copy_summary');
      GRANT INSERT,SELECT PRIVILEGES ON  public.gs_copy_summary To load_user;
    4. (可选,在管理员用户下)创建并给用户授权错误表pgxc_copy_error_log。
      说明:

      如果GUC参数enable_copy_error_log未设置(默认为off),或者设置为off,则无需使用错误表,无需创建。否则需要创建该错误表。

      SELECT copy_error_log_create() WHERE NOT EXISTS(SELECT * FROM pg_tables WHERE schemaname='public' AND tablename='pgxc_copy_error_log');
      GRANT INSERT,SELECT,DELETE ON  public.pgxc_copy_error_log To load_user;
    5. (在管理员用户下)切换用户
      \c - load_user

  2. (三权分立)对于普通用户和管理员用户。

    1. (在初始用户下)创建用户。
      CREATE USER load_user WITH PASSWORD '************';
    2. (在初始用户下)切换为load_user用户。
      \c - load_user
    3. (在自建用户下)创建gs_copy_summary表并添加索引。
      CREATE TABLE load_user.gs_copy_summary(relname varchar, begintime timestamptz, endtime timestamptz, id bigint, pid bigint, readrows bigint, skiprows bigint, loadrows bigint, errorrows bigint, whenrows bigint, allnullrows bigint, detail text);
      CREATE INDEX gs_copy_summary_idx ON load_user.gs_copy_summary(id);
    4. (在自建用户下,可选)创建pgxc_copy_error_log表并添加索引。
      说明:

      如果GUC参数enable_copy_error_log未设置(默认为off),或者设置为off,则无需使用错误表,无需创建。否则需要创建该错误表。

      CREATE TABLE load_user.pgxc_copy_error_log (relname varchar, begintime timestamptz, filename varchar, lineno int8, rawrecord text, detail text);
      CREATE INDEX copy_error_log_relname_idx ON load_user.pgxc_copy_error_log(relname);

  3. 创建表和控制文件,准备数据文件。

    创建表loader_tbl。

    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE  loader_tbl
    (
        ID   NUMBER,
        NAME VARCHAR2(20),
        CON  VARCHAR2(20),
        DT   DATE
    );
    

    (在gs_loader客户端)创建控制文件loader.ctl。

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    LOAD DATA
    truncate into table loader_tbl
    WHEN (2:2) = ',' 
    fields terminated by ','
    trailing nullcols
    (
        id integer external,
        name char(32),
        con ":id || '-' || :name",
        dt date
    )
    

    (在gs_loader客户端)创建guc参数guc.txt

    set a_format_copy_version='s1';

    (在gs_loader客户端)创建数据文件data.csv。

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    1,OK,,2007-07-8
    2,OK,,2008-07-8
    3,OK,,2009-07-8
    4,OK,,2007-07-8
    43,DISCARD,,2007-07-8
    ,,,
    32,DISCARD,,2007-07-8
    a,ERROR int,,2007-07-8
    8,ERROR date,,2007-37-8
    ,,,,
     ,
    8,ERROR fields,,2007-37-8
    ,,,
    5,OK,,2021-07-30
    

  4. 进行导入。

    执行导入前,先确认gs_loader工具有可执行权限。确保当前路径有文件写入权限(gs_loader在处理过程中会生成一些临时文件,导入完成后自动删除)。

    1
    gs_loader control=loader.ctl data=data.csv db=testdb bad=loader.bad errors=5 port=8000 passwd=************ user=load_user
    

    执行结果:

    1
    2
    3
    4
    5
    6
    gs_loader: version 0.1
     
     5 Rows successfully loaded. 
     
    log file is: 
     loader.log
    

注意:

gs_copy_summary记录调用的copy语法以及详细情况,[badfile]_bad.log文件记录错误数据以及详细情况,为防止上一次导入时记录的错误数据以及详细情况被覆盖,建议每次执行导入时使用不同的bad参数。pgxc_copy_error_log默认关闭,如果使用错误表pgxc_copy_error_log记录错误数据以及详细情况,请开启guc参数enable_copy_error_log。如需删除表中的数据,可以对上述表执行truncate或者delete操作。

参数说明

表2 gs_loader参数说明

参数

参数说明

参数类型 : 取值范围

help

查看帮助信息。

-

user

数据库链接用户(与-U等价)。

字符串

-U

数据库链接用户(与user等价)。

字符串

passwd

用户密码(与-W等价)。

字符串

-W

用户密码(与passwd等价)。

字符串

db

数据库名称(必选,与-d等价)。

字符串

-d

数据库名称(必选与db等价)。

字符串

host

指定正在运行服务器的主机名、Unix域套接字的路径、或者域名。接受以“,”分隔的字符串来指定多个主机地址,支持指定多个主机地址(与-h等价)。

当指定多个主机地址时,默认选择连接到主节点。

参考gsql --host参数

-h

指定正在运行服务器的主机名、Unix域套接字的路径、或者域名。接受以“,”分隔的字符串来指定多个主机地址,支持指定多个主机地址(与host等价)。

当指定多个主机地址时,默认选择连接到主节点。

参考gsql --host参数

port

指定数据库服务器的端口号。可以配置一个或多个,当配置一个时,所有的IP都使用同一个端口连接;当配置多个时,顺序与IP顺序相同,个数必须与IP数相等,当不相等时会报错(与-p等价)。

参考gsql --port参考

-p

指定数据库服务器的端口号。可以配置一个或多个,当配置一个时,所有的IP都使用同一个端口连接;当配置多个时,顺序与IP顺序相同,个数必须与IP数相等,当不相等时会报错(与port等价)。

参考gsql --port参考

create

是否创建pgxc_copy_error_log和gs_copy_summary表。

[true, false],默认true

clean

是否清除本次错误记录。

[true, false],默认false

data

数据文件,可以指定多个,或者通配符多字符通配(*)以及单字符通配(?)(必选)。

字符串

control

控制文件名称(必选)。

字符串

log

日志文件名称。

字符串

bad

出错行以及详细情况记录文件名称,也可以指定目录,不指定时根据数据文件名生成。

字符串

discard

WHEN匹配失败行记录文件名称,也可以指定目录,根据数据文件名生成。

字符串

errors

允许数据文件中出现多少出错行。

整数,默认0

skip

允许跳过数据文件的前多少行。

整数,默认0

bindsize

仅做语法兼容不实现功能。

-

rows

多行提交参数,指定导入多少行数据后进行一次提交。

整数,取值范围[1, 2147483647]

注意:
  • 参数均为小写,不支持大写,同时兼容gsql登录方式:-p端口号,-h主机,-d数据库,-U用户名,-W密码方式。
  • 使用rows参数时,提交次数不能超过1000次,否则会对性能产生影响。提交次数约等于数据文件中数据行数除以rows参数取值。不指定rows参数时,rows无默认取值,表现为只进行一次提交,即所有数据都导入表中后进行一次事务提交。可以配合skip参数进行使用,对已经完成导入的数据进行跳过。
  • gs_loader在设置guc参数a_format_load_with_constraints_violation开启支持约束冲突不回滚场景时,如果表带有BEFORE/AFTER ROW INSERT触发器,则每次提交行数不能超过1000万行。
  • gs_loader在设置guc参数a_format_load_with_constraints_violation开启支持约束冲突不回滚场景时,不支持语句级触发器。
  • gs_loader导入过程中,不需要转码场景下,单行数据(包含tuple的元数据,以下均包含)小于1GB-1B;转码场景下单行数据小于256MB-1B,对以下转码场景进行了特殊处理:UTF-8 -> GB18030/GB18030_2022的限制为小于512MB-1B,UTF-8 -> GBK的限制为小于1GB-1B。当单行数据过大而max_process_memory设置过小时会报错内存不足,需要调整max_process_memory的大小后进行重试。
  • 建议单个导入文件的大小不超过1GB。gs_loader对单个导入文件的大小没有限制,但是对大文件数据的导入通常会比较耗时,因此建议在使用的过程中,对较大的数据文件进行切分,启动多个gs_loader进程以append的形式向表中写入数据(如果有truncate的需要则需要单独执行truncate,而不是将truncate写入控制文件中)。在cpu资源足够时,这种做法可以有效地提升导入速率。
  • 对于数据文件的字段内容中存在换行符的场景(通常为gs_loader的csv模式),不推荐以split的方式进行简单的文件切分,可能会出现将一条逻辑记录切分为两条物理记录的情况,导致导入报错。
  • 如果是基本的数据迁移场景,建议先删除表上的索引,禁用表上的触发器,待数据迁移完成后再重建索引,恢复表上的触发器。对导入性能会有所提升。

控制文件

  • 语法说明:
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    LOAD [ DATA ]
    [CHARACTERSET char_set_name]
    [INFILE [directory_path] [filename ] ]
    [BADFILE [directory_path] [filename ] ]
    [OPTIONS(name=value)]
    [{ INSERT | APPEND | REPLACE | TRUNCATE }] 
    INTO TABLE table_name
    [{ INSERT | APPEND | REPLACE | TRUNCATE }] 
    [FIELDS CSV]
    [TERMINATED [BY] { 'string' }]
    [OPTIONALLY ENCLOSED BY { 'string' }]
    [TRAILING NULLCOLS]
    [ WHEN { (start:end) | column_name } {= | !=} string ]
    [(
    col_name [ [ POSITION ({ start:end }) ]  ["sql_string"] ] | [ FILLER [column_type [external] ] ] | [ CONSTANT "string" ] | [ SEQUENCE ( { COUNT | MAX | integer } [, incr] ) ]|[NULLIF (COL=BLANKS)]
    [, ...]
    )]
    
  • 参数说明:
    • CHARACTERSET

      字符集。

      取值范围:字符串。

      注意:

      控制文件中CHARACTERSET指定的字符集,应该和文件的编码格式保持一致,否则会报错或者导入数据乱码。

    • INFILE

      当前关键字无效,并在控制文件中需要单独占一行,运行时候会忽略该关键字。需要用户在gs_loader命令行参数中指定对应的数据文件。

    • BADFILE

      当前关键字无效,运行时候会忽略该关键字,如果gs_loader 命令行参数没有指定badfile,则会根据对应控制文件名称生成对应的badfile文件。

    • OPTIONS

      其中只有skip和rows功能生效,skip=n为导入时跳过前n条数据,rows=n为导入多少行数据后进行一次提交。命令行和控制文件同时指定时,命令行优先级更高。

    • INSERT | APPEND | REPLACE | TRUNCATE

      导入模式。

      INSERT:如果表中有数据,则报错。

      APPEND:直接插入数据。

      REPLACE:如果表中有数据,则全部删除,然后再插入。

      TRUNCATE:如果表中有数据,则全部删除,然后再插入。

      说明:
      • 在写控制文件(.ctl)文件时,在INTO TABLE table_name语句前后都可以指定(导入模式,INSERT | APPEND | REPLACE | TRUNCATE),使用优先级为:在INTO TABLE table_name语句后面指定导入模式优先级高于在INTO TABLE table_name语句前面指定导入模式,在INTO TABLE table_name语句后面指定导入模式会覆盖在前面指定的导入模式。
      • 当开启多个gs_loader会话,并发地向同一张表中导入数据时,推荐以APPEND的方式进行导入,以INSERT|REPLACE|TRUNCATE的方式会出现导入报错或数据导入不全的问题。
    • FIELDS CSV

      标识使用copy的CSV模式。在CSV模式下分隔符缺省值为逗号,引号字符的缺省值为双引号。

      注意:

      当前CSV模式下,被引号包裹的换行符被视为字段数据的一部分。

    • table_name

      表的名称(可以有模式修饰)。

      取值范围:已存在的表名。

    • TERMINATED [BY] { 'string' }

      在文件中分隔各个字段的字符串,分隔符最大长度不超过10个字节。

      取值范围:不允许包含\.abcdefghijklmnopqrstuvwxyz0123456789中的任何一个字符。

      缺省值:在文本模式下,缺省是水平制表符,在CSV模式下是一个逗号。

    • OPTIONALLY ENCLOSED BY { 'string' }

      CSV格式文件下的引号字符。

      仅在使用FIELDS CSV参数明确说明的CSV模式下缺省值:双引号。

      其余模式下无缺省值。

      注意:
      • 设置OPTIONALLY ENCLOSED BY { 'string' }时,要么数据左边不带引号字符,如果有引号字符,左右都必须为奇数个,但不必相等。
      • 当前仅CSV模式支持OPTIONALLY ENCLOSED BY { 'string' }。当指定OPTIONALLY ENCLOSED BY { 'string' }时,默认进入CSV模式。
    • TRAILING NULLCOLS

      当数据加载时,若数据源文件中一行的多个字段缺失的处理方式。

      当一行数据的最后存在一个或多个字段为空时,按照空值将其导入到表中。不设置则会报错字段为空,将这行数据当作错误数据处理。

    • WHEN { (start:end) | column_name } {= | !=}

      对行中的start到end之间的字符串,或者根据列名进行行过滤。

      取值范围:字符串。

    • POSITION ({ start:end })

      对列进行处理,根据start到end范围获取对应字符串。

    • "sql_string"

      对列进行处理,列表达式,根据表达式计算列的取值。详见•列表达式

      取值范围:字符串。

    • FILLER

      对列进行处理,如果出现FILLER,则这个字段跳过。

    • column_type [external]

      在导入数据时,根据不同的数据类型对数据进行处理。详见•数据类型

    • CONSTANT

      对列进行处理,将插入的对应字段设置为常量。

      取值范围:字符串。

    • SEQUENCE ( { COUNT | MAX | integer } [, incr] )

      对列进行处理,生成对应的序列值。

      • COUNT:表示根据表中数据的行数开始计算。
      • MAX:表示根据表中这一列的最大值开始计算。
      • integer:表示从用户指定的值开始计算。
      • incr:表示每次递增多少。
    • NULLIF

      对列进行处理,在多行导入场景中,若列名后未指定sysdate、constant、position、列表达式等运算时,执行导入操作,表现为未指定NULLIF关键字的列字段设置为空。

      当前只支持 COL POSITION() CHAR NULLIF (COL=BLANKS)语法。具体使用详见•NULLIF使用用例

    注意:
    • 不支持OPTIONS、INFILE、BADFILE,仅在特定场景下不报语法错误。
    • gs_loader使用bad文件来记录出错数据,该数据来自错误表的rawrecord字段,由于错误表对于以某种编码无法读取的错误不记录rawrecord,因此bad文件中遇到此情况时记录空行。
    • 单次导入的数据文件约束冲突的行数较多时,如数据库服务端机器内存较小(如32G),约束冲突数据量预估超200万时;或者内存128G以上,约束冲突超过1000万行时,可能会占用较大缓存,报"ERROR: memory is temporarily unavailable"而导入失败,所以此时不建议使用约束冲突不回滚特性。
  • bad文件对应数据为空的需要对应错误表的内容参考源文件和行号(不识别某种编码序列,不写bad文件内容,只记录空行)。
    loader=# select * from pgxc_copy_error_log;
           relname        |           begintime           | filename | lineno | rawrecord |                     detail                      
    ----------------------+-------------------------------+----------+--------+-----------+-------------------------------------------------
     public.test_gsloader | 2023-02-09 09:20:33.646843-05 | STDIN    |      1 |           | invalid byte sequence for encoding "UTF8": 0xb4
    (1 row)
    //如上例子对于loader对应的文件,查找数据文本第一行找出源数据
  • NULLIF使用用例
    // 建表
    CREATE TABLE gsloader_test_nullif(
    col1   varchar2(100) not null enable,
    col2   number(5,0) not null enable,
    col3   varchar2(200) not null enable,
    col4   varchar2(34) not null enable,
    col5   varchar2(750),
    col6   number(20,0),
    col7   varchar2(4000),
    col8   varchar2(200)
    );
    // 数据文件 test.csv
    6007 17060072021-09-0360070001102010000000230          1        600700010000218               0        1        1        229465        3
    6007 17060072021-09-0360070001102010000000299          1        600700010000282               0        1        1        230467        3
    6007 17060072021-09-0360070001102010000000242          1        600700010000255               0        1        1        226400        3
    6007 17060072021-09-0360070001102010000000202          1        600700010000288               0        1        1        219107        3
    6007 17060072021-09-0360070001102010000000294          1        600700010000243               0        1        1        204404        3
    6007 17060072021-09-0360070001102010000000217          1        600700010000270               0        1        1        226644        3
    // 控制文件 test.ctl
    LOAD DATA  
    CHARACTERSET UTF8
    TRUNCATE
    INTO TABLE gsloader_test_nullif
    TRAILING NULLCOLS
    (COL1 POSITION(1:10) CHAR NULLIF (COL1 = BLANKS),
    COL2  POSITION(11:14) CHAR NULLIF (COL2 = BLANKS),
    COL3  POSITION(21:30) CHAR NULLIF (COL3 = BLANKS),
    COL4  POSITION(31:40) CHAR NULLIF (COL4 = BLANKS),
    COL5  sysdate,
    COL6,
    COL7, 
    COL8 POSITION(71:80) CHAR NULLIF (COL8 = BLANKS))
    // 执行导入
    GS_LOADER -p xxx host=xxx control=test.ctl  data=test.csv -d testdb -W xxx 
    // 导入结果:导入成功
    loader=# SELECT * FROM gsloader_test_nullif;
        col1    | col2 |    col3    |    col4    |        col5         | col6 | col7 |   col8
    ------------+------+------------+------------+---------------------+------+------+-----------
     6007 17060 |  720 | 0360070001 | 1020100000 | 2023-05-17 20:32:03 |      |      | 010000218
     6007 17060 |  720 | 0360070001 | 1020100000 | 2023-05-17 20:32:03 |      |      | 010000282
     6007 17060 |  720 | 0360070001 | 1020100000 | 2023-05-17 20:32:03 |      |      | 010000255
     6007 17060 |  720 | 0360070001 | 1020100000 | 2023-05-17 20:32:03 |      |      | 010000288
     6007 17060 |  720 | 0360070001 | 1020100000 | 2023-05-17 20:32:03 |      |      | 010000243
     6007 17060 |  720 | 0360070001 | 1020100000 | 2023-05-17 20:32:03 |      |      | 010000270
    (6 rows)

    从导入表中的数据可以看出在使用NULLIF关键字后,除指定NULLIF运算和sysdate运算的列执行导入操作后导入字段正常,其余未指定运算的列表现为导入字段为空。

  • 列表达式

    gs_loader支持对指定列进行表达式转换和场景扩展:

    ({ column_name [ data_type ] [ AS transform_expr ] } [, ...])

    其中data_type指定该列在表达式参数中的数据类型;transform_expr为目标表达式,返回与表中目标列数据类型一致的结果值。

    示例:

    • ctl文件中不指定列类型,源数据不满足表中列限制(数据类型限制、数据长度限制)。
      // 建表
      create table t_test(id int, text varchar(5));
      // 数据文件 test.csv
      addf2,bbbbaaa,20220907,
      // 控制文件 test.ctl
      Load Data
      TRUNCATE INTO TABLE t_test
      fields terminated by ','
      TRAILING NULLCOLS(
      id "length(trim(:id))",
      text "replace(trim(:text),'bbbb','aa')"
      )
      // guc_param file
      set a_format_copy_version='s1';
      // 执行导入
      gs_loader -p xxx host=xxx control=test.ctl  data=test.csv -d testdb -W xxx guc_param=test_guc.txt
      // 导入结果:导入成功
      select * from t_test;
       id | text  
      ----+-------
        5 | aaaaa
      (1 row)
    • ctl文件中不指定列类型,隐式类型转换(涉及隐式类型转换,建议加上兼容性参数)。
      // 建表
      create table test(mes int, mes1 text, mes2 float8, mes3 timestamp with time zone, mes4 INTEGER);
      // 数据文件
      cat load_support_transform.data
      1,mmoo,12.6789,Thu Jan 01 15:04:28 1970 PST,32767
      2,yyds,180.883,Thu Jun 21 19:00:00 2012 PDT,32768
      // 控制文件
      cat load_support_transform.ctl 
      Load Data
      TRUNCATE INTO TABLE test
      fields terminated by ','
      TRAILING NULLCOLS(
      mes,
      mes1 "mes1 || mes2",
      mes2 "mes2 + 1",
      mes3 "date_trunc('year', mes3)",
      mes4
      )
      // guc_param file
      cat test_guc.txt
      set a_format_copy_version='s1';
      set a_format_dev_version='s2';
      set a_format_version='10c';
      //执行导入
      gs_loader -p xxx host=xxx control=load_support_transform.ctl data=load_support_transform.data -d testdb -W xxx guc_param=test_guc.txt 
      // 导入结果:导入成功
      select * from test;
       mes |    mes1     |  mes2   |          mes3          | mes4  
      -----+-------------+---------+------------------------+-------
         1 | mmoo12.6789 | 13.6789 | 1970-01-01 00:00:00+08 | 32767
         2 | yyds180.883 | 181.883 | 2012-01-01 00:00:00+08 | 32768
  • 数据类型

    对应控制文件中的column_type [external],在加载数据时,根据不同的数据类型对数据进行处理。gs_loader中可以将数据类型分为普通数据类型和特殊数据类型。

    • 普通数据类型
      • CHAR [(length)]:

        按照字段分隔符读取数据,并转换使用CHAR类型来保存值。length表示单条数据的最大长度,以字节为单位,通常一个字符占用一个字节,并且可以缺省,分为以下几种场景:

        • 缺省对length长度的声明时,length的值会根据POSITION的声明来继承最大长度值。
        • 声明了length的长度,则它会覆盖POSITION中对于最大长度的声明。
        • 缺省了length的声明,同时也缺省了POSITION的声明,length的长度会根据分隔符间长度进行设置。
        • 对于长度声明的优先级:length > POSITION > 分隔符。
        • 缺省length,POSITION,分隔符的声明时,length的默认长度为1。
        • 如果实际数据长度超过了length声明的最大长度,会报错。
      • INTEGER external [(length)]:

        按照字段分隔符读取数据,并转换使用INTEGER类型来保存值。length的使用规则与CHAR类型中相同。

      • FLOAT external [(length)]:

        按照字段分隔符读取数据,并转换使用FLOAT类型来保存值。length的使用规则与CHAR类型中相同。

      • DECIMAL external (length):

        按照字段分隔符读取数据,并转换使用DECIMAL类型来保存值。length的使用规则与CHAR类型中相同

      • TIMESTAMP:

        按照字段分隔符读取数据,并转换使用TIMESTAMP类型来保存值。

      • DATE:

        按照字段分隔符读取数据,并转换使用DATE类型来保存值。

      • DATE external:

        按照字段分隔符读取数据,并转换使用DATE类型来保存值。

      • SYSDATE:

        在数据库执行对应的插入时,取系统时间。该字段对应对应的值无法被引用使用,被引用使用的内容为SYSDATE字符串。

    • 特殊数据类型
      • INTEGER:

        无视字段分隔符读取四个字节长度的字符,按小端存储逻辑保存,然后将每个字符解析成十六进制ASCII码值,最后将整体转换为十进制数来保存值。

      • SMALLINT:

        无视字段分隔符读取两个字节长度的字符,按小端存储逻辑保存,然后将每个字符解析成十六进制ASCII码值,最后将整体转换为十进制数来保存值。

        示例:

        // 建表
        create table t_spec(col1 varchar(10), col2 varchar(10));
        // 数据文件
        cat t_spec.txt
        1234,5678,
        // 控制文件
        cat t_spec.ctl
        Load Data
        TRUNCATE INTO TABLE t_spec
        fields terminated by ','
        TRAILING NULLCOLS(
        col1 position(2:6) integer,
        col2 position(5:8) smallint
        )
        // guc_param file
        cat test_guc.txt
        set a_format_copy_version='s1';
        set a_format_dev_version='s2';
        set a_format_version='10c';
        // 执行导入
        gs_loader -p xxx host=xxx control=t_spec.ctl data=t_spec.txt -d testdb -W xxx guc_param=test_guc.txt
        // 导入结果:导入成功
        select * from t_spec;
           col1    | col2
        -----------+-------
         741618482 | 13612
        (1 row)
      • RAW:

        会把每个字符解析成ASCII码值保存,转义字符“\”不执行转义操作。

        限制:RAW不能使用分隔符。

        示例:

        // 建表
        create table t_raw(col raw(50));
        // 数据文件
        cat t_raw.txt
        12\n\x78!<~?'k^(%s)>/c[$50]
        // 控制文件
        cat t_raw.ctl
        Load Data
        TRUNCATE INTO TABLE t_raw
        TRAILING NULLCOLS(
        col position(1:50) raw
        )
        // guc_param file
        cat test_guc.txt
        set a_format_copy_version='s1';
        set a_format_dev_version='s2';
        set a_format_version='10c';
        // 执行导入
        gs_loader -p xxx host=xxx control=t_raw.ctl data=t_raw.txt -d testdb -W xxx guc_param=test_guc.txt
        // 导入结果:导入成功
        select * from t_raw;
                                  col
        --------------------------------------------------------
         31325C6E5C783738213C7E3F276B5E282573293E2F635B2435305D
        (1 row)
      注意:
      • 在多列导入场景中,不指定GUC参数时,部分position与分隔符不能同时使用。
      • 在多列导入场景中,指定GUC参数时,不支持部分列使用POSITION运算。
      • 在多列导入场景中,普通数据类型与特殊数据类型混用时,需全部指定POSITION。
      • 指定数据类型导入时,包含普通数据类型需要通过guc_param设置a_format_copy_version参数,包含特殊数据类型则需要通过guc_param设置a_format_copy_version, a_format_dev_version及a_format_version参数。
      • 列表达式涉及到系统函数时,需要根据对应功能通过guc_param设置合适的a_format_dev_version及a_format_version参数。
      • 带length数据类型的使用,length需指定为大于0的整数;RAW数据类型作为特殊类型,RAW(length)的使用区别于普通类型的使用,如INTEGER EXTERNAL(length)的使用,当不指定position时,INTEGER EXTERNAL(length)表现为,当length小于文本文件 (.csv/.txt等)中对应列数据长度时报错;当length大于文本文件 (.txt)中对应列数据长度时,输出INTEGER EXTERNAL类型的结果。RAW(length)当不指定position时表现为读取length个字符。
      • POSITION使用时,POSITION(start:end),start需设置为大于0的整数,且end值应大于等于start的值。
      • 并发导入时,若多个gs_loader的discard文件名或bad文件名指向同一目录同名文件,则后一个执行的gs_loader会中止报错。若前一个已经导入完成,则文件被覆盖

        报错如下:

        ERROR: An error occurred. Please check logfile.

        log文件中

        …lock failed: Resource temporarily unavailable…
      • 控制文件中对于字段值的部分若不为空且不使用本字段内容,则不占用数据文件的位置。

        比如控制文件如下:

        Load Data
        TRUNCATE INTO TABLE gsloader
        fields terminated by ','
        TRAILING NULLCOLS(
        id "trim(:id)",
        text "to_char(SYSDATE,'yyyymmdd')",
        gmt_create  "trim(:gmt_create)",
        create_str "trim(:create_str)"
        )

        数据文件如下:

        11,HELLO,33,

        导入结果为:

        loader=# select * from gsloader;
        id |  text  |     gmt_create      | create_str
        ----+--------+---------------------+------------
        11 | 2023-02-08 16:00:54 | HELLO |  33
提示

您即将访问非华为云网站,请注意账号财产安全

文档反馈

文档反馈

意见反馈

0/500

标记内容

同时提交标记内容