azure - 如何爆炸而不丢失空值？

Question

如何在 U-SQL 中分解数组而不丢失 Null 值？

在正常情况下交叉应用 | Explode()，空值丢失。

我从 here 中提取了以下脚本:

@content =
        SELECT *
        FROM(
            VALUES
            (
                12,
                "a, b, c,NULL"
            ),
            (  75,
                "f, g, h, i, j"
            )) AS t([ID], LetterList);         
    @inArray =
        SELECT [ID],
            SqlArray.Create(LetterList.Split(',')) AS LetterArray
        FROM @content;         
    @exploded =
        SELECT [ID],
            letter.Trim() AS Letter
        FROM @inArray
        CROSS APPLY
        EXPLODE(LetterArray) AS r(letter);         
    OUTPUT @exploded
    TO "/Outputs/explosion.tsv"
    USING Outputters.Tsv();

我期望的输出是:

ID - 信件
------------
12 - 一个
12 - b
12-c
12 - 空
75 - f
75克
75 - 小时
75 - 我
75 - j

Answer 1

您可以使用OUTER APPLY :

When OUTER APPLY is specified, one row is produced for each row of the left rowset even when the right-side rowset expression returns an empty rowset for that row.

@exploded =
        SELECT [ID],
            letter.Trim() AS Letter
        FROM @inArray
        OUTER APPLY
        EXPLODE(LetterArray) AS r(letter);