如何在BigQuery查询中为代理键选择指定字段

你当前用SHA256结合TO_JSON_STRING生成代理键时，会包含所有关联表的全部字段，想要仅基于SELECT语句中明确指定的输出字段生成哈希，可通过以下两种方式解决：

核心思路 #

原写法中STRUCT(user_, promo, ...)会将整个表对象的所有字段纳入哈希计算，只需改成仅包含SELECT中定义的、经过处理后的输出字段即可。

方式一：直接在STRUCT中列出目标字段 #

把SELECT里每一个要用来生成哈希的字段，明确写入STRUCT中，确保只包含这些最终输出的字段值。

方式二：通过子查询先筛选字段再生成哈希 #

先通过子查询提取所有需要的字段，再在外部查询中基于子查询结果生成哈希，避免重复编写字段列表，代码更简洁易维护。

修改后的代码示例 #

方式一：直接指定字段的写法 #

替换原查询中_surrogate_key部分的代码：

, SHA256(
    TO_JSON_STRING(
        STRUCT(
            _operating_country,
            id,
            `state`,
            city,
            zipcode,
            gender,
            date_of_birth,
            email,
            first_name,
            last_name,
            username,
            join_form_status,
            has_account,
            account_type,
            promocode,
            channel_acquisition,
            activation_timestamp,
            cancellation_timestamp,
            user_created_timestamp,
            card_expiry_timestamp
        )
    )
) AS _surrogate_key

方式二：子查询嵌套的写法 #

将原查询改写成子查询结构，外部查询负责生成哈希：

SELECT
    *,
    SHA256(TO_JSON_STRING(STRUCT(
        _operating_country,
        id,
        `state`,
        city,
        zipcode,
        gender,
        date_of_birth,
        email,
        first_name,
        last_name,
        username,
        join_form_status,
        has_account,
        account_type,
        promocode,
        channel_acquisition,
        activation_timestamp,
        cancellation_timestamp,
        user_created_timestamp,
        card_expiry_timestamp
    ))) AS _surrogate_key,
    CURRENT_DATE("UTC") AS _ingestion_date,
    CURRENT_DATETIME("UTC") AS _ingestion_datetime,
    valid_from,
    valid_to
