使用SQL語句直接高效地訪問文本文件.doc

“萬能數(shù)據(jù)庫查詢分析器”使用SQL語句直接高效地訪問文本文件馬根峰( 廣東聯(lián)合電子服務(wù)股份有限公司, 廣州 510300)摘要 用SQL語句來直接訪問文本文件？是在做夢嗎？本文詳細地介紹了“萬能數(shù)據(jù)庫查詢分析器”，中文版本DB 查詢分析器、英文版本DB Query Analyzer在 文本文件 處理方面非常強大的功能，你可以直接用SQL語句來訪問這些文本文件，訪問250萬條記錄的文件的復雜的關(guān)聯(lián)操作，也不過用時59秒鐘。需要注意的是，文本文件的第一行需要有列名。關(guān)鍵詞 DB 查詢分析器；DB Query Analyzer 1 引言 中國本土程序員馬根峰推出的個人作品-萬能數(shù)據(jù)庫查詢分析器，中文版本DB 查詢分析器、英文版本DB Query Analyzer。萬能數(shù)據(jù)庫查詢分析器集哈希技術(shù)、鏈表等多種數(shù)據(jù)結(jié)構(gòu)于一體，使用先進系統(tǒng)開發(fā)技術(shù)，經(jīng)歷4年的研究、開發(fā)、測試周期后在2006年面世。之后7年來一直在進行不斷地完善、升級，到目前為止，最新版本為5.04 ?！叭f能數(shù)據(jù)庫查詢分析器”核心部分就具有長達5萬多行代碼的工作量，使得其具有強大的功能、友好的操作界面、良好的操作性、跨越各種數(shù)據(jù)庫平臺乃至于EXCEL和文本文件。你可以通過它查詢ODBC數(shù)據(jù)源（包括世面上所有的數(shù)據(jù)庫、TXT/CSV文件、EXCEL文件）的數(shù)據(jù)。你可以同時執(zhí)行多條DML語句乃至存貯過程，結(jié)果會以你設(shè)定的表格、文本框、文件來返回。從數(shù)據(jù)庫導出千萬條數(shù)據(jù)時，效率與DBMS沒有什么區(qū)別。本文將以5.04版本為例，詳細闡述“萬能數(shù)據(jù)庫查詢分析器”中文版本DB 查詢分析器在 文本文件 處理方面非常強大的功能，你可以直接用SQL語句來訪問這些文本文件，訪問250萬條記錄的文件的復雜的關(guān)聯(lián)操作，也不過用時59秒鐘。要注意的是，文本文件的第一行需要有列名。2 產(chǎn)品獲得的成就及發(fā)展歷程 中文版本DB 查詢分析器在中關(guān)村在線 下載量超過10萬 多次，位居整個數(shù)據(jù)庫類排行榜中前20位。在程序員2007第2期的“新產(chǎn)品&工具點評”部分，編輯“特別推薦”了“萬能數(shù)據(jù)庫查詢分析器”發(fā)布。本期只點評了5個工具，分別是“Adobe Acrobat 8 中文版”、“迅雷搜索 1.7 新版上線”、“Google 桌面搜索 5.0 中文發(fā)布”、“BEA 發(fā)布 WebLogic SIP Server 3.0”和特別推薦“萬能數(shù)據(jù)庫查詢分析器”發(fā)布。前面4個都是國內(nèi)外大型軟件公司的產(chǎn)品，只有“萬能數(shù)據(jù)庫查詢分析器”是個人創(chuàng)作的軟件。截止到2013年4月17日，在Baidu上搜索關(guān)鍵字萬能數(shù)據(jù)庫查詢分析器，搜索結(jié)果達318萬。在Baidu上搜索關(guān)鍵字DB 查詢分析器、DB Query Analyzer，搜索結(jié)果分別在104萬、16萬左右；在Google上搜索“DB 查詢分析器”、“DB Query Analyzer”，結(jié)果分別達104萬、44萬之多。本人撰寫了關(guān)于“萬能數(shù)據(jù)庫查詢分析器”有關(guān)技術(shù)的64篇文章，發(fā)布在電腦編程技巧與維護、軟件、計算機時代、電腦編程技巧與維護、百度文庫、CSDN資源、和本人的四大博客上（CSDN博客、新浪博客、QQ空間和搜狐博客上）。3 為何用DB 查詢分析器來訪問文本文件來解決實際問題在廣東聯(lián)合電子服務(wù)股份有限公司實施一張網(wǎng)的過程中，由于路段上傳的流水存在大量的異常，導致按照正常的結(jié)算流程無法快速地進行路段的結(jié)算。因此，為了進行及時的結(jié)算，廣東省高速公路公司授權(quán)先將異常流水進行忽略，后期再將這些流水進行上傳、修改、拆分結(jié)算。最近，為了實施忽略流水的回傳，本人做的第一步就是從忽略的流水中按照（流水只有在管理點存在、中心和管理點都存在）這兩種情況，再按照流水金額小于0、等于0、大于0三類進行統(tǒng)計成臺賬（6個文件），然后業(yè)務(wù)組，再從這些分類批次（6個文件）中確認哪些批次需要回傳（形成2 個文件， 流水大于0.csv 和 流水小于0.csv ）。最終，本人還要根據(jù)這些這兩個文件中是否“回傳”，再從結(jié)算系統(tǒng)中根據(jù)復雜的邏輯，找出要回傳的流水號并按照區(qū)域分成4個文件。在本次處理中，源處理文件：流水大于0.csv 、 流水小于0.csv 、tb_road、 管理點存在中心不存在的流水_所有.txt 和 管理點與中心都存在的部分_所有.txt 這5個文件。后兩個文件是從結(jié)算系統(tǒng)數(shù)據(jù)庫服務(wù)器中通過復雜的邏輯處理生成的中間結(jié)果文件。輸出結(jié)果：要回傳的流水號并按照區(qū)域分成4個文件。解決方案：因此，輸入條件決定要么在結(jié)算數(shù)據(jù)庫系統(tǒng)中建立 數(shù)據(jù)表 來完成條件的判斷；要么將結(jié)果全部忽略的流水、需要關(guān)聯(lián)的數(shù)據(jù)表（tb_road）導出成 .CSV/.TXT 文件，再使用DB 查詢分析器強大、高效的 .CSV/.TXT 訪問功能來實施整個回傳流水文件的生成。 tb_road 表只是從生產(chǎn)機數(shù)據(jù)庫中導出tb_road表就行了。圖2 數(shù)據(jù)挖掘生成文本文件“管理點與中心都存在部分_所有.txt”4 5.04中文版本DB 查詢分析器為例 下面我們就以“萬能數(shù)據(jù)庫查詢分析器”的中文版本DB 查詢分析器 5.04為例，以Windows 2000 Server操作系統(tǒng)為平臺，先創(chuàng)建基于目錄 “D:ODBC_TXT_CSV”中 .TXT/.CSV 文件的ODBC數(shù)據(jù)源 “odbc_txt_csv”，然后再通過 DB 查詢分析器 5.04來訪問這些目錄下的文件。 操作系統(tǒng)： Windows 2000 Server操作系統(tǒng) CPU：2.8 GHZ 單核內(nèi)存：1GB 前臺程序： DB 查詢分析器 5.04 后臺文件： .TXT/.CSV圖3 創(chuàng)建基于 .CSV/.TXT 文件的ODBC數(shù)據(jù)源（一）圖4 創(chuàng)建基于 .CSV/.TXT 文件的ODBC數(shù)據(jù)源（二）圖5 登錄odbc_txt_csv，不用輸入用戶名和口令圖6 對象瀏覽器來查看數(shù)據(jù)源 odbc_txt_csv圖7 Windows資源管理器中，ODBC數(shù)據(jù)源odbc_txt_csv對應的目錄下所有的文件圖8 用SQL語句來訪問 .txt和 .csv文件，來生成各區(qū)域需要回傳的流水圖8中的復雜的SQL語句如下：select listno,2014-06-16from ( select distinct b.LISTNO,b.roadno -select count(*) as rec_num,sum(b.CASHMONEY)/100 as CASHMONEY,sum(b.ETCMONEY)/100 as ETCMONEY from ( select w.roadno,w.squaddate,w.roadname,w.outvehclass, sum(w.rec_count) as rec_count1,sum(w.cashmoney) as cashmoney1,sum(w.etcmoney) as etcmoney1 from ( select * FROM 流水大于0.csv where (備注 is null) and 回送標志=回送 and (cashmoney+etcmoney)0 union all select * FROM 流水小于0.csv where (備注 is null) and 回送標志=回送 and (cashmoney+etcmoney)0 ) w group by w.roadno,w.squaddate,w.roadname,w.outvehclass ) a,管理點與中心都存在部分_所有.txt b where ( (b.CASHMONEY+b.ETCMONEY)0 union all select * FROM 流水小于0.csv where (備注 is null) and 回送標志=回送 and (cashmoney+etcmoney)0 ) w group by w.roadno,w.squaddate,w.roadname,w.outvehclass ) a,管理點與中心都存在部分_所有.txt b where ( (b.CASHMONEY+b.ETCMONEY)0 ) and a.roadno=b.roadno and a.SQUADDATE=b.SQUADDATE and a.OUTVEHCLASS=b.OUTVEHCLASSNAME union select distinct b.LISTNO,b.roadno -select count(*) as rec_num,sum(b.CASHMONEY)/100 as CASHMONEY,sum(b.ETCMONEY)/100 as ETCMONEY from ( select w.roadno,w.squaddate,w.roadname,w.outvehclass, sum(w.rec_count) as rec_count1,sum(w.cashmoney) as cashmoney1,sum(w.etcmoney) as etcmoney1 from ( select * FROM 流水大于0.csv where (備注=只管理點存在) and 回送標志=回送 and (cashmoney+etcmoney)0 union all select * FROM 流水小于0.csv where (備注=只管理點存在) and 回送標志=回送 and (cashmoney+etcmoney)0 ) w group by w.roadno,w.squaddate,w.roadname,w.outvehclass ) a,管理點存在中心不存在的流水_所有.txt b where ( (b.CASHMONEY+b.ETCMONEY)0 union all select * FROM 流水小于0.csv where (備注=只管理點存在) and 回送標志=回送 and (cashmoney+etcmoney)0 ) w group by w.roadno,w.squaddate,w.roadname,w.outvehclass ) a,管理點存在中心不存在的流水_所有.txt b where ( (b.CASHMONEY+b.ETCMONEY)0 ) and a.roadno=b.roadno and a.SQUADDATE=b.SQUADDATE and a.OUTVEHCLASS=b.OUTVEHCLASSNAME ) www where roadno in (select roadno from tb_road.txt where areano=4407) 圖9 一開始執(zhí)行圖8中的SQL語句時，本機CPU使用率立刻攀升至97%圖10 生成中片區(qū)需要回傳的流水文件，共有6萬多條記錄圖11 從結(jié)算中心數(shù)據(jù)庫中用挖掘生成的所有忽略的流水，兩個文件共250多萬條記錄結(jié)論： 對于“交互式”這種數(shù)據(jù)處理的方式來說，將結(jié)果導出，用DB 查詢分析器的強大、高效的文本文件的處理功能來分析處理，也是一種不錯的選擇。并且DB 查詢分析器的效率非常之高、處理非常方便，可以將源文件當成一個數(shù)據(jù)庫中的數(shù)據(jù)表一樣用標準SQL語句來進行訪問。 本人的PC機只不過內(nèi)存1GB的2005年的DELL 臺式PC機，對250萬條記錄的文件進行關(guān)聯(lián)訪問的時候，也只是用不到59秒的時間就生成了一個區(qū)域的結(jié)果文件，期間CPU使用率高達97% 。5 本人撰寫的關(guān)于“萬能數(shù)據(jù)庫查詢分析器”的64 篇技術(shù)文章 目前以下6篇文章發(fā)布幾個國內(nèi)計算機刊物上：1 馬根峰 DB Query Analyzer中斷SQL語句的執(zhí)行 杭州：計算機時代，2011年第12期2 馬根峰 DB 查詢分析器 批量執(zhí)行DML語句并返回更詳細的信息 北京：電腦編程技巧與維護，2011年第24期3 馬根峰 DB Query Analyzer中的事務(wù)管理在DB2中的應用 北京：電腦編程技巧與維護，2011年第22期4 馬根峰 DB 查詢分析器中斷SQL語句的執(zhí)行 天津：軟件，2011年第6期5 馬根峰 萬能數(shù)據(jù)庫查詢分析器中的事務(wù)管理在Oracle中的應用 上海：微型電腦應用，2011年第11期 6 馬根峰 新產(chǎn)品&工具點評 特別推薦：“萬能數(shù)據(jù)庫查詢分析器”發(fā)布 程序員，2007年2期以下58篇文章發(fā)布在百度文庫、CSDN資源、和本人的四大博客上：萬能數(shù)據(jù)庫查詢分析器使用技巧之（一）直到萬能數(shù)據(jù)庫查詢分析器使用技巧之（十三）共13篇The 1st tip of DB Query Analyze直到The 13th skills of DB Query Analyzer共13篇如何在客戶端配置ODBC來訪問遠程DB2 for Windows服務(wù)器How to configure ODBC DSN in Client to access remote DB2 for Windows如何在服務(wù)器上配置ODBC來訪問本機DB2 for Windows服務(wù)器How to configure ODBC DSN to access local DB2 for Windows軟件開發(fā)頂尖高手的殺手锏SQL語句Which SQL statement is the trump card to the senior software developerDB 查詢分析器批量執(zhí)行DML語句并返回更詳細的信息用“萬能數(shù)據(jù)庫查詢分析器”測試SQLite對4大SQL精髓語句的支持用“DB查詢分析器”的對象瀏覽器來展現(xiàn)數(shù)據(jù)庫的數(shù)據(jù)字典萬能數(shù)據(jù)庫查詢分析器用戶已基本涵蓋當前所有數(shù)據(jù)庫系統(tǒng)The DBMS that DB Query Analyzer Users often use cover all kinds of DBMS查詢分析器應必須具備的功能-中斷查詢的執(zhí)行萬能數(shù)據(jù)庫查詢分析器的EXE文件加殼技術(shù)Exe packer prevent DB Query Ana