How to Write Points from CSV to InfluxDB v2 and InfluxDB Cloud

"
_time,region,host,usage_user,_measurement
2018-05-08T20:50:00Z,east,A,15.43,cpu
2018-05-08T20:50:20Z,east,A,59.25,cpu
2018-05-08T20:50:40Z,east,A,52.62,cpu
"

Using the Telegraf File Plugin to write CSV to InfluxDB Cloud

  • The -sample-config flag will generate the telegraf config file.
  • The -input-filter and -output-filter flags specify the input and output sources of the data, respectively.
  • The text following the > names the config file.
[[inputs.file]]
## Files to parse each interval. Accept standard unix glob matching rules,
## as well as ** to match recursive files and directories.
files = ["/path/to/writeCSV.csv"]
## Data format to consume.
## Each data format has its own unique set of configuration options, read
## more about them here:
## https://github.com/influxdata/telegraf/blob/master/docs/DATA_FORMATS_INPUT.md
## Data format to consume.
data_format = "csv"
## Indicates how many rows to treat as a header. By default, the parser assumes
## there is no header and will parse the first row as data. If set to anything more
## than 1, column names will be concatenated with the name listed in the next header row.
## If `csv_column_names` is specified, the column names in header will be overridden.
csv_header_row_count = 1
## Indicates the number of rows to skip before looking for header information.
csv_skip_rows = 1
## Indicates the number of columns to skip before looking for data to parse.
## These columns will be skipped in the header as well.
csv_skip_columns = 1

## The character reserved for marking a row as a comment row
## Commented rows are skipped and not parsed
csv_comment = "#"
## The column to extract the name of the metric from
csv_measurement_column = "_measurement"
## Columns listed here will be added as tags. Any other columns
## will be added as fields.
csv_tag_columns = ["host","region"]
## The column to extract time information for the metric
## `csv_timestamp_format` must be specified if this is used
csv_timestamp_column = "_time"
## The format of time data extracted from `csv_timestamp_column`
## this must be specified if `csv_timestamp_column` is specified
csv_timestamp_format = "2006-01-02T15:04:05Z07:00"
  • Bucket. A bucket is where data is stored. You can think of it as a database. All buckets have retention policies. You assign a bucket a retention policy upon bucket creation. The easiest way to create a bucket is through the InfluxDB Cloud user interface (UI), as described here. However, you can also use the CLI or API to create a bucket. This bucket will be referred to as our destination bucket, “mybucket”.
  • Token: An InfluxDB authentication token ensures security for users and their data. Tokens belong to organizations and describe permissions. You’ll need to create a read/write or all-access token scoped to your destination bucket. The easiest way to create a bucket is through the InfluxDB Cloud UI, as described here. However, you can also use the CLI or API to create a token.
  • Organization: Your organization will be the email you used to set up your InfluxDB Cloud account.
# Configuration for sending metrics to InfluxDB 2.0
[[outputs.influxdb_v2]]
## The URLs of the InfluxDB cluster nodes.
##
## Multiple URLs can be specified for a single cluster, only ONE of the
## urls will be written to each interval.
## ex: urls = ["http://127.0.0.1:8086"] if your using OSS
urls = ["https://us-west-2-1.aws.cloud2.influxdata.com"]

## Token for authentication.
token = "mytoken"

## Organization is the name of the organization you wish to write to.
organization = "myemail@email.com"

## Destination bucket to write into.
bucket = "mybucket"

Using the CLI to write CSV to InfluxDB Cloud

influx write -b mybucket\
-t mytoken
-o myemail@email.com
-f path/to/writeCSV.csv \
--header "#datatype dateTime:2006-01-02T15:04:05Z07:00,tag,tag,double,measurement”
influx write dryrun  -b mybucket -f ~/path/to/writeCSV.csv --header "#datatype dateTime:2006-01-02T15:04:05Z07:00,tag,tag,double,measurement"
cpu,host=A,region=east usage_user=15.43 1525812600000000000
cpu,host=A,region=east usage_user=59.25 1525812620000000000
cpu,host=A,region=east usage_user=52.62 1525812640000000000

