大數(shù)據(jù)技術(shù)與應(yīng)用基礎(chǔ)項(xiàng)目教程課件Sqoop數(shù)據(jù)遷移

主編:李俊杰謝志明副主編:肖政宏石慧謝高輝楊澤強(qiáng)出版社:《大數(shù)據(jù)技術(shù)與應(yīng)用》

——基礎(chǔ)項(xiàng)目教程項(xiàng)目八Sqoop數(shù)據(jù)遷移任務(wù)一Sqoop安裝及MySQL與HDFS數(shù)據(jù)遷移任務(wù)二MySQL與Hive/HBase數(shù)據(jù)轉(zhuǎn)移任務(wù)一Sqoop安裝及MySQL與HDFS數(shù)據(jù)遷移任務(wù)概述本任務(wù)需要完成Sqoop地安裝,使用SqoopImport與SqoopExport完成MySQL與HDFS之間數(shù)據(jù)地轉(zhuǎn)移,并檢查其結(jié)果。支撐知識(shí) 一,Sqoop簡(jiǎn)介二,Sqoop地基本命令 任務(wù)實(shí)施一,客戶端主機(jī)安裝Sqoop軟件二,安裝MySQL-Client軟件包三,MySQL與HDFS數(shù)據(jù)遷移任務(wù)一Sqoop安裝及MySQL與HDFS數(shù)據(jù)遷移

支撐知識(shí)一,Sqoop簡(jiǎn)介Sqoop是一個(gè)用來將Hadoop與關(guān)系型數(shù)據(jù)庫地?cái)?shù)據(jù)相互轉(zhuǎn)移地工具,可以將一個(gè)關(guān)系型數(shù)據(jù)庫（如:MySQL,Oracle,Postgres等）地?cái)?shù)據(jù)導(dǎo)入到HDFS,也可以將HDFS地?cái)?shù)據(jù)導(dǎo)出到關(guān)系型數(shù)據(jù)庫。任務(wù)一Sqoop安裝及MySQL與HDFS數(shù)據(jù)遷移

支撐知識(shí)二,Sqoop地基本命令Sqoop提供一系列命令,包括導(dǎo)入操作（import）,導(dǎo)出操作（export）,導(dǎo)入所有表（import-all-tables）,列出所有數(shù)據(jù)庫實(shí)例（list-databases）與列出數(shù)據(jù)庫實(shí)例所有表（list-tables）等。下面列舉一些Sqoop常用命令:（一）列出MySQL地所有數(shù)據(jù)庫sqooplist-databases--connectjdbc:mysql://IP:三三零六/--username用戶--password密碼（二）連接MySQL并列出數(shù)據(jù)庫地表sqooplist-tables--connectjdbc:mysql://IP:三三零六/數(shù)據(jù)庫--username用戶--password密碼（三）MySQL地表數(shù)據(jù)導(dǎo)入到HDFSsqoopimport--connectjdbc:mysql://IP:三三零六/數(shù)據(jù)庫--username用戶--password密碼--table表--num-mappersMap任務(wù)數(shù)--target-dirHDFS存放位置參數(shù)說明:--num-mappers或-m選項(xiàng)指定Map任務(wù)個(gè)數(shù),--target-dir選項(xiàng)指定HDFS存放位置。任務(wù)一Sqoop安裝及MySQL與HDFS數(shù)據(jù)遷移

支撐知識(shí)（四）HDFS導(dǎo)出到MySQL表sqoopexprot--connectjdbc:mysql://IP:三三零六/數(shù)據(jù)庫--username用戶--password密碼--table表--num-mappersMap任務(wù)數(shù)--export-dirHDFS存放位置（五）將MySQL地表結(jié)構(gòu)復(fù)制到Hivesqoopcreate-Hive-table--connectjdbc:mysql:/IP:三三零六/--username用戶--password密碼--table表--hive-tablehive表--fields-terminated-by"\零零零一"--lines-terminated-by"\n"參數(shù)說明:--fields-terminated-by"\零零零一"是設(shè)置每列之間地分隔符,"\零零零一"是ASCII碼地一,它也是hive地默認(rèn)行內(nèi)分隔符,而sqoop地默認(rèn)行內(nèi)分隔符為",",--lines-terminated-by"\n"設(shè)置地是每行之間地分隔符,此處為換行符,也是默認(rèn)地分隔符。（六）將數(shù)據(jù)從MySQL導(dǎo)入文件到Hive表sqoopimport--connectjdbc:mysql:/IP:三三零六/--username用戶--password密碼--table表--hive-import--hive-tablehive表--num-mappersMap任務(wù)數(shù)--fields-terminated-by"\零零零一";參數(shù)說明:--fields-terminated-by"\零零零一"需同創(chuàng)建hive表時(shí)保持一致。任務(wù)一Sqoop安裝及MySQL與HDFS數(shù)據(jù)遷移

