sqoop 安裝與配置使用

hive 與 mysql/pgsql 資料傳遞

sqoop 安裝

安裝包 上傳至 hive-server.

$ docker cp mysql-connector-java-5.1.28.jar dc387ff5c56d:/opt/mysql-connector-java-5.1.28.jar
$ docker cp sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz dc387ff5c56d:/opt/sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz

進入 hive-server 安裝 sqoop

$ docker exec -it dc387ff5c56d /bin/bash
# tar -zxvf sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz
# rm sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz 
# mv sqoop-1.4.6.bin__hadoop-2.0.4-alpha/ sqoop


# mv mysql-connector-java-5.1.28.jar sqoop/lib/mysql-connector-java-5.1.28.jar
# cp sqoop/conf/sqoop-env-template.sh sqoop/conf/sqoop-env.sh
# vim sqoop/conf/sqoop-env.sh

#Set the path to where bin/hive is available
export HIVE_HOME=/opt/hive/

sqoop 將sqoop 資料匯入

# /opt/sqoop/bin/sqoop list-databases --connect jdbc:mysql://192.168.20.204:3306/ --username ad_user --password 'dl_ad!@#123'


Warning: /opt/sqoop/bin/../../hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: /opt/sqoop/bin/../../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /opt/sqoop/bin/../../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
Warning: /opt/sqoop/bin/../../zookeeper does not exist! Accumulo imports will fail.
Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.
21/07/30 10:24:23 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6
21/07/30 10:24:23 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
21/07/30 10:24:23 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
addata
dl_ad
dl_college
e_statistics
i_commodity
i_marketing
i_member
i_operation
i_order
information_schema
log
mysql
performance_schema
test

在 hive 中建立 mysql 對應需要同步的資料庫

[work@Lan-Kvm-20157-Reptile2 ~]$ docker exec -it dc387ff5c56d /bin/bash
root@dc387ff5c56d:/opt# hive
hive> show databases;
OK
default
Time taken: 1.081 seconds, Fetched: 1 row(s)

# hive 建立對應資料庫
hive> create database i_member;
OK
Time taken: 0.155 seconds
hive> create database i_order;
OK
Time taken: 0.074 seconds
hive> create database i_commodity;
OK
Time taken: 0.074 seconds
hive> create database i_marketing;
OK
Time taken: 0.058 seconds
hive> create database i_operation;
OK
Time taken: 0.069 seconds

# 匯入 mysql 資料庫

資料匯入

$ /opt/sqoop/bin/sqoop import 
--connect jdbc:mysql://192.168.20.204:3306/i_member 
--username ad_user 
--password 'dl_ad!@#123' 
--table t_member 
--num-mappers 1 
--hive-import 
--fields-terminated-by "t" 
--hive-overwrite 
--hive-database i_member 
--hive-table t_member



21/08/01 08:48:01 INFO mapreduce.ImportJobBase: Transferred 0 bytes in 3.1657 seconds (0 bytes/sec)
21/08/01 08:48:01 INFO mapreduce.ImportJobBase: Retrieved 1000 records.
21/08/01 08:48:01 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `t_member` AS t LIMIT 1
21/08/01 08:48:01 WARN hive.TableDefWriter: Column create_time had to be cast to a less precise type in Hive
21/08/01 08:48:01 WARN hive.TableDefWriter: Column update_time had to be cast to a less precise type in Hive

21/08/01 08:48:01 INFO hive.HiveImport: Loading uploaded data into Hive

21/08/01 08:48:09 INFO hive.HiveImport: SLF4J: Class path contains multiple SLF4J bindings.
21/08/01 08:48:09 INFO hive.HiveImport: SLF4J: Found binding in [jar:file:/opt/hive/lib/log4j-slf4j-impl-2.4.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
21/08/01 08:48:09 INFO hive.HiveImport: SLF4J: Found binding in [jar:file:/opt/hadoop-2.7.1/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
21/08/01 08:48:09 INFO hive.HiveImport: SLF4J: See  for an explanation.
21/08/01 08:48:10 INFO hive.HiveImport: 
21/08/01 08:48:10 INFO hive.HiveImport: Logging initialized using configuration in file:/opt/hive/conf/hive-log4j2.properties Async: true
21/08/01 08:48:12 INFO hive.HiveImport: OK
21/08/01 08:48:12 INFO hive.HiveImport: Time taken: 1.583 seconds
21/08/01 08:48:13 INFO hive.HiveImport: Loading data to table i_member.t_member
21/08/01 08:48:15 INFO hive.HiveImport: OK
21/08/01 08:48:15 INFO hive.HiveImport: Time taken: 2.832 seconds
21/08/01 08:48:16 INFO hive.HiveImport: Hive import complete.


# 此時訪問 http://192.168.20.157:50070/explorer.html#/user/hive/warehouse/i_member.db 就可以檢視到 i_member 資料庫中 t_member 表

查詢資料驗證

hive> use i_member;
OK
Time taken: 0.04 seconds
hive> show tables;
OK
t_member
Time taken: 0.064 seconds, Fetched: 1 row(s)
hive> select * from t_member;
OK
1	bywind1	password	2	15321761517	1	2	k8fy5od-tunhgvlx	1	2019-11-10 09:00:00.0	2019-11-23 10:22:01.0
2	bywind2	password	1	15321761517	2	1	u694h5d2r0j-znib	1	2019-11-10 09:00:00.0	2019-11-29 10:22:01.0
3	bywind3	password	2	15321761517	3	1	_5836va1ck7hx9mt	1	2019-11-10 09:00:00.0	2019-11-22 10:22:01.0
4	bywind4	password	2	15321761517	4	3	t8a2wf_o3k0zdeiu	1	2019-11-10 09:00:00.0	2019-11-27 10:22:01.0
5	bywind5	password	2	15321761517	5	1	-g0m6qku4sfv97yn	1	2019-11-10 09:00:00.0	2019-11-23 10:22:01.0