Spark SQL

-- Examples
SELECT  bool_and(col) AS result
FROM    (
          VALUES (false),
                 (false),
                 (NULL)
        ) AS tab(col);

SELECT  account_id AS volc_account_id,
        product_code AS pm_product_code,
        MIN(`created_time`) AS stage_time
FROM    (
            SELECT  account_id,
                    temp.product_code,
                    temp.tag_value,
                    row_number() OVER (
                        PARTITION BY
                                account_id,
                                temp.product_code,
                                temp.tag_value
                        ORDER BY
                                tag.created_time DESC
                    ) AS rn,
                    tag.created_time
            FROM    xxx.dim_account_tag_df AS tag
            LATERAL VIEW
                    EXPLODE(json_to_map(tag.tag_value)) temp AS product_code,
                    tag_value
            WHERE   tag.date = '${date}'
            AND     tag.tag_key = 'xxx'
        )
WHERE   rn = 1
AND     tag_value = 1
GROUP BY
        account_id,
        product_code

INSERT OVERWRITE TABLE db.table PARTITION (date = '${date}')
SELECT  id,
        sla_condition_str,
        sla_id,
        lower_condition,
        lower_target,
        upper_condition,
        upper_target,
        compensate_rate,
        version,
        created_time,
        updated_time,
        status,
        is_deleted
FROM    db.tablename
WHERE   date = '${date}'
UNION
VALUES 
(
    1000001, -- id
    '[0.99,1.0]', -- condition_str
    1000001, -- sla_id
    '', -- lower_condition
    '', --lower_target
    '', -- upper_condition
    '', -- upper_target
    '', -- compensate_rate
    NULL, -- version
    NULL, -- created_time
    NULL, -- updated_time
    0,
    '0' --is_deleted
)

SELECT  a <=> b, -- true
        a = b    -- NULL
FROM    (
            SELECT  NULL AS a,
                    NULL AS b
        )

WITH
-- TLS Kafka 不保证数据 Exactly once，这里完全去重是为了做类似幂等的处理
distinct_log AS(
    SELECT  DISTINCT *
    FROM    eps_volc_sla.ods_sla_raw_http_log_from_tls_hourly
    WHERE   date = '${date}'
    AND     hour = '${hour}'
    AND     sli_key IN (
                -- TOS
                'tos_http_request_success_rate',
                -- TLS
                'tls_request_err_rate'
            )
),
-- 补点逻辑，小时级任务按小时补
template AS (
    SELECT  account_id,
            extra,
            sli_key,
            CAST((UNIX_TIMESTAMP('${date}${hour}', 'yyyyMMddHH') + i * (period * 60)) AS BIGINT) AS sli_time
    FROM    (
                SELECT  account_id,
                        extra,
                        sli_key,
                        period
                FROM    distinct_log
                GROUP BY
                        account_id,
                        extra,
                        sli_key,
                        period
            )
    LATERAL VIEW
            -- 例如: 一分钟一个点(period=1)，一个小时就需要构造 60 个点  => SPLIT(SPACE(59), ' ')
            -- TOS 和 TLS 现在都是按照 1 小时补点
            POSEXPLODE(SPLIT(SPACE(60 / period - 1), ' ')) seq AS i,
            x
),
-- 指标计算过程
indicator AS (
    SELECT  (`time` - `time` % (60 * period)) AS sli_time,
            UNIX_TIMESTAMP() AS event_time,
            sli_key,
            extra,
            account_id,
            CASE WHEN sli_key = 'tos_http_request_success_rate' THEN
                 -- TOS 请求成功率
                 1 - CAST(COUNT(IF(val >= 500 AND val < 600, 1, NULL)) AS DOUBLE) / COUNT(val)
                 WHEN sli_key IN ('tls_request_err_rate') THEN
                 -- TLS 请求错误率
                 CAST(COUNT(IF(val >= 500 AND val < 600, 1, NULL)) AS DOUBLE) / COUNT(val)
            END AS sli_value
    FROM    distinct_log
    GROUP BY
            account_id,
            extra,
            sli_key,
            period,
            (`time` - `time` % (60 * period))
)
INSERT OVERWRITE TABLE eps_volc_sla.dwd_sli_event_log_tls_hourly PARTITION (date = '${date}', hour = '${hour}')
SELECT  template.sli_time,
        indicator.event_time,
        template.sli_key,
        template.extra,
        template.account_id AS vol_account_id,
        CASE WHEN template.sli_key = 'tos_http_request_success_rate' THEN
             -- TOS 成功率，默认成功率为 1
             COALESCE(indicator.sli_value, 1)
             WHEN template.sli_key IN ('tls_request_err_rate') THEN
             -- TLS 错误率，默认错误率为 0
             COALESCE(indicator.sli_value, 0)
        END AS sli_value
FROM    template
LEFT JOIN
        indicator
ON      template.account_id = indicator.account_id
AND     template.extra = indicator.extra
AND     template.sli_key = indicator.sli_key
AND     template.sli_time = indicator.sli_time