支撐知識(shí)（七）將Hive地表數(shù)據(jù)導(dǎo)入到MySQL表sqoopexport--connectjdbc:mysql://IP:三三零六/數(shù)據(jù)庫--username用戶--password密碼--table表--export-dirHDFS文件--input-fields-terminated-by'\零零零一'（八）使用--query語句將數(shù)據(jù)從MySQL導(dǎo)入文件到Hive表sqoopimport--append--connectjdbc:mysql://IP:三三零六/數(shù)據(jù)庫--username用戶--password密碼--query"select…from…"--num-mappersMap任務(wù)數(shù)--target-dirHDFS存放位置--fields-terminated-by","（九）使用--columns--where語句將數(shù)據(jù)從MySQL導(dǎo)入到Hive表sqoopimport--append--connectjdbc:mysql://IP:三三零六/數(shù)據(jù)庫--username--用戶--password密碼--table表--columns"列名"--where"條件"--num-mappersMap任務(wù)數(shù)--target-dirHDFS存放位置--fields-terminated-by","任務(wù)一Sqoop安裝及MySQL與HDFS數(shù)據(jù)遷移任務(wù)實(shí)施

一,客戶端主機(jī)安裝Sqoop軟件（一）下載Sqoop軟件包到/home/hadoop目錄下,網(wǎng)址如下:https://mirrors.aliyun./apache/sqoop/一.四.六/sqoop-一.四.六.bin__hadoop-二.零.四-alpha.tar.gz（二）安裝Sqoop軟件hadoop@sw-desktop:~$cd/opthadoop@...$sudotarxvzf/home/hadoop/sqoop-一.四.六.bin__hadoop-二.零.四-alpha.tar.gzhadoop@...$sudochown-Rhadoop:hadoopsqoop-一.四.六.bin__hadoop-二.零.四-alpha任務(wù)一Sqoop安裝及MySQL與HDFS數(shù)據(jù)遷移任務(wù)實(shí)施

（三）修改Sqoop配置參數(shù)hadoop@...$cd/opt/sqoop-一.四.六.bin__hadoop-二.零.四-alpha/confhadoop@...$visqoop-env.sh添加如下內(nèi)容:exportHADOOP_MON_HOME=/opt/hadoop-二.七.三exportHADOOP_MAPRED_HOME=/opt/hadoop-二.七.三exportHBASE_HOME=/opt/hbase-一.二.四exportHIVE_HOME=/opt/apache-hive-二.一.一-binexportHCAT_HOME=/opt/apache-hive-二.一.一-bin/hcatalogexportZOOCFGDIR=/opt/zookeeper-三.四.九任務(wù)一Sqoop安裝及MySQL與HDFS數(shù)據(jù)遷移任務(wù)實(shí)施

（四）修改環(huán)境變量hadoop@...$vi/home/hadoop/.profile添加如下內(nèi)容:exportSQOOP_HOME=/opt/sqoop-一.四.六.bin__hadoop-二.零.四-alphaexportPATH=$PATH:$SQOOP_HOME/bin（五）環(huán)境變量生效hadoop@...$source/home/hadoop/.profile六,復(fù)制mysql-connector-java-五.一.四零.jarhadoop@...$cp/home/hadoop/mysql-connector-java-五.一.四零.jar\/opt/sqoop-一.四.六.bin__hadoop-二.零.四-alpha/lib任務(wù)一Sqoop安裝及MySQL與HDFS數(shù)據(jù)遷移任務(wù)實(shí)施

二,安裝MySQL-Client軟件包（一）在客戶端主機(jī)安裝MySQL-Clienthadoop@sw-desktop:~$sudoapt-getinstallmysql-client（二）MySQL用戶授權(quán)hadoop@master:~$mysql-uroot-p一二三四五六mysql>GRANTALLPRIVILEGESON*.*TO'sqoop'@'%'IDENTIFIEDBY'一二三四五六';mysql>GRANTALLPRIVILEGESON*.*TO'sqoop'@'localhost'IDENTIFIEDBY'一二三四五六';（三）創(chuàng)建數(shù)據(jù)hadoop@sw-desktop:~$mysql-hmaster-usqoop-p一二三四五六mysql>createdatabasesqoop;mysql>usesqoop;mysql>createtabledept(idint,namevarchar(二零),primarykey(id));mysql>insertintodeptvalues(六一零二一三,'云計(jì)算技術(shù)與應(yīng)用');mysql>insertintodeptvalues(六一零二一五,'大數(shù)據(jù)技術(shù)與應(yīng)用');mysql>insertintodeptvalues(五九零一零八,'軟件技術(shù)');任務(wù)一Sqoop安裝及MySQL與HDFS數(shù)據(jù)遷移任務(wù)實(shí)施

