大數(shù)據(jù)技術原理與操作應用 第9章 Sqoop 數(shù)據(jù)遷移

第9章Sqoop數(shù)據(jù)遷移1Sqoop基本概念Sqoop安裝配置Sqoop常用的相關命令使用Sqoop進行導入導出2?學習目標了解掌握掌握熟悉了解Sqoop基本概念12掌握Sqoop安裝配置4掌握使用Sqoop進行導入導出3熟悉Sqoop常用的相關命令章節(jié)概要越來越多的企業(yè)使用Hadoop作為處理大數(shù)據(jù)的分布式平臺,但許多企業(yè)仍有大量的數(shù)據(jù)存儲在關系型數(shù)據(jù)庫中,需要頻繁地將數(shù)據(jù)集在Hadoop和傳統(tǒng)數(shù)據(jù)庫之間轉移,這并非易事。因此,一款能夠幫助數(shù)據(jù)進行傳輸?shù)墓ぞ呔妥兊糜葹橹匾?。ApacheSqoop就是這樣的一款工具,可以在Hadoop和傳統(tǒng)關系型數(shù)據(jù)庫之間高效地轉移大量數(shù)據(jù)。

9.1 Sqoop介紹9.1.1 Sqoop概述ApacheSqoop(SQLtoHadoop)是Apache的一個頂級開源項目,主要用于有效地在傳統(tǒng)關系型數(shù)據(jù)庫和Hadoop之間進行大量的數(shù)據(jù)傳遞。Sqoop可以輕松地將關系型數(shù)據(jù)庫(如MySQL、Oracle等)中的數(shù)據(jù)導入Hadoop的HDFS或其他相關系統(tǒng)(如Hive和HBase)中,也可方便地將Hadoop或其他相關系統(tǒng)中的數(shù)據(jù)抽取出來并導出到關系型數(shù)據(jù)庫。Sqoop是關系型數(shù)據(jù)庫與Hadoop之間的數(shù)據(jù)傳遞工具,其工作流程如圖9.1所示。關系型數(shù)據(jù)庫是有類型的,Sqoop可以自動地根據(jù)數(shù)據(jù)庫中的類型將數(shù)據(jù)轉換到Hadoop中,實現(xiàn)數(shù)據(jù)映射和轉換的自動完成。Sqoop支持MySQL、Oracle等多種數(shù)據(jù)庫與Hadoop之間傳遞數(shù)據(jù),并且能高效、可控地利用資源,可以通過調(diào)整任務數(shù)來控制任務的并發(fā)度。

9.1 Sqoop介紹9.1.2 Sqoop導入導出工作機制Sqoop的底層工作機制是

MapReduce任務,也就是將Sqoop導入或?qū)С雒罘g成MapReduce作業(yè)來實現(xiàn)。Sqoop工具接收到用戶提交的Shell命令或“JavaApi”命令后,通過Sqoop中的任務翻譯器將命令轉換為一個只有Map任務的MapReduceJob。這個Job會訪問數(shù)據(jù)庫的元數(shù)據(jù)信息,通過并行的Map任務將關系型數(shù)據(jù)庫表中的數(shù)據(jù)一行一行地讀取出來,然后寫入Hadoop中。也可以將Hadoop中的數(shù)據(jù)導出到關系型數(shù)據(jù)庫。Sqoop的架構如圖9.2所示。9.2Sqoop架構圖

9.1 Sqoop介紹9.1.2 Sqoop導入導出工作機制Sqoop導入(SqoopImport)就是從關系型數(shù)據(jù)庫中提取出數(shù)據(jù)并導入HDFS,操作流程如圖9.3所示。圖9.3

Sqoop導入流程(1)Sqoop導入機制

9.1 Sqoop介紹9.1.2 Sqoop導入導出工作機制在導入的過程中,Sqoop會將輸入命令轉化為基于Map任務的MapReduce作業(yè),而MapReduce作業(yè)會通過InputFormat以JDBC的方式從關系型數(shù)據(jù)庫中讀取數(shù)據(jù)。多個Map任務并發(fā)執(zhí)行,將查詢到的數(shù)據(jù)復制到HDFS上。(1)Sqoop導入機制

