文档首页/ 数据仓库服务 GaussDB(DWS)/ 最佳实践/ 导入导出/ 使用外表功能实现GaussDB(DWS)集群间数据迁移
更新时间：2024-10-10 GMT+08:00
查看PDF
分享

使用外表功能实现GaussDB(DWS)集群间数据迁移

大数据融合分析场景下，支持同一区域内的多套GaussDB(DWS)集群之间的数据互通互访，本实践将演示通过Foreign Table方式从远端DWS导入数据到本地端DWS。

本实践演示过程为：以gsql作为数据库客户端，gsql安装在ECS，通过gsql连接DWS，再通过外表方式导入远端DWS的数据。

视频介绍

操作流程

本实践预计时长40分钟，基本流程如下：

  1. 准备工作
  2. 创建ECS
  3. 创建集群并下载工具包
  4. 使用GDS导入数据源
  5. 通过外表导入远端DWS数据

准备工作

已注册华为账号并开通华为云，账号不能处于欠费或冻结状态。

创建ECS

参见购买弹性云服务器购买。购买后，参见登录Linux弹性云服务器进行登录。

创建ECS过程中，注意选择与后续的DWS集群在同一个区域、可用区和同一个VPC子网下，ECS的操作系统选择与下面的gsql客户端/GDS工具的操作系统一致（本例以CentOS 7.6为例），并选择以密码方式登录。

创建集群并下载工具包

  1. 登录华为云管理控制台。
  2. 在“服务列表”中，选择“大数据 > 数据仓库服务”，单击右上角“创建数据仓库集群”。
  3. 参见表1进行参数配置。

    表1 软件配置

    参数名称

    配置方式

    区域

    选择“中国香港”。

    说明：
    • 本指导以“中国香港”为例进行介绍，如果您需要选择其他区域进行操作，请确保所有操作均在同一区域进行。
    • 请确保DWS跟ECS在同一个区域、可用区和同一个VPC子网下。

    可用分区

    可用区2

    产品类型

    标准数仓

    计算类型

    弹性云服务器

    存储类型

    SSD云盘

    CPU架构

    X86

    节点规格

    dws2.m6.4xlarge.8（16 vCPU | 128GB | 2000GB SSD）

    说明：

    如规格售罄，可选择其他可用区或规格。

    热数据存储

    100GB / 节点

    节点数量

    3

    集群名称

    dws-demo01

    管理员用户

    dbadmin

    管理员密码

    password，用户自定义

    确认密码

    password

    数据库端口

    8000

    虚拟私有云

    vpc-default

    子网

    subnet-default(192.168.0.0/24)

    须知：

    请确保与ECS在同一个VPC。

    安全组

    自动创建安全组

    公网访问

    现在购买

    宽带

    1Mbit/s

    高级配置

    默认配置

  4. 信息核对无误，单击“立即购买”，单击“提交”。
  5. 等待约10分钟，待集群创建成功后，单击集群名称进入“基本信息”，在“网络”区域，单击安全组名称，确认安全组规则已添加，以IP为192.168.0.x的客户端网段为例（本例gsql所在ECS的内网IP为192.168.0.90），需要添加192.168.0.0/24，端口为8000的安全组规则。
  6. 返回到集群“基本信息”界面，记录下“内网IP”。

  7. 返回到DWS控制台首页，左侧导航选择“管理 > 连接客户端”，选择ECS的操作系统（以CentOS 7.6为例，则选择“Redhat x86_64”），单击“下载”将工具包保存到本地。（工具包中包含gsql客户端和GDS工具）。
  8. 重复执行1~6，创建第二套DWS集群，名称设置为dws-demo02。

准备源数据

  1. 在本地PC指定目录下，创建以下3个.csv格式的文件，数据样例如下。

    • 数据文件“product_info0.csv” 
      1
2
3
4
5
      		 
      100,XHDK-A,2017-09-01,A,2017 Shirt Women,red,M,328,2017-09-04,715,good!
205,KDKE-B,2017-09-01,A,2017 T-shirt Women,pink,L,584,2017-09-05,40,very good!
300,JODL-X,2017-09-01,A,2017 T-shirt men,red,XL,15,2017-09-03,502,Bad.
310,QQPX-R,2017-09-02,B,2017 jacket women,red,L,411,2017-09-05,436,It's nice.
150,ABEF-C,2017-09-03,B,2017 Jeans Women,blue,M,123,2017-09-06,120,good.
    • 数据文件“product_info1.csv” 
      1
