Sqoop Operations HDFs exported to Oracle

Operation Details: https://www.cnblogs.com/xiaodf/p/6030102.html

Note: You need to create a table structure to be exported before exporting. An error occurs if the exported table does not exist in the database, and the data in the table repeats if multiple exports are repeated;

CREATE TABLE Emp_demo as SELECT * from EMP where 1=2;
CREATE TABLE Salgrade_demo as SELECT * from Salgrade where 1=2;

Export all fields of a table

Sqoop export--connect jdbc:oracle:thin:@192.168.1.107:1521:orcl \
--username SCOTT--password tiger \
--table Emp_demo  \
--export-dir/user/hadoop/emp-  M 1;

Repeatedly, the data in the table repeats, and the previously existing data is not deleted.

to export a specified field of a table

To see how the demo works, first delete the data that already exists in the table.

DELETE from Emp_demo;

Sqoop export--connect jdbc:oracle:thin:@192.168.1.107:1521:orcl \
--username SCOTT--password tiger \
--table Emp_demo  \
--columns "Empno,ename,job,sal,comm" \
--export-dir '/user/hadoop/emp_ COLUMN ' \
M 1;

Sqoop export--connect jdbc:oracle:thin:@192.168.1.107:1521:orcl \
--username SCOTT--password Tiger
Table Emp_demo  \
--columns "Empno,ename,job,sal,comm" \
--export-dir '/user/ Hadoop/emp '
M 1;

The specified field of the export table uses the specified separator

To see how the demo works, first delete the data that already exists in the table.

DELETE from Emp_demo;

Sqoop export--connect jdbc:oracle:thin:@192.168.1.107:1521:orcl \
--username SCOTT--password Tiger
Table Emp_demo  \
--columns "Empno,ename,job,sal,comm" \
--export-dir '/user/hadoop/emp_column_ SPLIT ' \
--fields-terminated-by ' \ t '--lines-terminated-by ' \ n ' m 1;

Scripts that do not have a delimiter specified will have an error when executing: caused by:java.lang.NumberFormatException

Sqoop export--connect jdbc:oracle:thin:@192.168.1.107:1521:orcl \
--username SCOTT--password tiger \
--table Emp_demo  \
--columns "Empno,ename,job,sal,comm" \
--export-dir '/user/hadoop/emp_column_split '
- M 1;

Description
1--fields-terminated-by ' \ t '--lines-terminated-by ' \ n ' to and import the same, otherwise error ;
2)Export command is not supported, after the last two export operations, the table has two of the same data .

Bulk Export

To see how the demo works, first delete the data that already exists in the table.

DELETE from Emp_demo;

Sqoop export  \
-dsqoop.export.records.per.statement=10 \
--connect jdbc:oracle:thin:@ 192.168.1.107:1521:ORCL \
--username SCOTT--password tiger \
--table emp_demo  \
--export-dir/ User/hadoop/emp-  M 1 \
--;

By default, data that reads a row of HDFs files is recorded into a relational database with low performance;

You can use bulk export to import 10 of data to a relational database at a time;

Export Guaranteed atomicity

To see how the demo works, first delete the data that already exists in the table.

DELETE from Emp_demo;

Sqoop export   --connect jdbc:oracle:thin:@192.168.1.107:1521:orcl \
--username SCOTT--password Tiger
Table Emp_demo  \
--export-dir/user/hadoop/emp  -M 1 \
--staging-table staging_emp  \
--clear-staging-table ;

The map task does not have data rollback operations and how to guarantee atomicity.

Sqoop in the target table, first import into the temporary table staging_emp, to determine the successful export, once again to the target table, to ensure atomicity;

When using--staging-table, the Staging_emp table must be created in advance and must have a primary key;
If you use--clear-staging-table,staging_emp if there is data, delete the data in the Staging_emp table before exporting it;

processing NULL data

Sqoop export   --connect jdbc:oracle:thin:@192.168.1.107:1521:orcl \
--username SCOTT--password Tiger
Table Emp_demo  \
--export-dir/user/hadoop/emp  -M 1 \-
-input-null-string ' \\n ' \ 
--input-null-non-string ' \\n ' ;

Update-key Operation

CREATE TABLE Emp_demo2 as SELECT * from Emp_demo where 1=1;

Change the ename of empno=7788 to scott11,empno=7782 ename to CLARK11

At this point, the ename of empno=7788 in HDFs is scott,empno=7782 ename for Clark.

Sqoop export--connect jdbc:oracle:thin:@192.168.1.107:1521:orcl \
--username SCOTT--password Tiger
Table Emp_demo2  \
--export-dir/user/hadoop/emp  \
--update-key EMPNO -M 1 ;

After execution, it was found that empno=7788 's ename was scott,empno=7782 's ename for Clark.

Change the ename of empno=7788 to scott11,empno=7782 ename to CLARK11
The table deletes any data except empno 7788 and 7782, and executes the

Sqoop export--connect jdbc:oracle:thin:@192.168.1.107:1521:orcl \
--username SCOTT--password tiger \
--table Emp_demo2  \
--export-dir/user/hadoop/emp  \
--update-key EMPNO -M 1;

After execution, the number of data bars found in the table was not added, but empno=7788 ename was found to be scott,empno=7782 ename for Clark

Summary:--update-key update only, not add

Update-mode Allowinsert Operation

In the Emp_demo2 table, change the empno=7788 ename to scott11,empno=7782 ename, delete some data, leaving only a few to do the test

Sqoop export--connect jdbc:oracle:thin:@192.168.1.107:1521:orcl \
--username SCOTT--password Tiger
Table Emp_demo2 \
--export-dir/user/hadoop/emp \
--update-key EMPNO \
--Update-mode Allowinsert -M 1;

After the execution, a total of 14 data were found, the data in the HDFs were exported to the database, and the empno=7788 ename changed to scott,empno=7782 Ename changed to Clark.
To perform one more time:

Sqoop export--connect jdbc:oracle:thin:@192.168.1.107:1521:orcl \
--username SCOTT--password tiger \
--table EMP_DEMO2 \
--export-dir/user/hadoop/emp \
--update-key EMPNO \-
-update-mode Allowinsert -M 1;

Still 14 data unchanged;

Summary: According to the specified ID, no data is inserted, the data will be updated ;