TL;DR Tech Tips — How to Construct a Table with Flux

By Anais Dotis-Georgiou / Product, Use Cases, Developer
Aug 18, 2020

Navigate to:

In this post, we share how to use the array.from() function to construct a table from an array of records with Flux. Flux is InfluxData’s scripting and functional query language.

This TL;DR assumes that you have either registered for a InfluxDB Cloud account – registering for a free account is the easiest way to get started with InfluxDB – or installed InfluxDB 2.0 OSS.

Q: How can I construct and write data to InfluxDB? A: Users have the following options for construction and writing temporary data to InfluxDB:

  • Generate line protocol and write it manually to InfluxDB through the UI. Writing line protocol data to InfluxDB through the UI is easy, if you don't need to include a specific timestamp. If a data point does not include a timestamp when it is received by the database, InfluxDB uses the current system time (UTC) of its host machine. However, if you need to add data with timestamps, you need to include Unix timestamps based on epoch time. This makes generating line protocol data a little difficult – you'll most likely rely on a timestamp converter.
  • Copy and edit an Annotated CSV and use the csv.from() function to write the data to a bucket. This approach isn't recommended because editing a CSV can be  difficult and invites human error. However, using the CSV export UI functionality in conjunction with the csv.from() is a great way to share data with colleagues and community. Community members with Flux questions frequently share their Annotated CSV data with me. Then I'm able to use the csv.from() function to help answer their Flux questions.
  • Use the influx write  command to write any CSV to InfluxDB. This is a great tool, but it assumes that you already have the data in a  CSV format. The influx write command is great for writing large volumes of existing CSV data to InfluxDB.
  • We've recently introduced a new method, the array.from() function is a Flux function that allows you to construct tables on the fly.

Q: What is the array.from() function? A: The array.from() function is a Flux function that allows you to construct tables from an array of objects. The array.from() function was created in response to community feedback. It addresses the limitations and hurdles associated with some of the alternative approaches by:

  • Allowing you to write data with timestamps in a human readable RFC3339 timestamp format (as opposed to epoch time).
  • Reliably construct data through a list of objects rather than manually editing an annotated CSV.
  • Saving you time. You don't need to collect authentication parameters to use array.from() like you do with influx write.

If you’re curious to learn more about the context, please take a look at this issue #3077.  If you have any other product feedback, I encourage you to create issues or share them in the community forum.

Q: How do I construct a table with array.from()? A: Navigate to the Flux Script Editor in the Explore tab. Search for array.from() and click the “Inject” button.

flux array.from function

Selecting “Inject” will automatically inject the corresponding Flux and Flux package. The default example is:

import "experimental/array"
 
|> array.from(rows: [{_time: 2020-01-01T00:00:00Z, _value: "foo"},{_time: 2020-01-02T00:00:00Z, _value: "bar"}])

By default, the UI injects a pipe-forward operator for all function injections. However, we don’t need the pipe-forward operator because we aren’t chaining operations together. If you press “Submit” as is, you would get the following error:

compilation failed: error at @3:3-3:5: invalid statement: |>

Please remove the pipe-forward operator in order to generate a table. The array.from() function uses the following keys to construct columns in your table:

  • _measurement: this key corresponds to the measurement column; the value must be a string.
  • _field: this key corresponds to the field column; the value must be string.
  • _value: this key corresponds to the field value column; the value can be a string, float, or integer.
  • _time: this key corresponds to the timestamp column; the value is in the RFC3339 timestamp format.
  • Tag set column can be given any key name; the value must be a string.

Here’s an example with additional keys:

import "experimental/array"
array.from(rows: [{_measurement: "m0", mytag: "t0", _field: "f0", _value: "foo", _time: 2020-01-01T00:00:00Z,},
{_measurement: "m0", mytag: "t0", _field: "f0", _value: "bar", _time: 2020-01-02T00:00:00Z}])

This Flux script yields the following result:

construct table flux

Here is the same result visualized as a table with the Table visualization:

Flux table visualization

Use the to() function to write your constructed table to a bucket.

Important note: The array.from() function writes all data to one table. If you write objects with different tags, measurements, and fields, all the data will still be in one table as though group() has been applied to your data. 

Q: How is array.from() useful? When might I use array.from()? A: The array.from() function can be used in a variety of instances. You might want to:

I hope you find this Tech Tip useful. If you have any questions or product feedback, please post them on the community site, Slack channel, or tweet us @InfluxDB. Thanks!