Null filed names (Newbie question)

[kettle_anonymous]

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

[MattCasters]

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>

[MattCasters]

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

[kettle_anonymous]

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

[MattCasters]

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

[kettle_anonymous]

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