Issues connecting Google Data Studio to BigQuery with window function-CodePudding

I have a complex BigQuery view that pulls data from various connected Google Sheets along with calculated data from within BigQuery. I'm trying to create a dashboard on top of the view in Data Studio.

I'm having an issue getting my data to show in Data Studio and have isolated it to a particular part of the underlying view in BigQuery.

I had an earlier problem that was answered by

With the details being

Data Studio cannot connect to your data set.

Failed to fetch data from the underlying data set

Removing the below line from the query solves the issue but then I don't have the desired data.

QUALIFY 1 = ROW_NUMBER() OVER(PARTITION BY order_id, order_date, item_sku ORDER BY effective_date DESC)

Is there a reason why this breaks Data Studio? Can I avoid it? Can I solve the original issue in a different way that doesn't use a window function?

UPDATE

Looks like there is an issue in Data Studio where it does not support the QUALIFY function.

Any suggestions on how I can re-write this query without using QUALIFY?

CodePudding user response：

I have fixed this by removing the QUALIFY function and re-writing my query as this

SELECT order_id, order_date,
  ARRAY_AGG(line_item) AS line_items
FROM (
  SELECT order_id, order_date,
      STRUCT(item_sku,
      item_quantity,
      item_subtotal,
      cost.product_cost,
      ROW_NUMBER() OVER(PARTITION BY order_id, order_date, item_sku ORDER BY effective_date DESC) AS row_num) AS line_item
  FROM `order_data_table`, UNNEST(line_items) AS items
  JOIN `price_history_table` AS cost
  ON items.item_sku = cost.sku AND effective_date < order_date 
)
WHERE line_item.row_num = 1
GROUP BY order_id, order_date

CodePudding user response：

As described in the issue, the workaround is to always include the WHERE clause. Without it, Data Studio gets confused and fails.

I had the same problem and adding WHERE TRUE to my queries fixed it without needing any other rewrite.