I need to create a view in logical space with total 5 columns. Two columns are from source 1 and other 2 columns are from source 2 and 1 column is sequence number. This sequence number is assigned to each record to serve as a unique identifier for the record. The view at logical space is pulling values for 4 columns (mentioned above) through live connections to 2 different source systems (source 1 and source 2 as mentioned above)
Can you please help if there is a way to generate sequence number in dremio and assig to each record (a record consists of above 4 fields , 2 from each source). Another challenge is , each time I select the record from view , the sequence number assigned to each record should not change. I mean if the sequence number for record 1 with account name ABC was 101 then if I query the same record at any time later , the sequence number should be 101 only for that record at later time as well.
I was wondering to use deterministic hash function in my view logic to derive a persistent value for this surrogate key. However it seems to me that dremio does not provide hash functionality .
I tried various ways like below:
ABS(MOD(BITAND(DBMS_UTILITY.GET_HASH_VALUE(TO_CHAR(duns_number), 0, POWER(2, 30) - 1), 2147483647), 1000000)) AS record_id
ABS(MOD(BITAND(DBMS_UTILITY.GET_HASH_VALUE(CAST(c.DUNS__c AS VARCHAR), 0, CAST(POWER(2, 30) - 1 AS INT)), 2147483647), 1000000)) AS record_id
ABS(MOD(BITAND(HASH(CAST(c.DUNS__c AS VARCHAR)), 2147483647), 1000000)) AS record_id
ABS(MOD(CAST(CAST(CAST(c.DUNS__c AS VARBINARY) AS INT) & 2147483647 AS BIGINT), 1000000)) AS record_id
ABS(MOD(CAST(BITAND(CAST(CAST(c.DUNS__c AS VARBINARY) AS INT), 2147483647) AS BIGINT), 1000000)) AS record_id
ABS(MOD(CAST(CONVERT_FROM(CONVERT_TO(c.DUNS__c, ‘UTF8’), ‘INT’) & 2147483647 AS BIGINT), 1000000)) AS record_id
ABS(MOD(CAST(HASH(CAST(c.DUNS__c AS VARCHAR), ‘MD5’) AS BIGINT) % 2147483647, 1000000)) AS record_id
ABS(MOD(CAST(CAST(CONVERT_FROM(HASH(CAST(c.DUNS__c AS VARCHAR), ‘SHA-256’), ‘INT’) AS BIGINT) % 2147483647 AS BIGINT), 1000000)) AS record_id
ABS(MOD(CAST(CONVERT_FROM(CONVERT_TO(c.DUNS__c, ‘UTF8’), ‘MD5’) % 2147483647 AS BIGINT), 1000000)) AS record_id
ABS(MOD(CAST(CONVERT_FROM(TO_HEX(CONVERT_TO(c.DUNS__c, ‘MD5’)), ‘INT’) % 2147483647 AS BIGINT), 1000000)) AS record_id
None of above is working . Kindly help with what is the best approach to achieve it.