search

airbytehq / airbyte

The leading data integration platform for ETL / ELT data pipelines from APIs, databases & files to data warehouses, data lakes & data lakehouses. Both self-hosted and Cloud-hosted.
https://airbyte.com
Other
15.72k stars 4.03k forks source link

[destination-snowflake] Confusing Error - `No active warehouse selected in the current session...` This should be a config error and mention permissions #40615

Open hongbo-miao opened 3 months ago

hongbo-miao commented 3 months ago

Connector Name

destination-snowflake

Connector Version

What step the error happened?

Configuring a new connector

Relevant information

I found this bug starts to show since destination-snowflake v3.9.0, v3.10.1 (latest as of today) also has this issue. However, v3.8.4 works well.

Here is my Terraform config

resource "airbyte_destination_snowflake" "hm_airbyte_destination_snowflake" {
  name         = "my-connector"
  workspace_id = "b076756a-703a-47d2-9cf7-e0b6fb651233"
  configuration = {
    host      = "hongbomiao.snowflakecomputing.com"
    warehouse = "PRODUCTION_HM_AIRBYTE_WH"
    database  = "PRODUCTION_HM_AIRBYTE_DB"
    schema    = "DATA_SCIENCE_PRIME_RADIANT_DATABASE_PRT_SCHEMA"
    role      = "HM_PRODUCTION_HM_AIRBYTE_DB_OWNER_ROLE"
    username  = "HM_PRODUCTION_HM_AIRBYTE_DB_OWNER_USER"
    credentials = {
      key_pair_authentication = {
        private_key          = var.snowflake_user_private_key
        private_key_password = var.snowflake_user_private_key_passphrase
      }
    }
    retention_period_days = 90
    raw_data_schema       = "_AIRBYTE_INTERNAL"
    disable_type_dedupe   = false
  }
}

Relevant log output

v3.8.4

Here is the screenshot when test the destination:

image

v3.9.0 to v3.10.1 (latest as of today)

Here is the screenshot when test the destination:

image

airbyte-worker log:

2024-06-28 09:39:14 platform > WARN main c.z.h.p.ProxyConnection(checkException):178 HikariPool-1 - Connection net.snowflake.client.jdbc.SnowflakeConnectionV1@79eeff87 marked as broken because of SQLSTATE(57P03), ErrorCode(606) net.snowflake.client.jdbc.SnowflakeSQLException: No active warehouse selected in the current session.  Select an active warehouse with the 'use warehouse' command.

    at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:144) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
    at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:77) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
    at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:501) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
    at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:407) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
    at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:482) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
    at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:199) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
    at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:133) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
    at net.snowflake.client.core.SFStatement.execute(SFStatement.java:769) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
    at net.snowflake.client.core.SFStatement.execute(SFStatement.java:677) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
    at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQueryInternal(SnowflakeStatementV1.java:267) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
    at net.snowflake.client.jdbc.SnowflakePreparedStatementV1.executeQuery(SnowflakePreparedStatementV1.java:122) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-5.1.0.jar:?]
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) [HikariCP-5.1.0.jar:?]
    at io.airbyte.cdk.db.jdbc.DefaultJdbcDatabase.unsafeQuery(DefaultJdbcDatabase.kt:120) [airbyte-cdk-core-0.37.1.jar:?]
    at io.airbyte.cdk.db.jdbc.JdbcDatabase.unsafeQuery(JdbcDatabase.kt:176) [airbyte-cdk-core-0.37.1.jar:?]
    at io.airbyte.cdk.db.jdbc.JdbcDatabase.queryJsons(JdbcDatabase.kt:196) [airbyte-cdk-core-0.37.1.jar:?]
    at io.airbyte.integrations.destination.snowflake.operation.SnowflakeStagingClient.copyIntoTableFromStage(SnowflakeStagingClient.kt:180) [io.airbyte.airbyte-integrations.connectors-destination-snowflake.jar:?]
    at io.airbyte.integrations.destination.snowflake.operation.SnowflakeStorageOperation.writeToStage(SnowflakeStorageOperation.kt:69) [io.airbyte.airbyte-integrations.connectors-destination-snowflake.jar:?]
    at io.airbyte.integrations.destination.snowflake.operation.SnowflakeStorageOperation.writeToStage(SnowflakeStorageOperation.kt:27) [io.airbyte.airbyte-integrations.connectors-destination-snowflake.jar:?]
    at io.airbyte.cdk.integrations.destination.staging.operation.StagingStreamOperations.writeRecords(StagingStreamOperations.kt:54) [airbyte-cdk-s3-destinations-0.37.1.jar:?]
    at io.airbyte.integrations.destination.snowflake.SnowflakeDestination.check(SnowflakeDestination.kt:155) [io.airbyte.airbyte-integrations.connectors-destination-snowflake.jar:?]
    at io.airbyte.cdk.integrations.base.IntegrationRunner.runInternal(IntegrationRunner.kt:160) [airbyte-cdk-core-0.37.1.jar:?]
    at io.airbyte.cdk.integrations.base.IntegrationRunner.run(IntegrationRunner.kt:116) [airbyte-cdk-core-0.37.1.jar:?]
    at io.airbyte.cdk.integrations.base.adaptive.AdaptiveDestinationRunner$Runner.run(AdaptiveDestinationRunner.kt:68) [airbyte-cdk-core-0.37.1.jar:?]
    at io.airbyte.integrations.destination.snowflake.SnowflakeDestinationKt.main(SnowflakeDestination.kt:345) [io.airbyte.airbyte-integrations.connectors-destination-snowflake.jar:?]

