• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. Tutorial: Using GDS to Import Data
  5. Step 5: Importing Data to DWS

Step 5: Importing Data to DWS

  1. Run the following statements to create the product_info table in DWS to store imported data:

    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. (Optional) This step is not required in this example because no index is created in Step 1. If the target table has indexes, the index information will be incrementally updated during import, affecting data import performance. You are advised to delete the indexes from the target table before the import. You can create indexes again after the import is complete.

    1. If there is an ordinary table index reasons_idx on the product_id column in the product_info table, delete the index before the import.
      DROP INDEX product_idx;
    2. After importing the data, create the index again.
      CREATE INDEX product_idx ON product_info(product_id);

  3. Import data from source data files to the product_info table through the foreign table product_info_ext.

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

  4. Run SELECT to view the data imported to the target table product_info.

    SELECT count(*) FROM product_info;

    If the following information is displayed, the import is successful:

    count 
    -------
         20
    (1 row)