數(shù)據(jù)倉庫Hive應(yīng)用實戰(zhàn)- 課件 模塊7 遷移平臺數(shù)據(jù)方法

理解Sqoop數(shù)據(jù)遷移原理能熟練部署配置Sqoop能熟練應(yīng)用Sqoop常用命令理解并熟記Sqoop導(dǎo)入導(dǎo)出命令的常用參數(shù)

Hadoop是一個越來越通用的分布式計算環(huán)境，主要用來處理大數(shù)據(jù)。隨云計算、大數(shù)據(jù)的發(fā)展，更多的用戶將數(shù)據(jù)集在Hadoop和傳統(tǒng)數(shù)據(jù)庫之間轉(zhuǎn)移，能夠幫助數(shù)據(jù)傳輸?shù)墓ぞ咦兊酶又匾pacheSqoop就是這樣一款工具，可以在Hadoop和關(guān)系型數(shù)據(jù)庫之間轉(zhuǎn)移大量數(shù)據(jù)。

本任務(wù)以“大數(shù)據(jù)商業(yè)智能選址”項目為實操載體，完成理解Sqoop的原理和架構(gòu)、部署配置Sqoop等學(xué)習(xí)目標(biāo)。任務(wù)1部署配置Sqoop7.1.1Sqoop介紹Sqoop是SQL-to-Hadoop的簡稱，是用于在Hadoop和關(guān)系數(shù)據(jù)庫服務(wù)器之間傳送數(shù)據(jù)的工具，主要在Hadoop生態(tài)組件如Hive、Hbase、HDFS與傳統(tǒng)數(shù)據(jù)庫如MYSQL、Oracle、Postgres等之間進(jìn)行數(shù)據(jù)傳遞，應(yīng)用Sqoop工具可以將關(guān)系型數(shù)據(jù)庫中的數(shù)據(jù)導(dǎo)入Hadoop的HDFS中，也可以將HDFS中數(shù)據(jù)導(dǎo)出到關(guān)系型數(shù)據(jù)庫中。目前Sqoop版本有Sqoop1和Sqoop2。Sqoop1是指Sqoop1.4.x系列版本，Sqoop2是指Sqoop1.99.x系列版本，兩個版本不兼容。任務(wù)1部署配置Sqoop7.1.1Sqoop介紹Sqoop2相對Sqoop1在以下方面進(jìn)行了改進(jìn)：在架構(gòu)上，Sqoop2引入Sqoopserver，集中化管理connector等。在訪問方式上，Sqoop2除CLI控制臺訪問方式外，增加RESTAPI、JAVAAPI、WEBUI三種訪問方式。在安全性能上，sqoop2中引入了基于角色的安全機制，管理員可以在sqoopServer上配置不同的角色。通過CLI方式訪問時，新增交互過程界面，保障輸入的密碼信息不會暴漏。任務(wù)1部署配置Sqoop7.1.2Sqoop架構(gòu)Sqoop是連接關(guān)系型數(shù)據(jù)庫和Hadoop的橋梁，主要包括導(dǎo)入和導(dǎo)出兩個功能。導(dǎo)入：將關(guān)系型數(shù)據(jù)庫的數(shù)據(jù)導(dǎo)入Hadoop及其相關(guān)的系統(tǒng)中，如HDFS、Hive、Hbase。導(dǎo)出：將數(shù)據(jù)從Hadoop系統(tǒng)中抽取并導(dǎo)出到關(guān)系型數(shù)據(jù)庫，如MYSQL、Oracle。任務(wù)1部署配置Sqoop7.1.2Sqoop架構(gòu)Sqoop可以高效、可控地利用資源，可以通過調(diào)整任務(wù)數(shù)來控制任務(wù)的并發(fā)度，可以自動完成數(shù)據(jù)映射和轉(zhuǎn)換。Sqoop工作流程如下圖所示：任務(wù)1部署配置Sqoop7.1.2Sqoop架構(gòu)Sqoop架構(gòu)部署簡單、使用方便，但也存在一些缺點，如命令行方式容易出錯、格式緊耦合、無法支持所有數(shù)據(jù)類型、安全機制不夠完善、安裝需要root權(quán)限、connector必須符合JDBC模型等。任務(wù)1部署配置SqoopSqoop是apache的頂級項目，是一款開源工具，我們可以直接在官網(wǎng)下載Sqoop進(jìn)行安裝配置，本任務(wù)實施依次完成部署Sqoop、配置Sqoop等操作，為實現(xiàn)數(shù)據(jù)遷移準(zhǔn)備好環(huán)境。任務(wù)1部署配置SqoopSqoop的部署與安裝7.1.3部署Sqoop1、下載安裝包Sqoop相關(guān)發(fā)行版本可以通過其官網(wǎng)/dist/sqoop/下載，本書使用1.4.7版本，包文件名為：sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz。任務(wù)1部署配置Sqoop7.1.3部署Sqoop2、上傳安裝文件使用secureFX工具將下載的Sqoop文件sqoop-1.4.7.bin__hadoop-2.6.0.tar上傳至hive服務(wù)器的hadoop用戶家目錄下的software中。[hadoop@hive~]$ll/home/hadoop/software/|grepsqoop-rw-rw-r--.1hadoophadoop179536046月2108:46sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz任務(wù)1部署配置Sqoop7.1.3部署Sqoop3、解壓文件使用tar命令解壓sqoop-1.4.7.bin__hadoop-2.6.0.tar至hadoop用戶家目錄下server中并重命名：[hadoop@hivesoftware]$tar-zxvfsqoop-1.4.7.bin__hadoop-2.6.0.tar.gz-C~/server/[hadoop@hive~]$mv~/server/sqoop-1.4.7.bin__hadoop-2.6.0/~/server/sqoop-1.4.7/任務(wù)1部署配置Sqoop7.1.4配置Sqoop第1步，在/etc/profile文件中設(shè)置Sqoop環(huán)境變量并生效。任務(wù)1部署配置Sqoop[hadoop@hive~]$sudovi/etc/profileexportSQOOP_HOME=/home/hadoop/server/sqoop-1.4.7exportPATH=$PATH:$JAVA_HOME/bin:$HADOOP_HOME/sbin:$HADOOP_HOME/bin:$HIVE_HOME/bin:$SQOOP_HOME/bin[hadoop@hive~]$source/etc/profile7.1.4配置Sqoop第2步，配置$SQOOP_HOME下bin/configure-sqoop文件。修改bin/configure-sqoop配置文件，注釋文件中關(guān)于HBase、Zookeeper等檢查。任務(wù)1部署配置Sqoop7.1.4配置Sqoop第3步，配置$SQOOP_HOME下conf/sqoop-env.sh文件。當(dāng)不存在sqoop-env.sh文件時，復(fù)制sqoop-env-template.sh文件，然后修改為sqoop-env.sh配置文件，并添加配置信息，如下所示：任務(wù)1部署配置Sqoop[hadoop@hive~]$cd/home/hadoop/server/sqoop-1.4.7/conf[hadoop@hiveconf]$cpsqoop-env-template.shsqoop-env.sh[hadoop@hiveconf]$visqoop-env.shexportHADOOP_COMMON_HOME=/home/hadoop/server/hadoop-2.9.2exportHADOOP_MAPRED_HOME=/home/hadoop/server/hadoop-2.9.2exportHIVE_HOME=/home/hadoop/server/apache-hive-2.3.4-bin7.1.4配置Sqoop第4步，驗證Sqoop版本信息。任務(wù)1部署配置Sqoop[hadoop@hive~]$sqoopversion21/08/1905:58:23INFOsqoop.Sqoop:RunningSqoopversion:1.4.7Sqoop1.4.7gitcommitid2328971411f57f0cb683dfb79d19d4d19d185dd8CompiledbymauglionThuDec2115:59:58STD20177.1.4配置Sqoop第5步，將mysql驅(qū)動包拷貝到$SQOOP_HOME的lib目錄下。任務(wù)1部署配置Sqoop[hadoop@hivelib]$ll/home/hadoop/server/sqoop-1.4.7/lib|grepmysql-rw-rw-r--.1hadoophadoop10048402月262018mysql-connector-java-5.1.46-bin.jar7.1.4配置Sqoop第6步，測試連接MySQL，通過sqooplist-databases命令查詢本地MySQL中所有數(shù)據(jù)庫。任務(wù)1部署配置Sqoop[hadoop@hivelib]$sqooplist-databases--connectjdbc:mysql://0:3306/--usernameroot-P7.1.4配置Sqoop第7步，Sqoop連接MySQL成功任務(wù)1部署配置Sqoop

