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	 shardGroupDuration	 replicaN	 default
autogen	 0		       168h0m0s		          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 retention policy’s duration and the third column shows the shard group duration. The fourth column shows the replication factor of the retention policy and the fifth 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	 shardGroupDuration	 replicaN	 default
autogen		        0		       168h0m0s		          1		       false
two_days_only	   48h0m0s		 24h0m0s			          1		       false
one_day_only	    24h0m0s		 1h0m0s			           1		       false
three_days_only	 72h0m0s		 24h0m0s			          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>' | =~ <regular_expression>]]]

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>' | =~ <regular_expression>]]

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.

SHOW TAG VALUES [FROM <measurement_name>] [WITH KEY [ = "<tag_key>" | IN ("<tag_key1>","<tag_key2")]] [WHERE <tag_key> [= '<tag_value>' | =~ <regular_expression>]]

Syntax notes:

• SHOW TAG VALUES supports regular expressions in the WITH KEY clause
• SHOW TAG VALUES requires a WITH clause if the query includes a WHERE clause

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 the tag keys location or randtag for all measurements where the tag key randtag has tag values:

> SHOW TAG VALUES WITH KEY IN ("location","randtag") WHERE "randtag" =~ /./

CLI response:

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

Return the tag values for all tag keys that do not include the letter c:

> SHOW TAG VALUES WITH KEY !~ /.*c.*/
name: h2o_quality
-----------------
key       value
randtag	  1
randtag	  2
randtag	  3

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 and field value data types across each measurement in the database. It takes the following form, where the FROM clause is optional:

SHOW FIELD KEYS [FROM <measurement_name>]

Note: A field’s type can differ across shards. If your field has more than one type, SHOW FIELD KEYS returns the type that occurs first in the following list: float, integer, string, boolean.

Return the field keys and field value data types across all measurements in the database NOAA_water_database:

> SHOW FIELD KEYS

CLI response:

name: average_temperature
-------------------------
fieldKey	  fieldType
degrees		  float

name: h2o_feet
--------------
fieldKey		          fieldType
level description	  string
water_level		       float

name: h2o_pH
------------
fieldKey	 fieldType
pH		      float

name: h2o_quality
-----------------
fieldKey	 fieldType
index		   float

name: h2o_temperature
---------------------
fieldKey	 fieldType
degrees		 float

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		          fieldType
level description	  string
water_level		       float