查询计算大文件的桌面程序工具

【摘要】
数据分析时,难免会遇到内存装不下的大数据文件,如何对大文件进行查询计算?本文分析比较了几种常用办法的优缺点,esProc SPL 是其中最适合数据分析师用于处理大文件的脚本。请点击查询计算大文件的桌面程序工具了解详情

 

什么是大文件?大文件是指大到计算机内存不足以一次性读入的文件。这种情况,直接使用桌面数据工具(比如 Excel)都无能为力了,常常需要编写程序来处理。而即使是写程序,也必须是分批读入进行计算处理,最后再按照不同的计算类型对分批处理结果进行恰当的汇总处理,比小文件数据的处理要很杂很多。大文件的种类也有多种,例如文本文件、Excel文件、XML文件、JSON文件、HTTP文件……等等,其中以文本 (txt 或 csv) 最为常见。

可以用于处理大文件的程序语言一般有以下几种:
1、 常规高级编程语言,比如 Java、C/C++、C#、Basic 等
2、  将文件数据导入到数据库后用SQL来处理
3、  Python
4、  esProc SPL

本文以文本文件举例,依次介绍以上几种程序方法进行大文件计算的特点。其它类型文件数据,除了读入数据的方式不同,读入后的处理思路就都与文本文件相似。

文中要用到的订单文件orders.txt中有5个列:orderkeyorderdatestatequantityamount,列数据间以TAB分隔,文件中第一行是列名,总共有1000万行数据。如下:

..

 

一、  高级语言(以Java为例)

用高级语言编程来计算,计算过程怎么编写,与具体的计算类型有关,不同类型的计算需要不同的计算过程。我们先来看看最简单的合计运算,例如求订单表orders.txt中订单金额amount的总和,用Java写出来是这样的:

BufferedReader br = new BufferedReader(new InputStreamReader( new FileInputStream("orders.txt") ) );
String[] fieldNames = br.readLine().split("\t");
double sumAmount = 0;
String line = null;
while(( line = br.readLine() )!= null) {
    String[] values = line.split("\t");
    float amount = Float.parseFloat(values[4] );  //假定已知第 5 列是 amount
    sumAmount += amount;
}

这段程序是一次读一行数据,访问文件时间太多,运行速度慢。想要运行快一些,需要一次读入大块数据(比如1万行),然后再用代码把数据拆成行来处理,过程会麻烦很多。

这是最简单的计算了,没有数据过滤和分组、排序等要求。其它求平均值、最大值与最小值的计算,跟这差不多。

如果要做分组汇总,代码就会麻烦得多。比如按州state分组后求各州的订单金额总和,计算思路是这样的:把各个分组保存起来,然后一行行地读各行中的state值,与保存的组比较,找到了则将本行订单金额加到组上,没找到则新加入一个组。最后直到所有行都处理完。如果按多个字段分组求多个统计值,比如按日期和州分组求订单金额总和、最大订单金额,程序的复杂度就会增加很多。

排序就更麻烦,还需要中间缓存文件。例如要按订单金额从大到小排序,因内存不足,不能读入所有数据来排序,计算思路是这样的:先读入5000(读多少行合适要根据内存而定)数据,排序后存到一个临时文件,再读入5000行排序后存到另一个临时文件……直到所有数据处理完,最后对这些临时文件进行有序归并——读出每个临时文件的第一行,找出应该排在最前面的那一行,写入到结果文件,然后从那个临时文件中再读出一行,继续比较找出最前面的一行写入结果文件。按此方法不断进行,直到所有数据行都写入结果文件。

用高级语言完成大文件的处理确实是相当繁琐的,对于非专业的程序人员,这几乎是不可行的。

 

二、  利用数据库

数据库内置了许多计算算法,对数据的查询计算功能比较完善,性能也比较好,因此可以考虑将文件型数据导入到数据库,生成数据库表,再使用SQL来进行数据查询计算。

这个办法麻烦的地方就是要将文件数据导入数据库,在导入数据之前,先要创建数据表结构,指定每个列的数据类型,例如创建订单表的SQL如下:

CREATE TABLE orders ( orderkey INTEGER NOT NULL,
       orderdate DATE NOT NULL,
       state  CHAR(20) NOT NULL, 
       quantity  INTEGER NOT NULL, 
       amount  DECIMAL(15,2) NOT NULL,
       PRIMARY KEY(orderkey)
);

