Perform RampID Translation in Snowflake

After you've set up the LiveRamp Identity native app in Snowflake (see "Set Up the LiveRamp Native App in Snowflake" for instructions), perform the following steps to perform RampID translation:

See the sections below for information on performing these tasks.

An input table containing the RampIDs to be translated needs to be prepared for each operation.

When creating input tables, keep the following guidelines in mind:

The output table is created by the operation that you run. For an example, see the "Perform Translation" section below.

To specify the variables to be used for the operation:

--Update this section with the appropriate variables
set customer_db_name = 'DATABASE';
set customer_schema_name = concat($customer_db_name, '.', 'PUBLIC');
set customer_input_table_name = concat($customer_schema_name, '.', 'INPUT_TABLE');
set customer_meta_table_name = concat($customer_schema_name, '.', 'META_TABLE');
set output_table_name = 'OUTPUT_TABLE';

-- Name of the installed application
set application_name = 'IDENTITY_RESOLUTION_AND_TRANSCODING';

The metadata table passes the required credentials, specifies the type of operation, and specifies the column names in the input table to reference for the original RampIDs, the domain to translate to, and the identifier type.

To create the metadata table:

--FOR TRANSCODING Update the parameters here for the metadata table
create or replace table identifier($customer_meta_table_name) as
select
    TO_VARCHAR(DECRYPT(ENCRYPT('<client_id>', 'HideFromLogs'), 'HideFromLogs'), 'utf-8') as client_id,
    TO_VARCHAR(DECRYPT(ENCRYPT('<client_secret>', 'HideFromLogs'), 'HideFromLogs'), 'utf-8') as client_secret,
    'transcoding' as execution_mode,
    'transcoding' as execution_type,
    '<column to be translated>' as target_column,
    '<column containing target domain>' as target_domain_column,
    '<column containing target type>' as target_type_column;

To set up the permissions for the tables used for translation, run the SQL in the Execution Steps worksheet shown below:

--The remainder of the commands should be run for ALL JOB TYPES, please switch to the Native App database and schema and execute the procedure.  Once completed, please run check_for_output for the output table to be written in the appropriate Job Schema

grant usage on database identifier ($customer_db_name) to application identifier($application_name);
grant usage on schema identifier ($customer_schema_name) to application identifier($application_name);
grant select on table identifier ($customer_input_table_name) to application identifier($application_name);
grant select on table identifier ($customer_meta_table_name) to application identifier($application_name);


use database identifier ($application_name);
use schema lr_app_schema;

You perform a translation operation by running the translation procedure to translate the RampIDs in the input table and then checking that the output has succeeded. You then open the output table to check the results.

To perform translation

Once the app returns a success message, the output should be displayed in the native app database under lr_app_schema.

If Snowflake returns a status message of Error, check the error message for any information to help you fix the issue and then try running the operation again. For some issues, the error message will direct you to contact LiveRamp Support. For more information, see "Snowflake Operation Error Codes".

The results end up in the output table in the same database. See the table below for an example of an output table.