Google Big Query and Tableau

Question

I have several large tables in Google Big Query. Each table contain the same columns. The tables are split up by month. E.g. all_relevant_data_January, all_relevant_data_february etc.

I want to get all this data into Tableau to display in a dashboard but only call 1 month at a time using a filter or parameter. This is so the data for that month in question is only returned.

Can i create a custom sql connection in tableau that uses a parameter to call only the table i need. Eg.

Select * from All_relevant_data_* Where monthyear = [month parameter]

Or am i better to dump it all in 1 table and then use a parameter? eg

Select * from All_relevant_data_all_months Where monthyear = [month parameter]

If i were to do this, will it load all the data into tableau or will it only call whichever month i select in the dashboard?

I have been struggling with tabledaterange in tableau and dont know how to do this if that is an option.

Any help is greatfully received.

Thanks.

Elliott Brossard Elliott Brossard · Accepted Answer · 2017-03-14T08:56:54

It sounds like you are interested in wildcard tables. Most users who aren't taking advantage of partitioned tables have their tables set up with suffixes such as 20170101, 20170201, 20170301, etc. If this is the case for you as well, you can write a query such as:

SELECT * FROM `All_relevant_data_*`
WHERE _TABLE_SUFFIX BETWEEN '20170101' AND '20170301';

If your tables do have suffixes such as January and February, you can still make this approach work, e.g. by extracting the month number from the name:

SELECT * FROM `All_relevant_data_*`
WHERE EXTRACT(MONTH FROM PARSE_DATE('%B', _TABLE_SUFFIX)) BETWEEN
  1 AND 3;

If you want a query that returns the month column in order to filter on it separately, you can use one of these modified forms:

-- Assumes that _TABLE_SUFFIX is e.g. 20170314
SELECT *, PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) AS date
FROM `All_relevant_data_*`;

Or:

-- Assumes that _TABLE_SUFFIX is e.g. January
SELECT *, EXTRACT(MONTH FROM PARSE_DATE('%B', _TABLE_SUFFIX)) AS month_number
FROM `All_relevant_data_*`;

Google Big Query and Tableau

1 Answers