Previously we used the group by statement extensively in order to execute complex aggregation queries
On this tutorial we are going to have a look at ‘into’ statements and the ‘order by’ close.
Apart from inserting or selecting data we might as well want to persist the results from one query into another table. The usages on something like this can vary. For example you might have a complex operation that cannot be executed in one single query.
Before we continue, make sure you have an influxdb instance up and running.
The most common action with an into query would be to populate a measurement with the results of a previous query.
Let’s copy a database.
1 2 3 4 | Query query = select() .into("\"copy_NOAA_water_database\".\"autogen\".:MEASUREMENT") .from(DATABASE, "\"NOAA_water_database\".\"autogen\"./.*/") .groupBy(new RawText("*")); |
The result of this query will be to copy the results into the h2o_feet_copy_1 measurement.
1 | SELECT * INTO "copy_NOAA_water_database"."autogen".:MEASUREMENT FROM "NOAA_water_database"."autogen"./.*/ GROUP BY *; |
Now let’s just copy a column into another table.
1 2 3 4 | Query query = select().column("water_level") .into("h2o_feet_copy_1") .from(DATABASE,"h2o_feet") .where(eq("location","coyote_creek")); |
Bellow is the query which is going to be execcuted.
1 | SELECT water_level INTO h2o_feet_copy_1 FROM h2o_feet WHERE location = 'coyote_creek'; |
Also we can do exactly the same thing with aggregations.
01 02 03 04 05 06 07 08 09 10 | Query query = 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)); LOGGER.info("Executing query "+query.getCommand()); QueryResult queryResult = influxDB.query(query); |
And generate a query which persists the aggregation result into a table.
1 | 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); |
Order clauses
Influxdb does provide ordering however it is limited only to dates.
So we will execute a query with ascending order.
1 2 3 | Query query = select().from(DATABASE, "h2o_feet") .where(eq("location","santa_monica")) .orderBy(asc()); |
And we get the ascending ordering as expected.
1 | SELECT * FROM h2o_feet WHERE location = 'santa_monica' ORDER BY time ASC; |
And the same query we shall executed with descending order.
1 2 3 | Query query = select().from(DATABASE, "h2o_feet") .where(eq("location","santa_monica")) .orderBy(desc()); |
1 | SELECT * FROM h2o_feet WHERE location = 'santa_monica' ORDER BY time DESC; |
That’s it! We just created some new databases and measurements by just using existing data in our database. We also executed some statements where we specified the time ordering.
You can find the sourcecode in github.
Published on Java Code Geeks with permission by Emmanouil Gkatziouras, partner at our JCG program. See the original article here: A guide to the InfluxDBMapper and QueryBuilder for Java Part: Into and Order Opinions expressed by Java Code Geeks contributors are their own. |
