SOCSEC

awk分类处理5亿MySQL数据

先说需求:需要处理一个5亿行数据的MySQL表,需要将其根据来源(source)字段分为不同的字表。

尝试一 使用MySQL语句进行查询插入

思考了下可根据MySQL语句进行查询插入,先是查询总共有多少种来源:

1
select distinct source from all_table;

根据不同的来源分别插入不同的字表。

1
insert sub_table1 select * from all_table where source='source1';

缺点:这种方法 消耗时间太长。5亿数据每次查询都很费时间。抛弃!

尝试二 对总表进行分区、分表

由于查询时间太长,于是思考对总表进行分区或者分表。由于不太懂,于是网上查询了下分区、分表。发现分表不是对已存在的表进行分表的,而是在存储时候进行分表存储。这就不适用我们的情况,果断放弃。

分区是将大表,分成若干个小的块,可以优化查询速度。于是采用分区尝试。具体参考 MySQL手册。

尝试结果仍不能满足需求,速度还是很慢。抛弃!

尝试三 使用awk逐行操作。

以上方法尝试不行之后,考虑自己写程序处理,考虑到在数据库中处理需要不断的进行查询,浪费时间。于是将数据表导出为csv格式。导出之后总共有50多G,于是考虑使用awk进行逐行操作。

将查询的source来源的类别存储在一个csv文件:source.scv,大表为:table.csv
使用awk脚本逐行分类写到根据source命名的.sql 文件中:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
#!/usr/bin/awk -f
BEGIN{
FS="\",\""
RS="\r\n"
print "start !!!!!"
print "----------------"
count=0
all=0
}
{
if (NR==FNR){
a=$1
split(a,item,"\n")
}else{
all=all+1
for (i = 1; i < 132; i = i + 1){
split(item[i],b,",")
if (match($10,b[1])!=0){
split($1,c,"\"")
username=c[2]
split($13,d,"\"")
LID=d[1]
printf "('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s'),\n",username,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,LID >> "./sql/"b[2]".sql"
count=count+1
}
}
}
}
END{
print "end"
print "all line",all
print "insert line",count
}

一般的i5 台式机 Ubuntu系统, 2个多小时就搞定了,之后是使用source命令导入到MySQL