0x10 SQL與NoSQL，數據橋梁Sqoop

來自： http://www.jianshu.com/p/7fcb24949637

SQL處理二維表格數據，是一種最樸素的工具，NoSQL是Not Only SQL，即不僅僅是SQL。從MySQL導入數據到HDFS文件系統中，最簡單的一種方式就是使用Sqoop，然后將HDFS中的數據和Hive建立映射。通過Sqoop作為數據橋梁，將傳統的數據也存入到NoSQL中來了，有了數據，猴戲才剛剛開始。

0x10.jpg

01 猴年伊始

《云戒云》系列的第一章16篇，從0x00到0x0F已經完成，感興趣可以查找歷史記錄閱讀。這是猴年第一篇，從本篇開始，文章標題的符號便從0x1開始。

SQL處理二維表格數據，是一種最樸素的工具，查詢、更新、修改、刪除這四種對數據的基本操作，是處理數據的一個巨大進步。近些年，各種新的數據處理技術興起了，都想革SQL的命，這些技術也被大家統稱為NoSQL。

NoSQL最初的意思是No SQL，估計應該是想和SQL劃清界線，就像GNU的遞歸縮寫GNU is Not Unix一樣。后來發現，雖然大量的NoSQL技術起來了，但SQL還是活得好好的，照樣發揮著很多不可替代的作用。漸漸地，大家也發現，原來這些新技術，也只是在不同的應用場景下對SQL的補充，因此也慢慢為NoSQL正名了，原來是Not Only SQL，即不僅僅是SQL，還有很多其它的處理非結構化數據和應用于各種場景的技術。甚至很多技術，雖然是在NoSQL的框架下，但也慢慢的又往SQL方向發展。

NoSQL是一種技術或者框架的統稱，包括以Mongodb，Hadoop，Hive，Cassandra，Hbase，Redis等為代表的框架技術，這些都在特定的領域有很多實際的應用。而SQL領域的開源代表自然是MySQL了。

很多企業中，業務數據都是存放在MySQL數據庫中的，當數據量太大后，單機版本的MySQL很難滿足業務分析的各種需求。此時，可能就需要將數據存入Hadoop集群環境中，那么本文的主角Sqoop便適時的出現了，用來架起SQL與NoSQL之間的數據橋梁。

02 MySQL導入HDFS

從MySQL導入到HDFS文件系統中，是最簡單的一種方式了，相當于直接將表的內容，導出成文件，存放到HDFS中，以便后用。

Sqoop最簡單的使用方式，就是一條命令，唯一需要的是配置相應的參數。sqoop可以將所有參數寫在一行上，也可以寫在配置文件里面。因為導入的選項過多，通常我們都把參數寫在配置文件里面，以便更好的調試。在導入到HDFS的過程中，需要配置以下參數：

• 使用import指令
• 數據源配置：驅動程序，IP地址，庫，表，用戶名，密碼
• 導入路徑，以及是否刪除存在的路徑
• 并行進程數，以及使用哪個字段進行切分
• 字段選擇，以及字段分隔符
• 查詢語句：自定義查詢，Limit可以在此處使用
• 查詢條件：自定義條件

配置文件示例：

# 文件名：your_table.options
import

--connect
jdbc:mysql://1.2.3.4/db_name
--username
your_username
--password
your_passwd
--table
your_table

--null-string
NULL

--columns
id, name

# --query
# select id, name, concat(id,name) from your_table where $CONDITIONS limit 100

# --where
# "status != 'D'"

--delete-target-dir
--target-dir
/pingjia/open_model_detail

--fields-terminated-by
'\001'

--split-by
id
--num-mappers
1

示例參數說明：

1. import指令，說明是導入，這兒的“入”是相對于hdfs來說的，即從MySQL導入到hdfs文件系統中。
2. 以雙橫線開頭的是參數，其中connect配置數據庫驅動及來源，此處配置了mysql及ip地址和數據庫名。
3. username, password配置用戶名密碼。table配置來源表名，此處需要注意，如果后面使用了query的方式，即指定了查詢語句，此處table需要注釋。
4. columns配置了從表中讀取的字段，可以是全部，也可以是部分。同上所求，如果指定了query則不需要配置columns
5. query是自己指定導出的sql語句，如果需要自定義導出，則使用。注意，這兒有一個where條件，無論是否使用條件，都需要帶上where $CONDITIONS，$CONDITIONS是后面配置的條件。
6. where用于單獨設置查詢條件
7. target-dir用于指定導入的目錄，從mysql中導入到hdfs中的數據是直接導入到目錄，而不是直接指定文件，文件名會自動生成。另外，如果需要在hive中使用分區，此處應該用子分區的名字。比如，增加一個year=2015的分區，那么，建立目錄的時候，把數據存入子目錄year=2015中去，這樣后面在hive中直接增加分區映射即可。delete-target-dir是如果目錄存在便刪除，否則會報錯。
8. fields-terminated-by用于配置導出的各字段之間，使用的分隔符，為防止數據內容里面包括空格，通常不推薦用空格，'\001'也是Hive中推薦的字段分隔符，當然，我們也是為了更好的在Hive中使用數據才這樣設置。
9. num-mappers是指定并行的mapper(進程數)，這也是使用sqoop的一大優勢，并行可以加快速度，默認使用4個進程并行。同時，split-by需要設置為一個字段名，通常是id主鍵，即在這個字段上進行切分成4個部分，每個進程導入一部分。另外，配置幾個進程數，最后目錄中生成的文件便是幾個，因此對于小表，建立設置num-mappers為1，最后只生成一個文件。

