Can I monitor a SELECT REFRESH_COLUMNS ( 'table' )

alalmeida

I've a table with 2 billions records and I'm testing the "amazing" feature called "Flattened Table". My question is, can I monitor a refresh_columns? I'm waiting for 6 hours and don't know what's happening.
Thank you

bhanu_kishore

Below table gives progress about projections being refreshed for a given table..

PROJECTION_REFRESHES

[Deleted User]

6 hours is lot of time . Do you have support contract ? if yes please open support case so that we can review further.

alalmeida

Yes Skamat we have contract. How can I send this problem to you. Today I spent 96,000 seconds in one refresh

[Deleted User]

Please open support ticket and include note to pass it to shrirang .

brocha

is there an answer to the original question? Is there a way to monitor progress of SELECT REFRESH_COLUMNS (with rebuild as the refresh method).
(The suggested answer is for projection refresh not column refresh)
Also if need to rebuild columns in multiple big tables will calling a single refresh_columns command with comma separated list of tables do the tables sequentially or in parallel?

SergeB

I believe this DC table dc_refresh_columns contains that information.

Jim_Knicely

That's correct! The dc_refresh_columns Data Collector table was introduced in Vertica 9.3:

dbadmin=> SELECT DISTINCT description FROM data_collector WHERE table_name = 'dc_refresh_columns';
           description
---------------------------------
 History of all column refreshes
(1 row)

See:
https://www.vertica.com/docs/ReleaseNotes/9.3.x/Vertica_9.3.x_Release_Notes.htm

In particular:

VER-68379 - Calls to REFRESH_COLUMNS can now be monitored in the new "dc_refresh_columns" table, which logs, for every call to REFRESH_COLUMNS, the time of the call, the name of the table, the refreshed columns, the mode used, the minimum and maximum key values, and the epoch.