InfluxQL Cheatsheet: Mastering Time-Series Data Querying

1. Connection and Basic Setup

InfluxDB CLI Connection

Bash

1
2
3
4
5
6
# Connect to InfluxDB CLI
influx -host localhost -port 8086 -username admin -password secret

# Select a database
USE my_database

HTTP API Connection

Bash

1
2
3
4
5
# Basic HTTP API query
curl -GET 'http://localhost:8086/query' \
     --data-urlencode "db=my_database" \
     --data-urlencode "q=SELECT * FROM measurement LIMIT 10"

Popular InfluxDB Clients

Python: influxdb-python
Go: influxdata/influxdb
Node.js: node-influx
Java: influxdb-java

2. Time-Series Database Concepts

Key Components

Measurement: Similar to a table (e.g., cpu_usage, temperature)
Tags: Indexed metadata (e.g., host, region) - used for filtering
Fields: Non-indexed data values (e.g., value, temperature)
Timestamp: Nanosecond-precision time of data point

Example Data Model

cpu_usage,host=server01,region=us-west value=65.4 1600000000000000
          ^tags         ^fields       ^timestamp

3. Basic InfluxQL Syntax

Query Structure

SQL

1
2
3
4
5
6
7
SELECT <fields>
FROM <measurement>
WHERE <condition>
GROUP BY <tags>
ORDER BY <time>
LIMIT <number>

4. Data Insertion (Line Protocol)

Line Protocol Format

<measurement>,<tag_key>=<tag_value> <field_key>=<field_value> <timestamp>

Example Insertions

SQL

1
2
3
4
5
6
7
# Write via CLI
INSERT cpu_usage,host=server01 value=65.4 1600000000000000

# Multiple points
INSERT temperature,location=kitchen temp=22.5 1600000001000000
INSERT temperature,location=bedroom temp=20.3 1600000002000000

5. SELECT Queries with Time-Based Filtering

Time Range Queries

SQL

1
2
3
4
5
6
7
8
9
# Last hour of data
SELECT * FROM cpu_usage 
WHERE time > NOW() - 1h

# Specific time range
SELECT * FROM temperature 
WHERE time >= '2023-01-01T00:00:00Z' 
  AND time <= '2023-01-02T00:00:00Z'

6. Aggregation Functions

Common Aggregations

SQL

1
2
3
4
5
6
7
8
9
10
# Mean value
SELECT MEAN(value) FROM cpu_usage

# Multiple aggregations
SELECT 
  MEAN(value) AS avg_cpu,
  MAX(value) AS peak_cpu,
  COUNT(value) AS total_measurements
FROM cpu_usage

7. Time-Based Grouping

Aggregation by Time Intervals

SQL

1
2
3
4
5
6
7
8
9
10
11
12
# Hourly aggregation
SELECT 
  MEAN(value) AS avg_temp
FROM temperature
GROUP BY time(1h)

# Custom time bucket with fill
SELECT 
  MEAN(value) AS avg_temp
FROM temperature
GROUP BY time(30m) FILL(previous)

8. Continuous Queries

Auto-Aggregation

SQL

1
2
3
4
5
6
7
8
9
10
# Create a continuous query
CREATE CONTINUOUS QUERY cq_1h_mean 
ON my_database
BEGIN
  SELECT MEAN(value) AS mean_value 
  INTO "aggregated_metrics"
  FROM "cpu_usage" 
  GROUP BY time(1h), host
END

9. Retention Policies

Managing Data Retention

SQL

1
2
3
4
5
6
7
8
9
10
# Create retention policy
CREATE RETENTION POLICY "two_weeks" 
ON "my_database" 
DURATION 2w REPLICATION 1 DEFAULT

# Modify retention policy
ALTER RETENTION POLICY "two_weeks" 
ON "my_database" 
DURATION 4w

10. Mathematical and String Functions

Mathematical Operations

SQL

1
2
3
4
5
6
7
8
9
10
11
12
# Calculations
SELECT 
  value * 1.5 AS adjusted_value,
  SQRT(value) AS root_value
FROM metrics

# String functions
SELECT 
  LOWER(host) AS lowercase_host,
  CONCAT(host, '-', region) AS host_region
FROM cpu_usage

11. Subqueries and Nested Queries

Complex Querying

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# Subquery example
SELECT MEAN(value) 
FROM (
  SELECT value 
  FROM cpu_usage 
  WHERE host = 'server01'
)

# Nested time-based selection
SELECT * 
FROM (
  SELECT MEAN(value) AS avg_value 
  FROM cpu_usage 
  GROUP BY time(1h)
) 
WHERE avg_value > 70

12. Schema Exploration

Metadata Queries

SQL

1
2
3
4
5
6
7
8
9
# List measurements
SHOW MEASUREMENTS

# Show tag keys
SHOW TAG KEYS FROM cpu_usage

# Show field keys
SHOW FIELD KEYS FROM temperature

13. Performance Optimization

Query Performance Tips

Use time range filters
Limit result sets
Avoid SELECT * on large datasets
Use tags for efficient filtering
Create appropriate indexes

Example Optimized Query

SQL

1
2
3
4
5
6
SELECT MEAN(value) 
FROM cpu_usage 
WHERE host = 'server01' 
  AND time > NOW() - 1d 
LIMIT 1000

14. IoT and Monitoring Patterns

Common Use Cases

Device health monitoring
Environmental sensing
Industrial IoT metrics
Network performance tracking

Sample IoT Query

SQL

1
2
3
4
5
6
7
SELECT 
  MEAN(temperature) AS avg_temp,
  MAX(humidity) AS peak_humidity
FROM sensor_data
WHERE device_type = 'environmental'
GROUP BY time(1h), device_id

15. Visualization Integration

Grafana Configuration

Add InfluxDB as a data source
Use $measurement and $tag_* variables
Create dynamic dashboards

Chronograf Dashboards

Built-in InfluxDB visualization
Create alerts and kapacitor rules
Real-time metric exploration

Pro Tips

Always use time-based filtering
Understand your data retention needs
Monitor query performance
Use continuous queries for pre-aggregation
Leverage tags for efficient querying

Resources

Official InfluxDB Documentation
InfluxData Community Forums
GitHub: InfluxDB Open Source

InfluxQL

Continue Learning