Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Hive Imports

avatar
New Contributor

Is there a way to create a table in hive and get it to read in the headers from the input file instead of listing out all of the headers and field types?

1 ACCEPTED SOLUTION

avatar
Explorer

got the answer from Chris - Systems Engineer - Cloudera

 

"--table" will refer to the Oracle schema and tablename

"--hive-table" will be the name of the database and table you are creating in Hive.  

 

If you aren't using a database other than the default database, then this value will be just the name of the table you are trying to create in Hive.  

 

[cghosh@usmbg-lcldpoct1 ~]$ sqoop import \

> --connect jdbc:oracle:thin:@usmbg-lcpmdbt1.amer.exel-intra.net:1592:CPMDWDEV \

> --username obaw \

> --password oracle \

> --table EDW.XXOB_GL_ACCOUNT_HIER_WIDS -m 1 \

> --hive-import \

> --hive-table XXOB_GL_ACCOUNT_HIER_WIDS \

> --target-dir /user/cghosh/sqoopimport/DWDEV/TEST

 

View solution in original post

3 REPLIES 3

avatar
Contributor

Not directly, no. Hive uses MapReduce InputFormat implementations under the hood that aren't aware of header records. Also, if it did respect headers, you run the risk of having two files in a directory with differing "schemas." Unless you do a fair bit of custom development, you'll need to create the table definition with CREATE TABLE and remove the header record from the file(s).

Eric Sammer
Engineering Manager / Cloudera / @esammer

avatar
Explorer

Eric,

 

i am connecting as a user user1 and have select privillage on a table from a user id user2
in hive not sure how it will find the table under the user2 id while i am connecting as user1

 

Oracle Database name “CPMDWDEV” , Table owner user id - “edw” and Connected id –“obaw”


[cghosh@usmbg-lcldpoct1 ~]$ sqoop import \
> --connect jdbc:oracle:thin:@usmbg-lcpmdbt1.amer.exel-intra.net:1592:CPMDWDEV \
> --username obaw \
> --password xxxxxx \
> --table EDW.XXOB_GL_ACCOUNT_HIER_WIDS -m 1 \
> --hive-import \
> --hive-table EDW.XXOB_GL_ACCOUNT_HIER_WIDS \
> --target-dir /user/cghosh/sqoopimport/DWDEV/TEST

 

Error

13/09/03 14:45:46 INFO hive.HiveImport: FAILED: Error in metadata: Invalid table name: CPMDWDEV.EDW.XXOB_GL_ACCOUNT_HIER_WIDS
13/09/03 14:45:46 INFO hive.HiveImport: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask
13/09/03 14:45:46 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: Hive exited with status 1

 

avatar
Explorer

got the answer from Chris - Systems Engineer - Cloudera

 

"--table" will refer to the Oracle schema and tablename

"--hive-table" will be the name of the database and table you are creating in Hive.  

 

If you aren't using a database other than the default database, then this value will be just the name of the table you are trying to create in Hive.  

 

[cghosh@usmbg-lcldpoct1 ~]$ sqoop import \

> --connect jdbc:oracle:thin:@usmbg-lcpmdbt1.amer.exel-intra.net:1592:CPMDWDEV \

> --username obaw \

> --password oracle \

> --table EDW.XXOB_GL_ACCOUNT_HIER_WIDS -m 1 \

> --hive-import \

> --hive-table XXOB_GL_ACCOUNT_HIER_WIDS \

> --target-dir /user/cghosh/sqoopimport/DWDEV/TEST