ice_pick.account_object#

Module Contents#

Classes#

Account

(TODO - see how this is handled in snowflake)

AccountObject

Role

User

Warehouse

Database

Schema

Integration

NetworkPolicy

ResourceMonitor

class ice_pick.account_object.Account(session)#

(TODO - see how this is handled in snowflake) For now here just as a reminder and to help with global privileges Some things can only be accessed at the account level

class ice_pick.account_object.AccountObject(session, name: str, object_type: str)#
__repr__()#

Return repr(self).

get_description()#

Return the description of the object as a string

Supports: - DATABASE - SCHEMA - INTEGRATION - NETWORK POLICY - SHARE - USER - WAREHOUSE

get_ddl(fully_qualified: bool = True) → str#

Return the ddl of the account object as a string

Supports: - DATABASE - SCHEMA

mock_ddl() → str#

Return the ddl of objects that don’t work with the Snowflake GET_DDL() function

Supports: - WAREHOUSE - USER - ROLE

get_grants_on()#

Supports:

get_grant_objects() → list#
drop()#

drops object

Supports: most objects

un_drop()#

undrops a dropped object Supports - database - schema

create(replace: bool = False)#

Supports:

class ice_pick.account_object.Role(session, name: str)#

Bases: AccountObject

mock_ddl() → str#

Return the ddl of objects that don’t work with the Snowflake GET_DDL() function

Supports: - WAREHOUSE - USER - ROLE

show_grants_of()#
show_grants_to()#
show_future_grants()#
show_grants_recursive()#

If a role is granted to another role, this function will also look at the privileges of the granted roles

class ice_pick.account_object.User(session, name: str)#

Bases: AccountObject

mock_ddl() → str#

Return the ddl of objects that don’t work with the Snowflake GET_DDL() function

Supports: - WAREHOUSE - USER - ROLE

show_grants_to() → pandas.DataFrame#

return dataframe with grant information

get_roles() → list#

return granted roles as Role objects

get_all_privileges() → pandas.DataFrame#

return all privileges for a user

check_privilege(schema_object: ice_pick.schema_object.SchemaObject) → list#

maybe a quick way to show privilege on object?

class ice_pick.account_object.Warehouse(session, name: str)#

Bases: AccountObject

mock_ddl()#

Return the ddl of objects that don’t work with the Snowflake GET_DDL() function

Supports: - WAREHOUSE - USER - ROLE

suspend()#
resume()#
load_history(date_range_start: int, date_range_end: int, interval: str = 'hour') → pandas.DataFrame#

This function returns warehouse activity within the last 14 days. This funciton requires elevated privileges to run, either: | - The ACCOUNTADMIN role can get results from this function as it has all of the global account permissions. | - A role with the MONITOR USAGE global privilege on the ACCOUNT can query this function for any warehouses in the account. | - A role with the MONITOR privilege on the WAREHOUSE can query this function for the warehouse it has permissions on. | - A role with the OWNERSHIP privilege on the WAREHOUSE has all permissions on the warehouse including MONITOR. | (https://docs.snowflake.com/en/sql-reference/functions/warehouse_load_history)

Parameters:

  • date_range_start (int) – start of hours/days ago interval

  • date_range_end (int) – end of hours/days ago interval

  • interval (str) – set the date range to either “days” or “hours”

Returns:

A snowpark dataframe with the unioned input dataframes

Return type:

snowpark.DataFrame

Example

Get the load history for the warehouse for the last 12 hours

>> warehouse.load_history(12, 0, interval = “hour”)

Returns Pandas DataFrame With:
- START_TIME
- END_TIME
- WAREHOUSE_NAME
- AVG_RUNNING
- AVG_QUEUED_LOAD
- AVG_QUEUED_PROVISIONING
- AVG_BLOCKED
metering_history(date_range_start: int, date_range_end: int, interval: str = 'hour')#

This table function can be used in queries to return the hourly credit usage for a single warehouse (or all the warehouses in your account) within a specified date range. This funciton requires elevated privileges to run, either: | - The ACCOUNTADMIN role can get results from this function as it has all of the global account permissions. | - A role with the MONITOR USAGE global privilege on the ACCOUNT can query this function for any warehouses in the account. (https://docs.snowflake.com/en/sql-reference/functions/warehouse_metering_history)

Parameters:

  • date_range_start (int) – start of hours/days ago interval

  • date_range_end (int) – end of hours/days ago interval

  • interval (str) – set the date range to either “days” or “hours”

Returns:

A snowpark dataframe with the unioned input dataframes

Return type:

snowpark.DataFrame

Example

Get the load history for the warehouse for the last 12 hours

>> warehouse.metering_history(12, 0, interval = “hour”)

Returns Pandas DataFrame With:
- START_TIME
- END_TIME
- WAREHOUSE_NAME
- CREDITS_USED
- CREDITS_USED_COMPUTE
- CREDITS_USED_CLOUD_SERVICES
query_history(date_range_start: int, date_range_end: int, interval: str = 'hour', result_limit: int = 1000)#
resize_recommendation(auto_apply: bool = False) → str#

Keeping this simple as a starting point. In most cases rules should be customized for individual use cases. Rules: | - If: Local disk spillage over the last 3 days > 30% for the longest running queries | - Then: recommend size up warehouse | | - If: Local disk spillage over the last 3 days < 2% for the longest running queries | - Then: recommend downsizing warehouse | | - If: Max queued overload time > 10 minutes | - And: Local disk spillage over the last 3 days < 2% for the longest running queries | - Then: recommend scaling out | | - If: Max queued overload time > 10 minutes | - And: Local disk spillage over the last 3 days > 2% for the longest running queries | - Then: recommend scaling up

resize(wh_size: str)#

Resize the warehouse to specified size Warehouse sizes: - XSMALL , ‘X-SMALL’ - SMALL - MEDIUM - LARGE - XLARGE , ‘X-LARGE’ - XXLARGE , X2LARGE , ‘2X-LARGE’ - XXXLARGE , X3LARGE , ‘3X-LARGE’ - X4LARGE , ‘4X-LARGE’ - X5LARGE , ‘5X-LARGE’ - X6LARGE , ‘6X-LARGE’

Parameters:

wh_size (str) – specified warehouse size

Returns:

a string with execution status

Return type:

str

Example

Resize the warehouse to “SMALL” size

>> warehouse.resize(“SMALL”)
set_auto_suspend(seconds: int)#

Specifies the number of seconds of inactivity after which a warehouse is automatically suspended. | - Setting a value less than 60 is allowed, but may not result in the desired/expected behavior because the background process that suspends a warehouse runs approximately every 60 seconds and, therefore, is not intended for enabling exact control over warehouse suspension. | - Setting a 0 or NULL value means the warehouse never suspends

Parameters:

seconds (int) – the number of seconds of inactivity after which a warehouse is automatically suspended

Returns:

a string with execution status

Return type:

str

Example

Set the warehouse timeout to 60 seconds

>> warehouse.auto_suspend(60)
class ice_pick.account_object.Database(session, name: str)#

Bases: AccountObject

class ice_pick.account_object.Schema(session, name: str)#

Bases: AccountObject

class ice_pick.account_object.Integration(session, name: str)#

Bases: AccountObject

class ice_pick.account_object.NetworkPolicy(session, name: str)#

Bases: AccountObject

class ice_pick.account_object.ResourceMonitor(session, name: str)#

Bases: AccountObject