9.1 Sqoop介紹9.1.2 Sqoop導入導出工作機制Sqoop導出(SqoopExport)通常是將數(shù)據(jù)從HDFS導出到關系型數(shù)據(jù)庫中,其操作流程如圖9.4所示。(2)Sqoop導出機制圖9.4Sqoop導出流程

9.1 Sqoop介紹9.1.2 Sqoop導入導出工作機制在進行數(shù)據(jù)導出前,需要先在關系型數(shù)據(jù)庫中創(chuàng)建好表。SqoopExport通常選擇JDBC完成數(shù)據(jù)導出。首先Sqoop會獲取目標表的元數(shù)據(jù);然后根據(jù)目標表的結構生成一個Java類,這個類用于從導出的文本文件中解析記錄,并將合適的值插入到目標表中。Sqoop根據(jù)輸入命令,啟動一個基于Map任務的MapReduce作業(yè)。多個Map任務并行執(zhí)行,從HDFS中讀取數(shù)據(jù),使用生成的類解析記錄,并使用JDBC方法將數(shù)據(jù)插入目標表中。(2)Sqoop導出機制

9.1 Sqoop介紹9.1.3Sqoop安裝配置下載Sqoop安裝包sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz，將其上傳到master節(jié)點的/home/apache/package目錄中，然后在該目錄中執(zhí)行命令將安裝包解壓到/home/apache/soft目錄，并重命名為sqoop-1.4.6。(1)下載并解壓Sqoop[apache@masterpackage]$tar-zvxfsqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz-C/home/apache/soft[apache@masterpackage]$mvsqoop-1.4.6.bin__hadoop-2.0.4-alphasqoop-1.4.6

9.1 Sqoop介紹9.1.3Sqoop安裝配置默認情況下，并不存在sqoop-env.sh文件，需要先創(chuàng)建一個。進入Sqoop安裝目錄的conf目錄，將sqoop-env-template.sh重命名為sqoop-env.sh：mvsqoop-env-template.shsqoop-env.sh。修改sqoop-env.sh的內(nèi)容，設置Hadoop和Hive的安裝路徑。HADOOP_COMMON_HOME是Hadoop的環(huán)境信息，HADOOP_MAPRED_HOME用于配置Hadoop的MapReduce存放目錄，這兩者必須有。因為本章需要使用Hive數(shù)據(jù)倉庫，所以還需要配置Hive的路徑。如果需要用到HBase，則還要添加HBase的路徑。Sqoop有內(nèi)置的zookeeper，可以直接使用，不用再進行配置。修改如下：(2)修改sqoop-env.sh文件#Setpathtowherebin/hadoopisavailableexportHADOOP_COMMON_HOME=/home/apache/soft/hadoop-2.7.7#Setpathtowherehadoop-*-core.jarisavailableexportHADOOP_MAPRED_HOME=/home/apache/soft/hadoop-2.7.8#Setthepathtowherebin/hiveisavailableexportHIVE_HOME=/home/apache/soft/hive-1.2.2

9.1 Sqoop介紹9.1.3Sqoop安裝配置將bin/configure-sqoop文件中關于HCAT_HOME、ACCUMULO_HOME和ZOOKEEPER_HOME的檢查注釋掉。如圖9.5所示。(3)修改bin/configure-sqoop文件圖9.5修改configure-sqoop文件

9.1 Sqoop介紹9.1.3Sqoop安裝配置Sqoop是Hadoop和傳統(tǒng)關系型數(shù)據(jù)庫之間進行數(shù)據(jù)傳遞的橋梁,使用JDBC來訪問關系型數(shù)據(jù)庫,需要添加數(shù)據(jù)庫驅(qū)動包。本章選擇的是MySQL數(shù)據(jù)庫,將“mysql-connector-java-5.1.38.jar”包拷貝到Sqoop的lib目錄下。(4)添加數(shù)據(jù)庫驅(qū)動包

