Permalink
Find file Copy path
984124b Oct 16, 2018
1 contributor

Users who have contributed to this file

600 lines (439 sloc) 16.3 KB

QueryBuilder:

Supposing that you have a measurement h2o_feet:

> SELECT * FROM "h2o_feet"

name: h2o_feet
--------------
time                   level description      location       water_level
2015-08-18T00:00:00Z   below 3 feet           santa_monica   2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica   4.938
The basic SELECT statement

Issue simple select statements

Query query = select().from(DATABASE,"h2o_feet");
SELECT * FROM "h2o_feet"

Select specific tags and fields from a single measurement

Query query = select("level description","location","water_level").from(DATABASE,"h2o_feet");
SELECT "level description",location,water_level FROM h2o_feet;

Select specific tags and fields from a single measurement, and provide their identifier type

Query query = select().column("\"level description\"::field").column("\"location\"::tag").column("\"water_level\"::field").from(DATABASE,"h2o_feet");
SELECT "level description"::field,"location"::tag,"water_level"::field FROM h2o_feet;

Select all fields from a single measurement

Query query = select().raw("*::field").from(DATABASE,"h2o_feet");
SELECT *::field FROM h2o_feet;

Select a specific field from a measurement and perform basic arithmetic

Query query = select().op(op(cop("water_level",MUL,2),"+",4)).from(DATABASE,"h2o_feet");
SELECT (water_level * 2) + 4 FROM h2o_feet;

Select all data from more than one measurement

Query query = select().from(DATABASE,"\"h2o_feet\",\"h2o_pH\"");
SELECT * FROM "h2o_feet","h2o_pH";

Select all data from a fully qualified measurement

Query query = select().from(DATABASE,"\"NOAA_water_database\".\"autogen\".\"h2o_feet\"");
SELECT * FROM "NOAA_water_database"."autogen"."h2o_feet";

Select data that have specific field key-values

Query query = select().from(DATABASE,"h2o_feet").where(gt("water_level",8));
SELECT * FROM h2o_feet WHERE water_level > 8;

Select data that have a specific string field key-value

Query query = select().from(DATABASE,"h2o_feet").where(eq("level description","below 3 feet"));
SELECT * FROM h2o_feet WHERE "level description" = 'below 3 feet';

Select data that have a specific field key-value and perform basic arithmetic

Query query = select().from(DATABASE,"h2o_feet").where(gt(cop("water_level",ADD,2),11.9));
SELECT * FROM h2o_feet WHERE (water_level + 2) > 11.9;

Select data that have a specific tag key-value

Query query = select().column("water_level").from(DATABASE,"h2o_feet").where(eq("location","santa_monica"));
SELECT water_level FROM h2o_feet WHERE location = 'santa_monica';

Select data that have specific field key-values and tag key-values

Query query = select().column("water_level").from(DATABASE,"h2o_feet")
                .where(neq("location","santa_monica"))
                .andNested()
                .and(lt("water_level",-0.59))
                .or(gt("water_level",9.95))
                .close();
SELECT water_level FROM h2o_feet WHERE location <> 'santa_monica' AND (water_level < -0.59 OR water_level > 9.95);

Select data that have specific timestamps

Query query = select().from(DATABASE,"h2o_feet")
                .where(gt("time",subTime(7,DAY)));
SELECT * FROM h2o_feet WHERE time > now() - 7d;
The GROUP BY clause

Group query results by a single tag

Query query = select().mean("water_level").from(DATABASE,"h2o_feet") .groupBy("location");
SELECT MEAN(water_level) FROM h2o_feet GROUP BY location;

Group query results by more than one tag

Query query = select().mean("index").from(DATABASE,"h2o_feet")
                .groupBy("location","randtag");
SELECT MEAN(index) FROM h2o_feet GROUP BY location,randtag;

Group query results by all tags

Query query = select().mean("index").from(DATABASE,"h2o_feet")
                .groupBy(raw("*"));
SELECT MEAN(index) FROM h2o_feet GROUP BY *;

GROUP BY time intervals

Group query results into 12 minute intervals

Query query = select().count("water_level").from(DATABASE,"h2o_feet")
                .where(eq("location","coyote_creek"))
                .and(gte("time","2015-08-18T00:00:00Z"))
                .and(lte("time","2015-08-18T00:30:00Z'"))
                .groupBy(time(12l,MINUTE));
SELECT COUNT(water_level) FROM h2o_feet WHERE location = 'coyote_creek' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z'' GROUP BY time(12m);

Group query results into 12 minutes intervals and by a tag key

        Query query = select().count("water_level").from(DATABASE,"h2o_feet")
                .where()
                .and(gte("time","2015-08-18T00:00:00Z"))
                .and(lte("time","2015-08-18T00:30:00Z'"))
                .groupBy(time(12l,MINUTE),"location");
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;
Advanced GROUP BY time() syntax

Group query results into 18 minute intervals and shift the preset time boundaries forward

Query query = select().mean("water_level").from(DATABASE,"h2o_feet")
                .where(eq("location","coyote_creek"))
                .and(gte("time","2015-08-18T00:06:00Z"))
                .and(lte("time","2015-08-18T00:54:00Z"))
                .groupBy(time(18l,MINUTE,6l,MINUTE));
