Is there a support for dialect mysql in tpcds query generation tool?

[krdeepakio]

Thanks you for helping so many people with this repository. It would have been impossible to get this to work by using the source from official site.

I have the data ready in the database. During query generation I did not find any option for mysql as a dialect. I have tried ansi and sqlserver but the queries generated there are not valid mysql queries.

Any help in this regard will be highly appreciated. Thank you.

[gregrahn]

Use the netezza.tpl. MySQL and Netezza both use "limit N" to limit the rows returned. Other adjustments may be necessary as well. Also note, MySQL does not support window functions, so you will have to use the version from the query_variants directory for those queries.

[krdeepakio]

Used the netezza variant, also took help from https://github.com/cloudera/impala-tpcds-kit
I am using this sed command to convert blanks to \N, so that mysql creates that column with null value. It works fine.
sed -i -e 's/^|/\N|/' -e 's/||/|\N|/g' -e 's/||/|\N|/g' -e 's/|$/|/' *.dat

Have you looked into the data generation code also, so that we can change it to produce \N instead of blank.

[gregrahn]

It's probably not difficult to set empty fields to \N, but it's also pretty lame that MySQL still has no option to load missing fields as NULL. There's been a feature request for this since 2006 here. An alternative is also to use the NULLIF(@colname,'') when loading.

I added #14 for this to track it.

[krdeepakio]

Thank you. I am importing 100GB of data in mysql. The server is 16 core xlarge ec2 instance with 64G RAM. I created the schema (tpcds.sql) and then loaded the data. It took around 7 hours. After that I am running tpcds_ri.sql. This has been running for last 10 hours and still only 20% complete. I must be doing something wrong.

My innodb settings are as follows.
innodb_buffer_pool_size - 2G
innodb_log_file_size - 512M
innodb_log_buffer_size - 1M

My guess is that buffer log size or log file size are culprit. If you have tested with 100G data, can you share your numbers.

[krdeepakio]

Changed the settings to 32G, 1G, 64M

Alter table add constraint foreign key still taking very long.

[krdeepakio]

Another request which may be simple to implement. Can we add the support for 10 GB data? It will be really helpful. Thank you.

[gregrahn]

A few things:

• Asking for MySQL tuning help is outside the scope of this issues for this project. Sorry.
• You can generate any size data you want, however
• Only certain scale factors are valid for official results. See Clause 3.1 in the TPC-DS Specification
• One topic per issue, so closing this one.