KNOWLEDGE BASE

Hadoop Hive Date Fields Become Null After an Extract Refresh


Published: 22 Jun 2015
Last Modified Date: 24 Jan 2017

Issue

When refreshing a Cloudera Hadoop Hive extract created in an older version of Tableau Desktop, some date values might become NULL.

Environment

  • Tableau Desktop
  • Cloudera Hadoop Hive

Resolution

Option 1

Change the field back to a String in Tableau Desktop and create a calculated field to manually to convert it to a datetime value.

Option 2

Separate the date field from the Hour, Minute, and Second field; then combine them back into a unified date time. The calculated fields may have to be altered slightly to take into account the string format. 
  • Base Date DATE(LEFT([Datetime],10)) Take the M/D/Y and cast as a Date.
  • Hour INT(MID([Datetime],11,3)) Find the HH value and cast as an Integer. 
  • Minute INT(MID([Datetime],15,2)) Find the MM value and cast as and Integer. 
  • Second INT(MID([Datetime],18,2)) Find the SS value and cast as an Integer.
Drag all three time calculations from the Measures pane to the Dimensions Pane. 
Date Time Field 
DATEADD( 
'hour', [Hour], DATEADD( 
'minute', [Minute], DATEADD( 
'second', [Second], [Date Calc])))

 

Cause

The data source is queried with a DATETIME conversion.

Additional Information

The starting point for the MID argument will need adjusting in order to accommodate a different string length. The easiest way to understand this is to remove the INT section, and observe where the data string is starting. 
Did this article resolve the issue?