大數(shù)據(jù)存儲-數(shù)據(jù)傳輸工具Sqoop

數(shù)據(jù)傳輸工具——Sqoop目前很多使用Hadoop技術(shù)的企業(yè)，仍然有大量的數(shù)據(jù)存儲在傳統(tǒng)關(guān)系型數(shù)據(jù)庫中。在開發(fā)的過程中總是需要將數(shù)據(jù)從傳統(tǒng)關(guān)系型數(shù)據(jù)庫傳輸?shù)紿adoop中，順利的傳輸過程能夠確保數(shù)據(jù)成功存儲。因此數(shù)據(jù)傳輸工具的選擇是一個非常重要的環(huán)節(jié)。項(xiàng)目描述項(xiàng)目背景某網(wǎng)站的數(shù)據(jù)庫中儲存了約30萬條用戶日志數(shù)據(jù)，已被導(dǎo)出為CSV格式的文件（law_visit_log_all.csv），文件記錄的是用戶在網(wǎng)站上的瀏覽行為信息，包括用戶IP、瀏覽時(shí)間、瀏覽網(wǎng)址類型等信息，具體的字段說明如表所示?，F(xiàn)通過對用戶日志數(shù)據(jù)進(jìn)行處理，根據(jù)瀏覽器類型進(jìn)行用戶信息分類，實(shí)現(xiàn)用戶群分，以便研究不同用戶群的興趣特征。篩選出瀏覽器類型為“iOS”的用戶信息保存至Hive中的表new_law中。項(xiàng)目描述項(xiàng)目背景屬性名稱屬性說明realIP真實(shí)IPrealAreacode地區(qū)編號userOS用戶瀏覽器類型fullURL網(wǎng)址現(xiàn)尋求一個方式或工具實(shí)現(xiàn)傳統(tǒng)關(guān)系型數(shù)據(jù)庫與Hadoop之間的數(shù)據(jù)傳輸，而Sqoop就是為了滿足這一需求而誕生的。本項(xiàng)目將介紹Sqoop的概念、安裝配置和基本使用，結(jié)合用戶日志數(shù)據(jù)實(shí)例，使用Sqoop實(shí)現(xiàn)在Hadoop和關(guān)系型數(shù)據(jù)庫之間進(jìn)行高效的數(shù)據(jù)傳輸，先將CSV格式的用戶日志數(shù)據(jù)導(dǎo)入MySQL，再將MySQL數(shù)據(jù)庫中的數(shù)據(jù)增量導(dǎo)入Hive，最后將Hive中的用戶日志信息篩選結(jié)果導(dǎo)出至MySQL中。項(xiàng)目描述項(xiàng)目目標(biāo)學(xué)習(xí)Sqoop的工作流程、核心理念和集群搭建過程，根據(jù)網(wǎng)站的用戶日志數(shù)據(jù)存儲需求安裝配置Sqoop集群。將用戶日志數(shù)據(jù)保存至MySQL數(shù)據(jù)庫中。學(xué)習(xí)Sqoopeval基本操作，在Sqoop中通過sqoopeval命令對MySQL中的用戶日志數(shù)據(jù)記錄數(shù)進(jìn)行查詢。學(xué)習(xí)Sqoopimport基本操作，使用Sqoop將MySQL中的用戶日志數(shù)據(jù)增量導(dǎo)入至Hive中。學(xué)習(xí)Sqoopexport基本操作，在Hive中篩選出瀏覽器類型為“iOS”的用戶信息并保存為new_law表，使用Sqoop將Hive中的new_law表數(shù)據(jù)傳輸至MySQL數(shù)據(jù)庫中。項(xiàng)目描述項(xiàng)目分析1

查詢MySQL用戶日志數(shù)據(jù)表記錄數(shù)

