CODEWITHSUNDEEP
sqlgoogle-bigquery
Thao Ly
Thao Ly

Reputation: 33

Using CONCAT on a database table name

I want to concat the table name with the lastDay date as a string, and have this kind of results :

dl-recommendation-engine:NDA_CHANEL_137002018.ga_sessions_20200128

I found this line to get me the day of yesterday

REPLACE(CAST(DATE_SUB(DATE_TRUNC(CURRENT_DATE(), DAY), INTERVAL 1 DAY) as STRING), "-","") I casted it as a string

But now I have to concat all and it doesn't work ...

date,
transaction.transactionId, 
ref.productSKU as productRef, 
associated.productSKU as productAssociated, 
ARRAY_LENGTH(hits.product) as nbProducts

FROM CONCAT("`dl-recommendation-engine:NDA_CHANEL_137002018.ga_sessions_",
REPLACE(CAST(DATE_SUB(DATE_TRUNC(CURRENT_DATE(), DAY), INTERVAL 1 DAY) as STRING), "-",""),
"`") as session,

UNNEST(hits) AS hits,
UNNEST(hits.product) as ref,
UNNEST(hits.product) as associated

AND hits.transaction.transactionId IS NOT NULL
AND ARRAY_LENGTH(hits.product) > 2```

Upvotes: 0

Views: 177

Answers (1)

Sabri Karagönen
Sabri Karagönen

Reputation: 2365

Why don't you use _TABLE_SUFFIX?

I simplified the query by deleting unnecessary columns here:

SELECT *
FROM `dl-recommendation-engine:NDA_CHANEL_137002018.ga_sessions_*` as session,
WHERE 
_TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY))

Upvotes: 2

Related Questions