Skip to main content

Set Up a Native App in Snowflake

Transcoding and identity resolution capabilities are available within Snowflake through native apps, which create a share to your account, opening up a view to query the reference data set from within your own Snowflake environment.

Once you've completed these steps, you're ready to perform the desired operation. See the articles listed below for the appropriate instructions:

Overall Steps

To set up a native app:

  1. Accept the Marketplace terms and conditions in the Snowflake UI.

    Note

    This must be performed by a user with an "orgadmin" role.

  2. Install the appropriate LiveRamp native app.

  3. Update the firewall configuration to enable authentication.

  4. Create and grant permissions to a role that gives the App Share database access to your tables (including logging and metrics tables).

  5. Create logging and metrics tables, add them to a share, and share that share back to LiveRamp. For more information, see the Snowflake help topic "Working with Shares."

See the sections below for detailed information on performing these steps.

Accept the Snowflake Marketplace Terms and Conditions

To install the LiveRamp native app, an organizational administrator (ORGADMIN) or greater role needs to accept the Snowflake Data Marketplace terms and conditions:

  1. Log into your Snowflake account with the ORGADMIN role.

    Note

    • Alternatively, once logged in click Switch Role next to the login name and select ORGADMIN.

    • The ORGADMIN role must have provided their first name, last name, and email address in their account properties. A USERADMIN or another role with OWNERSHIP properties can add these items to the account profile, if required.

  2. Click Organizations, and then select Snowflake Data Marketplace Billing.

  3. Click Review terms & conditions.

  4. In the Review Terms and Conditions dialog box, click the link to review the Terms and Services.

  5. If you agree to the terms and conditions, click Accept Terms & Conditions.

Install a LiveRamp Native App

After you've accepted the Marketplace terms and conditions, you install a LiveRamp native app using either the LiveRamp Transcoding tile or the LiveRamp Identity Resolution tile in the Snowflake Marketplace. The instructions that follow detail the process, using the Identity Resolution native app as an example.

This section details the installation from the Marketplace within the new interface. To switch from the legacy interface to the Snowsight interface, click the Snowsight icon to the left of your account name in the icon bar.

I-Set_Up_LR_Native_App-Snowsight_UI.png

Note

The Snowflake help topic "Installing a Snowflake Native Application from the UI" in Snowflake's native app documentation describes installation in more detail.

To install a native app:

  1. In Snowsight, click Private Sharing in the Data section of the left-hand panel.

    I-Set_Up_LR_Native_App-LR_Tile.png

  2. In the Privately Shared Listings section, click the appropriate LiveRamp tile.

    The listing page appears and describes the native app including several usage examples.

    I-Set_Up_LR_Native_App-Native_App_description.png

  3. Click Get to view the Create Database dialog box shown below.

    I-Set_Up_LR_Native_App-select_warehouse.png

  4. In Create Database, enter a name for your application.

  5. From the Add Roles drop-down menu, select the role(s) you want to be able to perform the transcoding operations.

    I-Set_Up_LR_Native_App-add_roles.png

    Tip

    Roles you might want to consider for inclusion are: SYSADMIN, USERADMIN, or maybe even PUBLIC.

  6. Click Select Warehouse and select the warehouse you will use to install the application.

    I-Set_Up_LR_Native_App-select_warehouse.png

  7. Click Get.

    Snowflake runs the installer script of the app to create a database with the name you specified. When the installation is complete the Installation Complete dialog box will appear.

    I-Set_Up_LR_Native_App-installation_complete.png

  8. Click Manage to return to the LiveRamp Marketplace listing page.

After the database is created successfully, the status on that page changes to Installed, and the button to open the app changes to Open as shown on the LiveRamp Transcoding Marketplace home page.

Open a Native App

To open a LiveRamp native app:

  1. Click Open on the listing page.

    I-Set_Up_LR_Native_App-open_button.png

    Note

    Alternatively in the Snowflake UI click on Private Sharing in the Data section of the left hand panel to return to the listing page, then click Open on the LiveRamp Native tile.

    You are taken to the Databases page in the Snowflake UI.

  2. Open the LiveRamp Transcode app from the Marketplace listing.

  3. Select the database you just created for the app.

    The worksheet with the various operations as queries saved as stored procedures is displayed.

    I-Set_Up_LR_Native_App-worksheet.png

In this interface, you can run a query or procedure by clicking on the procedure in the worksheet and then clicking the Run button shown boxed in red above.

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.

The Input table contains the RampIDs that need to be transcoded or the identifiers that need to be resolved. You also prepare the metadata table which specifies the parameters of the operation, including the type of operation (transcode or resolution).

The results of the operation appear in a read-only output table. The output table contains the converted or transcoded identifiers and lives in your Snowflake instance. Your customer data (in the form of the input table) and the metadata table never leaves your Snowflake instance.

Enable Authentication

The LiveRamp Identity APIs (ID-API) provide identity resolution technology for offline data (personally identifiable information or “PII,” such as email address, name, postal address, etc.). The LiveRamp native app relies on ID-API for authentication and authorization.

