Suggest an Idea

[lPrasanna]

We are looking for an option to create a column on every table we ingest and populate it with a value that would be unique within the base and change table.  

Use case:

We will be encrypting data in some columns.  To do this, we will keep the source column name and source data (stored as string) and we will create a new column to hold the encrypted data.  

Column name from source:  SSN

New column name to store encrypted data:  SSN_enc

we will run a post process job that will process each row of data and provide an encrypted version of the data as needed.  For this process to run, it requires a UUID in a column for each row

[john_wang]

Hello @lPrasanna ,

Without any User Defined Functions, the best way to generate UUID(GUID) in Replicate can be as:

lower(hex(randomblob(16)))

You may format it UUID style as "c71122df-18e4-4a78-a446-fbf7b8f2969b" by string function.

The screen copy :

Hope this helps.

Regards,

John.

[john_wang]

Hello @lPrasanna ,

A better expression with UUID format is:

lower(hex( randomblob(4)) || '-' || hex( randomblob(2))|| '-' || '4' || substr( hex( randomblob(2)), 2) || '-'|| substr('AB89', 1 + (abs(random()) % 4) , 1)  ||substr(hex(randomblob(2)), 2) || '-' || hex(randomblob(6)))

the output likes: c8eb0833-b9ff-468c-ac90-b9d4798ea208

Hope this helps.

Regards,

John.

[DrorHarari-Qlik]

It is not clear why a UUID is needed - is the goal being to use it as a salt? If so, it is much better to just use 

hex(randomblob(16))

 as the salt value and avoid the complex and costly work to create a UUID formatted text using SQL.

In any case, the proposed solution as described does not make much sense - note that this is not an opinion on whether Replicate should have a GUID() function but rather as a way to implement encryption. As described, the target database would have partial information for unpredictable time (until the 'post processing' is run), not to mention the added complexity of running that external post processing job.

Recent versions of Replicate allow adding a calculated column to all tables in a task - with such random data. If willing to use addon function, then it will possible to avoid the post-processing job at all and have consistent encrypted data in the target.

[Nulee_Massaro]

We can support what you are suggesting through our transformation functionality within Replicate. We suggest reaching out to Qlik Professional Services through your account team as they have assisted other customers with implementing this functionality. 

[john_wang]

Hello @lPrasanna ,

While we review this article, we realized many databases/data warehouses support UUID function directly, eg Oracle/MySQL/SQL Server/PostgreSQL/GBQ/Hive etc. May we know what's the database type and we'd like to check further if better approach for you.

thank you,

John.