9.1 Sqoop介紹9.1.3Sqoop安裝配置在Sqoop安裝目錄的bin目錄下,輸入version命令可查看Sqoop的版本,如圖9.6所示。(5)進行測試圖9.6Sqoopversion命令

9.1 Sqoop介紹9.1.3Sqoop安裝配置連接MySQL測試。Sqoop通過jdbc連接MySQL，輸出數(shù)據(jù)庫信息，命令為：bin/sqooplist-databases--connectjdbc:mysql://master:3306/--usernamehive--passwordHive@123，輸出結果如圖9.7所示。(5)進行測試圖9.7Sqoop連接MySQL測試

9.1 Sqoop介紹9.1.4 Sqoop常用命令對于不同的命令,有不同的參數(shù),而有些公用參數(shù)是大多數(shù)命令都支持的,表9.2列舉了用于數(shù)據(jù)庫連接的公用參數(shù),表9.3列舉了import命令的公用參數(shù),表9.4列舉了export命令的公用參數(shù),表9.5列舉了Hive相關的公用參數(shù)。表9.2

數(shù)據(jù)庫連接的公用參數(shù)參數(shù)描述

-connect連接關系型數(shù)據(jù)庫的url-connection-manager指定要使用的連接管理類-driver驅(qū)動器-username連接數(shù)據(jù)庫的用戶名-password連接數(shù)據(jù)庫的密碼-help打印幫助信息-verbose在控制臺打印出詳細信息

9.1 Sqoop介紹9.1.4 Sqoop常用命令表9.3import命令的公用參數(shù)參數(shù)描述

-fields-terminated-bychar設定字段分隔符,默認為逗號-lines-terminated-bychar設定每行記錄之間的分隔符,默認是“\n”-enclosed-bychar給字段值前加上指定的字符-escaped-bychar對字段中的雙引號加轉義符-mysql-delimiters設置MySQL默認的分隔符,字段之間以逗號分隔,行之間以“\n”分隔,默認轉義符是“\”,字段值使用單引號

-optionally-enclosed-bychar給帶有雙引號或單引號字段值前后加上指定字符

9.1 Sqoop介紹9.1.4 Sqoop常用命令表9.4export命令的公用參數(shù)參數(shù)描述

-input-fields-terminated-bychar定義字段之間的分隔符-input-lines-terminated-bychar定義行之間的分隔符-input-enclosed-bychar給字段值前后加上指定字符-input-escaped-bychar對含有轉義符的字段做轉義處理-input-optionally-enclosed-bychar給帶有雙引號或單引號的字段前后加上指定字符

9.1 Sqoop介紹9.1.4 Sqoop常用命令表9.5Hive相關的公用參數(shù)參數(shù)描述

-hive-import將數(shù)據(jù)從關系型數(shù)據(jù)庫中導入Hive表中-hive-overwrite覆蓋Hive表中已經(jīng)存在的數(shù)據(jù)-create-hive-table創(chuàng)建Hive表,如果目標表存在,則創(chuàng)建失敗-hive-table要創(chuàng)建的Hive表名-table指定關系型數(shù)據(jù)庫的表名-map-column-hivemap生成Hive表時,可以更改生成字段的數(shù)據(jù)類型-hive-partition-key創(chuàng)建分區(qū),后面跟分區(qū)名稱,分區(qū)字段的默認類型是string-hive-partition-valuev導入數(shù)據(jù)時,指定某個分區(qū)的值

9.1 Sqoop介紹9.1.4 Sqoop常用命令如果向Hive中導入數(shù)據(jù),那么當Hive中沒有對應表時會進行自動創(chuàng)建。如果進行增量導入數(shù)據(jù)到Hive,就需要設定“mode=append”或“mode=lastmodified”。append模式主要針對insert新增數(shù)據(jù)的增量導入,lastmodified模式主要針對update修改數(shù)據(jù)的增量導入。如果將查詢結果導入Hive,就需要使用參數(shù)“-query”。使用時必須伴隨參數(shù)“-target-dir、-hive-table”,如果查詢中有where條件,則條件后必須加上“﹩CONDITIONS”關鍵字。