上面使用了配置文件的方式，在配置文件中，可以使用#注釋，也可以使用空行，這樣方便做調試。配置好上面的參數文件，即可調用測試：

sqoop --options-file your_table.options

如果不報錯，最后會顯示導入的文件大小與文件行數。

這是一個導入速度的記錄，供參考：

Transferred 3.9978 GB in 811.4697 seconds (5.0448 MB/sec)
Retrieved 18589739 records.

Transferred 3.4982 GB in 350.2751 seconds (10.2266 MB/sec)
Retrieved 16809945 records.

Transferred 846.5802 MB in 164.0938 seconds (5.1591 MB/sec)
Retrieved 5242290 records.

Transferred 172.9216 MB in 72.2055 seconds (2.3949 MB/sec)
Retrieved 1069275 records.

03 增量導入

HDFS文件系統是不允許對記錄進行修改的，只能對文件進行刪除，或者追加新文件到目錄中。但Mysql數據中的增、刪、改是最基本的操作，因此導入的數據，可能一會兒就過期了。

從這兒也可以看出，并非所有數據都適合導入到HDFS，通常是日志數據或者非常大的需要統計分析的數據。通常不太大的表，也建議直接完整導入，因為本身導入速度已經夠快了，千萬級別的數據，也只是幾分鐘而已。

如果不考慮數據的修改問題，只考慮數據的增加問題，可以使用append模式導入。如果需要考慮數據修改，則使用lastmodified的模式。

增量的方式，需要指定以下幾個參數：

--check-column
filed_name
--incremental
append|lastmodified
--last-value
value

• check_colume：配置檢查增量的字段，通常是id字段，或者時間字段
• incremental: 增量的方式，追加或者最后修改，追加從上一次id開始，只追加大于這個id的數據，通常用于日志數據，或者數據不常更新的數據。最后修改，需要本身在Mysql里面，數據每次更新，都更新維護一個時間字段。在此，表示從指定的時間開始，大于這個時間的數據都是更新過的，都要導入
• last-value: 指定了上一次的id值或者上一次的時間

04 映射到hive

導入到HDFS中的數據，要進行統計分析，甚至會需要對多個文檔進行關聯分析，還是有不便之處，此時可以再使用Hive來進行數據關聯。

首先，需要在Hive中建立表結構，只選擇性的建立導入的數據字段，比如導入了id和name兩個字段，則Hive表也只建立這兩個字段。

另外，最好通過external關鍵字指定建立外部表，這樣Hive只管理表的元數據，真實的數據還是由HDFS來存儲和手工進行更新。即使刪除了Hive中的表，數據依然會存在于HDFS中，還可以另做它用。

建表，要指定字段的數據格式，通常只需要用四數據來替換Mysql的數據：

string ==> 替換char,varchar
int ==> 替換int
float ==> 替換float
timestamp ==> 替換datetime

另外，還需要指定存儲格式，字符分隔符和分區等，常用的一個建表語句如：

CREATE external TABLE your_table (
id int,
name string
)
PARTITIONED BY (pdyear string)
ROW FORMAT DELIMITED fields terminated by '\001'
STORED AS TEXTFILE
LOCATION '/path/your_table';

上面指定了一個分區pdyear，字段分隔符為'\001'，存儲成TEXTFILE格式，數據文件的目錄為/path/your_table（從MySQL導入到HDFS的目錄）。

如果導入的數據，配置了分區，即如下目錄結構：

/path/your_table/pdyear=2015
/path/your_table/pdyear=2016

則建立表后，表里面沒有對應上數據，需要添加分區到hive表中，在hive中執行以下語句：

alter table your_table add partition (pdyear='2015') location '/path/your_table/pdyear=2015';
alter table your_table add partition (pdyear='2016') location '/path/your_table/pdyear=2016';

完成上面的操作后，即可以在Hive中進行查詢和測試，查看是否有數據。Hive的hql語法，源于mysql的語法，只是對部分細節支持不一樣，因此可能需要調試一下。

05 HDFS導出到MySQL

在Hive中進行了一系列的復雜統計分析后，最后的結論可能還是需要存儲到Mysql中，那么可以在Hive語句中，將分析結果導出到HDFS中存儲起來，最后再使用Sqoop將HDFS的文件導入到MySQL表中，方便業務使用。

導出的配置示例：

export

--connect
jdbc:mysql://1.2.3.4/db_name
--username
your_username
--password
your_passwd
--table
your_table

--input-null-string
'\\N'

--update-mode
allowinsert
--update-key
id
--export-dir
/path/your_table/
--columns
id,name
--input-fields-terminated-by
'\001'

參數說明：

export：指令說明是導出
update-mode：allowinsert，配置了，使用更新模式，即如果Mysql中已經有數據了，則進行更新，如果沒有，則插入。判斷的字段使用update-key參數配置，需要這個字段是唯一索引的字段。
input-null-string：Hive中，導出的NULL為字符\N，要還原到Mysql中，依然為MyQL的Null的話，需要使用這個配置，指定NULL的字符串為'\N'

另外，導出的時候，如果Mysql表中有自動增長的主鍵字段，可以留空，生成數據的時候會自動填充。

06 猴戲開始

將MySQL中的數據導入到HDFS中，又將HDFS中的數據建立了到Hive表的映射。至此，通過Sqoop工具作為SQL與NoSQL的數據橋梁，將傳統的數據也存入到NoSQL中來了，有了數據，便是開始。

本篇文章結束了，卻也正是猴年的開始，不要走開，猴戲才剛剛開始，精彩更在后面。