service notes

influx command language

-host name
-database name
-password password blank: prompt for password (--password '').
-username username
-execute command
-format [json|csv|column]
-pretty for the json format.
-precision [rfc3339|h|m|s|ms|u|ns] format of the timestamp rfc3339:ccyy-mm-ddThh:mm:ss.sssZ default :nanoseconds.
-consistency [any|one|quorum|all] Set write consistency level
-import
-path to file to import
-pps points per second the import will allow. default zero.
-port #### default:8086
-socket ####
-ssl Use https
-type [influxql|flux]shell
-unsafeSsl when connecting to the cluster using https and not use SSL verification.
-compressed true if the import file is compressed
-version

connect host:port connects to another node specified by host:port
auth prompts for username and password
pretty toggles pretty print for the json format
chunked turns on chunked responses from server
chunk size size 0 to reset to the default
use db_name sets database
format [json| csv | column ] format of the server responses
precision [rfc3339|h|m|s|ms|u|ns] format of the timestamp:
consistency [any|one|quorum|all sets write consistency level
history command history
settings Example:
Setting           Value
--------          --------
Host              localhost:8086
Username          
Database          ruuvi
RetentionPolicy   
Pretty            false
Format            column
Write Consistency all 
Chunked           true
Chunk Size        0 
Write Consistency number of nodes that must confirm the write. If the requirement is not met the return value will be either partial write if some points in the batch fail or write failure if all points in the batch fail.
$INFLUXDB_GRAPHITE_CONSISTENCY_LEVEL see the Query String Parameters for Writes
clear settings such as database or retention policy. run 'clear' for help
exit |quit|ctrl+d
show [databases | series | measurements | tag keys | field keys ]

A full list of influxql commands can be found at: influxdata.com/influxdb/latest/query_language/spec/ commands

Examples:

Command, sutible to include in a script, to display the time(using Eastern Standard Time zone) of the most recent (last) temperature from a specfic ruuvi tag.
influx -precision rfc3339 -database ruuvi -execute "select last(temperature)*9/5+32 from ruuvi_measurements where mac='DC4B596ED18B' tz('EST')"

non-interactive query database "metrics" and pretty print json:

influx -database 'metrics' -execute 'select * from cpu' -format 'json' -pretty

Connect to a specific database on startup and set database context:(to defaults)

influx -database 'metrics' -host 'localhost' -port '8086'
influx -precision rfc3339 -database ruuvi

SQL-like query language named InfluxQL

Measurement names, tag keys, tag values, field keys, and field values are case sensitive.

Numerical field values are floats unless they include a trailing i

Use " (quotes) not ' (apostrophes). Not timestamps nor boolean (true, false)

Keywords

ALLALTERANYASASCBEGIN
BYCREATECONTINUOUSDATABASEDATABASESDEFAULT
DELETEDESCDESTINATIONSDIAGNOSTICSDISTINCTDROP
DURATIONENDEVERYEXPLAINFIELDFOR
FROMGRANTGRANTSGROUPGROUPSIN
INFINSERTINTOKEYKEYSKILL
LIMITSHOWMEASUREMENTMEASUREMENTSNAMEOFFSET
ONORDERPASSWORDPOLICYPOLICIESPRIVILEGES
QUERIESQUERYREADREPLICATIONRESAMPLERETENTION
REVOKESELECTSERIESSETSHARDSHARDS
SLIMITSOFFSETSTATSSUBSCRIPTIONSUBSCRIPTIONS TAG
TOUSERUSERSVALUESWHEREWITH
WRITE

influx
Connected to http://localhost:8086 version 1.6.4
InfluxDB shell version: 1.6.4

use ruuvi

show FIELD KEYS 
name: ruuvi_measurements
fieldKey                  fieldType
--------                  ---------
absoluteHumidity          float
accelerationAngleFromX    float
accelerationAngleFromY    float
accelerationAngleFromZ    float
accelerationTotal         float
accelerationX             float
accelerationY             float
accelerationZ             float
airDensity                float
batteryVoltage            float
dewPoint                  float
equilibriumVaporPressure  float
humidity                  float
measurementSequenceNumber integer
movementCounter           integer
pressure                  float
rssi                      integer
temperature               float
txPower                   integer


influx -precision rfc3339 -database ruuvi  # rfc3339 uses better time display

select  LAST(temperature) from ruuvi_measurements where mac='DC4B596ED18B' tz('EST') (Not ET, not EDT )
name: ruuvi_measurements
time                          last
----                          ----
2018-12-03T20:40:28.217-05:00 24.41




 select time, temperature, absoluteHumidity,batteryVoltage, pressure, txPower  from ruuvi_measurements 
                                                                    where mac='D3517872EC0F' and  time > 154079 and rssi> -70 limit 2;
name: ruuvi_measurements
time                     temperature absoluteHumidity  batteryVoltage pressure txPower
----                     ----------- ----------------  -------------- -------- -------
2018-03-22T17:05:14.741Z 6           4.064597097450367                99300    
2018-03-22T17:06:15.745Z 6           4.064597097450367                99300 


select time, temperature, absoluteHumidity,batteryVoltage, pressure, txPower  from ruuvi_measurements 
                                                                   where mac='D3517872EC0F' and batteryVoltage>0 limit 2
time                     temperature absoluteHumidity  batteryVoltage pressure txPower
----                     ----------- ----------------  -------------- -------- -------
2018-06-06T17:53:25.336Z 49.86       40.90683320213987  3.001          99442                ### last entry  as of 11/30/18
2018-06-06T17:53:36.438Z 49.83       40.84931173861518  3.019          99454                ### notice no txPower values



Other MACs  : E511D11ECBB8, E6C3BE6F656C , FB0ECBEF7354, F7FA744A1E1A, DFC8B0FE5CC0
                                                                       2018-02-01T01:55:27.436Z
D3517872EC0F 

 precision edited for breivity (ed)

select *  from ruuvi_measurements limit 5; 

select *  from ruuvi_measurements where mac='D3517872EC0F' and  time > 154079 and rssi> -70 limit 2;
name: ruuvi_measurements
time                absoluteHumidity  accelerationAngleFromX accelerationAngleFromY accelerationAngleFromZ accelerationTotal  accelerationX accelerationY accelerationZ airDensity         batteryVoltage dataFormat dewPoint equilibriumVaporPressure humidity mac          measurementSequenceNumber movementCounter pressure rssi temperature txPower
----                ----------------  ---------------------- ---------------------- ---------------------- -----------------  ------------- ------------- ------------- ----------         -------------- ---------- -------- ------------------------ -------- ---          ------------------------- --------------- -------- ---- ----------- -------
1540249289169000000 8.624138716002781 76.08684542427619      14.79288467670658      85.07363877795116      0.9898070519045619 0.238         0.957         0.085         1.2378516828652366 2.929          3          8.66     1121.3290262788335       100      D3517872EC0F                                           100486   -61  8.66        
1540249309853000000 8.618610510024277 75.79732116716174      14.938510454645076     85.46427794222969      0.9863351357424107 0.242         0.953         0.078         1.2379486447936163 2.929          3          8.65     1120.570472328523        100      D3517872EC0F                                           100490   -64  8.65        

select  time, temperature,humidity,batteryVoltage, rssi   from ruuvi_measurements where mac='D3517872EC0F'  order by time  limit 9
name: ruuvi_measurements
time                temperature humidity batteryVoltage rssi
----                ----------- -------- -------------- ----
1540249289169000000 8.66        100      2.929          -61
1540249299762000000 8.65        100      2.929          -76
1540249309853000000 8.65        100      2.929          -64
1540249320455000000 8.64        100      2.923          -62
1540249331052000000 8.65        100      2.929          -77
1540249341135000000 8.65        100      2.929          -76
1540249351743000000 8.65        100      2.929          -62
1540249361841000000 8.66        100      2.923          -64
1540249371940000000 8.64        100      2.923          -78

select  time, mac, temperature, rssi   from ruuvi_measurements  order by time  limit 9
name: ruuvi_measurements
time                mac          temperature rssi
----                ---          ----------- ----
1540249289169000000 D3517872EC0F 8.66        -61
1540249290475000000 F2C0C643AD03 21          -92
1540249299762000000 D3517872EC0F 8.65        -76
1540249301098000000 F2C0C643AD03 21          -93
1540249309853000000 D3517872EC0F 8.65        -64


select  time,  temperature, rssi   from ruuvi_measurements where mac='F2C0C643AD03' and time > 1540900000000000000 limit 900;

name: ruuvi_measurements
time                temperature rssi
----                ----------- ----
1540901073139000000 21          -62
1540901087223000000 24          -69
1540901097337000000 24          -85
1540901107431000000 24          -78
1540901118022000000 24          -77
1540901128623000000 25          -93
1540901139223000000 25          -79
1540901149313000000 25          -80
1540901159404000000 25          -78
1540901170492000000 25          -86
1540901180592000000 25          -78
1540901190676000000 25          -78
1540901273348000000 26.09       -85
1540901513209000000 27.14       -56
1540909474167000000 24.91       -70
1540909517698000000 24.95       -50
1540911372699000000 26.97       -76
1540911703507000000 26.83       -81




select  time, temperature,humidity,batteryVoltage, rssi   from ruuvi_measurements where mac='D3517872EC0F' and   time > 1542200000000000000  limit 90;


name: ruuvi_measurements
time                temperature        humidity batteryVoltage rssi
----                -----------        -------- -------------- ----
1542200000530000000 2.51               75       2.905          -56
1542200010627000000 2.5                74.5     2.911          -72
1542200020714000000 2.49               74       2.899          -71
1542200031307000000 2.51               73.5     2.911          -72
1542200041395000000 2.5                73.5     2.911          -70



select * from ruuvi_measurements;
name: ruuvi_measurements
time                absoluteHumidity  accelerationAngleFromX accelerationAngleFromY accelerationAngleFromZ accelerationTotal  accelerationX accelerationY accelerationZ airDensity         batteryVoltage dataFormat dewPoint          equilibriumVaporPressure humidity mac          measurementSequenceNumber movementCounter pressure rssi temperature txPower
----                ----------------  ---------------------- ---------------------- ---------------------- -----------------  ------------- ------------- ------------- ----------         -------------- ---------- --------          ------------------------ -------- ---          ------------------------- --------------- -------- ---- ----------- -------
1540249289169000000 8.62 76.08 14.79 85.07 0.989807 0.238         0.957         0.085         1.2378 2.929          3          8.66              1121.3290262788335       100      D3517872EC0F                                           100486   -61  8.66        

1540249290475000000 8.059030710937025                                                                                                                                   1.1862117308049511                4          8.292341167820647 2485.764136776915        44       F2C0C643AD03                                           100500   -92  21          

influx -precision rfc3339   # Hey it's a time series data base      rfc3339 := YYYY-MM-DDTHH:MM:SS.nnnnnnnnnZ

 InfluxDB HTTP API runs on port 8086
 system _internal


 SHOW RETENTION POLICIES on ruuvi
name    duration shardGroupDuration replicaN default
----    -------- ------------------ -------- -------
autogen 0s       168h0m0s           1        true

show stats 

sample data curl https://s3.amazonaws.com/noaa.water-database/NOAA_data.txt -o NOAA_data.txt 44 seconds 5MB CREATE DATABASE NOAA_water_database influx -import -path=NOAA_data.txt -precision=s -database=NOAA_water_database influx -precision rfc3339 -database NOAA_water_database SHOW measurements name: measurements name ---- average_temperature h2o_feet h2o_pH h2o_quality h2o_temperature SELECT COUNT("water_level") FROM h2o_feet name: h2o_feet time count ---- ----- 1970-01-01T00:00:00Z 15258 SELECT * FROM h2o_feet LIMIT 5 name: h2o_feet time level description location water_level ---- ----------------- -------- ----------- 2015-08-18T00:00:00Z below 3 feet santa_monica 2.064 2015-08-18T00:00:00Z between 6 and 9 feet coyote_creek 8.12 2015-08-18T00:06:00Z below 3 feet santa_monica 2.116 2015-08-18T00:06:00Z between 6 and 9 feet coyote_creek 8.005 2015-08-18T00:12:00Z below 3 feet santa_monica 2.028
SHOW DATABASES RETENTION POLICIES SERIES MEASUREMENTS TAG KEYS TAG VALUES FIELD KEYS SHOW DATABASES name: databases _internal ruuvi NOAA_water_database SHOW RETENTION POLICIES [ON database_name] SHOW SERIES [ON database_name] [FROM_clause] [WHERE tag_key operator [ 'tag_value' | regular_expression]] [LIMIT_clause] [OFFSET_clause] SHOW MEASUREMENTS [ON database_name] [WITH MEASUREMENT regular_expression] [WHERE tag_key operator ['tag_value' | regular_expression]] [LIMIT_clause] [OFFSET_clause SHOW TAG KEYS [ON database_name] [FROM_clause] [WHERE tag_key operator ['tag_value' | regular_expression]] [LIMIT_clause] [OFFSET_clause] SHOW TAG VALUES [ON database_name][FROM_clause] WITH KEY [ [operator "tag_key" | regular_expression] | [IN ("tag_key1","tag_key2")]] [WHERE tag_key operator ['tag_value' | regular_expression]] [LIMIT_clause] [OFFSET_clause] SHOW FIELD KEYS [ON database_name] [FROM measurement_name] name duration shardGroupDuration replicaN default autogen 0s 168h0m0s 1 true show SERIES key --- average_temperature,location=coyote_creek average_temperature,location=santa_monica h2o_feet,location=coyote_creek h2o_feet,location=santa_monica h2o_pH,location=coyote_creek h2o_pH,location=santa_monica h2o_quality,location=coyote_creek,randtag=1 h2o_quality,location=coyote_creek,randtag=2 h2o_quality,location=coyote_creek,randtag=3 h2o_quality,location=santa_monica,randtag=1 h2o_quality,location=santa_monica,randtag=2 h2o_quality,location=santa_monica,randtag=3 h2o_temperature,location=coyote_creek h2o_temperature,location=santa_monica average_temperature,location=coyote_creek average_temperature,location=santa_monica h2o_feet,location=coyote_creek h2o_feet,location=santa_monica h2o_pH,location=coyote_creek h2o_pH,location=santa_monica h2o_quality,location=coyote_creek,randtag=1 h2o_quality,location=coyote_creek,randtag=2 h2o_quality,location=coyote_creek,randtag=3 h2o_quality,location=santa_monica,randtag=1 h2o_quality,location=santa_monica,randtag=2 h2o_quality,location=santa_monica,randtag=3 h2o_temperature,location=coyote_creek h2o_temperature,location=santa_monica show SERIES key --- ruuvi_measurements,dataFormat=3,mac=DFC8B0FE5CC0 ruuvi_measurements,dataFormat=3,mac=E6C3BE6F656C ruuvi_measurements,dataFormat=3,mac=F7FA744A1E1A ruuvi_measurements,dataFormat=3,mac=FB0ECBEF7354 ruuvi_measurements,dataFormat=4,mac=D3517872EC0F ruuvi_measurements,dataFormat=4,mac=F2C0C643AD03 ruuvi_measurements,dataFormat=5,mac=E511D11ECBB8 ruuvi_measurements,dataFormat=5,mac=E6C3BE6F656C show FIELD KEYS name: ruuvi_measurements fieldKey fieldType -------- --------- absoluteHumidity float accelerationAngleFromX float accelerationAngleFromY float accelerationAngleFromZ float accelerationTotal float accelerationX float accelerationY float accelerationZ float airDensity float batteryVoltage float dewPoint float equilibriumVaporPressure float humidity float measurementSequenceNumber integer movementCounter integer pressure float rssi integer temperature float txPower integer show FIELD KEYS name: average_temperature degrees float name: h2o_feet level description string water_level float name: h2o_pH pH float name: h2o_quality index float name: h2o_temperature degrees float
docs.influxdata.com functions
Aggregations
COUNT( DISTINCT(
INTEGRAL( [ * | * average
INTEGRAL( field_key |
   /regular_expression/ ]
                [
, unit ]
area under the curve for subsequent field values and converts those results into the summed area per unit.
MEAN( MEDIAN( MODE(
STDDEV( SUM(
SPREAD( difference between the minimum and maximum
Selectors (only one is permitted)
BOTTOM(   field,n smallest n values
TOP(   field,n greatest n values
MIN( FIRST( oldest
select  FIRST(temperature) from ruuvi_measurements where mac='D48B9C6C230E' 
name: ruuvi_measurements
time                     first
----                     -----
2018-10-03T01:19:46.678Z 25.04
MAX( LAST( newest
 select  LAST(temperature) from ruuvi_measurements where mac='D48B9C6C230E' 
name: ruuvi_measurements
time                     last
----                     ----
2018-12-02T21:33:26.728Z 23.52
PERCENTILE(   field,nvalue that is larger than n percent of the field values SAMPLE(   field,n randomly select values
Transformations
CEILING(
FLOOR(
CUMULATIVE_SUM( DERIVATIVE( DIFFERENCE(
HISTOGRAM( MOVING_AVERAGE(
NON_NEGATIVE_DERIVATIVE( NON_NEGATIVE_DIFFERENCE(
ELAPSED(
Predictors
HOLT_WINTERS(

SELECT COUNT( [ * |  | // ] )
        [INTO_clause] 
            FROM_clause 
                [WHERE_clause] 
                    [GROUP_BY_clause] 
                        [ORDER_BY_clause] 
                            [LIMIT_clause] 
                                [OFFSET_clause] 
                                    [SLIMIT_clause] 
                                            [SOFFSET_clause] 

influxdb service

Starting (takes 10 seconds!)
influxd run --config /etc/influxdb/influxdb.conf


sudo service influxdb stop


influxd --help
Configure and start an InfluxDB server.  
Usage: influxd [[command] [arguments]] 
The commands are: 
    backup               downloads a snapshot of a data node and saves it to disk
    config               display the default configuration
    help                 display this help message
    restore              uses a snapshot of a data node to rebuild a cluster
    run                  run node with existing configuration
    version              displays the InfluxDB version

"run" is the default command.

Use "influxd [command] -help" for more information about a command.


Runs the InfluxDB server.

Usage: influxd run [flags]

    -config           defaults $INFLUXDB_CONFIG_PATH,  ~/.influxdb/influxdb.conf, or /etc/influxdb/influxdb.conf if a file
                        Disable the automatic loading of a configuration file using the null device (such as /dev/null).
    -pidfile          Write process ID to a file.
    -cpuprofile       Write CPU profiling information to a file.
    -memprofile        Write memory usage information to a file.

run: flag: help requested


influxd config       
Merging with configuration at: /etc/influxdb/influxdb.conf
reporting-disabled = false
bind-address = "127.0.0.1:8088"

[meta]
  dir = "/var/lib/influxdb/meta"
  retention-autocreate = true
  logging-enabled = true

[data]
  dir = "/var/lib/influxdb/data"
  index-version = "inmem"
  wal-dir = "/var/lib/influxdb/wal"
  wal-fsync-delay = "5s"
  validate-keys = false
  query-log-enabled = true
  cache-max-memory-size = 1073741824
  cache-snapshot-memory-size = 26214400
  cache-snapshot-write-cold-duration = "10m0s"
  compact-full-write-cold-duration = "4h0m0s"
  compact-throughput = 50331648
  compact-throughput-burst = 50331648
  max-series-per-database = 1000000
  max-values-per-tag = 100000
  max-concurrent-compactions = 0
  max-index-log-file-size = 1048576
  trace-logging-enabled = false
  tsm-use-madv-willneed = false

[coordinator]
  write-timeout = "10s"
  max-concurrent-queries = 0
  query-timeout = "8s"
  log-queries-after = "0s"
  max-select-point = 0
  max-select-series = 0
  max-select-buckets = 0

[retention]
  enabled = true
  check-interval = "30m0s"

[shard-precreation]
  enabled = true
  check-interval = "10m0s"
  advance-period = "30m0s"

[monitor]
  store-enabled = true
  store-database = "_internal"
  store-interval = "10s"

[subscriber]
  enabled = true
  http-timeout = "30s"
  insecure-skip-verify = false
  ca-certs = ""
  write-concurrency = 40
  write-buffer-size = 1000

[http]
  enabled = true
  bind-address = ":8086"
  auth-enabled = false
  log-enabled = true
  suppress-write-log = false
  write-tracing = false
  flux-enabled = false
  pprof-enabled = true
  debug-pprof-enabled = false
  https-enabled = false
  https-certificate = "/etc/ssl/influxdb.pem"
  https-private-key = ""
  max-row-limit = 0
  max-connection-limit = 0
  shared-secret = ""
  realm = "InfluxDB"
  unix-socket-enabled = false
  unix-socket-permissions = "0777"
  bind-socket = "/var/run/influxdb.sock"
  max-body-size = 25000000
  access-log-path = ""
  max-concurrent-write-limit = 0
  max-enqueued-write-limit = 0
  enqueued-write-timeout = 30000000000

[logging]
  format = "auto"
  level = "error"
  suppress-logo = false

[[graphite]]
  enabled = false
  bind-address = ":2003"
  database = "graphite"
  retention-policy = ""
  protocol = "tcp"
  batch-size = 5000
  batch-pending = 10
  batch-timeout = "1s"
  consistency-level = "one"
  separator = "."
  udp-read-buffer = 0

[[collectd]]
  enabled = false
  bind-address = ":25826"
  database = "collectd"
  retention-policy = ""
  batch-size = 5000
  batch-pending = 10
  batch-timeout = "10s"
  read-buffer = 0
  typesdb = "/usr/share/collectd/types.db"
  security-level = "none"
  auth-file = "/etc/collectd/auth_file"
  parse-multivalue-plugin = "split"

[[opentsdb]]
  enabled = false
  bind-address = ":4242"
  database = "opentsdb"
  retention-policy = ""
  consistency-level = "one"
  tls-enabled = false
  certificate = "/etc/ssl/influxdb.pem"
  batch-size = 1000
  batch-pending = 5
  batch-timeout = "1s"
  log-point-errors = true

[[udp]]
  enabled = false
  bind-address = ":8089"
  database = "udp"
  retention-policy = ""
  batch-size = 5000
  batch-pending = 10
  read-buffer = 0
  batch-timeout = "1s"
  precision = ""

[continuous_queries]
  log-enabled = true
  enabled = true
  query-stats-enabled = false
  run-interval = "1s"

[tls]
  min-version = ""
  max-version = ""

/var/lib/influxdb > 





datafiles must be owned/group influxdb influsdb

sudo find /var/lib/influxdb -user root # any found nee to be chown chgrp

                    cd /var/lib/influxdb
/var/lib/influxdb > sudo chgrp -vR influxdb  * |grep -v retained
/var/lib/influxdb > sudo chown -vR influxdb  * |grep -v retained

changed ownership of '0_chgrp' from root to influxdb
changed ownership of 'data/_internal/monitor/274/fields.idx' from root to influxdb
changed ownership of 'data/_internal/monitor/274' from root to influxdb
changed ownership of 'data/_internal/monitor/272/000000015-000000002.tsm' from root to influxdb
changed ownership of 'meta/meta.db' from root to influxdb
changed ownership of 'wal/_internal/monitor/274/_00001.wal' from root to influxdb
changed ownership of 'wal/_internal/monitor/274' from root to influxdb
later: changed ownership of 'data/ruuvi/autogen/10/000000001-000000001.tsm' from root to influxdb changed ownership of 'data/ruuvi/autogen/5/000000001-000000001.tsm' from root to influxdb changed ownership of 'data/_internal/monitor/276/000000010-000000001.tsm' from root to influxdb changed ownership of 'data/_internal/monitor/276/fields.idx' from root to influxdb changed ownership of 'wal/_internal/monitor/276/_00036.wal' from root to influxdb changed ownership of 'wal/_internal/monitor/276/_00035.wal' from root to influxdb

Benchmark vs Elasticsearch pdf influx_inspect