BigQuery中使用Regexp Extract提取AccessEntry的用户/组信息问题

假设你的BigQuery表中存储的原始字段内容格式如下：

"<AccessEntry: role=READER,
userbyemail=sjack@gmail.com>"

"<AccessEntry: role=WRITER,
specialgroup=projectReaders>"

可以通过以下SQL语句提取userbyemail/specialgroup字段名到entity_type列，对应的值到entity_value列：

SELECT
  access_entry AS original_entry,
  -- 提取实体类型（userbyemail或specialgroup）
  REGEXP_EXTRACT(cleaned_entry, r'(userbyemail|specialgroup)') AS entity_type,
  -- 提取实体对应的值
  REGEXP_EXTRACT(cleaned_entry, r'(userbyemail|specialgroup)=([^\s]+)') AS entity_value
FROM (
  SELECT
    access_entry,
    -- 清理原始字符串中的引号、尖括号等冗余符号
    REGEXP_REPLACE(access_entry, r'^"|<|>"$', '') AS cleaned_entry
  FROM
    `your_dataset.your_table` -- 替换为你的数据集和表名
)

正则说明 #

• REGEXP_REPLACE(access_entry, r'^"|<|>"$', '')：移除字符串开头的"、所有<以及结尾的>"，得到干净的AccessEntry: role=READER, userbyemail=sjack@gmail.com格式内容
• r'(userbyemail|specialgroup)'：精准匹配两种实体类型，捕获为entity_type
• r'(userbyemail|specialgroup)=([^\s]+)'：匹配实体类型后的赋值部分，捕获等号后的非空白字符作为entity_value

如果你的数据中存在单个条目包含多个实体的情况，可以改用REGEXP_EXTRACT_ALL提取数组后展开：

SELECT
  access_entry AS original_entry,
  entity_type,
  entity_value
FROM (
  SELECT
    access_entry,
    REGEXP_REPLACE(access_entry, r'^"|<|>"$', '') AS cleaned_entry
  FROM
    `your_dataset.your_table`
),
UNNEST(REGEXP_EXTRACT_ALL(cleaned_entry, r'(userbyemail|specialgroup)=([^\s]+)')) AS match,
UNNEST([SPLIT(match, '=')[OFFSET(0)]]) AS entity_type,
UNNEST([SPLIT(match, '=')[OFFSET(1)]]) AS entity_value

内容的提问来源于stack exchange，提问作者Shaker Khan