CODEWITHSUNDEEP
mysqlhdfsapache-drill
Shrinivas Deshmukh
Shrinivas Deshmukh

Reputation: 697

multi table join with apache drill

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)) ^

Upvotes: 1

Views: 1180

Answers (1)

Safvan CK
Safvan CK

Reputation: 1340

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`)

Upvotes: 2

Related Questions