sqoop - Sqoop Export - apache sqoop - sqoop tutorial - sqoop hadoop
Sqoop related tags : sqoop import , sqoop interview questions , sqoop export , sqoop commands , sqoop user guide , sqoop documentation
What is Sqoop Export?
- The export tool exports a set of files from HDFS back to an RDBMS. The target table must already exist in the database.
- The input files are read and parsed into a set of records according to the user-specified delimiters.
- The default operation is to transform these into a set of INSERT statements that inject the records into the database.
- In "update mode," Sqoop will generate UPDATE statements that replace existing records in the database.
Learn sqoop - sqoop tutorial - Sqoop Export - sqoop examples - sqoop programs
Export control arguments:
| Argument | Description |
|---|---|
| --direct | Use direct export fast path |
| --export-dir | HDFS source path for the export |
| -m,--num-mappers | Use n map tasks to export in parallel |
| --table | Table to populate |
| --update-key | Anchor column to use for updates. Use a comma separated list of columns if there are more than one column. |
| --update-mode | Specify how updates are performed when new rows are found with non-matching keys in database. |
| Legal values for mode include updateonly (default) and allowinsert. | |
| --input-null-string | The string to be interpreted as null for string columns |
| --input-null-non-string | The string to be interpreted as null for non-string columns |
| --staging-table | The table in which data will be staged before being inserted into the destination table. |
| --clear-staging-table | Indicates that any data present in the staging table can be deleted. |
| --batch | Use batch mode for underlying statement execution. |
Syntax
$ sqoop export (generic-args) (export-args)
$ sqoop-export (generic-args) (export-args)
Click "Copy code" button to copy into clipboard - By wikitechy - sqoop tutorial - team
Insert:
- Insert mode will insert the new records from HDFS to RDBMS table.
sqoop export --connect
jdbc:mysql://localhost/sqoop_export --table
student_exported --export-dir /sqoop/newstudent/part-m-00000
Click "Copy code" button to copy into clipboard - By wikitechy - sqoop tutorial - team
Note: If a record already present in the database table with same primary key, then it will raise MySQLIntegrityConstraintViolationException exception.
Update:
- Update mode will update the records in the RDBMS from HDFS data.
- Update mode only update already existing records, it will not insert new records in the RDBMS.
sqoop export --connect jdbc:mysql://localhost/sqoop_export--export-dir/sqoop/emp_last/part-m-00000 --update-key id
Click "Copy code" button to copy into clipboard - By wikitechy - sqoop tutorial - team
Example
- Let us take an example of the employee data in file, in HDFS.
- The employee data is available in emp_data file in ‘emp/’ directory in HDFS. The emp_data is as follows.
1201, nila, manager, 50000, TP
1202, manisha, preader, 50000, TP
1203, John, php dev, 30000, AC
1204, prasanth, php dev, 30000, AC
1205, venkat, admin, 20000, TP
1206, Harish p, grp des, 20000, GR
Click "Copy code" button to copy into clipboard - By wikitechy - sqoop tutorial - team
- It is mandatory that the table to be exported is created manually and is present in the database from where it has to be exported.
- The following query is used to create the table ‘wikitechy_employee’ in mysql command line.
$ mysql
mysql> USE db;
mysql> CREATE TABLE wikitechy_ employee (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(20),
deg VARCHAR(20),
salary INT,
dept VARCHAR(10));Click "Copy code" button to copy into clipboard - By wikitechy - sqoop tutorial - team
- The following command is used to export the table data (which is in emp_data file on HDFS) to the employee table in db database of Mysql database server.
- The following command is used to export the table data (which is in emp_data file on HDFS) to the employee table in db database of Mysql database server.
$ sqoop export \--connect jdbc:mysql://localhost/db \--username root \--table wikitechy_employee \ --export-dir /emp/emp_data
Click "Copy code" button to copy into clipboard - By wikitechy - sqoop tutorial - team
- The following command is used to verify the table in mysql command line.
mysql>select * from wikitechy_employee;
Click "Copy code" button to copy into clipboard - By wikitechy - sqoop tutorial - team
OUTPUT:
+------+--------------+-------------+-------------------+--------+
| Id | Name | Designation | Salary | Dept |
+------+--------------+-------------+-------------------+--------+
| 1201 | nila | manager | 50000 | TP |
| 1202 | manisha | preader | 50000 | TP |
| 1203 | John | php dev | 30000 | AC |
| 1204 | prasanth | php dev | 30000 | AC |