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.
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);
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');
