Are you pasting the query into the Custom SQL part of Tableau data connection window? If so, ensure:
-You are using the latest version of Tableau Desktop
-You haven't checked "Legacy SQL"
Also, I would consider removing the WITH part of your query so that your query looks something like this:
select parse_date("%Y%m%d",date) as GA_date
,COUNT(DISTINCT CONCAT(CAST(fullvisitorid AS string),CAST(visitid AS string))) AS no_sessions
, unnest(hits) as hits
where _TABLE_SUFFIX between
FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)) AND
FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
and totals.visits = 1 --The value is null if there are no interaction events in the session.
and hits.page.hostname is null
group by 1
In saying this, Custom SQL can lead to performance issues. I would recommend saving your SQL query as a BigQuery View and then connecting to said View instead.
Hope this helps!