2
3
4
5
      		 
      200,BCQP-E,2017-09-04,B,2017 casual pants men,black,L,997,2017-09-10,301,good quality.
250,EABE-D,2017-09-10,A,2017 dress women,black,S,841,2017-09-15,299,This dress fits well.
108,CDXK-F,2017-09-11,A,2017 dress women,red,M,85,2017-09-14,22,It's really amazing to buy.
450,MMCE-H,2017-09-11,A,2017 jacket women,white,M,114,2017-09-14,22,very good.
260,OCDA-G,2017-09-12,B,2017 woolen coat women,red,L,2004,2017-09-15,826,Very comfortable.
    • 数据文件“product_info2.csv” 
       1
 2
 3
 4
 5
 6
 7
 8
 9
10
      		 
      980,"ZKDS-J",2017-09-13,"B","2017 Women's Cotton Clothing","red","M",112,,,
98,"FKQB-I",2017-09-15,"B","2017 new shoes men","red","M",4345,2017-09-18,5473
50,"DMQY-K",2017-09-21,"A","2017 pants men","red","37",28,2017-09-25,58,"good","good","good"
80,"GKLW-l",2017-09-22,"A","2017 Jeans Men","red","39",58,2017-09-25,72,"Very comfortable."
30,"HWEC-L",2017-09-23,"A","2017 shoes women","red","M",403,2017-09-26,607,"good!"
40,"IQPD-M",2017-09-24,"B","2017 new pants Women","red","M",35,2017-09-27,52,"very good."
50,"LPEC-N",2017-09-25,"B","2017 dress Women","red","M",29,2017-09-28,47,"not good at all."
60,"NQAB-O",2017-09-26,"B","2017 jacket women","red","S",69,2017-09-29,70,"It's beautiful."
70,"HWNB-P",2017-09-27,"B","2017 jacket women","red","L",30,2017-09-30,55,"I like it so much"
80,"JKHU-Q",2017-09-29,"C","2017 T-shirt","red","M",90,2017-10-02,82,"very good."

  2. 使用root账户登录已创建好的ECS，执行以下命令创建数据源文件目录。

    mkdir -p /input_data

  3. 使用文件传输工具，将以上数据文档上传到ECS的/input_data目录下。

使用GDS导入数据源

  1. 使用root账户登录ECS，使用文件传输工具将7下载好的工具包上传到/opt目录下。
  2. 在/opt目录下解压工具包。

    cd /opt

    unzip dws_client_8.1.x_redhat_x64.zip

  3. 创建GDS用户，并修改数据源目录和GDS目录的属主。

    groupadd gdsgrp

    useradd -g gdsgrp gds_user

    chown -R gds_user:gdsgrp /opt/gds

    chown -R gds_user:gdsgrp /input_data

  4. 切换到gds_user用户。

    su - gds_user

  5. 导入GDS环境变量。

    仅8.1.x及以上版本需要执行，低版本请跳过。

    cd /opt/gds/bin

    source gds_env

  6. 启动GDS。

    /opt/gds/bin/gds -d /input_data/ -p 192.168.0.90:5000 -H 192.168.0.0/24 -l /opt/gds/gds_log.txt -D

    • -d dir：保存有待导入数据的数据文件所在目录。本教程中为“/input_data/”。
    • -p ip:port：GDS监听IP和监听端口。配置为GDS所在的ECS的内网IP，可与DWS通讯，本例为192.168.0.90:5000。
    • -H address_string：允许哪些主机连接和使用GDS服务。参数需为CIDR格式。本例设置为DWS的内网IP所在的网段即可。
    • -l log_file：存放GDS的日志文件路径及文件名。本教程为“/opt/gds/gds_log.txt”。
    • -D：后台运行GDS。

  7. 使用gsql连接第一套DWS集群。

    1. 执行exit切换root用户，进入ECS的/opt目录，导入gsql的环境变量。

      exit

      cd /opt

      source gsql_env.sh

    2. 进入/opt/bin目录，使用gsql连接第一套DWS集群。

      cd /opt/bin

      gsql -d gaussdb -h 192.168.0.8 -p 8000 -U dbadmin -W password -r

      • -d: 连接的数据库名，本例为默认数据库gaussdb。
      • -h：连接的DWS内网IP，即6查询到的内网IP，本例为192.168.0.8。
      • -p：DWS端口，固定为8000。
      • -U：数据库管理员用户，默认为dbadmin。
      • -W：管理员用户的密码，为3创建集群时设置的密码，本例password为用户创建集群设置的密码。

  8. 创建普通用户leo，并赋予创建外表的权限。 

    1
