如何使用 SQL 在 BigQuery 中查询 BYTES 字段? [英] How to query a BYTES field in BigQuery using SQL?

问题描述

我们对 Google BigQuery 表中的一个字段进行了 SHA256 加密，结果类型为 BYTES.

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=

如何在 BigQuery 中编写 SQL 查询来查询 BYTES 字段?

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

更新:现在我们使用下面的 Gordon 回答运行查询，但即使字节码与 BigQuery 显示的内容完全匹配，它也返回零结果.我们还需要做些什么来匹配字节吗?

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?

推荐答案

我认为 UI 显示的是字节列的基本 64 转义内容，因为它们无法呈现为 UTF-8.您需要使用例如

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

这篇关于如何使用 SQL 在 BigQuery 中查询 BYTES 字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！