• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. Data Import
  5. Importing Data from MRS to a Cluster
  6. Importing Data

Importing Data

Viewing Data in the MRS Data Source by Querying a Foreign Table

If the data amount is small, you can simply run SELECT to query the foreign table and view the data in the MRS data source.

  1. Run the following command to query data from the foreign table:

    SELECT * FROM foreign_product_info;

    If the query result displays data specified in Data File, the import is successful. The following information is displayed at the end of the query result:

    (20 rows)

    After data is queried, you can insert the data to ordinary tables in the database.

Querying Data After Importing It

You can query the MRS data after importing it to DWS.

  1. Create a table in the DWS database to store the imported data.

    The table structure must be the same as the structure of the foreign table created in Creating a Foreign Table. That is, the number of columns and column types must be the same.

    For example, create a table named product_info. The table example is as follows:

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

  2. Run the INSERT INTO.. SELECT .. statement to import data from the foreign table to the target table.

    Example:

    INSERT INTO product_info SELECT * FROM foreign_product_info;
    If information similar to the following is displayed, the data has been imported.
    INSERT 0 20

  3. Run the following SELECT statement to view data imported from MRS to DWS:

    SELECT * FROM product_info;

    If the query result displays data specified in Data File, the import is successful. The following information is displayed at the end of the query result:

    (20 rows)