CODEWITHSUNDEEP
oracle-databasehivesqoop
Ed Djatsa
Ed Djatsa

Reputation: 21

Error when creating a table with Sqoop

I want to create a table schema in Hive using "sqoop create-hive-table " . My DB is an Oracle Database 10g Enterprise Edition 10.2.0.4.0 , I use sqoop-1.2.0-cdh3u0 and hive-0.7.0-cdh3u0. The data to load afterwards on the Hive table is already on the HDFS, and I would prefer having the table metadata created automatically by sqoop & hive rather than creating it manually and loading the data afterwards.

Here is the command I execute :

$ sqoop create-hive-table --connect jdbc:oracle:thin:@//dbserver:1521/masterdb
        --username myuser --table DBFUNC1.R1_EVOLUTION
        --fields-terminated-by ',' -P

And I get the following error :

11/05/12 11:33:11 INFO hive.HiveImport: Loading uploaded data into Hive

11/05/12 11:33:12 INFO manager.OracleManager: Time zone has been set to GMT 11/05/12

11:33:12 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM BFUNC1.R1_EVOLUTION t WHERE 1=0

11/05/12 11:33:14 INFO hive.HiveImport: Hive history file=/tmp/edjatsay/hive_job_log_edjatsay_201105121133_1466429457.txt

11/05/12 11:33:14 INFO hive.HiveImport: FAILED: Parse Error: line 1:58 mismatched input ')' expecting Identifier in column specification

11/05/12 11:33:14 INFO hive.HiveImport:

11/05/12 11:33:14 ERROR tool.CreateHiveTableTool: Encountered IOException running create table job: java.io.IOException: Hive exited with status 11

Here is the schema of the table in the DB :

SQL> describe BFUNC1.R1_EVOLUTION;

OWNER_ID          NOT NULL NUMBER(38)    
AI_CODE           NOT NULL CHAR(3) 
TA_NUMBER         NOT NULL NUMBER(38)   
RULE              NOT NULL CHAR(4)
F_CLASS           NOT NULL CHAR(8)      
EVOLUTION_ID      NOT NULL NUMBER(38)
CREATION_MODIFID   NUMBER(38)
DISC_MODIFID       NUMBER(38)

CREATION_DATETIME   CHAR(19)
ENDED_DATETIME      CHAR(19)
EVOLUTION_STATE     CHAR(1)

Thanks in advance for your help.

Upvotes: 0

Views: 2217

Answers (2)

NS Saravanan
NS Saravanan

Reputation: 303

Below is the sqoop import query i use in my project:

oracle_connection.txt will have the connection info.

sqoop --options-file  oracle_connection.txt \
--table $DATABASE.$TABLENAME \
-m $NUMMAPPERS  \
--where "$CONDITION" \
--hive-import \
--map-column-hive "$COLLIST" \
--create-hive-table \
--hive-drop-import-delims \
--split-by $SPLITBYCOLUMN  \
--hive-table $HIVEDATABASE.$TABLENAME \
--bindir sqoop_hive_rxhome/bindir/ \
--outdir sqoop_hive_rxhome/outdir

Upvotes: 0

Ed Djatsa
Ed Djatsa

Reputation: 21

I finally solved this problem by importing importing a single row of my table with sqoop with the "--hive-import" option. This is the command I used :

$ sqoop import --connect jdbc:oracle:thin:@//dbserver:1521/masterdb --username myuser --table DBFUNC1.R1_EVOLUTION --where 'rownum=1' --verbose -P

Next, I simply deleted the single row imported previously and loaded manually the data I already had on HDFS. The purpose of this was to let Hive guess and create the metadata automatically. I order to be able to perform the overall process in a generic script.

Upvotes: 2

Related Questions