Querying Data on OBS Through Foreign Tables

Viewing Data on OBS 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 on OBS.

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

SELECT * FROM product_info_ext_obs;
If the query result is the same as the data in Original Data, the import is successful. The following information is displayed at the end of the query result:
1

(10 rows)
After data is queried, you can insert the data to common tables in the database.

Querying Data After Importing It

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:

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

          

        

      
     

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 product_info_ext_obs;
If information similar to the following is displayed, the data has been imported.
1

INSERT 0 10
Run the following SELECT command to view data imported from OBS to GaussDB(DWS):
1

SELECT * FROM product_info;
If the query result is the same as the data in Original Data, the import is successful. The following information is displayed at the end of the query result:
1

(10 rows)