目錄認(rèn)識Sqoop2增量導(dǎo)入MySQL用戶日志數(shù)據(jù)至Hive3導(dǎo)出Hive中的篩選結(jié)果到MySQL4Sqoop是一種用于在Hadoop和關(guān)系數(shù)據(jù)庫之間進(jìn)行高效傳輸數(shù)據(jù)的工具，它的出現(xiàn)使得Hadoop和關(guān)系數(shù)據(jù)庫之間的數(shù)據(jù)傳輸變得非常方便高效。本任務(wù)將詳細(xì)介紹Sqoop工具的基礎(chǔ)知識，包括內(nèi)容如下?；A(chǔ)概念安裝配置過程一些基本的傳輸命令任務(wù)描述Sqoop是一個用于在Hadoop和傳統(tǒng)關(guān)系型數(shù)據(jù)庫（MySQL、Oracle、Postgres等）之間傳輸數(shù)據(jù)的工具。Sqoop的工作流程如圖所示，使用Sqoopimport命令可以將傳統(tǒng)關(guān)系型數(shù)據(jù)庫中的數(shù)據(jù)導(dǎo)入Hadoop中（如HDFS、Hive、HBase），而使用Sqoopexport命令可以將集群中的數(shù)據(jù)導(dǎo)出至關(guān)系型數(shù)據(jù)庫。了解SqoopSqoop的核心理念包括如下內(nèi)容。將數(shù)據(jù)導(dǎo)入或?qū)С龅拿钷D(zhuǎn)換成MapReduce程序。轉(zhuǎn)換成的MapReduce程序會通過自定義InputFormat（數(shù)據(jù)輸入格式）和OutputFormat（數(shù)據(jù)輸出格式）實(shí)現(xiàn)數(shù)據(jù)的導(dǎo)入導(dǎo)出。通過運(yùn)行MapReduce程序?qū)崿F(xiàn)Hadoop和關(guān)系型數(shù)據(jù)庫中的數(shù)據(jù)相互轉(zhuǎn)移。了解SqoopSqoop在Linux中的安裝步驟如下。在虛擬機(jī)上創(chuàng)建Sqoop安裝目錄，將壓縮包解壓縮至Sqoop安裝目錄。Sqoop的配置文件存放在Sqoop根目錄下的conf目錄中。將配置文件sqoop-env-template.sh重命名為sqoop-env.sh。修改配置文件sqoop-env.sh。配置環(huán)境變量，打開/etc/profile文件，配置完成后運(yùn)行“source/etc/profile”命令使配置的環(huán)境變量生效。進(jìn)入MySQL官網(wǎng)的MySQL連接器下載頁面下載mysql-connector-java-5.1.42-bin.jar的MySQL依賴包，并將依賴包移動到Sqoop安裝目錄的lib文件夾下。解決Sqoop依賴，將安裝目錄下的sqoop-1.4.6.jar復(fù)制到Hadoop安裝目錄下的/share/Hadoop/yarn/目錄。安裝配置Sqoop測試是否安裝成功。執(zhí)行命令“sqoopversion”，若出現(xiàn)如圖所示結(jié)果，說明安裝成功。安裝配置SqoopSqoop提供了大量的傳輸命令以滿足HDFS、Hive、HBase和關(guān)系型數(shù)據(jù)庫中數(shù)據(jù)導(dǎo)入導(dǎo)出的需求。Sqoop的基本傳輸命令，如表所示。了解Sqoop基本傳輸命令傳輸命令解釋help返回Sqoop幫助信息，包括傳輸命令的詳情version返回Sqoop的版本信息list-databases列出所有數(shù)據(jù)庫名list-tables列出某個數(shù)據(jù)庫下所有的表import-all-tables導(dǎo)入某個數(shù)據(jù)庫下所有的表到HDFS中import將數(shù)據(jù)導(dǎo)入Hadoop集群export將Hadoop集群的數(shù)據(jù)導(dǎo)出關(guān)系型數(shù)據(jù)庫中eval使用SQL語句對關(guān)系型數(shù)據(jù)庫進(jìn)行操作job用于生成一個Sqoop的任務(wù)。生成后，該任務(wù)并不執(zhí)行，除非使用命令執(zhí)行該任務(wù)merge將HDFS中不同目錄下面的數(shù)據(jù)合在一起，并存放在指定的目錄中使用Sqoop時(shí)，不僅需要提供傳輸命令，還要提供傳輸命令對應(yīng)的參數(shù)才真正構(gòu)成一個完整的Sqoop命令。Sqoop大多數(shù)命令都支持的公共參數(shù)設(shè)置，如表所示。了解Sqoop基本傳輸命令參數(shù)解釋--connect連接關(guān)系型數(shù)據(jù)庫的URL--connection-manager指定要使用的連接管理類--username連接數(shù)據(jù)庫的用戶名--password連接數(shù)據(jù)庫的密碼--table指定關(guān)系數(shù)據(jù)庫的表名--fields-terminated-by<char>設(shè)定每個字段以什么符號作為結(jié)束，默認(rèn)為逗號--lines-terminated-by<char>設(shè)定每行記錄之間的分隔符，默認(rèn)是“\n”--verbose在控制臺打印出詳細(xì)信息以list-databases命令為例，列出MySQL中所有數(shù)據(jù)庫名，基本操作步驟如下。指定連接MySQL數(shù)據(jù)庫的URL。連接數(shù)據(jù)庫的用戶名及密碼。運(yùn)行查看結(jié)果，如圖所示。了解Sqoop基本傳輸命令1

