INFLUXQL & TICKSCRIPT

© 2017 InfluxData. All rights reserved.1
Agenda: New Practitioners Track
WORKSHOPAGENDA
8:30 AM – 9:00 AM Coffee & Pastries
9:00 AM – 9:15 AM Welcome
9:15 AM – 10:15 AM Introduction to the TICK Stack Katy Farmer
10:15 AM – 10:30 AM Break
10:30 AM – 11:30 AM Optimizing the TICK Stack Sam Dillard
11:30 AM – 11:45 AM Break
11:45 AM – 12:45 PM Chronograf and Dashboarding Russ Savage
12:45 PM – 1:45 PM Lunch
1:45 PM – 2:45 PM InfluxEnterprise Architectural Patterns Craig Hobbs
2:45 PM – 3:00 PM Break
3:00 PM – 4:00 PM InfluxQL & TICKscript Michael DeSa
4:00 PM – 4:15PM Closing

Michael Desa
Software Engineer,
InfluxData
InfluxQL & TICKscript
Michael Desa is a Software Engineer at InfluxData who
works on the 2.0 API for the InfluxData Platform. He has
led the InfluxDB training course across the US, providing
students with an in depth understanding of how InfluxDB
works as well as sharing best practices. He has a degree
in Math from the University of California, at Berkeley.

✓ What is InfluxQL
• What types of questions can I ask
about my data with InfluxQL
✓ What is TICKscript
• What types of questions can I solve
with TICKscript
✓ What is Flux
• Why does it matter
Agenda

InfluxQL

What is InfluxQL?
● A SQL-like query language for InfluxDB
○ Extends SQL to express queries that range over time
○ Limited support for relational features of SQL
● Easy to use
○ Should feel familiar to anyone with SQL experience
■ Not SQL compliant (which can bother those that want full access to SQL)
● Data definition and meta queries
○ Used to define and view database structures

Basic Select Statement
SELECT <field> FROM <measurement>
SELECT * FROM cpu
SELECT free FROM mem
SELECT x + y FROM vars
SELECT x,y FROM nums

Basic Select Statement
> SELECT * FROM h2o_quality LIMIT 10
name: h2o_quality
-----------------
time index location id
2015-08-18T00:00:00Z 41 coyote_creek 1
2015-08-18T00:00:00Z 99 santa_monica 2
2015-08-18T00:00:00Z 41 coyote_creek 1
2015-08-18T00:06:00Z 56 santa_monica 2
2015-08-18T00:06:00Z 11 coyote_creek 3
2015-08-18T00:06:00Z 11 coyote_creek 3
2015-08-18T00:12:00Z 65 santa_monica 3
2015-08-18T00:12:00Z 38 coyote_creek 1
2015-08-18T00:12:00Z 38 coyote_creek 1
2015-08-18T00:18:00Z 57 santa_monica 3

Select Statement with
WHERE clause
SELECT <field> FROM <measurement> WHERE <conditions>
SELECT * FROM cpu WHERE busy > 50
SELECT free FROM mem WHERE host = 'server1'
SELECT x + y FROM vars WHERE some_tag = 'some_key'
SELECT x,y FROM nums WHERE domain =~ /.*/

Relative Time
SELECT <field> FROM <measurement> WHERE <time>
SELECT * FROM cpu WHERE time > now() - 1h
SELECT * FROM cpu WHERE time > now() - 10s
SELECT free FROM mem WHERE time > now() - 4d
SELECT x + y FROM vars WHERE time > now() - 10w
SELECT x,y FROM nums WHERE time > now() + 15m

GROUP BY clause
[SELECT STATEMENT] GROUP BY <tag>
SELECT * FROM cpu GROUP BY host
SELECT * FROM cpu GROUP BY *
SELECT free FROM mem GROUP BY location, host

GROUP BY clause
> SELECT * FROM h2o_quality GROUP BY location
name: h2o_quality
tags: location = coyote_creek
time index id
---- ----- ---
2015-08-18T00:00:00Z 41 1
2015-08-18T00:00:00Z 41 1
name: h2o_quality
tags: location = santa_monica
time index id
---- ----- ---
2015-08-18T00:00:00Z 99 2
2015-08-18T00:06:00Z 56 2

