Annotated CSV

CSV tables must be encoded in UTF-8 and Unicode Normal Form C as defined in UAX15. InfluxDB removes carriage returns before newline characters.

In this topic, you’ll find examples of valid CSV syntax for responses to the following query:

CSV response format

Flux supports encodings listed below.

A table may have the following rows and columns.

Rows

  • Annotation rows: describe column properties.

  • Header row: defines column labels (one header row per table).

  • Record row: describes data in the table (one record per row).

Example

Encoding of a table with and without a header row.

Header row 

  1. result,table,_start,_stop,_time,region,host,_value
  2. my-result,0,2018-05-08T20:50:00Z,2018-05-08T20:51:00Z,2018-05-08T20:50:00Z,east,A,15.43
  3. my-result,0,2018-05-08T20:50:00Z,2018-05-08T20:51:00Z,2018-05-08T20:50:20Z,east,B,59.25
  4. my-result,0,2018-05-08T20:50:00Z,2018-05-08T20:51:00Z,2018-05-08T20:50:40Z,east,C,52.62
  1. my-result,0,2018-05-08T20:50:00Z,2018-05-08T20:51:00Z,2018-05-08T20:50:00Z,east,A,15.43
  2. my-result,0,2018-05-08T20:50:00Z,2018-05-08T20:51:00Z,2018-05-08T20:50:40Z,east,C,52.62

Columns

  • Annotation column: Only used in annotation rows. Always the first column. Displays the name of an annotation. Value can be empty or a supported . You’ll notice a space for this column for the entire length of the table, so rows appear to start with ,.

  • Result column: Contains the name of the result specified by the query.

  • Table column: Contains a unique ID for each table in a result.

If a file or data stream contains multiple tables or results, the following requirements must be met:

  • A table column indicates which table a row belongs to.
  • All rows in a table are contiguous.
  • An empty row delimits a new table boundary in the following cases:
    • Between tables in the same result that do not share a common table schema.
    • Between concatenated CSV files.
  • Each new table boundary starts with new annotation and header rows.
Example

Encoding of two tables in the same result with the same schema (header row) and different schema.

Different schema

  1. result,table,_start,_stop,_time,region,host,_value
  2. my-result,0,2018-05-08T20:50:00Z,2018-05-08T20:51:00Z,2018-05-08T20:50:00Z,east,A,15.43
  3. my-result,0,2018-05-08T20:50:00Z,2018-05-08T20:51:00Z,2018-05-08T20:50:20Z,east,B,59.25
  4. my-result,0,2018-05-08T20:50:00Z,2018-05-08T20:51:00Z,2018-05-08T20:50:40Z,east,C,52.62
  5. my-result,1,2018-05-08T20:50:00Z,2018-05-08T20:51:00Z,2018-05-08T20:50:00Z,west,A,62.73
  6. my-result,1,2018-05-08T20:50:00Z,2018-05-08T20:51:00Z,2018-05-08T20:50:20Z,west,B,12.83
  7. my-result,1,2018-05-08T20:50:00Z,2018-05-08T20:51:00Z,2018-05-08T20:50:40Z,west,C,51.62

Flux supports the following dialect options for text/csv format.

Annotations

Annotation rows describe column properties, and start with # (or commentPrefix value). The first column in an annotation row always contains the annotation name. Subsequent columns contain annotation values as shown in the table below.

To encode a table with its group key, the datatype, group, and annotations must be included. If a table has no rows, the default annotation provides the group key values.

Line protocol elements

Columns with data types (other than dateTime) in the #datatype annotation are treated as fields when converted to line protocol. Columns without a specified data type default to field when converted to line protocol and column values are left unmodified in line protocol. See an example and line protocol data types and format.

A column with time or dateTime #datatype annotations are used as the timestamp when converted to line protocol. If there are multiple time or dateTime columns, the last column (on the right) is used as the timestamp in line protocol. Other time columns are ignored and the influx write command outputs a warning.

