postgresql使用（一）：TPC-H tools生成数据集并导入至postgre的数据库

本专题

1. postgresql使用（一）：TPC-H tools生成数据集并导入至postgre的数据库
2. postgresql使用（二）：在TPC-H的数据库上pgbench 压力测试
3. postgresql使用（三）：收集Postgresql数据库统计信息

 

一、安装PostgreSQL 9.5

参考：https://www.jianshu.com/p/dda94c4ffd52

同时记录一些自己遇到的坑和解决方法。

Ubuntu PostgreSQL安装和配置   参考：https://www.cnblogs.com/Siegel/p/6917213.html

1. 安装

sudo apt-get update
apt install postgresql

安装完成

编辑config /etc/postgresql/9.5/main/postgresql.conf

将下面 listen_addresses = 'localhost'注释去掉并改为  listen_addresses = '*'
# - Connection Settings -
#listen_addresses = 'localhost'         
# what IP address(es) to listen on;               
# comma-separated list of addresses;    
# defaults to 'localhost'; use '*' for all
...

将下面password_encryption = on 注释打开
#password_encryption = on

 

切换用户

su - postgres

遇到问题：

su：认证失败

解决方法：https://blog.csdn.net/xiaoxiali/article/details/17096591

按照上面的链接修改后，先 su 再 su - postgres，切换成功：

 

2、psql命令

安装完后会有PostgreSQL的客户端psql，通过 sudo -u postgres psql 进入，提示符变成： postgres=#  

在这里可用执行SQL语句和psql的基本命令。可用的基本命令如下：

\password：设置密码
\q：退出
\h：查看SQL命令的解释，比如\h select。
\?：查看psql命令列表。
\l：列出所有数据库。
\c [database_name]：连接其他数据库。
\d：列出当前数据库的所有表格。
\d [table_name]：列出某一张表格的结构。
\du：列出所有用户。
\e：打开文本编辑器。
\conninfo：列出当前数据库和连接的信息。

psql

创建超级权限用户test，并设置密码0000（输入时是看不见的）。创建数据库testdb,并授权给用户test

sudo -u postgres createuser --superuser test;
sudo -u postgres psql
\password test;
0000
\q
sudo -u postgres createdb -O test testdb;

 

启动用户test下的testdb

psql -p 5432 -U test -d testdb

遇到问题：psql: FATAL: Peer authentication failed for user "test"

解决方法：

sudo gedit /etc/postgresql/9.5/main/pg_hba.conf

将

 # Database administrative login by Unix domain socket

local     all      postgres        peer

改为

# Database administrative login by Unix domain socket

local     all     postgres         trust

保存后执行下面的命令重新加载配置文件:

sudo /etc/init.d/postgresql reload

psql -U test -d testdb

出现下图，成功：

 

二、安装TPC-H工具生成数据集  

参考：

https://blog.csdn.net/zhenxue_xu/article/details/78306683

https://blog.csdn.net/tom_and_rocky/article/details/78814912

TPC-H下载地址：http://www.tpc.org/tpc_documents_current_versions/current_specifications.asp

嫌麻烦并且手头有积分，可以去CSDN下载区下载：https://download.csdn.net/download/iteapoy/10990998

填表格，会发送临时下载链接到邮箱，建议用gmail

 

填了邮箱后在邮箱里打开临时链接，开始下载

 

解压TPCH-tools 2.18.0_rc2（这里是我在win10上解压后的截图）

放到ubuntu中，在dbgen目录下找到并更改makefile.suite 生成dbgen

 

由于TPCH数据库参数没有PostgreSQL数据库选项，需要自己增加PG数据的脚本，在dbgen目录下更改tpcd.h文件

保存修改。在终端中cd到dbgen目录下，执行命令

make -f makefile.suite

执行成功后在dbgen目录下生成dbgen和qgen文件

运行dbgen生成.tbl数据。

在dbgen目录下执行

 ./dbgen -s 1 -f

• -s 1 表示生成1G数据
• -f 覆盖之前产生的文件

执行成功后会在dbgen目录下生成八个.tbl文件，可通过下列命令查看（在dbgen目录下）

 ls *.tbl 

看到产生八个tbl文件，也就是生成的数据集

在postgresql中建立tpch数据库，并创建表，相关表的创建语句可以从dss.ddl中复制

在dbgen文件夹下新建表 mysql.sql，把dss.ddl中的东西全部复制进去。

在用户test下的testdb数据库中建表

psql -p 5432 -U test -d testdb -f ./mysql.sql

参考：postgreSQL 命令行方式创建数据库及表

https://blog.csdn.net/hehong_78/article/details/6091011

 

导入数据

生成的tbl数据每一行的末尾会有一个“|”，导致PG数据库读取时报错，需要将最后一个“|”去掉，在dbgen目录下找到print.c, 注释145和147行，如下所示

 

在dbgen下新建/tbl文件夹，在dbgen文件夹中执行批处理

for i in `ls *.tbl`
do
 name="tbl/$i"
 echo $name
 `touch $name`
 `chmod 777 $name`
 sed 's/|$//' $i >> $name;
done

导入数据

