Database management using InfluxQL

    If you’re looking for queries (for example, SHOW DATABASES or SHOW RETENTION POLICIES), see Schema Exploration.

    The examples in the sections below use the InfluxDB .You can also execute the commands using the InfluxDB API; simply send a GET request to the /query endpoint and include the command in the URL parameter q.For more on using the InfluxDB API, see Querying data.

    Creates a new database.

    Syntax

    Description of syntax

    CREATE DATABASE requires a database .

    The WITH, DURATION, REPLICATION, SHARD DURATION, and NAME clauses are optional and create a single retention policy associated with the created database.If you do not specify one of the clauses after WITH, the relevant behavior defaults to the autogen retention policy settings.The created retention policy automatically serves as the database’s default retention policy.For more information about those clauses, see .

    A successful CREATE DATABASE query returns an empty result.If you attempt to create a database that already exists, InfluxDB does nothing and does not return an error.

    Examples

    Create a database
    1. > CREATE DATABASE "NOAA_water_database"
    2. >

    The query creates a database called NOAA_water_database.By default, InfluxDB also creates the autogen retention policy and associates it with the NOAA_water_database.

    Create a database with a specific retention policy
    1. > CREATE DATABASE "NOAA_water_database" WITH DURATION 3d REPLICATION 1 SHARD DURATION 1h NAME "liquid"
    2. >

    The query creates a database called NOAA_water_database.It also creates a default retention policy for NOAA_water_database with a DURATION of three days, a replication factor of one, a duration of one hour, and with the name liquid.

    Delete a database with DROP DATABASE

    The DROP DATABASE query deletes all of the data, measurements, series, continuous queries, and retention policies from the specified database.The query takes the following form:

    1. DROP DATABASE <database_name>

    Drop the database NOAA_water_database:

    1. > DROP DATABASE "NOAA_water_database"
    2. >

    A successful DROP DATABASE query returns an empty result.If you attempt to drop a database that does not exist, InfluxDB does not return an error.

    Drop series from the index with DROP SERIES

    The DROP SERIES query deletes all points from a series in a database,and it drops the series from the index.

    The query takes the following form, where you must specify either the FROM clause or the WHERE clause:

    1. DROP SERIES FROM <measurement_name[,measurement_name]> WHERE <tag_key>='<tag_value>'

    Drop all series from a single measurement:

    1. > DROP SERIES FROM "h2o_feet"

    Drop series with a specific tag pair from a single measurement:

    1. > DROP SERIES FROM "h2o_feet" WHERE "location" = 'santa_monica'

    Drop all points in the series that have a specific tag pair from all measurements in the database:

    The DELETE query deletes all points from a in a database.UnlikeDROP SERIES, it does not drop the series from the index and it supports time intervalsin the clause.

    The query takes the following form where you must include either the FROMclause or the WHERE clause, or both:

    1. DELETE FROM <measurement_name> WHERE [<tag_key>='<tag_value>'] | [<time interval>]

    Delete all data associated with the measurement h2o_feet:

    1. > DELETE FROM "h2o_feet"

    Delete all data associated with the measurement h2o_quality and where the tag randtag equals 3:

    1. > DELETE FROM "h2o_quality" WHERE "randtag" = '3'

    Delete all data in the database that occur before January 01, 2016:

    1. > DELETE WHERE time < '2016-01-01'

    A successful DELETE query returns an empty result.

    Things to note about DELETE:

    • DELETE supportsin the FROM clause when specifying measurement names and in the WHERE clausewhen specifying tag values.
    • DELETE does not support fields in the WHERE clause.

    Delete measurements with DROP MEASUREMENT

    The DROP MEASUREMENT query deletes all data and series from the specified measurement and deletes themeasurement from the index.

    The query takes the following form:

    1. DROP MEASUREMENT <measurement_name>

    Delete the measurement h2o_feet:

    1. > DROP MEASUREMENT "h2o_feet"

    A successful DROP MEASUREMENT query returns an empty result.

    Currently, InfluxDB does not support regular expressions with DROP MEASUREMENTS.See GitHub Issue for more information.

    Delete a shard with DROP SHARD

    The DROP SHARD query deletes a shard. It also drops the shard from the.The query takes the following form:

    1. DROP SHARD <shard_id_number>

    Delete the shard with the id 1:

    A successful DROP SHARD query returns an empty result.InfluxDB does not return an error if you attempt to drop a shard that does notexist.

    Retention policy management

    The following sections cover how to create, alter, and delete retention policies.Note that when you create a database, InfluxDB automatically creates a retention policy named autogen which has infinite retention.You may disable its auto-creation in the .

    Syntax

    1. CREATE RETENTION POLICY <retention_policy_name> ON <database_name> DURATION <duration> REPLICATION <n> [SHARD DURATION <duration>] [DEFAULT]

    Description of syntax

    DURATION
    • The DURATION clause determines how long InfluxDB keeps the data.The <duration> is a or INF (infinite).The minimum duration for a retention policy is one hour and the maximumduration is INF.
    REPLICATION

    • The REPLICATION clause determines how many independent copies of each pointare stored in the .

    • To ensure data is immediately available for queries, set the replication factor n to less than or equal to the number of data nodes in the cluster.

    • Replication factors do not serve a purpose with single node instances.
    SHARD DURATION
    • Optional. The SHARD DURATION clause determines the time range covered by a .
    • The <duration> is a duration literaland does not support an (infinite) duration.
    • By default, the shard group duration is determined by the retention policy’sDURATION:
    Retention Policy’s DURATIONShard Group Duration
    < 2 days1 hour
    >= 2 days and <= 6 months1 day
    > 6 months7 days

    The minimum allowable SHARD GROUP DURATION is 1h.If the CREATE RETENTION POLICY query attempts to set the SHARD GROUP DURATION to less than 1h and greater than 0s, InfluxDB automatically sets the SHARD GROUP DURATION to 1h.If the CREATE RETENTION POLICY query attempts to set the SHARD GROUP DURATION to 0s, InfluxDB automatically sets the SHARD GROUP DURATION according to the default settings listed above.

    Seefor recommended configurations.

    DEFAULT

    Sets the new retention policy as the default retention policy for the database.This setting is optional.

    Examples

    Create a retention policy
    1. > CREATE RETENTION POLICY "one_day_only" ON "NOAA_water_database" DURATION 1d REPLICATION 1
    2. >

    The query creates a retention policy called one_day_only for the databaseNOAA_water_database with a one day duration and a replication factor of one.

    Create a DEFAULT retention policy
    1. > CREATE RETENTION POLICY "one_day_only" ON "NOAA_water_database" DURATION 23h60m REPLICATION 1 DEFAULT
    2. >

    The query creates the same retention policy as the one in the example above, butsets it as the default retention policy for the database.

    A successful CREATE RETENTION POLICY query returns an empty response.If you attempt to create a retention policy identical to one that already exists, InfluxDB does not return an error.If you attempt to create a retention policy with the same name as an existing retention policy but with differing attributes, InfluxDB returns an error.

    Modify retention policies with ALTER RETENTION POLICY

    The ALTER RETENTION POLICY query takes the following form, where you must declare at least one of the retention policy attributes DURATION, REPLICATION, SHARD DURATION, or DEFAULT:

    1. ALTER RETENTION POLICY <retention_policy_name> ON <database_name> DURATION <duration> REPLICATION <n> SHARD DURATION <duration> DEFAULT

    Replication factors do not serve a purpose with single node instances.

    First, create the retention policy what_is_time with a DURATION of two days:

    1. > CREATE RETENTION POLICY "what_is_time" ON "NOAA_water_database" DURATION 2d REPLICATION 1
    2. >

    Modify what_is_time to have a three week DURATION, a two hour shard group duration, and make it the DEFAULT retention policy for NOAA_water_database.

    1. > ALTER RETENTION POLICY "what_is_time" ON "NOAA_water_database" DURATION 3w SHARD DURATION 2h DEFAULT

    In the last example, what_is_time retains its original replication factor of 1.

    A successful ALTER RETENTION POLICY query returns an empty result.

    Delete retention policies with DROP RETENTION POLICY

    Delete all measurements and data in a specific retention policy:

    Dropping a retention policy will permanently delete all measurements and data stored in the retention policy.

    1. DROP RETENTION POLICY <retention_policy_name> ON <database_name>

    Delete the retention policy what_is_time in the NOAA_water_database database: