Reading only requested number of partitions from BQ table doesn't work

[JD-V]

I am trying to query table which is partitioned by date field. Like this,

val prog_logs = spark.read.format("bigquery")
        .option("table", "project1:dataset.table")
        .option("filter", " date between '2019-09-10' and  '2019-09-11' ")
        .load()
        .cache()

This is reading entire table instead of only '2019-09-10' and '2019-09-11' partitions.

[JD-V]

@pmkc I would really appreciate if anybody can take a look at this.

[pmkc]

@davidrabinowitz is the new maintainer. Though I think we will need to consult with BigQuery Storage devs.

I have successfully used _PARTITION_DATE like that in a filter before.

[kmjung]

@JD-V I'd like to figure out what filter is actually pushed down to the storage API here. Do you have the read session ID from this query, or can you get one from a repro?

[JD-V]

I got this from very recent run, projects/mintreporting/locations/asia-southeast1/sessions/CAISCDlLRUpHSGlKGgJzZhoCc2k

@kmjung Let me know if this is helpful

[emkornfield]

Sorry for the late response but for proper push down in spark 2.4 I think you need: date between date('2019-09-10') and date('2019-09-11') (i.e. explicitly cast values to date).

[jesuejunior]

Sorry for the late response but for proper push down in spark 2.4 I think you need: date between date('2019-09-10') and date('2019-09-11') (i.e. explicitly cast values to date).

Did it work for someone?

I've to try and it always makes the table full scan.

Are there any tips to do it?

I'm using Dataproc with Spark 3+

[davidrabinowitz]

Can you please share a sample code? Also, can you please set the log level to debug and attach the output? I assume you use the latest version

[davidrabinowitz]

@jesuejunior is it still an issue? Can you please share the log of a sample app (it shows the pushed down filters)