了解DuckDB以实现数据隐私和安全
数据隐私和安全已经成为全球各个组织的关键问题。组织经常需要识别、掩盖或移除数据集中的敏感信息，同时保持数据的实用性。本文探讨如何利用DuckDB，一种内部分析数据库，实现高效的敏感数据整治。
为什么选择DuckDB？（你为什么应该关心它）
把DuckDB想象成是SQLite的在分析方面更有天赋的表亲。它是一个嵌入式数据库，可以直接在你的进程中运行，但专门设计用于处理分析工作负载。它为数据整治提供了哪些优势呢？想象一下，能够以惊人的速度处理大型数据集，而无需设置复杂的数据库服务器。听起来不错，对吧？
以下是DuckDB特别适合我们用例的原因：
- 由于其基于列的存储方式，速度极快。
- 您可以直接在现有的Python环境中运行它。
- 它可以轻松处理多种文件格式。
- 它与云存储结合得很好（稍后详细介绍）。
在本指南中，我将使用Python和DuckDB。DuckDB还支持其他语言，如他们的文档中所提到的。
使用DuckDB开始数据隐私
先决条件
- 已安装Python 3.9或更高版本
- 先前了解设置Python项目和虚拟环境或Conda环境
通过运行以下命令在虚拟环境中安装DuckDB：
Shell
pip install duckdb --upgrade
现在您已经安装了DuckDB，让我们创建一个DuckDB连接：
Python
import duckdb
import pandas as pd
# Create a DuckDB connection - it's this simple!
conn = duckdb.connect(database=':memory:')
高级PII数据脱敏技术
以下是如何实现强大的PII（个人可识别信息）脱敏的方法：
假设您有一个需要清理的客户信息数据集。以下是您可以处理常见情况的方法。
让我们创建示例数据：
SQL
CREATE TABLE customer_data AS
SELECT
'John Doe' as name,
'123-45-6789' as ssn,
'[email protected]' as email,
'123-456-7890' as phone;
- 这将创建一个名为
customer_data的表，其中包含一行示例敏感数据。 - 数据包括姓名、社保号、电子邮件和电话号码。
第二部分涉及使用regexp_replace进行脱敏模式：
SQL
-- Implement PII masking patterns
CREATE TABLE masked_data AS
SELECT
regexp_replace(name, '[a-zA-Z]', 'X') as masked_name,
regexp_replace(ssn, '[0-9]', '*') as masked_ssn,
regexp_replace(email, '(^[^@]+)(@.*$)', '****$2') as masked_email,
regexp_replace(phone, '[0-9]', '#') as masked_phone
FROM customer_data;
让我带您了解上述SQL代码的作用。
regexp_replace(name, '[a-zA-Z]', 'X')- 将所有字母（包括大写和小写）替换为
'X' - 示例：
"John Doe"变为"XXXX XXX"
- 将所有字母（包括大写和小写）替换为
regexp_replace(ssn, '[0-9]', '*') as masked_ssn- 将所有数字替换为
'*' - 示例：
"123-45-6789"变为"--***"
- 将所有数字替换为
regexp_replace(email, '(^[^@]+)(@.*$)', '****$2') as masked_email:(^[^@]+)捕获@符号之前的所有内容(@.*$)捕获@及其后面的所有内容- 将第一部分替换为
'****'，并保留域名部分 - 示例：
""变为"****@email.com"
regexp_replace(phone, '[0-9]', '#') as masked_phone:- 使用
'#'替换所有数字 - 例子：
"123-456-7890"变成"###-###-####"
- 使用
因此，您的数据将被转换如下：
- 原始数据：
name: John Doe
ssn: 123-45-6789
email: [email protected]
phone: 123-456-7890- 掩码数据：
masked_name: XXXX XXX
masked_ssn: ***-**-****
masked_email: ****@email.com
masked_phone: ###-###-####Python 实现
Python
import duckdb
import pandas as pd
def mask_pii_data():
# Create a DuckDB connection in memory
conn = duckdb.connect(database=':memory:')
try:
# Create and populate sample data
conn.execute("""
CREATE TABLE customer_data AS
SELECT
'John Doe' as name,
'123-45-6789' as ssn,
'[email protected]' as email,
'123-456-7890' as phone
""")
# Implement PII masking
conn.execute("""
CREATE TABLE masked_data AS
SELECT
regexp_replace(name, '[a-zA-Z]', 'X') as masked_name,
regexp_replace(ssn, '[0-9]', '*') as masked_ssn,
regexp_replace(email, '(^[^@]+)(@.*$)', '****$2') as masked_email,
regexp_replace(phone, '[0-9]', '#') as masked_phone
FROM customer_data
""")
# Fetch and display original data
print("Original Data:")
original_data = conn.execute("SELECT * FROM customer_data").fetchdf()
print(original_data)
print("\n")
# Fetch and display masked data
print("Masked Data:")
masked_data = conn.execute("SELECT * FROM masked_data").fetchdf()
print(masked_data)
return original_data, masked_data
except Exception as e:
print(f"An error occurred: {str(e)}")
return None, None
finally:
# Close the connection
conn.close()
基于规则的数据混淆
在深入技术细节之前，让我用简单的术语解释数据混淆。
数据混淆是隐藏或删除文档或数据库中的敏感信息的过程，同时保留整体结构和非敏感内容。想象一下，在数字形式下使用黑色记号笔隐藏印刷文件上的机密信息。
现在让我们使用 DuckDB 和 Python 实现数据混淆。我添加了这段带有注释的代码片段，这样您就可以轻松跟踪。
Python
import duckdb
import pandas as pd
def demonstrate_data_redaction():
# Create a connection
conn = duckdb.connect(':memory:')
# Create sample data with various sensitive information
conn.execute("""
CREATE TABLE sensitive_info AS SELECT * FROM (
VALUES
('John Doe', '[email protected]', 'CC: 4532-1234-5678-9012', 'Normal text'),
('Jane Smith', '[email protected]', 'SSN: 123-45-6789', 'Some notes'),
('Bob Wilson', '[email protected]', 'Password: SecretPass123!', 'Regular info'),
('Alice Brown', '[email protected]', 'API_KEY=abc123xyz', 'Basic text')
) AS t(name, email, sensitive_field, normal_text);
""")
# Define redaction rules
redaction_rules = {
'email': r'[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}', # Email pattern
'sensitive_field': r'(CC:\s*\d{4}[-\s]?\d{4}[-\s]?\d{4}[-\s]?\d{4}|SSN:\s*\d{3}-\d{2}-\d{4}|Password:\s*\S+|API_KEY=\S+)', # Various sensitive patterns
'name': r'[A-Z][a-z]+ [A-Z][a-z]+' # Full name pattern
}
# Show original data
print("Original Data:")
print(conn.execute("SELECT * FROM sensitive_info").fetchdf())
# Apply redaction
redact_sensitive_data(conn, 'sensitive_info', redaction_rules)
# Show redacted data
print("\nRedacted Data:")
print(conn.execute("SELECT * FROM redacted_data").fetchdf())
return conn
def redact_sensitive_data(conn, table_name, rules):
"""
Redact sensitive data based on specified patterns.
Parameters:
- conn: DuckDB connection
- table_name: Name of the table containing sensitive data
- rules: Dictionary of column names and their corresponding regex patterns to match sensitive data
"""
redaction_cases = []
# This creates a CASE statement for each column
# If the pattern matches, the value is redacted
# If not, the original value is kept
for column, pattern in rules.items():
redaction_cases.append(f"""
CASE
WHEN regexp_matches({column}, '{pattern}')
THEN '(REDACTED)'
ELSE {column}
END as {column}
""")
query = f"""
CREATE TABLE redacted_data AS
SELECT
{', '.join(redaction_cases)}
FROM {table_name};
"""
conn.execute(query)
# Example with custom redaction patterns
def demonstrate_custom_redaction():
conn = duckdb.connect(':memory:')
# Create sample data
conn.execute("""
CREATE TABLE customer_data AS SELECT * FROM (
VALUES
('John Doe', '123-45-6789', 'ACC#12345', '$5000'),
('Jane Smith', '987-65-4321', 'ACC#67890', '$3000'),
('Bob Wilson', '456-78-9012', 'ACC#11111', '$7500')
) AS t(name, ssn, account, balance);
""")
# Define custom redaction rules with different patterns
custom_rules = {
'name': {
'pattern': r'[A-Z][a-z]+ [A-Z][a-z]+',
'replacement': lambda match: f"{match[0][0]}*** {match[0].split()[1][0]}***"
},
'ssn': {
'pattern': r'\d{3}-\d{2}-\d{4}',
'replacement': 'XXX-XX-XXXX'
},
'account': {
'pattern': r'ACC#\d{5}',
'replacement': 'ACC#*****'
}
}
def apply_custom_redaction(conn, table_name, rules):
redaction_cases = []
for column, rule in rules.items():
redaction_cases.append(f"""
CASE
WHEN regexp_matches({column}, '{rule['pattern']}')
THEN '{rule['replacement']}'
ELSE {column}
END as {column}
""")
query = f"""
CREATE TABLE custom_redacted AS
SELECT
{', '.join(redaction_cases)},
balance -- Keep this column unchanged
FROM {table_name};
"""
conn.execute(query)
# Show original data
print("\nOriginal Customer Data:")
print(conn.execute("SELECT * FROM customer_data").fetchdf())
# Apply custom redaction
apply_custom_redaction(conn, 'customer_data', custom_rules)
# Show results
print("\nCustom Redacted Data:")
print(conn.execute("SELECT * FROM custom_redacted").fetchdf())
# Run demonstrations
print("=== Basic Redaction Demo ===")
demonstrate_data_redaction()
print("\n=== Custom Redaction Demo ===")
demonstrate_custom_redaction()
示例结果
混淆前：
name email sensitive_field
John Doe [email protected] CC: 4532-1234-5678-9012混淆后：
name email sensitive_field
(REDACTED) (REDACTED) (REDACTEd)结论
DuckDB 是一个简单但功能强大的内存数据库，可以帮助处理敏感数据。
请记住：
- 验证您的掩码数据。
- 对于大型数据集，请使用并行处理。
- 利用 DuckDB 的 S3 集成处理云数据。
- 在处理大型文件时注意内存使用情况。
Source:
https://dzone.com/articles/developers-guide-handling-sensitive-data-with-duckdb