Skip to main content

Set Up a Native App in Snowflake

Abstract

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.

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. Set the necessary variables that will be used in subsequent steps.

  4. Update the firewall configuration to enable authentication.

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

  6. 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 in to 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.

    Note

    You will need to use this name as your native app database name later in the process.

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

    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 Identity Resolution 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 Identity Resolution 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.

Set the Variables

Set some variables that will be used in the subsequent steps (Identity Resolution is shown as an example):

  1. Locate the Defining parameters for the Native App section at the top of the script.

    set native_app_db_name = ‘LIVERAMP_IDENTITY_RESOLUTION’;
set native_app_schema_name = ‘lr_app_schema’;

set role_name = ‘PERMISSIONS_ROLE’;

set customer_db_name = ‘RESOLUTION_DEMO’;
set customer_schema_name = concat($customer_db_name,'.','PUBLIC');;
set customer_input_table_name=concat($customer_schema_name,'.','RESOLUTION_INPUT_TABLE');
set customer_meta_table_name=concat($customer_schema_name,'.','RESOLUTION_META_TABLE');
set customer_metrics_table_name=concat($customer_schema_name,'.','METRICS_TABLE');
set customer_logging_table_name=concat($customer_schema_name,'.','LOGGING_TABLE');
set share_name='LIVERAMP_LOG_METRICS_SHARE';
set lr_account='poa18931';

  2. Make any necessary changes to the variables:

    Note

    Do not change native_app_schema_name. Leave this value as lr_app_schema.

    • LIVERAMP_IDENTITY_RESOLUTION: The name of the database native app is loaded to.

    • PERMISSIONS_ROLE: The name of the role to hand permissions to the native app. The permissions to database objects outside the native app are given to this role. This role is then granted to the native app database.

    • RESOLUTION_DEMO: The name of your database.

    • PUBLIC: The name of the schema that holds the tables for identity resolution.

    • RESOLUTION_INPUT_TABLE: The name of the input table to use for the operation.

    • RESOLUTION_META_TABLE: The name of the metadata table to use for the operation.

    • 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.

    • SHARE_NAME: The name of the share with logging and metrics to be sent to LiveRamp.

    • POA18931: The LiveRamp account number. Unless your LiveRamp representative indicates otherwise, please leave this as is.

  3. Click Run.

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 identifier ($native_app_database_name) set firewall_configuration=('https://9e7j3merc8.execute-api.us-east-2.amazonaws.com/Prod');

  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 and grant usage to the appropriate roles, execute the following:

create role if not exists identifier($role_name);
grant usage on database identifier($customer_db_name) to role identifier($role_name);
grant usage on schema identifier($customer_schema_name) to role identifier($role_name);
grant select on table  identifier($customer_input_table_name) to role identifier($role_name);
grant select on table identifier($customer_meta_table_name) to role identifier($role_name);
grant select, insert, update on identifier($customer_metrics_table_name) to role identifier($role_name);
grant select, insert, update on identifier($customer_logging_table_name) to role identifier($role_name);
grant role identifier($role_name) to database identifier($native_app_db_name);

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 log and metrics data are stored in the logging_table and metrics_table that you create in the Create Logging and Metrics tables section in the worksheet shown below.

To activate logging and metrics functionality:

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

    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 varchar(15),
   signature text
);

create table if not exists identifier($customer_logging_table_name)(
  ts timestamp_tz,
  uuid string,
  msg string
);

  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 identifier($customer_metrics_table_name) set CHANGE_TRACKING = TRUE;
alter table identifier($customer_logging_table_name) 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 section (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 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;
/*
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: