GoogleCloudPlatform / GoogleCloudPlatform/professional-services-data-validator
Teradata hash_sha256 UDF expects varchar but receives varbyte
- 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
Assessment
This issue has not been assessed yet.