Schema exploration using InfluxQL

    Sample data

    The data used in this document are available for download on the Sample Data page.

    Before proceeding, login to the Influx CLI.

    Returns a list of all on your instance.

    Examples

    Run a SHOW DATABASES query

    1. > SHOW DATABASES
    3. name: databases
    4. name
    5. ----
    6. NOAA_water_database
    7. _internal

    The query returns database names in a tabular format.This InfluxDB instance has two databases: NOAA_water_database and _internal.

    SHOW RETENTION POLICIES

    Returns a list of for the specified database.

    Syntax

    1. SHOW RETENTION POLICIES [ON <database_name>]

    Description of syntax

    ON <database_name> is optional.If the query does not include ON <database_name>, you must specify thedatabase with USE <database_name> in the or with the db querystring parameter in the InfluxDB API request.

    Examples

    Run a SHOW RETENTION POLICIES query with the ON clause

    1. > SHOW RETENTION POLICIES ON NOAA_water_database
    3. name      duration   shardGroupDuration   replicaN   default
    4. ----      --------   ------------------   --------   -------
    5. autogen   0s         168h0m0s             1          true

    The query returns the list of retention policies in the NOAA_water_databasedatabase in tabular format.The database has one retention policy called autogen.The autogen retention policy has an infinite ,a seven-day shard group duration,a of one, and it is the DEFAULT retention policy for the database.

    Run a SHOW RETENTION POLICIES query without the ON clause

    InfluxDB API

    Specify the database with USE <database_name>

    1. > USE NOAA_water_database
    2. Using database NOAA_water_database
    4. > SHOW RETENTION POLICIES
    6. name      duration   shardGroupDuration   replicaN   default
    7. ----      --------   ------------------   --------   -------
    8. autogen   0s         168h0m0s             1          true

    Specify the database with the db query string parameter:

    1. ~# curl -G "http://localhost:8086/query?db=NOAA_water_database&pretty=true" --data-urlencode "q=SHOW RETENTION POLICIES"
    3. {
    4.     "results": [
    5.         {
    6.             "statement_id": 0,
    7.             "series": [
    8.                 {
    9.                     "columns": [
    10.                         "name",
    11.                         "duration",
    12.                         "shardGroupDuration",
    13.                         "replicaN",
    14.                         "default"
    15.                     ],
    16.                     "values": [
    17.                         [
    18.                             "autogen",
    19.                             "0s",
    20.                             "168h0m0s",
    21.                             1,
    22.                             true
    23.                         ]
    24.                     ]
    25.                 }
    26.             ]
    27.         }
    28.     ]
    29. }

    Returns a list of forthe specified database.

    Syntax

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

    Description of syntax

    ON <database_name> is optional.If the query does not include ON <database_name>, you must specify thedatabase with USE <database_name> in the or with the db querystring parameter in the InfluxDB API request.

    The FROM, WHERE, LIMIT, and OFFSET clauses are optional.The WHERE clause supports tag comparisons; field comparisons are notvalid for the SHOW SERIES query.

    Supported operators in the WHERE clause:=   equal to<> not equal to!= not equal to=~ matches against!~ doesn’t match against

    See the Data Exploration page for documentation on the,LIMIT clause,,and on Regular Expressions in Queries.

    Run a SHOW SERIES query with the ON clause

    1. // Returns series for all shards in the database
    2. > SHOW SERIES ON NOAA_water_database
    4. key
    5. ---
    6. average_temperature,location=coyote_creek
    7. average_temperature,location=santa_monica
    8. h2o_feet,location=coyote_creek
    9. h2o_feet,location=santa_monica
    10. h2o_pH,location=coyote_creek
    11. h2o_pH,location=santa_monica
    12. h2o_quality,location=coyote_creek,randtag=1
    13. h2o_quality,location=coyote_creek,randtag=2
    14. h2o_quality,location=coyote_creek,randtag=3
    15. h2o_quality,location=santa_monica,randtag=1
    16. h2o_quality,location=santa_monica,randtag=2
    17. h2o_quality,location=santa_monica,randtag=3
    18. h2o_temperature,location=coyote_creek
    19. h2o_temperature,location=santa_monica

    The query’s output is similar to the line protocol format.Everything before the first comma is the name.Everything after the first comma is either a tag key or a .The NOAA_water_database has five different measurements and 14 different series.

    Run a SHOW SERIES query without the ON clause

    InfluxDB API

    Specify the database with USE <database_name>

    1. > USE NOAA_water_database
    2. Using database NOAA_water_database
    4. > SHOW SERIES
    6. key
    7. ---
    8. average_temperature,location=coyote_creek
    9. average_temperature,location=santa_monica
    10. h2o_feet,location=coyote_creek
    11. h2o_feet,location=santa_monica
    12. h2o_pH,location=coyote_creek
    13. h2o_pH,location=santa_monica
    14. h2o_quality,location=coyote_creek,randtag=1
    15. h2o_quality,location=coyote_creek,randtag=2
    16. h2o_quality,location=coyote_creek,randtag=3
    17. h2o_quality,location=santa_monica,randtag=1
    18. h2o_quality,location=santa_monica,randtag=2
    19. h2o_quality,location=santa_monica,randtag=3
    20. h2o_temperature,location=coyote_creek
    21. h2o_temperature,location=santa_monica

    Specify the database with the db query string parameter:

    1. ~# curl -G "http://localhost:8086/query?db=NOAA_water_database&pretty=true" --data-urlencode "q=SHOW SERIES"
    3. {
    4.     "results": [
    5.         {
    6.             "statement_id": 0,
    7.             "series": [
    8.                 {
    9.                     "columns": [
    10.                         "key"
    11.                     ],
    12.                     "values": [
    13.                         [
    14.                             "average_temperature,location=coyote_creek"
    15.                         ],
    16.                         [
    17.                             "average_temperature,location=santa_monica"
    18.                         ],
    19.                         [
    20.                             "h2o_feet,location=coyote_creek"
    21.                         ],
    22.                         [
    23.                             "h2o_feet,location=santa_monica"
    24.                         ],
    25.                         [
    26.                             "h2o_pH,location=coyote_creek"
    27.                         ],
    28.                         [
    29.                             "h2o_pH,location=santa_monica"
    30.                         ],
    31.                         [
    32.                             "h2o_quality,location=coyote_creek,randtag=1"
    33.                         ],
    34.                         [
    35.                             "h2o_quality,location=coyote_creek,randtag=2"
    36.                         ],
    37.                         [
    38.                             "h2o_quality,location=coyote_creek,randtag=3"
    39.                         ],
    40.                         [
    41.                             "h2o_quality,location=santa_monica,randtag=1"
    42.                         ],
    43.                         [
    44.                             "h2o_quality,location=santa_monica,randtag=2"
    45.                         ],
    46.                         [
    47.                             "h2o_quality,location=santa_monica,randtag=3"
    48.                         ],
    49.                         [
    50.                             "h2o_temperature,location=coyote_creek"
    51.                         ],
    52.                         [
    53.                             "h2o_temperature,location=santa_monica"
    54.                         ]
    55.                     ]
    56.                 }
    57.             ]
    58.         }
    59.     ]
    60. }

    Run a SHOW SERIES query with several clauses

    1. > SHOW SERIES ON NOAA_water_database FROM "h2o_quality" WHERE "location" = 'coyote_creek' LIMIT 2
    3. key
    4. ---
    5. h2o_quality,location=coyote_creek,randtag=1
    6. h2o_quality,location=coyote_creek,randtag=2

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

    Run a SHOW SERIES query limited by time

    Limit series returned within a specified shard group duration.

    1. // Returns all series in the current shard.
    2. > SHOW SERIES ON NOAA_water_database WHERE time < now() - 1m
    4. key
    5. ---
    6. average_temperature,location=coyote_creek
    7. h2o_feet,location=coyote_creek
    8. h2o_pH,location=coyote_creek
    9. h2o_quality,location=coyote_creek,randtag=2
    10. h2o_quality,location=coyote_creek,randtag=3
    11. h2o_temperature,location=coyote_creek

    The query above returns all series in the NOAA_water_database database in the current shard group. The WHERE clause limits results to series in the shard group that contain a timestamp in the last minute. Note, if a shard group duration is 7 days, results returned may be up to 7 days old.

    Note, if the specified shard group duration is 7 days, the query above returns series for the last 3 or 4 shards.

    SHOW MEASUREMENTS

    Syntax

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

    Description of Syntax

    ON <database_name> is optional.If the query does not include ON <database_name>, you must specify thedatabase with USE <database_name> in the CLI or with the db querystring parameter in the request.

    The WITH, WHERE, LIMIT and OFFSET clauses are optional.The WHERE clause supports tag comparisons; field comparisons are not valid for the SHOW MEASUREMENTS query.

    Supported operators in the WHERE clause:=   equal to<> not equal to!= not equal to=~ matches against!~ doesn’t match against

    See the Data Exploration page for documentation on theLIMIT clause,,and on Regular expressions in queries.

    Examples

    Run a SHOW MEASUREMENTS query with the ON clause

    1. > SHOW MEASUREMENTS ON NOAA_water_database
    3. name: measurements
    4. name
    5. ----
    6. average_temperature
    7. h2o_feet
    8. h2o_pH
    9. h2o_quality
    10. h2o_temperature

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

    Run a SHOW MEASUREMENTS query without the ON clause

    CLI

    Specify the database with USE <database_name>

    1. > USE NOAA_water_database
    2. Using database NOAA_water_database
    4. > SHOW MEASUREMENTS
    5. name: measurements
    6. name
    7. ----
    9. h2o_feet
    10. h2o_pH
    11. h2o_quality
    12. h2o_temperature

    Specify the database with the db query string parameter:

    1. ~# curl -G "http://localhost:8086/query?db=NOAA_water_database&pretty=true" --data-urlencode "q=SHOW MEASUREMENTS"
    3. {
    4.   {
    5.       "results": [
    6.           {
    7.               "statement_id": 0,
    8.               "series": [
    9.                   {
    10.                       "name": "measurements",
    11.                       "columns": [
    12.                           "name"
    13.                       ],
    14.                       "values": [
    15.                           [
    16.                               "average_temperature"
    17.                           ],
    18.                           [
    19.                               "h2o_feet"
    20.                           ],
    21.                           [
    22.                               "h2o_pH"
    23.                           ],
    24.                           [
    25.                               "h2o_quality"
    26.                           ],
    27.                           [
    28.                               "h2o_temperature"
    29.                           ]
    30.                       ]
    31.                   }
    32.               ]
    33.           }
    34.       ]
    35.   }

    Run a SHOW MEASUREMENTS query with several clauses (i)

    1. > SHOW MEASUREMENTS ON NOAA_water_database WITH MEASUREMENT =~ /h2o.*/ LIMIT 2 OFFSET 1
    3. name: measurements
    4. name
    5. ----
    6. h2o_pH
    7. h2o_quality

    The query returns the measurements in the NOAA_water_database database thatstart with h2o.The LIMIT and OFFSET clauses limit the number of measurement names returned totwo and offset the results by one, skipping the h2o_feet measurement.

    Run a SHOW MEASUREMENTS query with several clauses (ii)

    1. > SHOW MEASUREMENTS ON NOAA_water_database WITH MEASUREMENT =~ /h2o.*/ WHERE "randtag"  =~ /\d/
    3. name: measurements
    4. name
    5. ----
    6. h2o_quality

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

    Returns a list of tag keysassociated with the specified .

    Syntax

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

    Description of syntax

    ON <database_name> is optional.If the query does not include ON <database_name>, you must specify thedatabase with USE <database_name> in the CLI or with the db querystring parameter in the request.

    The FROM clause and the WHERE clause are optional.The WHERE clause supports tag comparisons; field comparisons are notvalid for the SHOW TAG KEYS query.

    Supported operators in the WHERE clause:=   equal to<> not equal to!= not equal to=~ matches against!~ doesn’t match against

    See the Data Exploration page for documentation on theFROM clause,,OFFSET clause,and on .

    Examples

    Run a SHOW TAG KEYS query with the ON clause

    1. > SHOW TAG KEYS ON "NOAA_water_database"
    3. name: average_temperature
    4. tagKey
    5. ------
    6. location
    8. name: h2o_feet
    9. tagKey
    10. ------
    11. location
    13. name: h2o_pH
    14. tagKey
    15. ------
    16. location
    18. name: h2o_quality
    19. tagKey
    20. ------
    21. location
    22. randtag
    24. name: h2o_temperature
    25. tagKey
    26. ------
    27. location

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

    Run a SHOW TAG KEYS query without the ON clause

    InfluxDB API

    Specify the database with USE <database_name>

    1. > USE NOAA_water_database
    2. Using database NOAA_water_database
    4. > SHOW TAG KEYS
    6. name: average_temperature
    7. tagKey
    8. ------
    9. location
    11. name: h2o_feet
    12. tagKey
    13. ------
    14. location
    16. name: h2o_pH
    17. tagKey
    18. ------
    19. location
    21. name: h2o_quality
    22. tagKey
    23. ------
    24. location
    25. randtag
    27. name: h2o_temperature
    28. tagKey
    29. ------
    30. location

    Specify the database with the db query string parameter:

    1. ~# curl -G "http://localhost:8086/query?db=NOAA_water_database&pretty=true" --data-urlencode "q=SHOW TAG KEYS"
    3. {
    4.     "results": [
    5.         {
    6.             "statement_id": 0,
    7.             "series": [
    8.                 {
    9.                     "name": "average_temperature",
    10.                     "columns": [
    11.                         "tagKey"
    12.                     ],
    13.                     "values": [
    14.                         [
    15.                             "location"
    16.                         ]
    17.                     ]
    18.                 },
    19.                 {
    20.                     "name": "h2o_feet",
    21.                     "columns": [
    22.                         "tagKey"
    23.                     ],
    24.                     "values": [
    25.                         [
    26.                             "location"
    27.                         ]
    28.                     ]
    29.                 },
    30.                 {
    31.                     "name": "h2o_pH",
    32.                     "columns": [
    33.                         "tagKey"
    34.                     ],
    35.                     "values": [
    36.                         [
    37.                             "location"
    38.                         ]
    39.                     ]
    40.                 },
    41.                 {
    42.                     "name": "h2o_quality",
    43.                     "columns": [
    44.                         "tagKey"
    45.                     ],
    46.                     "values": [
    47.                         [
    48.                             "location"
    49.                         ],
    50.                         [
    51.                             "randtag"
    52.                         ]
    53.                     ]
    54.                 },
    55.                 {
    56.                     "name": "h2o_temperature",
    57.                     "columns": [
    58.                         "tagKey"
    59.                     ],
    60.                     "values": [
    61.                         [
    62.                             "location"
    63.                         ]
    64.                     ]
    65.                 }
    66.             ]
    67.         }
    68.     ]
    69. }

    Run a SHOW TAG KEYS query with several clauses

    1. > SHOW TAG KEYS ON "NOAA_water_database" FROM "h2o_quality" LIMIT 1 OFFSET 1
    3. name: h2o_quality
    4. tagKey
    5. ------
    6. randtag

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

    SHOW TAG VALUES

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

    1. 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]

    Description of syntax

    ON <database_name> is optional.If the query does not include ON <database_name>, you must specify thedatabase with USE <database_name> in the CLI or with the db query string parameter in the 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 supports tag comparisons; field comparisons are notvalid for the SHOW TAG KEYS query.

    See the Data Exploration page for documentation on theFROM clause,,OFFSET clause,and on .

    Examples

    Run a SHOW TAG VALUES query with the ON clause

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

    Run a SHOW TAG VALUES query without the ON clause

    InfluxDB API

    Specify the database with USE <database_name>

    1. > USE NOAA_water_database
    2. Using database NOAA_water_database
    4. > SHOW TAG VALUES WITH KEY = "randtag"
    7. key       value
    8. ---       -----
    9. randtag   1
    10. randtag   2

    Specify the database with the db query string parameter:

    1. ~# curl -G "http://localhost:8086/query?db=NOAA_water_database&pretty=true" --data-urlencode 'q=SHOW TAG VALUES WITH KEY = "randtag"'
    3. {
    4.     "results": [
    5.         {
    6.             "statement_id": 0,
    7.             "series": [
    8.                 {
    9.                     "name": "h2o_quality",
    10.                     "columns": [
    11.                         "key",
    12.                         "value"
    13.                     ],
    14.                     "values": [
    15.                         [
    16.                             "randtag",
    17.                             "1"
    18.                         ],
    19.                         [
    20.                             "randtag",
    21.                             "2"
    22.                         ],
    23.                         [
    24.                             "randtag",
    25.                             "3"
    26.                         ]
    27.                     ]
    28.                 }
    29.             ]
    30.         }
    31.     ]
    32. }

    Run a SHOW TAG VALUES query with several clauses

    1. > SHOW TAG VALUES ON "NOAA_water_database" WITH KEY IN ("location","randtag") WHERE "randtag" =~ /./ LIMIT 3
    3. name: h2o_quality
    4. key        value
    5. ---        -----
    6. location   coyote_creek
    7. location   santa_monica
    8. randtag       1

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

    Returns the field keys and the of theirfield values.

    Syntax

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

    Description of syntax

    ON <database_name> is optional.If the query does not include ON <database_name>, you must specify thedatabase with USE <database_name> in the or with the db querystring parameter in the InfluxDB API request.

    The FROM clause is also optional.See the Data Exploration page for documentation on the.

    Examples

    Run a SHOW FIELD KEYS query with the ON clause

    1. > SHOW FIELD KEYS ON "NOAA_water_database"
    3. name: average_temperature
    4. fieldKey            fieldType
    5. --------            ---------
    6. degrees             float
    8. name: h2o_feet
    9. fieldKey            fieldType
    10. --------            ---------
    11. level description   string
    12. water_level         float
    14. name: h2o_pH
    15. fieldKey            fieldType
    16. --------            ---------
    17. pH                  float
    19. name: h2o_quality
    20. fieldKey            fieldType
    21. --------            ---------
    22. index               float
    24. name: h2o_temperature
    25. fieldKey            fieldType
    26. --------            ---------
    27. degrees             float

    The query returns the field keys and field value data types for eachmeasurement in the NOAA_water_database database.

    Run a SHOW FIELD KEYS query without the ON clause

    InfluxDB API

    Specify the database with USE <database_name>

    1. > USE NOAA_water_database
    2. Using database NOAA_water_database
    4. > SHOW FIELD KEYS
    6. name: average_temperature
    7. fieldKey            fieldType
    8. --------            ---------
    9. degrees             float
    11. name: h2o_feet
    12. fieldKey            fieldType
    13. --------            ---------
    14. level description   string
    15. water_level         float
    17. name: h2o_pH
    18. fieldKey            fieldType
    19. --------            ---------
    20. pH                  float
    22. name: h2o_quality
    23. fieldKey            fieldType
    24. --------            ---------
    25. index               float
    27. name: h2o_temperature
    28. fieldKey            fieldType
    29. --------            ---------
    30. degrees             float

    Specify the database with the db query string parameter:

    1. ~# curl -G "http://localhost:8086/query?db=NOAA_water_database&pretty=true" --data-urlencode 'q=SHOW FIELD KEYS'
    3. {
    4.     "results": [
    5.         {
    6.             "statement_id": 0,
    7.             "series": [
    8.                 {
    9.                     "name": "average_temperature",
    10.                     "columns": [
    11.                         "fieldKey",
    12.                         "fieldType"
    13.                     ],
    14.                     "values": [
    15.                         [
    16.                             "degrees",
    17.                             "float"
    18.                         ]
    19.                     ]
    20.                 },
    21.                 {
    22.                     "name": "h2o_feet",
    23.                     "columns": [
    24.                         "fieldKey",
    25.                         "fieldType"
    26.                     ],
    27.                     "values": [
    28.                         [
    29.                             "level description",
    30.                             "string"
    31.                         ],
    32.                         [
    33.                             "water_level",
    34.                             "float"
    35.                         ]
    36.                     ]
    37.                 },
    38.                 {
    39.                     "name": "h2o_pH",
    40.                     "columns": [
    41.                         "fieldKey",
    42.                         "fieldType"
    43.                     ],
    44.                     "values": [
    45.                         [
    46.                             "pH",
    47.                             "float"
    48.                         ]
    49.                     ]
    50.                 },
    51.                 {
    52.                     "name": "h2o_quality",
    53.                     "columns": [
    54.                         "fieldKey",
    55.                         "fieldType"
    56.                     ],
    57.                     "values": [
    58.                         [
    59.                             "index",
    60.                             "float"
    61.                         ]
    62.                     ]
    63.                 },
    64.                 {
    65.                     "name": "h2o_temperature",
    66.                     "columns": [
    67.                         "fieldKey",
    68.                         "fieldType"
    69.                     ],
    70.                     "values": [
    71.                         [
    72.                             "degrees",
    73.                             "float"
    74.                         ]
    75.                     ]
    76.                 }
    77.             ]
    78.         }
    79.     ]
    80. }

    Run a SHOW FIELD KEYS query with the FROM clause

    1. > SHOW FIELD KEYS ON "NOAA_water_database" FROM "h2o_feet"
    3. name: h2o_feet
    4. fieldKey            fieldType
    5. --------            ---------
    6. level description   string
    7. water_level         float

    The query returns the fields keys and field value data types for the h2o_feetmeasurement in the NOAA_water_database database.

    Common Issues with SHOW FIELD KEYS

    SHOW FIELD KEYS and field type discrepancies

    Field valuedata typescannot differ within a but theycan differ across shards.SHOW FIELD KEYS returns every data type, across every shard, associated withthe field key.

    Example

    The all_the_types field stores four different data types:

    1. > SHOW FIELD KEYS
    3. name: mymeas
    4. fieldKey        fieldType
    5. --------        ---------
    6. all_the_types   integer
    7. all_the_types   float
    8. all_the_types   string
    9. all_the_types   boolean

    Note that SHOW FIELD KEYS handles field type discrepancies differently fromSELECT statements.For more information, see the.

    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.

    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 MEASUREMENTS, SHOW FIELD KEYS, and so on.

    Example filtering SHOW TAG KEYS by time

    • Specify a shard duration on a new database or alter an existing shard duration. To specify a 1h shard duration when creating a new database, run the following command:
    1. > CREATE database mydb with duration 7d REPLICATION 1 SHARD DURATION 1h name myRP;

    Note: The minimum shard duration is 1h.

    • 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.
    1. > SHOW SHARDS
    2. name: mydb
    3. id database retention_policy shard_group start_time end_time expiry_time owners
    4. -- -------- ---------------- ----------- ---------- -------- ----------- ------
    5. > precision h
    • (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.
    1. // Insert a sample tag called "test_key" into the "test" measurement, and then check the timestamp:
    2. > INSERT test,test_key=hello value=1
    4. > select * from test
    5. name: test
    6. time test_key value
    7. ---- -------- -----
    8. 434820 hello 1
    10. // Add new tag keys with timestamps one, two, and three hours earlier:
    12. > INSERT test,test_key_1=hello value=1 434819
    13. > INSERT test,test_key_2=hello value=1 434819
    14. > INSERT test,test_key_3_=hello value=1 434818
    15. > INSERT test,test_key_4=hello value=1 434817
    • To find tag keys within a shard duration, run one of the following commands:

    SHOW TAG KEYS ON database-name <WHERE time clause> OR

    The examples below use test data from step 3.