CODEWITHSUNDEEP
google-bigquery
sam123
sam123

Reputation: 119

Query on multiple tables

I am trying to create a query to pull data from 2 different tables in BQ. Both of them contain the same schema. Is it possible to do something like this in BQ?

FROM (TABLE_DATE_RANGE([567.ga_sessions_],[1234.ga_sessions_],DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))) )

Upvotes: 1

Views: 618

Answers (3)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Table wildcard functions are a convenient way in BigQuery Legacy SQL to query data from a specific set of tables.
Specific Set here is actually Dataset.
So if your tables are spread across different datasets - you will need to UNION ALL subsets that belong to different datasets.
In your case it will be union of two sets: 

SELECT <fields> 
FROM 
  TABLE_DATE_RANGE([567.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')),
  TABLE_DATE_RANGE([1234.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))

added to address latest comment:

SELECT
  Domains,
  DATE,
  EXACT_COUNT_DISTINCT(fullvisitorid) AS UniqueCustomers,
FROM (
  SELECT
    *,
    "SiteA" AS Domains,
  FROM
    TABLE_DATE_RANGE([12342.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))),
(
  SELECT
    *,
    "SiteB" AS Domains,
  FROM
    TABLE_DATE_RANGE([12311.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))),
GROUP BY
  DATE,
  Domains

Upvotes: 1

Jonathan Weber
Jonathan Weber

Reputation: 1

For Legacy SQL, use the comma as a union operator to union two separate TABLE_DATE_RANGEs, like so:

FROM 
  TABLE_DATE_RANGE([1234.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')),
  TABLE_DATE_RANGE([567.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))

Upvotes: 0

Pavan Edara
Pavan Edara

Reputation: 2305

You can do this using the _TABLE_SUFFIX pseudo column in Standard SQL.

SELECT ...
FROM `myproject.567.ga_sessions_*`
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -2 DAY)) AND FORMAT_DATE("%Y%m%d", CURRENT_DATE())
UNION ALL
SELECT ...
FROM `myproject.1234.ga_sessions_*`
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -2 DAY)) AND FORMAT_DATE("%Y%m%d", CURRENT_DATE())

In Legacy SQL, something like this:

SELECT
  <desired fields>
FROM
  TABLE_DATE_RANGE([myproject.1234], DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')),
  TABLE_DATE_RANGE([myproject.567], DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))

Upvotes: 1

Related Questions