page221
307
Grant Entitlement
Grant entitlement to role
tpc_owner_role
to manage tasks.
USE ROLE securityadmin;
GRANT CREATE TASK ON SCHEMA tpc.tpc_owner TO ROLE tpc_owner_role;
USE ROLE accountadmin;
GRANT EXECUTE TASK ON ACCOUNT TO ROLE tpc_owner_role;
Reset the role to
tpc_owner_role
.
USE ROLE IDENTIFIER ( $tpc_owner_role );
Create Tasks
With the stored procedure and stream integration proven to work correctly, you can now
automate data ingestion by creating five tasks, one for each table segment.
Alternative methods of scheduling are available:
•
Tasks are advantageous as they exist within the confines of
Snowflake. There are no external dependencies, but they incur
latency because of the scheduled trigger timer.
•
External scheduling tools are advantageous for orchestrating
sequential data load and stored procedure execution without
incurring time delays between processing steps.
For testing purposes only, the
SCHEDULE
is set to 1 minute. In your real-world
scenario, the
SCHEDULE
should be set to a more representative value according to
expected raw or staging data arrival time.
CREATE OR REPLACE TASK tsk_part_customer_order_asia
WAREHOUSE = tpc_wh_small
SCHEDULE = '1 minute'
WHEN system$stream_has_data ( 'strm_part_customer_order_asia' )
AS
CALL sp_load_customer( 'ASIA' );
Chapter 8
parallelization