Sqoop operation Oracle Import to Hive

Import all fields of a table

-- Connect JDBC:ORACLE:THIN:@192.168.1.107:1521:ORCL \ -- username SCOTT--password Tiger \ -- table EMP \ --  hive-import  --create-hive-table--hive-table emp-  M 1;

If you report a similar mistake:

Exists

Remove the file from the HDFs system first: Hadoop fs-rmr/user/hadoop/emp

If you report a similar mistake:

 in metadata:alreadyexistsexception (message:Tableexists)

If you report a similar mistake:

 in thread "main" Java.lang.NoSuchMethodError:org.apache.thrift.EncodingUtils.setBit (BIZ) B

This is because hive and HBase are installed under the same path, and the thrift version in the Lib directory of HBase and hive is different.
HBase under the libthrift-0.x.0.jar,hive for the Libthrift-0.x.0.jar. The deletion of the 0.x.0 version under HBase will be changed to 0.x.0.
PS: I don't know why sqoop import data into hive and HBase .

Description:The hive table already exists and needs to be deleted first .

View:

descEmp;empnoDoubleename stringjob stringmgrDoublehiredate stringsalDoubleCommDoubleDeptnoDouble

Select *  fromEMP;7369.0SMITH Clerk7902.0  1980- A- - xx:xx:00.0   800.0   NULL    20.07499.0ALLEN salesman7698.0  1981- Geneva- - xx:xx:00.0   1600.0  300.0   30.07521.0WARD salesman7698.0  1981- Geneva- A xx:xx:00.0   1250.0  500.0   30.07566.0JONES MANAGER7839.0  1981-Geneva- Geneva xx:xx:00.0   2975.0  NULL    20.07654.0MARTIN salesman7698.0  1981- the- - xx:xx:00.0   1250.0  1400.0  30.0...

Note: In general, do not use--create-hive-table to create a table, because it creates the table field format, does not meet our requirements.

Import a specified field of a table

To create a hive table manually:

Create TableEmp_column (empnoint, ename string,job string,mgrint, HireDate string,salDouble, CommDouble, Deptnoint) row format delimited fields terminated by '\ t'Lines terminated by '\ n'stored asTextfile;

-- Connect JDBC:ORACLE:THIN:@192.168.1.107:1521:ORCL \ -- username SCOTT--password Tiger \ --  --columns "Empno,ename,job,sal,comm" \ -- fields-terminated-by ' \ t '--lines-terminated-by ' \ n ' \ -- hive-drop-import-delims--hive-import  --hive-table emp_column \-3;

Note: The data in hive is re-imported once again and every time the import is repeated.

-- Connect JDBC:ORACLE:THIN:@192.168.1.107:1521:ORCL \ -- username SCOTT--password Tiger \ -- table EMP--columns "Empno,ename,job,sal,comm" \ -- fields-terminated-by ' \ t '--lines-terminated-by ' \ n ' \ --  --hive-overwrite --hive-import--hive-table emp_column \ - 3;

Note:--hive-overwrite specifies a record that already exists in the overlay table, and 99% is to use overwrite to avoid duplicate data when re-running.

Import the specified field of the table to the Hive partition table

To create a hive partition table:

Create Tableemp_partition (empnoint, ename string,job string,mgrint, HireDate string,salDouble, CommDouble, Deptnoint) Partitioned by(PT string) row format delimited fields terminated by '\ t'Lines terminated by '\ n'stored asTextfile;

Import pt= ' 2013-08-01 '

-- Connect JDBC:ORACLE:THIN:@192.168.1.107:1521:ORCL \ -- username SCOTT--password Tiger \ -- table EMP--columns "Empno,ename,job,sal,comm" \ -- hive-overwrite--hive-import  --hive-table emp_partition \--fields-terminated-by ' \ t '-- Lines-terminated-by ' \ n '----hive-partition-key ' pt '--hive-partition-value ' 2013-08-01 ' \-3;

Import pt= ' 2013-08-02 '

-- Connect JDBC:ORACLE:THIN:@192.168.1.107:1521:ORCL \ -- username SCOTT--password Tiger \ -- table EMP--columns "Empno,ename,job,sal,comm" \ -- hive-overwrite--hive-import  --hive-table emp_partition \--fields-terminated-by ' \ t '-- Lines-terminated-by ' \ n ' \--hive-drop-import-delims  --hive-partition-key ' PT '-- Hive-partition-value ' 2013-08-02 ' -3;

Inquire:

Select *  from where pt='2013-08-01'; Select *  from where pt='2013-08-02';