SELECT MEAN(water_level) FROM h2o_feet WHERE location = 'coyote_creek' AND time >= '2015-08-18T00:06:00Z' AND time <= '2015-08-18T00:54:00Z' GROUP BY time(18m,6m);

Group query results into 12 minute intervals and shift the preset time boundaries back

Query query = select().mean("water_level").from(DATABASE,"h2o_feet")
                .where(eq("location","coyote_creek"))
                .and(gte("time","2015-08-18T00:06:00Z"))
                .and(lte("time","2015-08-18T00:54:00Z"))
                .groupBy(time(18l,MINUTE,-12l,MINUTE));
SELECT MEAN(water_level) FROM h2o_feet WHERE location = 'coyote_creek' AND time >= '2015-08-18T00:06:00Z' AND time <= '2015-08-18T00:54:00Z' GROUP BY time(18m,-12m);
GROUP BY time intervals and fill()
Query select = select()
                .column("water_level")
                .from(DATABASE, "h2o_feet")
                .where(gt("time", op(ti(24043524l, MINUTE), SUB, ti(6l, MINUTE))))
                .groupBy("water_level")
                .fill(100);
SELECT water_level FROM h2o_feet WHERE time > 24043524m - 6m GROUP BY water_level fill(100);"
The INTO clause

Rename a database

Query select = select()
                .into("\"copy_NOAA_water_database\".\"autogen\".:MEASUREMENT")
                .from(DATABASE, "\"NOAA_water_database\".\"autogen\"./.*/")
                .groupBy(new RawText("*"));
SELECT * INTO "copy_NOAA_water_database"."autogen".:MEASUREMENT FROM "NOAA_water_database"."autogen"./.*/ GROUP BY *;

Write the results of a query to a measurement

Query select = select().column("water_level").into("h2o_feet_copy_1").from(DATABASE,"h2o_feet").where(eq("location","coyote_creek"));
SELECT water_level INTO h2o_feet_copy_1 FROM h2o_feet WHERE location = 'coyote_creek';

Write aggregated results to a measurement

Query select = select()
                .mean("water_level")
                .into("all_my_averages")
                .from(DATABASE,"h2o_feet")
                .where(eq("location","coyote_creek"))
                .and(gte("time","2015-08-18T00:00:00Z"))
                .and(lte("time","2015-08-18T00:30:00Z"))
                .groupBy(time(12l,MINUTE));
SELECT MEAN(water_level) INTO all_my_averages FROM h2o_feet WHERE location = 'coyote_creek' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m);

Write aggregated results for more than one measurement to a different database (downsampling with backreferencing)

Query select = select()
                .mean(raw("*"))
                .into("\"where_else\".\"autogen\".:MEASUREMENT")
                .fromRaw(DATABASE, "/.*/")
                .where(gte("time","2015-08-18T00:00:00Z"))
                .and(lte("time","2015-08-18T00:06:00Z"))
                    .groupBy(time(12l,MINUTE));
SELECT MEAN(*) INTO "where_else"."autogen".:MEASUREMENT FROM /.*/ WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:06:00Z' GROUP BY time(12m);
ORDER BY time DESC

Return the newest points first

Query select = select().from(DATABASE,"h2o_feet")
                .where(eq("location","santa_monica"))
                .orderBy(desc());
SELECT * FROM h2o_feet WHERE location = 'santa_monica' ORDER BY time DESC;

Return the newest points first and include a GROUP BY time() clause

Query select = select().mean("water_level")
                .from(DATABASE,"h2o_feet")
                .where(gte("time","2015-08-18T00:00:00Z"))
                .and(lte("time","2015-08-18T00:42:00Z"))
                .groupBy(time(12l,MINUTE))
                .orderBy(desc());
SELECT MEAN(water_level) FROM h2o_feet WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:42:00Z' GROUP BY time(12m) ORDER BY time DESC;
The LIMIT clause

Limit the number of points returned

Query select = select("water_level","location")
                .from(DATABASE,"h2o_feet").limit(3);
SELECT water_level,location FROM h2o_feet LIMIT 3;

Limit the number points returned and include a GROUP BY clause

Query select = select().mean("water_level")
                .from(DATABASE,"h2o_feet")
                .where()
                .and(gte("time","2015-08-18T00:00:00Z"))
                .and(lte("time","2015-08-18T00:42:00Z"))
                .groupBy(raw("*"),time(12l,MINUTE))
                .limit(2);
SELECT MEAN(water_level) FROM h2o_feet WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:42:00Z' GROUP BY *,time(12m) LIMIT 2;
The SLIMIT clause

Limit the number of series returned

Query select = select().column("water_level")
                .from(DATABASE,"h2o_fleet")
                .groupBy(raw("*"))
                .sLimit(1);
SELECT water_level FROM "h2o_feet" GROUP BY * SLIMIT 1

Limit the number of series returned and include a GROUP BY time() clause

