文档首页 > > 开发指南> 数据库使用入门> 加载示例数据

加载示例数据

分享
更新时间: 2019/11/12 GMT+08:00

在默认数据库postgres中加载示例数据。您将上传的示例数据位于OBS上。

  • 请确保您的SQL客户端已经连接至集群,然后再操作。
  • TCP-DS的示例数据华为云目前仅在华北-北京一区域使用。
  1. 创建表。

    复制并执行下列创建表语句,以在postgres数据库中创建表。有关创建表的更多语法,请参考CREATE TABLE

    DROP SCHEMA if exists tpcds cascade;
    CREATE SCHEMA tpcds;
    SET current_schema TO tpcds;
    CREATE TABLE customer_address
    (
        ca_address_sk             integer               not null,
        ca_address_id             char(16)              not null,
        ca_street_number          char(10)                      ,
        ca_street_name            varchar(60)                   ,
        ca_street_type            char(15)                      ,
        ca_suite_number           char(10)                      ,
        ca_city                   varchar(60)                   ,
        ca_county                 varchar(30)                   ,
        ca_state                  char(2)                       ,
        ca_zip                    char(10)                      ,
        ca_country                varchar(20)                   ,
        ca_gmt_offset             decimal(5,2)                  ,
        ca_location_type          char(20)                      
    )WITH (orientation = column, COMPRESSION = MIDDLE);
    
    CREATE TABLE customer_demographics
    (
        cd_demo_sk                integer               not null,
        cd_gender                 char(1)                       ,
        cd_marital_status         char(1)                       ,
        cd_education_status       char(20)                      ,
        cd_purchase_estimate      integer                       ,
        cd_credit_rating          char(10)                      ,
        cd_dep_count              integer                       ,
        cd_dep_employed_count     integer                       ,
        cd_dep_college_count      integer                       
    )WITH (orientation = column, COMPRESSION = MIDDLE);
    
    CREATE TABLE date_dim
    (
        d_date_sk                 integer               not null,
        d_date_id                 char(16)              not null,
        d_date                    date                          ,
        d_month_seq               integer                       ,
        d_week_seq                integer                       ,
        d_quarter_seq             integer                       ,
        d_year                    integer                       ,
        d_dow                     integer                       ,
        d_moy                     integer                       ,
        d_dom                     integer                       ,
        d_qoy                     integer                       ,
        d_fy_year                 integer                       ,
        d_fy_quarter_seq          integer                       ,
        d_fy_week_seq             integer                       ,
        d_day_name                char(9)                       ,
        d_quarter_name            char(6)                       ,
        d_holiday                 char(1)                       ,
        d_weekend                 char(1)                       ,
        d_following_holiday       char(1)                       ,
        d_first_dom               integer                       ,
        d_last_dom                integer                       ,
        d_same_day_ly             integer                       ,
        d_same_day_lq             integer                       ,
        d_current_day             char(1)                       ,
        d_current_week            char(1)                       ,
        d_current_month           char(1)                       ,
        d_current_quarter         char(1)                       ,
        d_current_year            char(1)                       
    )WITH (orientation = column, COMPRESSION = MIDDLE);
    
    CREATE TABLE warehouse
    (
        w_warehouse_sk            integer               not null,
        w_warehouse_id            char(16)              not null,
        w_warehouse_name          varchar(20)                   ,
        w_warehouse_sq_ft         integer                       ,
        w_street_number           char(10)                      ,
        w_street_name             varchar(60)                   ,
        w_street_type             char(15)                      ,
        w_suite_number            char(10)                      ,
        w_city                    varchar(60)                   ,
        w_county                  varchar(30)                   ,
        w_state                   char(2)                       ,
        w_zip                     char(10)                      ,
        w_country                 varchar(20)                   ,
        w_gmt_offset              decimal(5,2)                  
    )WITH (orientation = column, COMPRESSION = MIDDLE);
    
    CREATE TABLE ship_mode
    (
        sm_ship_mode_sk           integer               not null,
        sm_ship_mode_id           char(16)              not null,
        sm_type                   char(30)                      ,
        sm_code                   char(10)                      ,
        sm_carrier                char(20)                      ,
        sm_contract               char(20)                      
    )WITH (orientation = column, COMPRESSION = MIDDLE);
    
    CREATE TABLE time_dim
    (
        t_time_sk                 integer               not null,
        t_time_id                 char(16)              not null,
        t_time                    integer                       ,
        t_hour                    integer                       ,
        t_minute                  integer                       ,
        t_second                  integer                       ,
        t_am_pm                   char(2)                       ,
        t_shift                   char(20)                      ,
        t_sub_shift               char(20)                      ,
        t_meal_time               char(20)                      
    )WITH (orientation = column, COMPRESSION = MIDDLE);
    
    CREATE TABLE reason
    (
        r_reason_sk               integer               not null,
        r_reason_id               char(16)              not null,
        r_reason_desc             char(100)                     
    )WITH (orientation = column, COMPRESSION = MIDDLE);
    
    CREATE TABLE income_band
    (
        ib_income_band_sk         integer               not null,
        ib_lower_bound            integer                       ,
        ib_upper_bound            integer                       
    )WITH (orientation = column, COMPRESSION = MIDDLE);
    
    CREATE TABLE item
    (
        i_item_sk                 integer               not null,
        i_item_id                 char(16)              not null,
        i_rec_start_date          date                          ,
        i_rec_end_date            date                          ,
        i_item_desc               varchar(200)                  ,
        i_current_price           decimal(7,2)                  ,
        i_wholesale_cost          decimal(7,2)                  ,
        i_brand_id                integer                       ,
        i_brand                   char(50)                      ,
        i_class_id                integer                       ,
        i_class                   char(50)                      ,
        i_category_id             integer                       ,
        i_category                char(50)                      ,
        i_manufact_id             integer                       ,
        i_manufact                char(50)                      ,
        i_size                    char(20)                      ,
        i_formulation             char(20)                      ,
        i_color                   char(20)                      ,
        i_units                   char(10)                      ,
        i_container               char(10)                      ,
        i_manager_id              integer                       ,
        i_product_name            char(50)                      
    )WITH (orientation = column, COMPRESSION = MIDDLE);
    
    CREATE TABLE store
    (
        s_store_sk                integer               not null,
        s_store_id                char(16)              not null,
        s_rec_start_date          date                          ,
        s_rec_end_date            date                          ,
        s_closed_date_sk          integer                       ,
        s_store_name              varchar(50)                   ,
        s_number_employees        integer                       ,
        s_floor_space             integer                       ,
        s_hours                   char(20)                      ,
        s_manager                 varchar(40)                   ,
        s_market_id               integer                       ,
        s_geography_class         varchar(100)                  ,
        s_market_desc             varchar(100)                  ,
        s_market_manager          varchar(40)                   ,
        s_division_id             integer                       ,
        s_division_name           varchar(50)                   ,
        s_company_id              integer                       ,
        s_company_name            varchar(50)                   ,
        s_street_number           varchar(10)                   ,
        s_street_name             varchar(60)                   ,
        s_street_type             char(15)                      ,
        s_suite_number            char(10)                      ,
        s_city                    varchar(60)                   ,
        s_county                  varchar(30)                   ,
        s_state                   char(2)                       ,
        s_zip                     char(10)                      ,
        s_country                 varchar(20)                   ,
        s_gmt_offset              decimal(5,2)                  ,
        s_tax_precentage          decimal(5,2)                  
    )WITH (orientation = column, COMPRESSION = MIDDLE);
    
    CREATE TABLE call_center
    (
        cc_call_center_sk         integer               not null,
        cc_call_center_id         char(16)              not null,
        cc_rec_start_date         date                          ,
        cc_rec_end_date           date                          ,
        cc_closed_date_sk         integer                       ,
        cc_open_date_sk           integer                       ,
        cc_name                   varchar(50)                   ,
        cc_class                  varchar(50)                   ,
        cc_employees              integer                       ,
        cc_sq_ft                  integer                       ,
        cc_hours                  char(20)                      ,
        cc_manager                varchar(40)                   ,
        cc_mkt_id                 integer                       ,
        cc_mkt_class              char(50)                      ,
        cc_mkt_desc               varchar(100)                  ,
        cc_market_manager         varchar(40)                   ,
        cc_division               integer                       ,
        cc_division_name          varchar(50)                   ,
        cc_company                integer                       ,
        cc_company_name           char(50)                      ,
        cc_street_number          char(10)                      ,
        cc_street_name            varchar(60)                   ,
        cc_street_type            char(15)                      ,
        cc_suite_number           char(10)                      ,
        cc_city                   varchar(60)                   ,
        cc_county                 varchar(30)                   ,
        cc_state                  char(2)                       ,
        cc_zip                    char(10)                      ,
        cc_country                varchar(20)                   ,
        cc_gmt_offset             decimal(5,2)                  ,
        cc_tax_percentage         decimal(5,2)                  
    )WITH (orientation = column, COMPRESSION = MIDDLE);
    
    CREATE TABLE customer
    (
        c_customer_sk             integer               not null,
        c_customer_id             char(16)              not null,
        c_current_cdemo_sk        integer                       ,
        c_current_hdemo_sk        integer                       ,
        c_current_addr_sk         integer                       ,
        c_first_shipto_date_sk    integer                       ,
        c_first_sales_date_sk     integer                       ,
        c_salutation              char(10)                      ,
        c_first_name              char(20)                      ,
        c_last_name               char(30)                      ,
        c_preferred_cust_flag     char(1)                       ,
        c_birth_day               integer                       ,
        c_birth_month             integer                       ,
        c_birth_year              integer                       ,
        c_birth_country           varchar(20)                   ,
        c_login                   char(13)                      ,
        c_email_address           char(50)                      ,
        c_last_review_date        char(10)                      
    )WITH (orientation = column, COMPRESSION = MIDDLE);
    
    CREATE TABLE web_site
    (
        web_site_sk               integer               not null,
        web_site_id               char(16)              not null,
        web_rec_start_date        date                          ,
        web_rec_end_date          date                          ,
        web_name                  varchar(50)                   ,
        web_open_date_sk          integer                       ,
        web_close_date_sk         integer                       ,
        web_class                 varchar(50)                   ,
        web_manager               varchar(40)                   ,
        web_mkt_id                integer                       ,
        web_mkt_class             varchar(50)                   ,
        web_mkt_desc              varchar(100)                  ,
        web_market_manager        varchar(40)                   ,
        web_company_id            integer                       ,
        web_company_name          char(50)                      ,
        web_street_number         char(10)                      ,
        web_street_name           varchar(60)                   ,
        web_street_type           char(15)                      ,
        web_suite_number          char(10)                      ,
        web_city                  varchar(60)                   ,
        web_county                varchar(30)                   ,
        web_state                 char(2)                       ,
        web_zip                   char(10)                      ,
        web_country               varchar(20)                   ,
        web_gmt_offset            decimal(5,2)                  ,
        web_tax_percentage        decimal(5,2)                  
    )WITH (orientation = column, COMPRESSION = MIDDLE);
    
    CREATE TABLE store_returns
    (
        sr_returned_date_sk       integer                       ,
        sr_return_time_sk         integer                       ,
        sr_item_sk                integer               not null,
        sr_customer_sk            integer                       ,
        sr_cdemo_sk               integer                       ,
        sr_hdemo_sk               integer                       ,
        sr_addr_sk                integer                       ,
        sr_store_sk               integer                       ,
        sr_reason_sk              integer                       ,
        sr_ticket_number          integer               not null,
        sr_return_quantity        integer                       ,
        sr_return_amt             decimal(7,2)                  ,
        sr_return_tax             decimal(7,2)                  ,
        sr_return_amt_inc_tax     decimal(7,2)                  ,
        sr_fee                    decimal(7,2)                  ,
        sr_return_ship_cost       decimal(7,2)                  ,
        sr_refunded_cash          decimal(7,2)                  ,
        sr_reversed_charge        decimal(7,2)                  ,
        sr_store_credit           decimal(7,2)                  ,
        sr_net_loss               decimal(7,2)                  
    )WITH (orientation = column, COMPRESSION = MIDDLE);
    
    CREATE TABLE household_demographics
    (
        hd_demo_sk                integer               not null,
        hd_income_band_sk         integer                       ,
        hd_buy_potential          char(15)                      ,
        hd_dep_count              integer                       ,
        hd_vehicle_count          integer                       
    )WITH (orientation = column, COMPRESSION = MIDDLE);
    
    CREATE TABLE web_page
    (
        wp_web_page_sk            integer               not null,
        wp_web_page_id            char(16)              not null,
        wp_rec_start_date         date                          ,
        wp_rec_end_date           date                          ,
        wp_creation_date_sk       integer                       ,
        wp_access_date_sk         integer                       ,
        wp_autogen_flag           char(1)                       ,
        wp_customer_sk            integer                       ,
        wp_url                    varchar(100)                  ,
        wp_type                   char(50)                      ,
        wp_char_count             integer                       ,
        wp_link_count             integer                       ,
        wp_image_count            integer                       ,
        wp_max_ad_count           integer                       
    )WITH (orientation = column, COMPRESSION = MIDDLE);
    
    CREATE TABLE promotion
    (
        p_promo_sk                integer               not null,
        p_promo_id                char(16)              not null,
        p_start_date_sk           integer                       ,
        p_end_date_sk             integer                       ,
        p_item_sk                 integer                       ,
        p_cost                    decimal(15,2)                 ,
        p_response_target         integer                       ,
        p_promo_name              char(50)                      ,
        p_channel_dmail           char(1)                       ,
        p_channel_email           char(1)                       ,
        p_channel_catalog         char(1)                       ,
        p_channel_tv              char(1)                       ,
        p_channel_radio           char(1)                       ,
        p_channel_press           char(1)                       ,
        p_channel_event           char(1)                       ,
        p_channel_demo            char(1)                       ,
        p_channel_details         varchar(100)                  ,
        p_purpose                 char(15)                      ,
        p_discount_active         char(1)                       
    )WITH (orientation = column, COMPRESSION = MIDDLE);
    
    CREATE TABLE catalog_page
    (
        cp_catalog_page_sk        integer               not null,
        cp_catalog_page_id        char(16)              not null,
        cp_start_date_sk          integer                       ,
        cp_end_date_sk            integer                       ,
        cp_department             varchar(50)                   ,
        cp_catalog_number         integer                       ,
        cp_catalog_page_number    integer                       ,
        cp_description            varchar(100)                  ,
        cp_type                   varchar(100)                  
    )WITH (orientation = column, COMPRESSION = MIDDLE);
    
    CREATE TABLE inventory
    (
        inv_date_sk               integer               not null,
        inv_item_sk               integer               not null,
        inv_warehouse_sk          integer               not null,
        inv_quantity_on_hand      integer                       
    )WITH (orientation = column, COMPRESSION = MIDDLE);
    
    CREATE TABLE catalog_returns
    (
        cr_returned_date_sk       integer                       ,
        cr_returned_time_sk       integer                       ,
        cr_item_sk                integer               not null,
        cr_refunded_customer_sk   integer                       ,
        cr_refunded_cdemo_sk      integer                       ,
        cr_refunded_hdemo_sk      integer                       ,
        cr_refunded_addr_sk       integer                       ,
        cr_returning_customer_sk  integer                       ,
        cr_returning_cdemo_sk     integer                       ,
        cr_returning_hdemo_sk     integer                       ,
        cr_returning_addr_sk      integer                       ,
        cr_call_center_sk         integer                       ,
        cr_catalog_page_sk        integer                       ,
        cr_ship_mode_sk           integer                       ,
        cr_warehouse_sk           integer                       ,
        cr_reason_sk              integer                       ,
        cr_order_number           integer               not null,
        cr_return_quantity        integer                       ,
        cr_return_amount          decimal(7,2)                  ,
        cr_return_tax             decimal(7,2)                  ,
        cr_return_amt_inc_tax     decimal(7,2)                  ,
        cr_fee                    decimal(7,2)                  ,
        cr_return_ship_cost       decimal(7,2)                  ,
        cr_refunded_cash          decimal(7,2)                  ,
        cr_reversed_charge        decimal(7,2)                  ,
        cr_store_credit           decimal(7,2)                  ,
        cr_net_loss               decimal(7,2)                  
    )WITH (orientation = column, COMPRESSION = MIDDLE);
    
    CREATE TABLE web_returns
    (
        wr_returned_date_sk       integer                       ,
        wr_returned_time_sk       integer                       ,
        wr_item_sk                integer               not null,
        wr_refunded_customer_sk   integer                       ,
        wr_refunded_cdemo_sk      integer                       ,
        wr_refunded_hdemo_sk      integer                       ,
        wr_refunded_addr_sk       integer                       ,
        wr_returning_customer_sk  integer                       ,
        wr_returning_cdemo_sk     integer                       ,
        wr_returning_hdemo_sk     integer                       ,
        wr_returning_addr_sk      integer                       ,
        wr_web_page_sk            integer                       ,
        wr_reason_sk              integer                       ,
        wr_order_number           integer               not null,
        wr_return_quantity        integer                       ,
        wr_return_amt             decimal(7,2)                  ,
        wr_return_tax             decimal(7,2)                  ,
        wr_return_amt_inc_tax     decimal(7,2)                  ,
        wr_fee                    decimal(7,2)                  ,
        wr_return_ship_cost       decimal(7,2)                  ,
        wr_refunded_cash          decimal(7,2)                  ,
        wr_reversed_charge        decimal(7,2)                  ,
        wr_account_credit         decimal(7,2)                  ,
        wr_net_loss               decimal(7,2)                  
    )WITH (orientation = column, COMPRESSION = MIDDLE);
    
    CREATE TABLE web_sales
    (
        ws_sold_date_sk           integer                       ,
        ws_sold_time_sk           integer                       ,
        ws_ship_date_sk           integer                       ,
        ws_item_sk                integer               not null,
        ws_bill_customer_sk       integer                       ,
        ws_bill_cdemo_sk          integer                       ,
        ws_bill_hdemo_sk          integer                       ,
        ws_bill_addr_sk           integer                       ,
        ws_ship_customer_sk       integer                       ,
        ws_ship_cdemo_sk          integer                       ,
        ws_ship_hdemo_sk          integer                       ,
        ws_ship_addr_sk           integer                       ,
        ws_web_page_sk            integer                       ,
        ws_web_site_sk            integer                       ,
        ws_ship_mode_sk           integer                       ,
        ws_warehouse_sk           integer                       ,
        ws_promo_sk               integer                       ,
        ws_order_number           integer               not null,
        ws_quantity               integer                       ,
        ws_wholesale_cost         decimal(7,2)                  ,
        ws_list_price             decimal(7,2)                  ,
        ws_sales_price            decimal(7,2)                  ,
        ws_ext_discount_amt       decimal(7,2)                  ,
        ws_ext_sales_price        decimal(7,2)                  ,
        ws_ext_wholesale_cost     decimal(7,2)                  ,
        ws_ext_list_price         decimal(7,2)                  ,
        ws_ext_tax                decimal(7,2)                  ,
        ws_coupon_amt             decimal(7,2)                  ,
        ws_ext_ship_cost          decimal(7,2)                  ,
        ws_net_paid               decimal(7,2)                  ,
        ws_net_paid_inc_tax       decimal(7,2)                  ,
        ws_net_paid_inc_ship      decimal(7,2)                  ,
        ws_net_paid_inc_ship_tax  decimal(7,2)                  ,
        ws_net_profit             decimal(7,2)                  
    )WITH (orientation = column, COMPRESSION = MIDDLE);
    
    CREATE TABLE catalog_sales
    (
        cs_sold_date_sk           integer                       ,
        cs_sold_time_sk           integer                       ,
        cs_ship_date_sk           integer                       ,
        cs_bill_customer_sk       integer                       ,
        cs_bill_cdemo_sk          integer                       ,
        cs_bill_hdemo_sk          integer                       ,
        cs_bill_addr_sk           integer                       ,
        cs_ship_customer_sk       integer                       ,
        cs_ship_cdemo_sk          integer                       ,
        cs_ship_hdemo_sk          integer                       ,
        cs_ship_addr_sk           integer                       ,
        cs_call_center_sk         integer                       ,
        cs_catalog_page_sk        integer                       ,
        cs_ship_mode_sk           integer                       ,
        cs_warehouse_sk           integer                       ,
        cs_item_sk                integer               not null,
        cs_promo_sk               integer                       ,
        cs_order_number           integer               not null,
        cs_quantity               integer                       ,
        cs_wholesale_cost         decimal(7,2)                  ,
        cs_list_price             decimal(7,2)                  ,
        cs_sales_price            decimal(7,2)                  ,
        cs_ext_discount_amt       decimal(7,2)                  ,
        cs_ext_sales_price        decimal(7,2)                  ,
        cs_ext_wholesale_cost     decimal(7,2)                  ,
        cs_ext_list_price         decimal(7,2)                  ,
        cs_ext_tax                decimal(7,2)                  ,
        cs_coupon_amt             decimal(7,2)                  ,
        cs_ext_ship_cost          decimal(7,2)                  ,
        cs_net_paid               decimal(7,2)                  ,
        cs_net_paid_inc_tax       decimal(7,2)                  ,
        cs_net_paid_inc_ship      decimal(7,2)                  ,
        cs_net_paid_inc_ship_tax  decimal(7,2)                  ,
        cs_net_profit             decimal(7,2)                  
    )WITH (orientation = column, COMPRESSION = MIDDLE);
    
    CREATE TABLE store_sales
    (
        ss_sold_date_sk           integer                       ,
        ss_sold_time_sk           integer                       ,
        ss_item_sk                integer               not null,
        ss_customer_sk            integer                       ,
        ss_cdemo_sk               integer                       ,
        ss_hdemo_sk               integer                       ,
        ss_addr_sk                integer                       ,
        ss_store_sk               integer                       ,
        ss_promo_sk               integer                       ,
        ss_ticket_number          integer               not null,
        ss_quantity               integer                       ,
        ss_wholesale_cost         decimal(7,2)                  ,
        ss_list_price             decimal(7,2)                  ,
        ss_sales_price            decimal(7,2)                  ,
        ss_ext_discount_amt       decimal(7,2)                  ,
        ss_ext_sales_price        decimal(7,2)                  ,
        ss_ext_wholesale_cost     decimal(7,2)                  ,
        ss_ext_list_price         decimal(7,2)                  ,
        ss_ext_tax                decimal(7,2)                  ,
        ss_coupon_amt             decimal(7,2)                  ,
        ss_net_paid               decimal(7,2)                  ,
        ss_net_paid_inc_tax       decimal(7,2)                  ,
        ss_net_profit             decimal(7,2)                  
    )WITH (orientation = column, COMPRESSION = MIDDLE);
    
  2. 创建OBS外表。
    复制并执行下列创建OBS外表语句,以在postgres数据库中创建OBS外表,用于识别数据格式及设置导入容错性。更多语法信息,请参考 CREATE FOREIGN TABLE (OBS导入导出)
    CREATE FOREIGN TABLE obs_from_customer_address_001
    (
    ca_address_sk integer not null,
    ca_address_id char(16) not null,
    ca_street_number char(10) ,
    ca_street_name varchar(60) ,
    ca_street_type char(15) ,
    ca_suite_number char(10) ,
    ca_city varchar(60) ,
    ca_county varchar(30) ,
    ca_state char(2) ,
    ca_zip char(10) ,
    ca_country varchar(20) ,
    ca_gmt_offset float4 ,
    ca_location_type char(20) 
    )
    SERVER gsmpp_server
    OPTIONS (
    location 'obs://dws/download/dws_sample_database_data_files/customer_address/customer_address',
    format 'text',
    delimiter '|',
    encoding 'utf8',
    noescaping 'true',
    ACCESS_KEY 'access_key_value_to_be_replaced',
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
    reject_limit 'unlimited',
    chunksize '64'
    )
    with err_obs_from_customer_address_001;
    
    CREATE FOREIGN TABLE obs_from_customer_demographics_001
    (
        cd_demo_sk                integer               not null,
        cd_gender                 char(1)                       ,
        cd_marital_status         char(1)                       ,
        cd_education_status       char(20)                      ,
        cd_purchase_estimate      integer                       ,
        cd_credit_rating          char(10)                      ,
        cd_dep_count              integer                       ,
        cd_dep_employed_count     integer                       ,
        cd_dep_college_count      integer                       
    )
    SERVER gsmpp_server
    OPTIONS (
    location 'obs://dws/download/dws_sample_database_data_files/customer_demographics/customer_demographics',
    format 'text',
    delimiter '|',
    encoding 'utf8',
    noescaping 'true',
    ACCESS_KEY 'access_key_value_to_be_replaced',
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
    reject_limit 'unlimited',
    chunksize '64'
    )
    with err_obs_from_customer_demographics_001;
    
    CREATE FOREIGN TABLE obs_from_date_dim_001
    (
        d_date_sk                 integer               not null,
        d_date_id                 char(16)              not null,
        d_date                    date                          ,
        d_month_seq               integer                       ,
        d_week_seq                integer                       ,
        d_quarter_seq             integer                       ,
        d_year                    integer                       ,
        d_dow                     integer                       ,
        d_moy                     integer                       ,
        d_dom                     integer                       ,
        d_qoy                     integer                       ,
        d_fy_year                 integer                       ,
        d_fy_quarter_seq          integer                       ,
        d_fy_week_seq             integer                       ,
        d_day_name                char(9)                       ,
        d_quarter_name            char(6)                       ,
        d_holiday                 char(1)                       ,
        d_weekend                 char(1)                       ,
        d_following_holiday       char(1)                       ,
        d_first_dom               integer                       ,
        d_last_dom                integer                       ,
        d_same_day_ly             integer                       ,
        d_same_day_lq             integer                       ,
        d_current_day             char(1)                       ,
        d_current_week            char(1)                       ,
        d_current_month           char(1)                       ,
        d_current_quarter         char(1)                       ,
        d_current_year            char(1)                       
    )
    SERVER gsmpp_server
    OPTIONS (
    location 'obs://dws/download/dws_sample_database_data_files/ date_dim/date_dim',
    format 'text',
    delimiter '|',
    encoding 'utf8',
    noescaping 'true',
    ACCESS_KEY 'access_key_value_to_be_replaced',
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
    reject_limit 'unlimited',
    chunksize '64'
    )
    with err_obs_from_date_dim_001;
    
    CREATE FOREIGN TABLE obs_from_warehouse_001
    (
        w_warehouse_sk            integer               not null,
        w_warehouse_id            char(16)              not null,
        w_warehouse_name          varchar(20)                   ,
        w_warehouse_sq_ft         integer                       ,
        w_street_number           char(10)                      ,
        w_street_name             varchar(60)                   ,
        w_street_type             char(15)                      ,
        w_suite_number            char(10)                      ,
        w_city                    varchar(60)                   ,
        w_county                  varchar(30)                   ,
        w_state                   char(2)                       ,
        w_zip                     char(10)                      ,
        w_country                 varchar(20)                   ,
        w_gmt_offset              decimal(5,2)                  
    )
    SERVER gsmpp_server
    OPTIONS (
    location 'obs://dws/download/dws_sample_database_data_files/ warehouse/warehouse',
    format 'text',
    delimiter '|',
    encoding 'utf8',
    noescaping 'true',
    ACCESS_KEY 'access_key_value_to_be_replaced',
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
    reject_limit 'unlimited',
    chunksize '64'
    )
    with err_obs_from_warehouse_001;
    
    CREATE FOREIGN TABLE obs_from_ship_mode_001
    (
        sm_ship_mode_sk           integer               not null,
        sm_ship_mode_id           char(16)              not null,
        sm_type                   char(30)                      ,
        sm_code                   char(10)                      ,
        sm_carrier                char(20)                      ,
        sm_contract               char(20)                      
    )
    SERVER gsmpp_server
    OPTIONS (
    location 'obs://dws/download/dws_sample_database_data_files/ ship_mode/ship_mode' ,
    format 'text',
    delimiter '|',
    encoding 'utf8',
    noescaping 'true',
    ACCESS_KEY 'access_key_value_to_be_replaced',
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
    reject_limit 'unlimited',
    chunksize '64'
    )
    with err_obs_from_ship_mode_001;
    
    CREATE FOREIGN TABLE obs_from_time_dim_001
    (
        t_time_sk                 integer               not null,
        t_time_id                 char(16)              not null,
        t_time                    integer                       ,
        t_hour                    integer                       ,
        t_minute                  integer                       ,
        t_second                  integer                       ,
        t_am_pm                   char(2)                       ,
        t_shift                   char(20)                      ,
        t_sub_shift               char(20)                      ,
        t_meal_time               char(20)                     
    )
    SERVER gsmpp_server
    OPTIONS (
    location 'obs://dws/download/dws_sample_database_data_files/time_dim/time_dim',
    format 'text',
    delimiter '|',
    encoding 'utf8',
    noescaping 'true',
    ACCESS_KEY 'access_key_value_to_be_replaced',
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
    reject_limit 'unlimited',
    chunksize '64'
    )
    with err_obs_from_time_dim_001;
    
    CREATE FOREIGN TABLE obs_from_reason_001
    (
        r_reason_sk               integer               not null,
        r_reason_id               char(16)              not null,
        r_reason_desc             char(100)                    
    )
    SERVER gsmpp_server
    OPTIONS (
    location 'obs://dws/download/dws_sample_database_data_files/reason/reason' ,
    format 'text',
    delimiter '|',
    encoding 'utf8',
    noescaping 'true',
    ACCESS_KEY 'access_key_value_to_be_replaced',
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
    reject_limit 'unlimited',
    chunksize '64'
    )
    with err_obs_from_reason_001;
    
    CREATE FOREIGN TABLE obs_from_income_band_001
    (
        ib_income_band_sk         integer               not null,
        ib_lower_bound            integer                       ,
        ib_upper_bound            integer                       
    )
    SERVER gsmpp_server
    OPTIONS (
    location 'obs://dws/download/dws_sample_database_data_files/income_band/income_band',
    format 'text',
    delimiter '|',
    encoding 'utf8',
    noescaping 'true',
    ACCESS_KEY 'access_key_value_to_be_replaced',
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
    reject_limit 'unlimited',
    chunksize '64'
    )
    with err_obs_from_income_band_001;
    
    CREATE FOREIGN TABLE obs_from_item_001
    (
        i_item_sk                 integer               not null,
        i_item_id                 char(16)              not null,
        i_rec_start_date          date                          ,
        i_rec_end_date            date                          ,
        i_item_desc               varchar(200)                  ,
        i_current_price           decimal(7,2)                  ,
        i_wholesale_cost          decimal(7,2)                  ,
        i_brand_id                integer                       ,
        i_brand                   char(50)                      ,
        i_class_id                integer                       ,
        i_class                   char(50)                      ,
        i_category_id             integer                       ,
        i_category                char(50)                      ,
        i_manufact_id             integer                       ,
        i_manufact                char(50)                      ,
        i_size                    char(20)                      ,
        i_formulation             char(20)                      ,
        i_color                   char(20)                      ,
        i_units                   char(10)                      ,
        i_container               char(10)                      ,
        i_manager_id              integer                       ,
        i_product_name            char(50)                      
    )
    SERVER gsmpp_server
    OPTIONS (
    location 'obs://dws/download/dws_sample_database_data_files/item/item',
    format 'text',
    delimiter '|',
    encoding 'utf8',
    noescaping 'true',
    ACCESS_KEY 'access_key_value_to_be_replaced',
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
    reject_limit 'unlimited',
    chunksize '64'
    )
    with err_obs_from_item_001;
    
    CREATE FOREIGN TABLE obs_from_store_001
    (
        s_store_sk                integer               not null,
        s_store_id                char(16)              not null,
        s_rec_start_date          date                          ,
        s_rec_end_date            date                          ,
        s_closed_date_sk          integer                       ,
        s_store_name              varchar(50)                   ,
        s_number_employees        integer                       ,
        s_floor_space             integer                       ,
        s_hours                   char(20)                      ,
        s_manager                 varchar(40)                   ,
        s_market_id               integer                       ,
        s_geography_class         varchar(100)                  ,
        s_market_desc             varchar(100)                  ,
        s_market_manager          varchar(40)                   ,
        s_division_id             integer                       ,
        s_division_name           varchar(50)                   ,
        s_company_id              integer                       ,
        s_company_name            varchar(50)                   ,
        s_street_number           varchar(10)                   ,
        s_street_name             varchar(60)                   ,
        s_street_type             char(15)                      ,
        s_suite_number            char(10)                      ,
        s_city                    varchar(60)                   ,
        s_county                  varchar(30)                   ,
        s_state                   char(2)                       ,
        s_zip                     char(10)                      ,
        s_country                 varchar(20)                   ,
        s_gmt_offset              decimal(5,2)                  ,
        s_tax_precentage          decimal(5,2)                  
    )
    SERVER gsmpp_server
    OPTIONS (
    location 'obs://dws/download/dws_sample_database_data_files/store/store',
    format 'text',
    delimiter '|',
    encoding 'utf8',
    noescaping 'true',
    ACCESS_KEY 'access_key_value_to_be_replaced',
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
    reject_limit 'unlimited',
    chunksize '64'
    )
    with err_obs_from_store_001;
    
    CREATE FOREIGN TABLE obs_from_call_center_001
    (
        cc_call_center_sk         integer               not null,
        cc_call_center_id         char(16)              not null,
        cc_rec_start_date         date                          ,
        cc_rec_end_date           date                          ,
        cc_closed_date_sk         integer                       ,
        cc_open_date_sk           integer                       ,
        cc_name                   varchar(50)                   ,
        cc_class                  varchar(50)                   ,
        cc_employees              integer                       ,
        cc_sq_ft                  integer                       ,
        cc_hours                  char(20)                      ,
        cc_manager                varchar(40)                   ,
        cc_mkt_id                 integer                       ,
        cc_mkt_class              char(50)                      ,
        cc_mkt_desc               varchar(100)                  ,
        cc_market_manager         varchar(40)                   ,
        cc_division               integer                       ,
        cc_division_name          varchar(50)                   ,
        cc_company                integer                       ,
        cc_company_name           char(50)                      ,
        cc_street_number          char(10)                      ,
        cc_street_name            varchar(60)                   ,
        cc_street_type            char(15)                      ,
        cc_suite_number           char(10)                      ,
        cc_city                   varchar(60)                   ,
        cc_county                 varchar(30)                   ,
        cc_state                  char(2)                       ,
        cc_zip                    char(10)                      ,
        cc_country                varchar(20)                   ,
        cc_gmt_offset             decimal(5,2)                  ,
        cc_tax_percentage         decimal(5,2)                  
    )
    SERVER gsmpp_server
    OPTIONS (
    location 'obs://dws/download/dws_sample_database_data_files/call_center/call_center',
    format 'text',
    delimiter '|',
    encoding 'utf8',
    noescaping 'true',
    ACCESS_KEY 'access_key_value_to_be_replaced',
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
    reject_limit 'unlimited',
    chunksize '64'
    )
    with err_obs_from_call_center_001;
    
    CREATE FOREIGN TABLE obs_from_customer_001
    (
        c_customer_sk             integer               not null,
        c_customer_id             char(16)              not null,
        c_current_cdemo_sk        integer                       ,
        c_current_hdemo_sk        integer                       ,
        c_current_addr_sk         integer                       ,
        c_first_shipto_date_sk    integer                       ,
        c_first_sales_date_sk     integer                       ,
        c_salutation              char(10)                      ,
        c_first_name              char(20)                      ,
        c_last_name               char(30)                      ,
        c_preferred_cust_flag     char(1)                       ,
        c_birth_day               integer                       ,
        c_birth_month             integer                       ,
        c_birth_year              integer                       ,
        c_birth_country           varchar(20)                   ,
        c_login                   char(13)                      ,
        c_email_address           char(50)                      ,
        c_last_review_date        char(10)                      
    )
    SERVER gsmpp_server
    OPTIONS (
    location 'obs://dws/download/dws_sample_database_data_files/customer/customer' ,
    format 'text',
    delimiter '|',
    encoding 'utf8',
    noescaping 'true',
    ACCESS_KEY 'access_key_value_to_be_replaced',
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
    reject_limit 'unlimited',
    chunksize '64'
    )
    with err_obs_from_customer_001;
    
    CREATE FOREIGN TABLE obs_from_web_site_001
    (
        web_site_sk               integer               not null,
        web_site_id               char(16)              not null,
        web_rec_start_date        date                          ,
        web_rec_end_date          date                          ,
        web_name                  varchar(50)                   ,
        web_open_date_sk          integer                       ,
        web_close_date_sk         integer                       ,
        web_class                 varchar(50)                   ,
        web_manager               varchar(40)                   ,
        web_mkt_id                integer                       ,
        web_mkt_class             varchar(50)                   ,
        web_mkt_desc              varchar(100)                  ,
        web_market_manager        varchar(40)                   ,
        web_company_id            integer                       ,
        web_company_name          char(50)                      ,
        web_street_number         char(10)                      ,
        web_street_name           varchar(60)                   ,
        web_street_type           char(15)                      ,
        web_suite_number          char(10)                      ,
        web_city                  varchar(60)                   ,
        web_county                varchar(30)                   ,
        web_state                 char(2)                       ,
        web_zip                   char(10)                      ,
        web_country               varchar(20)                   ,
        web_gmt_offset            decimal(5,2)                  ,
        web_tax_percentage        decimal(5,2)                  
    )
    SERVER gsmpp_server
    OPTIONS (
    location 'obs://dws/download/dws_sample_database_data_files/web_site/web_site' ,
    format 'text',
    delimiter '|',
    encoding 'utf8',
    noescaping 'true',
    ACCESS_KEY 'access_key_value_to_be_replaced',
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
    reject_limit 'unlimited',
    chunksize '64'
    )
    with err_obs_from_web_site_001;
    
    CREATE FOREIGN TABLE obs_from_store_returns_001
    (
        sr_returned_date_sk       integer                       ,
        sr_return_time_sk         integer                       ,
        sr_item_sk                integer               not null,
        sr_customer_sk            integer                       ,
        sr_cdemo_sk               integer                       ,
        sr_hdemo_sk               integer                       ,
        sr_addr_sk                integer                       ,
        sr_store_sk               integer                       ,
        sr_reason_sk              integer                       ,
        sr_ticket_number          bigint               not null,
        sr_return_quantity        integer                       ,
        sr_return_amt             decimal(7,2)                  ,
        sr_return_tax             decimal(7,2)                  ,
        sr_return_amt_inc_tax     decimal(7,2)                  ,
        sr_fee                    decimal(7,2)                  ,
        sr_return_ship_cost       decimal(7,2)                  ,
        sr_refunded_cash          decimal(7,2)                  ,
        sr_reversed_charge        decimal(7,2)                  ,
        sr_store_credit           decimal(7,2)                  ,
        sr_net_loss               decimal(7,2)                  
    )
    SERVER gsmpp_server
    OPTIONS (
    location 'obs://dws/download/dws_sample_database_data_files/store_returns/store_returns' ,
    format 'text',
    delimiter '|',
    encoding 'utf8',
    noescaping 'true',
    ACCESS_KEY 'access_key_value_to_be_replaced',
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
    reject_limit 'unlimited',
    chunksize '64'
    )
    with err_obs_from_store_returns_001;
    
    CREATE FOREIGN TABLE obs_from_household_demographics_001
    (
        hd_demo_sk                integer               not null,
        hd_income_band_sk         integer                       ,
        hd_buy_potential          char(15)                      ,
        hd_dep_count              integer                       ,
        hd_vehicle_count          integer                       
    )
    SERVER gsmpp_server
    OPTIONS (
    location 'obs://dws/download/dws_sample_database_data_files/household_demographics/household_demographics' ,
    format 'text',
    delimiter '|',
    encoding 'utf8',
    noescaping 'true',
    ACCESS_KEY 'access_key_value_to_be_replaced',
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
    reject_limit 'unlimited',
    chunksize '64'
    )
    with err_obs_from_household_demographics_001;
    
    CREATE FOREIGN TABLE obs_from_web_page_001
    (
        wp_web_page_sk            integer               not null,
        wp_web_page_id            char(16)              not null,
        wp_rec_start_date         date                          ,
        wp_rec_end_date           date                          ,
        wp_creation_date_sk       integer                       ,
        wp_access_date_sk         integer                       ,
        wp_autogen_flag           char(1)                       ,
        wp_customer_sk            integer                       ,
        wp_url                    varchar(100)                  ,
        wp_type                   char(50)                      ,
        wp_char_count             integer                       ,
        wp_link_count             integer                       ,
        wp_image_count            integer                       ,
        wp_max_ad_count           integer                       
    )
    SERVER gsmpp_server
    OPTIONS (
    location 'obs://dws/download/dws_sample_database_data_files/web_page/web_page' ,
    format 'text',
    delimiter '|',
    encoding 'utf8',
    noescaping 'true',
    ACCESS_KEY 'access_key_value_to_be_replaced',
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
    reject_limit 'unlimited',
    chunksize '64'
    )
    with err_obs_from_web_page_001;
    
    CREATE FOREIGN TABLE obs_from_promotion_001
    (
        p_promo_sk                integer               not null,
        p_promo_id                char(16)              not null,
        p_start_date_sk           integer                       ,
        p_end_date_sk             integer                       ,
        p_item_sk                 integer                       ,
        p_cost                    decimal(15,2)                 ,
        p_response_target         integer                       ,
        p_promo_name              char(50)                      ,
        p_channel_dmail           char(1)                       ,
        p_channel_email           char(1)                       ,
        p_channel_catalog         char(1)                       ,
        p_channel_tv              char(1)                       ,
        p_channel_radio           char(1)                       ,
        p_channel_press           char(1)                       ,
        p_channel_event           char(1)                       ,
        p_channel_demo            char(1)                       ,
        p_channel_details         varchar(100)                  ,
        p_purpose                 char(15)                      ,
        p_discount_active         char(1)                       
    )
    SERVER gsmpp_server
    OPTIONS (
    location 'obs://dws/download/dws_sample_database_data_files/promotion/promotion' ,
    format 'text',
    delimiter '|',
    encoding 'utf8',
    noescaping 'true',
    ACCESS_KEY 'access_key_value_to_be_replaced',
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
    reject_limit 'unlimited',
    chunksize '64'
    )
    with err_obs_from_promotion_001;
    
    CREATE FOREIGN TABLE obs_from_catalog_page_001
    (
        cp_catalog_page_sk        integer               not null,
        cp_catalog_page_id        char(16)              not null,
        cp_start_date_sk          integer                       ,
        cp_end_date_sk            integer                       ,
        cp_department             varchar(50)                   ,
        cp_catalog_number         integer                       ,
        cp_catalog_page_number    integer                       ,
        cp_description            varchar(100)                  ,
        cp_type                   varchar(100)                 
    )
    SERVER gsmpp_server
    OPTIONS (
    location 'obs://dws/download/dws_sample_database_data_files/catalog_page/catalog_page' ,
    format 'text',
    delimiter '|',
    encoding 'utf8',
    noescaping 'true',
    ACCESS_KEY 'access_key_value_to_be_replaced',
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
    reject_limit 'unlimited',
    chunksize '64'
    )
    with err_obs_from_catalog_page_001;
    
    CREATE FOREIGN TABLE obs_from_inventory_001
    (
        inv_date_sk               integer               not null,
        inv_item_sk               integer               not null,
        inv_warehouse_sk          integer               not null,
        inv_quantity_on_hand      integer                       
    )
    SERVER gsmpp_server
    OPTIONS (
    location 'obs://dws/download/dws_sample_database_data_files/inventory/inventory' ,
    format 'text',
    delimiter '|',
    encoding 'utf8',
    noescaping 'true',
    ACCESS_KEY 'access_key_value_to_be_replaced',
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
    reject_limit 'unlimited',
    chunksize '64'
    )
    with err_obs_from_inventory_001;
    
    CREATE FOREIGN TABLE obs_from_catalog_returns_001
    (
        cr_returned_date_sk       integer                       ,
        cr_returned_time_sk       integer                       ,
        cr_item_sk                integer               not null,
        cr_refunded_customer_sk   integer                       ,
        cr_refunded_cdemo_sk      integer                       ,
        cr_refunded_hdemo_sk      integer                       ,
        cr_refunded_addr_sk       integer                       ,
        cr_returning_customer_sk  integer                       ,
        cr_returning_cdemo_sk     integer                       ,
        cr_returning_hdemo_sk     integer                       ,
        cr_returning_addr_sk      integer                       ,
        cr_call_center_sk         integer                       ,
        cr_catalog_page_sk        integer                       ,
        cr_ship_mode_sk           integer                       ,
        cr_warehouse_sk           integer                       ,
        cr_reason_sk              integer                       ,
        cr_order_number           bigint               not null,
        cr_return_quantity        integer                       ,
        cr_return_amount          decimal(7,2)                  ,
        cr_return_tax             decimal(7,2)                  ,
        cr_return_amt_inc_tax     decimal(7,2)                  ,
        cr_fee                    decimal(7,2)                  ,
        cr_return_ship_cost       decimal(7,2)                  ,
        cr_refunded_cash          decimal(7,2)                  ,
        cr_reversed_charge        decimal(7,2)                  ,
        cr_store_credit           decimal(7,2)                  ,
        cr_net_loss               decimal(7,2)                 
    )
    SERVER gsmpp_server
    OPTIONS (
    location 'obs://dws/download/dws_sample_database_data_files/catalog_returns/catalog_returns' ,
    format 'text',
    delimiter '|',
    encoding 'utf8',
    noescaping 'true',
    ACCESS_KEY 'access_key_value_to_be_replaced',
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
    reject_limit 'unlimited',
    chunksize '64'
    )
    with err_obs_from_catalog_returns_001;
    
    CREATE FOREIGN TABLE obs_from_web_returns_001
    (
        wr_returned_date_sk       integer                       ,
        wr_returned_time_sk       integer                       ,
        wr_item_sk                integer               not null,
        wr_refunded_customer_sk   integer                       ,
        wr_refunded_cdemo_sk      integer                       ,
        wr_refunded_hdemo_sk      integer                       ,
        wr_refunded_addr_sk       integer                       ,
        wr_returning_customer_sk  integer                       ,
        wr_returning_cdemo_sk     integer                       ,
        wr_returning_hdemo_sk     integer                       ,
        wr_returning_addr_sk      integer                       ,
        wr_web_page_sk            integer                       ,
        wr_reason_sk              integer                       ,
        wr_order_number           bigint               not null,
        wr_return_quantity        integer                       ,
        wr_return_amt             decimal(7,2)                  ,
        wr_return_tax             decimal(7,2)                  ,
        wr_return_amt_inc_tax     decimal(7,2)                  ,
        wr_fee                    decimal(7,2)                  ,
        wr_return_ship_cost       decimal(7,2)                  ,
        wr_refunded_cash          decimal(7,2)                  ,
        wr_reversed_charge        decimal(7,2)                  ,
        wr_account_credit         decimal(7,2)                  ,
        wr_net_loss               decimal(7,2)                 
    )
    SERVER gsmpp_server
    OPTIONS (
    location 'obs://dws/download/dws_sample_database_data_files/web_returns/web_returns' ,
    format 'text',
    delimiter '|',
    encoding 'utf8',
    noescaping 'true',
    ACCESS_KEY 'access_key_value_to_be_replaced',
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
    reject_limit 'unlimited',
    chunksize '64'
    )
    with err_obs_from_web_returns_001;
    
    CREATE FOREIGN TABLE obs_from_web_sales_001
    (
        ws_sold_date_sk           integer                       ,
        ws_sold_time_sk           integer                       ,
        ws_ship_date_sk           integer                       ,
        ws_item_sk                integer               not null,
        ws_bill_customer_sk       integer                       ,
        ws_bill_cdemo_sk          integer                       ,
        ws_bill_hdemo_sk          integer                       ,
        ws_bill_addr_sk           integer                       ,
        ws_ship_customer_sk       integer                       ,
        ws_ship_cdemo_sk          integer                       ,
        ws_ship_hdemo_sk          integer                       ,
        ws_ship_addr_sk           integer                       ,
        ws_web_page_sk            integer                       ,
        ws_web_site_sk            integer                       ,
        ws_ship_mode_sk           integer                       ,
        ws_warehouse_sk           integer                       ,
        ws_promo_sk               integer                       ,
        ws_order_number           bigint               not null,
        ws_quantity               integer                       ,
        ws_wholesale_cost         decimal(7,2)                  ,
        ws_list_price             decimal(7,2)                  ,
        ws_sales_price            decimal(7,2)                  ,
        ws_ext_discount_amt       decimal(7,2)                  ,
        ws_ext_sales_price        decimal(7,2)                  ,
        ws_ext_wholesale_cost     decimal(7,2)                  ,
        ws_ext_list_price         decimal(7,2)                  ,
        ws_ext_tax                decimal(7,2)                  ,
        ws_coupon_amt             decimal(7,2)                  ,
        ws_ext_ship_cost          decimal(7,2)                  ,
        ws_net_paid               decimal(7,2)                  ,
        ws_net_paid_inc_tax       decimal(7,2)                  ,
        ws_net_paid_inc_ship      decimal(7,2)                  ,
        ws_net_paid_inc_ship_tax  decimal(7,2)                  ,
        ws_net_profit             decimal(7,2)                  
    )
    SERVER gsmpp_server
    OPTIONS (
    location 'obs://dws/download/dws_sample_database_data_files/web_sales/web_sales' ,
    format 'text',
    delimiter '|',
    encoding 'utf8',
    noescaping 'true',
    ACCESS_KEY 'access_key_value_to_be_replaced',
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
    reject_limit 'unlimited',
    chunksize '64'
    )
    with err_obs_from_web_sales_001;
    
    CREATE FOREIGN TABLE obs_from_catalog_sales_001
    (
        cs_sold_date_sk           integer                       ,
        cs_sold_time_sk           integer                       ,
        cs_ship_date_sk           integer                       ,
        cs_bill_customer_sk       integer                       ,
        cs_bill_cdemo_sk          integer                       ,
        cs_bill_hdemo_sk          integer                       ,
        cs_bill_addr_sk           integer                       ,
        cs_ship_customer_sk       integer                       ,
        cs_ship_cdemo_sk          integer                       ,
        cs_ship_hdemo_sk          integer                       ,
        cs_ship_addr_sk           integer                       ,
        cs_call_center_sk         integer                       ,
        cs_catalog_page_sk        integer                       ,
        cs_ship_mode_sk           integer                       ,
        cs_warehouse_sk           integer                       ,
        cs_item_sk                integer               not null,
        cs_promo_sk               integer                       ,
        cs_order_number           bigint               not null,
        cs_quantity               integer                       ,
        cs_wholesale_cost         decimal(7,2)                  ,
        cs_list_price             decimal(7,2)                  ,
        cs_sales_price            decimal(7,2)                  ,
        cs_ext_discount_amt       decimal(7,2)                  ,
        cs_ext_sales_price        decimal(7,2)                  ,
        cs_ext_wholesale_cost     decimal(7,2)                  ,
        cs_ext_list_price         decimal(7,2)                  ,
        cs_ext_tax                decimal(7,2)                  ,
        cs_coupon_amt             decimal(7,2)                  ,
        cs_ext_ship_cost          decimal(7,2)                  ,
        cs_net_paid               decimal(7,2)                  ,
        cs_net_paid_inc_tax       decimal(7,2)                  ,
        cs_net_paid_inc_ship      decimal(7,2)                  ,
        cs_net_paid_inc_ship_tax  decimal(7,2)                  ,
        cs_net_profit             decimal(7,2)                  
    )
    SERVER gsmpp_server
    OPTIONS (
    location 'obs://dws/download/dws_sample_database_data_files/catalog_sales/catalog_sales' ,
    format 'text',
    delimiter '|',
    encoding 'utf8',
    noescaping 'true',
    ACCESS_KEY 'access_key_value_to_be_replaced',
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
    reject_limit 'unlimited',
    chunksize '64'
    )
    with err_obs_from_catalog_sales_001;
    
    CREATE FOREIGN TABLE obs_from_store_sales_001
    (
        ss_sold_date_sk           integer                       ,
        ss_sold_time_sk           integer                       ,
        ss_item_sk                integer               not null,
        ss_customer_sk            integer                       ,
        ss_cdemo_sk               integer                       ,
        ss_hdemo_sk               integer                       ,
        ss_addr_sk                integer                       ,
        ss_store_sk               integer                       ,
        ss_promo_sk               integer                       ,
        ss_ticket_number          bigint               not null,
        ss_quantity               integer                       ,
        ss_wholesale_cost         decimal(7,2)                  ,
        ss_list_price             decimal(7,2)                  ,
        ss_sales_price            decimal(7,2)                  ,
        ss_ext_discount_amt       decimal(7,2)                  ,
        ss_ext_sales_price        decimal(7,2)                  ,
        ss_ext_wholesale_cost     decimal(7,2)                  ,
        ss_ext_list_price         decimal(7,2)                  ,
        ss_ext_tax                decimal(7,2)                  ,
        ss_coupon_amt             decimal(7,2)                  ,
        ss_net_paid               decimal(7,2)                  ,
        ss_net_paid_inc_tax       decimal(7,2)                  ,
        ss_net_profit             decimal(7,2)                  
    )
    SERVER gsmpp_server
    OPTIONS (
    location 'obs://dws/download/dws_sample_database_data_files/store_sales/store_sales' ,
    format 'text',
    delimiter '|',
    encoding 'utf8',
    noescaping 'true',
    ACCESS_KEY 'access_key_value_to_be_replaced',
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',
    reject_limit 'unlimited',
    chunksize '64'
    )
    with err_obs_from_store_sales_001;
  3. 将创建外表语句中的参数ACCESS_KEY和SECRET_ACCESS_KEY替换为实际值,然后在客户端工具中执行替换后的语句创建外表。

    ACCESS_KEY和SECRET_ACCESS_KEY的值,请参见本文档的创建访问密钥(AK和SK)章节进行获取,然后将获取到的值替换到创建外表语句中。

  4. 使用INSERT命令导入数据。
    INSERT INTO customer_address SELECT * FROM obs_from_customer_address_001 ;
    INSERT INTO customer_demographics SELECT * FROM obs_from_customer_demographics_001 ;
    INSERT INTO date_dim SELECT * FROM obs_from_date_dim_001;
    INSERT INTO warehouse SELECT * FROM obs_from_warehouse_001;
    INSERT INTO ship_mode SELECT * FROM obs_from_ship_mode_001;
    INSERT INTO time_dim SELECT * FROM obs_from_time_dim_001;
    INSERT INTO reason SELECT * FROM obs_from_reason_001;
    INSERT INTO income_band SELECT * FROM obs_from_income_band_001;
    INSERT INTO item SELECT * FROM obs_from_item_001;
    INSERT INTO store SELECT * FROM obs_from_store_001;
    INSERT INTO call_center SELECT * FROM obs_from_call_center_001;
    INSERT INTO customer SELECT * FROM obs_from_customer_001;
    INSERT INTO web_site SELECT * FROM obs_from_web_site_001;
    INSERT INTO store_returns SELECT * FROM obs_from_store_returns_001;
    INSERT INTO household_demographics SELECT * FROM obs_from_household_demographics_001;
    INSERT INTO web_page SELECT * FROM obs_from_web_page_001;
    INSERT INTO promotion SELECT * FROM obs_from_promotion_001;
    INSERT INTO catalog_page SELECT * FROM obs_from_catalog_page_001;
    INSERT INTO inventory SELECT * FROM obs_from_inventory_001;
    INSERT INTO catalog_returns SELECT * FROM obs_from_catalog_returns_001;
    INSERT INTO web_returns SELECT * FROM obs_from_web_returns_001;
    INSERT INTO web_sales SELECT * FROM obs_from_web_sales_001;
    INSERT INTO catalog_sales SELECT * FROM obs_from_catalog_sales_001;
    INSERT INTO store_sales SELECT * FROM obs_from_store_sales_001;
  5. 优化表性能。
    ANALYZE customer_address;
    ANALYZE customer_demographics;
    ANALYZE date_dim;
    ANALYZE warehouse;
    ANALYZE ship_mode;
    ANALYZE time_dim;
    ANALYZE reason;
    ANALYZE income_band;
    ANALYZE item;
    ANALYZE store;
    ANALYZE call_center;
    ANALYZE customer;
    ANALYZE web_site;
    ANALYZE store_returns;
    ANALYZE household_demographics;
    ANALYZE web_page;
    ANALYZE promotion;
    ANALYZE catalog_page;
    ANALYZE inventory;
    ANALYZE catalog_returns;
    ANALYZE web_returns;
    ANALYZE web_sales;
    ANALYZE catalog_sales;
    ANALYZE store_sales;
  6. 使用SELECT语句进行查询。更多语法信息,请参考SELECT
    --查询所有记录,且按字母升序排列。
    SELECT r_reason_desc FROM tpcds.reason ORDER BY r_reason_desc;
    
    --根据查询条件过滤,并对结果进行分组。
    SELECT r_reason_id, AVG(r_reason_sk) FROM tpcds.reason GROUP BY r_reason_id HAVING AVG(r_reason_sk) > 25;
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

跳转到云社区