There was a recent email exchange on the Apache Drill (SQL-on-Hadoop : https://drill.apache.org/) user mailing list regarding a limitation of SQuirreL SQL Client.
Here is the link to the thread on the mailing list :
http://mail-archives.apache.org/mod_mbox/drill-user/201705.mbox/%3C9c43150bec694b54b58cf8509df4297d%40SI-MBX1017.de.bosch.com%3E
The issue lies with SQuirreL SQL Client's inability to submit an entire query to Apache Drill in the event of a wild-card in the hadoop path (equivalent of the tablename).
Assume that we are querying a DFS path with the following nested directories (items in [] are the variations at that dir depth) : /nation_json/[a,b,c]/[1,2]/[y,n,idk]/nation.json
The user tries to submit a query that will query a select set of sub-directories (say, query all subdirectories, where the directory at depth 2 is 1) for a path on Hadoop DFS :
select * from dfs.root.nation_json/*/2/*/nation.json
However, users found that the query submitted is actually:
select * from dfs.root.nation_jsonnation.json
The SQuirreL SQL Client is submitting a truncated form of the actual query, leading to wrong results or SQL syntax errors.
For now, we're advising the community to use DBeaver for such use cases, but feel that more SQL clients should also be able to leverage the full capabilities of the Drill SQL Engine.
Can we get the developers behind SQuirreL SQL Client to fix this issue?
Thanks
Kunal Khatua
Please got to menu File -> New Session Properties -> Tab SQL ->
Uncheck the "Remove multi line comment (/.../) from SQL before sending to database" checkbox in the lower part of the panel.
That worked and resolved the issue. Thanks!
So the default of having this option checked is not optimal for new users of SQuirreL (at least not with certain databases)?
Generally I prefer avoiding to change long established defaults, but if there a compelling reason we all should be open to change. (I am not sure whether the Auto Commit SQL default is optimal either, especially as we have to scroll UP every time to see that option - so it is a bit hidden.)
BTW: I think the "perdefined" on the Session Properties -> SQL should be "predefined"