CODEWITHSUNDEEP
sqlpostgresqlgoogle-bigqueryuuid
Ana Priss
Ana Priss

Reputation: 21

Query UUID with BigQuery

I need to make a query on CloudSQL (PostgreSQL). One of the columns - id - has UUID values. BigQuery does not accept this type of data, and therefore reports this error:

Invalid table-valued function EXTERNAL_QUERY PostgreSQL type UUID in column id is not supported in BigQuery. You can cast it to a supported type by PostgreSQL CAST () function. at [1:15]

However, I cannot transform the UUID type into a string (for example) to complete the suggestion that the error gives, which is to use CAST.

Here's the way I'm trying:

SELECT CAST (id as STRING) FROM EXTERNAL_QUERY (" projects / xxx-sandbox / locations / us / connections / CloudSQL "," SELECT * FROM transactions; ");

But it doesn't work, because from what I understand, the UUID is a binary number.

What would be the best way to make this transformation for this query?

Upvotes: 1

Views: 3856

Answers (1)

Sahil Jain
Sahil Jain

Reputation: 96

Here is a page that shows which type can be casted into which: https://cloud.google.com/bigquery/docs/reference/standard-sql/federated_query_functions#postgressql_mapping

For UUID, think you will have to do the cast within the query (postgres) that fetches the data. As an example, I tried the following and it worked:

SELECT * FROM EXTERNAL_QUERY("projects/xxxxxxx/locations/us-central1/connections/xxxxxx", "SELECT CAST(myuuid AS TEXT) FROM mytable;");

Upvotes: 1

Related Questions