Select Statement with a
function
SELECT <function>(<field>) FROM <measurement>
SELECT count(value) FROM cpu
SELECT mean(free) FROM mem WHERE time > now() - 1h
SELECT sum(x) FROM vars WHERE x > 100
SELECT median(y) FROM nums WHERE domain = 'Z'

function
> SELECT count(index) FROM h2o_quality
WHERE location = 'coyote_creek'
name: h2o_quality
-----------------
time count
1970-01-01T00:00:00Z 12777

function
> SELECT max(usage_user) FROM cpu
WHERE time > now() - 10d
name: cpu
time max
---- ---
2018-11-05T22:12:05Z 54

Types of
Functions
● Aggregators
○ count
○ distinct
○ integral
○ mean
○ median
○ spread
○ sum
○ stddev
● Selectors
○ bottom
○ first
○ last
○ max
○ min
○ percentile
○ top
● Transformers
○ derivative
○ difference
○ moving_average
○ elapsed

GROUP BY time clause
[SELECT STATEMENT] WHERE <time condition>
GROUP BY time(<period>)
SELECT max(busy) FROM cpu WHERE time > now() - 1h
GROUP BY time(10m)
SELECT mean(free) FROM free WHERE time > now() - 1d
GROUP BY time(1h)

Invalid queries
SELECT busy FROM cpu WHERE time > now() - 1h
GROUP BY time(10m)
SELECT mean(busy) FROM cpu GROUP BY time(10m)

> SELECT mean(degrees) FROM average_temperature
WHERE time < '2015-09-19'
AND time > '2015-09-18'
GROUP BY time(12h)
name: average_temperature
-------------------------
time mean
2015-09-18T00:00:00Z 79.83613445378151
2015-09-18T12:00:00Z 79.65034965034965

GROUP BY time and
tag
WHERE time < '2015-09-19'
AND time > '2015-09-18'
GROUP BY time(12h), location
-------------------------
time mean
2015-09-18T00:00:00Z 79.83613445378151
2015-09-18T12:00:00Z 79.65034965034965
-------------------------
time mean
2015-09-18T00:00:00Z
2015-09-18T12:00:00Z 79.95033445378151

Select with fill
WHERE time < '2015-09-19'
AND time > '2015-09-18'
GROUP BY time(12h), location fill(<fill>)
-------------------------
time mean
2015-09-18T00:00:00Z 79.83613445378151
2015-09-18T12:00:00Z 79.65034965034965
-------------------------
time mean
2015-09-18T00:00:00Z <fill>
2015-09-18T12:00:00Z 79.95033445378151

Select with fill 10
WHERE time < '2015-09-19'
AND time > '2015-09-18'
GROUP BY time(12h), location fill(10)
-------------------------
time mean
2015-09-18T00:00:00Z 79.83613445378151
2015-09-18T12:00:00Z 79.65034965034965
-------------------------
time mean
2015-09-18T00:00:00Z 10
2015-09-18T12:00:00Z 79.95033445378151

Select with fill next
WHERE time < '2015-09-19'
AND time > '2015-09-18'
GROUP BY time(12h), location fill(next)
-------------------------
time mean
2015-09-18T00:00:00Z 79.83613445378151
2015-09-18T12:00:00Z 79.65034965034965
-------------------------
time mean
2015-09-18T00:00:00Z 79.95033445378151
2015-09-18T12:00:00Z 79.95033445378151

Select with fill none
WHERE time < '2015-09-19'
AND time > '2015-09-18'
GROUP BY time(12h), location fill(none)
-------------------------
time mean
2015-09-18T00:00:00Z 79.83613445378151
2015-09-18T12:00:00Z 79.65034965034965
-------------------------
time mean
2015-09-18T12:00:00Z 79.95033445378151

Select with fill null
WHERE time < '2015-09-19'
AND time > '2015-09-18'
GROUP BY time(12h), location fill(null)
-------------------------
time mean
2015-09-18T00:00:00Z 79.83613445378151
2015-09-18T12:00:00Z 79.65034965034965
-------------------------
time mean
2015-09-18T00:00:00Z
2015-09-18T12:00:00Z 79.95033445378151

Sub-query
SELECT … FROM (
SELECT …
FROM …
WHERE …
GROUP BY …
)WHERE …
GROUP BY …

