CODEWITHSUNDEEP
joingoogle-bigqueryleft-join
Iren Ramadhan
Iren Ramadhan

Reputation: 339

LEFT JOIN using _PARTITIONDATE

I'm currently using StandardSQL in BigQuery, I tried to join two sets of table one of which is a pseudo-column table partitioned by day.

I tried to use this query below:

SELECT 
        DISTINCT DATE(create_time) AS date,
        user_id,
        city_name,
        transaction_id,
        price

FROM 
        table_1 a

LEFT JOIN (SELECT user_id, city_name FROM table_2) b 
        ON (a.user_id = b.user_id AND DATE(create_time) = _PARTITIONDATE)

I've tried this kind of JOIN (using _PARTITIONDATE) and worked out, but for this particular query I got an error message:

Unrecognized name: _PARTITIONDATE

Can anyone tell me why this happened, and how could I solve this? Thanks in advance.

Upvotes: 0

Views: 647

Answers (1)

Guillem Xercavins
Guillem Xercavins

Reputation: 7058

The issue is that you are not selecting the _PARTITIONDATE field from table_2 when joining it so it can't recognize it:

SELECT user_id, city_name FROM table_2

In order to solve it you can add it as follows:

SELECT 
        DISTINCT DATE(create_time) AS date,
        user_id,
        city_name,
        transaction_id,
        price

FROM 
        table_1 a

LEFT JOIN (SELECT _PARTITIONDATE AS pd, user_id, city_name FROM table_2) b 
        ON (a.user_id = b.user_id AND DATE(create_time) = pd)

Note that you'll need an alias such as pd as it's a pseudocolumn

Probably it was working in the past if you were joining two tables directly such as in (you don't get selectivity benefits in that case):

FROM 
        table_1 a
LEFT JOIN table_2 b 
        ON (a.user_id = b.user_id AND DATE(create_time) = _PARTITIONDATE)

Upvotes: 1

Related Questions