change view ga_realtime_sessions_view_YYYYMMDD to standard sql

Question

I activated the streaming export from Google Analytics to BigQuery about a year ago but I'm having troubles changing the SQL syntax of the real-time view (ga_realtime_sessions_view_YYYYMMDD) to STANDARD SQL.

How can I change the SQL of this view? The view is defined as:

SELECT *
FROM [XXX.ga_realtime_sessions_20180424]
where exportKey in (
  SELECT exportKey
  FROM (
    SELECT
      exportKey,
      exportTimeUsec,
      MAX(exportTimeUsec) OVER (PARTITION BY visitKey) AS maxexportTimeUsec
    FROM [XXX.ga_realtime_sessions_20180424]
  )
  WHERE exportTimeUsec >= maxexportTimeUsec
);

Answer 1

You can create a standard SQL view of this form:

CREATE VIEW `XXX.ga_realtime_view` AS
SELECT
  _TABLE_SUFFIX AS suffix,
  ARRAY_AGG(t ORDER BY exportTimeUsec DESC LIMIT 1)[OFFSET(0)].*
FROM `XXX.ga_realtime_sessions_20*` AS t
GROUP BY suffix, visitKey;

This returns the latest row in accordance with exportTimeUsec for each visitKey. When querying the view, filter on the suffix corresponding to the date that you want. For example,

SELECT *
FROM `XXX.ga_realtime_view`
WHERE suffix = '180424';

This returns data from the XXX.ga_realtime_sessions_20180424 table.