Perform RampID Translation in BigQuery

When naming tables, follow these guidelines:

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.

As long as the column names in the input table stay the same, the original metadata table can be reused for multiple operations. You only need to create a new metadata table if you change the column names in the input table.

Metadata column names must match those shown in the table below. The column names are not case sensitive, and should not be enclosed in single or double quotation marks.

Here is an example of BigQuery SQL for creating a metadata table:

// Example SQL query to create a metadata table for translation

CREATE OR REPLACE TABLE <dataset>.<metadata_table_name> (
CLIENT_ID STRING, 
CLIENT_SECRET STRING, 
EXECUTION_MODE STRING, 
EXECUTION_TYPE STRING, 
TARGET_COLUMN STRING, 
TARGET_DOMAIN_COLUMN STRING, 
TARGET_TYPE_COLUMN STRING
);

For each run, if any changes are to be made to the metadata table to configure the run differently, update the metadata table.

// Example SQL query to insert a row into the metadata table for translation

insert into <dataset>.<metadata_table> values ('<client_id>', '<client_secret>', 'transcoding', 'TRANSCODING', 'RAMPID', 'TARGET_DOMAIN', 'TARGET_TYPE');

An input table needs to be prepared for each translation operation.

The column names for the input table can be whatever you want to use, as long as the names match the values specified in the metadata table.

Here are some examples of BigQuery SQL for creating this table:

// Example SQL query to create an input table for translation

CREATE OR REPLACE TABLE  <dataset>.<input_table_name> (
RAMPID STRING, 
TARGET_DOMAIN STRING, 
TARGET_TYPE STRING
);

// Or setup against an existing table

CREATE OR REPLACE VIEW <dataset>.<view_name> as 
SELECT 
d.rampid as RAMPID, 
'1234' as TARGET_DOMAIN, 
'RAMPID' as TARGET_TYPE 
FROM my_bq_table d;

Insert all RampIDs encoded in your domain into the newly created input table.

// Example SQL query to insert a row into the input table for translation

INSERT INTO <dataset>.<input_table> values ('some_ramp_id', '1234', 'RAMPID');

The output table is created after you share the tables and run the shared “bq_lr_invoke” stored procedure in the "Call the Shared Stored Procedure to Initiate Translation" section below.

You can use a parameterized and repeatable share script that selects the appropriate data to share with LiveRamp afterwards. See the code below for an example.

BEGIN
  -------------------------------------------------------------------------------
  -- UPDATE VARIABLES WITH CORRECT FULLY QUALIFIED TABLE & DATASET IDENTIFIERS --
  -------------------------------------------------------------------------------
  -- input_table is table that has ramp_ids to translate from, target_domain, and type
  DECLARE input_table STRING DEFAULT 'liveramp_consumer_tests.transcoding_input';
  -- input_metadata table has arugments for your run including client_id / client_secret
  DECLARE input_metadata STRING DEFAULT 'liveramp_consumer_tests.metadata_input';
  -- output_dataset is an empty dataset you created to share to LiveRamp to write outputs to.
  DECLARE output_dataset STRING DEFAULT 'liveramp_consumer_tests_output';
  -- project_name is the project in which your input tables and output_dataset lives.
  DECLARE project_name STRING DEFAULT 'unrestricted-coding';
  -------------------------------------------------------------------------------
  -- END VARIABLES DO NOT EDIT BELOW THIS
  -------------------------------------------------------------------------------
  DECLARE liveramp_iam_principle STRING DEFAULT 'user:new.user@liveramp.com';

  -- GRANT Read of Input Tables
  EXECUTE IMMEDIATE "GRANT `roles/bigquery.dataViewer` ON TABLE " || input_table || " TO '" || liveramp_iam_principle || "';";
  EXECUTE IMMEDIATE "GRANT `roles/bigquery.dataViewer` ON TABLE " || input_metadata || " TO '" || liveramp_iam_principle || "';";

  -- GRANT Write for Output Tables
  EXECUTE IMMEDIATE "GRANT `roles/bigquery.dataEditor` ON SCHEMA " || output_dataset || " TO '" || liveramp_iam_principle || "';";

  with fqdns AS
    (
      SELECT STRUCT("input_table" as key, project_name || "." || input_table as fqdn) as item
      UNION ALL
      SELECT STRUCT("input_metadata" as key, project_name || "." || input_metadata as fqdn) as item
      UNION ALL
      SELECT STRUCT("output_dataset" as key, project_name || "." || output_dataset as fqdn) as item
    )
  SELECT f.item.*
  FROM fqdns f;

END