page218
304
Streams can go stale where the data is not consumed within the retention period
and ensure each stream is cleared out before reuse. You can find more information on
streams going stale at
https://community.snowflake.com/s/article/The-query-
that-reads-or-consumes-the-stream-is-failing
. When a stream goes stale, re-
creating the stream will solve the issue though the contained data may be lost.
In this section I have called out how our specific implementation uses streams; you
can learn more about steams at
https://docs.snowflake.com/en/sql-reference/sql/
create-stream
.
Creating Stored Procedures
Let’s create a stored procedure passing
region_name
as a parameter that
must
match the
segment suffix you are processing.
CREATE OR REPLACE PROCEDURE sp_merge_test_load ( P_REGION STRING )
RETURNS string
LANGUAGE javascript
EXECUTE AS CALLER
AS
$$
var sql_stmt = "";
var err_state = "";
var result = "";
sql_stmt = "MERGE INTO part_customer_order_" + P_REGION + " c\n"
sql_stmt += "USING strm_part_customer_order_" + P_REGION + " s\n"
sql_stmt += "ON c.order_key = s.order_key\n"
sql_stmt += "AND s.region_name = '" + P_REGION + "'\n"
sql_stmt += "WHEN MATCHED AND s.operation = 'D' THEN DELETE\n"
sql_stmt += "WHEN MATCHED AND s.operation = 'U' THEN\n"
sql_stmt += " UPDATE SET customer_key = s.customer_key,\n"
sql_stmt += " customer_name = s.customer_name,\n"
sql_stmt += " order_date = s.order_date,\n"
sql_stmt += " nation_name = s.nation_name,\n"
sql_stmt += " region_name = s.region_name\n"
sql_stmt += "WHEN NOT MATCHED\n"
Chapter 8
parallelization