Vertica Database Script to monitor Data Load Streams

This Vertica Script will create a report on your load streams. I use this to identify my loading streams and give and output for how long they are running or what is their target table stats status. Also i generate a analyze_stats sql in case you need run them. monitor

WITH
    a AS
    (
        SELECT
            stream_name,
            schema_name,
            table_name,
            is_executing,
            accepted_row_count,
            rejected_row_count,
            DATEDIFF(ss,load_start::TIMESTAMP,GETDATE()::TIMESTAMP) AS DurationSec,
            ROUND((accepted_row_count+rejected_row_count)/DATEDIFF(ss,load_start::TIMESTAMP,GETDATE
            ()::TIMESTAMP),3.0) AS RowsPerSec
        FROM
            load_streams
        WHERE
            is_executing='true'
    )
    ,
    b AS
    (
        SELECT
            anchor_table_schema,
            anchor_table_name,
            SUM(row_count) AS Rows_Before_Load
        FROM
            projection_storage
        WHERE
            (
                anchor_table_schema,anchor_table_name) IN
            (
                SELECT
                    schema_name,
                    table_name
                FROM
                    a)
        GROUP BY
            anchor_table_schema,
            anchor_table_name
    )
SELECT
    a.*,
    case when ((a.accepted_row_count*100)/nullif(b.Rows_Before_Load,0)) isnull THEN 'No Data Before Load' END as LoadPercentSize,
    'select analyze_statistics('''||anchor_table_schema||'.'||anchor_table_name||''');' as RunStatCMD
FROM
    a
JOIN
    b
ON
    (
        a.table_name=b.anchor_table_name
    AND a.schema_name=b.anchor_table_schema);
    
The output will be the following:
stream_name                                    schema_name  table_name    is_executing  accepted_row_count  rejected_row_count  DurationSec  RowsPerSec  LoadPercentSize      RunStatCMD                                          
---------------------------------------------  -----------  ------------  ------------  ------------------  ------------------  -----------  ----------  -------------------  --------------------------------------------------  

Loading staging.Table  from Sql Server         staging      Table         true          401920              10                  15           26795.333   No Data Before Load  select analyze_statistics('staging.Table');