CODEWITHSUNDEEP
sqlgoogle-bigquery
Joe Scotto
Joe Scotto

Reputation: 10877

How to limit datasets using _table_suffix on complex query?

I understand how _TABLE_SUFFIX works and have successfully used it before on simpler queries. I'm currently trying to build an application that will get active users from 100+ datasets but have been running into resource limits. In order to bypass these resource limits I'm going to loop and run the query multiple times and limit how much it selects at once using _TABLE_SUFFIX.

Here is my current query:

WITH allTables AS (SELECT
  app,
  date,
  SUM(CASE WHEN period = 30  THEN users END) as days_30
FROM (
  SELECT
    CONCAT(user_dim.app_info.app_id, ':', user_dim.app_info.app_platform) as app,
    dates.date as date,
    periods.period as period,
    COUNT(DISTINCT user_dim.app_info.app_instance_id) as users
  FROM `table.app_events_*` as activity
    WHERE _TABLE_SUFFIX BETWEEN '20170101' AND '20170502'
    OR _TABLE_SUFFIX BETWEEN 'intraday_20170101' AND 'intraday_20170502'
  CROSS JOIN
    UNNEST(event_dim) AS event
  CROSS JOIN (
    SELECT DISTINCT
      TIMESTAMP_TRUNC(TIMESTAMP_MICROS(event.timestamp_micros), DAY, 'UTC') as date
    FROM `table.app_events_*`
    WHERE _TABLE_SUFFIX BETWEEN '20170101' AND '20170502'
    OR _TABLE_SUFFIX BETWEEN 'intraday_20170101' AND 'intraday_20170502'

    CROSS JOIN
        UNNEST(event_dim) as event) as dates
    CROSS JOIN (
      SELECT
        period
      FROM (
        SELECT 30 as period
      )
    ) as periods
    WHERE
      dates.date >= TIMESTAMP_TRUNC(TIMESTAMP_MICROS(event.timestamp_micros), DAY, 'UTC')
    AND
      FLOOR(TIMESTAMP_DIFF(dates.date, TIMESTAMP_MICROS(event.timestamp_micros), DAY)/periods.period) = 0
    GROUP BY 1,2,3
  )
  GROUP BY 1,2) 
SELECT
 app as target,
 UNIX_SECONDS(date) as datapoint_time,
 SUM(days_30) as datapoint_value
FROM allTables
WHERE date >= TIMESTAMP_ADD(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP, Day, 'UTC'), INTERVAL -30 DAY)
GROUP BY date,1
ORDER BY date ASC

This currently gives me:

Error: Syntax error: Expected ")" but got keyword CROSS at [14:3]

So my question is, how can I limit the amount of data I pull in using this query and _TABLE_SUFFIX? I feel like I'm missing something very simple here. Any help would be great, thanks!

Upvotes: 0

Views: 248

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33745

The CROSS JOIN UNNEST(event_dim) AS event (and the cross join following it) needs to come before the WHERE clause. You can read more in the query syntax documentation.

Upvotes: 2

Related Questions