Explore your schema using InfluxQL

Use InfluxQL to explore the schema of your time series data. Use the following InfluxQL commands to explore your schema:

• SHOW SERIES
• SHOW MEASUREMENTS
• SHOW TAG KEYS
• SHOW TAG VALUES
• SHOW FIELD KEYS
• SHOW FIELD KEY CARDINALITY
• SHOW TAG KEY CARDINALITY

Command examples use the NOAA water sample data.

SHOW SERIES

Return a list of series for the specified database.

Syntax

SHOW SERIES [ON <database_name>] [FROM_clause] [WHERE <tag_key> <operator> [ '<tag_value>' | <regular_expression>]] [LIMIT_clause] [OFFSET_clause]

• ON <database_name> is optional. If the query does not include ON <database_name>, you must specify the database with the db query string parameter in the InfluxDB API request.

• FROM, WHERE, LIMIT, and OFFSET clauses are optional.

• The WHERE clause in SHOW SERIES supports tag comparisons but not field comparisons.

  Supported operators in the WHERE clause:

  • =: equal to
  • <>: not equal to
  • !=: not equal to
  • =~: matches against
  • !~: doesn’t match against

See Explore data using InfluxQL for documentation on the FROM clause, LIMIT clause, OFFSET clause, and Regular Expressions.

Examples

Run SHOW SERIES with the ON clause

SHOW SERIES ON noaa

Output:

The query returns all series in the noaa database. The query’s output is 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. The noaa database has 5 different measurements and 13 different series.

Run SHOW SERIES with several clauses

SHOW SERIES ON noaa FROM "h2o_quality" WHERE "location" = 'coyote_creek' LIMIT 2

Output:

The query returns all series in the noaa database that are associated with the h2o_quality measurement and the tag location = coyote_creek. The LIMIT clause limits the number of series returned to two.

SHOW MEASUREMENTS

Returns a list of measurements for the specified database.

Syntax

SHOW MEASUREMENTS [ON <database_name>] [WITH MEASUREMENT <operator> ['<measurement_name>' | <regular_expression>]] [WHERE <tag_key> <operator> ['<tag_value>' | <regular_expression>]] [LIMIT_clause] [OFFSET_clause]

• ON <database_name> is optional. If the query does not include ON <database_name>, you must specify the database with the db query string parameter in the InfluxDB API request.

• The WITH, WHERE, LIMIT and OFFSET clauses are optional.

• The WHERE in SHOW MEASUREMENTS supports tag comparisons, but not field comparisons.

  Supported operators in the WHERE clause:

  • = : equal to
  • <>: not equal to
  • !=: not equal to
  • =~: matches against
  • !~: doesn’t match against

See Explore data using InfluxQL for documentation on the FROM clause, LIMIT clause, OFFSET clause, and Regular Expressions.

Examples

Run SHOW MEASUREMENTS with the ON clause

SHOW MEASUREMENTS ON noaa

Output:

The query returns the list of measurements in the noaa database. The database has five measurements: average_temperature, h2o_feet, h2o_pH, h2o_quality, and h2o_temperature.

Run SHOW MEASUREMENTS with several clauses (i)

SHOW MEASUREMENTS ON noaa WITH MEASUREMENT =~ /h2o.*/ LIMIT 2 OFFSET 1

Output:

The query returns the measurements in the noaa database that start with h2o. The LIMIT and OFFSET clauses limit the number of measurement names returned to two and offset the results by one, skipping the h2o_feet measurement.

Run SHOW MEASUREMENTS with several clauses (ii)

SHOW MEASUREMENTS ON noaa WITH MEASUREMENT =~ /h2o.*/ WHERE "randtag"  =~ /\d/

The query returns all measurements in the noaa that start with h2o and have values for the tag key randtag that include an integer.

SHOW TAG KEYS

Returns a list of tag keys associated with the specified database.

Syntax

SHOW TAG KEYS [ON <database_name>] [FROM_clause] WITH KEY [ [<operator> "<tag_key>" | <regular_expression>] | [IN ("<tag_key1>","<tag_key2>")]] [WHERE <tag_key> <operator> ['<tag_value>' | <regular_expression>]] [LIMIT_clause] [OFFSET_clause]

• ON <database_name> is optional. If the query does not include ON <database_name>, you must specify the database with db query string parameter in the InfluxDB API request.

• The FROM clause and the WHERE clause are optional.

• The WHERE clause in SHOW TAG KEYS supports tag comparisons, but not field comparisons.

  Supported operators in the WHERE clause:

  • = : equal to
  • <>: not equal to
  • !=: not equal to
  • =~: matches against
  • !~: doesn’t match against

