All Products
Search
Document Center

Time Series Database:Schema exploration

Last Updated:Nov 30, 2021

InfluxQL is an SQL-like language that can be used to perform operations on data stored in TSDB for InfluxDB®. The following sections describe the InfluxQL syntax that can be used to query your schema.

SHOW DATABASES

SHOW RETENTION POLICIES

SHOW SERIES

SHOW MEASUREMENTS

SHOW TAG KEYS

SHOW TAG VALUES

SHOW FIELD KEYS

-

-

Note

To download the sample data that is used in this topic, see Sample data.

Sample data

Before you start to query your schema, log on to the Influx CLI.

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

Notice

We recommend that you use the Time Series Database (TSDB) console to query database information and perform operations on databases.

SHOW DATABASES

Returns all databases that your account has permissions to query.

SHOW DATABASES

Examples

Run a SHOW DATABASES query

> SHOW DATABASES

name: databases
name
----
NOAA_water_database
_internal

The preceding query returns the names of the databases in a tabular format. The account has the permissions to query the NOAA_water_database database and the _internal database.

SHOW RETENTION POLICIES

Returns the data retention policy of the specified database.

SHOW RETENTION POLICIES [ON <database_name>]

Syntax description

The ON <database_name> clause is optional. If your query does not contain ON <database_name>, you must execute the USE <database_name> statement in the Influx CLI or set the db parameter in your HTTP API request to specify the database that you want to query.

Examples

Example 1: Run a SHOW RETENTION POLICIES query that contains the ON <database_name> clause

> SHOW RETENTION POLICIES ON NOAA_water_database

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

The preceding query returns the retention policy of the NOAA_water_database database in a tabular format. The response of the query shows that the database has only one retention policy named autogen. autogen is the DEFAULT retention policy of the database. This retention policy has an infinite duration, a seven-day shard group duration, and a replication factor of one.

Example 2: Run a SHOW RETENTION POLICIES query that does not contain the ON <database_name> clause

Execute the USE <database_name> statement to specify the database that you want to query

> USE NOAA_water_database
Using database NOAA_water_database

> SHOW RETENTION POLICIES

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

Set the db parameter to specify the database that you want to query

~# 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 of the specified database.

Syntax

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

Syntax description

The ON <database_name> clause is optional. If your query does not contain ON <database_name>, you must execute the USE <database_name> statement in the Influx CLI or set the db parameter in your HTTP API request to specify the database that you want to query.

The FROM, WHERE, LIMIT, and OFFSET clauses are also optional. Field comparison is not supported in SHOW SERIES queries. In the WHERE clause, you can use the operators described in the following table to compare tags.

The following table describes the operators that can be used in the WHERE clause.

=

Equal

<>

Not equal

!=

Not equal

=~

Match

!~

Not match

For more information about regular expressions and the FROM, LIMIT, and OFFSET clauses, see the Data exploration chapter.

Examples

Example 1: Run a SHOW SERIES query that contains the ON <database_name> 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

The preceding query returns results in a format that is similar to the line protocol format. The element before the first comma (,) in each line is the measurement name. The elements that follow the first comma (,) in each line are tag keys or tag values. The response of the query shows that the NOAA_water_database database has 5 different measurements and 14 different series.

Example 2: Run a SHOW SERIES query that does not contain the ON <database_name> clause

Execute the USE <database_name> statement to specify the database that you want to query

> 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

Set the db parameter to specify the database that you want to query

~# 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"
                        ]
                    ]
                }
            ]
        }
    ]
}

Example 3: Run a SHOW SERIES query that contains 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

The preceding query returns all the series that include the h2o_quality measurement and the location = coyote_creek tag-value pair from the NOAA_water_database database. The LIMIT clause limits the number of returned series to two.

SHOW MEASUREMENTS

Returns the measurements in the specified database.

Syntax

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

Syntax description

The ON <database_name> clause is optional. If your query does not contain ON <database_name>, you must execute the USE <database_name> statement in the Influx CLI or set the db parameter in your HTTP API request to specify the database that you want to query.

The WITH, WHERE, LIMIT, and OFFSET clauses are optional. Field comparison is not supported in SHOW MEASUREMENTS queries. In the WHERE clause, you can use the operators described in the following table to compare tags.

The following table describes the operators that can be used in the WHERE clause.

=

Equal

<>

Not equal

!=

Not equal

=~

Match

!~

Not match

For more information about regular expressions and the LIMIT and OFFSET clauses, see the Data exploration chapter.

Examples

Example 1: Run a SHOW MEASUREMENTS query that contains the ON <database_name> clause

> SHOW MEASUREMENTS ON NOAA_water_database

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

The preceding query returns the measurements in the NOAA_water_database database. The NOAA_water_database database has the following measurements: average_temperature, h2o_feet, h2o_pH, h2o_quality and h2o_temperature.

Example 2: Run a SHOW MEASUREMENTS query that does not contain the ON <database_name> clause

Execute the USE <database_name> statement to specify the database that you want to query

> USE NOAA_water_database
Using database NOAA_water_database

> SHOW MEASUREMENTS
name: measurements
name
----
average_temperature
h2o_feet
h2o_pH
h2o_quality
h2o_temperature

Set the db parameter to specify the database that you want to query

~# 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"
                          ]
                      ]
                  }
              ]
          }
      ]
  }

Example 3: Run a SHOW MEASUREMENTS query that contains multiple clauses (i)

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

name: measurements
name
----
h2o_pH
h2o_quality

