Create PIPE does not use proper DATABASE

[marcinef]

I tested new feature in 0.10.0

• create snowflake_pipe

but it fails to use specified database.

The plan looks good:

  # snowflake_pipe.pipe will be created
  + resource "snowflake_pipe" "pipe" {
      + auto_ingest          = false
      + comment              = "Snowpipe for my amazing data"
      + copy_statement       = "COPY INTO SOME DATABASE.SCHEMA "
      + database             = "MY-DB"
      + id                   = (known after apply)
      + name                 = "snow-pipe-data"
      + notification_channel = (known after apply)
      + owner                = (known after apply)
      + schema               = "PUBLIC"
    }

However the apply fails to use specified DATABASE:

Error: error creating pipe snow-pipe-data: 090105 (22000): Cannot perform operation. This session does not have a current database. Call 'USE DATABASE', or use a qualified name.

  on pipe.tf line 1, in resource "snowflake_pipe" "pipe":
   1: resource "snowflake_pipe" "pipe" {

[sjauld]

It's likely that this is due to the hyphen in your database name. Hyphens are not currently escaped.

[ryanking]

@sjauld good catch. we are planning some work to overhaul the way we generate queries. I will make sure to add this to the list of things to fix

[msluyter]

Does the issue also occur with underscores? Getting same error; no hyphens, but with a name like 'FOO_BAR'.

[NiallRees]

Also running into this issue with a database name including an underscore.

[NiallRees]

@sjauld good catch. we are planning some work to overhaul the way we generate queries. I will make sure to add this to the list of things to fix

If you're able to point me to the section of code which generates the 'use db' query I can submit a PR

[harrydaniels-IW]

I have the same underscore issue, is there a workaround?

[NiallRees]

I have the same underscore issue, is there a workaround?

So far I've been creating the pipes manually and then importing them into the terraform state so that at least there's a record they exist. You can't modify them through terraform though without the same issue as above

[ryanking]

If you're able to point me to the section of code which generates the 'use db' query I can submit a PR

The identifiers are quoted, so I don't think this is the problem. Either way the code for generating the queries is here or can be found by starting there.

I would like to help fix this, but given that my team doesn't use this resource my context on how it works is somewhat limited.

What would really help is if someone could write an acceptance test for this resource. One that reproduces this bug would be 💯 .

[NiallRees]

The issue for me was that the copy statement did not include the database name. This led to the error of

error creating pipe DBT_RUN_RESULTS_TEST: 090105 (22000): Cannot perform operation. This session does not have a current database. Call 'USE DATABASE', or use a qualified name.

I overcame the problem by fully specifying all references with DB_NAME.SCHEMA_NAME.STAGE/TABLE

As the provider doesn't execute a USE DATABASE command, you must fully specify all names.

Solved I think @ryanking (my database name has underscores)

[ryanking]

Going to close this for now, if someone wants to write up or code a feature to make this better, go for it.

[asad3gh]

I still have issues creating snowpipe using Terraform
Snowflake provider version is 0.89
If I specified a fully qualified name with db.schema.table --> Getting this error "This session does not have a current database"

If I specify just the table name --> i get table not found

Please advise

[sfc-gh-jcieslak]

@asad3gh please create a new GitHub issue using general usage template if you're still having this issue.