Install and configure sqoop

Sqoop is an open-source tool mainly used for data transmission between hadoop and traditional databases. The following is an excerpt from the sqoop user manual.

Sqoop is a tool designed to transfer data between hadoop andrelational databases. you can use sqoop to import data from arelational Database Management System (RDBMS) such as MySQL or oracleinto the hadoop Distributed File System (HDFS), transform the data
In hadoop mapreduce, and then export the data backinto an RDBMS.

Here I will mainly describe the installation process

1. Download the corresponding software

I used apache official version 0.20.2 for hadoop, but later I reported an error during usage. I checked some articles and found that sqoop does not support this version, it is generally recommended that you use cdh3. But later by copying the corresponding package to the sqoop-1.2.0-CDH3B4/lib, still can be used. Of course, you can choose to use cdh3 directly.

Below are the cdh3 and sqoop 1.2.0

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

Unzip, decompress 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, so you need to download mysql-connector-Java-*. jar and copy it to the sqoop-1.2.0-CDH3B4/lib.

2. Modify the sqoop file configure-sqoop and comment out hbase and zookeeper checks (unless you are going to use hadoop components such as habase)

# 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 zookeeper installation .'
# Exit 1
# Fi

3. Start hadoop and configure related environment variables (for example, $ hadoop_home) to use sqoop.

The following is an example of how to export table data from a database to an HDFS file.

[Wanghai01@tc-crm-rd01.tc sqoop-1.2.0-CDH3B4] $ bin/sqoop import -- connect JDBC: mysql: // XXXX: XX/CRM -- username CRM -- password 123456 -- table company-M 1
11/09/21 15:45:25 warn tool. basesqooptool: setting your password on the command-line is insecure. Consider using-P instead.
11/09/21 15:45:26 info tool. codegentool: Beginning code generation
11/09/21 15:45:26 info manager. mysqlmanager: executing SQL statement: Select T. * From 'company' as t limit 1
11/09/21 15:45:26 info manager. mysqlmanager: executing SQL statement: Select T. * From 'company' as t limit 1
11/09/21 15:45:26 info Orm. compilationmanager: hadoop_home is/home/wanghai01/hadoop/hadoop-0.20.2/bin /..
11/09/21 15:45:26 info Orm. compilationmanager: Found hadoop core jar at:/home/wanghai01/hadoop/hadoop-0.20.2/bin/../hadoop-0.20.2-core.jar
11/09/21 15:45:26 error Orm. compilationmanager: cocould not rename/tmp/sqoop-wanghai01/compile/2bd70cf2b712a9c7cdb0860722ea7c6/company. Java to/home/wanghai01/cloudera/sqoop-1.2.0-CDH3B4/./company.
11/09/21 15:45:26 info Orm. compilationmanager: Writing JAR file:/tmp/sqoop-wanghai01/compile/2bd70cf2b712a9c7cdb0860722ea7listed/company. Jar
11/09/21 15:45:26 warn manager. mysqlmanager: It looks like you are importing from MySQL.
11/09/21 15:45:26 warn manager. mysqlmanager: this transfer can be faster! Use the -- direct
11/09/21 15:45:26 warn manager. mysqlmanager: Option to exercise a MySQL-specific fast path.
11/09/21 15:45:26 info manager. mysqlmanager: Setting zero datetime behavior to converttonull (MySQL)
11/09/21 15:45:26 info mapreduce. importjobbase: Beginning import of Company
11/09/21 15:45:27 info manager. mysqlmanager: executing SQL statement: Select T. * From 'company' as t limit 1
11/09/21 15:45:28 info mapred. jobclient: running job: job_201101_21_0001
11/09/21 15:45:29 info mapred. jobclient: Map 0% reduce 0%
11/09/21 15:45:40 info mapred. jobclient: Map 100% reduce 0%
11/09/21 15:45:42 info mapred. jobclient: job complete: job_201101_21_0001
11/09/21 15:45:42 info mapred. jobclient: counters: 5
11/09/21 15:45:42 info mapred. jobclient: Job counters
11/09/21 15:45:42 info mapred. jobclient: Launched map tasks = 1
11/09/21 15:45:42 info mapred. jobclient: filesystemcounters
11/09/21 15:45:42 info mapred. jobclient: hdfs_bytes_written = 44
11/09/21 15:45:42 info mapred. jobclient: Map-Reduce framework
11/09/21 15:45:42 info mapred. jobclient: Map input records = 8
11/09/21 15:45:42 info mapred. jobclient: spilled records = 0
11/09/21 15:45:42 info mapred. jobclient: map output records = 8
11/09/21 15:45:42 info mapreduce. importjobbase: Transferred 44 bytes in 15.0061 seconds (2.9321 Bytes/sec)
11/09/21 15:45:42 info mapreduce. importjobbase: retrieved 8 records.

View data

Wanghai01@tc-crm-rd01.tc sqoop-1.2.0-CDH3B4 $ hadoop FS-CAT/user/wanghai01/Company/part-m-00000
1, xx
2, eee
1, xx
2, eee
1, xx
2, eee
1, xx
2, eee

Check it in the database.

Mysql> select * from company;
+ ------ +
| ID | Name |
+ ------ +
| 1 | XX |
| 2 | EEE |
| 1 | XX |
| 2 | EEE |
| 1 | XX |
| 2 | EEE |
| 1 | XX |
| 2 | EEE |
+ ------ +
8 rows in SET (0.00 Sec)

OK, there is no problem. After carefully reading the information output during command execution, an error is displayed, because I failed to execute the command before, and the temporary data is not cleared when the command is executed again.

The following documents are provided:

Sqoopuserguide

Sqoopdevguide