psql -p 5432 -U test -d testdb
copy region from '/mnt/hgfs/share/database/2.18.0_rc2/dbgen/tbl/region.tbl' with delimiter as '|' NULL '';
copy nation from '/mnt/hgfs/share/database/2.18.0_rc2/dbgen/tbl/nation.tbl' with delimiter as '|' NULL '';
copy partsupp from '/mnt/hgfs/share/database/2.18.0_rc2/dbgen/tbl/partsupp.tbl' with delimiter as '|' NULL '';
copy customer from '/mnt/hgfs/share/database/2.18.0_rc2/dbgen/tbl/customer.tbl' with delimiter as '|' NULL '';
copy lineitem from '/mnt/hgfs/share/database/2.18.0_rc2/dbgen/tbl/lineitem.tbl' with delimiter as '|' NULL '';
copy orders from '/mnt/hgfs/share/database/2.18.0_rc2/dbgen/tbl/orders.tbl' with delimiter as '|' NULL '';
copy part from '/mnt/hgfs/share/database/2.18.0_rc2/dbgen/tbl/part.tbl' with delimiter as '|' NULL '';
copy supplier from '/mnt/hgfs/share/database/2.18.0_rc2/dbgen/tbl/supplier.tbl' with delimiter as '|' NULL '';

给各表加约束条件

数据表的约束条件存放在dss.ri 文件中，复制并做相应更改在数据库中执行生成相关约束。

-- For table REGION
ALTER TABLE REGION
ADD PRIMARY KEY (R_REGIONKEY);

-- For table NATION
ALTER TABLE NATION
ADD PRIMARY KEY (N_NATIONKEY);

ALTER TABLE NATION
ADD FOREIGN KEY (N_REGIONKEY) references REGION;

COMMIT WORK;

-- For table PART
ALTER TABLE PART
ADD PRIMARY KEY (P_PARTKEY);

COMMIT WORK;

-- For table SUPPLIER
ALTER TABLE SUPPLIER
ADD PRIMARY KEY (S_SUPPKEY);

ALTER TABLE SUPPLIER
ADD FOREIGN KEY (S_NATIONKEY) references NATION;

COMMIT WORK;

-- For table PARTSUPP
ALTER TABLE PARTSUPP
ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY);

COMMIT WORK;

-- For table CUSTOMER
ALTER TABLE CUSTOMER
ADD PRIMARY KEY (C_CUSTKEY);

ALTER TABLE CUSTOMER
ADD FOREIGN KEY (C_NATIONKEY) references NATION;

COMMIT WORK;

-- For table LINEITEM
ALTER TABLE LINEITEM
ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);

COMMIT WORK;

-- For table ORDERS
ALTER TABLE ORDERS
ADD PRIMARY KEY (O_ORDERKEY);

COMMIT WORK;

-- For table PARTSUPP
ALTER TABLE PARTSUPP
ADD FOREIGN KEY (PS_SUPPKEY) references SUPPLIER;

COMMIT WORK;

ALTER TABLE PARTSUPP
ADD FOREIGN KEY (PS_PARTKEY) references PART;

COMMIT WORK;

-- For table ORDERS
ALTER TABLE ORDERS
ADD FOREIGN KEY (O_CUSTKEY) references CUSTOMER;

COMMIT WORK;

-- For table LINEITEM
ALTER TABLE LINEITEM
ADD FOREIGN KEY (L_ORDERKEY)  references ORDERS;

COMMIT WORK;

ALTER TABLE LINEITEM
ADD FOREIGN KEY (L_PARTKEY,L_SUPPKEY) references PARTSUPP;

COMMIT WORK;

 

生成查询语句

复制qgen 和dists.dss 到queries ,cd到queries目录下执行

./qgen -d 1 >d1.sql

-d表示默认参数，1表示按照模板一生成sql语句

如下，生成d1.sql查询

批量生成.sql文件

for i  in {1..22}
do
name="d$i.sql"
echo $name
./qgen -d $i >$name
done

执行查询

psql -p 5432 -U test -d testdb -f ./d1.sql

生成查询结果

将查询结果导出到文件夹中

\o /mnt/hgfs/share/database/2.18.0_rc2/dbgen/queries/out/d1.out
\i /mnt/hgfs/share/database/2.18.0_rc2/dbgen/queries/d1.sql
\o

最后，附录TPC-H（二）：22个SQL语句说明（基于TPC-H2.17.3版本）

Q01 统计查询

Q02 WHERE条件中，使用子查询(=)

Q03 多表关联统计查询，并统计(SUM)

Q04 WHERE条件中，使用子查询(EXISTS)，并统计(COUNT)

Q05 多表关联查询(=)，并统计(SUM)

Q06 条件(BETWEEN AND)查询，并统计(SUM)

Q07 带有FROM子查询，从结果集中统计(SUM)

Q08 带有FROM多表子查询，从结果集中的查询列上带有逻辑判断(WHEN THEN ELSE)的统计(SUM)

Q09 带有FROM多表子查询，查询表中使用函数(EXTRACT)，从结果集中统计(SUM)

Q10 多表条件查询(>=, <)，并统计(SUM)

Q11 在GROUP BY中使用比较条件(HAVING >)，比较值从子查询中查出

Q12 带有逻辑判断(WHEN AND/ WHEN OR)的查询，并统计(SUM)

Q13 带有FROM子查询，子查询中使用外联结

Q14 使用逻辑判断(WHEN ELSE)的查询

Q15 使用视图和表关联查询

Q16 在WHERE子句中使用子查询，使用IN/ NOT IN判断条件，并统计(COUNT)

Q17 在WHERE子句中使用子查询，使用<比较，使用了AVG函数

Q18 在WHERE子句中使用IN条件从子查询结果中比较

Q19 多条件比较查询

Q20 WHERE条件子查询(三层)

Q21 在WHERE条件中使用子查询，使用EXISTS和NOT EXISTS判断

Q22 在WHERE条件中使用判断子查询、IN、NOT EXISTS，并统计(SUM、COUNT)查询结果