Redis CSV Writer

Description

ezvpn-redis-csv is a pre-configured container docker for the IOhubTM environment.

It creates CVS files in the local file system from the measurements stored in the Redis database.

It can produce different CSV files, with different policies for data retention, collection frequency, log rotation, ...

Columns, saving frequency, log rotation, and other options can be configured.

Redis to CSV

ezvpn-redis-csv works only in conjunction with ezvpn-redis or with any other Redis instance as long as the data is saved in the format described in the ezvpn-redis container documentation.

How to use it

The CSV_CFG environment variable describes the container configuration.

CSV_CFG must contain a string representing a JSON object, describing how to create and fill the CSV files.

This below is a working example representing a case with:

  • creation of two csv files (csv-1.csv and csv-2.csv)
  • csv-1.csv file is populated every 10 minutes with two columns (Temperature, Pressure) with measurements coming from an OPC-UA server.
  • csv-2.csv file is populated every 5 minutes with two measurements from Modbus and Siemens protocols (Humidity setpoint, Humidity)
  • csv-1.csv file will be rotate every day and only 3 files are stored.
  • csv-3.csv file is populated every day at midnight on the Los Angeles timezone with two measurements from Modbus and Siemens protocols (Humidity setpoint, Humidity) using the date format YYYY-MM-DD HH:mm:ss:SSS. this file will be encoded to base64 and sent to the internal MQTT broker on topic fld/file/w/. Look at the file container to see how it works.
{
    "csvs": [
        {
            "name": "csv-1",
            "folder": "/tmp",
            "cron": "*/10 * * * *",
            "measurements": [
                {
                    "address": "opcua/myplc.temperature",
                    "column": "Temperature"
                },
                {
                    "address": "opcua/myplc.pressure",
                    "column": "Pressure"
                }
            ],
            "rotation": "daily",
            "maxFiles": 3,
            "zipOnRotate": false
        },
        {
            "name": "csv-2",
            "folder": "/csv_folder",
            "cron": "*/5 * * * *",
            "measurements": [
                {
                    "address": "modbus/rh-setpoint",
                    "column": "Humidity setpoint"
                },
                {
                    "address": "step7/humidity",
                    "column": "Humidity"
                }
            ],
            "rotation": "hourly",
            "timestampHeader": "ts",
            "showHumanDate": true,
            "humanDateHeader": "Local Date",
            "humanDateTimezone": "America/Los_Angeles"
        },
        {
            "name": "csv-3",
            "folder": "/csv",
            "cron": "00 00 * * *",
            "measurements": [
                {
                    "address": "modbus/rh-setpoint",
                    "column": "Humidity setpoint"
                },
                {
                    "address": "step7/humidity",
                    "column": "Humidity"
                }
            ],
            "rotation": "daily",
            "timestampHeader": "ts",
            "showHumanDate": true,
            "humanDateHeader": "Local Date",
            "humanDateTimezone": "America/Los_Angeles",
            "mqttPublish": {
                "host": "ezvpn-mqtt",
                "port": 1883,
                "topic": "fld/file/w/"
            }
        }
    ]
}

Mapping configuration (CSV_CFG)

The JSON object has one array field:

{
    "csvs": []
}

csvs: contains the list of the CSV files. Required

csvs

csvs is an array of objects, representing the CSV configuration file. The csvs array cannot be empty.

Each CSV is represented by the following object:

{
    "name": "<CSV file name>",
    "folder": "<folder to store CSV files>",
    "cron": "<frequency time>",
    "timezone": "<timezone>",
    "measurements": [<measurements definition>],
    "rotation": "<frequency time>",
    "maxFiles": <max files numbers>,
    "zipOnRotate": <true or false>,
    "timestampHeader": "<timestamp name column header>",
    "showHumanDate": <true or false>,
    "humanDateHeader": "<human readable date column header>",
    "humanDateTimezone": "<time zone for the humand readable date>",
    "mqttPublish": { <MQTT broker and topic to send files encoded to base64> }
}
  • name the CSV file name. Required

  • folder CSV files destination folder. Required

  • cron can be supplied in : cron time format Required

  • timezone: an optional timezone, see the list, to use for the cron expression. If not defined UTC is used.

  • measurements Required it is an array having this format:

{
    "address": "<protocol/measurement>",
    "column": "<CSV name column header>"
}
  • rotation frequency time to create a new CSV file. Can be set to daily or hourly. Defaults to daily:

    • When hourlythe CSV file is save in <folder>/<name>-<YYYY>-<MM>-<DD>-<HH>.csv
    • When dailythe CSV file is save in <folder>/<name>-<YYYY>-<MM>-<DD>.csv
  • maxFiles max number of files rotated. Defaults to 14. Max value 1024.

  • zipOnRotate Do not gzip rotated files. Defaults to false. If true, the rotated file, will have an additional .gz extension.

  • timestampHeader the CSV timestamp header column name. Defaults to Timestamp

  • showHumanDate If true a second column is added after , with a timezone localized human readable date. The date format used is YYYY-MM-DD HH:mm:ss:SSS.

  • humanDateHeader the human readable date header column name. Defaults to Local Date.

  • humanDateTimezone the human readable date timezone. Defaults to UTC.

  • mqttPublish when a csv files is archived/zipped, if mqttPublish defined the new file is automatically sent to the mqtt server defined in mqttPublish. mqttPublish is optional; if present, all its fields (host, port, topic) are required.

{
    "host": "<MQTT host>",
    "port": <MQTT port>,
    "topic": "<topic to send file encoded to base64>"
}

How Redis data is consolidated

At each Redis read cycle, only the last saved measure on Redis is used in the CSV.

e.g. your polling frequency on OPC-UA is every 100msec; every 5 minutes you will have about 3000 values on Redis. If your cron is defined as */5 * * * * you will save in the CSV file only one record, using the last of the 3000 values collected in the Redis database.

You are not saving all the measurements in the Redis database. The field polling frequency and the CSV generation frequency are not related.

The timestamp saved in the CSV file is the instant at the CSV record generation; it is not the measurement timestamp. The value is the number of milliseconds since the Unix Epoch ( 00:00:00 UTC on 1 January 1970).

Environment variables

When you start the ezvpn-redis-csv container, you can adjust the instance's configuration by passing one or more environment variables to the docker run command.

  • CSV_CFG: describes the container configuration.

Redis connection

  • REDIS_HOST: Redis host. Defaults to 127.0.0.1.
  • REDIS_PORT: Redis port. Defaults to 6379.
  • REDIS_PASSWORD: Redis authorization password.

How to share the CSV files

ezvpn-redis-csv provides two preconfigured shares:

  • the application volume on /ezvpn/app
  • the global volume on /ezvpn/global

You can use the already configured volumes to save the CSV files and make them available to other application (e.g. Filebrowser or Dropbox).

These volumes are created by default; they can be removed or changed.

You can send file defining mqttPublish using MQTT transport. Useful to send files to another IOhubTM instance using EZ VPNTM remote destination or to send the file to other containers that will manage its content, for example the file field container listens for an event on MQTT broker and writes the received file to the local filesystem.

Docker container details

Image: us-central1-docker.pkg.dev/ez-shared/iohub/iohub-redis-csv

Supported architecture: amd64

Changelog

v1.0.3
  • mqttPublish added
v1.0.2
  • Human readable timestamp, with timezone
v1.0.1
  • Timezone added in cron expression
v1.0.0
  • First Release