@Marvin I'm setting up the prefect-snowflake integration but when executing using a connector, i get:

snowflake.connector.errors.ProgrammingError: 090105 (22000): Cannot perform CREATE TABLE. This session does not have a current database. Call 'USE DATABASE', or use a qualified name.

The error you're encountering with Snowflake is related to not setting the current database and schema in your session. This needs to be specified so that SQL commands like

CREATE TABLE

know where to operate. You should specify the database and schema settings when creating your Snowflake connection. Here are some relevant insights from the Prefect documentation, which might help you: 1. Prefect 2.x Documentation: There is an example using Snowflake where you need to correctly specify parameters like

schema

,

database

, and

warehouse

in the

SnowflakeConnector

. Ensure your connector is configured with these parameters to avoid such errors. Source 2. Prefect 3.x Documentation: You should have a properly configured

SnowflakeCredentials

block, and it is recommended to select the database, schema, and warehouse manually in the Snowflake UI before connecting. You can review the method for saving and connecting through these blocks for consistency. Source In summary, make sure your connector setup includes the database and schema configurations. If you haven't set these up yet, you might need to: - Set the database and schema directly in your Snowflake UI session or include them in your connection setup script. - Use Prefect's SnowflakeCredentials and SnowflakeConnector with accurately filled parameters. These steps should help you resolve the "Cannot perform CREATE TABLE" error. If you continue to face issues, consider reviewing your connection details or the integration setup using Prefect's documentation or reach out with more details.

@Marvin im using 2.x but i call from a block, is that the problem?

im using 2.x but i call from a block, is that the problem? Using a block in Prefect 2.x to manage Snowflake connections should not inherently cause the problem you're facing. However, it is essential to ensure that the Snowflake block is configured correctly with all necessary parameters such as

database

,

schema

, and

warehouse

. When using a block for database connections, especially with Prefect’s Snowflake integration, you need to ensure the following: 1. Configuration Check: Double-check that your Snowflake block has all the required configurations:

database

,

schema

, and

warehouse

. These settings tell Snowflake where to perform SQL operations like

CREATE TABLE

. 2. Block Execution: Verify that you are loading and using the block correctly in your script. When you instantiate the Snowflake connection using the block, make sure it includes the necessary context settings. Here's a quick example demonstrating how you should typically handle it:

python
from prefect import flow
from prefect_snowflake import SnowflakeCredentials, SnowflakeConnector

@flow
def example_snowflake_flow():
    # Load credentials block
    creds = SnowflakeCredentials.load("your-credentials-block-name")
    # Create a connector with necessary parameters
    connector = SnowflakeConnector(
        database="your_database_name",
        schema="your_schema_name",
        warehouse="your_warehouse_name",
        credentials=creds
    )
    with connector as conn:
        # Execute your SQL command
        conn.execute("CREATE TABLE your_table_name (id INT)")

By correctly configuring the block with required parameters and using it in the flow, you should be able to resolve the error. For more detailed information, check out the Prefect 2.x Snowflake Integration Guide to ensure your setup aligns with recommended practices. If your configurations seem correct and the problem persists, feel free to share more details so I can assist further! Using Prefect 2.x with a block for your Snowflake connection should work correctly as long as the block is properly configured. It’s essential to ensure your

SnowflakeConnector

block includes all necessary parameters such as

schema

,

database

, and

warehouse

. Here's a short guide on how to properly set up the `SnowflakeConnector`: 1. Specify All Required Parameters: Make sure your block setup includes the database and schema, as shown in the examples: - Schema: Your current schema. - Database: Your current database. - Warehouse: The warehouse you want to use. 2. Example from Prefect's Documentation:

snow_connector = SnowflakeConnector(
       schema="MY_SCHEMA",
       database="MY_DATABASE",
       warehouse="COMPUTE_WH",
       fetch_size=1,
       credentials=SnowflakeCredentials(
           role="MYROLE",
           user="MYUSERNAME",
           account="your-snowflake-account",
           password=json.loads(your_secret_block.read_secret()).get("your-password"),
       )
   )

3. Verify the Block's Settings: If you're loading this configuration from a block, confirm that the block contains these settings and that your database and schema are correctly specified. 4. Database/Schema Context: If the parameters are set correctly in your connector, this error should not occur. Ensure that the session context (database and schema) is correctly established or specified. If you've double-checked these configurations and continue to face issues, please review the setup script or UI configuration to ensure that the database and schema contexts are explicitly set in the Snowflake session. Feel free to reach out with more specifics if needed!

@Marvin if i already defined the database and schema in the connector block, why do i have to do it again? it should be able to pull that information.

You're correct in expecting that if the database and schema are already defined in your Snowflake connector block, you shouldn't need to specify them again. When implementing a Prefect SnowflakeConnector, it should use the configuration from the block as long as it's loaded correctly. Here are a few things to check and consider: 1. Block Loading: Ensure that when you load the Snowflake connector block, all configurations—including

database

and `schema`—are indeed being pulled correctly. Verify that you are using the block in a way that accesses these parameters. 2. Inspect the Block Configuration: Double-check the block’s setup to confirm that it includes the correct database and schema settings. You can do this by inspecting how the block was created initially. 3. Use Within Context: Make sure the block is being used correctly within a context manager. The connection settings should be active during the execution of your SQL commands. 4. Consistency Across Setup: Confirm that no part of your setup or code overrides the block’s configuration. Ensure you're not accidentally resetting or omitting settings elsewhere in your flow. Here's a quick example to illustrate:

python
from prefect import flow
from prefect_snowflake import SnowflakeCredentials, SnowflakeConnector

@flow
def example_snowflake_flow():
    # Assuming this block includes the database and schema
    connector = SnowflakeConnector.load("your-snowflake-connector-block-name")
    
    with connector as conn:
        conn.execute("CREATE TABLE your_table_name (id INT)")

example_snowflake_flow()

If these checks are in place and you still encounter issues, there might be a deeper integration problem or a bug. It could be worth reaching out to Prefect's community support or potentially filing an issue if this seems like a malfunction.

I think this can happen if the database you set for the connector is invalid for the session. For example, if the database doesn't exist, or if the current user/role combination doesn't have permissions to do access that database. In other words, if you execute

use database doesnt_exist

when you can't use that database, then execute

create table public.foo

, it won't try to create table

doesnt_exist.public.foo