InfluxDB’s native data format is optimized for time-series storage and retrieval, not for the broad analytical tooling that typically consumes CSV or Parquet.
Here’s how you can export InfluxDB data and make it ready for your analytics workflow.
Let’s start with a practical example. Imagine you have a database named telegraf with a measurement cpu containing fields like usage_user and usage_system, and tags like host and region.
# Sample data creation (for demonstration)
influx -database telegraf -execute 'CREATE MEASUREMENT cpu WITH TAG SETTING host,region; INSERT cpu,host=server1,region=us-east usage_user=10,usage_system=5 1678886400000000000'
influx -database telegraf -execute 'INSERT cpu,host=server2,region=us-west usage_user=12,usage_system=6 1678886460000000000'
influx -database telegraf -execute 'INSERT cpu,host=server1,region=us-east usage_user=11,usage_system=5 1678886520000000000'
To export this data to CSV, you’ll use the influx CLI with the pretty output format disabled and pipe it to a file.
influx -database telegraf -execute 'SELECT * FROM cpu' --format csv > cpu_data.csv
This command queries all data from the cpu measurement in the telegraf database. The --format csv flag tells influx to output the results as comma-separated values. The output will look like this:
time,host,region,usage_user,usage_system
2023-03-15T13:20:00Z,server1,us-east,10,5
2023-03-15T13:21:00Z,server2,us-west,12,6
2023-03-15T13:22:00Z,server1,us-east,11,5
This CSV is directly consumable by most data analysis tools, including pandas, R, and spreadsheet applications.
Exporting to Parquet is a bit more involved because InfluxDB doesn’t have a native Parquet export. You’ll typically export to CSV first, then convert it using a tool that understands both formats. pandas in Python is an excellent choice for this.
First, ensure you have pandas installed:
pip install pandas pyarrow
( pyarrow is the necessary library for Parquet support in pandas)
Then, use a Python script to read the CSV and write it as Parquet:
import pandas as pd
# Read the CSV data
df = pd.read_csv('cpu_data.csv')
# Ensure the 'time' column is in datetime format, as it's often treated as a string
df['time'] = pd.to_datetime(df['time'])
# Write to Parquet
df.to_parquet('cpu_data.parquet', index=False)
Running this script will create cpu_data.parquet. Parquet is a columnar storage format, highly efficient for analytical queries, especially when dealing with large datasets. It supports schema evolution and compression, making it ideal for data warehousing and big data analytics.
The primary problem InfluxDB’s internal format presents for analytics is its structure. While it stores tags and fields efficiently, these are often exposed as a flattened, column-like structure in query results. A common pattern is that tags become columns, fields become columns, and the timestamp is also a column. This is precisely what the CSV and Parquet formats expect, so the export process is largely about transforming the InfluxDB query result into a tabular representation.
The SELECT * FROM measurement query is the most straightforward way to get all data. However, for analytics, you often need to select specific fields and tags, and potentially perform aggregations or transformations within the InfluxDB query itself before exporting. For example, to get only user CPU usage and the host for the last hour:
influx -database telegraf -execute 'SELECT time, host, usage_user FROM cpu WHERE time > now() - 1h' --format csv > recent_cpu_usage.csv
This pre-filtering and selection reduces the data volume before it hits your analytics tools, which is crucial for performance.
When you export data from InfluxDB using SELECT *, the time column is typically exported in ISO 8601 format (e.g., 2023-03-15T13:20:00Z). While this is human-readable, many analytical tools and libraries (like pandas) prefer dedicated datetime objects. It’s important to explicitly convert this column to a datetime type in your analytics tool after importing, as shown in the Python example. Failing to do so can lead to incorrect temporal operations.
The next hurdle you’ll likely encounter is handling data with different tag sets across records, which can complicate schema definition in analytical systems.