Azure Databricks批量替换Excel中JSON用户哈希为随机掩码值

我们的文件存储中有一个Excel文件，包含超过10000行JSON数据，示例JSON如下：

{"SearchName":"","Id":"","RequestType":"","StartDateUtc":"2022-12-01T00:00:00Z","EndDateUtc":"2023-04-28T00:00:00Z","RecordType":null,"Workload":"","Operations":[],"Users":["d503246e-285c-41bc-8b0a-bc79824146ea,ingrid.van.driel@vroon.nl,ab6019a4-851c-4af2-8ddc-1e03ee9be97a,bart.van.someren@vroon.nl,85ff7cda-5f2d-4d32-b51c-b88ad4d55b5a,nicky.ongenae@vroon.nl,48168530-659c-44d3-8985-65f9b0af2b85,erwin.weeda@vroon.nl,0937a1e5-8a68-4573-ae9c-e13f9a2f3617,Thessa.vanden.Oetelaar@vroon.nl,c822dd8b-0b79-4c13-af1e-bc080b8108c5,Hester.Blankenstein@vroon.nl,ca0de5ba-6ab2-4d34-b19d-ca702dcbdb8d,Alvin.Baltonado@ph.vroonshipmanagement.com"],"ObjectIds":[],"IPAddresses":[],"SiteIds":null,"AssociatedAdminUnits":[],"FreeText":"multifactor","ResultSize":0,"TimeoutInSeconds":345600,"ScopedAdminWithoutAdminUnits":false}

需要将JSON中Users字段内的所有用户哈希（UUID格式）和真实邮箱替换为随机掩码值（比如sam@contoso.com）。目前手动复制用户数据执行以下代码处理，效率极低：

import random  

main=['nzn1@contoso.com', 'oman2@contoso.com', 'oman3@contoso.com', 'oman4@contoso.com', 'oman5@contoso.com', 'oman6@contoso.com', 'oman7@contoso.com', 'oman8@contoso.com', 'oman9@contoso.com', 'omaz1@contoso.com', 'omaz2@contoso.com', 'omaz3@contoso.com', 'omaz4@contoso.com', 'omaz5@contoso.com', 'omaom6@contoso.com', 'omax7@contoso.com', 'omaz8@contoso.com', 'omaz9@contoso.com', 'omay1@contoso.com', 'omay2@contoso.com', 'omaom3@contoso.com', 'omax4@contoso.com', 'omax5@contoso.com', 'omax6@contoso.com', 'omaom7@contoso.com', 'omaw8@contoso.com', 'omaw9@contoso.com', 'omae1@contoso.com', 'omae2@contoso.com', 'omae3@contoso.com', 'omae4@contoso.com', 'omae5@contoso.com', 'omae6@contoso.com', 'omae7@contoso.com', 'omae8@contoso.com', 'omae9@contoso.com', 'omaf1@contoso.com', 'omaf2@contoso.com', 'omaf3@contoso.com', 'omaf4@contoso.com', 'omaf5@contoso.com', 'omaf6@contoso.com', 'omaf7@contoso.com', 'omaf8@contoso.com', 'omaf9@contoso.com', 'omag1@contoso.com', 'omag2@contoso.com', 'omag3@contoso.com', 'omag4@contoso.com', 'omah1@contoso.com', 'omay5@contoso.com', 'omal1@contoso.com', 'omam6@contoso.com', 'omao1@contoso.com', 'omaom2@contoso.com', 'omao4@contoso.com', 'omag5@contoso.com', 'omah2@contoso.com', 'omay6@contoso.com', 'omal2@contoso.com', 'omam7@contoso.com', 'omao2@contoso.com', 'omao5@contoso.com', 'omao6@contoso.com', 'omag6@contoso.com', 'omah3@contoso.com', 'omay7@contoso.com', 'omal3@contoso.com', 'omam8@contoso.com', 'omao3@contoso.com', 'omao7@contoso.com', 'omao9@contoso.com', 'omag7@contoso.com', 'omah4@contoso.com', 'omaq1@contoso.com', 'omaq3@contoso.com', 'omai3@contoso.com', 'omah8@contoso.com', 'omax3@contoso.com', 'omal8@contoso.com', 'oman4@contoso.com', 'omaq2@contoso.com', 'omaq4@contoso.com', 'omaq5@contoso.com', 'omai4@contoso.com', 'omah9@contoso.com', 'omaom4@contoso.com', 'omal9@contoso.com', 'oman5@contoso.com', 'omaom3@contoso.com', 'omaq6@contoso.com', 'omaq8@contoso.com', 'omai5@contoso.com', 'omai1@contoso.com', 'omaom5@contoso.com', 'omam1@contoso.com', 'oman6@contoso.com', 'omaq7@contoso.com', 'omaq9@contoso.com', 'omar2@contoso.com', 'omai6@contoso.com', 'omaw1@contoso.com', 'omaz6@contoso.com', 'omam2@contoso.com', 'oman7@contoso.com', 'omar1@contoso.com', 'omar3@contoso.com', 'omar5@contoso.com', 'omai7@contoso.com', 'omaw2@contoso.com', 'omaz7@contoso.com', 'omam3@contoso.com', 'oman8@contoso.com', 'omar4@contoso.com', 'omar6@contoso.com', 'omar8@contoso.com', 'omai8@contoso.com', 'omay3@contoso.com', 'omaom8@contoso.com', 'omam4@contoso.com', 'oman9@contoso.com', 'omar7@contoso.com', 'omar9@contoso.com', 'omaom1@contoso.com', 'omai9@contoso.com', 'omay4@contoso.com', 'omax9@contoso.com', 'omam5@contoso.com', 'oman7@contoso.com', 'oman8@contoso.com', 'oman9@contoso.com', 'omaz1@contoso.com', 'omaz2@contoso.com', 'omaz3@contoso.com', 'omaz4@contoso.com', 'omaz5@contoso.com', 'omaz6@contoso.com', 'omaz7@contoso.com', 'omaz8@contoso.com', 'omaz9@contoso.com', 'omay1@contoso.com', 'omay2@contoso.com', 'omay3@contoso.com', 'omay4@contoso.com', 'omay5@contoso.com', 'omay6@contoso.com', 'omay7@contoso.com', 'omay8@contoso.com', 'omay9@contoso.com', 'omae1@contoso.com', 'omae2@contoso.com', 'omae3@contoso.com', 'omae4@contoso.com', 'omae5@contoso.com', 'omae6@contoso.com', 'omae7@contoso.com', 'omaan1@contoso.com', 'omaan2@contoso.com', 'omaan3@contoso.com', 'omaan4@contoso.com', 'omaan5@contoso.com', 'omaan6@contoso.com']  

