大查询-数据去识别PII

Question

我正在寻找一种简单的方法，可以在BigQuery (以及一般的SQL中)中识别数据，即：

• 匿名具有唯一随机值的个人可识别信息(PII)
• 对于PII值，始终具有相同的去标识值。
• 具有检索原始值的可能性

我曾尝试实施一项解决方案，但我想知道是否有更有效的办法来处理这一问题。在我们的数据库中，我们没有太多的匿名信息(最多10万条条目)。

--这是解决方案的一个简单例子：

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
)

我创建了一个映射表(仅可由管理员访问)：

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

这是我的脚本，用于填充表dataset_clean_data.users。如果这是正确的方法，目标将是创建一个动态生成这些请求的过程。

-- 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 
;

最后我得到了这些数据：

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

感谢您的反馈。

Answer 1

Google帮助您加密敏感信息。您需要查找以下主题：

• 客户管理加密
• 自定义密钥加密

有关更多信息，您可以单击这里查看bigquery文档。

因此，对于你的问题，我建议使用谷歌已经实现的，避免过程中的冗余。不过，只关注您的场景(类似于替换密码 )，如果它适合您的特定场景，则可以将其转化为一个功能过程--有一些脚本命令可以帮助您实现它。有关脚本的信息，请查看此链接。

请注意，如果我们通常使用sql数据库，我认为最流行的方法之一是在插入/选择表时立即将数据加密到表中--加密/解密。大多数支持的使用都是在列级，对其他类型加密范围的支持因数据库而异。请查看此链接，以查找实际的列级加密示例的更多信息：

• 列级加密

更新16/11/2021：添加关于bigquery加密概念的附加链接

• 带关联数据的认证加密. AEAD概念

Bigquery示例

/* 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

注：原始源