HIVE中Sqoop1.4.6安裝、hive與oracle表互導(dǎo)的示例分析

這篇文章主要為大家展示了“HIVE中Sqoop1.4.6安裝、hive與oracle表互導(dǎo)的示例分析”，內(nèi)容簡(jiǎn)而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“HIVE中Sqoop1.4.6安裝、hive與oracle表互導(dǎo)的示例分析”這篇文章吧。

1. sqoop數(shù)據(jù)遷移

1.1 概述

sqoop是apache旗下一款“Hadoop和關(guān)系數(shù)據(jù)庫(kù)服務(wù)器之間傳送數(shù)據(jù)”的工具。 
導(dǎo)入數(shù)據(jù)：MySQL，Oracle導(dǎo)入數(shù)據(jù)到Hadoop的HDFS、HIVE、HBASE等數(shù)據(jù)存儲(chǔ)系統(tǒng)； 
導(dǎo)出數(shù)據(jù)：從Hadoop的文件系統(tǒng)中導(dǎo)出數(shù)據(jù)到關(guān)系數(shù)據(jù)庫(kù)

1.2 工作機(jī)制

將導(dǎo)入或?qū)С雒罘g成mapreduce程序來(lái)實(shí)現(xiàn) 
在翻譯出的mapreduce中主要是對(duì)inputformat和outputformat進(jìn)行定制

1.3 sqoop實(shí)戰(zhàn)及原理

1.3.1 sqoop安裝

安裝sqoop的前提是已經(jīng)具備java和hadoop的環(huán)境 
1、下載并解壓 
最新版下載地址http://ftp.wayne.edu/apache/sqoop/1.4.6/ 
比如：sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz

[root@hadoop1 sqoop]# tar -zxvf sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz
[root@hadoop1 sqoop]# mv sqoop-1.4.6.bin__hadoop-2.0.4-alpha sqoop
[root@hadoop1 sqoop]# ls
apache-hive-1.2.1-bin hadoop-2.7.4 hdfs pig-0.17.0 pig_1517170893185.log sqoop tmp

2、修改配置文件 
在/etc/profile中配置sqoop_home,代碼如下：

vim /etc/profile
export SQOOP_HOME=/usr/local/hadoop/sqoop
追加path
export PATH=$PATH:$SQOOP_HOME/bin
[root@hadoop1 sqoop]# source /etc/profile
$ cd $SQOOP_HOME/conf
$ mv sqoop-env-template.sh sqoop-env.sh

打開sqoop-env.sh并編輯下面幾行： ## 去掉前面的##

export HADOOP_COMMON_HOME=/usr/local/hadoop/hadoop-2.7.4/
export HADOOP_MAPRED_HOME=/usr/local/hadoop/hadoop-2.7.4/
export HIVE_HOME=/usr/local/hadoop/apache-hive-1.2.1-bin/

配置后的界面效果如下： 
這里寫圖片描述

3.1 加入oracle的驅(qū)動(dòng)包
將 ojdbc6.jar 放到 $SQOOP_HOME/lib/ 下。

3.2 加入mysql的jdbc驅(qū)動(dòng)包 
將mysql-connector-java-5.1.38.jar 放到 $SQOOP_HOME/lib/ 下。

4、驗(yàn)證啟動(dòng)

$ cd $SQOOP_HOME/bin
$ sqoop-version

預(yù)期的輸出：

[root@hadoop1 sqoop]# sqoop-version
Warning: /usr/local/hadoop/sqoop/../hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: /usr/local/hadoop/sqoop/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /usr/local/hadoop/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
Warning: /usr/local/hadoop/sqoop/../zookeeper does not exist! Accumulo imports will fail.
Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.
18/01/29 19:09:34 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6
Sqoop 1.4.6
git commit id c0c5a81723759fa575844a0a1eae8f510fa32c25
Compiled by root on Mon Apr 27 14:38:36 CST 2015
[root@hadoop1 sqoop]#

