文档首页 > > 开发指南> 教程:调优表设计> 步骤1:创建初始表并加装样例数据

步骤1:创建初始表并加装样例数据

分享
更新时间: 2019/06/24 GMT+08:00

创建一组不设置存储方式,无分布键、分布方式和压缩方式的表。然后,为这些表加载样例数据。

  1. (可选)创建集群。

    如果已经有可供使用的集群,则可跳过这一步。创建集群的操作,请按《数据仓库服务快速入门》中的步骤操作。同时请参考《数据仓库服务快速入门》中的办法使用SQL 客户端连接到集群并测试连接。

    本教程所使用的是8节点集群。也可以使用4节点集群进行测试。

  2. 使用最少的属性创建SS(Store_Sales)测试表。

    说明:

    如果SS表在当前数据库中已存在,需要先删除这些表。删除表使用DROP TABLE命令。如下示例表示删除表store_sales。

    DROP TABLE store_sales;

    考虑到本教程的目的,首次创建表时,没有设置存储方式、分布键、分布方式和压缩方式。

    执行CREATE TABLE命令创建图3中的11张表。限于篇幅,这里仅附store_sales的创建语法。请从附录初始表创建中拷贝所有建表语法进行创建。

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

  3. 为这些表加载样例数据。

    OBS存储桶中提供了本教程的样例数据。该存储桶向所有经过身份验证的云用户提供了读取权限。请按照下面的步骤加载这些样例数据:

    1. 为每个表创建对应的外表。

      DWS应用Postgres提供的外部数据封装器FDW(Foreign Data Wrapper)进行数据并行导入。因此需要先创建FDW表,又称外表。限于篇幅,此处仅给出“store_sales”表对应的外表“obs_from_store_sales_001”的创建语法。请从附录外表创建拷贝其他外表的语法进行创建。

      • 外表字段需与即将注入数据的普通表字段保持一致。例如此处store_sales表及其对应的外表obs_from_store_sales_001,他们的字段是一致的。
      • 这些外表语法能够帮助您获取OBS存储桶中为本教程所提供的样例数据。如果您需要加载其他样例数据,需进行SERVER gsmpp_server OPTIONS的调整。具体可参考”关于OBS并行导入”。
      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)                  
      )
      -- Configure OBS server information and data format details.
      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'
      )
      -- If create foreign table failed,record error message
      WITH err_obs_from_store_sales_001;
    2. 将创建外表语句中的参数ACCESS_KEY和SECRET_ACCESS_KEY替换为实际值,然后在客户端工具中执行替换后的语句创建外表。

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

    3. 执行数据导入。
      创建包含如下语句的insert.sql脚本文件,并执行.sql脚本文件。
      \timing on
      \parallel on 4
      INSERT INTO store_sales SELECT * FROM obs_from_store_sales_001;
      INSERT INTO date_dim SELECT * FROM obs_from_date_dim_001;
      INSERT INTO store SELECT * FROM obs_from_store_001;
      INSERT INTO item SELECT * FROM obs_from_item_001;
      INSERT INTO time_dim SELECT * FROM obs_from_time_dim_001;
      INSERT INTO promotion SELECT * FROM obs_from_promotion_001;
      INSERT INTO customer_demographics SELECT * from obs_from_customer_demographics_001 ;
      INSERT INTO customer_address SELECT * FROM obs_from_customer_address_001 ;
      INSERT INTO household_demographics SELECT * FROM obs_from_household_demographics_001;
      INSERT INTO customer SELECT * FROM obs_from_customer_001;
      INSERT INTO income_band SELECT * FROM obs_from_income_band_001;
      \parallel off

      结果应该类似如下:

      SET
      Timing is on.
      SET
      Time: 2.831 ms
      Parallel is on with scale 4.
      Parallel is off.
      INSERT 0 402
      Time: 1820.909 ms
      INSERT 0 73049
      Time: 2715.275 ms
      INSERT 0 86400
      Time: 2377.056 ms
      INSERT 0 1000
      Time: 4037.155 ms
      INSERT 0 204000
      Time: 7124.190 ms
      INSERT 0 7200
      Time: 2227.776 ms
      INSERT 0 1920800
      Time: 8672.647 ms
      INSERT 0 20
      Time: 2273.501 ms
      INSERT 0 1000000
      Time: 11430.991 ms
      INSERT 0 1981703
      Time: 20270.750 ms
      INSERT 0 287997024
      Time: 341395.680 ms
      total time: 341584  ms
    4. 计算所有11张表的总执行时间。该数字将作为加载时间记录在下一小节步骤1中的基准表内。
    5. 执行以下命令,验证每个表是否都已正确加载并将行数记录到表中。
      SELECT COUNT(*) FROM store_sales;
      SELECT COUNT(*) FROM date_dim;
      SELECT COUNT(*) FROM store;
      SELECT COUNT(*) FROM item;
      SELECT COUNT(*) FROM time_dim;
      SELECT COUNT(*) FROM promotion;
      SELECT COUNT(*) FROM customer_demographics;
      SELECT COUNT(*) FROM customer_address;
      SELECT COUNT(*) FROM household_demographics;
      SELECT COUNT(*) FROM customer;
      SELECT COUNT(*) FROM income_band;

      以下显示每个SS表的行数:

      表名称

      行数

      Store_Sales

      287997024

      Date_Dim

      73049

      Store

      402

      Item

      204000

      Time_Dim

      86400

      Promotion

      1000

      Customer_Demographics

      1920800

      Customer_Address

      1000000

      Household_Demographics

      7200

      Customer

      1981703

      Income_Band

      20

  4. 执行ANALYZE更新统计信息。

    ANALYZE;

    返回ANALYZE后,表示执行成功。

    ANALYZE

    ANALYZE语句可收集数据库中与表内容相关的统计信息,统计结果存储在系统表PG_STATISTIC中。查询优化器会使用这些统计数据,以生成最有效的执行计划。

    建议在执行了大批量插入/删除操作后,例行对表或全库执行ANALYZE语句更新统计信息。

分享:

    相关文档

    相关产品

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

提交成功!

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

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

*必选

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

字符长度不能超过200

提交反馈 取消

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

跳转到云社区