Performance Concepts: Virtual Warehouses Flashcards
Virtual Warehouse
A virtual warehouse is a named abstraction for a Massively Parallel Processing (MPP) compute cluster.
Virtual Warehouses execute what operations?
- DQL operations (SELECT)
- DML operations (UPDATE)
- Data Loading Operations (COPY INTO)
As a user you only interact with the _________ warehouse object not the underlying compute resources.
named
Spin up and shut-down a virtually __ number of warehouses without resource contention.
unlimited
Virtual Warehouse configuration can be changed ___________.
on-the-fly
Virtual Warehouses contain local ____ storage used to sore ______ data retrieved from the ________ layer.
SSD; raw; storage
Virtual Warehouses are created via the _____________ or through _______ commands.
Snowflake UI; SQL
DROP WAREHOUSE statement
DROP WAREHOUSE MY_WAREHOUSE;
CREATE WAREHOUSE statement
CREATE WAREHOUSE MY_MED_WH
WAREHOUSE_SIZE=’MEDIUM’;
ALTER WAREHOUSE SUSPEND statement
ALTER WAREHOUSE MY_WH SUSPEND;
ALTER WAREHOUSE resize statement
ALTER WAREHOUSE MY_WH_2 SET
WAREHOUSE_SIZE=MEDIUM;
CREATE HOUSE with minimum and maximum cluster statement
CREATE WAREHOUSE MY_WH_3
MIN_CLUSTER_COUNT=1
MAX_CLUSTER_COUNT=3
SCALING_POLICY=STANDARD;
Virtual Warehouse States
- Started
- Suspended
- Resizing
By default when a Virtual Warehouse is created it is in the ________________ state.
STARTED
Suspending a Virtual Warehouse puts it in the ________________ state, removing the compute nodes from a warehouse.
SUSPENDED
Resuming a Virtual Warehouse puts in back into the STARTED state and can execute queries.
STARTED
AUTO SUSPEND definition
Specifies the number of seconds of inactivity after which a warehouse is automatically suspended.
AUTO SUSPEND statement
CREATE WAREHOUSE MY_MED_WH
AUTO_SUSPEND=300;
AUTO RESUME definition
Specifies whether to automatically resume a warehouse when a SQL statement is submitted to it.