IssueTracker

I just barely missed the cutoff for getting a SHA1 hash function (along with MD5, SHA256, and SHA512) into the BigQuery release that is currently live :( They'll be in the next one that is released probably less than a month from now, though. We also have a more general fingerprinting function coming a bit later.

While it's not nearly as ideal, you can define a SHA1 function using JavaScript if you need it sooner: https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions. Making use of https://github.com/emn178/js-sha1/blob/master/src/sha1.js and http://stackoverflow.com/questions/23190056/hex-to-base64-converter-for-javascript, you can define your own version such as in the attached example.

Alternatively, there are probably other JavaScript hashing functions that you could try if you don't care that much about the algorithm.

The SHA1 function is now available. The documentation isn't live yet, but the newly supported functions include:

* MD5
* SHA1
* SHA256
* SHA512

All of these take either STRING or BYTES as input and return BYTES as output.

The documentation is live now: https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#hash-functions

Is this a bug or pending feature? Will SHA256 conversion work in create table calls as well?

We are getting this sha256 unrecognized error:
{'create_table_error': [{u'message': u'2.15 - 2.21: Unrecognized function sha256', u'location': u'query', u'reason': u'invalidQuery'}], 'bq_table_exists': 'no'}

For this code:
{'jobReference': {'jobId': '####', 'projectId': 'our_project_id'}, 'configuration': {'query': {'destinationTable': {'datasetId': 'our_dataset_id', 'tableId': u'our_table_id', 'projectId': 'our_project_id'}, 'useLegacySQL': False, 'allowLargeResults': True, 'priority': 'INTERACTIVE', 'query': u'SELECT SHA256(EID)\r\nFROM [our_project_id:our_dataset_id.our_table] LIMIT 100'}}}

This query works in the BigQuery web query tool - but isn't working for us when submitted through our Python script. Is there some trick to getting sha256 to be recognized this way?

Hi,

Many thanks to the team for this quick integration!

@Pierre thanks for the nice feedback!

@gilliam, I believe that you need useLegacySql in the request to the API (lowercase q and l) and your table name should be `our_project_id.our_dataset_id.our_table`. See also https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql

That was it - thanks! The useLegacySql field is case sensitive and the q and l should be lowercase.

Hi,

By any chance, is the cityhash anywhere in the development roadmap ?

Thanks,

We have a different function coming based on the fingerprinting algorithm from FarmHash (https://github.com/google/farmhash). Are you looking for the CityHash hashing function for the sake of backwards-compatibility? The problem with CityHash is that its values may change as new versions are released, so it isn't great for computing an index, and it doesn't have cross-platform implementations with consistent results.

I am indeed looking fo the CityHash for backwards-compatibility. We actually use the CityHash function in Java in our back-end system and we managed to get cross-platform consistency.
We are still using the HASH function in legacy SQL for the moment, but if it isn't migrated, we will need to find an other solution to our problem.