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

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

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

9.1 Sqoop介紹9.1.2 Sqoop導(dǎo)入導(dǎo)出工作機(jī)制Sqoop的底層工作機(jī)制是

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

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

Sqoop導(dǎo)入流程(1)Sqoop導(dǎo)入機(jī)制

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

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

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

9.1 Sqoop介紹9.1.3Sqoop安裝配置下載Sqoop安裝包sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz，將其上傳到master節(jié)點(diǎn)的/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安裝配置默認(rèn)情況下，并不存在sqoop-env.sh文件，需要先創(chuàng)建一個(gè)。進(jìn)入Sqoop安裝目錄的conf目錄，將sqoop-env-template.sh重命名為sqoop-env.sh：mvsqoop-env-template.shsqoop-env.sh。修改sqoop-env.sh的內(nèi)容，設(shè)置Hadoop和Hive的安裝路徑。HADOOP_COMMON_HOME是Hadoop的環(huán)境信息，HADOOP_MAPRED_HOME用于配置Hadoop的MapReduce存放目錄，這兩者必須有。因?yàn)楸菊滦枰褂肏ive數(shù)據(jù)倉(cāng)庫(kù)，所以還需要配置Hive的路徑。如果需要用到HBase，則還要添加HBase的路徑。Sqoop有內(nèi)置的zookeeper，可以直接使用，不用再進(jìn)行配置。修改如下：(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文件中關(guān)于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)關(guān)系型數(shù)據(jù)庫(kù)之間進(jìn)行數(shù)據(jù)傳遞的橋梁,使用JDBC來(lái)訪(fǎng)問(wèn)關(guān)系型數(shù)據(jù)庫(kù),需要添加數(shù)據(jù)庫(kù)驅(qū)動(dòng)包。本章選擇的是MySQL數(shù)據(jù)庫(kù),將“mysql-connector-java-5.1.38.jar”包拷貝到Sqoop的lib目錄下。(4)添加數(shù)據(jù)庫(kù)驅(qū)動(dòng)包

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

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

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

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

-connect連接關(guān)系型數(shù)據(jù)庫(kù)的url-connection-manager指定要使用的連接管理類(lèi)-driver驅(qū)動(dòng)器-username連接數(shù)據(jù)庫(kù)的用戶(hù)名-password連接數(shù)據(jù)庫(kù)的密碼-help打印幫助信息-verbose在控制臺(tái)打印出詳細(xì)信息

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

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

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

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對(duì)含有轉(zhuǎn)義符的字段做轉(zhuǎn)義處理-input-optionally-enclosed-bychar給帶有雙引號(hào)或單引號(hào)的字段前后加上指定字符

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

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

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

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

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

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

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

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

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

9.2 Sqoop數(shù)據(jù)導(dǎo)入9.2.1MySQL表數(shù)據(jù)導(dǎo)入HDFS(2)MySQL表數(shù)據(jù)導(dǎo)入HDFS在 HDFS中創(chuàng)建目錄“/sqoop”,用于存放從MySQL導(dǎo)入的數(shù)據(jù),命令為“hdfsdfs-mkdir/sqoop”。打開(kāi)master節(jié)點(diǎn)終端,切換到Sqoop的安裝目錄,在bin目錄下執(zhí)行SqoopImport命令將表“mysql_employee”的數(shù)據(jù)導(dǎo)入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ù)導(dǎo)入9.2.1MySQL表數(shù)據(jù)導(dǎo)入HDFS(2)MySQL表數(shù)據(jù)導(dǎo)入HDFS在 HDFS中創(chuàng)建目錄“/sqoop”,用于存放從MySQL導(dǎo)入的數(shù)據(jù),命令為“hdfsdfs-mkdir/sqoop”。打開(kāi)master節(jié)點(diǎn)終端,切換到Sqoop的安裝目錄,在bin目錄下執(zhí)行SqoopImport命令將表“mysql_employee”的數(shù)據(jù)導(dǎo)入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ù)導(dǎo)入9.2.1MySQL表數(shù)據(jù)導(dǎo)入HDFS(2)MySQL表數(shù)據(jù)導(dǎo)入HDFS--connect:后面是連接MySQL的url。這里使用JDBC連接Windows系統(tǒng)的MySQL數(shù)據(jù)庫(kù)sqoop_test,所以,IP地址為Windows系統(tǒng)的IP地址。--usernameroot:連接MySQL的用戶(hù)名為“root”。--passwordroot:連接MySQL的密碼為“root”。--tablemysql_employee:指定MySQL的表為mysql_employee。--target-dir/sqoop/mysql_employee:指定表數(shù)據(jù)導(dǎo)入HDFS的“/sqoop/mysql_employee”目錄。--m2:設(shè)置Map任務(wù)的并行度為“2”。--fields-terminated-by'\t':設(shè)置導(dǎo)入數(shù)據(jù)在HDFS文件中的字段分隔符為“\t”。

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

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

9.2 Sqoop數(shù)據(jù)導(dǎo)入9.2.2MySQL表數(shù)據(jù)導(dǎo)入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ù)導(dǎo)入9.2.1MySQL表數(shù)據(jù)導(dǎo)入HDFS

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

9.3Sqoop數(shù)據(jù)導(dǎo)出9.3.1 HDFS數(shù)據(jù)導(dǎo)出到MySQL(1)創(chuàng)建MySQL表在MySQL的sqoop_test數(shù)據(jù)庫(kù)中,創(chuàng)建表“employee_out”,結(jié)構(gòu)與“mysql_employee”一樣,也可以在navicat中直接通過(guò)復(fù)制“mysql_employee”的方式創(chuàng)建新表。確?！癳mployee_out”表中沒(méi)有數(shù)據(jù)。

9.3Sqoop數(shù)據(jù)導(dǎo)出9.3.1 HDFS數(shù)據(jù)導(dǎo)出到MySQL(2)將HDFS數(shù)據(jù)導(dǎo)出到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：指定待導(dǎo)出的HDFS數(shù)據(jù)文件的目錄。--input-fields-terminated-by'\t'：指定HDFS文件的字段分隔符為“\t”。--input-lines-terminated-by'\n'：指定HDFS文件的行分隔符為“\n”。

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

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