Allowed values for hash_datatype?

[mjahammel]

Are there allowed values for the variable hash_datatype other than STRING? I tried using BINARY (target is Snowflake), and am getting a couple of different errors, depending on the hash type selected. If the variable hash is set to SHA1 or SHA2, the error messages are "Invalid argument types for function 'LOWER': (BINARY(20))", "Invalid argument types for function 'LOWER': (BINARY(64))", respectively.

An example of the generated code for a hashkey is:

IFNULL(LOWER(SHA2_BINARY(NULLIF(CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(UPPER(CONCAT(
        IFNULL((CONCAT('\"', REPLACE(REPLACE(REPLACE(TRIM(CAST("BRANCH_CODE" AS STRING)), '\\', '\\\\'), '"', '\"'), '^^', '--'), '\"')), '^^')
        )), '\n', '')
        , '\t', '')
        , '\v', '')
        , '\r', '') AS STRING), '^^'))), 'TO_BINARY(0000000000000000000000000000000000000000000000000000000000000000)') AS DV_HASHKEY_HUB_BRANCH_TEST

The problem seems to be the call to LOWER immediately after the IFNULL. Also, the default value seems to be incorrect. Instead of:

'TO_BINARY(0000000000000000000000000000000000000000000000000000000000000000)'

should it not be:

TO_BINARY('0000000000000000000000000000000000000000000000000000000000000000')

Thanks,
Maury

[tkirschke]

Hi @mjahammel and thanks for reaching out!

Are there allowed values for the variable hash_datatype other than STRING? I tried using BINARY (target is Snowflake)

In theory, the variable "hash_datatype" is exactly for that, to define another output datatype for hash values. So it should be able to insert "BINARY".

But I checked it again, and it isn't working properly for me as well. The problems are the two things you mentioned above. The LOWER shouldn't be there, in case of BINARY, and the default value is incorrect.

I will create an issue out of this discussion and try to fix it ASAP.

Kind regards,
Tim

[tkirschke]

Hi @mjahammel,

I have fixed this problem and included it in the latest release.

This should fix the problem! Let me know if it works for you, for me it did 😸

Kind regards,
Tim

[mjahammel]

I downloaded the hotfix was able to use BINARY as the hash data type. Thanks.

However, I ran into another instance where quotes are placed around the TO_BINARY function. I tried building a PIT based on a Snap v1 model and it generated this:

COALESCE(sat_ovtn_member_bv.DV_HASHKEY_HUB_MEMBER, CAST('TO_BINARY('0000000000000000000000000000000000000000000000000000000000000000')' AS BINARY)) AS hk_sat_ovtn_member_bv

which results in the error:

14:33:43    001003 (42000): SQL compilation error:
14:33:43    syntax error line 41 at position 80 unexpected '0000000000000000000000000000000000000000000000000000000000000000'.

[tkirschke]

Hi @mjahammel ,

Great to hear that the hotfix worked for you!
The problem that you have mentioned is the logical consequence of my fix, without thinking about where the zero keys are used additionally. Completely forgot the PIT macros 😄

Will create one more Hotfix for that ASAP!

Kind regards,
Tim

[tkirschke]

Hi @mjahammel ,

We finally pushed the PR through, and the problem should be fixed now! Check out the latest release here!

[mjahammel]

The latest release fixes the problem with PITs. My small data vault prototype build now runs to completion. I haven't tried a bridge, though. :)

[bschlottfeldt]

Nice to hear! We are thinking on maybe developing a Bridge Macro as well for the package. Keep tuned :)