TL;DR InfluxDB Tech Tips – The Most Interesting InfluxDB & TICK STACK Related Issues, Workarounds & How-tos

4 minutes

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.

I set log-queries-after to 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 syntaxAPI writeAPI query/CLI queryCLI precisionConfiguration file
uYesYesYesNo
usNoNoNoYes
µNoYesNoNo
µsNoNoNoYes

In your case, set log-queries-after to 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

Check out the FAQ page for more on single- and double-quoting in queries, and see the Data Exploration page for more about specifying measurements in the FROM clause.

What’s next:

  • 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.

Related Blog Posts

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top