数据库导入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