Inspect InfluxDB Schema Using InfluxQL SHOW Commands - Time Series Database

InfluxQL provides SHOW statements for inspecting the structure of databases in TSDB for InfluxDB®. Use these statements to list databases, retention policies, series, measurements, tag keys, tag values, and field keys.

SHOW DATABASES	SHOW RETENTION POLICIES	SHOW SERIES
SHOW MEASUREMENTS	SHOW TAG KEYS	SHOW TAG VALUES
SHOW FIELD KEYS

Download the sample data used in the examples on this page from the InfluxDB sample data page.

Sample data

To follow the examples in this topic, connect to the Influx CLI:

$ influx -precision rfc3339
Connected to http://localhost:8086 version 1.7.x
InfluxDB shell 1.7.x
>

Important

Use the Time Series Database (TSDB) console to query database information and perform operations on databases.

SHOW DATABASES

Returns all databases that the account has permission to query.

Syntax

SHOW DATABASES

Example

> SHOW DATABASES

name: databases
name
----
NOAA_water_database
_internal

Results list each database in tabular format. The account has permission to query NOAA_water_database and _internal.

SHOW RETENTION POLICIES

Returns the retention policies for a specified database.

Syntax

SHOW RETENTION POLICIES [ON <database_name>]

ON <database_name> is optional. Without it, either run USE <database_name> in the Influx CLI first, or set the db parameter in your HTTP API request.

Examples

With the ON clause

> SHOW RETENTION POLICIES ON NOAA_water_database

name      duration   shardGroupDuration   replicaN   default
----      --------   ------------------   --------   -------
autogen   0s         168h0m0s             1          true

The NOAA_water_database database has one retention policy: autogen. It is the DEFAULT policy, with infinite duration (0s), a seven-day shard group duration (168h0m0s), and a replication factor of 1.

Without the ON clause — using the CLI

> USE NOAA_water_database
Using database NOAA_water_database

> SHOW RETENTION POLICIES

name      duration   shardGroupDuration   replicaN   default
----      --------   ------------------   --------   -------
autogen   0s         168h0m0s             1          true

Without the ON clause — using the HTTP API

~# curl -G "http://localhost:8086/query?db=NOAA_water_database&pretty=true" \
   --data-urlencode "q=SHOW RETENTION POLICIES"

{
    "results": [
        {
            "statement_id": 0,
            "series": [
                {
                    "columns": [
                        "name",
                        "duration",
                        "shardGroupDuration",
                        "replicaN",
                        "default"
                    ],
                    "values": [
                        [
                            "autogen",
                            "0s",
                            "168h0m0s",
                            1,
                            true
                        ]
                    ]
                }
            ]
        }
    ]
}

SHOW SERIES

Returns the series in a specified database.

Syntax

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

All clauses are optional. Field comparison is not supported — the WHERE clause only accepts tag comparisons. For details on FROM, LIMIT, and OFFSET, see the data exploration documentation.

Operators supported in the WHERE clause

Operator	Meaning
`=`	Equal
`<>`	Not equal
`!=`	Not equal
`=~`	Match
`!~`	Not match

Examples

With the ON clause

> SHOW SERIES ON NOAA_water_database

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

Results follow line protocol format: the element before the first comma is the measurement name, and subsequent elements are tag key-value pairs. The NOAA_water_database database has 5 measurements and 14 series.

Without the ON clause — using the CLI

> USE NOAA_water_database
Using database NOAA_water_database

> SHOW SERIES

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

Without the ON clause — using the HTTP API

~# curl -G "http://localhost:8086/query?db=NOAA_water_database&pretty=true" \
   --data-urlencode "q=SHOW SERIES"

{
    "results": [
        {
            "statement_id": 0,
            "series": [
                {
                    "columns": [
                        "key"
                    ],
                    "values": [
                        ["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"]
                    ]
                }
            ]
        }
    ]
}

With multiple clauses

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

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

Returns all series in NOAA_water_database that include the h2o_quality measurement and the location=coyote_creek tag. LIMIT 2 caps the results at two series.

