Schema Exploration

InfluxQL is an SQL-like query language for interacting with data in InfluxDB. The following sections cover useful query syntax for exploring your schema (that is, how you set up your time series data):

• See all databases with SHOW DATABASES
• Explore retention policies with SHOW RETENTION POLICIES
• Explore series with SHOW SERIES
• Explore measurements with SHOW MEASUREMENTS
• Explore tag keys with SHOW TAG KEYS
• Explore tag values with SHOW TAG VALUES
• Explore field keys with SHOW FIELD KEYS

The examples below query data using InfluxDB’s Command Line Interface (CLI). See the Querying Data guide for how to directly query data with the HTTP API.

Sample data

This document uses the same sample data as the Data Exploration page. The data is described and available for download on the Sample Data page.

See all databases with SHOW DATABASES

Get a list of all the databases in your system by entering:

SHOW DATABASES

CLI example:

> SHOW DATABASES
name: databases
---------------
name
NOAA_water_database

Explore retention policies with SHOW RETENTION POLICIES

The SHOW RETENTION POLICIES query lists the existing retention policies on a given database, and it takes the following form:

SHOW RETENTION POLICIES ON <database_name>

CLI example:

> SHOW RETENTION POLICIES ON NOAA_water_database

CLI response:

name	    duration	 replicaN	 default
default	 0		       1		       true

The first column of the output contains the names of the different retention policies in the specified database. The second column shows the duration and the third column shows the replication factor of the retention policy. The fourth column specifies if the retention policy is the DEFAULT retention policy for the database.

The following example shows a hypothetical CLI response where there are four different retention policies in the database, and where the DEFAULT retention policy is three_days_only:

name		           duration	 replicaN	 default
default		        0		       1		       false
two_days_only	   48h0m0s		 1		       false
one_day_only	    24h0m0s		 1		       false
three_days_only	 72h0m0s		 1		       true

Explore series with SHOW SERIES

The SHOW SERIES query returns the distinct series in your database and takes the following form, where the FROM and WHERE clauses are optional:

SHOW SERIES [FROM <measurement_name> [WHERE <tag_key>='<tag_value>']]

Return all series in the database NOAA_water_database:

> SHOW SERIES

CLI response:

key
average_temperature,location=coyote_creek
average_temperature,location=santa_monica
h2o_feet,location=coyote_creek
h2o_feet,location=santa_monica
h2o_pH,location=coyote_creek
h2o_pH,location=santa_monica
h2o_quality,location=coyote_creek,randtag=1
h2o_quality,location=coyote_creek,randtag=2
h2o_quality,location=coyote_creek,randtag=3
h2o_quality,location=santa_monica,randtag=1
h2o_quality,location=santa_monica,randtag=2
h2o_quality,location=santa_monica,randtag=3
h2o_temperature,location=coyote_creek
h2o_temperature,location=santa_monica

SHOW SERIES organizes its output similar to the line protocol format. Everything before the first comma is the measurement name. Everything after the first comma is either a tag key or a tag value.

From the output above you can see that the data in the database NOAA_water_database have five different measurements and 14 different series. The measurements are average_temperature, h2o_feet, h2o_pH, h2o_quality, and h2o_temperature. Every measurement has the tag key location with the tag values coyote_creek and santa_monica - that makes 10 series. The measurement h2o_quality has the additional tag key randtag with the tag values 1,2, and 3 - that makes 14 series.

Return series for a specific measurement:

> SHOW SERIES FROM h2o_quality

CLI response:

key
h2o_quality,location=coyote_creek,randtag=1
h2o_quality,location=coyote_creek,randtag=2
h2o_quality,location=coyote_creek,randtag=3
h2o_quality,location=santa_monica,randtag=1
h2o_quality,location=santa_monica,randtag=2
h2o_quality,location=santa_monica,randtag=3

Return series for a specific measurement and tag set:

> SHOW SERIES FROM h2o_quality WHERE location = 'coyote_creek'

CLI response:

key
h2o_quality,location=coyote_creek,randtag=1
h2o_quality,location=coyote_creek,randtag=2
h2o_quality,location=coyote_creek,randtag=3

Explore measurements with SHOW MEASUREMENTS

The SHOW MEASUREMENTS query returns the measurements in your database and it takes the following form:

SHOW MEASUREMENTS [WITH MEASUREMENT =~ <regular_expression>] [WHERE <tag_key>=<'tag_value'>]

Return all measurements in the NOAA_water_database database:

> SHOW MEASUREMENTS

