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

Preparations
Step 1: Preparing DLI Source Data
Step 2: Creating a GaussDB(DWS) Cluster
Step 3: Obtaining Authentication Information Required by the GaussDB(DWS) External Server
Step 4: Importing DLI Table Data Using a Foreign Table

Show all

Help Center/ GaussDB(DWS)/ Best Practices/ Import and Export/ Importing Table Data from DLI to a GaussDB(DWS) Cluster

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

Updated on 2024-11-08 GMT+08:00

View PDF

This exercise demonstrates how to use the GaussDB(DWS) foreign table function to import data from DLI to GaussDB(DWS).

For details about DLI, see What Is Data Lake Insight?

This exercise lasts for approximately 60 minutes and involves utilizing various cloud services such as Virtual Private Cloud (VPC) and Subnet, Data Lake Insight (DLI), Object Storage Service (OBS), and GaussDB(DWS). The following is an outline of the exercise.

Preparations
Step 1: Preparing DLI Source Data
Step 2: Creating a GaussDB(DWS) Cluster
Step 3: Obtaining Authentication Information Required by the GaussDB(DWS) External Server.
Step 4: Importing DLI Table Data Using a Foreign Table

Preparations

You have registered a Huawei ID and enabled Huawei Cloud services.. The account cannot be in arrears or frozen.
You have created a VPC and subnet. For details, see Creating a VPC.
You have obtained the AK and SK of your Huawei account. For details, see Access Keys.

Step 1: Preparing DLI Source Data

Create a DLI elastic resource pool and queue.

Log in to the Huawei Cloud console and choose Analytics > Data Lake Insight from the service list. The DLI console is displayed.
In the navigation pane on the left, choose Resources > Resource Pool.

Click Buy Resource Pool in the upper right corner, set the following parameters, and retain the default values for other parameters that are not described in the table.

**Table 1** DLI elastic resource pool parameters
Parameter	Value
Billing Mode	Pay-per-use
Region	CN-Hong Kong
Name	dli_dws
Specifications	Standard
CIDR Block	172.16.0.0/18.

Click Buy and click Submit.
After the resource pool is created, go to the next step.

On the elastic resource pool page, locate the row that contains the created resource pool, click Add Queue in the Operation column, and set the following parameters. Retain the default values for other parameters that are not described in the table.

**Table 2** Adding a queue
Parameter	Value
Name	dli_dws
Type	For SQL

Click Next and click OK. The queue is created.

Upload the source data to the OBS bucket.
1. An OBS bucket has been created with a user-defined name, for example, dli-obs01 (if the bucket name is already in use, use dli-obs02 instead). The region is CN-Hong Kong.
2. Download the data sample file.
3. Create a folder dli_order in the OBS bucket and upload the downloaded data file to that folder.
Go back to the DLI management console. In the navigation pane, click SQL Editor. Select dli_dws for Queue and Default for Database. Run the following command to create a database named dli_data:
1

CREATE DATABASE dli_data;

Create a table.

NOTE:

LOCATION specifies the OBS directory where the data file is stored, formatted as obs://OBS bucket name/folder name. In this example, the directory is obs://dli-obs01/dli_order. If the bucket name or folder name changes, substitute it accordingly.

       
          CREATE EXTERNAL TABLE dli_data.dli_order
     ( order_id      VARCHAR(12),
       order_channel VARCHAR(32),
       order_time    TIMESTAMP,
       cust_code     VARCHAR(6),
       pay_amount    DOUBLE,
       real_pay      DOUBLE ) 
STORED AS parquet
LOCATION 'obs://dli-obs01/dli_order';

Run the following statement to query data.
1

SELECT * FROM dli_data.dli_order;

Step 2: Creating a GaussDB(DWS) Cluster

Create a cluster . To ensure network connectivity, set the region of the GaussDB(DWS) cluster to CN-Hong Kong.

Step 3: Obtaining Authentication Information Required by the GaussDB(DWS) External Server

Obtain the endpoint of the OBS bucket.
1. Log in to the OBS management console.
2. Click the bucket name, choose Overview on the left, and record the endpoint.
Visit Endpoints to obtain the endpoint of DLI.

In this example, the endpoint is dli.ap-southeast-1.myhuaweicloud.com.

In this example (EU-Dublin), the endpoint is dli.eu-west-101.myhuaweicloud.com.
Obtain the project ID for the specific region of the account used to create DLI.
1. Move the cursor to the account name in the upper right corner and click My Credentials.
2. Choose API Credentials on the left.
3. In the list, find the region where the DLI instance is deployed, for example, CN-Hong Kong, and record the project ID corresponding to the region name.
Obtain the AK and SK of your account. For details, see Prerequisites.

Step 4: Importing DLI Table Data Using a Foreign Table

Log in to the GaussDB(DWS) database as the system administrator dbadmin. By default, you can log in to the GaussDB database.

Run the following SQL statements to create a foreign server: The OBS endpoint is obtained from 1, the AK and SK are obtained from Preparations, and the DLI endpoint is obtained from 2.

NOTE:

If the GaussDB(DWS) and DLI instances are created by the same account, enter the AK and SK twice.

       
          CREATE SERVER dli_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS 
(ADDRESS'OBS endpoint',
ACCESS_KEY'AK value'
SECRET_ACCESS_KEY'SK value'
       TYPE 'DLI',
DLI_ADDRESS'DLI endpoint',
DLI_ACCESS_KEY'AK value',
DLI_SECRET_ACCESS_KEY 'SK value'
     );

Run the following SQL statement to create a target schema:
1

CREATE SCHEMA dws_data;

Run the following SQL statements to create a foreign table: Replace Project ID with the actual value obtained in 3.

      
       
         
         CREATE FOREIGN TABLE dws_data.dli_pq_order (
  order_id VARCHAR(14) PRIMARY KEY NOT ENFORCED,
  order_channel VARCHAR(32),
  order_time TIMESTAMP,
  cust_code VARCHAR(6),
  pay_amount DOUBLE PRECISION,
  real_pay DOUBLE PRECISION
)
SERVER dli_server
OPTIONS (
  FORMAT 'parquet',
  ENCODING 'utf8',
DLI_PROJECT_ID'Project ID'
  DLI_DATABASE_NAME 'dli_data',
  DLI_TABLE_NAME 'dli_order')
DISTRIBUTE BY roundrobin;

        

      
     

Run the following SQL statement to query the DLI table data through the foreign table.
The DLI table data is successfully accessed.
1

SELECT * FROM dws_data.dli_pq_order;

Run the following SQL statements to create a local table for importing DLI table data:

      
         CREATE TABLE dws_data.dws_monthly_order
     ( order_month       CHAR(8),
       cust_code         VARCHAR(6),
       order_count       INT,
       total_pay_amount  DOUBLE PRECISION,
       total_real_pay    DOUBLE PRECISION );

Run the following SQL statements to query the monthly order details of 2023 and import the result to the GaussDB(DWS) table:

      
         INSERT INTO dws_data.dws_monthly_order
     ( order_month, cust_code, order_count     
     , total_pay_amount, total_real_pay )
SELECT TO_CHAR(order_time, 'MON-YYYY'), cust_code, COUNT(*)
     , SUM(pay_amount), SUM(real_pay)
  FROM dws_data.dli_pq_order
 WHERE DATE_PART('Year', order_time) = 2023
GROUP BY TO_CHAR(order_time, 'MON-YYYY'), cust_code;

Run the following SQL statement to query table data.

The DLI table data is successfully imported to the DWS database.
1

SELECT * FROM dws_data.dws_monthly_order;