Usage with temporary table functions

[sxm51]

How do we use (temp) table functions with macros. e.g. file.

Couldn't figure how to use the (filter) macros. (Since we can't select the database, table in query builder upfront) As a workaround, Is there a way to manually define the variables expected by the macros, so we could use the date, time macros.

Thanks.

[Slach]

which macros do you mean?

system.macros from clickhouse? Or template variables from grafana?

Could you share your query? and explain what do you try to achieve?

[sxm51]

Meant Grafana macros/template-variables, such as $timeFilter. As I understand, $timeFilter requires us to set the $dateCol, $dateTimeCol, via grafana UI. However, since the table is temporary and created on the fly, we cannot select those columns using the Grafana UI/wizard.

Wanted to understand, if we there is way to define the $dateCol, $dateTimeCol outside of Grafana UI workflow.

Goal is to use built in Grafana macros/template-variables where possible, so it remains close to the query conventions of regular tables. (Main one being to use the $timeFilter)

Here is an example query. Want to replace the WHERE date > toDate($from) AND date < toDate($to) with the $timeFilter or so. Do you think its feasible?

SELECT
  toDate(parseDateTimeBestEffort(splitByChar('/', _path)[-2])) AS date,
  count() AS count
FROM file('path/to/file', 'CSV')
WHERE date > toDate($from) AND date < toDate($to)
GROUP BY date

[Slach]

you need to know column name for apply $timeFilter as is

for you case could write $timeFilterByColumn(date)