9.2 Sqoop數(shù)據(jù)導入9.2.1MySQL表數(shù)據(jù)導入HDFS下載MySQL的Windows安裝包“mysql-es-sential-5.1.51-win32.msi”,雙擊進行安裝,注意字符集選擇“utf8”,然后安裝“navicat8_mysql_cs.exe”。2) 打開navicat,建立localhost連接,如圖9.8所示。單擊“連接測試”,彈出“連接成功”對話框,表示連接沒有問題。

(1)創(chuàng)建MySQL表圖9.8建立localhost連接

9.2 Sqoop數(shù)據(jù)導入9.2.1MySQL表數(shù)據(jù)導入HDFS

(1)創(chuàng)建MySQL表圖9.9創(chuàng)建sqoop_test數(shù)據(jù)庫3)創(chuàng)建數(shù)據(jù)庫。在localhost連接上右擊鼠標,選擇“創(chuàng)建數(shù)據(jù)庫”。輸入數(shù)據(jù)庫名“sqoop_test”,選擇字符集“utf8--UTF-8Unicode”,如圖9.9所示。

9.2 Sqoop數(shù)據(jù)導入9.2.1MySQL表數(shù)據(jù)導入HDFS

(1)創(chuàng)建MySQL表圖9.10創(chuàng)建MySQL表mysql_employee4)創(chuàng)建表。雙擊數(shù)據(jù)庫sqoop_test，在“表”上面點鼠標右鍵，創(chuàng)建表。設置屬性id、name、sex、age、department，如圖9.10所示。其中id設置為主鍵并且自動遞增，varchar類型的屬性的字符集都選擇utf-8。保存并鍵入表名mysql_employee。

9.2 Sqoop數(shù)據(jù)導入9.2.1MySQL表數(shù)據(jù)導入HDFS(2)MySQL表數(shù)據(jù)導入HDFS在 HDFS中創(chuàng)建目錄“/sqoop”,用于存放從MySQL導入的數(shù)據(jù),命令為“hdfsdfs-mkdir/sqoop”。打開master節(jié)點終端,切換到Sqoop的安裝目錄,在bin目錄下執(zhí)行SqoopImport命令將表“mysql_employee”的數(shù)據(jù)導入HDFS。執(zhí)行命令為:[apache@mastersqoop-1.4.6]$bin/sqoopimport--connect'jdbc:mysql://172.20.10.2/sqoop_test?useUnicode=true&characterEncoding=utf-8'--usernameroot--passwordroot--tablemysql_employee--target-dir/sqoop/mysql_employee--m2--fields-terminated-by'\t'--lines-terminated-by'\n'

9.2 Sqoop數(shù)據(jù)導入9.2.1MySQL表數(shù)據(jù)導入HDFS(2)MySQL表數(shù)據(jù)導入HDFS在 HDFS中創(chuàng)建目錄“/sqoop”,用于存放從MySQL導入的數(shù)據(jù),命令為“hdfsdfs-mkdir/sqoop”。打開master節(jié)點終端,切換到Sqoop的安裝目錄,在bin目錄下執(zhí)行SqoopImport命令將表“mysql_employee”的數(shù)據(jù)導入HDFS。執(zhí)行命令為:[apache@mastersqoop-1.4.6]$bin/sqoopimport--connect'jdbc:mysql://172.20.10.2/sqoop_test?useUnicode=true&characterEncoding=utf-8'--usernameroot--passwordroot--tablemysql_employee--target-dir/sqoop/mysql_employee--m2--fields-terminated-by'\t'--lines-terminated-by'\n'

