Hello All,
I am new to Knime platform. I have been using Alteryx for last 4 years and trying to see if I can replace Alteryx with Knime.
I am trying to create a simple workflow which issues a custom SQL to Denodo DB. The dataset is huge so I want to limit it o 100000 records. I am not able to add the Limit clause in the custom SQL. Any idea how it can be done?
If you come from Alteryx there is this free book to help you on the way:
Thank you
@mlauber71
for quick response. I did try adding the limit option in my Custom SQL but ran into this error. Query works fine if I remove the Limit clause.
image
1258×910 154 KB
@ShriatKNIME
you might have to just use the LIMIT clause alone in the DB Table Selector. It also might depend on your database.
image
1886×1304 266 KB
I have the same problem when using Knime with Denodo data source.
The cause is Denodo does not support LIMIT in subqueries, while Knime will generate all sorts of subqueries by default.
Explain the Knime subquery: In “DB Query” node , you can see the actual query by checking the “Parsed statement” after evaluate. It is like
"SELECT * FROM (SELECT … FROM … LIMIT ) AS … "
Here LIMIT is in the subquery, which won’t work in Denodo. Same thing happens if using “custom query” in “DB table selector”.
Solution:
In Knime, after “DB Connector” node, use “DB Query reader” and write sql scripts there. Don’t use “DB table selector”.
Hi
@pengchenhui
, whilst some databases use the non-standard LIMIT clause, I believe from looking at some Denodo community forums that Denodo does also support the ansi standard “fetch first n rows only” clause.
e.g. to fetch just 100 rows from table MYTABLE you would use:
select * from table MYTABLE fetch first 100 rows only
Typiclly, the “fetch first” clause can in most (maybe all?) databases that support it, appear in a subselect whereas the LIMIT clause often can’t be, so that might be worth a try.
select x,y,z from (select * from table MYTABLE fetch first 100 rows only)
Edit: Having just scrolled back up this thread, I see that
@ArjenEX
already mentioned that in relation to Denodo. Have you tried it?
Hi
@pengchenhui
, that seems like a rather poor implementation by Denodo and I would find that a very painful limitation as there are plenty of “real world” times outside of KNIME where I’ve wanted to limit the return in a sub query to just the first row it finds.
From KNIME’s perspective, I can see why it “wraps” queries in an outer
select * from (... )
. I’ve done similar myself when writing generic query builders outside of KNIME. It allows the whole process to retain control over the query that is being built inside, and can provide the ability to easily rename or cast the returned data columns without having to write a query-parser.
In terms of the use of the inner subquery when the “evaluate” button is pressed, the purpose there is to limit the returned query to just 10 rows. Placing the query inside a subquery and then limiting the returned rows on the outer query is a standard generic method that should work with just about any database. ( Although seemingly not Denodo in this instance
).
Instead of using LIMIT or FETCH FIRST, I think Denodo can also limit rows using the “old fashioned” method of ROWNUM function. I used to have to do something similar with Oracle before they included the “fetch” clause.
If it’s anything like Oracle’s implementation, it’s not as good as the other methods in all circumstances especially if you are trying to return data using an ORDER BY clause, because unlike FETCH FIRST, ROWNUM is evaluated BEFORE ordering, whereas FETCH FIRST is evaluate AFTER ordering, but you could try using:
where rownum() < 100
select * from table MYTABLE where rownum()<100
in your query and see if that works. (In Oracle we didn’t include the brackets, but I think brackets are required by Denodo)
If you need to return the first 100 rows, after some kind of ordering, ironically you might have to use a subquery yourself:
select * from
(select x,y,z from MYTABLE order by x)
where rownum() < 100
EDIT: I’ve just looked at some more Denodo documentation that says the rownum() function “can only be used in a select clause”, so you would unfortunately have to write this as
select * from
(select rownum() as rownum, x,y,z from MYTABLE ... etc)
where rownum < 100
A further alternative (which I think is horrible, but if all else fails, might be an option) is that to bring back a limited dataset in a specific instance you could write the query as a view, containing the fetch clause.
create view my_limited_view as
select x,y,z from mytable fetch first 100 rows only
and then in KNIME you would use the query
select x,y,z from my_limited_view
It’s not something I’d recommend or want to do myself but it’s another approach if everything else fails.
@takbb@everyone
Now that answer deserves kudos, especially when the person (I assume) is not working with denodo at all but still wants to help. Exactly this makes KNIME community so special to me
Waw, i have always mentoned it @takbb is a legend…taking his time to answer complicated question
Kudos
).