步骤1:创建初始表并加装样例数据 本章节主要介绍创建初始表并加装样例数据的最佳实践。 创建一组不设置存储方式,无分布键、分布方式和压缩方式的表。然后,为这些表加载样例数据。 1.(可选)创建集群。 如果已经有可供使用的集群,则可跳过这一步。创建集群的操作,请按中的步骤操作。同时请使用SQL客户端连接到集群并测试连接。 本实践所使用的是8节点集群。也可以使用4节点集群进行测试。 2.使用最少的属性创建SS(StoreSales)测试表。 说明 如果SS表在当前数据库中已存在,需要先删除这些表。删除表使用DROP TABLE命令。如下示例表示删除表storesales。 DROP TABLE storesales; 考虑到本实践的目的,首次创建表时,没有设置存储方式、分布键、分布方式和压缩方式。 执行CREATE TABLE命令创建上图"TPCDS Store Sales ERDiagram"中的11张表。限于篇幅,这里仅附storesales的创建语法。请从附录初始表创建中拷贝所有建表语法进行创建。 CREATE TABLE storesales ( sssolddatesk integer , sssoldtimesk integer , ssitemsk integer not null, sscustomersk integer , sscdemosk integer , sshdemosk integer , ssaddrsk integer , ssstoresk integer , sspromosk integer , ssticketnumber bigint not null, ssquantity integer , sswholesalecost decimal(7,2) , sslistprice decimal(7,2) , sssalesprice decimal(7,2) , ssextdiscountamt decimal(7,2) , ssextsalesprice decimal(7,2) , ssextwholesalecost decimal(7,2) , ssextlistprice decimal(7,2) , ssexttax decimal(7,2) , sscouponamt decimal(7,2) , ssnetpaid decimal(7,2) , ssnetpaidinctax decimal(7,2) , ssnetprofit decimal(7,2) ) ; 3.为这些表加载样例数据。 OBS存储桶中提供了本次实践的样例数据。该存储桶向所有经过身份验证的云用户提供了读取权限。请按照下面的步骤加载这些样例数据: a.为每个表创建对应的外表。 DWS应用Postgres提供的外部数据封装器FDW(Foreign Data Wrapper)进行数据并行导入。因此需要先创建FDW表,又称外表。限于篇幅,此处仅给出“storesales”表对应的外表“obsfromstoresales001”的创建语法。请从附录外表创建拷贝其他外表的语法进行创建。 说明 l 注意,以下语句中的代表OBS桶名,仅支持部分区域。DWS集群不支持跨区域访问OBS桶数据。 l 外表字段需与即将注入数据的普通表字段保持一致。例如此处storesales表及其对应的外表obsfromstoresales001,他们的字段是一致的。 l 这些外表语法能够帮助您获取OBS存储桶中为本次实践所提供的样例数据。如果您需要加载其他样例数据,需进行SERVER gsmppserver OPTIONS的调整。 CREATE FOREIGN TABLE obsfromstoresales001 ( sssolddatesk integer , sssoldtimesk integer , ssitemsk integer not null, sscustomersk integer , sscdemosk integer , sshdemosk integer , ssaddrsk integer , ssstoresk integer , sspromosk integer , ssticketnumber bigint not null, ssquantity integer , sswholesalecost decimal(7,2) , sslistprice decimal(7,2) , sssalesprice decimal(7,2) , ssextdiscountamt decimal(7,2) , ssextsalesprice decimal(7,2) , ssextwholesalecost decimal(7,2) , ssextlistprice decimal(7,2) , ssexttax decimal(7,2) , sscouponamt decimal(7,2) , ssnetpaid decimal(7,2) , ssnetpaidinctax decimal(7,2) , ssnetprofit decimal(7,2) ) Configure OBS server information and data format details. SERVER gsmppserver OPTIONS ( LOCATION '/tpcds/storesales', FORMAT 'text', DELIMITER '', ENCODING 'utf8', NOESCAPING 'true', ACCESSKEY 'accesskeyvaluetobereplaced', SECRETACCESSKEY 'secretaccesskeyvaluetobereplaced', REJECTLIMIT 'unlimited', CHUNKSIZE '64' ) If create foreign table failed,record error message WITH errobsfromstoresales001; b.将创建外表语句中的参数ACCESSKEY和SECRETACCESSKEY替换为实际值,然后在客户端工具中执行替换后的语句创建外表。 ACCESSKEY和SECRETACCESSKEY的值,请参见“常见问题通用问题”中的“如何获取Access Key ID(AK)和 Secret Access Key(SK)”。 c.执行数据导入。 创建包含如下语句的insert.sql脚本文件,并执行.sql脚本文件。 timing on parallel on 4 INSERT INTO storesales SELECT FROM obsfromstoresales001; INSERT INTO datedim SELECT FROM obsfromdatedim001; INSERT INTO store SELECT FROM obsfromstore001; INSERT INTO item SELECT FROM obsfromitem001; INSERT INTO timedim SELECT FROM obsfromtimedim001; INSERT INTO promotion SELECT FROM obsfrompromotion001; INSERT INTO customerdemographics SELECT from obsfromcustomerdemographics001 ; INSERT INTO customeraddress SELECT FROM obsfromcustomeraddress001 ; INSERT INTO householddemographics SELECT FROM obsfromhouseholddemographics001; INSERT INTO customer SELECT FROM obsfromcustomer001; INSERT INTO incomeband SELECT FROM obsfromincomeband001; 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 d.计算所有11张表的总执行时间。该数字将作为加载时间记录在下一小节步骤步骤1中的基准表内。 e.执行以下命令,验证每个表是否都已正确加载并将行数记录到表中。 SELECT COUNT() FROM storesales; SELECT COUNT() FROM datedim; SELECT COUNT() FROM store; SELECT COUNT() FROM item; SELECT COUNT() FROM timedim; SELECT COUNT() FROM promotion; SELECT COUNT() FROM customerdemographics; SELECT COUNT() FROM customeraddress; SELECT COUNT() FROM householddemographics; SELECT COUNT() FROM customer; SELECT COUNT() FROM incomeband; 以下显示每个SS表的行数: 表名称 行数 StoreSales 287997024 DateDim 73049 Store 402 Item 204000 TimeDim 86400 Promotion 1000 CustomerDemographics 1920800 CustomerAddress 1000000 HouseholdDemographics 7200 Customer 1981703 IncomeBand 20 4.执行ANALYZE更新统计信息。 ANALYZE; 返回ANALYZE后,表示执行成功。 ANALYZE ANALYZE语句可收集数据库中与表内容相关的统计信息,统计结果存储在系统表PGSTATISTIC中。查询优化器会使用这些统计数据,以生成最有效的执行计划。 建议在执行了大批量插入/删除操作后,例行对表或全库执行ANALYZE语句更新统计信息。