Set Up the LiveRamp Native App in Snowflake

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:

--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');