KNOWLEDGE BASE

Hadoop Hive Date Fields Become Null After an Extract Refresh


Published: 22 Jun 2015
Last Modified Date: 11 Oct 2019

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. 



Discuss this article... Feedback Forum
Did this article resolve the issue?