Big Query - data de-Identification PII

Admin 1
Viewed 353

I am looking for a simple way to de-identificate data in BigQuery (and in SQL in general), that handle:

  • Anonymize Personally identifiable information (PII) with a unique random value
  • Always have the same de-identificate value for a PII value
  • Have the possibility to retrieve the original value

I have tried to implement a solution, but I would like to know if there is a more efficient way to handle this. In our database, we don't have a lot of data to anonymise (max 100 000 entries).

This is a simple exemple of the solution:

CREATE SCHEMA dataset_raw_data; -- (only Admin has access to this dataset)
CREATE SCHEMA dataset_clean_data; -- (all team have access to this dataset)
CREATE table dataset_raw_data.users(
    id string,
    email_pii string,
    name_pii string
)
CREATE table dataset_clean_data.users(
    id string,
    email string,
    name string
)

I created a mapping table (only accessible by the Admin):

create table dataset_raw_data.pii_mapping(
    pii_value STRING,
    anonimize_value STRING
)

And this is my script to populate the table dataset_clean_data.users. If this is the right approach, the objective will be to create a procedure that generates these requests dynamically

-- Insert missing values in table dataset_raw_data.pii_mapping
insert into dataset_raw_data.pii_mapping
select lower(users.pii_value) as pii_value, GENERATE_UUID() as anonimize_value
from (
    select distinct email_pii as pii_value from dataset_raw_data.users
    union distinct
    select distinct name_pii as pii_value from dataset_raw_data.users
) as base_table
LEFT JOIN dataset_raw_data.pii_mapping as pii_mapping
  ON lower(base_table.pii_value) = pii_mapping.pii_value 
WHERE pii_mapping.pii_value  IS NULL
and base_table.pii_value IS NOT NULL;

-- Populate the table dataset_clean_data.users
insert into table dataset_clean_data.users
select base_table.id as id
pii_mapping1.anonimize_value as email
pii_mapping2.anonimize_value as name
from 
    dataset_raw_data.users as base_table,
    dataset_raw_data.pii_mapping as pii_mapping1,
    dataset_raw_data.pii_mapping as pii_mapping2
where 
    lower(base_table.email) = pii_mapping1.pii_value 
    AND lower(base_table.name) = pii_mapping2.pii_value 
;

So at the end I have those data:

select * from dataset_raw_data.users LIMIT 1;   -- 1 | bob@gmail.com | Bob
select * from dataset_clean_data.users LIMIT 1; -- 1 | 7d25df8d-4b4d-46ad-9df3-8c71bf092f9f | 1f042eaa-a6b1-4b86-95a9-f6188d3f70d7

Thanks for your feedback.

google-bigquery sql
1 Answers

Google-bigquery assist you encrypting sensitive information. You have these topics to look for:

For more information you can check the bigquery documentation about it by clicking here.

So, for your question, I would recommended use what google already implement, avoiding redundancy in the process. Still, focusing solely in your scenario, which looks like, a substitution cipher, if it fits your specific scenario, it works, to make it into a functional procedure there are scripting commands that will help you achieve it. Check this link for information about scripting.

Note aside, if we go with sql databases in general, I think one of the most popular approach is to have the data encrypted as soon as it gets into the table when table is inserted/selected - encrypted/decrypted respectively. Most supported usage is at column level, support for other kind of encryption scopes varies from database to database. Check this links to find more information for a practical sample of column level encryption:

Update 16/11/2021: Added additional link regarding encryption concepts in bigquery

Bigquery sample:

/* Create raw data table */
create or replace table `project-id.data-set.bikeshare_trips` as (
SELECT * FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips` 
where  start_time < '2014-01-01' limit 100
)

/* Create key table from data table */
create table `project-id.data-set.biketrips_keys` (
    trip_id INT64,
    keyset BYTES
) AS
Select trip_id,KEYS.NEW_KEYSET('AEAD_AES_GCM_256') as keyset from `project-id.data-set.bikeshare_trips`

/* Create encrypted table from uncrypted raw data table */
create table `project-id.data-set.biketrips_encrypted`
(
    trip_id INT64,
    subscriber_type BYTES 
) as 
select trip_id,
       AEAD.ENCRYPT(
           (select keyset from `project-id.data-set.biketrips_keys` keys where keys.trip_id=trips.trip_id),subscriber_type,CAST(trips.trip_id AS STRING)
       ) 
from `project-id.data-set.bikeshare_trips` trips

/* decrypt from encrypted data table */
select trip_id,
       AEAD.DECRYPT_STRING(
           (select keyset from `project-id.data-set.biketrips_keys` keys where keys.trip_id=trips.trip_id),subscriber_type,CAST(trips.trip_id AS STRING)
       ) 
from `project-id.data-set.biketrips_encrypted` trips

Note: original source

user_145515670
Related