ContextQMD
Back to Influxdb

Explore Schema

content/shared/influxdb-v2/query-data/influxql/explore-schema.md

latest20.1 KB
Original Source

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

{{% note %}} Command examples use the NOAA water sample data. {{% /note %}}

SHOW SERIES

Return a list of series for the specified database.

Syntax

sql
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

sql
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.

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

Run SHOW SERIES with several clauses

sql
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.

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

SHOW MEASUREMENTS

Returns a list of measurements for the specified database.

Syntax

sql
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

sql
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.

name
average_temperature
h2o_feet
h2o_pH
h2o_quality
h2o_temperature

Run SHOW MEASUREMENTS with several clauses (i)

sql
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.

name
h2o_pH
h2o_quality

Run SHOW MEASUREMENTS with several clauses (ii)

sql
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.

name
h2o_quality

SHOW TAG KEYS

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

Syntax

sql
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

sql
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.

nametagKey
average_temperaturelocation
h2o_feetlocation
h2o_pHlocation
h2o_qualitylocation
h2o_qualityrandtag
h2o_temperaturelocation

Run SHOW TAG KEYS with several clauses

sql
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.

nametagKey
h2o_qualityrandtag

Run SHOW TAG KEYS with a WITH KEY IN clause

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

Output:

measurementtagKey
average_temperaturelocation
h2o_feetlocation
h2o_pHlocation
h2o_qualitylocation
h2o_qualityrandtag
h2o_temperaturelocation

SHOW TAG VALUES

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

Syntax

sql
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

sql
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.

{{% influxql/table-meta %}} name: h2o_quality {{% /influxql/table-meta %}}

keyvalue
randtag1
randtag2
randtag3

Run a SHOW TAG VALUES query with several clauses

sql
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.

{{% influxql/table-meta %}} name: h2o_quality {{% /influxql/table-meta %}}

keyvalue
locationcoyote_creek
locationsanta_monica
randtag1

SHOW FIELD KEYS

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

Syntax

sql
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 %}} 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. {{% /note %}}

Examples

Run SHOW FIELD KEYS with the ON clause

sql
SHOW FIELD KEYS ON noaa

Output:

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

namefieldKeyfieldType
average_temperaturedegreesfloat
h2o_feetlevel descriptionstring
h2o_feetwater_levelfloat
h2o_pHpHfloat
h2o_qualityindexfloat
hh2o_temperaturedegreesfloat

Run SHOW FIELD KEYS with the FROM clause

sql
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.

namefieldKeyfieldType
h2o_feetlevel descriptionstring
h2o_feetwater_levelfloat

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:

sql
SHOW FIELD KEYS

{{% influxql/table-meta %}} name: mymeas {{% /influxql/table-meta %}}

fieldKeyfieldType
all_the_typesinteger
all_the_typesfloat
all_the_typesstring
all_the_typesboolean

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.

sql
-- 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

sql
-- show estimated tag key cardinality
SHOW TAG KEY CARDINALITY
-- show exact tag key cardinality
SHOW TAG KEY EXACT CARDINALITY
<!-- ### SHOW TAG VALUES CARDINALITY ```sql SHOW TAG VALUES EXACT CARDINALITY WITH KEY = "myTagKey" -- show exact tag key values cardinality for a specified tag key SHOW TAG VALUES EXACT CARDINALITY WITH KEY = "myTagKey" --> <!-- ### Filter meta queries by time When you filter meta queries by time, you may see results outside of your specified time. Meta query results are filtered at the shard level, so results can be approximately as granular as your shard group duration. If your time filter spans multiple shards, you'll get results from all shards with points in the specified time range. To review your shards and timestamps on points in the shard, run `SHOW SHARDS`. To learn more about shards and their duration, see [recommended shard groups durations](/influxdb/version/reference/internals/shards/#shard-group-duration). The example below shows how to filter `SHOW TAG KEYS` by approximately one hour using a 1h shard group duration. To filter other meta data, replace `SHOW TAG KEYS` with `SHOW TAG VALUES`, `SHOW SERIES`, `SHOW FIELD KEYS`, and so on. {{% note %}} **Note:** `SHOW MEASUREMENTS` cannot be filtered by time. {{% /note %}} #### Example filtering `SHOW TAG KEYS` by time 1. Specify a shard duration on a new database or [alter an existing shard duration](/enterprise_influxdb/v1/query_language/manage-database/#modify-retention-policies-with-alter-retention-policy). To specify a 1h shard duration when creating a new database, run the following command: ```sh > CREATE database mydb with duration 7d REPLICATION 1 SHARD DURATION 1h name myRP; ``` > **Note:** The minimum shard duration is 1h. 2. Verify the shard duration has the correct time interval (precision) by running the `SHOW SHARDS` command. The example below shows a shard duration with an hour precision. ```sh > SHOW SHARDS name: mydb id database retention_policy shard_group start_time end_time expiry_time owners -- -------- ---------------- ----------- ---------- -------- ----------- ------ > precision h ``` 3. (Optional) Insert sample tag keys. This step is for demonstration purposes. If you already have tag keys (or other meta data) to search for, skip this step. ```sh // Insert a sample tag called "test_key" into the "test" measurement, and then check the timestamp: > INSERT test,test_key=hello value=1 > select * from test name: test time test_key value ---- -------- ----- 434820 hello 1 // Add new tag keys with timestamps one, two, and three hours earlier: > INSERT test,test_key_1=hello value=1 434819 > INSERT test,test_key_2=hello value=1 434819 > INSERT test,test_key_3_=hello value=1 434818 > INSERT test,test_key_4=hello value=1 434817 > INSERT test,test_key_5_=hello value=1 434817 ``` 4. To find tag keys within a shard duration, run one of the following commands: `SHOW TAG KEYS ON database-name <WHERE time clause>` OR `SELECT * FROM measurement <WHERE time clause>` The examples below use test data from step 3. ```sh //Using data from Step 3, show tag keys between now and an hour ago > SHOW TAG KEYS ON mydb where time > now() -1h and time < now() name: test tagKey ------ test_key test_key_1 test_key_2 // Find tag keys between one and two hours ago > SHOW TAG KEYS ON mydb where > time > now() -2h and time < now()-1h name: test tagKey ------ test_key_1 test_key_2 test_key_3 // Find tag keys between two and three hours ago > SHOW TAG KEYS ON mydb where > time > now() -3h and time < now()-2h name: test tagKey ------select statement test_key_3 test_key_4 test_key_5 // For a specified measurement, find tag keys in a given shard by specifying the time boundaries of the shard SELECT * FROM test WHERE time >= '2019-08-09T00:00:00Z' and time < '2019-08-09T10:00:00Z' name: test time test_key_4 test_key_5 value ---- ------------ ------------ ----- 434817 hello 1 434817 hello 1 // For a specified database, find tag keys in a given shard by specifying the time boundaries of the shard > SHOW TAG KEYS ON mydb WHERE time >= '2019-08-09T00:00:00Z' and time < '2019-08-09T10:00:00Z' name: test tagKey ------ test_key_4 test_key_5 ``` -->