1

Hi peeps,

I want to use the Query Results data source to connect aggregated Elasticsearch results with another source. A problem I am facing is that my Elasticsearch query returns subbuckets within buckets which Redash converts into columns. This is all fine, however, I cannot reliably tell which column names are available.

When I try to query a non existent-column in the Query Results data source I am getting a “No such column” error. So I’d like to know if there is any way to select the existing columns in the redash SQLite syntax in order to avoid running into this error (or maybe even do something like SELECT IF COLUMN EXISTS, ELSE SELECT 0).

Any help would be greatly appreciated.

2

I know this is an old thread but I came up with a workaround for this.

The QRDS works by pushing your query result into an in-memory SQLite database at run-time. As such, you can use SQLite’s built-in reflection features to list the column names.

with sample as (SELECT * FROM query_179650)

select t.tbl_name, c.name col_name
from 
  sqlite_master t, 
  pragma_table_info(t.tbl_name) c
where t.type = 'table';

The opening CTE causes Redash to create the in-memory SQLite database. The actual SELECT statement borrows this trick from SO. It’s not as convenient as a full-blown schema viewer (which would be challenging to implement). But if you save this as a Query Snippet you can reference the fields on a QRDS query with ease.

2 Likes