REFRESH (프로젝션 함수)

00 개요

• 목적: Vertica 안에 Projection이라는 개념이 있는데, 이 Projection을 어떻게 하는  REFRESH() 함수에 대해 정리하기

01 REFRESH()

1. 정의 및 기능

• Synchronously refreshes one or more table projections in the foreground, and updates the PROJECTION_REFRESHES system table. 
• If you run REFRESH with no arguments, it refreshes all projections that contain stale data.
• If a refresh would violate a table or schema disk quota, the operation fails.
• This is a meta-function. You must call meta-functions in a top-level SELECT statement.

2. 문법

REFRESH ( [ '[[database.]schema.]table[,...]' ] )

1) Parameters

• [database.]schema
  • Database and schema. The default schema is public. If you specify a database, it must be the current database.
• table
  • The anchor table of the projections to refresh. If you specify multiple tables, REFRESH attempts to refresh them in parallel. Such calls are part of the Database Designer deployment (and deployment script).

2) 반환값

• If REFRESH does not refresh any projections, it returns a header string with no results.

Column	Returns
Projection Name	The projection targeted for refresh
Anchor Table	The projection's associated anchor table
Status	Projections' refresh status: - queued: Queued for refresh - refreshing: Refresh is in process - refreshed: Refresh successfully completed - failed: Refresh did not successfully complete
Refresh Method	Method used to refresh the projection
Error Count	Number of times a refresh failed for the projection
Duration (sec)	How long (in seconds) the projection refresh ran

 

3) Refrech Methods

• Vertica can refresh a projection from one of its buddies, if one is available. In this case, the target projection gets the source buddy's historical data. Otherwise, the projection is refreshed from scratch with data of the latest epoch at the time of the refresh operation. In this case, the projection cannot participate in historical queries on any epoch that precedes the refresh operation. 
• To determine the method used to refresh a given projection, query the REFRESH_METHOD column from the PROJECTION_REFRESHES system table.

3. 예시

• 다음은 2개의 테이블 안에 있는 projection들을 refresh함

=> SELECT REFRESH('t1, t2');
                                             REFRESH
----------------------------------------------------------------------------------------
Refresh completed with the following outcomes:

Projection Name: [Anchor Table] [Status] [Refresh Method] [Error Count] [Duration (sec)]
----------------------------------------------------------------------------------------

"public"."t1_p": [t1] [refreshed] [scratch] [0] [0]"public"."t2_p": [t2] [refreshed] [scratch] [0] [0]

• 다음은 1개의 테이블 안에 있는 projection만 refresh됨 

=> SELECT REFRESH('allow, public.deny, t')
                                               REFRESH
----------------------------------------------------------------------------------------

Refresh completed with the following outcomes:

Projection Name: [Anchor Table] [Status] [Refresh Method] [Error Count] [Duration (sec)]
----------------------------------------------------------------------------------------
"n/a"."n/a": [n/a] [failed: insufficient permissions on table "allow"] [] [1] [0]
"n/a"."n/a": [n/a] [failed: insufficient permissions on table "public.deny"] [] [1] [0]
"public"."t_p1": [t] [refreshed] [scratch] [0] [0]

02 Refreshing Projections

1. Projection을 Refresh하는 것이란

• When you create a projection for a table that already contains data, Vertica does not automatically load that data into the new projection. Instead, you must explicitly refresh that projection. Until you do so, the projection cannot participate in executing queries on its anchor table. 

2. Refresh하는 방법

• You can refresh a projection with one of the following functions:
  • START_REFRESH 
    • refreshes projections in the current schema with the latest data of their respective anchor tables. START_REFRESH runs asynchronously in the background. 
  • REFRESH 
    • synchronously refreshes one or more table projections in the foreground.
• Both functions update system tables that maintain info about a projection's refresh status: PROJECTION_REFRESHES, PROJECTIONS, and PROJECTION_CHECKPOINT_EPOCHS.
• If a refresh would violate a table or schema disk quota, the operation fails.

3. Projection의 Refresh 정보 얻기

• You can query the PROJECTION_REFRESHES and PROJECTIONS system tables to view the progress of the refresh operation. 
• You can also call the GET_PROJECTIONS function to view the final status of projection refreshes for a given table:

=> SELECT GET_PROJECTIONS('customer_dimension');
                                           GET_PROJECTIONS
----------------------------------------------------------------------------------------------------------
 Current system K is 1.
# of Nodes: 3.
Table public.customer_dimension has 2 projections.

Projection Name: [Segmented] [Seg Cols] [# of Buddies] [Buddy Projections] [Safe] [UptoDate] [Stats]
----------------------------------------------------------------------------------------------------
public.customer_dimension_b1 [Segmented: Yes] [Seg Cols: "public.customer_dimension.customer_key"] [K: 1]
       [public.customer_dimension_b0] [Safe: Yes] [UptoDate: Yes] [Stats: RowCounts]
public.customer_dimension_b0 [Segmented: Yes] [Seg Cols: "public.customer_dimension.customer_key"] [K: 1]
       [public.customer_dimension_b1] [Safe: Yes] [UptoDate: Yes] [Stats: RowCounts]

(1 row)
Refre

참조

1. https://docs.vertica.com/23.3.x/en/sql-reference/functions/management-functions/projection-functions/refresh/ 
2. https://docs.vertica.com/23.3.x/en/admin/projections/refreshing-projections/
3.