Sqoop truncation of date data from Oracle data to Hive

Solution to the problem when the date type is poured into hive when the Oracle database is present

1. Description of the problem:

Using Sqoop to pour the Oracle data table into hive, the date data in Oracle will be truncated in seconds, leaving only ' yyyy-mm-dd ' instead of ' yyyy-mm-dd HH24:mi:ss ' format, followed by ' Hh24:mi: SS ' is automatically truncated, and this truncation can cause problems in parsing processing of time requirements to seconds.

2. Solution:

When you pour the data table with Sqoop, add the--map-column-hive and--map-column-java parameters to change the default mapping type of the Data table column (by default, the date type is mapped to string in the Sqoop into hive). Mapping the date column to the timestamp type, in my question I created the job through Sqoop, and the data table was poured in time increments, the data type of the Passtime column is date, and the script is as follows:

--map-column-java passtime=java.sql.Timestamp --map-column-hive passtime=Timestamp  -- Incremental append--connect jdbc:oracle:thin:@118.228. 196.29:15211 --bindir/opt/sqoop-1.4. 4/lib--table v_vehicleinfo--check-column passtime-Last'2014-04-20 12:00:00  '

Attention:

1) The two parameters of the Red font label are used to change the mapping type of the column, the blue font is labeled with the data type, and the Java.sql.Timestamp is written with the package name, otherwise it may go wrong.

2)--map-column-java and--map-column-hive have a parameter to show, if only--map-column-hive will change the data type of the table column in hive, The Tablename.java source file generated by CodeGen will also be a java.sql.Date type, so that when converted to the timestamp type in the Hive table, an error occurs causing the Passtime field in hive to be all null. The reason may be due to the default format ' YYYY-M-DD ' for the date type, and the strict requirement to follow the ' Yyyy-mm-dd hh:mm:ss[.f ...] when converting to the hive timestamp type. Format conversion, see Hive official website Timestamp format description.

3. Reference notes:

Sqoop User Guide:http://sqoop.apache.org/docs/1.4.5/sqoopuserguide.html#_selecting_the_data_to_import View map-column-hive and Map-column-java parameter descriptions

Hive languagemanual:https://cwiki.apache.org/confluence/display/hive/languagemanual+types# Languagemanualtypes-timestamp Viewing the time type description for hive support

Hive Date function
http://athenaxu.blog.163.com/blog/static/2057220392014025332775/
Hive Data Type Conversions
http://www.iteblog.com/archives/892

Hive turns on row-to-column functionality:
> Set hive.cli.print.header=true; Print column names
> Set hive.cli.print.row.to.vertical=true; Turn on row to column function, if you must turn on the Print column name function
> Set hive.cli.print.row.to.vertical.num=1; Set the number of columns to display per row
> select * from example_table where pt= ' 2012-03-31 ' limit 2;

Hive View data table structure, column type

> desc TableName;

Sqoop truncation of date data from Oracle data to Hive