本任務(wù)以“大數(shù)據(jù)商業(yè)智能選址”項目為實操載體，完成熟練Sqoop常用命令、理解Sqoop數(shù)據(jù)遷移方式、將Hive中數(shù)據(jù)導(dǎo)出到MySQL、將MySQL表數(shù)據(jù)導(dǎo)入Hive等學(xué)習(xí)目標(biāo)。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)7.2.1Sqoop常用命令Sqoop工具提供了一系列命令進(jìn)行數(shù)據(jù)遷移操作，通過幫助命令sqoophelp查看所支持的所有命令，如下圖所示：任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)7.2.1Sqoop常用命令任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)命令功能描述list-databases列出所有數(shù)據(jù)庫名list-tables列出某個數(shù)據(jù)庫下所有表import將關(guān)系型數(shù)據(jù)庫中數(shù)據(jù)導(dǎo)入到Hadoopexport將Hadoop中數(shù)據(jù)導(dǎo)出到關(guān)系型數(shù)據(jù)庫create-hive-table創(chuàng)建Hive表import-all-tables導(dǎo)入某個數(shù)據(jù)庫下所有表到HDFS中merge將HDFS中不同目錄下面的數(shù)據(jù)合在一起，并存放在指定的目錄中help打印sqoop幫助信息version打印sqoop版本信息7.2.2Sqoop數(shù)據(jù)遷移方式1.Sqoop數(shù)據(jù)導(dǎo)入