查詢MySQL用戶日志數(shù)據(jù)表記錄數(shù)

目錄認(rèn)識Sqoop2增量MySQL導(dǎo)入用戶日志數(shù)據(jù)至Hive3導(dǎo)出Hive中的篩選結(jié)果到MySQL4在開發(fā)過程中，常常需要先查詢關(guān)系數(shù)據(jù)庫中將要被導(dǎo)入的數(shù)據(jù)，以確保數(shù)據(jù)的正確性，而來回切換的過程相當(dāng)麻煩。Sqoop提供了eval命令解決這一問題，Sqoopeval命令允許用戶使用SQL語句對關(guān)系型數(shù)據(jù)庫進(jìn)行簡單的SQL查詢操作。本任務(wù)將實(shí)現(xiàn)使用eval命令查詢MySQL用戶日志數(shù)據(jù)表的記錄數(shù)。任務(wù)描述eval命令允許用戶使用SQL語句對關(guān)系型數(shù)據(jù)庫進(jìn)行查詢操作，并將結(jié)果輸出到控制臺。eval命令參數(shù)如下表所示。下面以使用eval命令查詢student數(shù)據(jù)表的數(shù)據(jù)為例，介紹eval命令的基礎(chǔ)用法。

在test數(shù)據(jù)庫創(chuàng)建student數(shù)據(jù)表，并插入數(shù)據(jù)。調(diào)用eval命令，利用“--query”參數(shù)查詢student表的數(shù)據(jù)。查看Hive數(shù)據(jù)表的結(jié)構(gòu)和數(shù)據(jù)，結(jié)果如右圖所示。掌握Sqoopeval基本操作參數(shù)解釋--e\--query<SQLstatement>運(yùn)行簡單的SQL查詢語句根據(jù)law_visit_log_all.csv表的數(shù)據(jù)字段說明，具體的實(shí)現(xiàn)步驟如下。在MySQL數(shù)據(jù)庫中創(chuàng)建存儲用戶日志數(shù)據(jù)表law_log，并將law_visit_log_all.csv文件數(shù)據(jù)導(dǎo)入law_log表中。使用eval命令查詢MySQL用戶日志數(shù)據(jù)表的記錄數(shù)。最后得到用戶日志數(shù)據(jù)表記錄數(shù)為295349，完成結(jié)果如圖所示。任務(wù)實(shí)現(xiàn)1

查詢MySQL用戶日志數(shù)據(jù)表記錄數(shù)

