Sql query on druid datasource

When I try to sql query a druid datasource, this error is thrown:

query:

select * from table

error:

Unrecognized token ‘select’: was expecting (‘true’, ‘false’ or ‘null’) at [Source: (StringReader); line: 1, column: 7]

Is it even possible to sql query a druid datasource via metabase?
I can do that via Druid console though.

By the way I have a table containing a list of sales. Each sale has a category and a selling price. I need to get the avarage price of each category. In sql I’d do something like:

SELECT AVG(price) FROM table GROUP BY category

Any chance I can do this without sql on metabase?

Hi @enlo

Which version of Metabase?

I don’t have Druid, but you should be able to use SQL, just like with any other console.
I don’t know why it’s failing that plain query, but it looks like the error is returned from the database, not Metabase.
Can you make a question using the interface, like Simple question? Or do you have the same errors with all types of questions?

As for making queries without SQL - yes, that’s possible, and kinda what Metabase is all about.
I would recommend that you read the documentation, specifically the about creating questions:
https://metabase.com/docs/latest/users-guide/04-asking-questions.html

My fault: the table I was trying to simple query had all string fields so I could only count rows and I couldn’t find sum or avg options. I’ve changed types and it works good now.

SQL query still throwing error as above. I’m on version 0.33.3
Why do you think the error is returned from the db and not from metabase? (I’m kinda new to metabase)

@enlo
So the table you’re trying to use in SQL, which fails, what happens if you use Simple question to view the table? If that works, then click the Editor-icon top-right and convert to SQL by clicking top-right “View the SQL”. Then compare that to your failing query.
I think the error comes from something not-Metabase, since there’s no error string like that. But it could also come from the driver.

I followed your instructions and the raw query is:

{“descending”:null,“queryType”:“select”,“dimensions”:[“dimension1”,“dimension2”],“pagingSpec”:{“threshold”:1048576},“granularity”:“all”,“dataSource”:“datasourceName”,“context”:{“timeout”:60000,“queryId”:“a16dcaae-ebfd-4cef-b9f4-51b390fba169”},“intervals”:[“1900-01-01/2100-01-01”],“metrics”:[“metric1”,“metric2”]}

Apparently I can’t use sql to query druid, but I can run native queries. I tried to run this query in the sql/native editor and it works:

{
“queryType”: “select”,
“dataSource”: “datasource_name”,
“descending”: “false”,
“dimensions”:[],
“metrics”:[],
“granularity”: “all”,
“intervals”: [
“2018-01-01/2019-01-02”
],
“pagingSpec”:{“pagingIdentifiers”: {}, “threshold”:5}
}

@enlo
Have you enabled SQL queries on the server?
https://druid.apache.org/docs/latest/querying/sql#server-configuration
As I don’t run Druid, I don’t know if there’s a setting in Metabase > Admin > Databases too

Yes I have druid.sql.query=true in common.runtime.properties file. In fact I can use sql within druid console.
Anyway I don’t think I need to use sql anymore since I solved the mistake I was doing with simple query.

1 Like