Manage bucket schemas

Use explicit bucket schemas to enforce column names, tags, fields, and data types for your data. Explicit bucket schemas ensure that measurements have specific columns and data types and prevent non-conforming write requests.

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

• Create an explicit bucket and schema
  • Create a bucket schema using the influx CLI
  • Create a bucket schema using the InfluxDB HTTP API
  • Write valid schemas
• View bucket schema type and schemas
  • View schema type and schemas in the InfluxDB UI
  • View schema type and schemas using the influx CLI
  • View schema type and schemas using the InfluxDB HTTP API
• Update a bucket schema
  • Update a bucket schema using the influx CLI
  • Update a bucket schema using the InfluxDB HTTP API
• Troubleshoot bucket schema errors
• Troubleshoot write errors

Create an explicit bucket and schema

To create an explicit bucket and schemas for your data, do the following:

1. If you haven’t already, create a bucket that enforces explicit schemas.
2. Create a bucket schema.

Create a bucket schema

With an explicit bucket, you predefine measurement schemas with column names, tags, fields, and data types for measurements. A measurement schema has the following properties:

• name: the measurement name. The name must match the measurement column in your data, obey naming rules, and be unique within the bucket.
• columns: a list of column definitions for the measurement.

To learn more about rules for measurement names and columns, see how to write valid schemas.

Use the influx CLI or InfluxDB HTTP API to create an explicit bucket schema for a measurement.

• Create a bucket schema using the influx CLI
• Create a bucket schema using the InfluxDB HTTP API

Create a bucket schema using the influx CLI

1. Use your text editor to create a schema columns file for each measurement you want to add. 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,dataType
   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"}
   

2. Use the influx bucket-schema create command to define an explicit bucket measurement schema. In your command, specify values for the following flags:

   • --name: the measurement name.
   • --columns-file: the location of the file that contains column definitions for your measurement.

   For example, each of the following commands adds a unique measurement schema to 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     
   

Create a bucket schema using the InfluxDB HTTP API

Send a request to the HTTP API /api/v2/buckets/{BUCKET_ID}/schema/measurements endpoint and set the following properties in the request body:

• name: the measurement name.
• columns: an array of column definitions for your measurement.

For example, the following request defines the explicit bucket measurement schema for airSensors measurements:

POST https://cloud2.influxdata.com/api/v2/buckets/{BUCKET_ID}/schema/measurements

{
	"name": "airSensors",
	"columns": [
          {"name": "time", "type": "timestamp"},
          {"name": "sensorId", "type": "tag"},
          {"name": "temperature", "type": "field"},
          {"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"}
]

View bucket schema type and schemas

Use the InfluxDB UI, influx CLI, or InfluxDB HTTP API to view schema type and schemas for buckets.

View schema type and schemas in the InfluxDB UI

1. View buckets.
2. In the list of buckets, see the Schema Type in the metadata that follows each bucket name.
3. Buckets with Schema Type: Explicit display the Show Schema button. Click Show Schema to view measurement schemas for the bucket.

View schema type and schemas using the influx CLI

To list schemas for a bucket, use the influx bucket-schema list command. To view schema column definitions and metadata, specify the --json flag.

View schema type and schemas using the InfluxDB HTTP API

To list schemas for a bucket, send a request to the InfluxDB HTTP /api/v2/buckets/{BUCKET_ID}/schema/measurements endpoint:

GET https://cloud2.influxdata.com/api/v2/buckets/{BUCKET_ID}/schema/measurements

Update a bucket schema

Use the influx CLI or the InfluxDB HTTP API to add new columns to an explicit bucket schema. You can’t modify or delete columns in bucket schemas.

• Update a bucket schema using the influx CLI
• Update a bucket schema using the InfluxDB HTTP API

Update a bucket schema using the influx CLI

1. View the existing measurement schema and save the columns list to a file.

2. In your text editor or terminal, append new columns to the list from the previous step. 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. To update the bucket schema, use the influx bucket-schema update command and specify the columns file with the --columns-file flag.

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

Update a bucket schema using the InfluxDB HTTP API

1. View the existing measurement schema and copy the columns list.

2. Send a request to the HTTP API /api/v2/buckets/{BUCKET_ID}/schema/measurements/{MEASUREMENT_ID} endpoint.

   In the request body, set the columns property to a list of old and new column definitions for the measurement schema–for example, the following request appends the new column CO2 to columns retrieved in the previous step:

   PATCH https://cloud2.influxdata.com/api/v2/buckets/{BUCKET_ID}/schema/measurements/{MEASUREMENT_ID}

   {
     "columns": [
             {"name": "time", "type": "timestamp"},
             {"name": "sensorId", "type": "tag"},
             {"name": "temperature", "type": "field"},
             {"name": "humidity", "type": "field", "dataType": "float"},
             {"name": "CO2", "type": "field", "dataType": "float"}
       ]
   }
   

Troubleshoot bucket schema errors

Bucket not found

Creating and updating bucket schema requires WRITE permission for the bucket.
If your API token doesn’t have WRITE permission for the bucket, InfluxDB returns the following error:

Error: bucket "my_explicit_bucket" not found

Failed to create measurement

Each measurement in a bucket can have one schema. If you attempt to create a schema for an existing measurement, InfluxDB rejects the new schema and returns the following error:

Error: failed to create measurement: 422 Unprocessable Entity

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.