SQL Query (MySQL) returns only one entry

1

Hello,

I am new to InfluxDB, but I set it up in Docker and it worked like a charm. I use it to query a MySQL-Database with “inputs.sql” and this gives me back the value I´m looking for.

BUT: We want to do a query which should return multiple entries but only one entry appears in my bucket. A sample query could look like this:

SELECT invoice_amount
FROM customer
WHERE receipt_dat BETWEEN ‘2022-10-01’ AND ‘2022-10-31’
ORDER BY invoice_amount DESC
LIMIT 10

This returns only one value, although several results are displayed in the MySQL browser.

Is it possible to get multiple entrys? If yes how can I achive this?

Thank you very much!
Benjamin

2

Hello @Benjamin,
Welcome! Can you please share the values in your MySQL and the raw table results of your Flux query? Please look for the raw results toggle in the UI to the left of the submit button. Thank you!

3

Hello Anaisdg,

thanks for your reply. I made a short test as an example:

https://imgur.com/CfNEtBY

https://imgur.com/3KjeI8F

The first screenshot shows the output in the MySQL browser. The second screenshot shows the raw table in InfluxDB.

Only the last value of the query is stored in influxdb. But I would like to have all 10 values in InfluxDB. Is this possible or am I thinking wrong?

Benjamin

4

This is because all the values (will) have the same timestamp and you have no tag to distinguish the different values.
the first row will be an insert and the following 9 lines updates.

You can either put a proper key in you rows (otherwise it will be just timestamp) or return the total, meaning SELECT SUM(invoice_amount) FROM ...

have a look at the key concepts about tags, fields and series

5

Hi Giovanni_Luisotto,

thank you for your reply and the link. Ok I think I understand this now.

So I could use the name of the customer as a proper key, right? But how can I put this a proper key in my rows and dont use the timestamp?

For example:

SELECT customer_name, invoice_amount
FROM customer
WHERE receipt_dat BETWEEN ‘2022-10-01’ AND ‘2022-10-31’
ORDER BY invoice_amount DESC
LIMIT 10

Thanks again,
Benjamin

6

I don’t know what’s the proper key for you, as it depends strictly on data and use case.

The timestamp (autogenerated if not provided) is always part of the key, this is a time-series database after all…

about how to let’s say add the customer as a tag (and therefore part of the key) see the docs of the input plugin you are using in case of input.sql

    ## Column names containing tags
    ## An empty include list will reject all columns and an empty exclude list will not exclude any column.
    ## I.e. by default no columns will be returned as tag and the tags are empty.
    # tag_columns_include = []
    # tag_columns_exclude = []
7

Thank you Giovanni_Luisotto,

I´ll mark this as the solution.

I think I have to open a new thread. Because it works on simple queries. But with a more complicated query (with “INNER JOIN”) it only stores the last value in InfluxDB.

Thanks again,
Benjamin