Using FORMAT_DATETIME with Tableau and BigQuery

Question

I'm connecting to BigQuery to get information for a Sankey Diagram in Tableau. However, I am getting this information from 2 different datasets: "audience exited" and "audience entered". I am using the User IDs and the original timestamps to join the 2 datasets. However, the timestamps are in a datetime format and those times do not coincide with each other across datasets given that a user can exit an audience at 2 a.m and only enter a new audience at 4 a.m. Hence, I am using "FORMAT_DATETIME" to remove the time on the original timestamps i.e: from "2021/07/07 23:32" to "2021-Jul-7" as shown in the SQL code below:

SELECT `audience_exited`.`active_customers` AS `active_customers`,
  `audience_exited`.`audience_key` AS `audience_key`,
  FORMAT_DATETIME("%Y-%b-%d",auience_exited`.`original_timestamp`) AS `original_timestamp`,
  `audience_exited`.`received_at` AS `received_at`,
  `audience_exited`.`user_id` AS `user_id`,
  `audience_entered`.`active_customers` AS `active_customers__audience_entered_`,
  `audience_entered`.`audience_key` AS `audience_key__audience_entered_`,
  FORMAT_DATETIME("%Y-%b-%d",`audience_entered`.`original_timestamp`) AS `original_timestamp__audience_entered_`,
  `audience_entered`.`received_at` AS `received_at__audience_entered_`,
  `audience_entered`.`user_id` AS `user_id__audience_entered_`,
  "audience_key" AS Vizside
FROM `dial-a-delivery-ke.personas_personas_prod`.`audience_exited` `audience_exited`
  FULL JOIN `dial-a-delivery-ke.personas_personas_prod`.`audience_entered` `audience_entered` ON ((`audience_exited`.`user_id` = `audience_entered`.`user_id`) AND (`audience_exited`.`original_timestamp` = `audience_entered`.`original_timestamp`))

I get the following error when I run it in Tableau:

An error occurred while communicating with the data source

Error Code: 015CFBE6 The Google BigQuery service was unable to compile the query. Syntax error: Expected ")" but got identifier . at [5:46]

And I do not know what to make of this since everything seems fine to me. Please can you assist with this error?

Double quotes delimit names, not string literals. So you should probably use '%Y-%b-%d'. However, if you just want to get from a datetime to its date, I'd suggest to use the DATE function: date(audience_entered.original_timestamp). — Thorsten Kettner
But shouldn't this be put in the ON clause where you compare the dates, rather than in the SELECT clause? Or have I misunderstood the issue? — Thorsten Kettner
On a side note: I consider almost all the backticks just noise in the query. You can remove them. Some names though, require them (e.g. dial-a-delivery-ke, because without backticks these would be considered four separate column names and some subtractions). But there I'd rather change the names in the database, so they don't require thse ugly backticks. At last: Do you really need a full outer join? You want to select both people that came and never left and others that left and never came? — Thorsten Kettner
@ThorstenKettner, awesome, thank you so much. The "Date" option fixed my issue, I don't know why I hadn't thought of that to be honest. Kindly make your comment an answer so I can accept it. — Dylan Ndengu
I don't have much time. I suggest you write an answer and post your final working query there and then accept your own answer, so it may help future readers with the same problem. — Thorsten Kettner

Mohammad Mohammad · Accepted Answer · 2021-07-29T16:00:25


TRY BELOW CODE

SELECT `audience_exited`.`active_customers` AS `active_customers`,
  `audience_exited`.`audience_key` AS `audience_key`,
  FORMAT_DATETIME("%Y-%b-%d",`auience_exited`.`original_timestamp`) AS `original_timestamp`,
  `audience_exited`.`received_at` AS `received_at`,
  `audience_exited`.`user_id` AS `user_id`,
  `audience_entered`.`active_customers` AS `active_customers__audience_entered_`,
  `audience_entered`.`audience_key` AS `audience_key__audience_entered_`,
  FORMAT_DATETIME("%Y-%b-%d",`audience_entered`.`original_timestamp`) AS `original_timestamp__audience_entered_`,
  `audience_entered`.`received_at` AS `received_at__audience_entered_`,
  `audience_entered`.`user_id` AS `user_id__audience_entered_`,
  "audience_key" AS Vizside
FROM `dial-a-delivery-ke.personas_personas_prod`.`audience_exited` `audience_exited`
  FULL JOIN `dial-a-delivery-ke.personas_personas_prod`.`audience_entered` `audience_entered` ON ((`audience_exited`.`user_id` = `audience_entered`.`user_id`) AND (`audience_exited`.`original_timestamp` = `audience_entered`.`original_timestamp`))

Using FORMAT_DATETIME with Tableau and BigQuery

1 Answers