CLI response:

name: measurements
------------------
name
average_temperature
h2o_feet
h2o_pH
h2o_quality
h2o_temperature

From the output you can see that the database NOAA_water_database has five measurements: average_temperature, h2o_feet, h2o_pH, h2o_quality, and h2o_temperature.

Return measurements where the tag key randtag equals 1:

> SHOW MEASUREMENTS WHERE randtag = '1'

CLI response:

name: measurements
------------------
name
h2o_quality

Only the measurement h2o_quality contains the tag set randtag = 1.

Use a regular expression to return measurements where the tag key randtag is a digit:

SHOW MEASUREMENTS WHERE randtag =~ /\d/

CLI response:

name: measurements
------------------
name
h2o_quality

Use a regular expression with WITH MEASUREMENT to return all measurements that start with h2o:

> SHOW MEASUREMENTS WITH MEASUREMENT =~ /h2o.*/

CLI response:

name: measurements
------------------
name
h2o_feet
h2o_pH
h2o_quality
h2o_temperature

Explore tag keys with SHOW TAG KEYS

SHOW TAG KEYS returns the tag keys associated with each measurement and takes the following form, where the FROM clause is optional:

SHOW TAG KEYS [FROM <measurement_name>]

Return all tag keys that are in the database NOAA_water_database:

> SHOW TAG KEYS

CLI response:

name: average_temperature
-------------------------
tagKey
location

name: h2o_feet
--------------
tagKey
location

name: h2o_pH
------------
tagKey
location

name: h2o_quality
-----------------
tagKey
location
randtag

name: h2o_temperature
---------------------
tagKey
location

InfluxDB organizes the output by measurement. Notice that each of the five measurements has the tag key location and that the measurement h2o_quality also has the tag key randtag.

Return the tag keys for a specific measurement:

> SHOW TAG KEYS FROM h2o_temperature

CLI response:

name: h2o_temperature
---------------------
tagKey
location

Explore tag values with SHOW TAG VALUES

The SHOW TAG VALUES query returns the set of tag values for a specific tag key across all measurements in the database. Syntax for specifying a single tag key:

SHOW TAG VALUES [FROM <measurement_name>] WITH KEY = "<tag_key>"

Syntax for specifying more than one tag key:

SHOW TAG VALUES [FROM <measurement_name>] WITH KEY IN ("<tag_key1>","<tag_key2")

Return the tag values for a single tag key (randtag) across all measurements in the database NOAA_water_database:

> SHOW TAG VALUES WITH KEY = "randtag"

CLI response:

name: h2o_quality
-----------------
key	     value
randtag	 1
randtag	 3
randtag	 2

Return the tag values for two tag keys (location and randtag) across all measurements in the database NOAA_water_database:

> SHOW TAG VALUES WITH KEY IN ("location","randtag")

CLI response:

name: average_temperature
-------------------------
key		     value
location	 coyote_creek
location	 santa_monica


name: h2o_feet
--------------
key		     value
location	 coyote_creek
location	 santa_monica


name: h2o_pH
------------
key		     value
location	 coyote_creek
location	 santa_monica


name: h2o_quality
-----------------
key		     value
location	 coyote_creek
randtag		 1
randtag		 3
randtag		 2
location	 santa_monica


name: h2o_temperature
---------------------
key		     value
location	 coyote_creek
location	 santa_monica

Return the tag values for the tag key randtag for a specific measurement in the NOAA_water_database database:

> SHOW TAG VALUES FROM average_temperature WITH KEY = "randtag"

CLI response:

The measurement average_temperature doesn’t have the tag key randtag so InfluxDB returns nothing.

Explore field keys with SHOW FIELD KEYS

The SHOW FIELD KEYS query returns the field keys across each measurement in the database. It takes the following form, where the FROM clause is optional:

SHOW FIELD KEYS [FROM <measurement_name>]

Return the field keys across all measurements in the database NOAA_water_database:

> SHOW FIELD KEYS

CLI response:

name: average_temperature
-------------------------
fieldKey
degrees

name: h2o_feet
--------------
fieldKey
level description
water_level

name: h2o_pH
------------
fieldKey
pH

name: h2o_quality
-----------------
fieldKey
index

name: h2o_temperature
---------------------
fieldKey
degrees

Return the field keys in the measurement h2o_feet in the database NOAA_water_database:

> SHOW FIELD KEYS FROM h2o_feet

CLI response:

name: h2o_feet
--------------
fieldKey
level description
water_level