Geek Answers Handbook

Sqoop Hive table import, dataType table does not match database

Using Sqoop to import data from oracle to hive, it works fine, but it creates a table in a bush with only two data types String and Double. I want to use timeStamp as a data type for some columns. How can i do this.

bin/sqoop import --table TEST_TABLE --connect jdbc:oracle:thin:@HOST:PORT:orcl --username USER1 -password password -hive-import --hive-home /user/lib/Hive/
+6
source share
3 answers

In addition to the answers above, we can also observe when an error arrives, for example

In my case, I had two types of data columns that caused the error: json and binary

for the json column, an error occurred when the Java class was executed at the very beginning of the import process:

 /04/19 09:37:58 ERROR orm.ClassWriter: Cannot resolve SQL type

for a binary column , an error was selected when importing into hive tables (after importing data and adding it to HDFS files)

 16/04/19 09:51:22 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: Hive does not support the SQL type for column featured_binary

To get rid of these two errors, I had to provide the following parameters

 --map-column-java column1_json=String,column2_json=String,featured_binary=String --map-column-hive column1_json=STRING,column2_json=STRING,featured_binary=STRING

Therefore, we may need to provide

 --map-column-java

or

 --map-column-hive

depending on the failure.

+5
source

You can use the --map-column-hive to override the default mapping. This parameter expects a comma-separated list of key-value pairs separated by = to indicate which column should be mapped to the type in Hive.

 sqoop import \ ... --hive-import \ --map-column-hive id=STRING,price=DECIMAL
+4
source

Added a new function with sqoop-2103 / sqoop 1.4.5, which allows you to call decimal precision with the map-column-hive parameter. Example:

 --map-column-hive 'TESTDOLLAR_AMT=DECIMAL(20%2C2)'

This syntax will define the field as DECIMAL(20,2) . %2C used as a comma, and the parameter must be in single quotes if sent from the bash shell.

I tried using Decimal unchanged, and I got Decimal(10,0) by default.

+3
source

More articles:


All Articles