This is the 19th day of my participation in the August More Text Challenge

If ❤️ my article is helpful, welcome to like, follow. This is the biggest encouragement for me to continue my technical creation. More previous articles in my personal column

Sqoop is installed and configured for use

Overview of sqOOP usage

Sqoop tool is an apache open Source Foundation tool to transfer synchronous data between Hadoop and relational database server. Core functions: Import and export

Import data: Import data from relational databases such as MySQL and Oracle to HDFS, HIVE, and HBASE data storage systems

Export data: Export data from the Hadoop file system to MySQL and Oracle

Sqoop implements data transfer between Hive and relational databases such as mysql/ PGSQL

Sqoop installation

Upload the sqOOP tool installation package to the Hive-server docker container.

$docker cp mysql-connector-java-5.1.28.jar dc387ff5c56d:/opt/mysql-connector- 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. GzCopy the code

Enter the Hive-server container and install the SQoop tool

$ 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/

Copy the code

Sqoop imports the data

# / opt/sqoop/bin/sqoop list - the 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. Pleaseset $HBASE_HOMEto the root of your HBase installation. Warning: /opt/sqoop/bin/.. /.. /hcatalog does not exist! HCatalogjobs will fail.
Please set $HCAT_HOMEto the root of your HCatalog installation. Warning: /opt/sqoop/bin/.. /.. /accumulo does not exist! Accumulo imports will fail. Pleaseset $ACCUMULO_HOMEto the root of your Accumulo installation. Warning: /opt/sqoop/bin/.. /.. /zookeeper does not exist! Accumulo imports will fail. Pleaseset $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

Copy the code

Create a mysql database to be synchronized in Hive

[work@Lan-Kvm-20157-Reptile2 ~]$ docker exec -it dc387ff5c56d /bin/bash
root@dc387ff5c56d:/opt# hivehive> show databases; OK default Time taken: 1.081 seconds, Touch_type: 1 row(s)# hive create database
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

# import mysql database


Copy the code

Start data import

$/ opt/sqoop/bin/sqoop import \ - connect JDBC: mysql: / / 192.168.20.204:3306 / i_member \ - username ad_user \ - the 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 the binding in [the jar: file: / opt/hive/lib/log4j - slf4j - impl - against 2.4.1. Jar! / org/slf4j/impl/StaticLoggerBinder class] 21/08/01 08:48:09 INFO hive.HiveImport: SLF4J: Found binding in [the 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 http://www.slf4j.org/codes.html#multiple_bindings 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. # visit http://192.168.20.157:50070/explorer.html#/user/hive/warehouse/i_member.db to view the i_member at this time T_member table in databaseCopy the code

Query data validation

hive> use i_member; OK Time taken: 0.04 seconds hive> show tables; OK t_member Time taken: 0.064 seconds, touch: 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 2 bywind2 Password 1 15321761517 21 U694H5D2R0J-ZNIB 1 2019-11-10 09:00:00:00 0 2019-11-29 10:22:01.0 3 BYwind3 password 2 15321761517 1 _15365vck7hx9mt 1 2019-11-10 09:00:00 0 2019-11-22 10:22:01 4 password 2 15321761517 4 3 T8a2wf_o3k0zdeiu 1 2019-11-10 09:00:00 0 2019-11-27 10:22:00:00 5 bywind5 password 2 15321761517 5 1-g0m6qku4sfv97yn 1 The 2019-11-10 09:00:00. 0 2019-11-23 10:22:01. 0Copy the code