Ingesting data using Telegraf to TimescaleDB in Narrow Format

TimescaleDB and PostgreSQL
1

I am interested in collecting data from an OPC UA server and ingesting it into Postgresql via Telegraf. I have had some success in that I can input the data from an OPC server and ingest it into Postgresql in a wide format with each tag having its own column. Thus, I end up with thousands of columns and many null values. What I need to do is to log the data in a narrow format so I would have something like: TagID, Time, quality and a single value column. Currently, I have a Tag lookup but struggling to store the values in a narrow format. See the attached screenshot

wideformat
wideformat701×291 136 KB

. My telegraf.conf file is:

 [[inputs.opcua]]
  endpoint = "opc.tcp://WS2019:49310"
  security_policy = "None"
  security_mode = "None"
  
  nodes = [
    {name = "M1039_Flow", namespace="2", identifier_type="s", identifier="Simulator.Pumps.M1039_Flow"},
    {name = "M1039_Power", namespace="2", identifier_type="s", identifier="Simulator.Pumps.M1039_Power"}
  ]

[[outputs.postgresql]]
  connection = "host=172.16.13.24 user=myUser password=myPassword dbname=timescaledb sslmode=disable"
  schema = "public"
  
  # Enable this to store tags in a separate table with foreign keys
  tags_as_foreign_keys = true
  
  # Optional: You can customize the suffix for the tag table name
  tag_table_suffix = "_tags"
  timestamp_column_name = "time"
  
  create_templates = [
    '''CREATE TABLE {{ .table }} ({{ .columns }})''',
  ] 
Any guidance would be very much appreciated. Thanks.
2

Have you checked our official template suggestion?

[[outputs.postgresql]]
  connection = "host=localhost user=your_user password=your_password dbname=your_db sslmode=disable"
  schema = "public"  # or your preferred schema
  tags_as_foreign_keys = true
  create_templates = [
    '''CREATE TABLE {{ .table }} ({{ .columns }})''',
    '''SELECT create_hypertable({{ .table|quoteLiteral }}, by_range('time', INTERVAL '1 week'), if_not_exists => true)''',
  ]
3

I did look at the template but the screenshot below suggests it would create a wide table

image
image875×203 23.5 KB
which is something I would like to avoid. It’s not ideal when I have thousands of different metrics.

4

Hi @karibi, I’d recommend you to check with Telegraf folks, because it seems more in the way it’s streaming the info. If you got any new info, please, also share in the thread it can be useful for further users.