2
    		 
    CREATE USER leo WITH PASSWORD 'password';
ALTER USER leo USEFT;

  9. 切换到leo用户，创建GDS外表。

    以下LOCATION参数请填写为6的GDS的监听IP和端口，后面加上/*，例如：gsfs://192.168.0.90:5000/*

     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
    		 
    SET ROLE leo PASSWORD 'password';
DROP FOREIGN TABLE IF EXISTS 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 'gsfs://192.168.0.90:5000/*',
FORMAT 'CSV' ,
DELIMITER ',',
ENCODING 'utf8',
HEADER 'false',
FILL_MISSING_FIELDS 'true',
IGNORE_EXTRA_DATA 'true'
)
READ ONLY
LOG INTO product_info_err 
PER NODE REJECT LIMIT 'unlimited';

  10. 创建本地表。 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
    		 
    DROP TABLE IF EXISTS product_info;
CREATE TABLE product_info
(
    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)                   
) 
WITH (
orientation = column,
compression=middle
) 
DISTRIBUTE BY hash (product_id);

  11. 从GDS外表导入数据并查询，数据导入成功。 

    1
2
    		 
    INSERT INTO product_info SELECT * FROM product_info_ext ;
SELECT count(*) FROM product_info;

通过外表导入远端DWS数据

  1. 参见7在ECS上连接第二套集群，其中连接地址改为第二套集群的地址，本例为192.168.0.86。
  2. 创建普通用户jim，并赋予创建外表和server的权限。FOREIGN DATA WRAPPER固定为gc_fdw。 

    1
2
3
    		 
    CREATE USER jim WITH PASSWORD 'password';
ALTER USER jim USEFT;
GRANT ALL ON FOREIGN DATA WRAPPER gc_fdw TO jim;

  3. 切换到jim用户，创建server。 

    1
2
3
4
5
6
7
    		 
    SET ROLE jim PASSWORD 'password';
CREATE SERVER server_remote FOREIGN DATA WRAPPER gc_fdw OPTIONS  
   (address '192.168.0.8:8000,192.168.0.158:8000' , 
  dbname 'gaussdb',  
  username 'leo',  
  password 'password' 
);
    • address：第一套集群的两个内网IP和端口，参见6获取，本例为192.168.0.8:8000,192.168.0.158:8000。
    • dbname：连接的第一套集群的数据库名，本例为gaussdb。
    • username：连接的第一套集群的用户名，本例为leo。
    • password：用户名密码。

  4. 创建外表。

    外表的字段和约束，必须与待访问表的字段和约束保持一致。

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
    		 
    CREATE FOREIGN TABLE region 
( 
    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 
    server_remote 
OPTIONS 
( 
    schema_name 'leo', 
    table_name 'product_info', 
    encoding 'utf8' 
);
    • SERVER：上一步创建的server的名称，本例为server_remote。
    • schema_name：待访问的第一套集群的schema名称，本例为leo。
    • table_name：待访问的第一套集群的表名，参见10获取，本例为product_info。
    • encoding：保持与第一套集群的数据库编码一致，参见9获取，本例为utf8。

  5. 查看创建的server和外表。 

    1
2
    		 
    \des+ server_remote
\d+ region

  6. 创建本地表。

    表的字段和约束，必须与待访问表的字段和约束保持一致。

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
    		 
    CREATE TABLE local_region 
( 
    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)        
) 

WITH (
orientation = column,
compression=middle
) 
DISTRIBUTE BY hash (product_id);

  7. 通过外表导入数据到本地表。 

    1
2
    		 
    INSERT INTO local_region SELECT * FROM region;
SELECT * FROM local_region;

  8. 您也可以直接查询外表而无需将数据导入。 

    1
    		 
    SELECT * FROM region;

父主题： 导入导出