Contribute

hongbo-miao commented 3 months ago

I found even "test the destination" succeed, but when actually run the job, I still got error no matter which Snowflake destination connector version:

2024-07-01 05:37:10 destination > WARN type-and-dedupe c.z.h.p.ProxyConnection(checkException):178 HikariPool-1 - Connection net.snowflake.client.jdbc.SnowflakeConnectionV1@d66a18f marked as broken because of SQLSTATE(57P03), ErrorCode(606) net.snowflake.client.jdbc.SnowflakeSQLException: No active warehouse selected in the current session.  Select an active warehouse with the 'use warehouse' command.
      at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:144) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
      at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:77) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
      at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:501) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
      at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:407) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
      at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:482) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
      at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:199) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
      at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:133) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
      at net.snowflake.client.core.SFStatement.execute(SFStatement.java:769) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
      at net.snowflake.client.core.SFStatement.execute(SFStatement.java:677) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
      at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQueryInternal(SnowflakeStatementV1.java:267) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
      at net.snowflake.client.jdbc.SnowflakePreparedStatementV1.executeQuery(SnowflakePreparedStatementV1.java:122) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
      at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-5.1.0.jar:?]
      at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-5.1.0.jar:?]
      at io.airbyte.cdk.db.jdbc.DefaultJdbcDatabase.unsafeQuery(DefaultJdbcDatabase.java:129) ~[airbyte-cdk-core-0.23.2.jar:?]
      at io.airbyte.cdk.db.jdbc.JdbcDatabase.unsafeQuery(JdbcDatabase.java:200) ~[airbyte-cdk-core-0.23.2.jar:?]
      at io.airbyte.cdk.db.jdbc.JdbcDatabase.queryJsons(JdbcDatabase.java:216) ~[airbyte-cdk-core-0.23.2.jar:?]
      at io.airbyte.integrations.destination.snowflake.typing_deduping.SnowflakeV1V2Migrator.doesAirbyteInternalNamespaceExist(SnowflakeV1V2Migrator.java:42) ~[io.airbyte.airbyte-integrations.connectors-destination-snowflake.jar:?]
      at io.airbyte.integrations.base.destination.typing_deduping.BaseDestinationV1V2Migrator.doesValidV2RawTableAlreadyExist(BaseDestinationV1V2Migrator.java:114) ~[airbyte-cdk-typing-deduping-0.23.2.jar:?]
      at io.airbyte.integrations.base.destination.typing_deduping.BaseDestinationV1V2Migrator.shouldMigrate(BaseDestinationV1V2Migrator.java:47) ~[airbyte-cdk-typing-deduping-0.23.2.jar:?]
      at io.airbyte.integrations.base.destination.typing_deduping.BaseDestinationV1V2Migrator.migrateIfNecessary(BaseDestinationV1V2Migrator.java:27) ~[airbyte-cdk-typing-deduping-0.23.2.jar:?]
      at io.airbyte.integrations.base.destination.typing_deduping.DefaultTyperDeduper.lambda$runMigrationsAsync$0(DefaultTyperDeduper.java:138) ~[airbyte-cdk-typing-deduping-0.23.2.jar:?]
      at java.base/java.util.concurrent.CompletableFuture$AsyncRun.run(CompletableFuture.java:1804) ~[?:?]
      at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) ~[?:?]
      at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) ~[?:?]
      at java.base/java.lang.Thread.run(Thread.java:1583) [?:?]
