数据仓库服务 GAUSSDB(DWS)-TPC-DS查询测试:命令生成方法

时间：2024-10-22 11:41:47

命令生成方法

TPC-DS标准99个SQL查询语句可用如下方法生成：

准备工作。生成TPC-DS查询语句前需要修改query_templates目录下的文件：

登录测试过程申请的E CS ，进入/data1/script/tpcds-kit/DSGen-software-code-3.2.0rc1/query_templates目录：

      
           cd /data1/script/tpcds-kit/DSGen-software-code-3.2.0rc1/query_templates

新建文件hwdws.tpl，内容为：

      
           define __LIMITA = "";
define __LIMITB = "";
define __LIMITC = "limit %d";
define _BEGIN = "-- begin query " + [_QUERY] + " in stream " + [_STREAM] + " using template " + [_TEMPLATE];
define _END = "-- end query " + [_QUERY] + " in stream " + [_STREAM] + " using template " + [_TEMPLATE];

因TPC-DS工具中SQL语句生成模板有语法错误，需修改query77.tpl，将135行的‘, coalesce(returns, 0) returns’改为‘, coalesce(returns, 0) as returns’。

执行以下命令生成查询语句：

    
         cd /data1/script/tpcds-kit/DSGen-software-code-3.2.0rc1/tools
./dsqgen  -input ../query_templates/templates.lst -directory ../query_templates/ -scale 1000 -dialect hwdws

执行后会生成query_0.sql文件，里面放着99个标准SQL语句，需要手动去切分成99个文件。

生成的标准查询中如下日期函数语法在 GaussDB (DWS)暂不支持，需要手动进行修改：

    
     
       
       
         Q5:  and (cast('2001-08-19' as date) +  14 days)
修改为
and (cast('2001-08-19' as date) +  14)

Q12:and (cast('1999-02-28' as date) + 30 days)
修改为
and (cast('1999-02-28' as date) + 30)

Q16:(cast('1999-4-01' as date) + 60 days)
修改为
(cast('1999-4-01' as date) + 60)

Q20:and (cast('1998-05-05' as date) + 30 days)
修改为
and (cast('1998-05-05' as date) + 30)

Q21:and d_date between (cast ('2000-05-19' as date) - 30 days)
修改为
and d_date between (cast ('2000-05-19' as date) - 30)

and (cast ('2000-05-19' as date) + 30 days)
修改为
and (cast ('2000-05-19' as date) + 30)

Q32:(cast('1999-02-22' as date) + 90 days)
修改为
(cast('1999-02-22' as date) + 90)

Q37:and d_date between cast('1998-04-29' as date) and (cast('1998-04-29' as date) +  60 days)
修改为
and d_date between cast('1998-04-29' as date) and (cast('1998-04-29' as date) +  60)

Q40:and d_date between (cast ('2002-05-10' as date) - 30 days)
修改为
and d_date between (cast ('2002-05-10' as date) - 30)

and (cast ('2002-05-10' as date) + 30 days)
修改为
and (cast ('2002-05-10' as date) + 30)

Q77:and (cast('1999-08-29' as date) +  30 days)
修改为
and (cast('1999-08-29' as date) +  30)

Q80:and (cast('2002-08-04' as date) +  30 days)
修改为
and (cast('2002-08-04' as date) +  30)

Q82:and d_date between cast('1998-01-18' as date) and (cast('1998-01-18' as date) +  60 days)
修改为
and d_date between cast('1998-01-18' as date) and (cast('1998-01-18' as date) +  60)

Q92:(cast('2001-01-26' as date) + 90 days)
修改为
(cast('2001-01-26' as date) + 90)

Q94:(cast('1999-5-01' as date) + 60 days)
修改为
(cast('1999-5-01' as date) + 60)

Q95:(cast('1999-4-01' as date) + 60 days)
修改为
(cast('1999-4-01' as date) + 60)

Q98:and (cast('2002-04-01' as date) + 30 days)
修改为
and (cast('2002-04-01' as date) + 30)

        

      

    
   