The preceding query returns the measurements whose names start with h2o in the NOAA_water_database database. The LIMIT clause limits the number of returned measurements to two. The OFFSET clause skips h2o_feet. The query returns the first two measurements that appear after h2o_feet.

Example 4: Run a SHOW MEASUREMENTS query that contains multiple clauses (ii)

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

name: measurements
name
----
h2o_quality

The preceding query returns the measurements that meet the specified requirements in the NOAA_water_database database. One of the requirements is that the measurement names must start with h2o. The other requirement is that the values of the randtag tag must include at least one integer.

SHOW TAG KEYS

Returns the tag keys in the specified database.

Syntax

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

Syntax description

The ON <database_name> clause is optional. If your query does not contain ON <database_name>, you must execute the USE <database_name> statement in the Influx CLI or set the db parameter in your HTTP API request to specify the database that you want to query.

The FROM and WHERE clauses are optional. Field comparison is not supported in SHOW TAG KEYS queries. In the WHERE clause, you can use the operators described in the following table to compare tags.

The following table describes the operators that can be used in the WHERE clause.

=

Equal

<>

Not equal

!=

Not equal

=~

Match

!~

Not match

For more information about regular expressions and the FROM, LIMIT, and OFFSET clauses, see the Data exploration chapter.

Examples

Example 1: Run a SHOW TAG KEYS query that contains the ON <database_name> 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

The preceding query returns the tag keys in the NOAA_water_database database. The results are grouped based on measurement names. The results show that each measurement has the location tag key. The h2o_quality measurement has two tag keys: randtag and location.

Example 2: Run a SHOW TAG KEYS query that does not contain the ON <database_name> clause

Execute the USE <database_name> statement to specify the database that you want to query

> 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

Set the db parameter to specify the database that you want to query

~# 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"
                        ]
                    ]
                }
            ]
        }
    ]
}

Example 3: Run a SHOW TAG KEYS query that contains multiple clauses

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

name: h2o_quality
tagKey
------
randtag

The preceding query returns the tag key from the h2o_quality measurement in the NOAA_water_database database. The LIMIT clause limits the number of returned tag keys to one. The OFFSET clause skips the first measurement.

SHOW TAG VALUES

Returns the tag values of the specified tag keys in the database that you want to query.

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]

Syntax description

The ON <database_name> clause is optional. If your query does not contain ON <database_name>, you must execute the USE <database_name> statement in the Influx CLI or set the db parameter in your HTTP API request to specify the database that you want to query.

The WITH clause is required. You can specify one regular expression or one or more tag keys in the WITH clause.

The FROM, WHERE, LIMIT, and OFFSET clauses are optional. Field comparison is not supported in SHOW TAG VALUES queries. In the WHERE clause, you can use the operators described in the following table to compare tags.

The following table describes the operators that can be used in the WITH and WHERE clauses.

=

Equal

<>

Not equal

!=

Not equal

=~

Match

!~

Not match

For more information about regular expressions and the FROM, LIMIT, and OFFSET clauses, see the Data exploration chapter.

Examples

Example 1: Run a SHOW TAG VALUES query that contains the ON <database_name> clause

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

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

The preceding query returns all the tag values of the randtag tag key in the NOAA_water_database database. SHOW TAG VALUES groups the results based on measurement names.

Example 2: Run a SHOW TAG VALUES query that does not contain the ON <database_name> clause

Execute the USE <database_name> statement to specify the database that you want to query

> 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

Set the db parameter to specify the database that you want to query

~# 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"
                        ]
                    ]
                }
            ]
        }
    ]
}

Example 3: Run a SHOW TAG VALUES query that contains 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

The preceding query returns the tag values that meet the specified requirements of the measurements in the NOAA_water_database database. The requirements are that the tag key must be location or randtag, and the values of the randtag tag key cannot be NULL values. The LIMIT clause limits the number of returned tag values to three.

SHOW FIELD KEYS

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

Syntax

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

Syntax description

The ON <database_name> clause is optional. If your query does not contain ON <database_name>, you must execute the USE <database_name> statement in the Influx CLI or set the db parameter in your HTTP API request to specify the database that you want to query.

The FROM clause is also optional. For more information about the FROM clause, see the Data exploration chapter.

Note

Note: The data types of field values can be different across shards. If multiple data types are to be returned, the SHOW FIELD KEYS statement returns results based on the following sequence: FLOAT, INTEGER, STRING, BOOLEAN.

Examples

Example 1: Run a SHOW FIELD KEYS query that contains the ON <database_name> 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

The preceding query returns the field keys and the data types of the field values from each measurement in the NOAA_water_database database.

Example 2: Run a SHOW FIELD KEYS query that does not contain the ON <database_name> clause

Execute the USE <database_name> statement to specify the database that you want to query

> 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

Set the db parameter to specify the database that you want to query

~# 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"
                        ]
                    ]
                }
            ]
        }
    ]
}

Example 3: Run a SHOW FIELD KEYS query that contains the FROM clause

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

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

The preceding query returns the field keys and the data types of the field values from the h2o_feet measurement in the NOAA_water_database database.

FAQ about SHOW FIELD KEYS

Question 1: SHOW FIELD KEYS and differences in the data types of field values

The data types of field values must be the same within a shard. The data types of field values can be different across shards. The SHOW FIELD KEYS statement returns the data types of field values associated with field keys in each shard.

Examples

The following code block shows that the all_the_types field stores four data types:

> SHOW FIELD KEYS

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

The SHOW FIELD KEYS statement handles differences in the data types of field values in a different manner from the SELECT statement. For more information, see the FAQ topic.

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