FROM (
    SELECT
        user_._operating_country AS _operating_country
        /* DEMOGRAPHICS */
        , user_.userId AS id
        , UPPER(address_.county) AS `state`
        , UPPER(address_.city) AS city
        , UPPER(address_.postcode) as zipcode
        , CASE UPPER(user_.gender)
            WHEN "FEMALE" THEN "Female"
            WHEN "MALE" THEN "Male"
            ELSE "Unknown"
        END AS gender
        , user_.dob AS date_of_birth

        /* PII */
        , user_.email AS email
        , user_.firstName AS first_name
        , user_.lastName AS last_name
        , user_.username AS username
        
        /* ACCOUNT */
        , user_.joinFormStatus AS join_form_status
        , account.userId IS NOT NULL AS has_account
        , account.accountType AS account_type 
        , UPPER(
            COALESCE(
                parent_promo.code, parent_promo_ref.code, promo.code, promo_ref.code
            )
        ) AS promocode 
        , COALESCE(
            parent_promo_group.description, parent_promo_group_ref.description, promo_group_ref.description, promo_group_ref.description
        ) AS channel_acquisition
        /* TIMESTAMPS */
        , user_.activationDate AS activation_timestamp
        , user_.cancellationDate AS cancellation_timestamp
        , user_.created AS user_created_timestamp
        , account.cardExpiryDate AS card_expiry_timestamp
        , user_._scd_valid_from AS valid_from
        , user_._scd_valid_to AS valid_to

        FROM `user_` AS user_

        -- 保留原查询所有JOIN与WHERE逻辑
        LEFT JOIN `account` AS account 
            ON user_.userId = account.userId 
            AND user_._operating_country = account._operating_country
            AND user_._metadata_timestamp BETWEEN account._scd_valid_from AND COALESCE(account._scd_valid_to, CURRENT_TIMESTAMP())
    

        LEFT JOIN `promo` AS promo 
            ON UPPER(TRIM(user_.signupPromo)) = promo.code 
            AND user_._operating_country = promo._operating_country
            AND user_._metadata_timestamp BETWEEN promo._scd_valid_from AND COALESCE(promo._scd_valid_to, CURRENT_TIMESTAMP())
    

        LEFT JOIN `promo_group` AS promo_group 
            ON promo.groupId = promo_group.groupId 
            AND promo._operating_country = promo_group._operating_country
            AND promo._metadata_timestamp BETWEEN promo_group._scd_valid_from AND COALESCE(promo_group._scd_valid_to, CURRENT_TIMESTAMP())
    

        LEFT JOIN `promo_pair` AS promo_pair 
            ON IFNULL(promo.code, RIGHT(UPPER(TRIM(user_.signupPromo)), 2)) = promo_pair.pair 
            AND promo._operating_country = promo_pair._operating_country
        
        LEFT JOIN `promo_ref` AS promo_ref 
            ON promo_pair.inviteePromotion = promo_ref.code 
            AND promo_pair._operating_country = promo_ref._operating_country
        
        LEFT JOIN `promo_group_ref` AS promo_group_ref 
            ON promo_ref.groupId = promo_group_ref.groupId 
            AND promo_ref._operating_country = promo_group_ref._operating_country
            AND promo_ref._metadata_timestamp BETWEEN promo_group_ref._scd_valid_from AND COALESCE(promo_group_ref._scd_valid_to, CURRENT_TIMESTAMP())
      
          
          /* Parents of children users users */
        LEFT JOIN `relations` AS relations 
            ON user_.userId = relations.userId 
            AND relations.relationType = 'CHILD' 
            AND user_._operating_country = relations._operating_country
            AND user_._metadata_timestamp BETWEEN relations._scd_valid_from AND COALESCE(relations._scd_valid_to, CURRENT_TIMESTAMP())
        
        
        LEFT JOIN `parent` AS parent 
            ON relations.relatedId = parent.userId 
            AND relations._operating_country = parent._operating_country
            AND relations._metadata_timestamp BETWEEN parent._scd_valid_from AND COALESCE(parent._scd_valid_to, CURRENT_TIMESTAMP())
        
        
        LEFT JOIN `parent_promo` AS parent_promo 
            ON UPPER(TRIM(parent.signupPromo)) = parent_promo.code
            AND parent._operating_country = parent_promo._operating_country
            AND parent._metadata_timestamp BETWEEN parent_promo._scd_valid_from AND COALESCE(parent_promo._scd_valid_to, CURRENT_TIMESTAMP())
        
        
        LEFT JOIN `parent_promo_group` AS parent_promo_group 
            ON parent_promo.groupId = parent_promo_group.groupId 
            AND parent_promo._operating_country = parent_promo_group._operating_country
            AND parent_promo._metadata_timestamp BETWEEN parent_promo_group._scd_valid_from AND COALESCE(parent_promo_group._scd_valid_to, CURRENT_TIMESTAMP())
        
        
        LEFT JOIN `parent_promo_pair` AS parent_promo_pair 
            ON IFNULL(parent_promo.code, RIGHT(UPPER(TRIM(parent.signupPromo)), 2)) = parent_promo_pair.pair 
            AND IFNULL(parent_promo._operating_country, parent._operating_country) = parent_promo_pair._operating_country
                
        LEFT JOIN `parent_promo_ref` AS parent_promo_ref 
            ON parent_promo_pair.inviteePromotion = parent_promo_ref.code 
            AND parent_promo_pair._operating_country = parent_promo_ref._operating_country
        
        LEFT JOIN `parent_promo_group_ref` AS parent_promo_group_ref 
            ON parent_promo_ref.groupId = parent_promo_group_ref.groupId
            AND parent_promo_ref._operating_country = parent_promo_group_ref._operating_country
            AND parent_promo_ref._metadata_timestamp BETWEEN parent_promo_group_ref._scd_valid_from AND COALESCE(parent_promo_group_ref._scd_valid_to, CURRENT_TIMESTAMP())
        
        
        LEFT JOIN `address_` AS address_ 
            ON COALESCE(parent.userId, user_.userId) = address_.userId 
            AND COALESCE(parent._operating_country, user_._operating_country) = address_._operating_country
            AND COALESCE(parent._metadata_timestamp, user_._metadata_timestamp) BETWEEN address_._scd_valid_from AND COALESCE(address_._scd_valid_to, CURRENT_TIMESTAMP())
        
    
        WHERE GREATEST(
                    user_._ingestion_date
                    , address_._ingestion_date
                    , relations._ingestion_date
                    , account._ingestion_date
                    , promo._ingestion_date
                    , promo_group._ingestion_date
                    , promo_ref._ingestion_date
                    , promo_group_ref._ingestion_date
                    , parent._ingestion_date
                    , parent_promo._ingestion_date
                    , parent_promo_group._ingestion_date
                    , parent_promo_ref._ingestion_date
                    , parent_promo_group_ref._ingestion_date
                ) > (SELECT MAX(_ingestion_date) FROM `destination_table`)
) AS core_data

注意事项 #

• 确保STRUCT中的字段与SELECT输出的字段完全匹配，包括别名和计算后的结果，保证哈希值准确对应最终输出内容。
• 后续修改SELECT字段时，需同步更新STRUCT中的字段列表，避免哈希与实际字段不匹配。

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