multi table join with apache drill

Question

I am trying to join one table from mysql and 2 csv files from hdfs, so as a whole I'm trying to join 3 tables using apache drill.

It works when I join mysql table and 1 dfs file, but fails when I try to join 1 mysql table and 2 dfs csv files. Please help.

Here is the code:

select CAST(a.Longitude AS DOUBLE),CAST(a.Latitude AS DOUBLE) 
from ((MySQL.dummy.`accident_05_07` as a 
inner join dfs.`/user/drill/accidents_2009_to_2011.csv` as b 
on a.Longitude=CAST(b.Longitude AS DOUBLE)) 
inner join dfs.`/user/drill/accidents_2012_to_2014.csv` as c 
on a.Longitude=CAST(c.Longitude AS DOUBLE))

[Error Id: 90c4ac92-83e9-4b23-bcd9-5c1535cd58ad on inpunpclx1825e.kih.kmart.com:31010] org.apache.drill.common.exceptions.UserRemoteException: PARSE ERROR: Non-query expression encountered in illegal context SQL Query select CAST(a.Longitude AS DOUBLE),CAST(a.Latitude AS DOUBLE) from((MySQL.dummy.accident_05_07 as a inner join dfs./user/drill/accidents_2009_to_2011.csv as b on a.Longitude=CAST(b.Longitude AS DOUBLE)) inner join dfs./user/drill/accidents_2012_to_2014.csv as c on a.Longitude=CAST(c.Longitude AS DOUBLE)) ^

Also posting error message with stacktrace would be useful to help identify the problem. — Arina Yelchiyeva
The problem in your query is your extra paranthesis. If you remove them your sql will run. The join will be from the first table to last table in order. — Vezir

Safvan CK Safvan CK · Accepted Answer · 2018-05-11T14:54:35

Try this way:

SELECT `a`.`Longitude`,`a`.`Latitude` 
FROM ((`MySQL`.`dummy`.`accident_05_07` `a` 
INNER JOIN `dfs`.`/user/drill/accidents_2009_to_2011.csv` `b` 
ON `a`.`Longitude`=`b`.`Longitude`) 
INNER JOIN `dfs`.`/user/drill/accidents_2012_to_2014.csv` `c` 
ON `a`.`Longitude`=`c`.`Longitude`)

multi table join with apache drill

1 Answers