4

I've got an almost identical scenario to this question:

How to choose the latest partition in BigQuery table?

With one additional complication. I need the result to display in Data Studio.

The setup

I've got a series of data sets which appear at different time intervals apart and I need to get the most recent partition. Because they're inconsistent time periods apart I can't just get the last day and use that.

I can use BigQuery scripting to successfully prune the queries with a dynamic query, but when I move this query into Data Studio the query doesn't load properly.

The table loads in the data sources part:

enter image description here

But when I actually try to use it in the report:

Data Studio cannot connect to your data set.

Failed to fetch data from the underlying data set

Error ID: e6546a97

Is there a way to get Data Studio to display this properly with pruning?

Example query

DECLARE max_date DATE;
SET max_date = (SELECT DATE(MAX(_partitiontime)) FROM `dataset.table`);   

SELECT *
FROM `dataset.table`
WHERE DATE(_partitiontime ) = max_date
Dominic Woodman
  • 719
  • 2
  • 8
  • 18
  • 1
    `DECLARE` statement don't work in DataStudio, because the whole custom query is wrapped in an outer select so this syntax becomes invalid `SELECT ........ FROM (DECLARE max_date .......................................)` – Pentium10 May 26 '21 at 08:00
  • Aah that makes sense. I'm surprised it even manages to figure out there is table in it all. – Dominic Woodman May 26 '21 at 10:34

2 Answers2

2

Workaround:

A possibility is to use date parameters and make a query like the following:

SELECT *
FROM `dataset.table`
WHERE DATE(_PARTITION_TIME)>= PARSE_DATE("%Y%m%d", @DS_START_DATE)

This does not precisely the answer, but with a date range defaulted to "yesterday to today" data you effectively prune your table to only recent partitions. In the case that the data is irregular as you mention it, users still have the possibility to manually extend the date range until they find the data.

In parallel, you can also add the following custom query to your data sources:

SELECT 
    MAX(SAFE.PARSE_DATE('%Y%m%d',partition_id)) AS latest_available_partition
FROM `dataset.INFORMATION_SCHEMA.PARTITIONS`
WHERE TABLE_NAME = "table"

and display it into a table to provide the information to users.

Indeed, this workaround implies that you trust your end users not to play too much with the date range.

Cylldby
  • 1,783
  • 1
  • 4
  • 17
1

You have access to all your partitions information in the project.dataset.INFORMATION_SCHEMA.PARTITIONS table. Therefore you can try something like:

SELECT *
FROM `dataset.table`
WHERE DATE(_PARTITION_TIME) = (
    SELECT 
        MAX(SAFE.PARSE_DATE('%Y%m%d',partition_id))
    FROM `dataset.INFORMATION_SCHEMA.PARTITIONS`
    WHERE TABLE_NAME = "table"
)

Make sure you understand that pruning works with the above query:

enter image description here

Pentium10
  • 204,586
  • 122
  • 423
  • 502
Cylldby
  • 1,783
  • 1
  • 4
  • 17
  • The query works, but unfortunately it does still scan all the partitions, which on some of the huge tables I've got to work with isn't going to be a great solution. – Dominic Woodman May 26 '21 at 10:33
  • @DominicWoodman are you sure it does? I'm using this trick and works nicely. – Pentium10 May 26 '21 at 14:03
  • Very strange indeed : this query works as expected on a partitioned table within my project (querying 10Mb, mainly of INFORMATION_SCHEMA, instead of the hundreds of Mb of the whole table), however when I try the same query on partitioned tables from BQ public datasets the whole table is parsed... – Cylldby May 26 '21 at 15:46
  • @Cylldby pruning does work nicely, see the picture about a large public dataset. Don't take the number that's projected on the BigQuery UI, that's using a dry-run feature and doesn't reflect pruning. Run the query and see it's pruning the partitions. – Pentium10 May 27 '21 at 19:20