9.2 Sqoop數(shù)據(jù)導入9.2.1MySQL表數(shù)據(jù)導入HDFS(2)MySQL表數(shù)據(jù)導入HDFS--connect:后面是連接MySQL的url。這里使用JDBC連接Windows系統(tǒng)的MySQL數(shù)據(jù)庫sqoop_test,所以,IP地址為Windows系統(tǒng)的IP地址。--usernameroot:連接MySQL的用戶名為“root”。--passwordroot:連接MySQL的密碼為“root”。--tablemysql_employee:指定MySQL的表為mysql_employee。--target-dir/sqoop/mysql_employee:指定表數(shù)據(jù)導入HDFS的“/sqoop/mysql_employee”目錄。--m2:設置Map任務的并行度為“2”。--fields-terminated-by'\t':設置導入數(shù)據(jù)在HDFS文件中的字段分隔符為“\t”。

9.2 Sqoop數(shù)據(jù)導入9.2.1MySQL表數(shù)據(jù)導入HDFS

(3)查看導入結果使用HDFS命令查看“/sqoop/mysql_employee”目錄,里面的兩個文件“part-m-00000”和“part-m-00001”就是導入后生成的文件。因為設置了Map的并行度為“2”,所以生成了兩個HDFS文件?？梢赃M一步查看這兩個文件的內(nèi)容,其結果如圖9.12所示。圖9.12MySQL表數(shù)據(jù)導入HDFS結果查看

9.2 Sqoop數(shù)據(jù)導入9.2.2MySQL表數(shù)據(jù)導入Hive[apache@mastersqoop-1.4.6]$bin/sqoopimport\>--connect'jdbc:mysql://172.20.10.2/sqoop_test?useUnicode=true&characterEncoding=utf-8'\>--usernameroot\>--passwordroot\>--tablemysql_employee\>--hive-import\>--hive-overwrite\>--hive-databaseapachedb\>--create-hive-table\>--hive-tablemysql_employee2\>--m2\>--fields-terminated-by','\>--lines-terminated-by'\n'

9.2 Sqoop數(shù)據(jù)導入9.2.1MySQL表數(shù)據(jù)導入HDFS

(3)查看導入結果通過HDFSWeb查看Hive數(shù)據(jù)表文件,如圖9.13所示,在“/user/hive/warehouse/apachedb/”目錄下新增了一個“mysql_employee2”目錄,里面包括了兩個文件,這個就是導入的Hive表數(shù)據(jù)文件。圖9.13導入的Hive表數(shù)據(jù)文件圖9.14將MySQL表數(shù)據(jù)導入到Hive中

9.3Sqoop數(shù)據(jù)導出9.3.1 HDFS數(shù)據(jù)導出到MySQL(1)創(chuàng)建MySQL表在MySQL的sqoop_test數(shù)據(jù)庫中,創(chuàng)建表“employee_out”,結構與“mysql_employee”一樣,也可以在navicat中直接通過復制“mysql_employee”的方式創(chuàng)建新表。確保“employee_out”表中沒有數(shù)據(jù)。

9.3Sqoop數(shù)據(jù)導出9.3.1 HDFS數(shù)據(jù)導出到MySQL(2)將HDFS數(shù)據(jù)導出到MySQL[apache@mastersqoop-1.4.6]$bin/sqoopexport\>--connect'jdbc:mysql://172.20.10.2/sqoop_test?useUnicode=true&characterEncoding=utf-8'\>--usernameroot\>--passwordroot\>--tableemployee_out\>--export-dir/sqoop/mysql_employee\>--input-fields-terminated-by'\t'\>--input-lines-terminated-by'\n'\>--m2--export-dir/sqoop/mysql_employee：指定待導出的HDFS數(shù)據(jù)文件的目錄。--input-fields-terminated-by'\t'：指定HDFS文件的字段分隔符為“\t”。--input-lines-terminated-by'\n'：指定HDFS文件的行分隔符為“\n”。

9.3Sqoop數(shù)據(jù)導出9.3.1 HDFS數(shù)據(jù)導出到MySQL(3)查看導出結果

9.3Sqoop數(shù)據(jù)導出9.3.2 Hive數(shù)據(jù)導出到MySQL(1)創(chuàng)建MySQL