Drill Error: UNSUPPORTED_OPERATION ERROR: Only COUNT, MIN and MAX aggregate functions supported for VarChar type

0
votes

below is csv file.

csv file: 

STATE,place,populationmales,populationfemales,literatemales,literatefemales
JAMMU & KASHMIR,Urban,1855942,1558164,1386708,960337
Kupwara,Rural,418370,357952,245342,148633
Kupwara,Urban,56756,42486,39055,21046

Query:

select `state`,sum(`populationmales`) from dfs.`/Users/ks1162/Documents/pmv.csv` GROUP BY `state`;

When i query sum of 'populationmales' which is not a varchar type still getting error

error:

UNSUPPORTED_OPERATION ERROR: Only COUNT, MIN and MAX aggregate functions supported for VarChar type

Below is configuration of csv file in storage plugin:

"csv": {
  "type": "text",
  "extensions": [
    "csv"
  ],
  "extractHeader": true,
  "delimiter": ","
},
1
javasqlspring-bootapache-drill

1 Answers

0
votes

Drill by default reads data from CSV files as VARCHAR. You should either cast required columns to BIGINT:

select `state`,sum(cast(`populationmales` as BIGINT)) from dfs.`/Users/ks1162/Documents/pmv.csv` GROUP BY `state`;

or use schema provisioning feature (for more details please refer to https://drill.apache.org/docs/create-or-replace-schema/).