Support Questions
Find answers, ask questions, and share your expertise

Can the SQL generated by a QueryDatabaseTable processor be overridden with custom SQL? For example, to insert a sub-SELECT or calculated column?

Highlighted

Can the SQL generated by a QueryDatabaseTable processor be overridden with custom SQL? For example, to insert a sub-SELECT or calculated column?

Rising Star
 
3 REPLIES 3
Highlighted

Re: Can the SQL generated by a QueryDatabaseTable processor be overridden with custom SQL? For example, to insert a sub-SELECT or calculated column?

I do not know the answer but am very interested in hearing whether this is possible or not. If not, it should be a feature request.

Highlighted

Re: Can the SQL generated by a QueryDatabaseTable processor be overridden with custom SQL? For example, to insert a sub-SELECT or calculated column?

Rising Star

So, while I have not heard back from Hortonworks or anyone here on HCC, I have done some experimentation on my own. Within the "Columns to Return" property, you can add or override a column with any SQL that would be valid within the first clause of a SELECT statement. I have been able to insert both basic SQL functions (e.g., TO_CHAR) and complex subselects (including WHERE clause references to other columns in the base table). Subselects need to be identified with a column name.

For example, this will work as a value for Columns to Return:

ID, NAME, SELECT 'test' FROM DUAL AS MY_TEST_TEXT

If you need to override a WHERE clause in the SELECT, you should be able to use a GeneratetableFetch processor to do that, although I have not experimented with that.

Re: Can the SQL generated by a QueryDatabaseTable processor be overridden with custom SQL? For example, to insert a sub-SELECT or calculated column?

Super Guru

It is not currently possible, feel free to file a New Feature or Improvement Jira to cover this. As @William Brooks mentioned, it may be possible to put some functions into the Columns To Return property, but be aware that this may not always work for your database. For example, I don't believe it works for Oracle because the columns to return are included in a nested SELECT query when doing pagination, so it would give an error that the same alias is being used for two different columns (the one in the nested select and the one in the outer select; although they end up being the same column, it is not allowed).

An alternative might be to use GenerateTableFetch, which instead of executing the SQL like QueryDatabaseTable, outputs the SQL itself. You might be able to follow GenerateTableFetch with ReplaceText or something to tweak the SQL before execution (presumably by ExecuteSQL or PutSQL).

Don't have an account?