到這里，整個(gè)Sqoop安裝工作完成。

數(shù)據(jù)遷移> oracle to hive ## 注意 HIVE 表名需要大寫
sqoop# sqoop import --hive-import --connect jdbc:oracle:thin:@10.100.25.8:1521:devdb1 --username andy --password andy \
--table ANDY --hive-database oracletohive --hive-table ANDY -m 1
說(shuō)明： 遷移的表時(shí)，如果 hive中已經(jīng)存在，則默認(rèn)會(huì)追加在原表中。 如果 hive 中不存在，則自動(dòng)創(chuàng)建。

日志輸出：
18/01/29 19:35:46 INFO hive.HiveImport: Loading uploaded data into Hive
18/01/29 19:35:51 INFO hive.HiveImport: 
18/01/29 19:35:51 INFO hive.HiveImport: Logging initialized using configuration in jar:file:/usr/local/hadoop/apache-hive-1.2.1-bin/lib/hive-common-1.2.1.jar!/hive-log4j.properties
18/01/29 19:36:02 INFO hive.HiveImport: OK
18/01/29 19:36:02 INFO hive.HiveImport: Time taken: 2.42 seconds
18/01/29 19:36:03 INFO hive.HiveImport: Loading data to table oracletohive.andy
18/01/29 19:36:04 INFO hive.HiveImport: Table oracletohive.andy stats: [numFiles=1, totalSize=1996]
18/01/29 19:36:04 INFO hive.HiveImport: OK
18/01/29 19:36:04 INFO hive.HiveImport: Time taken: 1.579 seconds
18/01/29 19:36:04 INFO hive.HiveImport: Hive import complete.
18/01/29 19:36:04 INFO hive.HiveImport: Export directory is contains the _SUCCESS file only, removing the directory.

> show databases;
OK
default
oracletohive
Time taken: 0.027 seconds, Fetched: 2 row(s)
hive> 
> use oracletohive;
OK
Time taken: 0.034 seconds
hive> 
> show tables;
OK
andy
Time taken: 0.037 seconds, Fetched: 1 row(s)
hive> select count(*) from andy;
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2018-01-29 19:43:46,040 Stage-1 map = 0%, reduce = 0%
2018-01-29 19:43:54,738 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.49 sec
2018-01-29 19:44:03,323 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 3.39 sec
MapReduce Total cumulative CPU time: 3 seconds 390 msec
Ended Job = job_1517222580457_0002
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1 Reduce: 1 Cumulative CPU: 3.39 sec HDFS Read: 16343 HDFS Write: 2 SUCCESS
Total MapReduce CPU Time Spent: 3 seconds 390 msec
OK
7
Time taken: 34.543 seconds, Fetched: 1 row(s)

數(shù)據(jù)遷移> hive to oracle

hive遷移oracle，需要提前在oracle中創(chuàng)建遷移的表，否則報(bào) java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

sqoop# sqoop export --table ANDY --connect jdbc:oracle:thin:@10.100.25.8:1521:devdb1 --username andy --password andy 
--export-dir /user/hive/warehouse/oracletohive.db/andy --input-fields-terminated-by '\001' \
--input-lines-terminated-by '\n'

日志輸出：
18/01/29 20:21:34 INFO mapreduce.Job: Job job_1517222580457_0005 completed successfully
18/01/29 20:21:34 INFO mapreduce.Job: Counters: 30
。。。。。 省略輸出
18/01/29 20:21:34 INFO mapreduce.ExportJobBase: Transferred 5.502 KB in 116.7414 seconds (48.2605 bytes/sec)
18/01/29 20:21:34 INFO mapreduce.ExportJobBase: Exported 7 records.

-- oracle端查看
SQL> select count(*) from andy;

COUNT(*)
----------
14 > 由 7條 變?yōu)榱?14條 , 說(shuō)明 hive 導(dǎo)入 oracle 成功！

以上是“HIVE中Sqoop1.4.6安裝、hive與oracle表互導(dǎo)的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！