TL;DR InfluxDB Tech Tips - The Most Interesting InfluxDB & TICK STACK Related Issues, Workarounds & How-tos
By Regan Kuchan / Mar 09, 2017 / InfluxDB, Tech Tips
In this weekly post we recap the most interesting InfluxDB and TICK-stack related issues, workarounds, how-tos and Q&A from GitHub, IRC and the InfluxData Community that you might have missed in the last week or so.
The nuances of microsecond syntax
Q: When I use
u to specify microseconds in a query, I get the expected results. When I use that same
u to specify microseconds in the configuration file, I get a
parse config error. Is this what I should expect? Is there a way to specify microseconds in the configuration file?
u in a query:
> SELECT * FROM "u_and_me" WHERE time = 200u name: u_and_me time value ---- ----- 1970-01-01T00:00:00.0002Z 1
u in a configuration file:
run: parse config: time: unknown unit u in duration 200u.
200u and I saw that error when I restarted InfluxDB.
A: This is a known issue in InfluxDB. The proper microsecond syntax differs for writes, queries, the CLI’s
precision command, and the configuration file. The table below outlines the acceptable microsecond syntax for each of those categories:
|Microsecond syntax||API write||API query/CLI query||CLI precision||Configuration file|
In your case, set
200us in your configuration file and you’ll be good to go!
The syntax for sub-sub-queries
Q: I’m trying to include several subqueries within one main query but I keep getting a parsing error. Is this a supported behavior? The bolded lines are the subqueries:
> SELECT MEAN("max"),MEAN("min") FROM ( SELECT MAX("subways") FROM "public_transit" GROUP BY "id_number"; SELECT MIN("subways") FROM "public_transit" GROUP BY "id_number","crowd_index") ERR: error parsing query: found ;, expected ) at line 1, char 102
A: Queries in InfluxQL support several nested subqueries but not several
SELECT statements per subquery. Here’s an example of several nested subqueries:
> SELECT SUM("max") FROM ( SELECT MAX("min") FROM ( SELECT MIN("subways") FROM "public_transit" GROUP BY "id_number","crowd_index") GROUP BY "id_number") name: public_transit time sum ---- --- 1970-01-01T00:00:00Z 29
In your case, you’ll need to place your subqueries into two separate queries:
SELECT MEAN("max") FROM ( SELECT MAX("subways") FROM "public_transit" GROUP BY "id_number"); SELECT MEAN("min") FROM ( SELECT MIN("subways") FROM "public_transit" GROUP BY "id_number","crowd_index") name: public_transit time mean ---- ---- 1970-01-01T00:00:00Z 14.5 name: public_transit time mean ---- ---- 1970-01-01T00:00:00Z 12.75
The sneaky characters in identifier names
Q: I’ve successfully written a data point to InfluxDB but I get an odd error when I try to query it (see below). Am I doing something wrong?
> INSERT dot.com value=12 > SHOW MEASUREMENTS name: measurements name ---- dot.com <--- yay! > SELECT * FROM dot.com ERR: retention policy not found: dot <--- ??
A: In queries, double-quote identifiers if they start with a digit, contain characters other than
[A-z,0-9,_], or if they are an InfluxQL keyword. In your case, your measurement name includes a period so you need to double quote that name in queries:
> SELECT * FROM "dot.com" name: dot.com time value ---- ----- 2017-03-08T01:24:36.117449459Z 12
If you’re interested, you’re seeing that error because InfluxDB thinks that you’re trying to fully qualify the measurement name. You fully qualify a measurement by specifying its database and retention policy, separated by periods:
> SELECT * FROM "tldr"."auto"."dot.com" |_____________________| "database"."retention-policy"."measurement" name: dot.com time value ---- ----- 2017-03-08T01:24:36.117449459Z 12
- Downloads for the TICK-stack are live on our "downloads" page.
- Deploy on the Cloud: Get started with a FREE trial of InfluxDB Cloud featuring fully-managed clusters, Kapacitor and Grafana.
- Deploy on Your Servers: Want to run InfluxDB clusters on your servers? Try a FREE 14-day trial of InfluxDB Enterprise featuring an intuitive UI for deploying, monitoring and rebalancing clusters, plus managing backups and restores.
- Tell Your Story: Over 300 companies have shared their story on how InfluxDB is helping them succeed. Submit your testimonial and get a limited edition hoodie as a thank you.