What's New
Function Overview
Product Bulletin
- Product Notice
- Version Support Notes
Service Overview
- GaussDB(DWS) Infographics
- What Is GaussDB(DWS)?
- Data Warehouse Types
- Data Warehouse Flavors
- Advantages
- Application Scenarios
- Functions
- Concepts
- Related Services
- Security
- GaussDB(DWS) Permissions Management
- GaussDB(DWS) Access
- Restrictions
- Technical Support
- Service Quotas
- GaussDB(DWS) Technical Specifications
Billing
- GaussDB(DWS) Billing Overview
- Billing Modes
- Item
- Billing Examples
- Billing Mode Change
- Renewal
  - Overview
  - Manual Renewal
- Bills
- Arrears
- Stopping Billing
- Cost Management
- Billing FAQs
Getting Started
- Checkpoint Vehicle Analysis
- Supply Chain Requirement Analysis of a Company
- Operations Status Analysis of a Retail Department Store
- Creating a Time Series Table
- Best Practices of Hot and Cold Data Management
- Best Practices for Automatic Partition Management
- Creating a Cluster and Connecting to It
- Using CDM to Migrate MySQL Data to the GaussDB(DWS) Cluster
- Using DLI Flink Jobs to Write Kafka Data to GaussDB(DWS) in Real Time
- Basic SQL Operations
- Database Quick Start
- Getting Started with Common Practices
User Guide
- Using GaussDB(DWS)
- Preparations
- Creating a GaussDB(DWS) Cluster
- Connecting to a GaussDB(DWS) Cluster
- Creating a GaussDB(DWS) Database and User
- Migrating Service Data to a GaussDB(DWS) Cluster
  - Migrating Data to a GaussDB(DWS) Cluster Using GDS-Kafka
  - Data Source Management
- GaussDB(DWS) Cluster Data Security and Encryption
- GaussDB(DWS) Cluster Management
- GaussDB(DWS) Cluster O&M
Best Practices
- Import and Export
- Data Migration
- Data Analytics
- Decoupled Storage and Compute
  - DWS 3.0 Decoupled Storage and Compute Usage Suggestions and Performance Optimization
- Data Development
- Database Management
- Performance Tuning
  - Optimizing Table Structure Design to Enhance GaussDB(DWS) Query Performance
  - Analyzing SQL Statements That Are Being Executed to Handle GaussDB(DWS) Performance Issues
- Cluster Management
  - Binding Different Resource Pools to Two Types of Jobs to Balance Load for GaussDB(DWS)
  - Scaling Options for GaussDB(DWS) with a Coupled Storage-Compute Architecture
Data Migration and Synchronization
- Data Migration to GaussDB(DWS)
- Importing Data
- Full Database Migration
  - Using CDM to Migrate Data to GaussDB(DWS)
  - Using DSC to Migrate SQL Scripts
- Real-time Import
  - Using DRS to Import Data to GaussDB(DWS)
  - Real-time Data Import From Kafka GaussDB(DWS)
- Metadata Migration
  - Using gs_dump and gs_dumpall to Export Metadata
  - Using gs_restore to Import Data