See Explore data using InfluxQL for documentation on the FROM clause, LIMIT clause, OFFSET clause, and Regular Expressions.

Examples

Run SHOW TAG KEYS with the ON clause

SHOW TAG KEYS ON noaa

Output:

The query returns the list of tag keys in the noaa database. The output groups tag keys by measurement name; it shows that every measurement has the location tag key and that the h2o_quality measurement has an additional randtag tag key.

Run SHOW TAG KEYS with several clauses

SHOW TAG KEYS ON noaa FROM "h2o_quality" LIMIT 1 OFFSET 1

Output:

The query returns tag keys from the h2o_quality measurement in the noaa database. The LIMIT and OFFSET clauses limit the number of tag keys returned to one and offsets the results by one.

Run SHOW TAG KEYS with a WITH KEY IN clause

SHOW TAG KEYS ON noaa WITH KEY IN ("location")

Output:

SHOW TAG VALUES

Returns the list of tag values for the specified tag key(s) in the database.

Syntax

SHOW TAG VALUES [ON <database_name>][FROM_clause] WITH KEY [ [<operator> "<tag_key>" | <regular_expression>] | [IN ("<tag_key1>","<tag_key2>")]] [WHERE <tag_key> <operator> ['<tag_value>' | <regular_expression>]] [LIMIT_clause] [OFFSET_clause]

• ON <database_name> is optional. If the query does not include ON <database_name>, you must specify the database with the db query string parameter in the InfluxDB API request.

• The WITH clause is required. It supports specifying a single tag key, a regular expression, and multiple tag keys.

• The FROM, WHERE, LIMIT, and OFFSET clauses are optional.

• The WHERE clause in SHOW TAG KEYS supports tag comparisons, but not field comparisons.

  Supported operators in the WITH and WHERE clauses:

  • = : equal to
  • <>: not equal to
  • !=: not equal to
  • =~: matches against
  • !~: doesn’t match against

See Explore data using InfluxQL for documentation on the FROM clause, LIMIT clause, OFFSET clause, and Regular Expressions.

Examples

Run SHOW TAG VALUES with the ON clause

SHOW TAG VALUES ON noaa WITH KEY = "randtag"

Output:

The query returns all tag values of the randtag tag key in the noaa database. SHOW TAG VALUES groups query results by measurement name.

name: h2o_quality

Run a SHOW TAG VALUES query with several clauses

SHOW TAG VALUES ON noaa WITH KEY IN ("location","randtag") WHERE "randtag" =~ /./ LIMIT 3

Output:

The query returns the tag values of the tag keys location and randtag for all measurements in the noaa database where randtag has tag values. The LIMIT clause limits the number of tag values returned to three.

name: h2o_quality

SHOW FIELD KEYS

Returns the field keys and the data type of their field values.

Syntax

SHOW FIELD KEYS [ON <database_name>] [FROM <measurement_name>]

• ON <database_name> is optional. If the query does not include ON <database_name>, you must specify the database with USE <database_name> when using the InfluxQL shell or with the db query string parameter in the InfluxDB 1.x compatibility API request.
• The FROM clause is optional. See the Data Exploration page for documentation on the FROM clause.

Note: A field’s data 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.

Examples

Run SHOW FIELD KEYS with the ON clause

SHOW FIELD KEYS ON noaa

Output:

The query returns the field keys and field value data types for each measurement in the noaa database.

Run SHOW FIELD KEYS with the FROM clause

SHOW FIELD KEYS ON noaa FROM h2o_feet

Output:

The query returns the fields keys and field value data types for the h2o_feet measurement in the noaa database.

Common Issues with SHOW FIELD KEYS

SHOW FIELD KEYS and field type discrepancies

Field value data types cannot differ within a shard but they can differ across shards. SHOW FIELD KEYS returns every data type, across every shard, associated with the field key.

Example

The all_the_types field stores four different data types:

SHOW FIELD KEYS

name: mymeas

Note that SHOW FIELD KEYS handles field type discrepancies differently from SELECT statements. For more information, see the How does InfluxDB handle field type discrepancies across shards?.

SHOW FIELD KEY CARDINALITY

Cardinality is the product of all unique databases, retention policies, measurements, field keys and tag values in your Influx instance. Managing cardinality is important, as high cardinality leads to greater resource usage.

-- show estimated cardinality of the field key set of current database
SHOW FIELD KEY CARDINALITY
-- show exact cardinality on field key set of specified database
SHOW FIELD KEY EXACT CARDINALITY ON noaa

SHOW TAG KEY CARDINALITY

-- show estimated tag key cardinality
SHOW TAG KEY CARDINALITY
-- show exact tag key cardinality
SHOW TAG KEY EXACT CARDINALITY