I am trying to join two tables from different data sources(RDBMS and json), however, the query is successful but the data in one table(json data source) is UPPERCASE and data in another table(RDBMS data source) is LOWERCASE. As case sensitive comparison is happening there is not data in the resultant table.
select * from dfs.`table1` as t1 join plugin.`table2` as t2 on t1.a = t2.a limit 10
No results from the above query.
However, I have achieved to get results by using UPPERCASE and LOWERCASE functions available in SQL:2014.
select * from dfs.`table1` as t1 join plugin.`table2` as t2 on UPPERCASE(t1.a) = t2.a limit 10
I would like to know whether I can configure drill to do a case-insensitive comparison instead of using UPPERCASE and LOWERCASE functions.
Short answer - You can't.
In big RDBMS you can achieve this by setting collation to utf8-ci or similar but as far I know, here is no option in Drill for that.
In general, you should avoid implicit case-insensitive joins because it can often lead to unforeseen results. Comparing UPPERCASE()'d values is best practice in this case.