- Exporting Data
- Other Operations
Developer Guide
- Standard Data Warehouse (9.1.0.x)
- Standard data warehouse (8.1.3.x)
- Hybrid Data Warehouse (9.1.0.x）
- Hybrid Data Warehouse (8.1.3.x）
- Historical Versions
SQL Syntax Reference
- SQL Syntax Reference (9.1.0.x)
- SQL Syntax Reference (8.1.3.x)
- Historical Versions
Performance Tuning
- Overview of Query Performance Optimization
- Query Execution Process
- SQL Execution Plan
- SQL Optimization Guide
- Optimization Cases
- SQL Execution Troubleshooting
- query_band Load Identification
- Common Performance Parameter Optimization Design
Tool Guide
- Overview
- Downloading Related Tools
- gsql
- Data Studio
- GDS
- DSC
- DataCheck
- DWS-Connector
- Server Tool
API Reference
- Before You Start
- API Overview
- Calling APIs
- Getting Started
- API Description
- Application Cases
  - Using Postman to Call the API for Creating a Cluster
  - Using Postman to Call the API for Creating a Snapshot
- Introduction
- Appendix
SDK Reference
- SDK Overview
FAQs
- Top FAQs
- Product Consulting
- Database Connections
- Data Migration
- Database Usage
- Cluster Management
- Account Permissions
- Database Performance
- Backup and Restoration
  1. Why Does It Take a Long Time to Create an Automated Snapshot in GaussDB(DWS)?
  2. Does a GaussDB(DWS) Snapshot Have the Same Function as an EVS Snapshot?
Troubleshooting
- Database Connections
- JDBC/ODBC
- Data Import and Export
- Database Parameter Modification
- Account/Permission/Password
- Cluster Performance
- Cluster Exceptions
  - The Disk Usage Alarm Is Frequently Generated
- Database Use
Videos
Performance White Paper
- Overview
- Test Result
  - TPC-H Single Query Test
  - TPC-DS Single Query Test
- Test Methods
- Appendixes
  - TPC-H Test Sets
  - TPC-DS Test Sets
Technical White Paper
- GaussDB(DWS)
- Platforms and Technical Specifications Supported by GaussDB(DWS)
  - Technical Specifications
- GaussDB(DWS) Core Technologies
- GaussDB(DWS) Tools
  - Client Tools
  - Database Monitoring Tool
- External APIs
Error Code Reference
- 8.2.0 and earlier versions
  - Management Console Error Code
  - Data Warehouse Service Error Codes
- 8.2.1 or later versions
Glossary
More Documents
- User Guide
- API Reference (ME-Abu Dhabi Region)
- Developer Guide (ME-Abu Dhabi Region)
- SQL Syntax Reference (ME-Abu Dhabi Region)
- Tool Guide (ME-Abu Dhabi Region)
- Error Code Reference (ME-Abu Dhabi Region)
  - Management Console Error Code
- User Guide (Paris Region)
- API Reference (Paris Region)
- Developer Guide (Paris Region)
- SQL Syntax Reference (Paris Region)
- Tool Guide (Paris Region)
- Error Code Reference (Paris Region)
  - Management Console Error Code
- User Guide (Kuala Lumpur Region)
- API Reference (Kuala Lumpur Region)
- Developer Guide (Kuala Lumpur Region)
- SQL Syntax Reference (Kuala Lumpur Region)
- Tool Guide (Kuala Lumpur Region)
- Error Code Reference (Kuala Lumpur Region)
  - Management Console Error Code
General Reference
- Glossary
- Service Level Agreement
- White Papers
- Endpoints
- Permissions

On this page

General Procedure
Preparations
Creating an ECS
Creating a Cluster and Downloading the Tool Package
Preparing Source Data
Importing Data Sources Using GDS
Importing Remote GaussDB(DWS) Data Using a Foreign Table

Show all

Help Center/ GaussDB(DWS)/ Best Practices/ Import and Export/ Migrating Data Between GaussDB(DWS) Clusters Using Foreign Tables

Migrating Data Between GaussDB(DWS) Clusters Using Foreign Tables

Updated on 2024-10-29 GMT+08:00

View PDF

In the era of big data convergent analysis, GaussDB(DWS) clusters in the same region can communicate with each other. This practice demonstrates how to import data from a remote GaussDB(DWS) cluster to the local GaussDB(DWS) cluster using foreign tables.

The demonstration procedure is as follows: Install the gsql database client on an ECS, connect to GaussDB(DWS) using gsql, and import data from the remote GaussDB(DWS) using a foreign table.

General Procedure

This practice takes about 40 minutes. The basic process is as follows:

Preparations
Creating an ECS
Creating a Cluster and Downloading the Tool Package
Importing Data Sources Using GDS
Importing Remote GaussDB(DWS) Data Using a Foreign Table

Preparations

You have registered a Huawei account and enabled Huawei Cloud. The account cannot be in arrears or frozen.

Creating an ECS

For details, see Purchasing an ECS. After purchasing an ECS, log in to the ECS by referring to Logging In to a Linux ECS.

NOTICE:

When creating an ECS, ensure that the ECS and the GaussDB(DWS) clusters to be created are in the same VPC subnet and in the same region and AZ . The ECS OS is the same as that of the gsql client or GDS (CentOS 7.6 is used as an example), and the password is used for login.

Creating a Cluster and Downloading the Tool Package

Log in to the Huawei Cloud management console.
Choose Service List > Analytics > Data Warehouse Service. On the page that is displayed, click Create Cluster in the upper right corner.

Configure the parameters according to Table 1.

**Table 1** Software configuration
Parameter	Configuration
Region	Select the CN-Hong Kong region. NOTE: CN-Hong Kong is used as an example. You can select other regions as required. Ensure that all operations are performed in the same region. Ensure that GaussDB(DWS) and the ECS are in the same region, AZ, and VPC subnet.
AZ	AZ2
Resource	Standard data warehouse
Compute Resource	ECS
Storage Type	Cloud SSD
CPU Architecture	x86
Node Flavor	dws2.m6.4xlarge.8 (16 vCPUs \| 128 GB \| 2000 GB SSD) NOTE: If this flavor is sold out, select other AZs or flavors.
Hot Storage	100 GB/node
Nodes	3
Cluster Name	dws-demo01
Administrator Account	dbadmin
Administrator Password	User-defined password
Confirm Password	password
Database Port	8000
VPC	vpc-default
Subnet	subnet-default(192.168.0.0/24) NOTICE: Ensure that the cluster and the ECS are in the same VPC subnet.
Security Group	Automatic creation
EIP	Buy now
Bandwidth	1 Mbit/s
Advanced Settings	Default

Confirm the information, click Next, and then click Submit.
Wait for about 10 minutes. After the cluster is created, click the cluster name to go to the Basic Information page. Choose Network, click a security group name, and verify that a security group rule has been added. In this example, the client IP address is 192.168.0.x (the private network IP address of the ECS where gsql is located is 192.168.0.90). Therefore, you need to add a security group rule in which the IP address is 192.168.0.0/24 and port number is 8000.
Return to the Basic Information tab of the cluster and record the value of Private Network IP Address.
Return to the home page of the GaussDB(DWS) console. In the navigation tree on the left, choose Management > Client Connections, select the appropriate ECS OS (such as Redhat x86_64 for CentOS 7.6), and click Download to save the tool package to your local PC. The tool package contains the gsql client and GDS.
Repeat 1 to 6 to create a second GaussDB(DWS) cluster and set its name to dws-demo02.

Preparing Source Data

Create the following three CSV files in the specified directory on the local PC:

Data file product_info0.csv

        
           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.

Data file product_info1.csv

        
           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.

Data file product_info2.csv

        
           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."

Log in to the created ECS as user root and run the following command to create a data source file directory:

mkdir -p /input_data
Use a file transfer tool to upload the preceding data files to the /input_data directory of the ECS.

Importing Data Sources Using GDS

Log in to the ECS as user root and use a file transfer tool to upload the downloaded tool package in 7 to the /opt directory.
Decompress the tool package in the /opt directory.

cd /opt

unzip dws_client_8.1.x_redhat_x64.zip
Create a GDS user and change the owners of the data source and GDS directories.

groupadd gdsgrp

useradd -g gdsgrp gds_user

chown -R gds_user:gdsgrp /opt/gds

chown -R gds_user:gdsgrp /input_data
Switch to user gds_user.

su - gds_user
Import the GDS environment variables.

NOTE:

This step is required only for 8.1.x or later. For earlier versions, skip this step.

cd /opt/gds/bin

source gds_env
Start 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: directory for storing data files that contain data to be imported. This practice uses /input_data/ as an example.
- -p ip:port: listening IP address and port for GDS. Set this parameter to the private network IP address of the ECS where GDS is installed so that GDS can communicate with GaussDB(DWS). In this example, 192.168.0.90:5000 is used.
- -H address_string: hosts that are allowed to connect to and use GDS. The value must be in CIDR format. In this example, the network segment of the GaussDB(DWS) private network IP address is used.
- -l log_file: GDS log directory and log file name. In this example, /opt/gds/gds_log.txt is used.
- -D: GDS in daemon mode.
Connect to the first GaussDB(DWS) cluster using gsql.
1. Run the exit command to switch to user root, go to the /opt directory of the ECS, and import the environment variables of gsql.
  exit
  
  cd /opt
  
  source gsql_env.sh
2. Go to the /opt/bin directory and connect to the first GaussDB(DWS) cluster using gsql.
  cd /opt/bin
  
  gsql -d gaussdb -h 192.168.0.8 -p 8000 -U dbadmin -W password -r
  - -d: name of the connected database. In this example, the default database gaussdb is used.
  - -h: private network IP address of the connected GaussDB(DWS) database queried in 6. In this example, 192.168.0.8 is used.
  - -p: GaussDB(DWS) port. The value is 8000.
  - -U: database administrator. The value defaults to dbadmin.
  - -W: administrator password, which is set during cluster creation in 3. In this example, replace password with your actual password.
Create a common user leo and grant the user the permission for creating foreign tables.
1 2

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

Switch to user leo and create a GDS foreign table.

NOTE:

Set LOCATION to the GDS listening IP address and port number obtained in 6, for example, gsfs://192.168.0.90:5000/*.

      
       
         
         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';

        

      
     

Create a local table.

      
       
         
         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);

        

      
     

Import data from the GDS foreign table and check whether the data is successfully imported.

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

Importing Remote GaussDB(DWS) Data Using a Foreign Table

Connect to the second cluster on the ECS by referring to 7. Change the connection address to the address of the second cluster. In this example, 192.168.0.86 is used.

Create a common user jim and grant the user the permission for creating foreign tables and servers. The value of FOREIGN DATA WRAPPER is gc_fdws.

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

Switch to user jim and create a server.

      
         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: private network IP addresses and port number of the first cluster obtained in 6. In this example, 192.168.0.8:8000 and 192.168.0.158:8000 are used.
dbname: database name of the first connected cluster. In this example, gaussdb is used.
username: username of the first connected cluster. In this example, leo is used.
password: user password

Create a foreign table.

NOTICE:

The columns and constraints of the foreign table must be consistent with those of the table to be accessed.

       
        
          
          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: name of the server created in the previous step. In this example, server_remote is used.
schema_name: schema name of the first cluster to be accessed. In this example, leo is used.
table_name: table name of the first cluster to be accessed obtained in 10. In this example, product_info is used.
encoding: The value must be the same as that of the first cluster obtained in 9. In this example, utf8 is used.

View the created server and foreign table.
1 2

\des+ server_remote \d+ region

Create a local table.

NOTICE:

The columns and constraints of the table must be consistent with those of the table to be accessed.

       
        
          
          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);

         

       
      

Import data to the local table using the foreign table.

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

Query the foreign table without importing data.
1

SELECT * FROM region;

Parent topic: Import and Export

Previous topic: Importing Table Data from DLI to a GaussDB(DWS) Cluster

Next topic: Exporting ORC Data from a GaussDB(DWS) Cluster to an MRS Cluster

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot