-
Notifications
You must be signed in to change notification settings - Fork 1
/
0029. Virtual Warehouses.sql
47 lines (39 loc) · 3.3 KB
/
0029. Virtual Warehouses.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- Virtual Warehouse creation
-- sizes
-- state properties
-- behaviour
use role sysadmin;
create warehouse data_analysis_warehouse
warehouse_size = 'SMALL'
auto_suspend = 600 -- in seconds
auto_resume = TRUE
initially_suspended = TRUE; -- by default warehouse gets started as soon as its created, we can turn off that using this paramater.
--set context
use warehouse data_analysis_warehouse;
use schema snowflake_sample_data.tpch_sf1000;
-- show state of virtyal warehouse
show warehouses;
SELECT
"name", "state", "type", "size", "min_cluster_count", "max_cluster_count", "running", "queued", "is_default", "auto_suspend", "auto_resume", "scaling_policy"
FROM TABLE(result_scan(last_query_id()));
-- +-------------------------+-----------+----------+---------+-------------------+-------------------+---------+--------+------------+--------------+-------------+----------------+
-- | name | state | type | size | min_cluster_count | max_cluster_count | running | queued | is_default | auto_suspend | auto_resume | scaling_policy |
-- |-------------------------+-----------+----------+---------+-------------------+-------------------+---------+--------+------------+--------------+-------------+----------------|
-- | COMPUTE_WH | SUSPENDED | STANDARD | X-Small | 1 | 1 | 0 | 0 | Y | 600 | true | STANDARD |
-- | DATA_ANALYSIS_WAREHOUSE | SUSPENDED | STANDARD | Small | 1 | 1 | 0 | 0 | N | 600 | true | STANDARD |
-- +-------------------------+-----------+----------+---------+-------------------+-------------------+---------+--------+------------+--------------+-------------+----------------+
alter warehouse data_analysis_warehouse resume;
-- manually suspend a warehouse
alter warehouse data_analysis_warehouse suspend;
-- alter the configurations on the fly
alter warehouse data_analysis_warehouse set warehouse_size = 'LARGE';
alter warehouse data_analysis_warehouse set auto_suspend = 300;
alter warehouse data_analysis_warehouse set auto_resume = FALSE;
-- +-------------------------+-----------+----------+---------+-------------------+-------------------+---------+--------+------------+--------------+-------------+----------------+
-- | name | state | type | size | min_cluster_count | max_cluster_count | running | queued | is_default | auto_suspend | auto_resume | scaling_policy |
-- |-------------------------+-----------+----------+---------+-------------------+-------------------+---------+--------+------------+--------------+-------------+----------------|
-- | COMPUTE_WH | SUSPENDED | STANDARD | X-Small | 1 | 1 | 0 | 0 | Y | 600 | true | STANDARD |
-- | DATA_ANALYSIS_WAREHOUSE | SUSPENDED | STANDARD | Large | 1 | 1 | 0 | 0 | N | 300 | false | STANDARD |
-- +-------------------------+-----------+----------+---------+-------------------+-------------------+---------+--------+------------+--------------+-------------+----------------+
-- Clear-down resources
DROP WAREHOUSE DATA_ANALYSIS_WAREHOUSE;