Tutorial 1: Create a database, schema, table, and warehouse

Introduction

After completing the common setup, you are ready to start using the Snowflake Python API. In this tutorial, you create and manage a Snowflake database, schema, table, and virtual warehouse.

Create a database, schema, and table

You can use your root object to create a database, schema, and table in your Snowflake account.

  1. To create a database, run the following code in the next cell in your notebook:

    database = root.databases.create(
      Database(
        name="PYTHON_API_DB"),
        mode=CreateMode.or_replace
      )
    
    Copy

    This code, which is functionally equivalent to the SQL command CREATE OR REPLACE DATABASE PYTHON_API_DB, creates a database in your account named PYTHON_API_DB. This code follows a common pattern for managing objects in Snowflake, described in detail as follows:

    • root.databases.create() creates a database in Snowflake. It accepts two arguments: a Database object and a mode.

    • You pass a Database object by using Database(name="PYTHON_API_DB"), and set the name of the database by using the name argument. Recall that you imported Database on line 3 of the notebook.

    • You specify the creation mode by passing the mode argument. In this case, you set it to CreateMode.or_replace, but the following are other valid values:

      • CreateMode.if_not_exists: Functionally equivalent to CREATE IF NOT EXISTS in SQL.

      • CreateMode.error_if_exists: Raises an exception if the object already exists in Snowflake. This is the default value if a a mode is not specified.

    • You manage the database programmatically by storing a reference to the database in an object you created named database.

  2. In Snowsight, navigate to the databases section of your Snowflake account. If your code was successful, you should see the PYTHON_API_DB database.

    Tip

    If you use VS Code, install the Snowflake extension to explore all Snowflake objects within your editor.

  3. To create a schema in the PYTHON_API_DB database, run the following code in your next cell:

    schema = database.schemas.create(
      Schema(
        name="PYTHON_API_SCHEMA"),
        mode=CreateMode.or_replace,
      )
    
    Copy

    Note that you call .schemas.create() on the database object you created previously.

  4. To create a table in the schema you just created, run the following code in your next cell:

    table = schema.tables.create(
      Table(
        name="PYTHON_API_TABLE",
        columns=[
          TableColumn(
            name="TEMPERATURE",
            datatype="int",
            nullable=False,
          ),
          TableColumn(
            name="LOCATION",
            datatype="string",
          ),
        ],
      ),
    mode=CreateMode.or_replace
    )
    
    Copy

    This code creates a table in the PYTHON_API_SCHEMA schema, with two columns and their data types specified: TEMPERATURE as int and LOCATION as string.

    These last two code examples should look and feel familiar since they follow the pattern you saw in the first step where you created the PYTHON_API_DB database.

  5. After running the code in the last two cells, navigate back to your Snowflake account in Snowsight and confirm that the objects were created.

Retrieve object data

You can retrieve metadata about an object in Snowflake.

  1. To retrieve details about the PYTHON_API_TABLE table you created previously, run the following code in your next notebook cell:

    table_details = table.fetch()
    
    Copy

    fetch() returns a TableModel object. You can then call .to_dict() on the resulting object to view information about the object.

  2. To print the table details, run the following code in your next cell:

    table_details.to_dict()
    
    Copy

    In the notebook, you should see a dictionary printed that contains metadata about the PYTHON_API_TABLE table, similar to the following:

    {
        "name": "PYTHON_API_TABLE",
        "kind": "TABLE",
        "enable_schema_evolution": False,
        "change_tracking": False,
        "data_retention_time_in_days": 1,
        "max_data_extension_time_in_days": 14,
        "default_ddl_collation": "",
        "columns": [
            {"name": "TEMPERATURE", "datatype": "NUMBER(38,0)", "nullable": False},
            {"name": "LOCATION", "datatype": "VARCHAR(16777216)", "nullable": True},
        ],
        "created_on": datetime.datetime(
            2024, 5, 9, 8, 59, 15, 832000, tzinfo=datetime.timezone.utc
        ),
        "database_name": "PYTHON_API_DB",
        "schema_name": "PYTHON_API_SCHEMA",
        "rows": 0,
        "bytes": 0,
        "owner": "ACCOUNTADMIN",
        "automatic_clustering": False,
        "search_optimization": False,
        "owner_role_type": "ROLE",
    }
    
    Copy

    As shown, this dictionary contains information about the PYTHON_API_TABLE table that you created previously, with detailed information about columns, owner, database, schema, and more.