Sqoop數(shù)據(jù)導(dǎo)入是指從非大數(shù)據(jù)集群（RDBMS）向大數(shù)據(jù)集群（HDFS、HIVE、HBASE）等具有分布式存儲結(jié)構(gòu)的文件系統(tǒng)中傳輸數(shù)據(jù)，表中的每一行被視為一條記錄，所有記錄默認(rèn)以文本文件格式進(jìn)行逐行存儲。

Sqoop實現(xiàn)數(shù)據(jù)導(dǎo)入采用import命令。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)7.2.2Sqoop數(shù)據(jù)遷移方式2.Sqoop數(shù)據(jù)導(dǎo)出

Sqoop數(shù)據(jù)導(dǎo)出是指從大數(shù)據(jù)集群（HDFS、HIVE、HBASE）等分布式文件系統(tǒng)或數(shù)據(jù)倉庫向非大數(shù)據(jù)集群（RDBMS）傳輸數(shù)據(jù)。

Sqoop工具使用export命令完成數(shù)據(jù)導(dǎo)出操作。在導(dǎo)出操作之前，目標(biāo)表必須存在于目標(biāo)數(shù)據(jù)庫中，否則會數(shù)據(jù)導(dǎo)出失敗。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)前面我們已經(jīng)完成了Sqoop的安裝配置，本任務(wù)實施依次完成將Hive中數(shù)據(jù)遷移導(dǎo)出到MySQL、將MySQL中數(shù)據(jù)遷移導(dǎo)入到Hive等操作，最終熟練掌握數(shù)據(jù)的遷移。Sqoop數(shù)據(jù)遷移任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)7.2.3將Hive中數(shù)據(jù)遷移導(dǎo)出到MySQL1、將Hive中非分區(qū)表數(shù)據(jù)遷移導(dǎo)出到MySQL【例】將Hive中ods_site庫的ods_bts_industry表數(shù)據(jù)遷移到MySQL中。

