大查询-数据去标识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 | [e-mail] | Bob
select * from dataset_clean_data.users LIMIT 1; -- 1 | 7d25df8d-4b4d-46ad-9df3-8c71bf092f9f | 1f042eaa-a6b1-4b86-95a9-f6188d3f70d7

感谢您的反馈。

Answer 1

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

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

有关详细信息，您可以单击此处查看有关它的bigquery文档。

所以，对于你的问题，我建议使用谷歌已经实现的，避免过程中的冗余。尽管如此，只关注你的场景，它看起来像是一个替代密码，如果它适合你的特定场景，它是有效的，为了使它成为一个功能过程，有脚本命令可以帮助你实现它。查看此链接以获取有关脚本的信息。

请注意，如果我们一般使用sql数据库，我认为最流行的方法之一是在插入/选择表时，一旦数据进入表，就对其进行加密-分别加密/解密。最受支持的使用是在列级别，对其他类型加密作用域的支持因数据库而异。查看此链接以查找列级加密的实际示例的更多信息：

• 列级加密

更新16/11/2021：在bigquery 中添加了有关加密概念的附加链接

• 具有相关数据的身份验证加密-AEAD概念

大查询样本：

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

注：原始来源