Issue in JOIN query in apache drill

1
votes

File stored in Hive:

[
  {
    "occupation": "guitarist",
    "fav_game": "football",
    "name": "d1"
  },
  {
    "occupation": "dancer",
    "fav_game": "chess",
    "name": "k1"
  },
  {
    "occupation": "traveller",
    "fav_game": "cricket",
    "name": "p1"
  },
  {
    "occupation": "drummer",
    "fav_game": "archery",
    "name": "d2"
  },
  {
    "occupation": "farmer",
    "fav_game": "cricket",
    "name": "k2"
  },
  {
    "occupation": "singer",
    "fav_game": "football",
    "name": "s1"
  }
]

CSV file in hadoop:

name,age,city
d1,23,delhi
k1,23,indore
p1,23,blore
d2,25,delhi
k2,30,delhi
s1,25,delhi

I queried them individually, it's working fine. Then, I tried join query:

select * from hdfs.`/demo/distribution.csv` d join hive.demo.`user_details` u on d.name = u.name

I got the following issue:

org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: DrillRuntimeException: Join only supports implicit casts between 1. Numeric data 2. Varchar, Varbinary data 3. Date, Timestamp data Left type: INT, Right type: VARCHAR. Add explicit casts to avoid this error Fragment 0:0 [Error Id: b01db9c8-fb35-4ef8-a1c0-31b68ff7ae8d on IMPETUS-DSRV03.IMPETUS.CO.IN:31010]

2
apache-drill

2 Answers

0
votes

Please refer this https://drill.apache.org/docs/data-type-conversion/ We need to do explicit typecasting to deal with such scenario.

Consider we have a JSON file employee.json and a csv file sample.csv. In order to query on both at the same time , in one query we need to do type casting.

0: jdbc:drill:zk=local> select emp.employee_id, dept.department_description, phy.columns[2], phy.columns[3] FROM cp.`employee.json` emp , cp.`department.json` dept, dfs.`/tmp/sample.csv` phy where CAST(emp.employee_id AS INT) =  CAST(phy.columns[0] AS INT) and emp.department_id = dept.department_id;

Here we are typecasting CAST(emp.employee_id AS INT) = CAST(phy.columns[0] AS INT) so that equality does not fail.

Refer this for more detail:- http://www.devinline.com/2015/11/apache-drill-setup-and-SQL-query-execution.html#multiple_src

0
votes

You need to cast even though by default it has taken varchar. Try this:

select * from hdfs.`/demo/distribution.csv` d join hive.demo.`user_details` u on cast(d.name as VARCHAR) = cast(u.name as VARCHAR)

But you cannot refer to column name directly from csv. you need to consider columns[0] for name.