Connecting Maxcompute (Alibaba Cloud) to Churney

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, ip address.

If we would like to share a users table with Churney, it is best to create a view that points to the table where you have the liberty to choose which columns you would like to share, and hash the private information according to Facebook's guide. Let’s suppose you would like to share the table df_ew1_332.users with Churney. To do so, you can create a new Maxcompute instance on the same region as you raw data and create a view like in this example:

CREATE OR REPLACE VIEW  churney.users_view as

SELECT
   sha2(lower(trim(name)), 256) name,
   sha2(lower(trim(email)), 256) email,
   created_at

FROM df_ew1_332.users;

What you will share with Churney will be the view in churney.users_view and not the original df_ew1_332.users

However, if the data that you are planning to share does not contain any private information, then you can skip the creation of views and share the original schema df_ew1_332

Go under RAM -> User and create a churney user with OpenAPIAccess

An access key and secret access key will be generated. Note them down and share them securely with Churney

Go in DataWorks and run the following script to give permissions to Churney on the Maxcompute instance and on the specific tables/views you will share with Churney

create role Churney;
grant READ ,LIST, CreateInstance
     on project <your_project_name>
     to ROLE Churney;


grant SELECT, DESCRIBE ON TABLE users
TO ROLE Churney;


add user RAM$<your_alibaba_id>:<churney_user_id>;


grant Churney TO RAM$<your_alibaba_id>:<churney_user_id>;

Where to find <your_alibaba_id>?

If you check on your admin user, this is the account_id that goes in the red box

Where to find <churney_user_id> ?

You can find this id if you go back to the user you created in the previous step and get the UID in the red box

Like in the screenshot below, create a new bucket for Churney and make sure to Block Public Access

Go in RAM -> Policies and create this policy to allow Churney to access the OSS bucket

{
    "Version": "1",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "oss:PutObject",
                "oss:GetObject",
                "oss:ListBuckets",
                "oss:GetBucketLocation",
                "oss:ListMultipartUploads",
                "oss:ListObjects",
                "oss:ListObjectVersions",
                "oss:ListParts"
            ],
            "Resource": [
                "acs:oss:oss-eu-west-1:<your_alibaba_id>:<oss-bucket>",
               "acs:oss:oss-eu-west-1:<your_alibaba_id>:<oss-bucket>/*"
            ]
        }
    ]
}

Make sure to change <oss-bucket> to the name of the bucket. In this example, that would be <churney-unload>

Go under RAM -> Roles and create a new role churney-unload-oss-policy where you would attach the policy above. Then go in the Trust policy and change it to this:

{
 "Statement": [
   {
     "Action": "sts:AssumeRole",
     "Effect": "Allow",
     "Principal": {
       "RAM": [
         "acs:ram::<your_alibaba_id>:user/churney"
       ]
     }
   },
   {
     "Action": "sts:AssumeRole",
     "Effect": "Allow",
     "Principal": {
       "Service": [
         "odps.aliyuncs.com"
       ]
     }
   }
 ],
 "Version": "1"
}

The ARN of the maxcompute-unloader-user - in the red circle in the screenshot above

The name of the oss bucket and its endpoint. You can find that as in the screenshot below, by going to your bucket and click on Overview

The name of the maxcompute instance and its endpoint, something in this pattern https://service.eu-west-1.maxcompute.aliyun.com/api

and the names of the tables you would like us to access.

The access key and secret access key of the user you created for Churney. If you didn’t note down the keys in the moment you created the user, you can generate new ones by going to the user and clicking Generate Access Key