從Hive遷移數(shù)據(jù)到MySQL前，必須在MySQL中提前創(chuàng)建好目標(biāo)表，且該表結(jié)構(gòu)應(yīng)與Hive中對應(yīng)表的元數(shù)據(jù)結(jié)構(gòu)一致。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)7.2.3將Hive中數(shù)據(jù)遷移導(dǎo)出到MySQL第1步，在MySQL中創(chuàng)建數(shù)據(jù)庫mysql_ods_site，在MySQL中執(zhí)行以下代碼。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)7.2.3將Hive中數(shù)據(jù)遷移導(dǎo)出到MySQL第2步，在數(shù)據(jù)庫mysql_ods_site中創(chuàng)建數(shù)據(jù)表mysql_ods_bts_industry，用于存儲從Hive的ods_bts_industry表中遷移來的數(shù)據(jù)，在MySQL中執(zhí)行以下代碼。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)7.2.3將Hive中數(shù)據(jù)遷移導(dǎo)出到MySQL第3步，從Hive中ods_site庫的ods_bts_industry表數(shù)據(jù)遷移到MySQL的mysql_ods_site庫的mysql_ods_bts_industry表，在Linux中執(zhí)行以下命令。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)7.2.3將Hive中數(shù)據(jù)遷移導(dǎo)出到MySQL第4步，驗證數(shù)據(jù)自Hive遷移到MySQL。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)7.2.3將Hive中數(shù)據(jù)遷移導(dǎo)出到MySQL2、將Hive中分區(qū)表數(shù)據(jù)遷移導(dǎo)出到MySQL【例】將Hive中ods_site庫的分區(qū)表ods_resident_pop中分區(qū)字段為month=201805的數(shù)據(jù)遷移到MySQL中。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)7.2.3將Hive中數(shù)據(jù)遷移導(dǎo)出到MySQL第1步，在數(shù)據(jù)庫mysql_ods_site中創(chuàng)建數(shù)據(jù)表mysql_ods_resident_pop_201805，用于存儲從Hive的ods_resident_pop分區(qū)表中分區(qū)字段month=201805遷移來的數(shù)據(jù)，在MySQL中執(zhí)行以下代碼。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)7.2.3將Hive中數(shù)據(jù)遷移導(dǎo)出到MySQL第2步，從Hive中ods_site庫的分區(qū)表ods_resident_pop分區(qū)字段為month=201805的數(shù)據(jù)遷移到mysql_ods_site庫的mysql_ods_resident_pop_201805表，在Linux中執(zhí)行以下命令。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)$sqoopexport\--connectjdbc:mysql://0:3306/mysql_ods_site\--usernameroot\-P\--tablemysql_ods_resident_pop_201805\--num-mappers1\--export-dir/project/warehouse/intelligent_site/ods_site.db/ods_resident_pop/month=201805\--input-fields-terminated-by"\0001"7.2.3將Hive中數(shù)據(jù)遷移導(dǎo)出到MySQL第3步，驗證數(shù)據(jù)自Hive遷移到MySQL。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)7.2.4

將MySql中數(shù)據(jù)遷移導(dǎo)入到Hive1MySQL表數(shù)據(jù)全量導(dǎo)入Hive將MySQL中mysql_ods_site庫中mysql_ods_bts_industry表的數(shù)據(jù)遷移到Hive中ods_site庫的mysql_ods_bts_industry_all表中。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)7.2.4

將MySql中數(shù)據(jù)遷移導(dǎo)入到Hive第1步，執(zhí)行以下數(shù)據(jù)遷移導(dǎo)入命令。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)7.2.4

將MySql中數(shù)據(jù)遷移導(dǎo)入到Hive第2步，驗證數(shù)據(jù)是否從MySQL全量遷移導(dǎo)入到Hive表。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)7.2.4

將MySql中數(shù)據(jù)遷移導(dǎo)入到Hive2MySQL表數(shù)據(jù)篩選子集導(dǎo)入Hive將MySQL中mysql_ods_site庫中mysql_ods_bts_industry表中“行業(yè)數(shù)量”大于零的數(shù)據(jù)遷移到Hive中ods_site庫的mysql_ods_bts_industry_numgt0表中。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)7.2.4

將MySql中數(shù)據(jù)遷移導(dǎo)入到Hive第1步，執(zhí)行以下數(shù)據(jù)遷移導(dǎo)入命令。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)$sqoopimport\--connectjdbc:mysql://0:3306/mysql_ods_site\--usernameroot\-P\--tablemysql_ods_bts_industry\--num-mappers1\--hive-tableods_site.mysql_ods_bts_industry_numgt0\--create-hive-table\--hive-import\--where"num>0"7.2.4

將MySql中數(shù)據(jù)遷移導(dǎo)入到Hive第2步，驗證滿足過濾條件的數(shù)據(jù)子集是否從MySQL遷移導(dǎo)入到Hive表。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)7.2.4

