Connecting Databricks GCP to Churney

What kind of data is required?

The short answer is as much as possible. The long answer is that Churney requires data about:

• Payments
• Trials (if applicable)
• User demographic (if available)
• User activity

Additionally, we need to know the location (region) of your data warehouse.

‍

Create hashed views

‍

To create the views, we would need to create queries to hash the PII columns.

For context, Facebook has a guide on how to hash contact information for their conversion api: https://developers.facebook.com/docs/marketing-api/conversions-api/parameters/customer-information-parameters. Google has a similar guide for enhanced conversions using their ads api https://developers.google.com/google-ads/api/docs/conversions/enhance-conversions. Basically, we want to hash columns that contain data which would allow one to determine the identity of a user: First name, last name, birth day, street address, phone number, email address etc.

‍

Create a dataset named churney .

If the raw data contains email, phone, birthday or other identifiers, the columns need to be excluded and hashed in the view.

Example, if the raw data lies under raw_data.sensitive and you would like to create a view churney.sensitive_data_view

‍

CREATE OR REPLACE <your_project>.churney.sensitive_data_view as ( 
select
SHA2(LOWER(TRIM(email)), 256) as email,
SHA2(LOWER(TRIM(name)), 256) as name,
SHA2(LTRIM('0', regexp_replace(phone, '[()-+-]', '')), 256) as phone,
SHA2(date_format(birthday, 'yyyyMMdd'), 256) as birthday,
* EXCEPT(email, name, phone, birthday)
from <your_project>.raw_data.sensitive
)

If you want to test it how the script looks with some data:

with sensitive as
(
 select 1 as id,
 "abc" as name,
 "abc@abc.com" as email,
 current_date() as birthday,
 '+4511111111' as phone
)
select
SHA2(LOWER(TRIM(email)), 256) as email,
SHA2(LOWER(TRIM(name)), 256) as name,
SHA2(LTRIM('0', regexp_replace(phone, '[()-+-]', '')), 256) as phone,
SHA2(date_format(birthday, 'yyyyMMdd'), 256) as birthday,
* EXCEPT(email, name, phone, birthday)
from sensitive

‍

Create a service principal for Churney

Go to your account -> Settings, and then Identity and access -> Users and create the new service principal

Then go to the service principal to generate a token as below. The maximum lifetime is 730 days, so please use that. Store the secret somewhere safe for now.

Create a group for the churney service princial as below:

And add the service principal to the group:

Create a storage credential

Churney will give you the name of their gs bucket for this step.

Go to Catalog -> Storage Credentials -> Create credentials

‍

Note down the service account you will see and share it with Churney. This is the service account that will access Churney’s google storage bucket to unload the data.

‍

Go to the storage credential and grant the below permissions to yourself

And finally run

CREATE EXTERNAL LOCATION IF NOT EXISTS `churney_external`
URL 'gs://<bucket_name>/'
WITH (STORAGE CREDENTIAL `churney-storage-credential`);

Where the <bucket_name> contains the gcp bucket of Churney.

Then run

GRANT CREATE EXTERNAL TABLE ON EXTERNAL LOCATION `churney_external` TO `Churney`;
GRANT READ FILES ON EXTERNAL LOCATION `churney_external` TO `Churney`;

‍

Grant permissions on schema

‍

Let’s assume that you would like to share the views under churney_views with Churney

Churney will create external tables pointing at the gs bucket, so let’s create a schema for the churney_exports:

CREATE SCHEMA test.churney_exports;

Churney will maintain the exports here, so grant the permissions below

For the churney_views schema, Churney only needs Data reader, so grant those permissions as below

Grant permissions to the warehouse

Finally, go the your warehouse, and add the service principal as below:

What to share with Churney

• The token generated for the user (in a safe way) and the id of the principal. You can find the id as below:

• The region of your Databricks (e.g. europe-west1)
• The name of the export dataset create for Churney (in this example churney_exports)
• The service account generated from the storage credential setup
• The catalog name (in this example test)
• The connection details of the sql warehouse: server_hostname and http_path as below

‍

‍