Unpack prometheus_remote_write measure from Prometheus Remote Write Parser for SQL output

Hello, i’m currently attempting to store metrics from prometheus into mysql(as bad as that sounds), but upon attempting to run the program i came across two problems from this error:

2024-03-18T06:45:01Z E! [agent] Error writing to outputs.sql: Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘CREATE TABLE ‘prometheus_remote_write’(“timestamp” TIMESTAMP,“event_name” TEXT,"’ at line 1

  1. regular quotes and double quotes remain from parsing
  2. The Measure data, prometheus_remote_write, is treated as the table name, creating a huge create table string involving all of the metrics.

Is there a solution to both remove the quotes/convert to backquotes, and unpack the measure into many metrics, with the metric name as the table name?

Many Thanks.

Look at the strings processor to replace charachters.

I believe the table name is based on the metric name. So in order to break things up you would need to update the metric name of the metrics. If you are using the prometheus_remote_write parser in this case, which will set the metric name, then you could either use a processor along wtih hard coded updates or you could use a starlark script that renames each metric based of the key. That would however create a different table for every metric.

I’m sure there are other methods, but just a couple of ideas off the top of my head.

1 Like

Much Appreciated for the response! turns out i was a bit lacking in the reading the documentation department and the data structure is as you mentioned.

I tried using the string processor and tried every option for replacing strings with

[[processors.strings]]
[[processors.strings.replace]]
measure = “"
field_key = "

field = “"
tag = "

tag_key = “*”
old = “"”
## New value with which to replace
new = “`”
but none of the options captures the quotes, so i assume that the quotation marks resulted from the output plugin.

however, I managed to make it work by using the starlark script provided and enabling ansi mode on mysql server with
SET GLOBAL SQL_MODE=ANSI_QUOTES;

1 Like