Mogry Mogry -4 years ago 526
SQL Question

Combine / Concat columns in one new column in Google BigQuery

Somebody a hint how I can put the values of four columns into a new column in Google BigQuery? Standard SQL Dialect. Not Legacy. If I try a concat (in the select statement or as a subselect)

Concat (visitId,fullVisitorId,visitNumber) as identifier,
FROM ...

(SELECT Concat(visitId,fullVisitorId,visitNumber) as identifier FROM ...),
FROM ...

I get a error message like:
Error: No matching signature for function CONCAT for argument types: INT64, STRING, INT64. Supported signatures: CONCAT(STRING, [STRING, ...]); CONCAT(BYTES, [BYTES, ...]) at [5:3]

It would be great if someone can help me with this. Thanks.

Answer Source

Have you tried casting them before the concat:

SELECT Concat(cast(visitId as string), cast(fullVisitorId as string), cast(visitNumber as string)) as identifier,

I don't know what you want to do with the result. However, it might make more sense to put the values in to an array or a struct. Often, you don't need to combine values into a string, because more complex types are available.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download