無論程式設計師，還是資料分析師或科研工作者，有很多人都會編寫SQL。無論單機上的access，還是區域網中的mysql 或雲環境中的Hadoop，也有很多平臺都支援SQL。經過半個多世紀的演化，SQL已是應用廣、成熟度高、使用方便的資料查詢語言。
但有時候資料在CSV/TSV/XLS之類的檔案裡，為了能繼續使用熟悉的SQL，我們不得不先把檔案匯入資料庫。這個過程很麻煩，要建立表結構、設定欄位名和資料型別、分配許可權，再等待載入。如果涉及多個檔案匯入，這個過程就會變成更為耗時耗力的重複勞動。檔案越多體積越大，還要應對錶空間不足的問題。有些檔案經常更新變化，就不得不反覆匯入，這都令人難以忍受。
更麻煩的是，還有些檔案就不能匯入資料庫，或者勉強匯入也沒法用，還不如用Python或JAVA等開發語言直接計算。常見的情況有：開頭或結尾的資料無用、分隔符不規範（不可見字元、雙字元）、文字格式不規範。比如一行對應多條記錄的情況：
| name,state,tripsSmith,Colorado,2020-01-02 2020-01-05 2010-02-03Jeff,Connecticut,2020-01-09Smith,Indiana,2020-01-21 2020-02-10 |
此外，大部分資料庫都不支援匯入xlsx，需要安裝Excel或第三方工具。安裝Excel的話，需要將xlsx轉為csv再匯入資料庫，煩；用第三方工具直接匯入的話，環境配置複雜，有的只支援xls，有的只支援低版本的xlsx，更煩。
總之，檔案入庫是個麻煩不斷的過程。
那麼，有沒有能直接針對檔案執行SQL的工具？
可以想象，如果能在檔案上直接執行SQL，就能避開入庫帶來的一切麻煩，大大提高工作效率。由此還能帶來更大的好處：再也不必為了計算檔案，而專門部署和維護一套資料庫系統。
優點如此顯著，工具自然不缺。
但先別高興太早。號稱能直接針對檔案執行SQL的工具，或多或少都會存在一些缺陷，有些缺陷還很致命。下面，讓我們先從最輕便的命令列工具開始，深挖一下各類工具的真實能力。
csvsql
既然是命令列工具，csvsql必然具備短小快捷的優點，比如帶列名的sales.csv檔案，按client列分組，對每組的amount列求和，只需在命令列簡單寫一句：
| D:\csvkit\csvsql\bin> csvsql –query “select client, sum(amount) from’sales’group by client” salse.csv |
遺憾的是，csvsql除了體積小、編寫SQL快捷之外，就只剩缺點了，其中最大的缺點是安裝配置複雜。csvsql本質上不是獨立程式，而是個Python指令碼，所以必須事先配置好Python環境，又因為許多功能依賴第三方，所以還要下載合適版本的函式庫。這些對程式設計師來說還算容易，但對資料分析師來說，就有點太為難了。
csvsql的第二大缺點是沒有自己的計算引擎。csvsql內建了一個SQLite資料庫，當我們敲完SQL後，csvsql先以IN-MEMORY模式啟動SQLite，然後默默建表，並將檔案全部載入到SQLite，接著把針對檔案的SQL翻譯成針對資料庫表的SQL，再執行翻譯後的SQL。
沒有自己的計算引擎，首先會導致SQL能力不足。csvsql必須根據SQLite的語法設計一套自己的SQL，還要將針對檔案的SQL翻譯成針對庫表的SQL，沒有強大的技術實力，自己設計的SQL就會趨於保守，翻譯的過程也很難面面俱到，所以csvsql丟失了很多基本功能，比如模糊查詢和日期函式，而這些功能SQLite原本是支援的。
沒有自己的計算引擎，還導致計算效能不足、檔案體積受限。每次執行SQL時，csvsql並非直接對檔案計算，而是多了一步匯入記憶體的過程，加上型別轉換，耗費的時間會相當可觀，因此計算效能較差。檔案從硬碟到記憶體後，體積會變大很多，如果檔案較大而機器記憶體較小，不僅載入時間漫長，還可能發生記憶體溢位，所以檔案不能太大。
歸根到底，上面兩大缺點是因為技術實力不行，所以，下面更多的缺點就不難理解了。
第三點：只支援文字檔案，不支援日常工作中很常見的Excel。
第四點：對文字格式要求太嚴。csvsql只能讀取最基本的CSV格式，如果要實現定義分隔符、跳過無用行、區分首行列名等常用功能，就只能藉助其它文字編輯工具進行預處理。如果開頭或結尾的資料無用、分隔符不規範（不可見字元、雙字元）、文字格式不規範，則須藉助Python、JAVA等開發語言進行預處理。
類似csvsql的命令列工具還有不少，比如textql、querycsv.py、q，這些工具雖然略有差異，但因為基本原理類似，所以上面的缺點一個都不少。
雖然命令列工具都沒有自己的計算引擎，但有一類工具一定有，那就是資料庫系統。下面讓我們看看HSQLDB，一款相當常見、且能直接針對檔案執行SQL的資料庫系統。
HSQLDB
資料庫本身就是技術實力的表現，所以HSQLDB不僅有自己的計算引擎，還提供了強大的SQL語法，無論關聯查詢、子查詢，還是模糊查詢、日期函式，HSQLDB一個都不少，這一點是命令列工具無法比擬的。
但安裝、管理、維護資料庫是一件很麻煩的事，尤其是維護，我們不僅要分配許可權、硬碟、記憶體、快取，還要配置各種各樣的複雜引數，這些工作對資料分析師來說相當困難。事實上，我們之所以希望在檔案上直接執行SQL，很重要的一個原因就是為了避開資料庫的維護。
不僅日常的維護和管理麻煩，在檔案上執行SQL時，操作步驟仍然複雜。
啟動HSQLDB的伺服器和客戶端（也可用第三方客戶端工具比如SQuirreL SQL）的命令格式就很複雜
| D:\jre1.8\bin>java –Xms128m –Xmx2403m -cp d:\hsql\hsqldb.jar org.hsqldb.server.Server -database.0 file: d:\hsql\database\demo -dbname.0 demoD:\jre1.8\bin>java -Xms128m -Xmx2403m -cp d:\hsql\hsqldb.jar org.hsqldb.util.DatabaseManager |
這東西，不熟悉java的非專業程式設計師都會望而生畏。
客戶端連線伺服器的引數配置也很複雜：
還要執行多條預處理語句，包括刪除可能存在的表名、新建表結構、將檔案對應到表，才能真正執行SQL。其中，將檔案對應到資料庫表時，需配置大量引數，如下：
| SET TABLE sales SOURCE “sales.csv;fs=,;encoding=UTF-8;quoted=false;ignore_first=true; cache_scale=100”; |
除了操作過程複雜，HSQLDB還有很多不合理之處。HSQL不支援自動解析資料型別，把這個麻煩扔給了使用者，讓使用者自己建表結構。為了方便使用，很多檔案的首行都是欄位名，尤其是csv，但HSQLDB要求使用者建表結構並指定欄位名，這就導致檔案首行的欄位名完全無用。我們的啟動方式明明是server而不是in-memory，但HSQLDB卻會把檔案事先快取到記憶體，這就會影響整體效能，尤其是檔案較大時。
可以看到，HSQLDB雖然有自己的計算引擎，但底層核心仍然是資料庫表，並非真正的檔案計算引擎，所以才會出現諸多不合理之處。由於缺乏真正的檔案計算引擎，所以HSQLDB不支援Excel，不支援常見的文字格式，更不支援不規範的文字格式等等。
與HSQLDB類似，H2 database和PostgreSQL也可以針對檔案執行SQL，操作過程雖然迥異，但基本原理沒大區別，所以優點缺點如出一轍，這裡不再贅述。
資料庫缺乏真正的檔案計算引擎，而且從安裝管理到配置執行都很複雜，那有沒有既有檔案計算引擎，還有友好的互動介面，可大幅降低使用難度的桌面工具呢？還真有，下面會講到。
OpenOffice Base
作為桌面工具，OpenOffice Base的易用性令人印象深刻：一鍵安裝，即安即用無須配置；介面友好，操作快捷互動流暢。
不僅外表易用，OpenOffice Base的核心也很強大，因為它有真正的檔案計算引擎，可以對檔案直接計算，而不必將檔案載入到其他資料庫，也不必用資料庫引擎計算檔案。這就帶來了三個明顯的改進：自動識別檔案中的資料型別，可計算超過記憶體的大檔案，整體效能大幅提高。
可惜的是，這個檔案計算引擎並不完善。
第一，OpenOffice Base只支援文字檔案，不支援日常工作中很常用的Excel，這就大大限制了使用場景。
第二，OpenOffice Base的SQL能力極為有限，很多基本功能無法實現，比如下面的關聯查詢：
| select employee.name, sales.orderdate, sales.amount from sales left join employee on sales.sellerid= employee.eid |
第三，對文字格式限制過多。
除了預設格式，不支援任何其他格式的資料型別，比如下面特殊格式的日期：
| orderid,client,sellerid,amount,orderdate1,UJRNP,17,392.0,01-01-20122,SJCH,6,4802.0,31-01-2012 |
不支援特殊分隔符，比如下面的格式：
| orderid||client||sellerid||amount||orderdate1||UJRNP||17||392.0||2012-01-012||SJCH||6||4802.0||2012-01-31 |
複雜情況就更不能處理了，比如下面的格式：
| producer: allen date:2013-11-01 //前兩行無用26 //以下為多行記錄TAS 1 2142.42009-08-05 33DSGC 1 613.22009-08-14 |
如果對格式標準的文字檔案進行最基本的查詢，OpenOffice Base是首選工具，但實際工作和理想環境不同。在實際工作中，我們會經常遇到Excel，文字格式總是各式各樣，SQL演算法也應自由靈活。如何才能在實際工作中對檔案執行SQL呢？那當然是檔案計算引擎較為完善的工具了，準確地說，是下面要講到的程式語言整合元件。
Microsoft text/xls driver
作為實力大廠出品的程式語言整合元件，Microsoft text/xls driver幾乎無所不能。在SQL語法方面，該元件不僅支援模糊查詢、日期函式，也支援子查詢和關聯查詢，基本覆蓋實際工作所能遇到的各種演算法。比如關聯兩個csv檔案：
| select client.clientname, sales.orderdate, sales.amount from [sales.csv] as sales left join [client.csv] as client on sales.clientid=client.clientid |
檔案型別方面，該元件不僅支援TXT/CVS/TVS，也支援XLS/XLSX，而且沒有版本限制，這一點在實際工作中非常便利。
對於常見的文字格式，該元件支援得非常好，比如定義分隔符、區分首行列名、設定固定寬度的列、自動識別資料型別。對於複雜的文字格式，該元件雖然不能直接處理，但能透過程式設計間接計算。理論上來說，程式設計師可用該元件讀取任何文字格式，可實現任意業務演算法。
Microsoft text/xls driver的檔案計算引擎較為完善，從能力上來說缺陷很少，但它是程式設計師的專業工具，並不適合資料分析師。
第一，該元件的技術門檻很高，要想正常使用，必須掌握一門程式語言，比如C#或VB。在熟悉程式語言的基礎上，還需要學會ODBC、OLEDB、ADO這三種具體的程式設計介面之一。
第二，該元件需要編寫大量難懂的程式碼，才能真正執行上面的提到的SQL語句。簡化後程式碼如下：
| string path= string.Empty;path=”d:/data”;if (string.IsNullOrWhiteSpace(path)) return null;string connstring = string.Empty;connstring = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + path + “;Extended Properties=’text;HDR=YES;FMT=Delimited’;”;DataSet ds = null;OleDbConnection conn = null;try{conn = new OleDbConnection(connstring); conn.Open();OleDbDataAdapter myCommand = null;myCommand = new OleDbDataAdapter(strSql, connstring);//在這裡執行**SQL**ds = new DataSet();myCommand.Fill(ds, “table1”);}catch (Exception e){ throw e;}finally{ conn.Close();}return ds; |
第三，該元件擴充套件困難。上面的程式碼只能適用於最規範的文字檔案，如果要定義字串格式或資料型別，還需使用schema.ini配置檔案。如果要解析複雜的文字格式，還需額外編寫大量程式碼。
除此之外， Microsoft text/xls driver還存在一個問題：text driver和xls drvier雖然用法類似，卻是兩個獨立的元件。換句話說，文字檔案和Excel之間不能進行關聯計算，除非額外編寫大量程式碼。
與Microsoft text/xls driver類似，CSVJDBC/ExcelJDBC、SpatiaLite也是支援在檔案上直接執行SQL的程式語言整合元件，雖然語言環境不同，但基本原理類似，所以使用難度都很高。而且這兩種元件不是實力大廠的產品，比起Microsoft text/xls driver要差上不少。
Microsoft text/xls driver的檔案計算引擎雖然完善，但使用難度很高。OpenOffice Base易用，但檔案計算引擎不夠完善。那有沒有兼具二者優點，適合資料分析師使用的工具呢？要求苛刻，中者寥寥，目前只發現下面這麼一款產品。
esProc
與OpenOffice Base類似，esProc是易用的桌上型工具，一鍵安裝無需配置。比OpenOffice更易用的是，esProc可以在單元格直接編寫多條SQL，計算結果和SQL直接呈現在同一介面，點選SQL所在的單元格可切換觀察計算結果。
與Microsoft text/xls driver類似，esProc具有完善的計算引擎，可支援實際工作所能遇到的各種SQL語法（見上圖），這是OpenOffice Base做不到的。
檔案型別方面，esProc支援TXT/CVS/TVS，也支援不同版本的XLS/XLSX。比Microsoft text/xls driver更強大的是，esProc可以直接對文字檔案和Excel進行關聯計算（見上圖）。
除了規範的檔案格式外，esProc還可以利用擴充套件函式應用更復雜的情況。比如分隔符為”||”的檔案，OpenOffice Base無法解析，Microsoft text/xls driver要編寫大量程式碼，而esProc只需在SQL中使用擴充套件函式：
| $select * from {file(“sep.txt”).import@t(;,”||”)} |
esProc可自動識別資料型別，對於非預設格式的資料，比如前面提到的特殊日期，OpenOffice Base無法解析，Microsoft text/xls driver需用schema.ini配合程式碼實現，而esProc只要使用簡單的擴充套件函式就能輕鬆應對：
| $select * from{file(“style.csv”).import@ct(orderid,client,sellerid,amount,orderdate
”dd-MM-yyyy”)} |
格式複雜的文字，比如前面提到的一行文字對應多條記錄的情況，OpenOffice Base無法解析，Microsoft text/xls driver需資深程式設計師編寫大量複雜程式碼，而使用擴充套件函式後esProc也不難處理：
| $select * from{file(“trip.csv”).import@tc().news(trips.array(“ “);name,state,~:trip)} |
分析過十幾種工具之後，我們可以得出最終結論：號稱支援檔案上直接執行SQL的工具中，絕大多數只是徒有其表，真正可用的其實只有Microsoft text/xls driver和esProc，前者只適合程式設計師，後者才適合資料分析師。
本作品採用《CC 協議》，轉載必須註明作者和本文連結