sql - druid syntax to group by time (second/minute/hour/day/month/year)

Question

I am new to druid and I just ingested some data that has the following columns:

COLUMN      TYPE
======================
__time      TIMESTAMP
bId         VARCHAR
count       BIGINT
lId         VARCHAR
sum__v      BIGINT
sum_c       DOUBLE
sum_cId     BIGINT
sum_tc      BIGINT
sum_td      BIGINT
sum_ts      BIGINT
sum_vi      DOUBLE
sum_vs      DOUBLE

I want to get the average on a few columns grouped by minute. I can't seem to get the correct syntax since it seems each of these queries is causing errors:

SELECT AVG(sum_ts), AVG(sum_vi) FROM graph GROUP BY MINUTE;

Error: Unknown exception

Encountered "MINUTE ;" at line 3, column 10. Was expecting one of: "ARRAY" ... "CASE" ... "CUBE" ... "CURRENT" ... "CURRENT_CATALOG" ... "CURRENT_DATE" ... "CURRENT_DEFAULT_TRANSFORM_GROUP" ... "CURRENT_PATH" ... "CURRENT_ROLE" ... "CURRENT_SCHEMA" ... "CURRENT_TIME" ... "CURRENT_TIMESTAMP" ... "CURRENT_USER" ... "DATE" ... "EXISTS" ... "FALSE" ... "INTERVAL" ... "LOCALTIME" ... "LOCALTIMESTAMP" ... "MULTISET" ... "NEW" ... "NEXT" ... "NOT" ... "NULL" ... "PERIOD" ... "ROLLUP" ... "SESSION_USER" ... "SYSTEM_USER" ... "TIME" ... "TIMESTAMP" ... "TRUE" ... "UNKNOWN" ... "USER" ... <UNSIGNED_INTEGER_LITERAL> ... <APPROX_NUMERIC_LITERAL> ... <DECIMAL_NUMERIC_LITERAL> ... <BINARY_STRING_LITERAL> ... <QUOTED_STRING> ... <PREFIXED_STRING_LITERAL> ... <UNICODE_STRING_LITERAL> ... <LBRACE_D> ... <LBRACE_T> ... <LBRACE_TS> ... <LBRACE_FN> ... "?" ... "+" ... "-" ... <BRACKET_QUOTED_IDENTIFIER> ... <QUOTED_IDENTIFIER> ... <BACK_QUOTED_IDENTIFIER> ... <IDENTIFIER> ... <UNICODE_QUOTED_IDENTIFIER> ... "GROUPING" ... "(" ... "CAST" ... "EXTRACT" ... "POSITION" ... "CONVERT" ... "TRANSLATE" ... "OVERLAY" ... "FLOOR" ... "CEIL" ... "CEILING" ... "SUBSTRING" ... "TRIM" ... "CLASSIFIER" ... "MATCH_NUMBER" ... "RUNNING" ... "PREV" ... "JSON_EXISTS" ... "JSON_VALUE" ... "JSON_QUERY" ... "JSON_OBJECT" ... "JSON_OBJECTAGG" ... "JSON_ARRAY" ... "JSON_ARRAYAGG" ... "SPECIFIC" ... "ABS" ... "AVG" ... "CARDINALITY" ... "CHAR_LENGTH" ... "CHARACTER_LENGTH" ... "COALESCE" ... "COLLECT" ... "COVAR_POP" ... "COVAR_SAMP" ... "CUME_DIST" ... "COUNT" ... "DENSE_RANK" ... "ELEMENT" ... "EXP" ... "FIRST_VALUE" ... "FUSION" ... "HOUR" ... "LAG" ... "LEAD" ... "LEFT" ... "LAST_VALUE" ... "LN" ... "LOWER" ... "MAX" ... "MIN" ... "MINUTE" ... "MINUTE" "(" ...

org.apache.calcite.sql.parser.SqlParseException

SELECT AVG(sum_ts), AVG(sum_vi) FROM graph GROUP BY MINUTE(__time);

Error: Unknown exception

Encountered ";" at line 3, column 24. Was expecting one of: <EOF> "EXCEPT" ... "FETCH" ... "FILTER" ... "HAVING" ... "INTERSECT" ... "LIMIT" ... "OFFSET" ... "ORDER" ... "OVER" ... "MINUS" ... "UNION" ... "WINDOW" ... "WITHIN" ... "," ... "." ... "NOT" ... "IN" ... "<" ... "<=" ... ">" ... ">=" ... "=" ... "<>" ... "!=" ... "BETWEEN" ... "LIKE" ... "SIMILAR" ... "+" ... "-" ... "*" ... "/" ... "%" ... "||" ... "AND" ... "OR" ... "IS" ... "MEMBER" ... "SUBMULTISET" ... "CONTAINS" ... "OVERLAPS" ... "EQUALS" ... "PRECEDES" ... "SUCCEEDS" ... "IMMEDIATELY" ... "MULTISET" ... "[" ... "FORMAT" ... "IGNORE" ... "RESPECT" ...

org.apache.calcite.sql.parser.SqlParseException

What is the correct syntax to group by the minute? (or hour, or day, or month, or year?)

question from:https://stackoverflow.com/questions/65912667/druid-syntax-to-group-by-time-second-minute-hour-day-month-year

Answer 1

try:

SELECT
EXTRACT(MINUTE FROM __time) as minutes, AVG(sum_ts), AVG(sum_vi)
FROM graph
GROUP BY 1