Query select = select().column("water_level")
                .from(DATABASE,"h2o_feet")
                .where()
                .and(gte("time","2015-08-18T00:00:00Z"))
                .and(lte("time","2015-08-18T00:42:00Z"))
                .groupBy(raw("*"),time(12l,MINUTE))
                .sLimit(1);
SELECT water_level FROM h2o_feet WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:42:00Z' GROUP BY *,time(12m) SLIMIT 1;
The OFFSET clause

Paginate points

Query select = select("water_level","location").from(DATABASE,"h2o_feet").limit(3,3);
SELECT water_level,location FROM h2o_feet LIMIT 3 OFFSET 3;
The SOFFSET clause

Paginate series and include all clauses

Query select = select().mean("water_level")
                .from(DATABASE,"h2o_feet")
                .where()
                .and(gte("time","2015-08-18T00:00:00Z"))
                .and(lte("time","2015-08-18T00:42:00Z"))
                .groupBy(raw("*"),time(12l,MINUTE))
                .orderBy(desc())
                .limit(2,2)
                .sLimit(1,1);
SELECT MEAN(water_level) FROM h2o_feet WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:42:00Z' GROUP BY *,time(12m) ORDER BY time DESC LIMIT 2 OFFSET 2 SLIMIT 1 SOFFSET 1;
The Time Zone clause

Return the UTC offset for Chicago’s time zone

Query select = select()
                .column("test1")
                .from(DATABASE, "h2o_feet")
                .groupBy("test2", "test3")
                .sLimit(1)
                .tz("America/Chicago");
SELECT test1 FROM foobar GROUP BY test2,test3 SLIMIT 1 tz('America/Chicago');
Time Syntax

Specify a time range with RFC3339 date-time strings

Query select = select().column("water_level")
                .from(DATABASE,"h2o_feet")
                .where(eq("location","santa_monica"))
                .and(gte("time","2015-08-18T00:00:00.000000000Z"))
                .and(lte("time","2015-08-18T00:12:00Z"));
SELECT water_level FROM h2o_feet WHERE location = 'santa_monica' AND time >= '2015-08-18T00:00:00.000000000Z' AND time <= '2015-08-18T00:12:00Z';

Specify a time range with second-precision epoch timestamps

Query select = select().column("water_level")
                .from(DATABASE,"h2o_feet")
                .where(eq("location","santa_monica"))
                .and(gte("time",ti(1439856000l,SECOND)))
                .and(lte("time",ti(1439856720l,SECOND)));
SELECT water_level FROM h2o_feet WHERE location = 'santa_monica' AND time >= 1439856000s AND time <= 1439856720s;

Perform basic arithmetic on an RFC3339-like date-time string

Query select = select().column("water_level")
                .from(DATABASE,"h2o_feet")
                .where(eq("location","santa_monica"))
                .and(gte("time",op("2015-09-18T21:24:00Z",SUB,ti(6l,MINUTE))));
SELECT water_level FROM h2o_feet WHERE location = 'santa_monica' AND time >= '2015-09-18T21:24:00Z' - 6m;

Perform basic arithmetic on an epoch timestamp

Query select = select().column("water_level")
                .from(DATABASE,"h2o_feet")
                .where(eq("location","santa_monica"))
                .and(gte("time",op(ti(24043524l,MINUTE),SUB,ti(6l,MINUTE))));
SELECT water_level FROM h2o_feet WHERE location = 'santa_monica' AND time >= 24043524m - 6m;

Specify a time range with relative time

Query select = select().column("water_level")
                .from(DATABASE,"h2o_feet")
                .where(eq("location","santa_monica"))
                .and(gte("time",subTime(1l,HOUR)));
SELECT water_level FROM h2o_feet WHERE location = 'santa_monica' AND time >= now() - 1h;
Regular expressions

Use a regular expression to specify field keys and tag keys in the SELECT clause

Query select = select().regex("l").from(DATABASE,"h2o_feet").limit(1);
SELECT /l/ FROM h2o_feet LIMIT 1;

Use a regular expression to specify field keys with a function in the SELECT clause

Query select = select().regex("l").distinct().from(DATABASE,"h2o_feet").limit(1);
SELECT DISTINCT /l/ FROM h2o_feet LIMIT 1;

Use a regular expression to specify measurements in the FROM clause

Query select = select().mean("degrees").fromRaw(DATABASE,"/temperature/");
SELECT MEAN(degrees) FROM /temperature/;

Use a regular expression to specify a field value in the WHERE clause

Query select = select().regex("/l/").from(DATABASE,"h2o_feet").where(regex("level description","/between/")).limit(1);
SELECT /l/ FROM h2o_feet WHERE "level description" =~ /between/ LIMIT 1;

Use a regular expression to specify tag keys in the GROUP BY clause

Query select = select().regex("/l/").from(DATABASE,"h2o_feet").where(regex("level description","/between/")).groupBy(raw("/l/")).limit(1);
SELECT /l/ FROM h2o_feet WHERE "level description" =~ /between/ GROUP BY /l/ LIMIT 1;

Function with no direct implementation can be supported by raw expressions

Query select = select().raw("an expression on select").from(dbName, "cpu").where("an expression as condition");
SELECT an expression on select FROM h2o_feet WHERE an expression as condition;