Tracking Issue: Support influxql raw query

[Rachelint]

Describe This Problem

We plan to support influxql mainly by converting it to sql in the statement level.

So the first thing to do is to process the difference between influxql and sql.

Difference is listed as following：

General

• Only string type tag, and string、int64、uint64、float64、boolean type field allowed.

Projection clause

• Automatically include some selected columns
  • timestamp key.
  • table(measurement) name.
  • group by key.
• Only fields can be parameters in function.
• Regex and special wilcards, like: feat: support rewrite basic raw query in influxql #683

select *::tag;

• use ::type to represent cast, if faield, null will be filled.
• Regex and special wilcards about tag may be covered by tags in group by. feat: support rewrite basic raw query in influxql #683
• Column name can be quoted in double quotes.
• Fill NULL to not exist column.

select * group by *

no tags will be in the project in the result.

• Regex and special wilcards in function call.
• All above in the subquery.

From clause

• Support regex.
• Support from multiple measurements(Not supported in short term).

Group by clause

• Regex and wilcard in group by like: feat: support rewrite basic raw query in influxql #683

select a from t group by *::tag / *

• Group by without aggregation.

Order by clause

• Only can order by time. https://docs.influxdata.com/influxdb/v1.8/query_language/explore-data/#basic-group-by-time-syntax

Proposal

• We should process the wildcard and regex by rewriting the influxql statement.
• Then we convert it to sql statement.
• Finally, throw it into datafusion.

Additional Context

• Integration tests: Integration tests for InfluxQL #716
  No response

[jiacai2050]

Other difference I found

Project clause

Column name can be quoted in double quotes

SELECT "level description","location","water_level" FROM "h2o_feet"

It will output string directly in SQL.

Source: https://docs.influxdata.com/influxdb/v1.8/query_language/explore-data/

[jiacai2050]

As for time() in groupby

• https://docs.influxdata.com/influxdb/v1.8/query_language/explore-data/#group-query-results-into-12-minutes-intervals-and-by-a-tag-key

> SELECT COUNT("water_level") FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m),"location"

name: h2o_feet
tags: location=coyote_creek
time                   count
----                   -----
2015-08-18T00:00:00Z   2
2015-08-18T00:12:00Z   2
2015-08-18T00:24:00Z   2

name: h2o_feet
tags: location=santa_monica
time                   count
----                   -----
2015-08-18T00:00:00Z   2
2015-08-18T00:12:00Z   2
2015-08-18T00:24:00Z   2

We can rewrite it to SQL, something like

SELECT
  to_timestamp_millis(DATE_BIN (INTERVAL '12' minute, timestamp, 0::TIMESTAMP) ) AS groupby_time,
  location,
  COUNT(`water_level`)  
FROM
    "h2o_feet"
WHERE
    timestamp >= '2015-08-18T00:00:00Z'
    AND timestamp <= '2015-08-18T00:30:00Z'
GROUP BY
    groupby_time,
    location;
    

Plan to write an optimize rule to do this.

Question: does sql parser support time(12m) in group by clause?

[Rachelint]

As for time() in groupby

* https://docs.influxdata.com/influxdb/v1.8/query_language/explore-data/#group-query-results-into-12-minutes-intervals-and-by-a-tag-key

> SELECT COUNT("water_level") FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m),"location"

name: h2o_feet
tags: location=coyote_creek
time                   count
----                   -----
2015-08-18T00:00:00Z   2
2015-08-18T00:12:00Z   2
2015-08-18T00:24:00Z   2

name: h2o_feet
tags: location=santa_monica
time                   count
----                   -----
2015-08-18T00:00:00Z   2
2015-08-18T00:12:00Z   2
2015-08-18T00:24:00Z   2

We can rewrite it to SQL, something like

SELECT
  to_timestamp_millis(DATE_BIN (INTERVAL '12' minute, timestamp, 0::TIMESTAMP) ) AS groupby_time,
  location,
  COUNT(`water_level`)  
FROM
    "h2o_feet"
WHERE
    timestamp >= '2015-08-18T00:00:00Z'
    AND timestamp <= '2015-08-18T00:30:00Z'
GROUP BY
    groupby_time,
    location;
    

Plan to write an optimize rule to do this.

Question: does sql parser support time(12m) in group by clause?

OK, I add them to the diffrence list.

[aierui]

This thing is the same as what 6112 they are doing.

[jiacai2050]

Thanks for your tips.

We have forked influxql related logical(parser and planner) into an independent crate already.

• https://github.com/CeresDB/influxql