Skip to main content

Jython Evaluator converts a field of "Date" type to a field of "DateTime" type.

  • February 14, 2022
  • 1 reply
  • 195 views
AkshayJadhav
StreamSets Employee
Forum|alt.badge.img

ISSUE: Jython Evaluator converts a field of "Date" type to a field of "DateTime" type. 

 

SOLUTION: If the target system needs to read the data as "DateTime", you can use the Field Type Converter processor to convert the "DateTime" type to "Date".

Fundamentally the SDC record types and scripting processor types do not have 1:1 mapping - this is due different types on the scripting language side and we always try to be careful. In this case, the return object from Jython is "DateTime" and the Data Collector cannot know if the "Time" aspect is or is not intentionally used. Therefore using the Field Type Converter processor is generally the right approach.

Did this topic help you find an answer to your question?

1 reply

R
Roadie

Thank you Akshay. But we are using same pipeline fragment and jython evaluator inside the fragment to convert all sql datetime to Snowflake datetime, Sql datetime null to SF null datetime and SQL date to SF date. Able to convert the string values having YYYY-MM-DD HH:MI:SS.FF/YYYY-MM-DD HH:MI:SS to datetime in Streamsets and for null string have used constant “NULL_DATE” as there is no other way to have NULL as date type date. Now the problem is I am not able to convert YYYY-MM-DD to date format so that to load in Snowflake. I cannot use type converter as you have suggested as we have more than 500 tables and we need to make this dynamic instead of hardcoding the column names in field type converter. Please see the Jython code below and help to solve the same:

 

import datetime as dt

for record in records:
  try:
    for colName,value in record.value.items():
      if 'unicode' in str(type(value)):
        if (len(value) == 19 or len(value) == 26 or len(value) == 27 or len(value) == 10) and '-' in value and '2' in value:
          
          if len(value) == 26 and value[4] == '-' and value[7] == '-'  and value[13] == ':'  and value[19] == '.' :
            record.value[colName] = dt.datetime.strptime(value, '%Y-%m-%d %H:%M:%S.%f') - dt.timedelta(hours=5, minutes=30)
          
          elif len(value) == 27 and value[4] == '-' and value[7] == '-'  and value[13] == ':'  and value[19] == '.' :
            record.value[colName] = dt.datetime.strptime(value[:-2], '%Y-%m-%d %H:%M:%S.%f') - dt.timedelta(hours=5, minutes=30)
            
          elif len(value) == 19 and value[4] == '-' and value[7] == '-' and value[13] == ':':
            record.value[colName] = dt.datetime.strptime(value, '%Y-%m-%d %H:%M:%S') - dt.timedelta(hours=5, minutes=30)
            
          elif len(value) == 10 and value[4] == '-' and value[7] == '-':
            record.value[colName] = ‘Please help here to convert to date instead of datetime’ → This needs to be in date so that it can be inserted as date in Snowflake otherwise I am having issue datetime cannot be inserted in date field in destination
            
      elif 'date' in str(colName) and 'NoneType' in str(type(value)):
        record.value[colName] = NULL_DATE
          
    # Write record to the output
    sdc.output.write(record)

  except Exception as e:
    # Send record to error
    sdc.error.write(record, str(e))

Reply


Terms & Conditions