sqoop installation deployment

The prerequisite for installing sqoop is that you already have a Java and Hadoop environment.

Download address: https://www.apache.org/dyn/closer.lua/sqoop/

Here we use: sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz

1. sqoop installation and configuration

(1) Upload the installation package sqoop-1.4.7.bin_hadoop-2.6.0.tar.gz to the virtual machine

(2) Enter the uploaded installation package directory and unzip the sqoop installation package to the specified directory, such as:

tar -zxvf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz -C /opt/module/

After decompression, enter the extracted directory and change the name to sqoop

mv sqoop-1.4.7.bin__hadoop-2.6.0 sqoop

2. Set the sqoop environment variable

Command:

vi /root/.bash_profile

Add the following:

export SQOOP_HOME=/opt/module/sqoop
export PATH=$PATH:$SQOOP_HOME/bin

Make settings effective immediately:

source /root/.bash_profile

3. Modify the configuration file

The sqoop configuration file is similar to most big data frameworks. It is in the conf directory under the sqoop root directory.

Enter conf under sqoop

Command:

cd /opt/module/sqoop/conf

(1) Rename the configuration file sqoop-env-template.sh

mv sqoop-env-template.sh sqoop-env.sh

(2) Modify the configuration file sqoop-env.sh

Command:

vi sqoop-env.sh

Modify the following:

export HADOOP_COMMON_HOME=/opt/module/hadoop		//Hadoop installation directory
export HADOOP_MAPRED_HOME=/opt/module/hadoop		//Hadoop installation directory
export HIVE_HOME=/opt/module/hive					//hive installation directory
export ZOOKEEPER_HOME=/opt/module/zookeeper			//zookeeper installation directory
export ZOOCFGDIR=/opt/module/zookeeper				//zookeeper installation directory

4. Copy JDBC Driver

Upload the jdbc driver to the / opt/software directory.

Enter the directory where the jdbc driver is stored and copy the jdbc driver to the lib directory of sqoop

cp mysql-connector-java-5.1.37.jar /opt/module/sqoop/lib/

5. Verify Sqoop

After entering the sqoop installation directory, we can verify whether the sqoop configuration is correct through a command:

bin/sqoop help

6. Test whether Sqoop can successfully connect to the database

Enter the sqoop installation directory and execute

bin/sqoop list-databases --connect jdbc:mysql://master:3306/ --username root --password 111111

The following output appears:

7. Transfer the data in hive to MySQL database through sqoop

7.1 hive table under construction

(1) First create a table in hive. The data type corresponds to the data type in MySQL

hive> create table student
    > (id varchar(50),name varchar(50),age int,school varchar(50),class varchar(50)) 
    > row format delimited fields terminated by ',';

As shown in the figure:

(2) Prepare data in a format corresponding to the table structure

Add the following contents to the student.txt file:

19308001,zhangsan,20,111111,222222
19308002,lisi,21,333333,444444
19308003,wangwu,20,555555,666666
19308004,liliu,20,777777,888888
19308005,zhangfei,21,999999,111111

Note: whatever is used as the separator, the comma is used here

Then upload it to / opt/software

(3) Import txt file data into hive

load data local inpath '/opt/software/student.txt' into table student;
# inpath is the path where txt files are stored

(4) View data in table

hive> select * from student;

As shown in the figure:

7.2 creating tables in MySQL

Log in to MySQL

mysql -uroot -p

(1) Create a database

mysql> create database hive;

(2) Using hive database

mysql> use hive;

(3) Create table in database

mysql> create table student (id varchar(50),name varchar(50),age int,school varchar(50),class varchar(50));

As shown in the figure:

(4) Add a piece of data

mysql> insert into student values('19308009','wangmazi','22','666666','777777');

As shown in the figure:

7.3 sqoop transfers data from hive to MySQL

Enter the / opt/module/sqoop/bin directory and execute the command

[root@master bin]# sqoop export --connect jdbc:mysql://master:3306/hive --username root --password 111111 --table student --num-mappers 1 --export-dir /user/hive/warehouse/student --input-fields-terminated-by ','

//explain
//master:3306/hive     						 Hive refers to the database in MySQL
--export-dir /user/hive/warehouse/student 	Directory to export data
--input-fields-terminated-by ',' 			What delimiters are used to split fields when exporting data
--table student 							Export to MySQL Which table do you want

The location of the table created by hive on hdfs is: / user/hive/warehouse/student

As shown in the figure:

After success, enter the MySQL database to query the data

As shown in the figure: