Vertica – Projection Refreshes

Before a new projection can go live, it needs to be refreshed from the raw data.A refresh transfers data to projections that are not able to participate in query execution due to missing or out-of-date data.

START_REFRESH()

Several rules to being able to do an asynchronous refresh mainly (Full details here)

  • All nodes must be up in order to start a refresh..
  • Runs in the background asynchronously
SELECT START_REFRESH();

REFRESH()

Slightly different in that it performs a synchronous, optionally-targeted refresh of a specified table’s projections. (Full details here)

select refresh('schema.table');:

MONITORING

select node_name, projection_name, refresh_status, refresh_start 
from projection_refreshes where anchor_table_name = 'table';

More detail for refreshing projections

select node_name, projection_name, refresh_status, 
refresh_start, refresh_duration_sec, runtime_priority 
from projection_refreshes 
where is_executing = 't';

Vertica – Recovery Monitoring

Use the admintools view_cluster tool from the command line to see the cluster state:

$ /opt/vertica/bin/admintools -t view_cluster»

DB | Host | State

———+————–+————

<data_base> | 112.17.31.10 | RECOVERING

<data_base> | 112.17.31.11 | UP

<data_base> | 112.17.31.12 | UP

<data_base> | 112.17.31.17 | UP

________________________________

2 Main tables for monitoring recovery within the database –

RECOVERY_STATUS

PROJECTION_RECOVERIES

However the database has to be UP to be able to use! so only really useful when only one node is recovering and cluster is k-safety compliant.

Specifically, the recovery_status system table includes information about the node that is recovering, the epoch being recovered, the current recovery phase, and running status:

select node_name, recover_epoch, recovery_phase, current_completed, is_running 
from recovery_status;

The projection_recoveries system table maintains history of projection recoveries. To check the recovery status, you can summarize the data for the recovering node, and run the same query several times to see if the counts change. Differing counts indicate that the recovery is working and in the process of recovering all missing data.

select node_name, status , progress from projection_recoveries; 

After recovery is complete, all nodes should show UP.

Checking the epoch should show all the nodes at the same level.

EPOCH

A logical unit of time in which a single change is made to data in the system.

SELECT GET_CURRENT_EPOCH();

Vertica – Export DDL statements for objects

Useful function for exporting individual DDL for objects or multiple

EXPORT_OBJECTS

Syntax

EXPORT_OBJECTS( [ 'destination' ] , [ 'scope' ] , [ 'ksafe' ] )

Examples

--if directory selected then regardless of node this is run on, file writes to node 1
SELECT EXPORT_OBJECTS('/tmp/DDL.sql','schema.id_b0,schema.id_b1',true)
--keep directory NULL to write to screen
SELECT EXPORT_OBJECTS('','schema.events_id_b0,schema.events_id_b1',true)

Vertica – Power of Projections

A very good article which describes in comparative detail what projections are and what makes them different from indexes and materialized views.

The Power of Projections – Part 1: Understanding Projections and What They Do

The Power of Projections – Part 2: Understanding the Simplicity of Projections and the Vertica Database Designer™

The Power of Projections – Part 3: Comparing and Contrasting Projections to Materialized Views and Indexes