How to query a BYTES field in BigQuery using SQL?

Question

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?

Elliott Brossard · Accepted Answer · 2017-02-04 14:06:01Z

19

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=');

answered Feb 4, 2017 at 14:06

Elliott Brossard

34k2 gold badges75 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Praxiteles Over a year ago

Thx. That worked. StackOverflow - how many projects does it save every night. Seriously the best coding resource on the planet.

Gordon Linoff · Accepted Answer · 2017-02-04 13:27:57Z

6

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

I think you want:

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

answered Feb 4, 2017 at 13:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Praxiteles Over a year ago

That got the query running - but it is returning zero results. If we query the table, see the SHA256 field, copy it (like that one in the question) and query it like you have shown - BigQuery is telling us there are zero results. Is there another trick to the bytes conversion?

Collectives™ on Stack Overflow

How to query a BYTES field in BigQuery using SQL?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related