Use Cases of hadoop plug-in sqoop

Sqoop is a plug-in the hadoop project. You can import the content in HDFS of the Distributed File System to a specified MySQL table, or import the content in MySQL to the HDFS File System for subsequent operations.

Test Environment Description:

Hadoop version: hadoop-0.20.2

Sqoop: sqoop-1.2.0-CDH3B4

Java version: jdk1.7.0 _ 67

MySQL version: 5.1.65

Note:

Because I installed the hadoop-0.20.2 version, sqoop does not support this version, but you can use the cdh3 version of hadoop, you can also copy the relevant files to achieve the purpose.

Download link:

Http://archive.cloudera.com/cdh/3/hadoop-0.20.2-CDH3B4.tar.gz

Http://archive.cloudera.com/cdh/3/sqoop-1.2.0-CDH3B4.tar.gz

Sqoop-1.2.0-CDH3B4 depends on the hadoop-core-0.20.2-CDH3B4.jar, so you need to download hadoop-0.20.2-cdh3b4.tar.gz, unzip and copy the hadoop-0.20.2-CDH3B4/hadoop-core-0.20.2-cdh3b4. jar to the sqoop-1.2.0-CDH3B4/lib.

In addition, sqoop depends on MySQL-connector ctor-Java-*. jar when importing Mysql Data to run, so you need to download mysql-connector-Java-*. jar and copy it to the sqoop-1.2.0-CDH3B4/lib

I. sqoop basic configuration:

1) Description of profile environment variables:

Export java_home =/usr/local/jdk1.7.0 _ 67/export jre_home =/usr/local/jdk1.7.0 _ 67/jreexport classpath =.: $ java_home/lib/DT. jar: $ java_home/lib/tools. jar: $ classpathexport pig_home =/usr/local/pig-0.9.2 # Tell the pig client, local hadoop profile where export pig_classpath =/usr/local/hadoop-0.20.2/confexport hbase_home =/usr/local/hbase-0.90.5export hadoop_home =/usr/local/hadoop-0.20.2export sqoop_home =/usr/local /sqoop-1.2.0-CDH3B4export hive_home =/usr/local/hive-0.8.1export Path = $ java_home/bin: $ path:/usr/local/bin: $ hadoop_home/bin: $ hbase_home/bin: $ pig_home/bin: $ pig_classpathi: $ hive_home/bin: $ sqoop_home/bin: $ path

2) Because sqoop does not need to check the hbase and zookeeper environment variables in this test case, we need to explain the following:

Edit the file/usr/local/sqoop-1.2.0-CDH3B4/bin/configure-sqoop, modify the line and add the comment in:

#if [ ! -d "${HBASE_HOME}" ];then#  echo "Error: $HBASE_HOME does not exist!"#  echo ‘Please set $HBASE_HOME to the root of your HBase installation.‘#  exit 1#fi#if [ ! -d "${ZOOKEEPER_HOME}" ]; then#  echo "Error: $ZOOKEEPER_HOME does not exist!"#  echo ‘Please set $ZOOKEEPER_HOME to the root of your ZooKeeperinstallation.‘#  exit 1#fi

3) create an sqoop library and perform the following operations:

Mysql> grant all privileges on sqoop. * To 'sqoop '@ '2017. 172. %' identified by 'routon' with grant option;

Mysql> flush privileges;

4) create a students table and insert data:

Mysql> Create Table students (ID int not null primary key, name varchar (20), age INT );

Insert test data:

insert into studentsvalues(‘001‘,‘abc‘,29);insert into students values(‘002‘,‘def‘,28);insert into students values(‘003‘,‘aaaa‘,26);insert into students values(‘004‘,‘efsaz‘,60);insert into students values(‘005‘,‘kiass‘,63);

The data in the table is:

Mysql> select * from students;

+ ---- + ------- + ------ +

| ID | Name | age |

+ ---- + ------- + ------ +

| 2 | def | 28 |

| 3 | AAAA | 26 |

| 4 | efsaz | 60 |

| 5 | kiass | 63 |

+ ---- + ------- + ------ +

4 rows in SET (0.00 Sec)

5) on the node1 node of the master device, test whether sqoop can successfully connect to MySQL on the remote host node29. If the table created in the MySQL database appears, the connection is successful.

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/4C/EC/wKioL1RHa16gTePuAAFl12i8EMM960.jpg "Title =" Export mysql.jpg "alt =" wkiol1rha16gtepuaafl12i8emm960.jpg "/>

Through sqoop, you can view the students table in the Remote Test MySQL database;

6) import data from the MySQL database to the HDFS File System:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/4C/EC/wKioL1RHa-WRNxzFAAjyO64ZK_U536.jpg "Title =" mysqldata import to HDFS .jpg "alt =" wKioL1RHa-WRNxzFAAjyO64ZK_U536.jpg "/> check whether the import is successful in the HDFS File System:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/4C/EA/wKiom1RHa-iw-z6ZAAGonEYmHME428.jpg "Title =" hdfsstudents.jpg "alt =" wKiom1RHa-iw-z6ZAAGonEYmHME428.jpg "/>

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/4C/EC/wKioL1RHbIPD5PagAAH0w3gK8ig189.jpg "Title =" HDFS file .jpg "alt =" wkiol1rhapd5pagaah0w1_k8ig189.jpg "/>

Description: MySQL Chinese content has been imported into the Distributed File System!

7) Import files from HDFS to MySQL

Note: before importing data, clear the data in the students table and run the delete from students command;

Run the sqoop command on the master server to import the HDFS file content to the students table in the MySQL data!

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/4C/EC/wKioL1RHbbmR4BjTAAil9nd-y-w858.jpg "Title =" HDFS import to mysql中.jpg "alt =" wKioL1RHbbmR4BjTAAil9nd-y-w858.jpg "/>

View the MySQL table:

Mysql> select * from students;

+ ---- + ------- + ------ +

| ID | Name | age |

+ ---- + ------- + ------ +

| 2 | def | 28 |

| 3 | AAAA | 26 |

| 4 | efsaz | 60 |

| 5 | kiass | 63 |

+ ---- + ------- + ------ +

4 rows in SET (0.00 Sec)

Test complete!

This article is from the "shine_forever blog", please be sure to keep this source http://shineforever.blog.51cto.com/1429204/1566788

Use Cases of hadoop plug-in sqoop