Bug P2
Status Update
No update yet.
Comments
bl...@google.com
ho...@google.com
Correct query would be:
SELECT
*
FROM (
SELECT
(SELECT SUM(subtotal) FROM UNNEST(order_product)) AS clmn100000_,
(SELECT SUM(inventory_value) FROM UNNEST(order_product) x, UNNEST(x.stock_move))AS clmn100001_
FROM (
SELECT
*
FROM (
SELECT
order_product,
-- stock_move.inventory_value AS clmn4_,
t0.order_key AS clmn0_
FROM
`fh-bigquery.public_dump.onerowjson_201905` t0
-- , t0.order_product,
-- order_product.stock_move
)
WHERE
clmn0_ IN (4430) ) )
LIMIT
20000000
SELECT
*
FROM (
SELECT
(SELECT SUM(subtotal) FROM UNNEST(order_product)) AS clmn100000_,
(SELECT SUM(inventory_value) FROM UNNEST(order_product) x, UNNEST(x.stock_move))AS clmn100001_
FROM (
SELECT
*
FROM (
SELECT
order_product,
-- stock_move.inventory_value AS clmn4_,
t0.order_key AS clmn0_
FROM
`fh-bigquery.public_dump.onerowjson_201905` t0
-- , t0.order_product,
-- order_product.stock_move
)
WHERE
clmn0_ IN (4430) ) )
LIMIT
20000000
el...@google.com
Did you mean to file a bug with Data Studio? I think they generate these queries that get sent to BigQuery.
ho...@google.com
Going deeper: this query seems to be generated by BI Engine. Data Studio
generates a different one (also wrong).
On Tue, May 14, 2019, 08:40 elliottb <buganizer-system+elliottb@google.com>
wrote:
generates a different one (also wrong).
On Tue, May 14, 2019, 08:40 elliottb <buganizer-system+elliottb@google.com>
wrote:
le...@gmail.com
Is there any progress on this, or any way for me to follow along with its status? Is this the correct place to follow the issue or has it been moved somewhere else?
I'm running into some issues with Data Studio because of this bug, and the workaround I was using is becoming a bit too much to maintain. It would be great if Data Studio used the correct queries.
Any idea when a fix can be expected? Thanks very much for the help.
I'm running into some issues with Data Studio because of this bug, and the workaround I was using is becoming a bit too much to maintain. It would be great if Data Studio used the correct queries.
Any idea when a fix can be expected? Thanks very much for the help.
el...@google.com
le...@gmail.com
Does anyone look at this? I'm just confused what's happening. This bug has been sitting here for weeks? Isn't it a big deal? The wrong queries are being generated which means users are getting incorrect results in Data Studio? Has anyone looked at this?
[Deleted User] <[Deleted User]>
We're seeing the same behavior—the results change depending on whether there's an active BI Engine instance.
That's far from expected behavior, and should at minimum generate some type or warning (and a report-level option to disable BI Engine)
That's far from expected behavior, and should at minimum generate some type or warning (and a report-level option to disable BI Engine)
[Deleted User] <[Deleted User]>
We have been observing the same behaviour as well. This should, as mentioned by others, at least bgenerate a warning, as this obscures the data completely
[Deleted User] <[Deleted User]>
We're using Data Studio to create custom reports against GCP's billing export to BigQuery. We rely on the labels field to break down costs by a variety of dimensions not normally available in the billing export. Unfortunately the labels field is a nested column, which means that the report double counts for each label on the record.
Please provide an update on this issue as it is making it impossible to trust Data Studio for analyzing our hosting costs.
Please provide an update on this issue as it is making it impossible to trust Data Studio for analyzing our hosting costs.
le...@gmail.com
What is going on here? Isn't this a major bug? Is DataStudio only mean to be used to query flat, unnested data? If so, shouldn't this be explained somewhere?
I reported this bug on Stack Overflow 5 months, and have yet to receive any sort of information from someone at Google. I've been resorting to creating flat view tables for Data Studio to query against rather than simply accessing my source table because it can't handle the nested data.
Please provide an update, thanks!
I reported this bug on Stack Overflow 5 months, and have yet to receive any sort of information from someone at Google. I've been resorting to creating flat view tables for Data Studio to query against rather than simply accessing my source table because it can't handle the nested data.
Please provide an update, thanks!
[Deleted User] <[Deleted User]>
Yes, this is very important to fix. It's impacting us as well. Shocked that it has taken this long. It should be a high priority bug! What's the point of nested tables if we can't fully utilise them in data studio? the whole point is for efficiency, doing more with less tables and we can't do that right now.
ho...@google.com
As a side comment, I usually avoid Data Studio with nested rows for a different reason: BI Engine doesn't accelerate nested rows either - and BI Engine does amazing things for BQ+DS dashboards.
Instead, I create flat tables just for BQ+BI+DS, and things work really well.
Instead, I create flat tables just for BQ+BI+DS, and things work really well.
[Deleted User] <[Deleted User]>
RE: #13 . . . nowhere in the BI Engine documentation does it note that nested fields aren't accelerated, and the quick start specifically mentions that you can use any public dataset to get started (no caveats for those containing nested data). Do you have any public reference for this?
I've seen case studies talking about BI Engine performance improvements that show nested data, so I'm really surprised to hear this.
I've seen case studies talking about BI Engine performance improvements that show nested data, so I'm really surprised to hear this.
ho...@google.com
Here, specifics about repeated data not being supported today, and what is
supported by BI Engine
https://cloud.google.com/bi-engine/docs/optimized-sql
On Wed, Apr 8, 2020, 20:04 <buganizer-system@google.com> wrote:
supported by BI Engine
On Wed, Apr 8, 2020, 20:04 <buganizer-system@google.com> wrote:
[Deleted User] <[Deleted User]>
Thanks for the advice, but that's not a fix to the bug :).
There are other benefits to nested tables that outweigh BI engine benefits. Such as the ability to have pre-joined data in one table, in a nested format, minimise the number of tables we hold in BQ and the number of data sources in DS. Also, I'm aware that nested tables support for BI engine is being worked on by you guys - see this issue here that I raised previously (about BI engine not optimizing)https://issuetracker.google.com/issues/146359808#comment2
But anyway, that's off-topic to what this ticket's issue is about
There are other benefits to nested tables that outweigh BI engine benefits. Such as the ability to have pre-joined data in one table, in a nested format, minimise the number of tables we hold in BQ and the number of data sources in DS. Also, I'm aware that nested tables support for BI engine is being worked on by you guys - see this issue here that I raised previously (about BI engine not optimizing)
But anyway, that's off-topic to what this ticket's issue is about
[Deleted User] <[Deleted User]>
This is a big issue for us too. We are tracking user metrics from different products and each product has its own nested column structure in the table.
I think I didn't notice it earlier because I happened to not be using more than one nested column structure per table. The unnesting of one seems to work as expected, it's when we have more than one that it becomes an issue.
I think I didn't notice it earlier because I happened to not be using more than one nested column structure per table. The unnesting of one seems to work as expected, it's when we have more than one that it becomes an issue.
bo...@rtbhouse.com
Any update on this?
[Deleted User] <[Deleted User]>
Comment has been deleted.
an...@gmail.com
Would love to see this fixed
ja...@cashea.app
Hi there, any updates on this issue?
Description
Sample table:
When producing the SUM of order_product.subtotal and stock_move.inventory_value, Data Studio UNNEST() and CROSS JOINs both nested repeated columns, producing the wrong result.
We get 527.79
It should be 247.91
Data Studio generated query:
SELECT
*
FROM (
SELECT
SUM(clmn3_) AS clmn100000_,
SUM(clmn4_) AS clmn100001_
FROM (
SELECT
*
FROM (
SELECT
order_product.subtotal AS clmn3_,
stock_move.inventory_value AS clmn4_,
t0.order_key AS clmn0_
FROM
`fh-bigquery.public_dump.onerowjson_201905` t0,
t0.order_product,
order_product.stock_move )
WHERE
clmn0_ IN (4430) ) )
LIMIT
20000000