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

An input table needs to be prepared for each identity resolution 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.

See the table below for a list of the suggested input table columns and descriptions for email-only resolution.

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

// Example SQL query to create an input table for email resolution

CREATE OR REPLACE TABLE  <dataset>.<input_table_name> (
HASHED_EMAIL STRING, 
ATTRIBUTE_1 STRING, 
ATTRIBUTE_12 STRING
);

// Or setup against an existing table

CREATE OR REPLACE VIEW <dataset>.<view_name> as 
SELECT 
d.hashed_email as HASHED_EMAIL, 
d.gender as ATTRIBUTE_1, 
d.name as ATTRIBUTE_2 
FROM my_bq_table d;

// Example SQL query to insert a row into the input table for email resolution
INSERT INTO <dataset>.<input_table> values ('some_ramp_id', '1234', 'RAMPID');

The output table is created after you share the tables and notify LiveRamp to initiate identity resolution. For output table example, see the "Notify LiveRamp to Initiate Identity Resolution" 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 transcode 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