Sqoop for data import and export

Sqoop is a tool used for data import and export, typically used in the framework of Hadoop, where common scenarios include importing data from a MySQL database into HDFs or hive, Hbase, or exporting it to a relational database. The following sections show the process of importing and exporting several pieces of code.

Import data from MySQL into the Hadoop cluster (HDFS):

The script command is first posted:

./sqoop import--connect jdbc:mysql://localhost/test--username root--password Lakers--table persons-m 1

Hence the name incredible,

Username: User name for database

Password: User name corresponding to the password

Table: Names of tables in the data that need to be imported

-M: Number of boot MapReduce.

This is done by importing all the data from a table (test) into HDFs, where you can set the path to import to HDFs through the parameters--target-dir <dir> settings, and many other parameter settings can be viewed through the website: http:// Sqoop.apache.org/docs/1.4.6/sqoopuserguide.html#_importing_data_into_hive_2

Import some of the data from the test table into HDFs:

./sqoop import--connect jdbc:mysql://localhost/test--username root--password Lakers--query ' select ID,name from Perso NS where $CONDITIONS '--target-dir/persons-m 1

To import MySQL data into hive:

The traditional way to import data from MySQL into HDFs is to:

1 export MySQL data to local

2 Creating a table format in hive

3 Importing data exported locally into hive

But after using the Sqoop, we can do the above operation in one step.

First of all, the MySQL data into the hive, is actually imported to the HDFS, but the advantage is that we can directly through the hive to process the data, get the results we want, the following shell script and the above script is very similar.

./sqoop import--connect jdbc:mysql://localhost/test--username root--password Lakers--table persons-m 1--hive-import

Here is a need to note that when we import MySQL data into the hive is not necessary to create a table, in the import process Sqoop will automatically parse the data, help us create the corresponding format table structure. It has to be said that it saves a lot of effort, after all, creating a table structure is cumbersome, especially in the case of a lot of table fields.

And the above difference is the last add--hive-import also can be set through the parameters of the import directory, from the official website query.

Data import to HBase in MySQL:

./sqoop import--connect jdbc:mysql://localhost/test--username root--password Lakers--table persons-m 1--hbase-creat E-table--hbase-table person2--column-family info--hbase-row-key sid

--hbase-create-table: Automatically create hbase table

--hbase-table: Show

--column-family: Column Cluster name

--hbase-row-key: Specifies the key in the rowkey corresponding MYSQ

Note: At present, the data can not be directly exported to the HBase directly to MySQL, you could first export to the local, and then import MySQL

To export data from hive to MySQL:

./sqoop export--connect jdbc:mysql://localhost/test--username root--password lakers-m 1--table personcopy--export-di R/hive/warehouse/persons--input-fields-terminated-by ' \0001 '

--export-dir: Catalog of exported data

--input-fileds-terminated-by: A separator between fields

Attention:

It is important to note that we do not need to create a table structure when we import hive from MySQL, but we need to create our own table structure when we export hive data to MySQL, for example, our MySQL A varchar (50) or varchar (60) is automatically resolved to string type in hive, but instead, importing string types in hive into MySQL does not know that parsing is a varchar (. , so at this point we need to manually create the corresponding table structure.