SHOW MEASUREMENTS

Returns the measurements in a specified database.

Syntax

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

All clauses are optional. Field comparison is not supported — the WHERE clause only accepts tag comparisons. For details on LIMIT and OFFSET, see the data exploration documentation.

Operators supported in the WHERE clause

Operator	Meaning
`=`	Equal
`<>`	Not equal
`!=`	Not equal
`=~`	Match
`!~`	Not match

Examples

With the ON clause

> SHOW MEASUREMENTS ON NOAA_water_database

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

The NOAA_water_database database has five measurements: average_temperature, h2o_feet, h2o_pH, h2o_quality, and h2o_temperature.

Without the ON clause — using the CLI

> USE NOAA_water_database
Using database NOAA_water_database

> SHOW MEASUREMENTS

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

Without the ON clause — using the HTTP API

~# curl -G "http://localhost:8086/query?db=NOAA_water_database&pretty=true" \
   --data-urlencode "q=SHOW MEASUREMENTS"

{
    "results": [
        {
            "statement_id": 0,
            "series": [
                {
                    "name": "measurements",
                    "columns": [
                        "name"
                    ],
                    "values": [
                        ["average_temperature"],
                        ["h2o_feet"],
                        ["h2o_pH"],
                        ["h2o_quality"],
                        ["h2o_temperature"]
                    ]
                }
            ]
        }
    ]
}

With a regex and LIMIT/OFFSET

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

name: measurements
name
----
h2o_pH
h2o_quality

Returns measurements whose names match h2o.* in NOAA_water_database. LIMIT 2 caps results at two. OFFSET 1 skips the first match (h2o_feet), so results start from h2o_pH.

With a regex and a tag filter

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

name: measurements
name
----
h2o_quality

Returns measurements in NOAA_water_database whose names match h2o.* and have at least one numeric randtag value.

SHOW TAG KEYS

Returns the tag keys in a specified database.

Syntax

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

ON, FROM, and WHERE are optional. Field comparison is not supported. For details on FROM, LIMIT, and OFFSET, see the data exploration documentation.

Operators supported in the WHERE clause

Operator	Meaning
`=`	Equal
`<>`	Not equal
`!=`	Not equal
`=~`	Match
`!~`	Not match

Examples

With the ON clause

> SHOW TAG KEYS ON "NOAA_water_database"

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

Results are grouped by measurement. Every measurement has the location tag key. The h2o_quality measurement has two tag keys: location and randtag.

Without the ON clause — using the CLI

> USE NOAA_water_database
Using database NOAA_water_database

> SHOW TAG KEYS

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

Without the ON clause — using the HTTP API

~# curl -G "http://localhost:8086/query?db=NOAA_water_database&pretty=true" \
   --data-urlencode "q=SHOW TAG KEYS"

{
    "results": [
        {
            "statement_id": 0,
            "series": [
                {
                    "name": "average_temperature",
                    "columns": ["tagKey"],
                    "values": [["location"]]
                },
                {
                    "name": "h2o_feet",
                    "columns": ["tagKey"],
                    "values": [["location"]]
                },
                {
                    "name": "h2o_pH",
                    "columns": ["tagKey"],
                    "values": [["location"]]
                },
                {
                    "name": "h2o_quality",
                    "columns": ["tagKey"],
                    "values": [["location"], ["randtag"]]
                },
                {
                    "name": "h2o_temperature",
                    "columns": ["tagKey"],
                    "values": [["location"]]
                }
            ]
        }
    ]
}

With multiple clauses

> SHOW TAG KEYS ON "NOAA_water_database" FROM "h2o_quality" LIMIT 1 OFFSET 1

name: h2o_quality
tagKey
------
randtag

Returns tag keys from the h2o_quality measurement in NOAA_water_database. LIMIT 1 caps results at one tag key. OFFSET 1 skips location, so the result starts from randtag.

SHOW TAG VALUES

