How can I do a query on a csv file using the header file with apache drill

0
votes

I have a problem querying a csv file with header like this:

ID, NOMBRE, APELLIDOS, PROVINCIA
12121212, MARIA, LIONZA, MADRID
12312312, JAIMITO, PEREZ, ALMERÍA
13131313, BRUNO, DIAZ, MALAGA
23423423, HARLEY, QUINN, BARCELONA

I am doing it over apache drill UI v1.8. When a do the following query
SELECT * FROM dfs.'path_to_file/clientes.csv' it works perfectly and it returns a table like this: enter image description here

But if I do a query specifying the columns names (the headers of the csv file) then some columns are empty for any reason I haven´t figured out yet and is driving me crazy p.e. this query
SELECT ID, NOMBRE FROM dfs.'path_to_file/clientes.csv'

Return this

enter image description here

Also I edited the dfs plugin and added the property extractHeader to true

...
"csv": {
      "type": "text",
      "extensions": [
        "csv"
      ],
      "extractHeader": true,
      "delimiter": ","
    },
...

So what I'm doing wrong? why I can query by ID but not by the other fields (header names) like NOMBRE or PROVINCIA. Do you have any idea?

2
csvapache-drill

2 Answers

2
votes

I believe the issue you are encountering is due to the spaces in the header fields.

To verify this hypothesis, try this query (Note the spaces and back ticks in the field names):

SELECT `ID `, `NOMBRE ` FROM <your file>

The easy fix is to remove the spaces in the header.

0
votes

Seems like a bug.

Removed "extractHeader from the dfs plugin.

...
"csv": {
      "type": "text",
      "extensions": [
        "csv"
      ],
      "delimiter": ","
    },
...

and tried with older approach 

select columns[0] as id ,columns[1] as NOMBRE from `a.csv`;

Output:

+-----------+-----------+
|    id     |  NOMBRE   |
+-----------+-----------+
| ID        |  NOMBRE   |
| 12121212  |  MARIA    |
| 12312312  |  JAIMITO  |
| 13131313  |  BRUNO    |
| 23423423  |  HARLEY   |
+-----------+-----------+

Works fine.