Shulou(Shulou.com)06/01 Report--

This article mainly shows you "what is the use of Sqoop", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "what is the use of Sqoop" this article?

I. Overview of Sqoop

Sqoop is an open source big data component, mainly used to transfer data between Hadoop (Hive, HBase, etc.) and traditional databases (mysql, postgresql, oracle, etc.).

Usually the basic functions of the components of data handling: import and export.

Since Sqoop is a component of big data's technical system, importing relational databases into Hadoop storage systems is called import and vice versa.

Sqoop is a command line component tool that converts import or export commands into mapreduce programs. Inputformat and outputformat are mainly customized in mapreduce.

II. Environmental deployment

When testing Sqoop components, you should at least have basic environments such as Hadoop series, relational data, JDK, and so on.

Since Sqoop is a tool component, you can install it on a single node.

1. Upload the installation package

Install package and version: sqoop-1.4.6

[root@hop01 opt] # tar-zxf sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz [root@hop01 opt] # mv sqoop-1.4.6.bin__hadoop-2.0.4-alpha sqoop1.4.62, modify configuration file

File location: sqoop1.4.6/conf

[root@hop01 conf] # pwd/opt/sqoop1.4.6/conf [root@hop01 conf] # mv sqoop-env-template.sh sqoop-env.sh

Configuration content: involves hadoop series common components and scheduling component zookeeper.

[root@hop01 conf] # vim sqoop-env.sh# configuration content export HADOOP_COMMON_HOME=/opt/hadoop2.7export HADOOP_MAPRED_HOME=/opt/hadoop2.7export HIVE_HOME=/opt/hive1.2export HBASE_HOME=/opt/hbase-1.3.1export ZOOKEEPER_HOME=/opt/zookeeper3.4export ZOOCFGDIR=/opt/zookeeper3.43, configuration environment variable [root@hop01 opt] # vim / etc/profileexport SQOOP_HOME=/opt/sqoop1.4.6export PATH=$PATH:$SQOOP_HOME/ bin [root @ hop01 opt] # source / etc/profile4, Introduce MySQL driver [root@hop01 opt] # cp mysql-connector-java-5.1.27-bin.jar sqoop1.4.6/lib/5, environment check

Key points: import and export

View the help commands and check the version number through version. Sqoop is a tool based on command-line operations, so the commands here will be used below.

6. Related environment

At this point, take a look at the relevant environments in the sqoop deployment node, which are basically in cluster mode:

7. Test MySQL connection sqoop list-databases-- connect jdbc:mysql://hop01:3306/-- username root-- password 123456

Here is the command to view the MySQL database. As shown in the figure, the result is printed correctly:

3. Data import case 1. MySQL data script CREATE TABLE `user user` (`id`int (11) NOT NULL AUTO_INCREMENT COMMENT 'primary key id', `user_ name` varchar', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' user table'; INSERT INTO `sq_ import`.`tb _ user` (`id`, `user_ name`) VALUES (1, 'spring'); INSERT INTO `sq_ import`.`tb _ user` (`id`, `user_ name`) VALUES (2,' cantilever') INSERT INTO `sq_ import`.`tb _ user` (`id`, `user_ name`) VALUES (3, 'java')

2. Sqoop import script

Specify the tables of the database and import all of them into the Hadoop system. Note that the Hadoop service needs to be started here.

Sqoop import-- connect jdbc:mysql://hop01:3306/sq_import\-- username root\-- password 123456\-- table tb_user\-- target-dir / hopdir/user/tbuser0\-m 13, Hadoop query

[root@hop01 ~] # hadoop fs-cat / hopdir/user/tbuser0/part-m-000004, specified columns and conditions

The SQL statement of the query must have WHERE$CONDITIONS:

Sqoop import-- connect jdbc:mysql://hop01:3306/sq_import\-- username root\-- password 123456\-- target-dir / hopdir/user/tbname0\-- num-mappers 1\-- query 'select user_name from tb_user where 1 and $CONDITIONS;'

View the export results:

[root@hop01 ~] # hadoop fs-cat / hopdir/user/tbname0/part-m-000005, import Hive components

If the database used by hive is not specified, the default library is imported by default, and the table name is automatically created:

Sqoop import-- connect jdbc:mysql://hop01:3306/sq_import\-- username root\-- password 123456\-- table tb_user\-- hive-import\-M1

For the execution process, you can observe the execution log of sqoop here:

Step 1: import the data from MySQL to the default path of HDFS

Step 2: migrate the data from the temporary directory to the hive table

6. Import HBase components

The current cluster version of hbase is 1.3. You need to create a table before you can perform data import normally:

Sqoop import-- connect jdbc:mysql://hop01:3306/sq_import\-- username root\-- password 123456\-- table tb_user\-- columns "id,user_name"\-column-family "info"\-- hbase-table tb_user\-- hbase-row-key id\-- split-by id

View the table data in HBase:

IV. Data export cases

Create a new MySQL database and table, and then export the data from HDFS to MySQL. Here you can use the data generated by the first import script:

Sqoop export--connect jdbc:mysql://hop01:3306/sq_export\-- username root\-- password 123456\-- table tb_user\-- num-mappers 1\-- export-dir / hopdir/user/tbuser0/part-m-00000\-- num-mappers 1\-input-fields-terminated-by ","

Look at the data in MySQL again, and the record is completely exported. Here, there is a separator between each data field. The syntax rules can be compared with the results of the script-HDFs data query.

The above is all the content of this article "what's the use of Sqoop?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.