snowflake.core.table.TableCollection

class snowflake.core.table.TableCollection(schema: SchemaResource)

Bases: SchemaObjectCollectionParent[TableResource]

Represents the collection operations on the Snowflake Table resource.

With this collection, you can create, iterate through, and search for tables that you have access to in the current context.

Examples

Creating a table instance:

>>> tables = root.databases["my_db"].schemas["my_schema"].tables
>>> new_table = Table(
...     name="accounts",
...     columns=[
...         TableColumn(
...             name="id",
...             datatype="int",
...             nullable=False,
...             autoincrement=True,
...             autoincrement_start=0,
...             autoincrement_increment=1,
...         ),
...         TableColumn(name="created_on", datatype="timestamp_tz", nullable=False),
...         TableColumn(name="email", datatype="string", nullable=False),
...         TableColumn(name="password_hash", datatype="string", nullable=False),
...     ],
... )
>>> tables.create(new_tables)
Copy

Attributes

database
root

Methods

create(table: Table | str, *, as_select: str | None = None, template: str | None = None, like_table: str | None = None, clone_table: str | Clone | None = None, copy_grants: bool | None = False, mode: CreateMode = CreateMode.error_if_exists) → TableResource

Create a table in Snowflake.

Parameters:

  • table (Table) – The Table object, together with the Table’s properties, object parameters, columns, and constraints. It can either be a table name or a Table object when it’s used together with as_select, template, like_table, clone_table. It must be a Table when it’s not used with these clauses. Table has the following properties: name; kind, cluster_by, enable_schema_evolution, change_tracking, data_retention_time_in_days, max_data_extension_time_in_days, default_ddl_collation, columns, constraints, comment, database_name, schema_name are optional.

  • as_select (str, optional) – Creates a table from a select statement.

  • template (str, optional) – Create a table using the templates specified in staged files.

  • like_table (str, optional) – Create a new table like the specified one, but empty.

  • clone_table (str or Clone, optional) – Create a new table by cloning the specified table.

  • copy_grants (bool, optional) – Copy grants when clone_table is provided.

  • mode (CreateMode, optional) –

    One of the following strings.

    CreateMode.error_if_exists: Throw an snowflake.core.exceptions.ConflictError if the table already exists in Snowflake. Equivalent to SQL create table <name> ....

    CreateMode.or_replace: Replace if the task already exists in Snowflake. Equivalent to SQL create or replace table <name> ....

    CreateMode.if_not_exists: Do nothing if the task already exists in Snowflake. Equivalent to SQL create table <name> if not exists...

    Default value is CreateMode.error_if_exists.

Examples

Creating a table instance:

>>> tables = root.databases["my_db"].schemas["my_schema"].tables
>>> new_table = Table(
...     name="events",
...     columns=[
...         TableColumn(
...             name="id",
...             datatype="int",
...             nullable=False,
...             autoincrement=True,
...             autoincrement_start=0,
...             autoincrement_increment=1,
...         ),
...         TableColumn(name="category", datatype="string"),
...         TableColumn(name="event", datatype="string"),
...     ],
...     comment="store events/logs in here",
... )
>>> tables.create(new_tables)
Copy

Cloning a Table instance:

>>> tables = root.databases["my_db"].schemas["my_schema"].tables
>>> tables.create("new_table", clone_table="original_table_name")
Copy

Cloning a Table instance in a different database and schema:

>>> tables = root.databases["my_db"].schemas["my_schema"].tables
>>> tables.create("new_table", clone_table="database_name.schema_name.original_table_name")
Copy

Notes

Not currently implemented:

  • Row access policy

  • Column masking policy

  • Search optimization

  • Tags

  • Stage file format and copy options

items() → ItemsView[str, T]
iter(*, like: str | None = None, starts_with: str | None = None, limit: int | None = None, from_name: str | None = None, history: bool = False, deep: bool = False) → Iterator[Table]

Iterate through Table objects from Snowflake, filtering on any optional ‘like’ pattern.

Parameters:

  • like (str, optional) – A case-insensitive string functioning as a filter, with support for SQL wildcard characters (% and _).

  • starts_with (str, optional) – String used to filter the command output based on the string of characters that appear at the beginning of the object name. Uses case-sensitive pattern matching.

  • limit (int, optional) – Limit of the maximum number of rows returned by iter(). The default is None, which behaves equivalently to show_limit=10000. This value must be between 1 and 10000.

  • from_name (str, optional) – Fetch rows only following the first row whose object name matches the specified string. This is case-sensitive and does not have to be the full name.

  • history (bool, optional) – Include dropped tables that have not yet been purged yet.

  • deep (bool, optional) – Fetch the sub-resources columns and constraints of every table if it’s True. Default False.

Examples

Showing all tables that you have access to see in a schema:

>>> tables = my_schema.tables.iter()
Copy

Showing information of the exact table you want to see:

>>> tables = my_schema.tables.iter(like="my-table-name")
Copy

Showing tables starting with ‘my-table-name-‘:

>>> tables = my_schema.tables.iter(like="my-table-name-%")
Copy

Using a for loop to retrieve information from iterator:

>>> for table in table:
>>>     print(table.name, table.kind)
Copy
keys() → KeysView[str]
values() → ValuesView[T]
Language: English