0
0
In continuation of the previous Snowflake function post, we will talk about some more commonly used System function in this post.
6. SYSTEM$STREAM_HAS_DATA: Indicates whether a specified stream contains change data capture (CDC) records. Therefore, if the task includes a WHEN clause with a SYSTEM$STREAM_HAS_DATA condition, verify that the specified stream contained change data capture (CDC) records when the task was last scheduled to run.
This function to be use in the WHEN expression in the definition of tasks. In other words, If the specified stream contains no change data, the task skips the current run. In addition, This check can help avoid starting or resuming a warehouse unnecessarily.
create or replace stream
ORDER_STREAM on table s_order;
create or replace task STREAM_TASK
warehouse = compute_wh
schedule = ‘5 minute’
when
system$stream_has_data(‘ORDER_STREAM’)
as
<< YOUR QUERY>>;
7. SYSTEM$CANCEL_QUERY: Cancels the specified query (or statement) if it is currently active/running.
SYSTEM$CANCEL_QUERY( <query_id> )
Consider the scenario where you have some long running queries in your Snowflake account because of unwanted cartesian join or wrong filter condition and you want to kill the query from the SQL without logging to the worksheet, However, You can explicitly cancel the query by specifying the Query Id in function.
CANCEL
8. SYSTEM$CANCEL_ALL_QUERIES: System function, Cancels all active/running queries in the specified session.
SYSTEM$CANCEL_ALL_QUERIES( <session_id> )
For instance, To obtain the ID for a session, log into the web interface as an account administrator (user with the ACCOUNTADMIN role) and go to:
Account -> Sessions
However, This function is not intended for canceling queries for a particular warehouse or user.
Firstly, To Cancel all the queries for particular warehouse:
ALTER WAREHOUSE [ IF EXISTS ] [ <name> ] ABORT ALL QUERIES
Finally, To Cancel all the queries for particular User:
ALTER USER [ IF EXISTS ] <name> ABORT ALL QUERIES
9. SYSTEM$ABORT_TRANSACTION: Aborts the specified transaction, if it is running. If the transaction has already been committed or roll back, then the state of the transaction is not altered.
In other words, Transactions can be aborted only by the user who started the transaction or an account administrator.
SYSTEM$ABORT_TRANSACTION(<transaction_id>)
Firstly, To find out the locked transaction:
show locks in account;
Finally, Fetch the Transaction id
select system$abort_transaction(<<Transaction id>>);
10. SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER: Enables Database Replication for a specified account in an organization. Call the SQL function once for each account in your organization for which you are enabling Database Replication.
SELECT SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER(‘<account_locator>’,
‘ENABLE_ACCOUNT_DATABASE_REPLICATION’, ‘true’);
In addition, Here we have created a target account where we need to replicate the Current Database:
CREATE ACCOUNT target_Accnt1
ADMIN_NAME = sachinreplicate
ADMIN_PASSWORD = ‘xxxxxx’
EMAIL = 'sachin.mittal04@gmail.com'
REGION = aws_us_west_2
EDITION = ENTERPRISE
output:
{“accountLocator”:”LXA64033″,”accountLocatorUrl”:”https://lxa64033.snowflakecomputing.com”,”accountName”:”TARGET_ACCNT1″,”url”: “https://eodlduq-target_accnt1.snowflakecomputing.com”,”edition”:”ENTERPRISE”,”regionGroup”:”PUBLIC”,”cloud”:”AWS”,”region”:”AWS_US_WEST_2″}
select system$global_account_set_parameter(‘LXA64033’,
‘ENABLE_ACCOUNT_DATABASE_REPLICATION’, ‘true’);