Returns the tag values for specified tag keys in a 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, FROM, WHERE, LIMIT, and OFFSET are optional. The WITH KEY clause is required — specify one or more tag keys or a regular expression. Field comparison is not supported. For details on FROM, LIMIT, and OFFSET, see the data exploration documentation.

Operators supported in the WITH and WHERE clauses

Operator	Meaning
`=`	Equal
`<>`	Not equal
`!=`	Not equal
`=~`	Match
`!~`	Not match

Examples

With the ON clause

> SHOW TAG VALUES ON "NOAA_water_database" WITH KEY = "randtag"

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

Returns all values of the randtag tag key in NOAA_water_database. Results are grouped by measurement.

Without the ON clause — using the CLI

> USE NOAA_water_database
Using database NOAA_water_database

> SHOW TAG VALUES WITH KEY = "randtag"

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

Without the ON clause — using the HTTP API

~# curl -G "http://localhost:8086/query?db=NOAA_water_database&pretty=true" \
   --data-urlencode 'q=SHOW TAG VALUES WITH KEY = "randtag"'

{
    "results": [
        {
            "statement_id": 0,
            "series": [
                {
                    "name": "h2o_quality",
                    "columns": ["key", "value"],
                    "values": [
                        ["randtag", "1"],
                        ["randtag", "2"],
                        ["randtag", "3"]
                    ]
                }
            ]
        }
    ]
}

With multiple clauses

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

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

Returns up to three tag values from measurements in NOAA_water_database where the tag key is location or randtag and the randtag value is not null.

SHOW FIELD KEYS

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

Syntax

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

Both clauses are optional. For details on FROM, see the data exploration documentation.

Field value data types can differ across shards. When multiple data types exist for the same field key, SHOW FIELD KEYS returns them in this order: float, integer, string, boolean.

Examples

With the ON clause

> SHOW FIELD KEYS ON "NOAA_water_database"

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

Returns all field keys and their data types for every measurement in NOAA_water_database.

Without the ON clause — using the CLI

> USE NOAA_water_database
Using database NOAA_water_database

> SHOW FIELD KEYS

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

Without the ON clause — using the HTTP API

~# curl -G "http://localhost:8086/query?db=NOAA_water_database&pretty=true" \
   --data-urlencode 'q=SHOW FIELD KEYS'

{
    "results": [
        {
            "statement_id": 0,
            "series": [
                {
                    "name": "average_temperature",
                    "columns": ["fieldKey", "fieldType"],
                    "values": [["degrees", "float"]]
                },
                {
                    "name": "h2o_feet",
                    "columns": ["fieldKey", "fieldType"],
                    "values": [
                        ["level description", "string"],
                        ["water_level", "float"]
                    ]
                },
                {
                    "name": "h2o_pH",
                    "columns": ["fieldKey", "fieldType"],
                    "values": [["pH", "float"]]
                },
                {
                    "name": "h2o_quality",
                    "columns": ["fieldKey", "fieldType"],
                    "values": [["index", "float"]]
                },
                {
                    "name": "h2o_temperature",
                    "columns": ["fieldKey", "fieldType"],
                    "values": [["degrees", "float"]]
                }
            ]
        }
    ]
}

For a single measurement

> SHOW FIELD KEYS ON "NOAA_water_database" FROM "h2o_feet"

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

Returns only the field keys and data types for the h2o_feet measurement.

FAQ about SHOW FIELD KEYS

Why does SHOW FIELD KEYS show multiple data types for the same field key?

Field value data types must be consistent within a shard, but can differ across shards. SHOW FIELD KEYS reports the data type associated with each field key in every shard.

For example, if the all_the_types field has been written with different data types across multiple shards:

> SHOW FIELD KEYS

name: mymeas
fieldKey        fieldType
--------        ---------
all_the_types   integer
all_the_types   float
all_the_types   string
all_the_types   boolean

Important

SHOW FIELD KEYS handles cross-shard type differences differently from the SELECT statement. See the FAQ topic for details.

InfluxDB® is a trademark registered by InfluxData, which is not affiliated with, and does not endorse, TSDB for InfluxDB®.