【TPC-DS】trino+S3+hive+postgresql性能测试----生成500GB数据（六）

【注意事项】

1. 测试不同的数据前，需要清空之前的测试的旧数据
2. 连接postgressql，清空每个表的数据
3. 连接hive，清空每个表的数据

 

【总结】

• 生成数据：使用多个磁盘，共同生成500GB数据，生成数据放在后台执行，避免中断
• 写s3数据：每个磁盘，均修改mys3中脚本，由customer_4_4.dat、customer_3_4.dat、customer_2_4.dat、customer_1_4.dat均写入S3的customer

 

#生成数据存放的路径（自己定义），建议不要放在系统盘，可放在数据盘即可

/data1/trino/TpcdsData

 

第一步：生成数据

#后台运行生成数据

注：使用了4台服务器，每个服务的路径下均执行响应的命令

#125
cd /root/trino/tpcds-kit/tools
nohup ./dsdgen -SCALE 500GB -DIR /data1/trino/TpcdsData -parallel 4 -child 4 &
#124
cd /root/trino/tpcds-kit/tools
nohup ./dsdgen -SCALE 500GB -DIR /data1/trino/TpcdsData -parallel 4 -child 3 &
#123
cd /root/trino/tpcds-kit/tools
nohup ./dsdgen -SCALE 500GB -DIR /data1/trino/TpcdsData -parallel 4 -child 2 &
#122
cd /root/trino/tpcds-kit/tools
nohup ./dsdgen -SCALE 500GB -DIR /data1/trino/TpcdsData -parallel 4 -child 1 &

#每台服务器上均做软连接（注：因我自己喜欢把数据放在一起，所以做了软连接，根据需求而定，可以不做这一步均可）

ln -sv /data1/trino/TpcdsData /root/trino/tpcds-kit

查看生成数据是否成功，#125为例，其余的服务器查看方式一致

 

第二步：写数据（写入s3）

注意事项：根据每个磁盘的数据文件，修改表名catalog_returns_4_4，其余的不变

#125，mys3中的内容修改后如下，

aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/catalog_returns_4_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/catalog_returns/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/catalog_sales_4_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/catalog_sales/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/customer_address_4_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/customer_address/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/customer_4_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/customer/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/customer_demographics_4_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/customer_demographics/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/inventory_4_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/inventory/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/store_returns_4_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/store_returns/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/store_sales_4_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/store_sales/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/web_returns_4_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/web_returns/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/web_sales_4_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/web_sales/

#124

aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/catalog_returns_3_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/catalog_returns/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/catalog_sales_3_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/catalog_sales/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/customer_address_3_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/customer_address/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/customer_3_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/customer/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/customer_demographics_3_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/customer_demographics/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/inventory_3_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/inventory/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/store_returns_3_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/store_returns/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/store_sales_3_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/store_sales/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/web_returns_3_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/web_returns/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/web_sales_3_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/web_sales/

#123

aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/catalog_returns_2_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/catalog_returns/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/catalog_sales_2_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/catalog_sales/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/customer_address_2_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/customer_address/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/customer_2_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/customer/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/customer_demographics_2_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/customer_demographics/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/inventory_2_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/inventory/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/store_returns_2_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/store_returns/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/store_sales_2_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/store_sale/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/web_returns_2_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/web_returns/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/web_sales_2_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/web_sales/

#122

aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/catalog_returns_1_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/catalog_returns/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/catalog_sales_1_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/catalog_sales/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/customer_address_1_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/customer_address/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/customer_1_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/customer/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/customer_demographics_1_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/customer_demographics/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/inventory_1_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/inventory/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/store_returns_1_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/store_returns/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/store_sales_1_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/store_sale/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/web_returns_1_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/web_returns/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 cp /root/trino/tpcds-kit/TpcdsData/web_sales_1_4.dat s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/web_sales/

#查看125的执行数据是否生成

aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 ls s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/catalog_returns/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 ls s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/catalog_sales/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 ls s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/customer_address/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 ls s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/customer/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 ls s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/customer_demographics/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 ls s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/inventory/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 ls s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/store_returns/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 ls s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/store_sales/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 ls s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/web_returns/
aws --endpoint-url http://obs.cn-southwest-2.myhuaweicloud.com s3 ls s3://fastdata-tsdb/deepdevhwc/syw_postgreSQL_TPCds/web_sales/