Sub-query - Having
SELECT mean
FROM (SELECT mean(usage_user) FROM cpu
WHERE time > now() - 10m
GROUP BY time(1m))
WHERE mean > 10

Sub-query - Counting
the distinct tag values
SELECT distinct(count(host))
FROM (SELECT usage_user, host FROM cpu
WHERE time > now() - 10m)

Question
What happens?
WHERE time > now() - 1d GROUP BY time(1d)

Answer
WHERE time > now() - 1d GROUP BY time(1d)
name: cpu
time max
---- ---
2018-11-07T00:00:00Z 37.37373737373738
2018-11-08T00:00:00Z 72

How to get
one value back
GROUP BY time(1d,now())
name: cpu
time max
---- ---
2018-11-07T15:50:38.560319Z 72
2018-11-08T15:50:38.560319Z

I only want one!!
GROUP BY time(1d,now()) fill(none)
name: cpu
time max
---- ---
2018-11-07T15:51:37.466919Z 72

Question
What happens?
// No data in the time range
> SELECT count(usage_user) FROM cpu
WHERE time > now() - 10s

Answer
> SELECT count(usage_user) FROM cpu
…
😞

Question
Write a query that
computes the average
of the usage_user and
usage_system fields
grouped by host in 20
second intervals
cpu,host=A usage_user=10,usage_system=70 10s
cpu,host=B usage_user=14,usage_system=40 10s
cpu,host=C usage_user=10,usage_system=12 10s

Answer
SELECT mean(usage_user), mean(usage_system)
FROM cpu
GROUP BY time(20s), host

Answer
SELECT mean(*)
FROM cpu
GROUP BY time(20s), host

Question
Write a query that
computes the mean of
free field for memory
and the mean of the
usage_user field for
cpu in 20s windows
cpu,host=A usage_user=10 10s
mem,host=A free=10 10s

Answer
SELECT mean(usage_user)
FROM cpu
GROUP BY time(20s);
SELECT mean(free)
FROM mem
GROUP BY time(20s)

Question
Write a query that
computes total rate of
change across all of
the counters grouped
by path
http_req,host=A,path=/ counter=10 10s
http_req,host=A,path=/home counter=20 10s
http_req,host=B,path=/admin counter=24 10s
http_req,host=B,path=/home counter=20 10s

Answer
SELECT sum(rate) FROM (
SELECT non_negative_derivative(counter)
FROM http_req
WHERE <time range>
GROUP BY *
) GROUP BY time(10s), path

Answer
SELECT sum(rate) FROM (
SELECT non_negative_derivative(max(counter))
FROM http_req
WHERE <time range>
GROUP BY time(10s), *
) GROUP BY time(10s), path

Question
Write a query that
computes the ratio of
system load5 to cpu
usage_user
sys,host=A load5=10 10s

TICKscript

What is TICKscript?
● Domain Specific Language for Kapacitor
○ Used to define Kapacitor tasks
● Made for processing streams of data
○ No support for ad-hoc execution
● Data model based off on InfluxDB line protocol
● Great for alerting
○ Expressing complex queries can be difficult and repetitive

TICK Script
• Chain invocation language
– | chains together different nodes
– . refers to specific attributes on a
node
• Variables refer to values
– Strings
– Ints, Floats
– Durations
– Pipelines
var measurement = 'requests'
var data = stream
|from()
.measurement(measurement)
|where(lambda: "is_up" == TRUE)
|where(lambda: "my_field" > 10)
|window()
.period(5m)
.every(5m)
// Count number of points in window
data
|count('value')
.as('the_count')
// Compute mean of data window
data
|mean('value')
.as('the_average')

TICKScript Syntax - Quoting Rules
• Double Quotes
– References data in lambda
expression
• Single Quotes
– Literal String value
// ' means the use the literal string value
var measurement = 'requests'
var data = stream
|from()
.measurement(measurement)
// " means use the reference value
|where(lambda: "is_up" == TRUE)
|where(lambda: "my_field" > 10)
|window()
.period(5m)
.every(5m)
// ' means to use the literal string value
data
|count('value')
.as('the_count')

