Introduction to InfluxDB and php usage

InfluxDB (time series database) is a common use scenario: monitoring data statistics. Record the usage of computer memory every millisecond, and then use the graphical interface (InfluxDB V1 generally cooperates with Grafana) to make a broken line diagram of memory usage according to the statistical data;
The input of reference text here can be understood as recording some data (commonly used monitoring data, buried point statistics, etc.) according to time, and then making charts for statistics.

1, What is influxDB

InfluxDB is an open source temporal data developed by InfluxData. Written by Go, it focuses on querying and storing temporal data with high performance. InfluxDB is widely used in monitoring data of storage system, real-time data of IoT industry and other scenarios. It is used to store nice when operations are frequent and easy to be concurrent. For example, ant forest collects energy services.

2, Comparison of basic concepts of common relational database (MySQL)

concept MySQL InfluxDB
Database (same as) database database
Table (different) table measurement
Columns (different) column Tag (with index, not required), field (without index), timestamp (unique primary key)
  • tag set: a set of tag key s and tag value s for different groups;
  • field set: the set of each group of field key and field value;
  • retention policy: the data storage policy (the default policy is autogen) does not delete data. It specifies the retention time of data to achieve the purpose of clearing data;
  • series: collection of common retention policy, measurement and tag set;

The sample data are as follows: census is measurement, butterflies and honeybees are field key, and location and scientist are tag key

name: census
------
time                 butterflies     honeybees     location     scientist
2015-08-18T00:00:00Z      12             23           1         langstroth
2015-08-18T00:00:00Z      1              30           1         perpetua
2015-08-18T00:06:00Z      11             28           1         langstroth
2015-08-18T00:06:00Z      11             28           2         langstroth

There are three tag set s in the example

3, Attention

  • tag can only be of string type
  • field type unlimited
  • join is not supported
  • Support CONTINUOUS QUERY operations (summary statistics): CONTINUOUS QUERY
  • Cooperate with Telegraf service (Telegraf can monitor system CPU, memory, network and other data)
  • Cooperate with Grafana service (image interface for data presentation, visualization of data in influxdb)

4, Common influxQL

-- View all databases
show databases;
-- Use a specific database
use database_name;
-- View all measurement
show measurements;
-- Query 10 pieces of data
select * from measurement_name limit 10;
-- The time field in the data displays a nanosecond timestamp by default, which is changed to a readable format
precision rfc3339; -- Then query, the time is rfc3339 standard format 
-- Or you can bring this parameter directly when connecting to the database
influx -precision rfc3339
-- View a measurement All in tag key 
show tag keys
-- View a measurement All in field key 
show field keys
-- View a measurement All save policies in(There can be multiple, one identified as default)
show retention policies;
--Delete database
drop database "db_name";
--Delete data table
drop measurement "measurement_name"

5, Installation

1, windows for incluxdb (64 bit)

1. Direct installation dependency in project
composer require influxdb/influxdb-php

The download address is: dl.influxdata.com/influxdb/release...

2. Modify the influxdb.conf file, and modify the meta, wal, and data paths

Create three corresponding folders.
After decompression, double-click incluxd.exe and inclux.exe to enter the command line.

Of course, you can also go to the official download to choose the package suitable for you
Infixdb, Telegraf, Chronograf and Kapacitor can download the versions of each platform from the official website below
Download the official website: portal.influxdata.com/downloads/

2, Virtual machine installation (execute the command to complete the installation)

1. Installing influxdb through ubuntu
sudo apt install influxdb
sudo apt install influxdb-client
2. Check the status of influxdb and q exit. Set startup
//View status
sudo service influxdb status
//Set startup and self startup
sudo service influxdb start
3. Start
influxd
4. Enter
influx

6, Finally, an example written in PHP is attached

Insertion method of package
public static function insert(string $table,
                                  array $tags = [],
                                  array $fields = [],
                                  $value = null,
                                  $timestamp = null)
    {
        $point = new Point($table, $value, $tags, $fields, $timestamp);
        dispatch(function () use ($point) {
            InfluxDB::writePayload((string)$point);
        });
    }
Usage: of course, the premise is that you have to first use
InfluxDB::insert(InfluxDBTableConstants::FT, compact('user_id', 'behavior'), compact('amount', 'balance'));
//Method of obtaining information
public static function getPoints(string $table, array $where = [], string $order = 'time desc', int $limit = 20): array
    {
        $query_where = [];
        foreach ($where as $field => $item) {
            if (is_numeric($field)) {
                if (is_string($item) || is_numeric($item)) {
                    $query_where[] = $item;
                } else if (is_array($item)) {
                    if (count($item) === 2) {
                        $query_where[] = "{$item[0]} = '{$item[1]}'";
                    } else if (count($item) === 3) {
                        $query_where[] = "{$item[0]} {$item[1]} '{$item[2]}'";
                    } else {
                        throw new \InvalidArgumentException('where');
                    }
                } else {
                    throw new \InvalidArgumentException('where');
                }
            } else if (is_string($field)) {
                $str_where = "$field";
                if (is_string($item) || is_numeric($item)) {
                    $str_where .= " = '$item'";
                } else if (is_array($item) && count($item) === 2) {
                    $str_where .= " {$item[0]} '{$item[1]}'";
                } else {
                    throw new \InvalidArgumentException('where');
                }
                $query_where[] = $str_where;
            } else {
                throw new \InvalidArgumentException('where');
            }
        }
        $res = InfluxDB::getBuilder()->from($table)
            ->where($query_where)
            ->orderBy($order, '')
            ->limit($limit)
            ->getResultSet()
            ->getPoints();
        return $res;
    }
    public static function getData(string $table_name, array $where = []): array
    {
        $ret = [];
        $list = InfluxDB::getPoints($table_name, $where);
        if ($list) {
            $ret[] = collect($list)->map(function ($item) {
                $time = $item['time'];
                $time = explode('.', $time)[0] . '+00:00';
                $item['time'] = Carbon::parse($time)
                    ->setTimezone(config('app.timezone'))
                    ->format('Y-m-d H:i:s');
                return $item;
            })->toArray();
            $ret[] = [
                'last_time' => $list[count($list) - 1]['time']
            ];
        } else {
            $ret = [[], []];
        }
        return $ret;
    }
//And how to use it
$wt = InfluxDB::getData(InfluxDBTableConstants::WT,$where);

Tags: Laravel

Posted on Wed, 01 Dec 2021 07:47:35 -0500 by kath421