Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Problem Loading date from text file to Luciddb

  1. #1
    Join Date
    Nov 2009
    Posts
    11

    Default Problem Loading date from text file to Luciddb

    Hi,

    I'm trying to evaluate using Lucid in place of Postgres for an OLAP project. Lucid is latest stable release. PDI is latest release 4.2. I'm using Streaming Loader.

    I'm reading from text file including a DATE field. I'm loading that into a DATE field on Lucid. I get an error that TIMESTAMP cannot be converted to DATE. If I convert the LUCID table to TIMESTAMP, then it loads no problem.

    This is what it's trying to do:
    CAST (null AS TIMESTAMP) AS "repdte"

    This is the error:
    Cannot assign to target field 'REPDTE' of type DATE from source field 'repdte' of type TIMESTAMP

    Q: How can I format my incoming DATE field in PDI so I do not need to change my LUCID data type from date to timestamp for it to work.

    Note that when I use regular method to load to LUCID (not the Streaming Loader), it worked without any issues (same formatting on PDI and LUCIDDB data type defined as DATE).

    Any help appreciated.
    Regards,
    Sean

  2. #2
    Join Date
    Jun 2007
    Posts
    260

    Default

    Sean

    You should be able to specify the format of the date against the input field in the text file read step.

    Alternatively, if you place a SELECT transformation step somewhere between your read file and table output steps, you can change the metadata of the field to specify the output format.

    I normally read date fields as strings and then use the combination of mappings and select to correct the data and format the output. Mappings because dates like blank and null fields sometime results in errors because the database may not recognise NULL or a blank/empty string ("") to be a valid date.
    Last edited by crafter; 11-21-2011 at 01:34 AM. Reason: spell.

  3. #3
    Join Date
    Nov 2009
    Posts
    11

    Default

    Thanks crafter for your reply. But that's not the solution as I'm already converting the string date to DATE format before loading to LUCID. Also when I don't use the Steam Loader, it loads fine. In PDI, there is only DATE format with a mask (I'm using yyyy/mm/dd). There is no TIMESTAMP yet LUCID thinks that the column sent to it is in TIMESTAMP format (the date is just like 2011/11/21 w/o any TIME or anything else).

  4. #4

    Default same problem

    Hi psm2000,

    I have the same problem.

    I'm using a select values before my LucidDB Streaming Loader. In my select values, for my date field, I'm using a DATE type and format yyyy/mm/dd.

    I then have the error :
    ,CAST (null AS TIMESTAMP) AS "date_from
    Cannot assign to target field 'date_from' of type DATE from source field 'date_from' of type TIMESTAMP

    Did you find a solution to your problem ?

    In my table, the data type of the field date_from is DATE.
    I'm using pdi.4.2.1

  5. #5
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    I have to admit I only ever tested with TIMESTAMP fields on LucidDB, not DATE.
    So if LucidDB has a problem with the cast, perhaps we need to supply another column in the Fields tab to allow us to give hints to LucidDB.

    Did you guys ever find or file a JIRA case for this problem?

  6. #6

    Default

    Hi MattCasters,

    Is there a way to solve this problem by using a Select values step before the LucidDB Streaming Loader ?

    If i add a column in the fields tab of the LucidDB Streaming loader step, will i have to add a column also in my tables in LucidDB ?

  7. #7

    Default

    I have the same issue any solution ¿?

  8. #8
    Join Date
    Nov 1999
    Posts
    459

    Default

    FYI: JIRA case is over here:
    http://jira.pentaho.com/browse/PDI-8222

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.