TL;DR InfluxDB Tech Tips - Syntax Consolidation For SHOW Queries

By Regan Kuchan / Developer
Nov 24, 2016

 In this post we recap some of the new features that you might have missed in the most recent InfluxDB release, including syntax consolidation for SHOW queries. Check in next week for more on the most interesting InfluxDB and TICK-stack related issues, workarounds, how-tos and Q&A from GitHub, IRC and the InfluxDB Google Group.

You may have heard that we recently released InfluxDB v1.1. In addition to various performance and stability improvements, version 1.1 offers several new features and syntax developments. Here are a couple updates that you might have missed:

Syntax consolidation for SHOW queries

In versions prior to 1.1, the syntax for SHOW queries was inconsistent. Some queries, like SHOW RETENTION POLICIES, required an ON <database_name> clause, but other queries, like SHOW MEASUREMENTS, automatically queried the database specified by the HTTP API’s db query string parameter or the CLI’s USEd database.

In versions 1.1+, all SHOW queries support the ON <database_name> clause and that clause is optional. If the SHOW query does not specify the ON clause, you must specify the database with USE <database_name> in the CLI or with the db query string parameter in the HTTP API request. See the Schema Exploration page for the relevant SHOW queries and for more information about the updated syntax.

Some examples:

# HTTP API request with the ON clause

~# curl -G "http://localhost:8086/query?pretty=true" --data-urlencode 'q=SHOW MEASUREMENTS ON "thanksgiving"'
{
    "results": [
        {
            "series": [
                {
                    "name": "measurements",
                    "columns": [
                        "name"
                    ],
                    "values": [
                        [
                            "cornucopia"
                        ],
                        [
                            "cornbread"
                        ],
                        [
                            "pie"
                        ],
                        [
                            "sweet_potatoes"
                        ],
                        [
                            "turkey"
                        ]
                    ]
                }
            ]
        }
    ]
}

# HTTP API request without the ON clause

~# curl -G "http://localhost:8086/query?db=thanksgiving&pretty=true" --data-urlencode 'q=SHOW MEASUREMENTS'
{
    "results": [
        {
            "series": [
                {
                    "name": "measurements",
                    "columns": [
                        "name"
                    ],
                    "values": [
                        [
                            "cornucopia"
                        ],
                        [
                            "cornbread"
                        ],
                        [
                            "pie"
                        ],
                        [
                            "sweet_potatoes"
                        ],
                        [
                            "turkey"
                        ]
                    ]
                }
            ]
        }
    ]
}

New function: SAMPLE()

The new SAMPLE() function returns a random sample of N points from the specified data. It uses reservoir sampling to generate the random sample and it supports all field types.

Example:

> SELECT * FROM "pies"
name: pies
time                  pumpkin
----                  -------
2016-11-21T17:00:00Z  34
2016-11-21T18:00:00Z  33
2016-11-21T19:00:00Z  2
2016-11-21T20:00:00Z  0
2016-11-21T21:00:00Z  1

> SELECT SAMPLE("pumpkin",2) FROM "pies"
name: pies
time                  sample
----                  ------
2016-11-21T18:00:00Z  33
2016-11-21T21:00:00Z  1

New function: CUMULATIVE_SUM()

The new CUMULATIVE_SUM() function returns the cumulative sum of consecutive field values. The function supports int64 and float64 field types.

Example:

> SELECT * FROM "couch_naps"
name: couch_naps
time                  value
----                  -----
2016-11-21T17:00:00Z  2
2016-11-21T17:10:00Z  3
2016-11-21T17:20:00Z  16

> SELECT CUMULATIVE_SUM("value") FROM "couch_naps"
name: couch_naps
time                  cumulative_sum
----                  --------------
2016-11-21T17:00:00Z  2
2016-11-21T17:10:00Z  5
2016-11-21T17:20:00Z  21

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