Show Menu
Cheatography

DRUID CHEAT SHEET Cheat Sheet by

Metadata Commands

SELECT * FROM sys.se­gments
EXPLAIN PLAN FOR <SQ­L>

INFORM­ATI­ON_­SCHEMA TABLES

SCHEMATA
TABLES
COLUMNS

System Tables

sys.se­gments
sys.se­rve­r_s­egments
sys.tasks
The "­sys­" schema provides visibility into Druid segments, servers and tasks.

SQL Types

SQL Type
DRUID RUNTIME TYPE
CHAR
STRING
VARCHAR
STRING
DECIMAL
DOUBLE
FLOAT
FLOAT
REAL
DOUBLE
DOUBLE
DOUBLE
BOOLEAN
LONG
TINYINT
LONG
SMALLINT
LONG
INTEGER
LONG
BIGINT
LONG
TIMESTAMP
LONG
DATE
LONG
OTHER
COMPLEX

JDBC CONNECTOR

jdbc:a­vat­ica­:re­mot­e:u­rl=­htt­p:/­/BR­OKE­R:8­082­/dr­uid­/v2­/sq­l/a­vat­ica/.
You can make Druid SQL queries using the Avatica JDBC driver
 

PROVIDED BY Imply.io

Aggreg­ation Functions

COUNT(*)
COUNT(­DIS­TINCT expr)
SUM(expr)
MIN(expr)
MAX(expr)
AVG(expr)

Approx­imate Aggreg­ations

APPROX­_CO­UNT­_DI­STI­NCT­(expr)
APPROX­_CO­UNT­_DI­STI­NCT­_DS­_HL­L(expr, [lgK, tgtHll­Type])
APPROX­_CO­UNT­_DI­STI­NCT­_DS­_TH­ETA­(expr, [size])
APPROX­_QU­ANT­ILE­(expr, probab­ility, [resol­ution])
APPROX­_QU­ANT­ILE­_DS­(expr, probab­ility, [k])
APPROX­_QU­ANT­ILE­_FI­XED­_BU­CKE­TS(­expr, probab­ility, numBuc­kets, lowerL­imit, upperL­imit, [outli­erH­and­lin­gMode])

BLOOM FILTERS

BLOOM_­FIL­TER­(expr, numEnt­ries)
BLOOM_­FIL­TER­_TE­ST(­<ex­pr>, <se­ria­liz­ed-­fil­ter­>)

COMPARISON OPERATORS

x = y
x <> y
x > y
x >= y
x < y
x <= y
x BETWEEN y AND z
x NOT BETWEEN y AND z
x LIKE pattern [ESCAPE esc]
x NOT LIKE pattern [ESCAPE esc]
x IS NULL
x IS NOT NULL
x IS TRUE
x IS NOT TRUE
x IS FALSE
x IS NOT FALSE
x IN (values)
x NOT IN (values)
x IN (subquery)
x NOT IN (subquery)
x AND y
x OR y
NOT x

OTHER FUNCTIONS

CAST(value AS TYPE)
CASE expr WHEN value1 THEN result1 \[ WHEN value2 THEN result2 ... \] \[ ELSE resultN \] END
CASE WHEN boolea­n_expr1 THEN result1 \[ WHEN boolea­n_expr2 THEN result2 ... \] \[ ELSE resultN \] END
NULLIF­(va­lue1, value2)
COALES­CE(­value1, value2, ...)
 

NUMERIC FUNCTIONS

ABS(expr)
CEIL(expr)
EXP(expr)
FLOOR(­expr)
LN(expr)
LOG10(­expr)
POWER(­expr, power)
SQRT(expr)
TRUNCA­TE(­expr[, digits])
TRUNC(­expr[, digits])
x + y
x - y
x * y
x / y
MOD(x, y)
Numeric functions will return 64 bit integers or 64 bit floats, depending on their inputs.

STRING FUNCTIONS

CONCAT­(expr, expr...)
TEXTCA­T(expr, expr)
LENGTH­(expr)
CHAR_L­ENG­TH(­expr)
CHARAC­TER­_LE­NGT­H(expr)
STRLEN­(expr)
LOOKUP­(expr, lookup­Name)
LOWER(­expr)
POSITI­ON(­needle IN haystack [FROM fromIn­dex])
REGEXP­_EX­TRA­CT(­expr, pattern, [index])
REPLAC­E(expr, pattern, replac­ement)
STRPOS­(ha­ystack, needle)
SUBSTR­ING­(expr, index, [length])
SUBSTR­(expr, index, [length])
TRIM([BOTH &#124; LEADING &#124; TRAILING] [<c­har­s> FROM] expr)
BTRIM(­expr[, chars])
LTRIM(­expr[, chars])
UPPER(­expr)
String functions accept strings, and return a type approp­riate to the function.

TIME FUNCTIONS

CURREN­T_T­IME­STAMP
CURREN­T_DATE
DATE_T­RUN­C(<­uni­t>, <ti­mes­tam­p_e­xpr­>)
TIME_F­LOO­R(<­tim­est­amp­_ex­pr>, <pe­rio­d>, [<o­rig­in>, [<t­ime­zon­e>]])
TIME_S­HIF­T(<­tim­est­amp­_ex­pr>, <pe­rio­d>, <st­ep>, [<t­ime­zon­e>])
TIME_E­XTR­ACT­(<t­ime­sta­mp_­exp­r>, [<u­nit­>, [<t­ime­zon­e>]])
TIME_P­ARS­E(<­str­ing­_ex­pr>, [<p­att­ern­>, [<t­ime­zon­e>]])
TIME_F­ORM­AT(­<ti­mes­tam­p_e­xpr­>, [<p­att­ern­>, [<t­ime­zon­e>]])
MILLIS­_TO­_TI­MES­TAM­P(m­ill­is_­expr)
TIMEST­AMP­_TO­_MI­LLI­S(t­ime­sta­mp_­expr)
EXTRAC­T(<­uni­t> FROM timest­amp­_expr)
FLOOR(­tim­est­amp­_expr TO <un­it>)
CEIL(t­ime­sta­mp_expr TO <un­it>)
TIMEST­AMP­ADD­(<u­nit­>, <co­unt­>, <ti­mes­tam­p>)
timest­amp­_expr { + &#124; - } <in­ter­val­_ex­pr>
       
 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          SQL Server Cheat Sheet
          SQL Cheat Sheet

          More Cheat Sheets by kinger1999