將MySql中數(shù)據(jù)遷移導(dǎo)入到Hive3MySQL表數(shù)據(jù)查詢子集導(dǎo)入Hive將MySQL中mysql_ods_site庫中mysql_ods_bts_industry表中“行業(yè)數(shù)量”大于零的“bts_id、num”兩列數(shù)據(jù)遷移到Hive中ods_site庫的mysql_ods_bts_industry_numgt0_nocode表中。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)7.2.4

將MySql中數(shù)據(jù)遷移導(dǎo)入到Hive第1步，執(zhí)行以下數(shù)據(jù)遷移導(dǎo)入命令。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)$sqoopimport\--connectjdbc:mysql://0:3306/mysql_ods_site\--usernameroot\-P\--num-mappers1\--hive-tableods_site.mysql_ods_bts_industry_numgt0_nocode\--create-hive-table\--target-dir/project/warehouse/intelligent_site/ods_site.db/mysql_ods_bts_industry_numgt0_nocode\--hive-import\--query'SELECTbts_id,numFROMmysql_ods_bts_industryWHEREnum>0AND$CONDITIONS'7.2.4

將MySql中數(shù)據(jù)遷移導(dǎo)入到Hive第2步，驗證滿足查詢條件的數(shù)據(jù)子集是否從MySQL遷移導(dǎo)入到Hive表。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)7.2.4

將MySql中數(shù)據(jù)遷移導(dǎo)入到Hive4.將MySQL表數(shù)據(jù)全量導(dǎo)入到Hive分區(qū)表將MySQL中mysql_ods_site庫中mysql_ods_resident_pop_201805表數(shù)據(jù)遷移到Hive中ods_site庫的分區(qū)表mysql_ods_resident_pop_1的month=201805的分區(qū)字段中。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)7.2.4

將MySql中數(shù)據(jù)遷移導(dǎo)入到Hive第1步，執(zhí)行以下數(shù)據(jù)遷移導(dǎo)入命令。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)$sqoopimport\--connectjdbc:mysql://0:3306/mysql_ods_site\--usernameroot\-P\--tablemysql_ods_resident_pop_201805\--num-mappers1\--hive-databaseods_site\--hive-tablemysql_ods_resident_pop_1\--hive-partition-keymonth\--hive-partition-value201805\--create-hive-table\--hive-import7.2.4

將MySql中數(shù)據(jù)遷移導(dǎo)入到Hive第2步，查看分區(qū)表mysql_ods_resident_pop_1的表結(jié)構(gòu)。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)7.2.4

將MySql中數(shù)據(jù)遷移導(dǎo)入到Hive第3步，查看分區(qū)表mysql_ods_resident_pop_1的數(shù)據(jù)。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)7.2.4

將MySql中數(shù)據(jù)遷移導(dǎo)入到Hive5.將MySQL表數(shù)據(jù)子集導(dǎo)入到Hive分區(qū)表將MySQL中mysql_ods_site庫中mysql_ods_resident_pop_201805表中前3條數(shù)據(jù)遷移到Hive中ods_site庫的分區(qū)表mysql_ods_resident_pop_2的month=201806的分區(qū)字段中，表中第4/5/6條數(shù)據(jù)遷移到Hive中ods_site庫的分區(qū)表mysql_ods_resident_pop_2的month=201807的分區(qū)字段中。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)7.2.4

將MySql中數(shù)據(jù)遷移導(dǎo)入到Hive第1步，執(zhí)行以下數(shù)據(jù)遷移命令，導(dǎo)入前3條數(shù)據(jù)到表分區(qū)month=201806中。任務(wù)2應(yīng)用Sqoop遷移數(shù)據(jù)$sqoopimport\--connectjdbc:mysql://0:3306/mysql_ods_site\--usernameroot\-P\--query'SELECT*FROMmysql_ods_resident_pop_201805WHERE$CONDITIONSlimit0,3'\--target-dir/project/warehouse/intelligent_site/ods_site.db/mysql_ods_resident_pop_2/month=201806\--num-mappers1\--hive-databaseods_site\--hive-tablemysql_ods_resident_pop_2\--hive