Biqguery table splits data into date tables in tableau

Question

I connected bigquery with tableau and have just one dataset"analytics_217037102' . But in tableau my dataset is splitted into different tables and in each table data is arranged datewise.e.g events20201101 as one table, 20201001 as 2nd table. Here goes the image of dataset and tables

I can't select all splitted tables together and put filters on different dates. I can select one table at a time e.g if I select event table 20200101, then I can just see graphs and data of this table, I can't select tables together. Moreover, tables doesn't have any connection with each other. They contain different data on each table but same columns and fields type. Help me to make it one dataset, so that I can select all data together and put filters on different dates.

I am not running any query. I connected bigquery with tableau and my dataset is divided into tables with different dates. e.g table20200101 contains january 01 data, table20201105 contains November 5th day data. Due to this reason I can't select all tables and apply SQL query on all tables together — Hafsa Saleem

astentx astentx · Accepted Answer · 2020-11-30T05:27:44

It is because in the source they are really different tables. You need to combine data from them, for example, with custom SQL via UNION or by wildcard table syntax, or union the data with assistant, or by creating view on UNION and using it as a source table.

UPD: If you use UNION option in Tableau or in database view, then you'll need to update the query every time new table added to source, so it is not good for tables that are added frequently. For this scenario you need a custom SQL with wildcart table syntax, which will handle new tables for you. You can also restrict table accessed with where clause on _TABLE_SUFFIX column (for example where _TABLE_SUFFIX between '<low_value>' and '<high_value>' or where _TABLE_SUFFIX like '2020%') to limit accessed tables via Tableau parameters. But this feature uses the result structure of the most recent table, so it should be considered in design.

Example:

select table_schema, table_name, creation_time
from TEST.INFORMATION_SCHEMA.TABLES
+-----+--------------+------------+-----------------------------+
| Row | table_schema | table_name |        creation_time        |
+-----+--------------+------------+-----------------------------+
|   1 | TEST         | TEST_03    | 2020-11-30 14:05:18.440 UTC |
|   2 | TEST         | TEST_02    | 2020-11-30 13:27:24.036 UTC |
+-----+--------------+------------+-----------------------------+

select *
from `TEST.TEST_*`
where _TABLE_SUFFIX = '02'
+-----+------+----+
| Row |  Q   | ID |
+-----+------+----+
|   1 | null |  4 |
|   2 | null |  3 |
+-----+------+----+

select *
from `TEST.TEST_02`
+-----+----+------+------------+-----+----------+
| Row | ID | NAME |     DT     | VAL | NewField |
+-----+----+------+------------+-----+----------+
|   1 |  4 | QWE4 | 2020-02-10 |  40 | null     |
|   2 |  3 | QWE3 | 2020-02-20 |  30 | null     |
+-----+----+------+------------+-----+----------+

Biqguery table splits data into date tables in tableau

1 Answers