Using the csv.from() Flux function to write CSV to InfluxDB Cloud

import "csv"
csvData = "
_time,region,host,usage_user,_measurement
2018-05-08T20:50:00Z,east,A,15.43,cpu
2018-05-08T20:50:20Z,east,A,59.25,cpu
2018-05-08T20:50:40Z,east,A,52.62,cpu
"
csv.from(csv: csvData, mode: "raw")
|> rename(columns: {usage_user: "_value"})
|> map(fn: (r) => ({ r with _value: float(v: r._value) }))
|> map(fn: (r) => ({ r with _time: time(v: r._time) }))
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> to(bucket: "mybucket")
  • Store your CSV data in a variable.
  • Specify the path to your CSV. Please note that the CSV must exist in the same file system running the fluxd process. In other words, this approach doesn’t work in the InfluxDB UI for InfluxDB Cloud.
  • Then we use the rename() function to rename the usage_user column to “_value”. This column is renamed because the InfluxDB UI visualizes columns with the name “_value” by default.
  • The csv.from() function will convert our data to strings by default.
  • The map() function applies the time() function to each record in the “_time” column.
  • The time() function converts the string value to a time.

Using the Python client to write CSV to InfluxDB Cloud

import pandas as pd
from influxdb_client import InfluxDBClient

client = InfluxDBClient(url="https://us-west-2-1.aws.cloud2.influxdata.com", token="mytoken", org="myorg")
write_client = client.write_api()

df = pd.read_csv("~/Desktop/writeCSV.csv")
df['_time'] = pd.to_datetime(df['_time'], format="%Y-%m-%dT%H:%M:%SZ")
df.set_index(['_time'])

write_client.write("mybucket", "anais@influxdata.com", record=df, data_frame_measurement_name="_measurement",
data_frame_tag_columns=['region', 'host'])

Conclusions about writing CSV to InfluxDB v2 Cloud and OSS

Further reading

  1. TL;DR InfluxDB Tech Tips — How to Use the CLI Locally to Access the Flux REPL and Write a Regular CSV to InfluxDB Cloud: This post shares how to use the InfluxDB CLI with InfluxDB Cloud to write a CSV to InfluxDB.
  2. TL;DR InfluxDB Tech Tips — How to Interpret an Annotated CSV: This post describes how to interpret an Annotated CSV, the Flux query result format for InfluxDB.
  3. Getting Started with Python and InfluxDB v2.0: This post describes how to get started with the Python Client Library.
  4. Getting Started with InfluxDB and Pandas: This post shares how to get started with Pandas to write dataframes to InfluxDB and return dataframes with a Flux query.
  5. Top 5 Hurdles for Flux Beginners and Resources for Learning to Use Flux: This post describes common hurdles for Flux beginners and how to tackle them by using the InfluxDB UI, understanding Annotated CSV, and more.
  6. Top 5 Hurdles for Intermediate Flux Users and Resources for Optimizing Flux: This post describes common hurdles for intermediate and advanced Flux users while providing more detail on pushdown patterns, how the Flux engine works, and more.

--

--

--

Developer Advocate at InfluxData

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Improve Your Data Wrangling With Object Oriented Programming

Why I started coding?

Integrate Cloud Applications with webMethods

PVS-Studio for Visual Studio

Resurrection of a Blog

Wakefit Engineering

How Recursion Works : a step-by-step walkthrough of this useful programming technique

Why AWS Landing Zone?

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Anais Dotis

Anais Dotis

Developer Advocate at InfluxData

More from Medium

Quick transcriptomics: Expression quantification and basic concepts — Part 1

How to create a Radar Chart on Google Sheets in 3 screenshots

Drawing on Paper #1

Link Multiple Files in Excel