目錄認(rèn)識Sqoop2增量導(dǎo)入MySQL用戶日志數(shù)據(jù)至Hive3導(dǎo)出Hive中的篩選結(jié)果到MySQL4Sqoop核心功能是在Hadoop集群與傳統(tǒng)的關(guān)系型數(shù)據(jù)庫之間傳輸數(shù)據(jù)，因此，導(dǎo)入數(shù)據(jù)無疑是至關(guān)重要的一部分。本任務(wù)將使用Sqoopimport命令將MySQL數(shù)據(jù)庫中的用戶日志數(shù)據(jù)增量導(dǎo)入Hive中。任務(wù)描述Sqoopimport命令可將關(guān)系型數(shù)據(jù)庫中的數(shù)據(jù)導(dǎo)入Hadoop集群中，包括HDFS、Hive、Hbase。import命令可通過設(shè)置特定的參數(shù)滿足各種數(shù)據(jù)導(dǎo)入需求，其參數(shù)設(shè)置如表所示。掌握Sqoopimport基本操作參數(shù)解釋--append將數(shù)據(jù)追加到HDFS中已經(jīng)存在的DataSet中，如果使用該參數(shù)，Sqoop會將數(shù)據(jù)先導(dǎo)入臨時(shí)文件目錄，再合并--as-avrodatafile將數(shù)據(jù)導(dǎo)入一個Avro數(shù)據(jù)文件中--as-sequencefile將數(shù)據(jù)導(dǎo)入一個sequence文件中--as-textfile將數(shù)據(jù)導(dǎo)入一個普通文本文件中--columns<col1,col2,col3>指定要導(dǎo)入的字段--num-mappers<n>啟動n個map來并行導(dǎo)入數(shù)據(jù)，默認(rèn)4個--target-dir<dir>指定導(dǎo)入的HDFS路徑續(xù)接上表。掌握Sqoopimport基本操作參數(shù)解釋--incremental<mode>數(shù)據(jù)導(dǎo)入模式，mode可選append或lastmodified--null-string<null-string>string類型的列如果null，替換為指定字符串--null-non-string<null-string>非string類型的列如果null，替換為指定字符串--where從關(guān)系數(shù)據(jù)庫導(dǎo)入數(shù)據(jù)時(shí)的查詢條件--query導(dǎo)入查詢SQL查詢語句的結(jié)果，使用時(shí)必須伴隨參--target-dir，--hive-table，如果查詢中有where條件，則條件后必須加上$CONDITIONS關(guān)鍵字--check-column<col>作為增量導(dǎo)入的判斷列名--last-value<value>指定某一個值，用于標(biāo)記增量導(dǎo)入的位置以使用import命令將student表中“id”小于3的數(shù)據(jù)導(dǎo)入HDFS的/user/sqoop目錄下為例，實(shí)現(xiàn)步驟如下。指定連接MySQL數(shù)據(jù)庫的URL。連接數(shù)據(jù)庫的用戶名及密碼。選擇數(shù)據(jù)表student，設(shè)置保存目錄為/user/sqoop。設(shè)置判斷條件，篩選“id”小于3的數(shù)據(jù)。運(yùn)行程序結(jié)束后，在HDFS的web端口中查看從MySQL數(shù)據(jù)庫導(dǎo)入的數(shù)據(jù)，如圖所示。了解Sqoop基本傳輸命令在現(xiàn)實(shí)生產(chǎn)環(huán)境中，系統(tǒng)會定期從與業(yè)務(wù)相關(guān)的關(guān)系型數(shù)據(jù)庫將數(shù)據(jù)導(dǎo)入至Hadoop集群中，這時(shí)可以選擇增量導(dǎo)入數(shù)據(jù)。增量導(dǎo)入需要提供一個字段作為增量導(dǎo)入的標(biāo)識，即需要調(diào)用“--check-column”參數(shù)。以增量導(dǎo)入student數(shù)據(jù)表的數(shù)據(jù)到HDFS為例，增量導(dǎo)入的操作從“id”等于3的數(shù)據(jù)開始。程序運(yùn)行結(jié)束，增量導(dǎo)入的操作會新建一個文件存放數(shù)據(jù)，不會與之前導(dǎo)入的數(shù)據(jù)合并，結(jié)果如圖所示。了解Sqoop基本傳輸命令Sqoopjob允許用戶創(chuàng)建并使用已保存的Sqoop任務(wù)（job）。已創(chuàng)建保存的job并不執(zhí)行，除非使用參數(shù)執(zhí)行該任務(wù)。若已創(chuàng)建保存的job被配置為執(zhí)行增量導(dǎo)入，則只需在已保存的job中更新關(guān)于增量導(dǎo)入的行的狀態(tài)，設(shè)置job只增量導(dǎo)入最新的行。job命令參數(shù)設(shè)置如表所示。掌握Sqoopjob基本操作參數(shù)解釋--create<job-name>創(chuàng)建一個job，創(chuàng)建完成后會自動保存job--delete<job-name>刪除一個job--exec<job-name>執(zhí)行一個job--list顯示job列表--show<job-name>顯示一個job的信息以創(chuàng)建并運(yùn)行一個list-databases命令的job為例，介紹job的基礎(chǔ)用法，實(shí)現(xiàn)步驟如下。創(chuàng)建項(xiàng)目myjob。指定連接MySQL數(shù)據(jù)庫的URL。連接數(shù)據(jù)庫的用戶名。運(yùn)行程序，結(jié)果如圖所示。掌握Sqoopimport基本操作執(zhí)行job文件，實(shí)現(xiàn)免密操作步驟如下。調(diào)用“--password-file”參數(shù)。在HDFS創(chuàng)建/user/password目錄。密碼保存在一個文件mysql.pwd中，并將該文件上傳至HDFS的/user/password目錄下。將/user/password/mysql.pwd文件的權(quán)限設(shè)置為只讀。修改Sqoop安裝目錄下的conf文件夾中的sqoop-site.xml文件內(nèi)容。最后使用Sqoopjob命令，調(diào)用“--password-file”參數(shù)聲明密碼文件的路徑，創(chuàng)建免手動輸入密碼的job。掌握Sqoopimport基本操作Sqoop支持使用import傳輸命令設(shè)置特殊的參數(shù)，實(shí)現(xiàn)直接將MySQL中的數(shù)據(jù)導(dǎo)入Hive。相關(guān)參數(shù)設(shè)置如表所示。使用Sqoop從MySQL導(dǎo)入數(shù)據(jù)至Hive參數(shù)解釋--hive-import將數(shù)據(jù)從關(guān)系數(shù)據(jù)庫中導(dǎo)入Hive表--hive-overwrite覆蓋掉在Hive表中已經(jīng)存在的數(shù)據(jù)--hive-table后面接要創(chuàng)建的Hive表，默認(rèn)使用MySQL的表名--table指定關(guān)系數(shù)據(jù)庫的表名--hive-partition-value<v>導(dǎo)入數(shù)據(jù)時(shí)，指定某個分區(qū)的值--hive-drop-import-delims在導(dǎo)入數(shù)據(jù)至Hive時(shí)，去掉數(shù)據(jù)中的\r、\n、\013、\010這樣的字符--hive-delims-replacement<val>用自定義的字符串替換掉數(shù)據(jù)中的\r、\n和\013、\010等字符以導(dǎo)入MySQL數(shù)據(jù)庫的student表數(shù)據(jù)到Hive為例，使用Sqoopimport命令將數(shù)據(jù)導(dǎo)入Hive中。實(shí)現(xiàn)步驟如下。在Hive中創(chuàng)建一個表結(jié)構(gòu)與MySQL的student表結(jié)構(gòu)一致的表。使用Sqoopimport命令，并調(diào)用對應(yīng)的參數(shù)將student表的數(shù)據(jù)導(dǎo)入Hive中。查看Hive數(shù)據(jù)表的結(jié)構(gòu)和數(shù)據(jù)，表結(jié)構(gòu)與數(shù)據(jù)應(yīng)與MySQL中的一致。結(jié)果如圖所示。

