Issues connecting Google Data Studio to BigQuery with window function

mister_b 0
Viewed 64

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 this question.

I am running effectively the query from that post, saved as a view and then connected to Data Studio.

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) 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 
  QUALIFY 1 = ROW_NUMBER() OVER(PARTITION BY order_id, order_date, item_sku ORDER BY effective_date DESC)
)
GROUP BY order_id, order_date

This query uses a window function and it is this that is causing my issue. Whenever I try to connect to the data I get this.

Data Set Configuration Error

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?

google-data-studio google-bigquery
1 Answers

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
mister_b0
Related