[SQOOP-1946] DateSplitter relies on database string-to-date conversion when creating splits based on date columns

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.4.5
• Component/s: connectors/generic
• Assignee: Unassigned
• Reporter: Andre Araujo
• Created: 24/Dec/14 00:38
• Updated: 07/Oct/20 08:55

Description

When running a sqoop import job with a split-by column of data type DATE, the DateSplitter relies on the database implicit string-to-date convertion when generating the lower and upper bound clauses for the splits. The splits are generated with clauses similar to the following:

date_col >= '2013-08-26 00:00:00.0'
date_col <= '2013-08-26 00:00:00.0'

This forces the database to either implcitly convert the date_col to string or implicitly cast the literal string to a date type. In case the database default date format is not the expected, this could lead to either exceptions, as in the example below, or to unexpected behavior (string comparison of to dates in different string formats).

For Oracle databases, for example, we may see the following errors when the database cannot implicitly convert the string above to date:

2014-12-23 12:38:25,690 INFO [main] org.apache.hadoop.mapred.MapTask: Processing split: sales_date >= '2013-08-26 00:00:00.0' AND sales_date <= '2013-08-26 00:00:00.0'

2014-12-23 12:38:25,745 INFO [main] org.apache.sqoop.mapreduce.db.DBRecordReader: Working on split: sales_date >= '2013-08-26 00:00:00.0' AND sales_date <= '2013-08-26 00:00:00.0'

2014-12-23 12:38:25,860 INFO [main] org.apache.sqoop.mapreduce.db.DBRecordReader: Executing query: select * from schema.TABLE where ( date_col >= '2013-08-26 00:00:00.0' ) AND ( date_col <= '2013-08-26 00:00:00.0' )

2014-12-23 12:38:25,909 ERROR [main] org.apache.sqoop.mapreduce.db.DBRecordReader: Top level exception:  

java.sql.SQLDataException: ORA-01861: literal does not match format string 

A workaround for that is to set the default date format for the database user used by Sqoop to the "expected" format using a logon trigger in Oracle, like the one below:

CREATE OR REPLACE TRIGGER tr_a_l_set_date_format
   AFTER LOGON ON DATABASE WHEN (USER = 'SQOOP_USER')
   BEGIN
     execute immediate 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
   END;

A better form, though, would be to explicitly convert the date string literal to a DATE using a specific format, in the exact same way that OracleManager.datetimeToQueryString() does.

Issue Links

• links to: GitHub Pull Request #86