如果换个其它结构的数据文件,那么需要另写一条建表的SQL。这里特别需要指定数据类型,否则数据库无法接受这些数据,而这是许多非专业程序员很不熟悉的地方。

对于导入过程,数据库一般都提供了直接导入文本文件的工具,而其它文件则不能直接导入,需要先转换为文本文件。Excel文件还可以直接另存为文本,而对于XML文件、JSON文件、HTTP文件等则又需要编写程序将它们转化为文本文件,或者编写程序先读入文件数据,生成一条SQL语句将数据写入数据库表中,无论如何都是非常繁琐的事情。

 

数据存入到数据库表以后,查询计算确实就非常简单了,分组、排序都挺容易,示例如下:

1、  求订单金额总和

select sum(amount) from orders;

2、  按州分组求各州订单金额总和

select state, sum(amount) from orders group by state;

3、  按订单金额排序

select * from orders order by amount;

利用数据库能很方便地查询计算较大数据量,但把大文件导入数据库却很繁琐而且有一定的专业技能要求。相对于高级语言的可行性大幅提高,但仍不够好。

三、  Python

Python也没有提供直接针对大文件的处理语法,其实现思路和高级语言类似,如前面的计算订单金额总和写出来大概是:

sumAmount=0
with open("orders.txt",'r') as f:
    while True:
        line = f.readline()
        if not line:
            break
        sumAmount += float(line.split("\t")[4])
print(sumAmount)

对于分组排序这类复杂一些的运算,如果实现前面说过的思路,用Python也非常麻烦,并不比java简单多少。但Python有个pandas包,封装了不少结构化数据的处理函数。如果是可读入内存的小文件,它可以很简单地处理。可惜的是,pandas没有针对大文件提供直接分批处理的方法,还是要自己写。使用pandas的复杂度比直接硬写要小很多,但仍然要实现前面讨论过的思路。

分组运算写起来太麻烦,我们还是把上面的求和运算基于pandas写出来感受一下。

import pandas as pd
chunk_data = pd.read_csv("orders.txt",sep="\t",header=None,chunksize=100000)
sumAmount=0
for chunk in chunk_data:
    sumAmount+=chunk[4].sum()
print(sumAmount)

使用pandas后可以把文本看成一行行有结构的数据,不再需要自己拆分。

 

Python处理小文件没有大问题,但对于大文件未提供有效支持。和高级语言相比,减轻的工作量很有限,可用程度不高,还不如数据库。

另外,Python是个解释执行语言,执行速度远远低于高级语言,处理大文件时感受会更明显。

 

四、  esProc  SPL

esProc是专业的数据处理工具,与数据库一样内置了各种查询计算算法,可以直接使用文本、ExcelXmlJson等文件数据计算,不需要导入数据的过程。

esProc提供了游标,可以分批读入数据再计算,这样就可以很方便地处理大文件了。象前面的例子,只要写一行代码就可以:

1、  求订单金额总和

=file("orders.txt").cursor@t().total(sum(amount))

如果想再加个过滤也很容易,比如只统计2009年开始的订单金额总和:

=file("orders.txt").cursor@t().select(orderdate>=date("2009-01-01")).total(sum(amount))

做分组、排序也简单:

2、  按州分组求各州订单金额总和

=file("orders.txt").cursor@t().groups(state;sum(amount))

3、  按订单金额排序

=file("orders.txt").cursor@t().sortx(amount)

 

esProc甚至还允许直接对文件使用SQL查询,如前面3例写出来如下:

$select sum(amount) from "orders.txt"

$select state, sum(amount) from "orders.txt" group by state

$select * from "orders.txt" order by amount

 

esProc还内置了并行计算,能充分利用多核CPU提高性能,这对于大文件是特别有用的。比如分组汇总计算写成:

=file("orders.txt").cursor@tm(;4).groups(state;sum(amount))

将会按 4 路并行方式计算,在普通多核笔记本上速度能提高 2-3 倍。相对来讲,高级语言能实现并行,但不仅麻烦,而且只有专业程序员才会做。Python基本上不能并行。SQL得看数据库,Oracle这种专业数据库没问题,而简易的MySQL就不行。

esProc SPL拥有了SQL的优点,又避免了数据导入的麻烦,对于桌面数据分析人员来讲,是用于处理大文件的最合适工具。