Create a More Interesting Stream
TICKscript
• Create 5m windows of data that emit
every 1m
• Compute the average of the field
usage_user
• Log the result
// cpu.tick
stream
|from()
.measurement('cpu')
|window()
.period(5m)
.every(1m)
|mean('usage_user')
.as('mean_usage_user')
|log()

An even more interesting TICKscript
• Filter on the tag cpu=cpu-total
• Create 5m windows of data that emit every
1m
usage_user
• Log the result
// cpu.tick
stream
|from()
.measurement('cpu')
|where(lambda: "cpu" == 'cpu-total')
|window()
.period(5m)
.every(1m)
|mean('usage_user')
.as('mean_usage_user')
|log()

Create a Batch TICKscript
• Query 5m windows of data every 1m
usage_user
• Log the result
// batch_cpu.tick
batch
|query('''
SELECT mean("usage_user") AS
mean_usage_user
FROM "telegraf"."autogen"."cpu"
''')
.period(5m)
.every(1m)
|log()

Types of Nodes
● BatchNode
● StreamNode
● AlertNode
● BarrierNode
● ChangeDetect
● CombineNode
● DefaultNode
● DeleteNode
● DerivativeNode
● EC2AutoscaleNode
● EvalNode
● FlattenNode
● FromNode
● GroupByNode
● HTTPOutNode
● HTTPPostNode
● InfluxDBOutNode
● InfluxQLNode
● JoinNode
● K8sAutoscaleNode
● KapacitorLoopback
● LogNode
● NoOpNode
● QueryNode
● SampleNode
● ShiftNode
● SideloadNode
● StateCountNode
● StateDurationNode
● StatsNode
● SwarmAutoscaleNode
● UDFNode
● UnionNode
● WhereNode
● WindowNode

Batch Stream
● Issues an InfluxQL query to
InfluxDB on a schedule
● Yields the resulting data to the
rest of the Kapacitor pipeline
● InfluxDB writes data to Kapacitor
as it receives writes
● Each individual point is yielded to
the Kapacitor pipeline

Answer
var data = stream
|from()
.measurement('cpu')
.groupBy('host')
|window()
.period(20s)
.every(20s)
data
|mean('usage_user')
data
|mean('usage_system')

Not Possible
var data = stream
|from()
.measurement('cpu')
|window()
.period(20s)
.every(20s)
data
|mean(*) // Cant do this

Answer
stream
|from()
.measurement('http_req')
.groupBy(*)
|derivative('counter')
.nonNegative()
.as('rate')
|groupBy('path')
|sum('rate')

Answer
var cpu = stream
|from()
.measurement('cpu')
var sys = stream
|from()
.measurement('sys')
cpu
|join('sys')
.as('cpu', 'sys')
|eval(lambda: "cpu.usage_user" / "sys.load5")

Flux

What is Flux?
● “Data Scripting Language”
○ Idea is to allow for more features than you would expect from a pure
query language
● Made for processing streams of data
○ Supports ad-hoc queries
● Rich data model that is distinct from the InfluxDB data model
○ Makes it easier to reason about the internals of what the language is
doing to your data

More General
|> mean()

Answer
|> filter(fn: (r) => r._measurement == "cpu" OR
r._measurement == "mem")
|> filter(fn: (r) => r._field == "usage_user" OR
r._field == "free")
|> mean()

Turning it into a
function so I never
have to think
about it again
rate =(table=<-, m, field="counter", by, interval) =>
|> filter(fn: (r) => r._measurement == m)
|> filter(fn: (r) => r._field == field)
|> group(by: by)
|> window(period: interval, every: interval)
|> sum()
|> rate(m: "http_req",
by: ["path"],
interval: 20s)

Answer
mybucket = from(bucket: "mybucket")
cpu = mybucket
|> filter(fn: (r) => r._field == "usage_user")
sys = mybucket
|> filter(fn: (r) => r._measurement == "sys")
|> filter(fn: (r) => r._field == "load5")
join(tables: {cpu: cpu, sys: sys}, on: ["_time"])
|> map(fn: (r) => r._cpu_value / r._sys_value)

INFLUXQL & TICKSCRIPT

Recommended

More Related Content

What's hot (14)

Similar to INFLUXQL & TICKSCRIPT (20)

More from InfluxData (20)

Recently uploaded (15)

INFLUXQL & TICKSCRIPT