掌握Sqoopimport基本操作用戶日志數(shù)據(jù)是一份靜態(tài)離線數(shù)據(jù)，實(shí)現(xiàn)將用戶日志數(shù)據(jù)增量導(dǎo)入Hive中，將日志數(shù)據(jù)分成兩部分導(dǎo)入。具體操作步驟如下。導(dǎo)入第一部分?jǐn)?shù)據(jù)至Hive，導(dǎo)入“system”是“iOS”的數(shù)據(jù)至Hive中的表law_log導(dǎo)入“system”是“iOS”的數(shù)據(jù)至Hive中的表law_log。在Hive中查詢導(dǎo)入的前5條數(shù)據(jù)，結(jié)果如圖所示。任務(wù)實(shí)現(xiàn)1

查詢MySQL用戶日志數(shù)據(jù)表記錄數(shù)

目錄認(rèn)識Sqoop2增量導(dǎo)入MySQL用戶日志數(shù)據(jù)至Hive3導(dǎo)出Hive中的篩選結(jié)果到MySQL4在開發(fā)過程中，經(jīng)常需要在Hadoop集群中進(jìn)行數(shù)據(jù)處理，再將處理之后的篩選結(jié)果保存到MySQL中，以便后續(xù)使用。本任務(wù)要求實(shí)現(xiàn)將存儲在Hive中的篩選結(jié)果導(dǎo)出至MySQL。任務(wù)描述Sqoopexport命令可將Hadoop集群中的數(shù)據(jù)導(dǎo)出至關(guān)系型數(shù)據(jù)庫中，export命令參數(shù)設(shè)置如表所示。掌握Sqoopexport基本操作參數(shù)解釋--direct利用數(shù)據(jù)庫自帶的導(dǎo)入導(dǎo)出工具，以便于提高效率--export-dir<dir>存放數(shù)據(jù)的HDFS的源目錄--table<table-name>指定導(dǎo)出至哪個RDBMS中的表--update-key<col-name>用于更新的錨列，當(dāng)此列（或多列）的值相同時(shí)則判定為重復(fù)記錄--columns<col1,col2,col3>指定要導(dǎo)出的字段--update-mode<mode>當(dāng)插入的數(shù)據(jù)重復(fù)時(shí)，可選updateonly、allowinsert兩個模式--staging-table<staging-table-name>創(chuàng)建一張臨時(shí)表，用于存放所有事務(wù)的結(jié)果，然后將所有事務(wù)結(jié)果一次性導(dǎo)入目標(biāo)表中，防止錯誤。--clear-staging-table如果staging-table參數(shù)非空，則可以在導(dǎo)出操作執(zhí)行前，清空臨時(shí)事務(wù)結(jié)果表Sqoopexport導(dǎo)出命令可以將數(shù)據(jù)導(dǎo)出至關(guān)系型數(shù)據(jù)庫的數(shù)據(jù)表中，其本質(zhì)是將Sqoopexport導(dǎo)出命令轉(zhuǎn)換成INSERT的SQL語句，并將對應(yīng)的數(shù)據(jù)記錄插入到目標(biāo)數(shù)據(jù)庫表中。Sqoop提供了“--update-key”參數(shù)解決目標(biāo)數(shù)據(jù)庫表中已存在該條數(shù)據(jù)記錄的問題。Sqoop提供了“--update-mode<mode>”參數(shù)設(shè)定插入數(shù)據(jù)的模式，支持updateonly和allowinsert兩種模式。設(shè)置為updateonly模式，插入數(shù)據(jù)時(shí)，若遇到重復(fù)的數(shù)據(jù)，則會直接修改原來的數(shù)據(jù)。設(shè)置為al