azure - 优化 Spark SQL 查询以进行数据聚合

Question

我正在尝试使用 Spark SQL 从 Azure 数据库聚合数据。我看到别人最初的做法是这样的:

select
max(Timestamp),
mean(datacolumn1),
median(datacolumn2),
max(datacolumn3)
from myTable where datacolumn2 = "Yes"
and Timestamp between "1684738800000000" and "1684825200000000"
GROUP BY FLOOR((Timestamp)/(N*U*1E6))*(N*U*1E6)

其中 N = 数字，针对聚合类型的整数乘数，U = 聚合类型中的秒数(即秒 = 1、分钟 = 60、小时 = 3600 等)。这应该对这些组内的数据进行平均，但我看到的问题是，当我在本地转换时间戳时，时间戳不正确，这肯定是查询的问题，但我不知道到底是什么。所以我尝试完全重写它:

WITH Aggregated AS (
    -- Filter data, convert timestamps, and aggregate in one step
    SELECT
        FLOOR(Timestamp / (1000000 * N * U)) * (N * U) AS grouped_timestamp,
        min(Timestamp) as min_timestamp,
        AVG(data1) AS data1,
        AVG(data2) AS data2,
        AVG(data3) AS data3
    FROM dataBaseTable
    WHERE Timestamp BETWEEN UNIXtstart AND UNIXtstop
    GROUP BY FLOOR(Timestamp / (1000000 * N * U))
)

SELECT
    grouped_timestamp,
    from_unixtime(CAST(grouped_timestamp / 1000000 AS BIGINT)) AS datetime_representation,
    data1,
    data2,
    data3
FROM Aggregated
ORDER BY grouped_timestamp;

虽然它工作得很好，但速度也非常慢。大约慢 120 倍，这是不现实的，因为数据密度约为微秒。因此，如果我尝试每月甚至每年查询数据的时间戳，最终会在服务器上运行数十亿行。所以我尝试通过改变一些事情来优化它:

WITH Aggregated AS (
    -- Filter data, convert timestamps, and aggregate in one step
    SELECT
        CAST(FLOOR(CAST(Timestamp AS BIGINT) / (1000000 * N * U)) * (N * U) AS BIGINT) AS grouped_timestamp,
        MIN(Timestamp) as min_timestamp,
        AVG(data1) AS data1,
        AVG(data2) AS data2,
        AVG(data3) AS data3
    FROM dataBaseTable
    WHERE Timestamp BETWEEN UNIXtstart AND UNIXtstop
    GROUP BY FLOOR(CAST(Timestamp AS BIGINT) / (1000000 * N * U))
)

SELECT
    grouped_timestamp,
    from_unixtime(CAST(grouped_timestamp / 1000000 AS BIGINT)) AS datetime_representation,
    data1,
    data2,
    data3
FROM Aggregated
ORDER BY grouped_timestamp;

但是，这会产生不正确的时间戳(即-8741307600)，当我查找它时，这意味着可能存在整数溢出。我尝试通过显式将 Timestamp 转换为 bigint 来避免它，但这不起作用。尽管这是最后一个选项更快，但我认为它的数学计算不正确。第一个选项是理想的，但并不正确。第二个选项是正确的，但速度太慢。有没有一种方法能够正确、快速地聚合包含数十亿行的大型数据集？

注意:UNIXtstart、UNIXtstop、N 和 U 都是随每个查询而更改的用户输入。这是一个模仿我正在使用的数据结构的测试矩阵:https://dbfiddle.uk/JadOeVwF

PS - 这是 Spark SQL，当我输入 Spark 时找不到它的标签，或者至少可能有一个名称不同但在标签中含义相同的名称。不知道

Answer 1

我在我的环境中尝试了对示例数据的查询，其中 N 为 1，U 为 3600，持续了一小时。即使我得到了和你一样的结果，错误的时间戳。

但是当我给 U 35 (2100) 分钟时就可以了。

因此，您可以使用下面更改后的查询来获取正确的时间戳。

WITH Aggregated AS (
    SELECT
        CAST(floor(CAST(Timestamp  AS  BIGINT) /  1000000  /  N  /  U) * (N  *  U) AS  BIGINT) AS grouped_timestamp,
        MIN(Timestamp) as min_timestamp,
        AVG(data1) AS data1,
        AVG(data2) AS data2,
        AVG(data3) AS data3
        FROM sample1
        WHERE  Timestamp  BETWEEN  "1684738800000000"  and  "1684825200000000"
        GROUP  BY  FLOOR(CAST(Timestamp  AS  BIGINT) /  1000000  /  N /  U)
        )
        
SELECT
    grouped_timestamp,
    from_unixtime(CAST(grouped_timestamp AS  BIGINT)) AS datetime_representation,
    data1,
    data2,
    data3
FROM Aggregated
ORDER  BY grouped_timestamp;

输出:

在这里，您可以看到我获得了 1 小时时间范围内的聚合值并持续 2 小时。