Stack Trace: net.snowflake.client.jdbc.SnowflakeSQLException: No active warehouse selected in the current session.  Select an active warehouse with the 'use warehouse' command.
      at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:144)
      at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:77)
      at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:501)
      at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:407)
      at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:482)
      at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:199)
      at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:133)
      at net.snowflake.client.core.SFStatement.execute(SFStatement.java:769)
      at net.snowflake.client.core.SFStatement.execute(SFStatement.java:677)
      at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQueryInternal(SnowflakeStatementV1.java:267)
      at net.snowflake.client.jdbc.SnowflakePreparedStatementV1.executeQuery(SnowflakePreparedStatementV1.java:122)
      at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
      at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
      at io.airbyte.cdk.db.jdbc.DefaultJdbcDatabase.unsafeQuery(DefaultJdbcDatabase.java:129)
      at io.airbyte.cdk.db.jdbc.JdbcDatabase.unsafeQuery(JdbcDatabase.java:200)
      at io.airbyte.cdk.db.jdbc.JdbcDatabase.queryJsons(JdbcDatabase.java:216)
      at io.airbyte.integrations.destination.snowflake.typing_deduping.SnowflakeV1V2Migrator.doesAirbyteInternalNamespaceExist(SnowflakeV1V2Migrator.java:42)
      at io.airbyte.integrations.base.destination.typing_deduping.BaseDestinationV1V2Migrator.doesValidV2RawTableAlreadyExist(BaseDestinationV1V2Migrator.java:114)
      at io.airbyte.integrations.base.destination.typing_deduping.BaseDestinationV1V2Migrator.shouldMigrate(BaseDestinationV1V2Migrator.java:47)
      at io.airbyte.integrations.base.destination.typing_deduping.BaseDestinationV1V2Migrator.migrateIfNecessary(BaseDestinationV1V2Migrator.java:27)
      at io.airbyte.integrations.base.destination.typing_deduping.DefaultTyperDeduper.lambda$runMigrationsAsync$0(DefaultTyperDeduper.java:138)
      at java.base/java.util.concurrent.CompletableFuture$AsyncRun.run(CompletableFuture.java:1804)
      at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
      at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
      at java.base/java.lang.Thread.run(Thread.java:1583)
hongbo-miao commented 3 months ago

It turns out a false alarm. I think all verisons including latest v3.10.1 all work well!

I actually made several mistakes.

Here are tips to make it work, let's based on this Snowflake destination settings:

image

  1. (I made a mistake here) Make sure you granted the Snowflake role you use in Airbyte of the warehouse permission, in my case, meaning PRODUCTION_HM_AIRBYTE_DB_OWNER_ROLE should have permission of PRODUCTION_HM_AIRBYTE_WH.
  2. (I made a mistake here) Make sure your warehouse in settings has no typo, in my case it should be PRODUCTION_HM_AIRBYTE_WH.
  3. (I think this one is optional, but I always set) Snowflake user has default warehouse and default role set. In my case PRODUCTION_HM_AIRBYTE_DB_OWNER_USER default warehouse is PRODUCTION_HM_AIRBYTE_WH and default role is PRODUCTION_HM_AIRBYTE_DB_OWNER_ROLE.

Hope it helps future users! ☺️

I will leave this open as I feel the error is kind of misleading, maybe worth to throw different errors such as

marcosmarxm commented 2 months ago

@airbytehq/destinations can someone take a look into this?

HishamAssi commented 2 months ago

Can I take a jab at this as a first time contributor?

I see the only time that string is used is at:

AssertionsForClassTypes.assertThat(standardCheckConnectionOutput.message)
            .contains(
                SnowflakeInsertDestinationAcceptanceTest.Companion.NO_ACTIVE_WAREHOUSE_ERR_MSG
            )

It seems like a pretty generic catch all error message so I can just edit the ERROR Message (NO_ACTIVE_WAREHOUSE_ERR_MSG) to allude to a permission issue as well.

marcosmarxm commented 2 months ago

@airbytehq/destinations would be someone able to check the suggestion to fix the issue? Thanks!

evantahler commented 2 months ago

@HishamAssi - sure, please link the PR once you have it ready

evantahler commented 3 weeks ago

We'll tackle this in-house