三,MySQL與HDFS數(shù)據(jù)遷移（一）查看MySQL數(shù)據(jù)庫hadoop@sw-desktop:~$sqooplist-databases--connectjdbc:mysql://master:三三零六/\--usernamesqoop--password一二三四五六（二）查看MySQL表hadoop@sw-desktop:~$sqooplist-tables--connectjdbc:mysql://master:三三零六/sqoop\--usernamesqoop--password一二三四五六（三）MySQL表導(dǎo)入到HDFShadoop@sw-desktop:~$sqoopimport--connectjdbc:mysql://master:三三零六/sqoop\--usernamesqoop--password一二三四五六--tabledept-m一--target-dir/user/dept（四）查看HDFShadoop@sw-desktop:~$hdfsdfs-ls/user/depthadoop@sw-desktop:~$hdfsdfs-cat/user/dept/part-m-零零零零零任務(wù)一Sqoop安裝及MySQL與HDFS數(shù)據(jù)遷移任務(wù)實(shí)施

（五）清空MySQL地dept表mysql>usesqoop;mysql>truncatedept;（六）數(shù)據(jù)從HDFS導(dǎo)出到MySQL表hadoop@sw-desktop:~$sqoopexport--connectjdbc:mysql://master:三三零六/sqoop\--usernamesqoop--password一二三四五六--tabledept-m一--export-dir/user/dept（七）查詢dept表mysql>select*fromdept;（八）SqoopImport增量導(dǎo)入到HDFS上mysql>insertintodeptvalues(五九零一零一,'計(jì)算機(jī)應(yīng)用技術(shù)');hadoop@sw-desktop:~$sqoopimport--connectjdbc:mysql://master:三三零六/sqoop\--usernamesqoop--password一二三四五六--tabledept-m一--target-dir/user/dept\--incrementalappend--check-columnidhadoop@sw-desktop:~$hdfsdfs-ls/user/depthadoop@sw-desktop:~$hdfsdfs-cat/user/dept/part-m-零零零零一任務(wù)二MySQL與Hive/HBase數(shù)據(jù)轉(zhuǎn)移任務(wù)概述Hive與HBase運(yùn)行在Hadoop非常重要地組件,Hive是數(shù)據(jù)倉庫,HBase是分布式數(shù)據(jù)庫,本任務(wù)主要完成MySQL與Hive之間以及MySQl與HBase之間數(shù)據(jù)轉(zhuǎn)移。任務(wù)實(shí)施一,MySQL與Hive之間數(shù)據(jù)轉(zhuǎn)移二,MySQL與HBase之間數(shù)據(jù)轉(zhuǎn)移任務(wù)二MySQL與Hive/HBase數(shù)據(jù)轉(zhuǎn)移

任務(wù)實(shí)施一,MySQL與Hive之間數(shù)據(jù)轉(zhuǎn)移（一）MySQL地dept表導(dǎo)入Hivehadoop@sw-desktop:~$sqoopimport--connectjdbc:mysql://master:三三零六/sqoop\--usernamesqoop--password一二三四五六--tabledept-m一--hive-import參數(shù)說明:--hive-import指定導(dǎo)入Hive,--hive-table指定Hive表名,--hive-overwrite可以覆蓋原Hive表,--create-hive-table可以將原來MySQL表結(jié)構(gòu)復(fù)制到Hive表。（二）查看Hive表數(shù)據(jù)hive>showtables;hive>select*fromdept;任務(wù)二MySQL與Hive/HBase數(shù)據(jù)轉(zhuǎn)移

任務(wù)實(shí)施（三）清空MySQL地dept表mysql>usesqoop;mysql>truncatedept;（四）Hive表數(shù)據(jù)導(dǎo)出到MySQLhadoop@sw-desktop:~$sqoopexport--connectjdbc:mysql://master:三三零六/sqoop\--usernamesqoop--password一二三四五六--tabledept-m一\--export-dir/hive/warehouse/dept