l=["0e07209b-807b-4938-8bfd-f87cee98e924,invoices@it.vroonoffshore.com,c747a82c-656e-40eb-9194-88c4a0f8061e"]  
n=len(l)  
print(n)  
print(random.sample(main,n))

咨询：在Azure Databricks中是否存在批量替换该Excel文件中JSON的Users字段内所有用户哈希及真实邮箱为随机掩码值并写回指定位置的实现方式？

可以在Azure Databricks中通过PySpark实现批量处理，步骤如下：

1. 读取Excel文件 #

首先确保已安装Spark Excel依赖（如com.crealytics:spark-excel_2.12:0.13.7），然后读取Excel文件，假设JSON数据在名为json_column的列中：

# 读取Excel文件
df = spark.read.format("com.crealytics.spark.excel") \
    .option("header", "true") \
    .option("inferSchema", "false") \
    .load("/path/to/input/excel/file.xlsx")

2. 定义掩码处理UDF #

编写用户自定义函数，解析JSON中的Users字段，将每个哈希/邮箱替换为随机掩码邮箱：

import json
import random
from pyspark.sql.functions import udf
from pyspark.sql.types import StringType

# 掩码邮箱列表（替换为你提供的完整main列表）
mask_emails = ['nzn1@contoso.com', 'oman2@contoso.com', ...]

def mask_users(json_str):
    try:
        data = json.loads(json_str)
        users_list = data.get("Users", [])
        masked_users = []
        for user_str in users_list:
            # 分割逗号分隔的用户项
            items = user_str.split(",")
            # 为每个项随机选取掩码邮箱
            masked_items = [random.choice(mask_emails) for _ in items]
            masked_users.append(",".join(masked_items))
        data["Users"] = masked_users
        return json.dumps(data)
    except Exception:
        # 解析失败时返回原JSON
        return json_str

# 注册UDF
mask_users_udf = udf(mask_users, StringType())

3. 应用掩码处理 #

将UDF应用到JSON列，生成处理后的列：

# 生成掩码后的JSON列
processed_df = df.withColumn("masked_json", mask_users_udf(df["json_column"]))

4. 写回Excel文件 #

将处理后的结果写回指定位置：

# 写回Excel
processed_df.write.format("com.crealytics.spark.excel") \
    .option("header", "true") \
    .mode("overwrite") \
    .save("/path/to/output/excel/file.xlsx")

优化建议 #

• 若掩码列表过大，可使用广播变量减少Executor内存占用：

  from pyspark.sql.functions import broadcast
  
  # 创建广播变量
  broadcast_mask = spark.sparkContext.broadcast(mask_emails)
  
  # 修改UDF使用广播变量
  def mask_users(json_str):
      try:
          data = json.loads(json_str)
          users_list = data.get("Users", [])
          masked_users = []
          mask_list = broadcast_mask.value
          for user_str in users_list:
              items = user_str.split(",")
              masked_items = [random.choice(mask_list) for _ in items]
              masked_users.append(",".join(masked_items))
          data["Users"] = masked_users
          return json.dumps(data)
      except Exception:
          return json_str
  

• 针对超大文件，可调整spark.sql.shuffle.partitions参数提升并行处理速度。

内容的提问来源于stack exchange，提问作者data Greek