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

289

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

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

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

　　本文大綱：

  1. 將Text文件（包括CSV文件）導入MySQL

  3. 將XML文件導入MySQL

  5. 將JSON文件導入MySQL

  7. 使用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 分享