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

时间：2025-02-12 14:55:03

命令生成方法

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)