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

Importing Data

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

If the data amount is small, you can directly 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:

    1
    SELECT * FROM foreign_product_info;
    

    If the query result is the same as the data 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 common tables in the database.

Querying Data After Importing It

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

  1. Create a table in GaussDB(DWS) to store imported data.

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

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

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    DROP TABLE IF EXISTS product_info;
    CREATE TABLE product_info
    (
        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)                   
    ) 
    with (
    orientation = column,
    compression=middle
    ) 
    DISTRIBUTE BY HASH (product_id);
    

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

    Example:

    1
    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 command to view data imported from MRS to GaussDB(DWS):

    1
    SELECT * FROM product_info;
    

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

    (20 rows)