To enable authentication:

  1. Update the firewall configuration to enable the native app to send requests for authentication:

    alter database <native_app_database_name> set firewall_configuration=('https://9e7j3merc8.execute-api.us-east-2.amazonaws.com/Prod');

    Substitute the appropriate value for the <native_app_database_name> variable shown in the code above

    • For transcoding: LR_APP_SHARE

    • For resolution: LR_APP_SHARE_RESOLUTION

  2. Click Run.

Grant Shared Database permissions

To access the required tables (the input and metadata tables), the appropriate permissions must be granted to the native app. The following procedures create the role for access to these tables.

Note

The formats of these tables are described in the sections on preparing the tables in "Perform RampID Transcoding" and in "Perform Identity Resolution".

To create the appropriate roles, execute the following:

create role if not exists demo_permissions_role;
grant usage on database <consumer_database_name> to role <permissions_role>;
grant usage on schema <consumer_database_name>.<consumer_schema_name> to role <permissions_role>;
grant select on table  <consumer_database_name>.<consumer_schema_name>.<input_table> to role <permissions_role>;
grant select on table <consumer_database_name>.<consumer_schema_name>.<meta_table> to role <permissions_role>;
grant select, insert, update on <consumer_database_name>.<consumer_schema_name>.<metrics_table> to role <permissions_role>;
grant select, insert, update on <consumer_database_name>.<consumer_schema_name>.<logging_table> to role <permissions_role>;
grant role demo_permissions_role to database <native_app_database_name>;

Substitute the appropriate values for the following variables in the code above:

  • permissions_role: A role that has the appropriate permissions associated to run the stored procedure. The role can be created or can be assigned to an existing role in the native app installation process.

  • consumer_database_name: The database associated with the input table.

  • consumer_schema_name: The schema associated with the input table.

  • input_table: The table that contains input data to the process (identity resolution or transcoding). It can be a newly created table or an existing one.

  • meta_table: The table that is created for interaction with the LiveRamp native app containing metadata about the process (column header, process, etc) and client ID and secret.

  • metrics_table: The table created to collect and share usage metrics with the LiveRamp Snowflake account.

  • logging_table: The table created to collect and share logging information with the LiveRamp Snowflake account.

  • native_app_database_name: The name of the native app share from LiveRamp: "LR_APP_SHARE" for transcoding or "LR_APP_SHARE_RESOLUTION" for identity resolution.

    Note

    These names were the default names provided upon installation during setup, and they may have been changed for your use.

Activate Logging and Metrics

The native app can log activity to a log table, and aggregate event data into a set of metrics. If you are running multiple operations, logging and metrics can help you better understand your performance.

The data are stored in the logging_table and in the metrics_table that you create from the stored procedure named Create Logging and Metrics tables in the application that is shown below.

To activate logging and metrics functionality:

  1. Access the LiveRamp native app, click in the Create Logging and Metrics Table section, and then click Run.

    <consumer_database_name>.<consumer_schema_name>.<metrics_table> (
  event_time timestamp_tz,
  output_table_name text,
  uuid text,
  tenant text,
  metrics variant,
  job_type varchar(15),
  signature text
);

create or replace table
<consumer_database_name>.<consumer_schema_name>.<logging_table>;(
  ts timestamp_tz,
  uuid string,
  msg string
);

    Note

    The <...> represent variable names that can be replaced.

  2. Verify that the two commands shown below have set CHANGE_TRACKING = TRUE. If not, change the value(s) to TRUE,

    -- Alter Shared Table CHANGE_TRACKING to TRUE (Needed for LiveRamp account to create table streams).
alter table <consumer_database_name>.<consumer_schema_name>.<metrics_table> set CHANGE_TRACKING = TRUE;
alter table <consumer_database_name>.<consumer_schema_name>.<logging_table> set CHANGE_TRACKING = TRUE;
/*
END
*/

  3. Click the line with the procedure and then click Run to enforce the change.

  4. Run the commands in the Create logging and metrics share procedure (shown below) and share back the new share with logging and metrics table with LiveRamp.

    Note

    The logging and metrics table needs to be shared back to LiveRamp. LiveRamp validates the metrics and makes the output table visible.

    // Create logging and metrics share
/*
This step shares back logging and metrics share back to LiveRamp to enable processing at LiveRamp's end and setting output table as visible.
*/
*/
create or replace share <logging_metrics_share>;
grant usage on database <consumer_database_name> to share <logging_metrics_share>;
grant usage on schema <consumer_database_name>.<consumer_schema_name> to share <logging_metrics_share>;
grant select on <consumer_database_name>.<consumer_schema_name>.<metrics_table> to share <logging_metrics_share>;
grant select on <consumer_database_name>.<consumer_schema_name>.<logging_table> to share <logging_metrics_share>;
alter share <logging_metrics_share> add accounts = <LIVERAMP_ACCOUNT>;
/*
END SECTION
*/

Note

If the logging share is not shared back with LiveRamp, the output table will not be visible in your Snowflake instance.

The value of the LiveRamp account will be provided to you by your LiveRamp account manager.

Usage data are collected in the metrics table, which is part of your database.

Each record 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

In this section: