Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Null filed names (Newbie question)

  1. #1
    Join Date
    Sep 2005
    Posts
    1,403

    Default Null filed names (Newbie question)

    Hello:


    I am attempting to run a SQL query, that extracts/calculates/combines data from several tables and insert this result set into a reporting datamart.



    The set-up is Spoon running against a My-Sql Database. The My-Sql driver I am using is: mysql-connector-java-5.0.0-beta-bin.jar.



    The Spoon set has three steps:
    Step 1: Calcluate Date Range For Yesterday (Get System Info Step)
    Step 2: Get CMP Data (Table Input Step)
    Step 3: Insert CMP Data into Reporting Database (Table Output Step)



    In Step 2 the query I am attempting to run is:



    SELECT IF(sec.nickname not like 'site%' or sec.nickname is null, 'site-Alpha', sec.nickname) AS 'section', msa.time_opened, u.email, u.first_name, u.last_name, SEC_TO_TIME(UNIX_TIMESTAMP(MAX(cs.time_started))-UNIX_TIMESTAMP(MIN(msa.time_opened))) AS 'elapsed_time',
    COUNT(DISTINCT cr.time_requested) AS 'pages'
    FROM users u,
    sessions s,
    meme_space_activations msa
    LEFT JOIN content_retrievals cr ON msa.id = cr.meme_space_activation_id
    LEFT JOIN content_searches cs ON msa.id = cs.meme_space_activation_id
    LEFT JOIN sections sec ON msa.meme_space_id = sec.meme_space_id
    WHERE msa.session_id = s.id
    AND u.id = s.user_id
    AND msa.time_opened > ?
    AND msa.time_opened < ?
    GROUP BY s.id, msa.time_opened
    ORDER BY 1 ASC, 2 ASC, 5 ASC;



    Using Spoon, I have set up a Table Input step. The output of this query has "null" fields. The AS 'section' is not setting the field value correctly.



    Any help is appreciated.
    Chanu

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

    Default RE: Null filed names (Newbie question)

    Here is the translation in case anyone needs it:

    <pre>
    Hello:

    I am attempting to run a SQL query, that extracts/calculates/combines data from several tables and insert this result set into a reporting datamart.

    The set-up is Spoon running against a My-Sql Database. The My-Sql driver I am using is: mysql-connector-java-5.0.0-beta-bin.jar.

    The Spoon set has three steps:
    Step 1: Calcluate Date Range For Yesterday (Get System Info Step)
    Step 2: Get CMP Data (Table Input Step)
    Step 3: Insert CMP Data into Reporting Database (Table Output Step)

    In Step 2 the query I am attempting to run is:

    SELECT IF(sec.nickname not like 'site%' or sec.nickname is null, 'site-Alpha', sec.nickname) AS 'section', msa.time_opened, u.email, u.first_name, u.last_name, SEC_TO_TIME(UNIX_TIMESTAMP(MAX(cs.time_started))-UNIX_TIMESTAMP(MIN(msa.time_opened))) AS 'elapsed_time',
    COUNT(DISTINCT cr.time_requested) AS 'pages'
    FROM users u,
    sessions s,
    meme_space_activations msa
    LEFT JOIN content_retrievals cr ON msa.id = cr.meme_space_activation_id
    LEFT JOIN content_searches cs ON msa.id = cs.meme_space_activation_id
    LEFT JOIN sections sec ON msa.meme_space_id = sec.meme_space_id
    WHERE msa.session_id = s.id
    AND u.id = s.user_id
    AND msa.time_opened > ?
    AND msa.time_opened < ?
    GROUP BY s.id, msa.time_opened
    ORDER BY 1 ASC, 2 ASC, 5 ASC;

    Using Spoon, I have set up a Table Input step. The output of this query has "null" fields. The AS 'section' is not setting the field value correctly.

    Any help is appreciated.
    Chanu
    </pre>

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

    Default RE: Null filed names (Newbie question)

    Hi Chanu,

    I&#39;m sure this is not a Kettle problem but rather another bug for MySQL.

    On the date-range, I advice you to:

    1) use >= and <
    2) use a kettle date range (start & end of date range from get system info)
    You need to use a logging table to set actually use this.

    This way you never lose data, even if the transformation could not be run for several days.

    Matt

  4. #4
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: Null filed names (Newbie question)

    Hello Matt:

    Thanks for your reply. The error I am seeing is:

    Prepared statement : INSERT INTO mspoke_usage(, time_opened, email, first_name, last_name, , pages) VALUES ( ?, ?, ?, ?, ?, ?, ?)

    The issue is that the fields defined using the AS keyword in the select statement are not being properly represented in the output of the Get CMP Usage Data step. The Show Output Fields command displays:


    Fieldname Type Length Precision Step origin
    String 255 - Get CMP Usage Data
    time_opened Date - - Get CMP Usage Data
    email String 255 - Get CMP Usage Data
    first_name String 255 - Get CMP Usage Data
    last_name String 255 - Get CMP Usage Data
    Date - - Get CMP Usage Data
    Integer 15 0 Get CMP Usage Data


    Notice that the first, sixth and the seventh field-names are missing. I am not sure how to fix this…

    I have attached the repository xml file for your convenience.


    Thanks for you help,
    Chanu

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

    Default RE: Null filed names (Newbie question)

    Again, this 100% certain a JDBC bug.
    There is little you can do about it besides complaining about it at bugs.mysql.com

    The fields you see comes straight out of the meta-data of the result from the query.
    If the fields are not shown, then there is something wrong with the JDBC driver.
    I strongly suggest you try a couple of stable JDBC drivers (3.1.12 for example) to see if it happens everywhere.

    Another option is to simply do the IF-THEN calculations in Kettle. That might save you some trouble.

    Matt

  6. #6
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: Null filed names (Newbie question)

    Hello Matt:

    Thanks you for your help. It was a jdbc-driver issue. I downloaded the latest production version and everything is working correctly.

    Again thanks for your help.
    Chanu

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.