Mixing
InfluxDB: INTO with multiple measurement
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I'm trying to write aggregated result from two measurements into a single measurement.
I found on documentation that you can write multiple matching measurements with :MEASUREMENT keyword in INTO query. Like
SELECT * INTO "copy_NOAA_water_database"."autogen".:MEASUREMENT FROM
"NOAA_water_database"."autogen"./.*/
What I'm trying to do is aggregate from multiple measurements and write result to a single measurement.
SELECT mean("water_level") INTO
"copy_NOAA_water_database"."autogen"."water_agg" FROM
"NOAA_water_database"."autogen"./.*/ GROUP BY time(15m), *
The above query runs successfully, but I'm not sure whether influx has considered points from all measurement of NOAA_water_database or just last appearing measurement is considered.
influxdb
asked Jan 3 at 5:48
Hardik SondagarHardik Sondagar
2,45731936
add a comment |
I'm trying to write aggregated result from two measurements into a single measurement.
I found on documentation that you can write multiple matching measurements with :MEASUREMENT keyword in INTO query. Like
SELECT * INTO "copy_NOAA_water_database"."autogen".:MEASUREMENT FROM
"NOAA_water_database"."autogen"./.*/
What I'm trying to do is aggregate from multiple measurements and write result to a single measurement.
SELECT mean("water_level") INTO
"copy_NOAA_water_database"."autogen"."water_agg" FROM
"NOAA_water_database"."autogen"./.*/ GROUP BY time(15m), *
The above query runs successfully, but I'm not sure whether influx has considered points from all measurement of NOAA_water_database or just last appearing measurement is considered.
influxdb
asked Jan 3 at 5:48
Hardik SondagarHardik Sondagar
2,45731936
add a comment |
I'm trying to write aggregated result from two measurements into a single measurement.
I found on documentation that you can write multiple matching measurements with :MEASUREMENT keyword in INTO query. Like
SELECT * INTO "copy_NOAA_water_database"."autogen".:MEASUREMENT FROM
"NOAA_water_database"."autogen"./.*/
What I'm trying to do is aggregate from multiple measurements and write result to a single measurement.
SELECT mean("water_level") INTO
"copy_NOAA_water_database"."autogen"."water_agg" FROM
"NOAA_water_database"."autogen"./.*/ GROUP BY time(15m), *
The above query runs successfully, but I'm not sure whether influx has considered points from all measurement of NOAA_water_database or just last appearing measurement is considered.
influxdb
asked Jan 3 at 5:48
Hardik SondagarHardik Sondagar
2,45731936
I'm trying to write aggregated result from two measurements into a single measurement.
I found on documentation that you can write multiple matching measurements with :MEASUREMENT keyword in INTO query. Like
SELECT * INTO "copy_NOAA_water_database"."autogen".:MEASUREMENT FROM
"NOAA_water_database"."autogen"./.*/
What I'm trying to do is aggregate from multiple measurements and write result to a single measurement.
SELECT mean("water_level") INTO
"copy_NOAA_water_database"."autogen"."water_agg" FROM
"NOAA_water_database"."autogen"./.*/ GROUP BY time(15m), *
The above query runs successfully, but I'm not sure whether influx has considered points from all measurement of NOAA_water_database or just last appearing measurement is considered.
influxdb
influxdb
asked Jan 3 at 5:48
Hardik SondagarHardik Sondagar
2,45731936
asked Jan 3 at 5:48
Hardik SondagarHardik Sondagar
2,45731936
asked Jan 3 at 5:48
Hardik SondagarHardik Sondagar
2,45731936
asked Jan 3 at 5:48
Hardik SondagarHardik Sondagar
2,45731936
asked Jan 3 at 5:48
Hardik SondagarHardik Sondagar
2,45731936
2,45731936
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Q: I'm not sure whether influx has considered points from all measurement of NOAA_water_database or just last appearing measurement is considered.
A: I suspect influxdb is not aggregating the data from your measurements.
I think it is only aggregating the data from each measurement individually and then for each output write it to your specified measurement and since the resolved time of the mean operation can possibly be the same, measurement B's result can overwrite measurement A's result.
I derived this theory by doing an experiment using the following dataset;
INSERT cpu,host=serverA value=10
INSERT cpu,host=serverA value=20
INSERT cpu2,host=serverA value=5
INSERT cpu2,host=serverA value=15
Doing a SELECT statement similar to your query above returns;
select * FROM "historian"."autogen"./cpu.*/
name: cpu
time host value
---- ---- -----
1546511130857357196 serverA 10
1546511132744883738 serverA 20
name: cpu2
time host value
---- ---- -----
1546511156629403118 serverA 5
1546511157888695746 serverA 15
Then instead of using mean I do sum to find test the behaviour of influxdb.
I also simplified the query by dropping the groupBy operation.
Doing a sum gives me;
SELECT sum("value") INTO test_sum FROM "historian"."autogen"./.*/
name: result
time written
---- -------
0 2
> select * from test_sum;
name: test_sum
time sum
---- ---
0 20
Theory: if influx is aggregating the data from all measurements, the sum result would not be 20. It should be 50. The only way 20 can be derived is from by summing 5 + 15 which is the data from the last measurement.
But when we do the sum operation, influx did told us 2 rows were written. My theory to this is that, the influx did calculate the sum of the first measurement however as first and second summation's result time is both 0 therefore the 2nd measurement's result would have overwritten the first result's.
Recommended solution:
The best tool to do this job is actually influxdb's kapacitor. It is a great tool because it is fast however it is also extremely to learn.
Alternatively if your dataset isn't huge which I suspect it should be alright since you are grouping by 15m. You can write a script in your favourite programming language to read out the data, do the mean and then write the data back to influxdb.
answered Jan 3 at 11:13
Samuel TohSamuel Toh
8,58331219
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54016955%2finfluxdb-into-with-multiple-measurement%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Q: I'm not sure whether influx has considered points from all measurement of NOAA_water_database or just last appearing measurement is considered.
A: I suspect influxdb is not aggregating the data from your measurements.
I think it is only aggregating the data from each measurement individually and then for each output write it to your specified measurement and since the resolved time of the mean operation can possibly be the same, measurement B's result can overwrite measurement A's result.
I derived this theory by doing an experiment using the following dataset;
INSERT cpu,host=serverA value=10
INSERT cpu,host=serverA value=20
INSERT cpu2,host=serverA value=5
INSERT cpu2,host=serverA value=15
Doing a SELECT statement similar to your query above returns;
select * FROM "historian"."autogen"./cpu.*/
name: cpu
time host value
---- ---- -----
1546511130857357196 serverA 10
1546511132744883738 serverA 20
name: cpu2
time host value
---- ---- -----
1546511156629403118 serverA 5
1546511157888695746 serverA 15
Then instead of using mean I do sum to find test the behaviour of influxdb.
I also simplified the query by dropping the groupBy operation.
Doing a sum gives me;
SELECT sum("value") INTO test_sum FROM "historian"."autogen"./.*/
name: result
time written
---- -------
0 2
> select * from test_sum;
name: test_sum
time sum
---- ---
0 20
Theory: if influx is aggregating the data from all measurements, the sum result would not be 20. It should be 50. The only way 20 can be derived is from by summing 5 + 15 which is the data from the last measurement.
But when we do the sum operation, influx did told us 2 rows were written. My theory to this is that, the influx did calculate the sum of the first measurement however as first and second summation's result time is both 0 therefore the 2nd measurement's result would have overwritten the first result's.
Recommended solution:
The best tool to do this job is actually influxdb's kapacitor. It is a great tool because it is fast however it is also extremely to learn.
Alternatively if your dataset isn't huge which I suspect it should be alright since you are grouping by 15m. You can write a script in your favourite programming language to read out the data, do the mean and then write the data back to influxdb.
answered Jan 3 at 11:13
Samuel TohSamuel Toh
8,58331219
add a comment |
Q: I'm not sure whether influx has considered points from all measurement of NOAA_water_database or just last appearing measurement is considered.
A: I suspect influxdb is not aggregating the data from your measurements.
I think it is only aggregating the data from each measurement individually and then for each output write it to your specified measurement and since the resolved time of the mean operation can possibly be the same, measurement B's result can overwrite measurement A's result.
I derived this theory by doing an experiment using the following dataset;
INSERT cpu,host=serverA value=10
INSERT cpu,host=serverA value=20
INSERT cpu2,host=serverA value=5
INSERT cpu2,host=serverA value=15
Doing a SELECT statement similar to your query above returns;
select * FROM "historian"."autogen"./cpu.*/
name: cpu
time host value
---- ---- -----
1546511130857357196 serverA 10
1546511132744883738 serverA 20
name: cpu2
time host value
---- ---- -----
1546511156629403118 serverA 5
1546511157888695746 serverA 15
Then instead of using mean I do sum to find test the behaviour of influxdb.
I also simplified the query by dropping the groupBy operation.
Doing a sum gives me;
SELECT sum("value") INTO test_sum FROM "historian"."autogen"./.*/
name: result
time written
---- -------
0 2
> select * from test_sum;
name: test_sum
time sum
---- ---
0 20
Theory: if influx is aggregating the data from all measurements, the sum result would not be 20. It should be 50. The only way 20 can be derived is from by summing 5 + 15 which is the data from the last measurement.
But when we do the sum operation, influx did told us 2 rows were written. My theory to this is that, the influx did calculate the sum of the first measurement however as first and second summation's result time is both 0 therefore the 2nd measurement's result would have overwritten the first result's.
Recommended solution:
The best tool to do this job is actually influxdb's kapacitor. It is a great tool because it is fast however it is also extremely to learn.
Alternatively if your dataset isn't huge which I suspect it should be alright since you are grouping by 15m. You can write a script in your favourite programming language to read out the data, do the mean and then write the data back to influxdb.
answered Jan 3 at 11:13
Samuel TohSamuel Toh
8,58331219
add a comment |
Q: I'm not sure whether influx has considered points from all measurement of NOAA_water_database or just last appearing measurement is considered.
A: I suspect influxdb is not aggregating the data from your measurements.
I think it is only aggregating the data from each measurement individually and then for each output write it to your specified measurement and since the resolved time of the mean operation can possibly be the same, measurement B's result can overwrite measurement A's result.
I derived this theory by doing an experiment using the following dataset;
INSERT cpu,host=serverA value=10
INSERT cpu,host=serverA value=20
INSERT cpu2,host=serverA value=5
INSERT cpu2,host=serverA value=15
Doing a SELECT statement similar to your query above returns;
select * FROM "historian"."autogen"./cpu.*/
name: cpu
time host value
---- ---- -----
1546511130857357196 serverA 10
1546511132744883738 serverA 20
name: cpu2
time host value
---- ---- -----
1546511156629403118 serverA 5
1546511157888695746 serverA 15
Then instead of using mean I do sum to find test the behaviour of influxdb.
I also simplified the query by dropping the groupBy operation.
Doing a sum gives me;
SELECT sum("value") INTO test_sum FROM "historian"."autogen"./.*/
name: result
time written
---- -------
0 2
> select * from test_sum;
name: test_sum
time sum
---- ---
0 20
Theory: if influx is aggregating the data from all measurements, the sum result would not be 20. It should be 50. The only way 20 can be derived is from by summing 5 + 15 which is the data from the last measurement.
But when we do the sum operation, influx did told us 2 rows were written. My theory to this is that, the influx did calculate the sum of the first measurement however as first and second summation's result time is both 0 therefore the 2nd measurement's result would have overwritten the first result's.
Recommended solution:
The best tool to do this job is actually influxdb's kapacitor. It is a great tool because it is fast however it is also extremely to learn.
Alternatively if your dataset isn't huge which I suspect it should be alright since you are grouping by 15m. You can write a script in your favourite programming language to read out the data, do the mean and then write the data back to influxdb.
answered Jan 3 at 11:13
Samuel TohSamuel Toh
8,58331219
Q: I'm not sure whether influx has considered points from all measurement of NOAA_water_database or just last appearing measurement is considered.
A: I suspect influxdb is not aggregating the data from your measurements.
I think it is only aggregating the data from each measurement individually and then for each output write it to your specified measurement and since the resolved time of the mean operation can possibly be the same, measurement B's result can overwrite measurement A's result.
I derived this theory by doing an experiment using the following dataset;
INSERT cpu,host=serverA value=10
INSERT cpu,host=serverA value=20
INSERT cpu2,host=serverA value=5
INSERT cpu2,host=serverA value=15
Doing a SELECT statement similar to your query above returns;
select * FROM "historian"."autogen"./cpu.*/
name: cpu
time host value
---- ---- -----
1546511130857357196 serverA 10
1546511132744883738 serverA 20
name: cpu2
time host value
---- ---- -----
1546511156629403118 serverA 5
1546511157888695746 serverA 15
Then instead of using mean I do sum to find test the behaviour of influxdb.
I also simplified the query by dropping the groupBy operation.
Doing a sum gives me;
SELECT sum("value") INTO test_sum FROM "historian"."autogen"./.*/
name: result
time written
---- -------
0 2
> select * from test_sum;
name: test_sum
time sum
---- ---
0 20
Theory: if influx is aggregating the data from all measurements, the sum result would not be 20. It should be 50. The only way 20 can be derived is from by summing 5 + 15 which is the data from the last measurement.
But when we do the sum operation, influx did told us 2 rows were written. My theory to this is that, the influx did calculate the sum of the first measurement however as first and second summation's result time is both 0 therefore the 2nd measurement's result would have overwritten the first result's.
Recommended solution:
The best tool to do this job is actually influxdb's kapacitor. It is a great tool because it is fast however it is also extremely to learn.
Alternatively if your dataset isn't huge which I suspect it should be alright since you are grouping by 15m. You can write a script in your favourite programming language to read out the data, do the mean and then write the data back to influxdb.
answered Jan 3 at 11:13
Samuel TohSamuel Toh
8,58331219
answered Jan 3 at 11:13
Samuel TohSamuel Toh
8,58331219
answered Jan 3 at 11:13
Samuel TohSamuel Toh
8,58331219
answered Jan 3 at 11:13
Samuel TohSamuel Toh
8,58331219
8,58331219
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54016955%2finfluxdb-into-with-multiple-measurement%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