Object metadata is useful when building business logic in your application. For example, you might build logic that runs depending on certain information about an object. You can use fetch() to retrieve object metadata in such scenarios.

Programmatically update a table

You can programmatically add a column to a table. The PYTHON_API_TABLE table currently has two columns: TEMPERATURE and LOCATION. Consider a scenario where you want to add a new column named ELEVATION of type int and set it as the table’s primary key.

  1. Run the following code in your next cell:

    table_details.columns.append(
        TableColumn(
          name="elevation",
          datatype="int",
          nullable=False,
          constraints=[PrimaryKey()],
        )
    )
    
    Copy

    Note

    This code does not create the column. Instead, this column definition is appended to the array that represents the table’s columns in the TableModel. To view this array, review the value of columns as described in the previous step.

  2. To modify the table and add the column, run the following code in your next cell:

    table.create_or_update(table_details)
    
    Copy

    In this line, you call create_or_update() on the object representing PYTHON_API_TABLE and pass the updated value of table_details. This line adds the ELEVATION column to PYTHON_API_TABLE.

  3. To confirm the column was added, run the following code in your next cell:

    table.fetch().to_dict()
    
    Copy

    The output should look similar to the following:

    {
        "name": "PYTHON_API_TABLE",
        "kind": "TABLE",
        "enable_schema_evolution": False,
        "change_tracking": False,
        "data_retention_time_in_days": 1,
        "max_data_extension_time_in_days": 14,
        "default_ddl_collation": "",
        "columns": [
            {"name": "TEMPERATURE", "datatype": "NUMBER(38,0)", "nullable": False},
            {"name": "LOCATION", "datatype": "VARCHAR(16777216)", "nullable": True},
            {"name": "ELEVATION", "datatype": "NUMBER(38,0)", "nullable": False},
        ],
        "created_on": datetime.datetime(
            2024, 5, 9, 8, 59, 15, 832000, tzinfo=datetime.timezone.utc
        ),
        "database_name": "PYTHON_API_DB",
        "schema_name": "PYTHON_API_SCHEMA",
        "rows": 0,
        "bytes": 0,
        "owner": "ACCOUNTADMIN",
        "automatic_clustering": False,
        "search_optimization": False,
        "owner_role_type": "ROLE",
        "constraints": [
            {"name": "ELEVATION", "column_names": ["ELEVATION"], "constraint_type": "PRIMARY KEY"}
        ]
    }
    
    Copy

    Review the value of columns and the value of constraints, both of which now include the ELEVATION column.

    To visually confirm the new column, navigate to your Snowflake account in Snowsight and inspect the table.

Create, suspend, and delete a warehouse

