將外部數據導入(import)數據庫是在數據庫應用中一個很常見的需求。其實這就是在數據的管理和操作中的ETL (Extract, transform, load)的L (Load)部分,也就是說,將特定結構(structure)或者格式(format)的數據導入某個目的地(比如數據庫,這里我們討論MySQL)。
本文要討論的內容,是如何方便地將多種格式(JSON, Text, XML, CSV)的數據導入MySQL之中。
本文大綱:
-
將Text文件(包括CSV文件)導入MySQL
-
將XML文件導入MySQL
-
將JSON文件導入MySQL
-
使用MySQL workbench的Table Data Export and Import Wizard進行JSON或CSV文件的導入導出
1. 將Text文件(包括CSV文件)導入MySQL
這里我們的討論是基于一個假定,Text file和CSV file是有著比較規范的格式的(properly formatted),比如說每行的每個數據域(field)之間是由一個共同的分隔符(比如tab: t)分隔的。
那么首先,你需要根據你的數據的格式(有哪些域),來設計好數據庫的對應的表 (的Schema)。
舉個例子,要處理的Text文件或者CSV文件是以t作為分隔符的,每行有id, name, balance這么三個數據域,那么首先我們需要在數據庫中創建這個表:
CREATE?TABLE?sometable(id?INT,?name?VARCHAR(255),?balance?DECIMAL(8,4));
創建成功以后就可以導入了。操作方式很簡單:
LOAD?DATA?LOCAL?INFILE?'你的文件路徑(如~/file.csv)'?INTO?TABLE?sometable?FIELDS?TERMINATED?BY?'t'?[ENCLOSED?BY?'"'(可選)]?LINES?TERMINATED?BY?'n'?(id,?name,?balance)
這里要注意的是,我們需要開啟local-infile這個MySQL的配置參數,才能夠成功導入。究其原因,從MySQL的Manual中可以看到這么一段話:
LOCAL works only if your server and your client both have been configured to permit it. For example, if mysqld was started with –local-infile=0, LOCAL does not work. See Section 6.1.6, “Security Issues with LOAD DATA LOCAL”.
這是MySQL出于安全考慮的默認配置。因此,我們需要在配置文件my.cnf中(以Debian發行版的Linux, 如Ubuntu為例, 即是在/etc/my.cnf中),確保:
local-infile=1
抑或是在命令行啟動MySQL時加上–local-infile這一項:
mysql?--local-infile?-uroot?-pyourpwd?yourdbname
此外,我們也可以使用MySQL的一個官方導入程序 mysqlimport,這個程序本質上就是為LOAD DATA FILE提供了一個命令行的interface,很容易理解,我們這里就不再詳述。
2. 將XML文件導入MySQL
這件事的完成方式,與我們的XML的形式有著很大的關系。
舉個例子說,當你的XML數據文件有著很非常規范的格式,比如:
<?xml version="1.0"?> ??<row> ????<field>1</field> ????<field>Free</field> ????<field>2333.3333</field> ???</row> ??<row> ????<field>2</field> ????<field>Niki</field> ????<field>1289.2333</field> ??</row>
或者
<row></row>
我們就可以很方便使用LOAD XML來導入,這里可以參見MySQL的官方手冊–LOAD XML Syntax。
然而我們可能有另外一些需求,比如說,我們可能會想要將XML文件的域映射到不同名字的列(TABLE COLUMN)之中。這里要注意,MySQL v5.0.7以后,MySQL的Stored Procedure中不能再運行LOAD XML INFILE 或者LOAD DATA INFILE。所以轉換的程序(procedure)的編寫方式與在此之前有所不同。這里,我們需要使用 Load_File()和ExtractValue()這兩個函數。
以下是一個示例XML文件和程序:文件:
<?xml version="1.0"?><some_list> ??<someone></someone> ??<someone></someone> ??<someone></someone></some_list>
程序:
DELIMITER?$$ CREATE?DEFINER=`root`@`localhost`?PROCEDURE?`import_some_xml`(path?varchar(255),?node?varchar(255)) BEGIN ????declare?xml_content?text; ????declare?v_row_index?int?unsigned?default?0;??? ????declare?v_row_count?int?unsigned;?? ????declare?v_xpath_row?varchar(255);? ? ????set?xml_content?=?load_file(path); ? ????--?calculate?the?number?of?row?elements.??? ????set?v_row_count??=?extractValue(xml_content,?concat('count(',?node,?')'));? ???? ????--?loop?through?all?the?row?elements???? ????while?v_row_index?<p> 在MySQL中,使用它進行導入:</p><pre class="brush:sql;">call?import_some_xml('你的XML文件路徑',?'/some_list/someone');
程序相當的直白,只要了解一下MySQL的腳本編寫即可。
這里提一下DELIMITER $$。我們知道MySQL的命令分隔符默認為分號,然而腳本中很顯然是有分號的,但是我們并不希望立即執行,所以我們需要臨時更改分隔符。
3. 將JSON文件導入MySQL
如何將JSON文件導入MySQL中,是一個很有趣的話題。JSON是一種現在相當常用的文件結構,所以掌握它的導入具有比較廣泛的意義。
很多時候,我們處理的JSON數據是以如下形式出現的:
{"name":"Julia","gender":"female"} {"name":"Alice","gender":"female"} {"name":"Bob","gender":"male"} {"name":"Julian","gender":"male"}
而并不是規整的[{},{},{},{}](一些NoSQL數據庫的Export)。
這樣的形勢對于載入有一個好處:因為每一行是一個JSON Object,所以我們便可以按行處理此文件,而不需要因為JSON的嚴格結構將整個文件(比如一個許多G的.json文件)全部載入。
方式一 使用common-schema
common-schema是一個應用很廣泛的MySQL的框架,它有著很豐富的功能和詳細的文檔。我們可以使用它的JSON解析的功能。(它還具有JSON轉換成XML等等方便的功能)
具體說來,將common-schema導入之后,使用它的extract_json_value函數即可。源碼中:
create?function?extract_json_value( ????json_text?text?charset?utf8, ????xpath?text?charset?utf8 )?returns?text?charset?utf8
該函數接受兩個參數,一個是json_text,表示json文件的內容,另一個是xpath,表示數據的結構(這里可以類比XML文件的處理)。很多讀者應該知道,XPath是用來對XML中的元素進行定位的,這里也可以作一樣的理解。
以本段開始的幾行JSON為例,這里common-schema的使用如下例:
select?common_schema.extract_json_value(f.event_data,'/name')?as?name,?common_schema.extract_json_value(f.event_data,'/gender')?as?gender,?sum(f.event_count)?as?event_count?from?json_event_fact?f?group?by?name,?gender;
關于event_data,我們需要先理解LOAD DATA INFILE是一個event,不同的event type對應不同的event data。這部分知識可以參看Event Data for Specific Event Types
如果感興趣,可以參看其源碼。參看一個受到廣泛使用的項目的源碼,對于自身成長是很有益的。
當然了,我們也可以像之前處理XML文件導入一樣,自己編寫程序。這里便不再給出實例程序,有興趣的讀者可以自行編寫或者跟筆者交流。
方式二 使用mysqljsonimport
這是Anders Karlsson的一個完成度很高的作品。這一份程序由C寫成。它依賴于一個JSON Parser,Jansson。他們都有著比較好的維護和文檔,所以使用上體驗很好。
mysqljsonimport的下載在SourceForge上。具體使用參照其文檔即可。
為了方便不熟悉源碼安裝的朋友,筆者在這里提一下安裝流程和注意事項。安裝命令順序如下:
$?wget?http://sourceforge.net/projects/mysqljson/files/myjsonimport_1.6/mysqljsonimport-1.6.tar.gz? $?tar?xvfz?mysqljsonimport-1.6.tar.gz? $?cd?mysqljsonimport-1.6? $?./configure?–-with-mysql=/xxx/mysql? $?make $?make?check? $?sudo?make?install
–with-mysql這一步不是必要的,只要你安裝的mysql的路徑是系統的默認路徑。很關鍵的,而且很容易被不熟悉的朋友忽略的是,這一個C程序要成功編譯和運行,是需要MySQL的C API的,所以需要安裝的依賴,除了jansson,還有libmysqlclient-dev。
jansson的安裝就是簡單的源碼安裝,libmysqlclient-dev則可以使用包管理工具(比如ubuntu中使用apt-get即可;編譯和安裝前,建議先sudo apt-get update以避免不必要的麻煩)。
導入命令:
$?./mysqljsonimport?–-database?test?–-table?tablename?jsonfilename
還有一個parser,作者是Kazuho,感興趣的讀者可以參看一下,他的相關博文是mysql_json – a MySQL UDF for parsing JSON ,github項目是mysql_json。
4. 使用MySQL workbench
Workbench這個工具對于許多不熟悉SQL語言或者命令行的朋友還是很方便和友好的。利用它,可以方便地導入和導出CSV和JSON文件。
具體操作圖例參見MySQL官方手冊即可:Table Data Export and Import Wizard,這里不再贅述。
總結
本文介紹了將不同格式(JSON, Text, XML, CSV)的文件導入MySQL數據庫的一些詳細手段,并進行了一些分析,目的在于幫助讀者掃除一些導入的障礙,理清一些概念。之所以沒有討論導出,是因為導出是一個MySQL到外的操作,是以MySQL本身為轉移的,只要參考MySQL本身的機理即可。
真正對于大量數據的導入導出,需要思考的問題會很多(比如說在導入時,如何考慮Sharding),這需要另開一篇討論了。
謝謝閱讀,歡迎指正。