Time column values should be Unix timestamps (in an ), RFC3339, or RFC3339Nano.

Example line protocol elements in datatype annotation
  1. #datatype measurement,tag,tag,field,field,ignored,time
  2. m,cpu,host,time_steal,usage_user,nothing,time
  3. cpu,cpu1,host1,0,2.7,a,1482669077000000000
  4. cpu,cpu1,host2,0,2.2,b,1482669087000000000

Resulting line protocol:

  1. cpu,cpu=cpu1,host=host1 time_steal=0,usage_user=2.7 1482669077000000000
  2. cpu,cpu=cpu1,host=host2 time_steal=0,usage_user=2.2 1482669087000000000
Example of mixing data types line protocol elements
  1. #datatype measurement,tag,string,double,boolean,long,unsignedLong,duration,dateTime
  2. #default test,annotatedDatatypes,,,,,,
  4. ,,str1,1.0,true,1,1,1ms,1
  5. ,,str2,2.0,false,2,2,2us,2020-01-11T10:10:10Z

Resulting line protocol:

Flux requires all annotation and header rows in annotated CSV. The example below illustrates how to use the csv.from() function to read annotated CSV in Flux:

  2. csvData = "#datatype,string,long,dateTime:RFC3339,dateTime:RFC3339,dateTime:RFC3339,string,string,double,string
  3. #group,false,false,true,true,false,true,false,false,true
  4. #default,,,,,,,,,
  5. ,result,table,_start,_stop,_time,region,host,_value,_measurement
  6. ,,0,2018-05-08T20:50:00Z,2018-05-08T20:51:00Z,2018-05-08T20:50:00Z,east,A,15.43,cpu
  7. ,,0,2018-05-08T20:50:00Z,2018-05-08T20:51:00Z,2018-05-08T20:50:20Z,east,B,59.25,cpu
  8. ,,0,2018-05-08T20:50:00Z,2018-05-08T20:51:00Z,2018-05-08T20:50:40Z,east,C,52.62,cpu
  9. ,,1,2018-05-08T20:50:00Z,2018-05-08T20:51:00Z,2018-05-08T20:50:00Z,west,A,62.73,cpu
  10. ,,1,2018-05-08T20:50:00Z,2018-05-08T20:51:00Z,2018-05-08T20:50:20Z,west,B,12.83,cpu
  11. ,,1,2018-05-08T20:50:00Z,2018-05-08T20:51:00Z,2018-05-08T20:50:40Z,west,C,51.62,cpu
  12. "
  14. csv.from(csv: csvData)

Flux only supports in the #datatype annotation. It does does not support line protocol elements.

Errors

If an error occurs during execution, a table returns with:

  • An error column that contains an error message.
  • A reference column with a unique reference code to identify more information about the error.
  • A second row with error properties.

If an error occurs:

  • Before results materialize, the HTTP status code indicates an error. Error details are encoded in the csv table.
  • After partial results are sent to the client, the error is encoded as the next table and remaining results are discarded. In this case, the HTTP status code remains 200 OK.
Example
  1. #datatype,string,long
  2. ,error,reference
  3. ,Failed to parse query,897

Encoding for an error that occurs after a valid table has been encoded:

  1. #datatype,string,long,dateTime:RFC3339,dateTime:RFC3339,dateTime:RFC3339,string,string,double
  2. ,result,table,_start,_stop,_time,region,host,_value
  3. ,my-result,1,2018-05-08T20:50:00Z,2018-05-08T20:51:00Z,2018-05-08T20:50:00Z,west,A,62.73
  4. ,my-result,1,2018-05-08T20:50:00Z,2018-05-08T20:51:00Z,2018-05-08T20:50:20Z,west,B,12.83
  5. ,my-result,1,2018-05-08T20:50:00Z,2018-05-08T20:51:00Z,2018-05-08T20:50:40Z,west,C,51.62