CODEWITHSUNDEEP
sqlgoogle-bigquery
Praxiteles
Praxiteles

Reputation: 6030

How to query a BYTES field in BigQuery using SQL?

We have SHA256 encrypted a field in our table on Google BigQuery which left the result type as BYTES.

We have tried writing various matching field queries but none are apparently correct.

SELECT * WHERE
field LIKE '16D6M7PN3w7Cn8mJyrmrUSZY9ummMf5QCGEMuiSmSlw=' 

SELECT ...
field = '16D6M7PN3w7Cn8mJyrmrUSZY9ummMf5QCGEMuiSmSlw='

SELECT ...
field = 16D6M7PN3w7Cn8mJyrmrUSZY9ummMf5QCGEMuiSmSlw=

How does one write a SQL query in BigQuery to query BYTES fields?

Update: Now we have the query running using Gordon's answer below but it is returning zero results even though the byte code is an exact match for what BigQuery is showing. Is there something more we need to do to match bytes?

Upvotes: 7

Views: 13945

Answers (2)

Elliott Brossard
Elliott Brossard

Reputation: 33755

I think the UI is showing the base 64-escaped contents of the bytes columns, since they can't be rendered as UTF-8. You'll need to use e.g.

SELECT *
FROM T
WHERE field = FROM_BASE64('16D6M7PN3w7Cn8mJyrmrUSZY9ummMf5QCGEMuiSmSlw=');

Upvotes: 19

Gordon Linoff
Gordon Linoff

Reputation: 1270443

A bytes literal starts with b. The documentation explains this.

I think you want:

SELECT *
FROM . . .
WHERE field = b'16D6M7PN3w7Cn8mJyrmrUSZY9ummMf5QCGEMuiSmSlw='

Upvotes: 6

Related Questions