Manage bucket schemas

Use explicit bucket schemas to enforce column names, tags, fields, and data types for your data.

By default, buckets have an implicit schema-type and a schema that conforms to your data. To require data to have specific columns and data types and prevent non-conforming write requests, create a bucket schema.

After you create a bucket schema, you’re ready to write data to your bucket.

• Create a bucket schema
• Update a bucket schema
• Troubleshoot write errors

Create a bucket schema

Use the influx CLI to set the schema-type and measurement schemas for your bucket:

1. Create a bucket with the schema-type flag set to explicit.

   influx bucket create \
     --name my_schema_bucket \
     --schema-type explicit
   

2. Use your text editor to create a schema columns file for each measurement you want to add. The file defines the column names, tags, fields, and data types to require for a measurement.

   Format the file as CSV, JSON, or Newline delimited JSON (NDJSON), as in the following examples:

   usage-resources.csv usage-cpu.json sensor.ndjson

   name,type,data_type
   time,timestamp,
   host,tag,
   service,tag,
   fsRead,field,float
   fsWrite,field,float
   

   [
   {"name": "time", "type": "timestamp"},
   {"name": "service", "type": "tag"},
   {"name": "host", "type": "tag"},
   {"name": "usage_user", "type": "field", "dataType": "float"},
   {"name": "usage_system", "type": "field", "dataType": "float"}
   ]
   

   {"name": "time", "type": "timestamp"}
   {"name": "service", "type": "tag"}
   {"name": "sensor", "type": "tag"}
   {"name": "temperature", "type": "field", "dataType": "float"}
   {"name": "humidity", "type": "field", "dataType": "float"}
   

   Write valid schemas

   To ensure your schema is valid, review InfluxDB data elements. Follow these rules when creating your schema columns file:

   1. Use valid measurement and column names that:

      • Are unique within the schema
      • Are 1 to 128 characters long
      • Contain only Unicode characters
      • Don’t start with underscore _
      • Don’t start with a number 0-9
      • Don’t contain single quote ' or double quote "

   2. Include a column with the timestamp type.

   3. Include at least one column with the field type (without a field, there is no time-series data), as in the following example:

      Valid: a schema with timestamp and field columns.

      [
       {"name":"time","type":"timestamp"},
       {"name":"fsWrite","type":"field"}
      ]
      

      Not valid: a schema without a field column.

      [
       {"name":"time","type":"timestamp"},
       {"name":"host","type":"tag"}
      ]
      

   The default field data type is string. To set the data type of a field column, provide the dataType property and a valid field data type (string, float, integer, or boolean), as in the following example:

   [
    {"name":"time","type":"timestamp"},
    {"name":"fsWrite","type":"field","dataType":"float"}
   ]
   

3. Use the influx bucket-schema create command to add the schema for each measurement to your bucket.

   Provide the following:

   • location of your file with the columns-file flag
   • measurement name with the name flag. The name must match the measurement column in your data, obey naming rules, and be unique within the bucket.

   influx bucket-schema create \
    --bucket my_explicit_bucket \
    --name usage_resources \
    --columns-file usage-resources.csv
   
   influx bucket-schema create \
    --bucket my_explicit_bucket \
    --name usage_cpu \
    --columns-file usage-cpu.json
   
   influx bucket-schema create \
    --bucket my_explicit_bucket \
    --name sensor \
    --columns-file sensor.ndjson     
   

   Troubleshoot create errors

   Failed to create measurement

   If you attempt to create a schema for an existing measurement name, InfluxDB rejects the new schema and returns the following error:

   Error: failed to create measurement: 422 Unprocessable Entity
   

Update a bucket schema

Use the influx bucket-schema update command to add new columns to a schema. You cannot modify or delete columns in bucket schemas.

1. View explicit bucket schemas. Use the extended-output flag with the influx bucket list command to view column details.

   influx bucket-schema list \
     --bucket my_explicit_bucket \
     --extended-output
   

   InfluxDB returns column details:

   ID                      Measurement Name        Column Name     Column Type     Column Data Type   Bucket ID
   07c62z721z2ca000        sensor                  time            timestamp                          a7d5558b880a95da
   07c62z721z2ca000        sensor                  service         tag                                a7d5558b880a95da
   07c62z721z2ca000        sensor                  sensor          tag                                a7d5558b880a95da
   07c62z721z2ca000        sensor                  temperature     field           float              a7d5558b880a95da
   07c62z721z2ca000        sensor                  humidity        field           float              a7d5558b880a95da
   

2. In your text editor or terminal, create or edit the schema columns file and append new columns. The following example appends column CO2 to the original sensor.ndjson schema file:

   # sensor.ndjson
   {"name": "time", "type": "timestamp"}
   {"name": "service", "type": "tag"}
   {"name": "sensor", "type": "tag"}
   {"name": "temperature", "type": "field", "dataType": "float"}
   {"name": "humidity", "type": "field", "dataType": "float"}
   
   

   echo '{"name": "CO2", "type": "field", "dataType": "float"}' >> sensor.ndjson
   

3. Use the influx bucket-schema update command to add the new columns to the bucket schema.

   influx bucket-schema update \
     --bucket my_explicit_bucket \
     --name sensor \
     --columns-file sensor.ndjson
   

Troubleshoot write errors

InfluxDB returns an error for the following reasons:

• data in the write request doesn’t conform to a defined schema.
• data in the write request doesn’t have a schema defined for the bucket.
• data in the write request has invalid syntax.

To resolve failures and partial writes, see how to troubleshoot writes.