1 Reply Latest reply on Sep 20, 2018 3:18 PM by Patrick A Van Der Hyde

    BigQuery Connector not totally "partition compliant" ?

    Philippe DEQUEKER

      Hi guys,

       

      I let you know a specific comportment when we use partitioned tables in BigQuery (date column).

      I hope that someone can help me!

       

      My use case :

      My source table is partitioned by Date (basket_sales_date is a date type).

      2018-09-03 15_39_40-Google BigQuery.png2018-09-03 15_40_01-Google BigQuery.png

       

      I add it in my datasource. Tableau detect a "Datetime" for this column and affect the Date type (in data panel).

      2018-09-03 15_37_30-Clipboard.png2018-09-03 15_52_02-Tableau - Book3.png

       

       

      I create my first worksheet, just with number of records and a filter on this date (range of date).

      But ! The SQL generated by Tableau contains automatically a "cast( basket_sales_date as TIMESTAMP)".

      Example :

      SELECT SUM(CAST(1 AS INT64)) AS `sum_Number_of_Records_ok`,

        1.1000000000000001 AS `X__alias__0`

      FROM `ExampleTicket`

      WHERE (CAST(`basket_sales_date` AS TIMESTAMP) = CAST(DATE('2018-02-10') AS TIMESTAMP))

      GROUP BY 2

       

      In BigQuery, all partitions are processed (because there is a transformation on the column). If i remove (manually) the cast function in BigQuery, we process only the used partition.

      For a big table, it's a problem. The query is more costly and is executed with more long time.

       

      So my question, How to change the behavior?

       

      In this source, tips 7: zulily’s top 10 tips for self-service analytics with Google BigQuery and Tableau | Tableau Software

      Tableau seem doesn't support partitioned tables by Date and proposed "Wildcard tables" (i deduce by the screenshot but it's not written).

      In my case, is there a solution ? (with same structure table / without Wildcard tables)

       

      Others informations :

      BigQuery : Standard SQL / Partitioned table

      My Tableau Desktop version : 2018.2

      Tests with "Continuous or Discrete Date"

       

      Thanks for reading me