I have written this query in both standard and legacy SQL but I keep getting different errors ranging from Syntax Error or that it can't even find the table. I have tried this in Tableau's Custom SQL data connector and the Web UI and get the same syntax error. I am trying to query a year's worth of Google Analytics tables, but am getting an error like this for standard SQL:
Syntax error: Unexpected string literal '93868086.ga_sessions_*' at [1:244]
I am not too concerned about the Legacy SQL because I think the tables I am trying to query doesn't like it. I am just confused why it doesn't expect a string when common syntax is to add the table as a string. Am I doing something wrong? I usually write in Legacy SQL so I wouldn't be surprised if I am missing something. Any help would be appreciated.
Standard SQL:
SELECT
date,
channelGrouping,
geoNetwork.networkLocation,
device.browserVersion,
hits.dataSource,
device,
hits.page,
SUM(totals.timeOnSite),
SUM(totals.visits),
SUM(totals.bounces)
FROM
'93868086.ga_sessions_*'
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY
date ASC
BigQuery Standard SQL requires backticks around the table name if it is not conform to [A-Za-z_][A-Za-z_0-9]*
regex
So, you need to use like below
FROM `93868086.ga_sessions_*`
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With