GoogleCloudPlatform / GoogleCloudPlatform/professional-services-data-validator

Teradata hash_sha256 UDF expects varchar but receives varbyte

Open
#1,314 2 comments 0 reactions 0 assignees View on GitHub
type: question
Dominant language
Python
Stars
524
Forks
171
Avg merge
5d 15h
Merged PRs (30d)
4

Description

The [DVT documentation](https://github.com/GoogleCloudPlatform/professional-services-data-validator/tree/develop?tab=readme-ov-file#row-validations) says to use [this UDF](https://github.com/akuroda/teradata-udf-sha2/blob/master/src/sha256.c) for row validations on Teradata.
The [documentation](https://github.com/akuroda/teradata-udf-sha2/blob/master/README#L14) on the UDF indicates that it should be given a varchar.

However, the [ibis_addon/operations.py RawSQL#format_hashbytes_teradata](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/third_party/ibis/ibis_addon/operations.py#L169) function is hardcoded to always invoke `TransUnicodeToUTF8` before calling the UDF:
```python
if op.how == "sha256":
return f"rtrim(hash_sha256(TransUnicodeToUTF8({arg})))"
```

The [Teradata documentation](https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/SQL-Operators-and-User-Defined-Functions/Compression/Decompression-Functions/TransUnicodeToUTF8/Result-Type) shows that `TransUnicodeToUTF8` returns a `VARBYTE(64000)`, not a varchar.

When I run a simple row validation against Teradata for a single column, it throws this error:
`[Version 20.0.0.20] [Session 240884] [Teradata Database] [Error 9881] Function 'hash_sha256' called with an invalid number or type of parameters`

Is there a way to make the conversion to UTF8 happen as a calculated field that could be removed from the config file or to add a cast back to a varchar after performing it? If the conversion and hashing weren't tightly bound I could work around it.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.