Column analysis errors on 'query being null' - can't run analysis

One Star

Column analysis errors on 'query being null' - can't run analysis

using talend dq to connect w/ our data in Vertica DB
I'm attempting to run a column analysis on a 'email' column. I'm able to provide the specific column I want. I can even provide it an indicator (out of the box, ex: blank text, email pattern) to see if data in that column fits the pattern.
When I run the analysis, I get a popup with error
'Fail to run this analysis: " <analysis name that i provided>". Error message: At least one analysis execution failed, check the error logs for more details.'
Then I see the following message in the error log view
'2012-09-14 12:25:44,288 ERROR org.talend.dq.analysis.ColumnAnalysisSqlExecutor - Query not executed for indicator: "Blank text" query is null'
Also, the exception stack trace is not available.
in the analysis details, under Analysis Parameter, I've got Execution engine set to SQL. if i change that to Java I get the following error
'specified result set type is not supported'
Any insights would be appreciated,
regards,
Vivek
Employee

Re: Column analysis errors on 'query being null' - can't run analysis

Hi Vivek,
Please, have a look at the generated query for the blank count indicator.
You may right click on the indicator in the analysis and choose "view executed query" when the engine is of SQL type.
Or you may find it in the log before the error message.
I guess the query is incorrect for the vertica database.
If it's the case, then you may adapt the default query template (in the indicator's definition in Libraries/Indicators/System indicators/simple statistics/blank count indicator) so that it can be executed on a vertica database.
One Star

Re: Column analysis errors on 'query being null' - can't run analysis

Thanks Sebastiao,
in connecting with Veritca DB;
I see 'query not generated yet' when I hover over the 'Blank text' indicator label in my analysis. I've only seen a 'generated' query when I've defined my own indicator and used it in another analysis. With that said, my indicator was being used on a 'table analysis', this issue is with 'column analysis'. Could it be an issue with the type of analysis?
On a side note, I noticed this earlier that there are various query templates for a given indicator. I'm assuming that I can leave the multiple templates as is and that I don't have to manually go and delete the 'extra' templates for every indicator that I use. I'm assuming that somehow, TOSDQ will be able to get the 'right' template to use. Is this assumption correct?
Vivek
Employee

Re: Column analysis errors on 'query being null' - can't run analysis

Hi Vivek,
the query is actually only generated after you run the analysis.
It's possible that if you got an error on your indicator while you run the analysis, the query will not be stored and you won't be able to see it (maybe have a look in the error log, all queries are written when executed, but if you say that it's null, then of course, this is an error). The problem may come from the fact that this kind of indicator cannot currently work with your database.
Yes, there are different templates for different databases. As we don't have support for Vertica yet, the "default" template will be used. Have a look at the default blank count template which is:
SELECT COUNT(<%=__COLUMN_NAMES__%>) FROM <%=__TABLE_NAME__%> WHERE TRIM(<%=__COLUMN_NAMES__%>) = '' <%=__AND_WHERE_CLAUSE__%>
Does the TRIM function exist on Vertica?