You can also manage virtual warehouses with the Snowflake Python API. For example, you might need to create another warehouse temporarily to run certain queries. In this scenario, you can use the API to create, suspend, and delete a warehouse.

  1. To retrieve the collection of warehouses associated with your session, run the following code in your next cell:

    warehouses = root.warehouses
    
    Copy

    You manage warehouses in your session using the resulting warehouses object.

  2. To define and create a new warehouse, run the following code in your next cell:

    python_api_wh = Warehouse(
        name="PYTHON_API_WH",
        warehouse_size="SMALL",
        auto_suspend=500,
    )
    
    warehouse = warehouses.create(python_api_wh,mode=CreateMode.or_replace)
    
    Copy

    In this code, you define a new warehouse by instantiating Warehouse and specifying the warehouse’s name, size, and auto-suspend policy. The auto-suspend timeout is in units of seconds. In this case, the warehouse will suspend after 8.33 minutes of inactivity.

    You then create the warehouse by calling create() on your warehouse collection. You store the reference in the resulting warehouse object. Navigate to your Snowflake account in Snowsight and confirm that the warehouse was created.

  3. To retrieve information about the warehouse, run the following code in your next cell:

    warehouse_details = warehouse.fetch()
    warehouse_details.to_dict()
    
    Copy

    This code should look familiar, as it follows the same pattern you used to fetch table metadata in a previous step. The output should be similar to the following:

    {
      'name': 'PYTHON_API_WH',
      'auto_suspend': 500,
      'auto_resume': 'true',
      'resource_monitor': 'null',
      'comment': '',
      'max_concurrency_level': 8,
      'statement_queued_timeout_in_seconds': 0,
      'statement_timeout_in_seconds': 172800,
      'tags': {},
      'warehouse_type': 'STANDARD',
      'warehouse_size': 'Small'
    }
    
    Copy
  4. If you have multiple warehouses in your session, you can use the API to iterate through them or to search for a specific warehouse.

    Run the following code in your next cell:

    warehouse_list = warehouses.iter(like="PYTHON_API_WH")
    result = next(warehouse_list)
    result.to_dict()
    
    Copy

    In this code, you call iter() on the warehouse collection and pass the like argument, which you use to return any warehouses that have a name matching the specified string. In this case, you pass the name of the warehouse you defined previously, but this argument is generally a case-insensitive string that functions as a filter, with support for SQL wildcard characters like % and _.

    After running the cell, you should see output similar to the following, which shows that you successfully returned a matching warehouse:

    {
      'name': 'PYTHON_API_WH',
      'auto_suspend': 500,
      'auto_resume': 'true',
      'resource_monitor': 'null',
      'comment': '',
      'max_concurrency_level': 8,
      'statement_queued_timeout_in_seconds': 0,
      'statement_timeout_in_seconds': 172800,
      'tags': {},
      'warehouse_type': 'STANDARD',
      'warehouse_size': 'Small'
    }
    
    Copy
  5. To programmatically modify the warehouse by changing its size to LARGE, run the following code in your next cell:

    warehouse = root.warehouses.create(Warehouse(
        name="PYTHON_API_WH",
        warehouse_size="LARGE",
        auto_suspend=500,
    ), mode=CreateMode.or_replace)
    
    Copy
  6. To confirm that the warehouse size was updated to LARGE, run the following code in your next cell:

    warehouse.fetch().size
    
    Copy

    Or, you can navigate to your Snowflake account in Snowsight and confirm the change in warehouse size.

  7. (Optional) To delete the warehouse, run the following code in your next cell. This is optional in case you want to continue using the warehouse in subsequent steps.

    warehouse.delete()
    
    Copy

    Navigate once again to your Snowflake account in Snowsight and confirm the deletion of the warehouse.

What’s next?

Congratulations! In this tutorial, you learned the fundamentals for managing Snowflake resource objects using the Snowflake Python API.

Summary

Along the way, you completed the following steps:

  • Install the Snowflake Python API.

  • Set up a connection to Snowflake.

  • Create a database, schema, and table.

  • Retrieve object information.

  • Programmatically update an object.

  • Create, suspend, and delete a warehouse.

Additional resources

For more examples of using the Snowflake Python API to manage other types of objects in Snowflake, see the following developer guides:

Guide

Description

Managing Snowflake users, roles, and grants with Python

Use the API to create and manage users, roles, and grants.

Managing Snowflake stages with Python

Use the API to create and manage stages.

Managing Snowflake tasks and task graphs with Python

Use the API to create, execute, and manage tasks and task graphs.

Managing Snowpark Container Services (including service functions) with Python

Use the API to manage components of Snowpark Container Services, including compute pools, image repositories, services, and service functions.