如何获取表名、当日原始数据量、SQL作业状态及起止时间？

一、SQL Server 环境查询方案 #

SQL Server的作业日志存储在msdb系统库中，结合临时表数据统计，可通过以下SQL获取目标信息：

SELECT
    -- 从作业命令中提取临时表名（需根据你的作业语句调整匹配逻辑）
    SUBSTRING(js.command, CHARINDEX('FROM staging.', js.command) + 12, 
              CHARINDEX(' ', js.command, CHARINDEX('FROM staging.', js.command)) - CHARINDEX('FROM staging.', js.command) - 12) AS 表名,
    -- 查询当日临时表的原始数据量（假设表有create_time时间字段）
    (SELECT COUNT(*) FROM staging.[表名] WHERE CONVERT(DATE, create_time) = CONVERT(DATE, GETDATE())) AS 当日原始数据量,
    -- 转换作业状态为可读文本
    CASE jh.run_status 
        WHEN 0 THEN '失败'
        WHEN 1 THEN '成功'
        WHEN 2 THEN '重试'
        ELSE '未知'
    END AS 作业状态,
    -- 计算作业开始时间
    CONVERT(DATETIME, RTRIM(jh.run_date)) + (jh.run_time / 10000.0 / 24 + (jh.run_time % 10000) / 100.0 / 60 / 24 + (jh.run_time % 100) / 60 / 60 / 24) AS 作业开始时间,
    -- 计算作业结束时间
    DATEADD(SECOND, jh.run_duration / 10000 * 3600 + (jh.run_duration % 10000) / 100 * 60 + jh.run_duration % 100, 
            CONVERT(DATETIME, RTRIM(jh.run_date)) + (jh.run_time / 10000.0 / 24 + (jh.run_time % 10000) / 100.0 / 60 / 24 + (jh.run_time % 100) / 60 / 60 / 24)) AS 作业结束时间
FROM 
    msdb.dbo.sysjobhistory jh
JOIN 
    msdb.dbo.sysjobs j ON jh.job_id = j.job_id
JOIN 
    msdb.dbo.sysjobsteps js ON jh.job_id = js.job_id AND jh.step_id = js.step_id
WHERE 
    -- 筛选当日运行的作业
    CONVERT(DATE, CONVERT(DATETIME, RTRIM(jh.run_date)) + (jh.run_time / 10000.0 / 24 + (jh.run_time % 10000) / 100.0 / 60 / 24 + (jh.run_time % 100) / 60 / 60 / 24)) = CONVERT(DATE, GETDATE())
    -- 替换为你的同步作业名称
    AND j.name = '每日Staging到Warehouse同步作业'

说明：如果临时表命名有统一规律（如staging.tbl_xxx），可调整SUBSTRING的提取逻辑；若作业步骤操作的表不固定，建议维护临时表清单表做关联查询。

二、Snowflake 环境查询方案 #

Snowflake的任务（作业）历史和查询日志可通过INFORMATION_SCHEMA视图获取：

SELECT
    t.TABLE_NAME AS 表名,
    -- 统计当日插入临时表的数据量
    (SELECT SUM(ROWS_INSERTED) FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
        DATEADD('day', -1, CURRENT_DATE()), CURRENT_DATE())) 
     WHERE QUERY_TEXT LIKE '%INSERT INTO staging.' || t.TABLE_NAME || '%'
        AND QUERY_TYPE = 'INSERT') AS 当日原始数据量,
    -- 转换任务状态为可读文本
    CASE th.STATUS 
        WHEN 'SUCCEEDED' THEN '成功'
        WHEN 'FAILED' THEN '失败'
        WHEN 'RUNNING' THEN '运行中'
        ELSE '未知'
    END AS 作业状态,
    th.QUERY_START_TIME AS 作业开始时间,
    th.QUERY_END_TIME AS 作业结束时间
FROM 
    INFORMATION_SCHEMA.TABLES t
JOIN 
    TABLE(INFORMATION_SCHEMA.TASK_HISTORY(
        DATEADD('day', -1, CURRENT_DATE()), CURRENT_DATE())) th
ON 
    th.QUERY_TEXT LIKE '%staging.' || t.TABLE_NAME || '%'
WHERE 
    -- 替换为你的临时表Schema
    t.TABLE_SCHEMA = 'STAGING'
    -- 替换为你的同步任务名称
    AND th.TASK_NAME = '每日同步任务'

三、通用优化方案（推荐） #

如果作业步骤复杂，直接解析系统日志容易出错，建议在同步逻辑中新增自定义日志表，主动记录关键信息：

-- 1. 创建同步日志表
CREATE TABLE sync_job_log (
    log_id INT IDENTITY(1,1) PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    raw_data_count INT NOT NULL,
    job_status VARCHAR(20) NOT NULL,
    start_time DATETIME NOT NULL,
    end_time DATETIME NOT NULL,
    sync_date DATE NOT NULL
);

-- 2. 在每个表同步步骤末尾插入日志（示例）
DECLARE @start_time DATETIME = GETDATE();
-- 此处为你的同步逻辑：INSERT INTO warehouse.tbl_user SELECT * FROM staging.tbl_user WHERE ...
INSERT INTO sync_job_log (table_name, raw_data_count, job_status, start_time, end_time, sync_date)
VALUES (
    'staging.tbl_user',
    (SELECT COUNT(*) FROM staging.tbl_user WHERE create_time >= CONVERT(DATE, GETDATE())),
    '成功',
    @start_time,
    GETDATE(),
    CONVERT(DATE, GETDATE())
);

后续查询直接从日志表取数即可，无需解析作业命令：

SELECT 
    table_name AS 表名,
    raw_data_count AS 当日原始数据量,
    job_status AS 作业状态,
    start_time AS 作业开始时间,
    end_time AS 作业结束时间
FROM sync_job_log
WHERE sync_date = CONVERT(DATE, GETDATE());

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