pg，mysql，clickhouse导入性能测试

数据库导入csv文件测试及性能测试，1348189条数据，配置：4核(1.8MHz)8G，m2固态

clickhouse22

# ck建表
create table dwv_order(
    order_id            Int64,
	product_id          Int32,
	city_id             Int32,
	district            String,
	county				Int32,
	type				Int32,
	combo_type			Int32,
	traffic_type		Int32,
	passenger_count		Int32,
	driver_product_id	Int32,
	start_dest_distance Int32,
	arrive_time			DateTime64,
	departure_time		DateTime64,
	pre_total_fee		Int32,
	normal_time			Int32,
	bubble_trace_id		String,
	product_1level		Int32,
	dest_lng			Float64,
	dest_lat			Float64,
	starting_lng		Float64,
	starting_lat		Float64,
	year				String,
	month				String,
	day					String
) engine =MergeTree()
partition by toYYYYMMDD(departure_time)
order by (order_id,departure_time,bubble_trace_id,county);

# ck导入
dos2unix order.txt
sed -e 's/NULL/\\N/g'  order.txt > order.tsv
clickhouse-client    --query="insert into dwv_order FORMAT TSV" < /opt/order.tsv

#import.sh
echo "$(date "+[%Y-%m-%d %H:%M:%S.%3N]") >> start"
clickhouse-client    --query="insert into dwv_order FORMAT TSV" < /opt/order.tsv
echo "$(date "+[%Y-%m-%d %H:%M:%S.%3N]") >> end"

mysql8

docker run     -p 3306:3306     -e MYSQL_ROOT_PASSWORD=xdclass.net168     -v /home/data/mysql/data:/var/lib/mysql:rw     -v /etc/localtime:/etc/localtime:ro     --name xdclass_mysql     --restart=always     -d mysql:8.0
docker run --name mysql5 -e MYSQL_ROOT_PASSWORD=root  -p 13306:3306 -d mysql:5.7
#mysql建表
create table test_data.dwv_order
(
	order_id bigint null,
	product_id int null,
	city_id int null,
	district varchar(50) charset utf8 null,
	county int null,
	type int null,
	combo_type int null,
	traffic_type int null,
	passenger_count int null,
	driver_product_id int null,
	start_dest_distance int null,
	arrive_time datetime null,
	departure_time datetime null,
	pre_total_fee int null,
	normal_time int null,
	bubble_trace_id varchar(50) charset utf8 null,
	product_1level int null,
	dest_lng double null,
	dest_lat double null,
	starting_lng double null,
	starting_lat double null,
	year varchar(5) charset utf8 null,
	month varchar(5) charset utf8 null,
	day varchar(5) charset utf8 null
);
create index dwv_order_bubble_trace_id_index
	on test_data.dwv_order (bubble_trace_id);
create index dwv_order_departure_time_index
	on test_data.dwv_order (departure_time);
create index dwv_order_order_id_index
	on test_data.dwv_order (order_id);


    
#mysql导入
dos2unix order.txt
cp order.txt order.tsv
#设置允许本地导入
SET GLOBAL local_infile=1;
mysql -uroot -p --local-infile=1
LOAD DATA LOCAL INFILE '/opt/order.tsv' INTO TABLE test_data.dwv_order;

相关命令

LOAD DATA INFILE '/path/to/test.csv'
INTO TABLE testing
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

postgresql13

 docker run --name pg13 -e POSTGRES_PASSWORD=postgres -p 5432:5432 -v /mnt/docker/opt:/opt -v /mnt/docker/postgres/data:/var/lib/postgresql/data -d  postgres:13.12
# pg建表
create table dwv_order
(
	order_id bigint,
	product_id integer,
	city_id integer,
	district varchar(50),
	county integer,
	type integer,
	combo_type integer,
	traffic_type integer,
	passenger_count integer,
	driver_product_id integer,
	start_dest_distance integer,
	arrive_time timestamp,
	departure_time timestamp,
	pre_total_fee integer,
	normal_time integer,
	bubble_trace_id varchar(50),
	product_1level integer,
	dest_lng double precision,
	dest_lat double precision,
	starting_lng double precision,
	starting_lat double precision,
	year varchar(5),
	month varchar(5),
	day varchar(5)
);
alter table dwv_order owner to postgres;
create index dwv_order_order_id_index
	on dwv_order (order_id);
create index dwv_order_departure_time_index
	on dwv_order (departure_time);
create index dwv_order_bubble_trace_id_index
	on dwv_order (bubble_trace_id);



#导入
sed -e 's/0000-00-00 00:00:00/NULL/g'  order.txt > order0.tsv
sed "s/^/1/g" order0.tsv > order1.tsv
sed "s/^/2/g" order0.tsv > order2.tsv
sed "s/^/3/g" order0.tsv > order3.tsv
sed "s/^/4/g" order0.tsv > order4.tsv
sed "s/^/5/g" order0.tsv > order5.tsv
sed "s/^/6/g" order0.tsv > order6.tsv
sed "s/^/7/g" order0.tsv > order7.tsv
sed "s/^/8/g" order0.tsv > order8.tsv
sed "s/^/9/g" order0.tsv > order9.tsv

cat order0.tsv order1.tsv order2.tsv order3.tsv order4.tsv order5.tsv order6.tsv order7.tsv order8.tsv order9.tsv > order.tsv

cat>>import.sh<<EOF
echo "$(date "+[%Y-%m-%d %H:%M:%S.%3N]") >> start"
psql -U postgres -c " COPY dwv_order FROM '/opt/order.tsv' with NULL as 'NULL' "
echo "$(date "+[%Y-%m-%d %H:%M:%S.%3N]") >> end"
EOF