InfluxDB：如何回填先前未测量的测量标签（如果可能的话）？ [英] InfluxDB: How to backfill measurement tags that were previously not measured (if possible at all)?

问题描述

大约一个月前，我开始使用Node-RED从智能电表记录数据，看起来像是json数据（有效负载是重要的位）：

I'm started logging data from my Smart Meter using Node-RED about a month ago, it looked like this json data (the payload is the important bit):

{
  "topic":"stat/smartmeter/all",
  "payload":"{
    \"kwh_low_tarrif\":866.696,
    \"kwh_high_tarrif\":902.156,
    \"current_tarrif\":1,
    \"current_watt_draw\":485,
    \"gas_timestamp\":1554675307000,
    \"gas_total\":326.509,
    \"kwh_combined\":1768.852
  }",
  "qos":0,
  "retain":false,
  "_topic":"stat/smartmeter/all",
  "_msgid":"db4ebc0.72b9a48"
}

此数据的问题是我做了电我的Grafana仪表板中的天然气费用计算：

The problem with this data is that I did my electrical & gas cost calculations in my Grafana dashboard:

I使用InfluxDB数据选择中的数学函数将成本硬编码到仪表板中：

I hardcoded the costs into the dashboard using a math function in the InfluxDB data selection:

您可以看到我使用的价值（或价格）是每千瓦时0.230662欧元。现在愚蠢的我从来没有想过要能够在该价格波动的多年内进行计算，因此一旦我发现了电商的公共API端点，可以在其中读取我的特定计划的价格，便将其添加到测量中，因此现在json-data看起来像这样：

There you can see I used the value (or price rather) of 0.230662 euro's per kWh of used electricity. Now silly me never thought about wanting to be able to run calculations over multiple years where this price would fluctuate, so once I discovered my electricity provider's public API endpoint where I could read out the prices for my specific plan, I added it to the measurements, so now the json-data looks like this:

{
  "topic":"stat/smartmeter/all",
  "payload":"{
    \"kwh_low_tarrif\":866.696,
    \"kwh_high_tarrif\":902.156,
    \"kwh_low_price\":0.230662,
    \"kwh_high_price\":0.230662,
    \"current_tarrif\":1,
    \"current_watt_draw\":485,
    \"current_kwh_price\":0.230662,
    \"gas_timestamp\":1554675307000,
    \"gas_total\":326.509,
    \"gas_price\":0.804565,
    \"kwh_combined\":1768.852
  }",
  "qos":0,
  "retain":false,
  "_topic":"stat/smartmeter/all",
  "_msgid":"db4ebc0.72b9a48"
}

唯一问题（以及我的主要问题）是：

The only problem (and my main questions with it) now is that:

1）如何编写在价格计算中使用该值的查询？

1) How do I write a query that uses this value in the price calculation? The query I'm using now (from the screenshot above) is:

SELECT distinct("kwh_combined")  * 0.230662 FROM "smartmeter" WHERE $timeFilter GROUP BY time($__interval) fill(linear)

2）如何回填数据？（写那些电&从记录开始就将汽油价格添加到数据库中，然后将其添加到我当时进行的测量中。）

2) How do I backfill data? (write those electric & gas prices into the database from the beginning of my logging, adding it to the measurements I took back then)

我宁愿使用以前在我的代码中硬编码的值面板设置为我已经进行的测量，而不必为不存在或空的情况编写异常。.我的意思是，由于价格没有变化，因此数据本身尽可能保持静态，因此不会那么难，对吗？即使需要重建数据，我也可以将其重新插入到新的数据集中并自己添加字段吗？

I would much rather have the values I previously had hardcoded in my panel set into the measurements I already took instead of having to write an exception for when the measurements aren't present or 'null'.. I mean, the data itself is as static as can be since the prices haven't changed, so it can't be THAT hard, can it? Even if it need to rebuild the data, can I just re'insert it into a new data collection and ADD the fields myself?

请告诉我，这对于InfluxDB是可行的...

Please tell me this is doable for InfluxDB...

我的意思是，在MySQL中，它将是一个简单的ALTER TABLE语句，可能在具有空值的记录上进行简单的插入。

I mean, in MySQL it would a simple ALTER TABLE statement with perhaps a simple insert on the records which had null values.

..还是让我要求一个时间序列日志记录系统更改已经记录的数据的数据结构是不合理的，我是否要求过多的InfluxDB？

.. or is it unreasonable for me to ask a time-series logging system to be able to alter it's data structure of already logged data and am I asking too much of InfluxDB?

推荐答案

您可以向现有的度量添加额外的字段（price_1，price_2，...）。将它们插入完全相同的时间戳和标记值，以使用准备在grafana中使用的这些新价格列扩展现有数据记录：

You can add extra fields (price_1, price_2,...) to existing measurement. Insert them with exactly same timestamp and tag values to get existing data records extended with these new price columns ready to be used in grafana:

INSERT smartmeter,tagA=tagAvalue,tagB=tagBvalue,... price1=1234,price2=4321 1549983966469023105
...

另一种方法是导出现有数据，添加额外的价格列并重新导入。

Another way would be to export existing data, add extra price columns and import back.

这两种方法看起来都不简单。
使用 select ...进入语法似乎更容易运行（没有这个丑陋的 0 * last（）+对我不起作用... hack）：

Neither way looks simple. Using select ... into syntax seems easier to run (didn't worked for me without this ugly 0*last() + ... hack):

    SELECT 
       last(kwh_combined) as kwh_combined,
       last(other_field) as other_field,
        ...add other fields here...
       0 * last(kwh_combined) + 1234 as price1,
       0 * last(kwh_combined) + 4321 as price2 
       INTO new_smartmeter
    FROM smartmeter GROUP BY *

这篇关于InfluxDB：如何回填先前未测量的测量标签（如果可能的话）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！