CODEWITHSUNDEEP
snowflake-cloud-data-platform
Username784792
Username784792

Reputation: 135

Materialized View how to set a refresh time with task?

I am trying to set a task to refresh a materialized view every hour. I have tried this:

  1. I ran and INSERT of new data to the original table. The Materialized View updated instantly
  2. Forcing the table to drop, the undrop the table that makes up the materialized view. It resulted in a full restoration at the specific time - though this would get expensive quickly

Drop table BookInventory;

Undrop table BookInventory;

I could not find anything in documentation on scheduling a creation of a materilaized view. Has anyone done this before?

Upvotes: 0

Views: 1884

Answers (2)

Erick Roesch
Erick Roesch

Reputation: 241

Another alternative is that you simply create your own "materialized view" via a custom procedure that you can schedule via a task.

the procedure creates a temp table like the current, including grants. Then inserts the data into this table from a view. Finally swap the tables and drop the temp table. Best to create this as a Transient table since there's no need for Time Travel.

CREATE OR REPLACE PROCEDURE utl.arch_create_mview_sp(P_TABLE_NM VARCHAR, P_VIEW_NM VARCHAR)
  RETURNS STRING
  LANGUAGE JAVASCRIPT
  EXECUTE AS CALLER
AS $$
  var result = "";
  var sqlCmd = "";
  var rs = "";
  var tmpTableNM = P_TABLE_NM + "_tmp";

  try {
    sqlCmd = "CREATE OR REPLACE TABLE " + tmpTableNM + " LIKE " + P_TABLE_NM + " COPY GRANTS";
    snowflake.execute( {sqlText: sqlCmd} );

    sqlCmd = "INSERT INTO " + tmpTableNM + " SELECT * FROM " + P_VIEW_NM;
    rs = snowflake.execute( {sqlText: sqlCmd} );
    rs.next();

    result = "rows inserted: " + rs.getColumnValue(1);

    sqlCmd = "ALTER TABLE " + P_TABLE_NM + " SWAP WITH " + tmpTableNM;
    snowflake.execute( {sqlText: sqlCmd} );

    sqlCmd = "DROP TABLE " + tmpTableNM;
    snowflake.execute( {sqlText: sqlCmd} );
  }
  catch (err) {
      result =  "Failed: Code: " + err.code + " | State: " + err.state;
      result += "\n  Message: " + err.message;
      result += "\nStack Trace:\n" + err.stackTraceTxt; 
    }
  }
  return result;
$$;

Upvotes: 1

Mike Gohl
Mike Gohl

Reputation: 737

You can suspend and resume materialized views. But you cannot query a suspended MV. What are you trying to accomplish? You are not going to save money, only defer the cost.

Upvotes: 0

Related Questions