Set Up the LiveRamp Native App in Snowflake
Translation and identity resolution capabilities are available within Snowflake through the LiveRamp Identity Resolution and Translation native app, which creates a share to your account, opening up a view to query the reference data set from within your own Snowflake environment.
Translation and identity resolution capabilities are available within Snowflake through the LiveRamp Identity Resolution and Translation native app, which creates a share to your account, opening up a view to query the reference data set from within your own Snowflake environment.
Caution
This content is for customers utilizing the latest version of the LiveRamp Identity Resolution and Translation native app in Snowflake (starting October 2023). For customers utilizing previous versions of LiveRamp's native apps in Snowflake (the LiveRamp Identity Resolution native app or the LiveRamp Translation native app), see this documentation.
Once you've completed these steps to set up the native app, you're ready to perform the desired operation. See the articles listed below for the appropriate instructions:
Overall Steps
To set up the LiveRamp native app:
Accept the Snowflake Consumer Terms of Service in Snowflake.
Note
This must be performed by a user with an "orgadmin" role.
Install the LiveRamp Identity Resolution and Transcoding native app.
See the sections below for detailed information on performing these steps.
Accept the Snowflake Consumer Terms of Service
To install the LiveRamp native app, an organizational administrator (ORGADMIN) or greater role needs to accept the Snowflake Consumer Terms of Service. Follow Snowflake's instructions listed here.
Install the LiveRamp Identity Resolution and Transcoding Native App
Note
To install the LiveRamp native app, you will need to have an organizational administrator (ORGADMIN) role or greater.
The Snowflake help topic "Installing an Application from a Snowflake Marketplace Listing" in Snowflake's native app documentation describes installation in more detail.
To install the LiveRamp Identity Resolution and Transcoding native app:
From the left navigation bar of Snowsight, select
.Search or browse to find the LiveRamp Identity Resolution and Transcoding native app and then click
.If your email address has not been previously verified with Snowflake, you’ll receive an email to allow you to verify your email address.
Fill in the fields on the form and then click
.LiveRamp will then reach out to you to begin the contracting process and other required steps. Once these steps have been completed, LiveRamp shares the native app to your account.
Once the native app has been shared to your account, select
.From the “Recently Shared with You” area, click
for the LiveRamp Identity Resolution and Transcoding native app.Select the appropriate warehouse.
Note
See Snowflake’s warehouse documentation for more information on the creation and use of warehouses. A warehouse is a specification of computer resources used for operations and follows Snowflake’s sizing and pricing rules for pricing and availability. A Snowflake warehouse will auto-suspend after a certain period of inactivity.
If desired, change the application name.
Click
.The installation process begins.
Note
Do not leave the page or refresh the browser until you receive an email confirming that the installation has finished.
After the database is created successfully, the App will show up as an installed Application within the Apps section.
Open the LiveRamp Native App
To open the LiveRamp native app, doubleclick the application name in the Apps area.
You are taken to the Readme view from the application, which provides key details on inputs allowed, data configuration, and expected application behavior.
At the bottom of the screen, you’ll see sample SQL for the one-time initial application setup, as well as sample SQL with execution steps for performing an operation.
At the top of each sample SQL, an “Open in Worksheets” option appears upon hover to allow you to open the sample SQL in a worksheet.
Perform Initial Application Setup
Before you perform an operation, you must perform the initial setup of the native app by running the Application Setup SQL with your updated values. This setup performs the following actions:
Sets the necessary variables that will be used in subsequent steps.
Creates an integration to LiveRamp’s auth API and enables the application to use it.
Creates logging and metrics tables, adds them to a share, and shares that share back to LiveRamp. For more information, see the Snowflake help topic "Working with Shares."
Note
Once completed, these setup steps do not need to be performed again.
If you need to change the metrics or logging table after initial setup, contact LiveRamp before running the setup again to make sure variables are correctly mapped.
To perform the initial application setup:
Open the Application Setup worksheet with the sample SQL for initial application setup.
Update the following variables in the sample SQL shown below and then run the SQL:
Note
Do not change the schema name. Leave this value as
lr_app_schema
.DATABASE
: The name of your database.PUBLIC
: The name of the schema that holds the tables for identity resolution.METRICS_TABLE
: The name of the metrics table to use for the operation.LOGGING_TABLE:
The name of the logging table to use for the operation.LIVERAMP_IDENTITY_NATIVE_APP
: The name of the database the native app is loaded to.COMPANY_LIVERAMP_LOG_METRICS_SHARE:
The name of the share with logging and metrics to be sent to LiveRamp. Change "COMPANY" in the variable to your company name.LR_ACCOUNT_ID
: The LiveRamp account number. Use the appropriate number, depending on your Snowflake region and cloud provider:aws-us-east-1: POA18931
aws-us-west-2: FAA28932
azure-east-us-2: BL60425
gcp-us-central-1: ZP11254
LIVERAMP_AUTH_INTEGRATION
: The API integration which allows the native app to reach LiveRamps authentication API.
--The configuration works off parameterized values, please update this section with the appropriate variables that will be referred to later in the process. set customer_db_name = 'DATABASE'; set customer_schema_name = concat($customer_db_name, '.', 'PUBLIC'); set customer_metrics_table_name = concat($customer_schema_name, '.', 'METRICS_TABLE'); set customer_logging_table_name = concat($customer_schema_name, '.', 'LOGGING_TABLE'); -- Set the name of the application, you can choose to change the name of the application or leave it as is set application_name = 'IDENTITY_RESOLUTION_AND_TRANSCODING'; --The application will create a share back to LiveRamp’s Snowflake account, please follow the example formatting below for the share updating the COMPANY reference set share_name = 'COMPANY_LIVERAMP_LOG_METRICS_SHARE'; --Set the LiveRamp Snowflake account for the share of metrics and logging -- aws-us-east-1: POA18931 -- aws-us-west-2: FAA28932 -- azure-east-us-2: BL60425 -- gcp-us-central-1: ZP11254 set lr_account = 'LR_ACCOUNT_ID'; -- To create the logging and metrics tables, please execute the following create table if not exists identifier($customer_metrics_table_name) ( event_time timestamp_tz, output_table_name text, uuid text, tenant text, metrics variant, job_type text, signature text ); create table if not exists identifier($customer_logging_table_name) ( ts timestamp_tz, uuid string, msg string ); alter table identifier ($customer_metrics_table_name) set CHANGE_TRACKING = TRUE; alter table identifier ($customer_logging_table_name) set CHANGE_TRACKING = TRUE; --Update usage permissions on the appropriate database, schema and table names 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, insert, update on identifier($customer_metrics_table_name) to application identifier ($application_name); grant select, insert, update on identifier($customer_logging_table_name) to application identifier ($application_name); create share if not exists identifier($share_name); grant usage on database identifier($customer_db_name) to share identifier ($share_name); grant usage on schema identifier($customer_schema_name) to share identifier ($share_name); grant select on identifier($customer_metrics_table_name) to share identifier ($share_name); grant select on identifier($customer_logging_table_name) to share identifier ($share_name); alter share identifier($share_name) add accounts = $lr_account; -- Our application reaches out with client ID and secret to authenticate the workflow, please create and allow the application to use the API integration create api integration if not exists LIVERAMP_AUTH_INTEGRATION api_provider = aws_api_gateway api_aws_role_arn = 'arn:aws:iam::715724997226:role/snowflake-lambda' api_allowed_prefixes = ('https://9e7j3merc8.execute-api.us-east-2.amazonaws.com/Prod') enabled = true; grant usage on integration LIVERAMP_AUTH_INTEGRATION to application identifier($application_name); use database identifier($application_name); use schema lr_app_schema; call enable_auth_api_call('LIVERAMP_AUTH_INTEGRATION');
The Logging and Metrics Tables
The native app logs activity to a log table and aggregates event data into a set of metrics. If you are running multiple operations, logging and metrics can help you understand performance and enables information to be shared with LiveRamp for debugging, application health, and billing metrics.
When you run the Application Setup SQL, the logging and metrics tables are created, they’re added to a share, and that share is shared back to LiveRamp.
Note
The logging and metrics tables need to be shared back to LiveRamp. LiveRamp validates the metrics and makes the output table visible.
If the logging share is not shared back with LiveRamp, the output table will not be visible in your Snowflake instance.
Once set up, the share does not need to be adjusted and usage data will be collected in the metrics table, which is part of your database.
Note
If you need to change the metrics or logging table after the initial setup, contact LiveRamp before running the setup again to make sure variables are correctly mapped.
Each record in the metrics table contains:
A timestamp
The UUID (transaction ID)
The tenant identifier
The number of records processed or not, an error count
The job type
An encrypted field that contains all of this information combined