• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. Data Import
  5. Concurrently Importing Data from OBS
  6. About Parallel Data Import from OBS

About Parallel Data Import from OBS

Object Storage Service (OBS) is an object-based massive storage service provided by the public cloud, featuring data storage of high security, proven reliability, and cost-effectiveness. OBS provides large storage capacity for you to store files of any type.

DWS, a data warehouse service, uses OBS as a platform for converting cluster data and external data, satisfying the requirements for secure, reliable, and cost-effective storage.

You can import data in TEXT, CSV, or ORC format from OBS to DWS for query, and can remotely read data from OBS. You are advised to import frequently accessed hot data to DWS to facilitate queries and store cold data to OBS for remote read to reduce cost.

Currently, the operations on ORC files are different from those on TEXT and CSV files.

  • Import or query data in TEXT or CSV format

    You do not need to customize external servers. Remotely reading data in TXT or CSV format is slow. About Parallel Data Import from OBS describes how to import data in TEXT or CSV format from OBS to a cluster. To remotely read data, simply do SELECT to the foreign table created in Creating an OBS Foreign Table.

  • Import or query data in ORC format

    You need to customize an external server. OBS Query Overview describes how to perform the operations on ORC data on OBS. Its subsection Querying Data on OBS Through Foreign Tables describes how to query data in ORC format through a foreign table and how to import and then query data.

Overview

During data migration and Extract-Transform-Load (ETL), a massive volume of data needs to be imported to DWS in parallel. The common import mode is time-consuming. When you import data in parallel using OBS foreign tables, source data files to be imported are identified based on the import URL and data formats specified in the tables. Data is imported in parallel through DNs to DWS, which improves the overall import performance.

Advantages:
  • The CN only plans and delivers data import tasks, and the DNs execute these tasks. This reduces CN resource usage, enabling the CN to process external requests.
  • In this way, the computing capabilities and bandwidths of all the DNs are fully leveraged to import data.
  • You can preprocess data before the import.
  • Fault tolerance can be configured for data format errors during the data import. You can locate incorrect data based on displayed error information after the data is imported.

Disadvantage:

You need to create OBS foreign tables and store to-be-imported data on OBS.

Application Scenario:

A large volume of local data is imported concurrently on many DNs.

Related Concepts

  • Source data file: a TEXT, CSV, or ORC file that stores data to be imported in parallel.
  • OBS: a cloud storage service used to store unstructured data, such as documents, images, and videos. Data is imported in parallel from the OBS server to DWS.
  • Bucket: a container storing objects on OBS.
    • Object storage is a flat storage mode. Layered file system structures are not needed because all objects in buckets are at the same logical layer.
    • In OBS, each bucket name must be unique and cannot be changed. A default ACL is created with a bucket. Each item in the ACL contains permissions granted to certain users, such as READ, WRITE, and FULL_CONTROL. Only authorized users can perform bucket operations, such as creating, deleting, viewing, and setting ACLs for buckets.
    • A user can create a maximum of 100 buckets. The total data size and the number of objects and files in each bucket are not limited.
  • Object: a basic data storage unit in OBS. Data uploaded by users is stored in OBS buckets as objects. Object attributes include Key, Metadata, and Data.

    Generally, objects are managed as files. However, OBS has no file system–related concepts, such as files and folders. To let users easily manage data, OBS allows them to simulate folders. Users can add a slash (/) in the object name, for example, tpcds1000/stock.csv. In this name, tpcds1000 is regarded as the folder name and stock.csv the file name. The value of key (object name) is still tpcds1000/stock.csv, and the content of the object is the content of the stock.csv file.

  • Key: name of an object. It is a UTF-8 character sequence containing 1 to 1024 characters. A key value must be unique in a bucket. Users can name the objects they stored or obtained as Bucket name+Object name.
  • Metadata: object metadata, which contains information about the object. There are system metadata and user metadata. The metadata is uploaded to OBS as key-value pairs together with HTTP headers.
    • System metadata is generated by OBS and used for processing object data. System metadata includes Date, Content-length, last-modify, and Content-MD5.
    • User metadata contains object descriptions specified by users for uploading objects.
  • Data: object content. OBS does not sense the content and regards it as stateless binary data.
  • Ordinary table: A database table that stores data imported to data files in parallel. Ordinary tables are classified into row-store tables and column-store tables.
  • Foreign table: A foreign table is used to identify data in a source data file. The foreign table stores information, such as the location, format, encoding, and inter-data delimiter of a source data file.

How Data Is Imported

Figure 1 shows how data is imported from OBS. The CN plans and delivers data import tasks. It delivers tasks to each DN by file.

The allocation method is as follows:

In Figure 1, there are four DNs (DN0 to DN3) and OBS stores six files numbered from t1.data.0 to t1.data.5. The files are assigned as follows:

t1.data.0 -> DN0

t1.data.1 -> DN1

t1.data.2 -> DN2

t1.data.3 -> DN3

t1.data.4 -> DN0

t1.data.5 -> DN1

DN0 and DN1 each are assigned with two files. Other DNs each are assigned with one file.

The import performance is the best when each DN is assigned with one OBS file and all the files have the same size. To improve the performance of loading data from OBS, split the data file into multiple files as evenly as possible before storing it to OBS. The recommended number of split files is an integer multiple of the DN quantity.

Figure 1 Importing data using OBS foreign tables

Import Process

Figure 2 Parallel import procedure
Table 1 Process description

Procedure

Description

Subtask

Upload data to OBS.

Plan the storage path on the OBS server and upload data files.

For details, see Uploading Data to OBS.

-

Create an OBS foreign table.

Create a foreign table to identify source data files on the OBS server. The OBS foreign table stores data source information, such as its bucket name, object name, file format, storage location, encoding, and delimiters.

For details, see Creating an OBS Foreign Table.

-

Import data.

After creating the foreign table, run the INSERT statement to efficiently import data to the target tables.

For details, see Importing Data.

-

Handle the table with import errors.

If errors occur during data import, handle them based on the displayed error information described in Handling Import Errors to ensure data integrity.

-

Improve query efficiency.

After data is imported, run the ANALYZE statement to generate table statistics. The ANALYZE statement stores the statistics in the PG_STATISTIC system catalog. The execution plan generator uses the statistics to generate the most efficient query execution plan.

For details, see Doing ANALYZE and VACUUM to a Table.

-