如何將 JSON, Text, XML, CSV 數據文件導入 MySQL數據庫中

  將外部數據導入(import)數據庫是在數據庫應用中一個很常見的需求。其實這就是在數據的管理和操作中的ETL (Extract, transform, load)的L (Load)部分,也就是說,將特定結構(structure)或者格式(format)的數據導入某個目的地(比如數據庫,這里我們討論MySQL)。

如何將 JSON, Text, XML, CSV 數據文件導入 MySQL數據庫中

  本文要討論的內容,是如何方便地將多種格式(JSON, Text, XML, CSV)的數據導入MySQL之中。

  本文大綱:

  1. 將Text文件(包括CSV文件)導入MySQL

  2. 將XML文件導入MySQL

  3. 將JSON文件導入MySQL

  4. 使用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數據庫中

 總結

  本文介紹了將不同格式(JSON, Text, XML, CSV)的文件導入MySQL數據庫的一些詳細手段,并進行了一些分析,目的在于幫助讀者掃除一些導入的障礙,理清一些概念。之所以沒有討論導出,是因為導出是一個MySQL到外的操作,是以MySQL本身為轉移的,只要參考MySQL本身的機理即可。

  真正對于大量數據的導入導出,需要思考的問題會很多(比如說在導入時,如何考慮Sharding),這需要另開一篇討論了。

  謝謝閱讀,歡迎指正。

? 版權聲明
THE END
喜歡就支持一下吧
點贊9 分享