Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

db-doc failing with Snowflake #3622

Closed
awhitford-cip opened this issue Dec 28, 2022 · 8 comments
Closed

db-doc failing with Snowflake #3622

awhitford-cip opened this issue Dec 28, 2022 · 8 comments
Labels
DBSnowflake TypeBug

Comments

@awhitford-cip
Copy link

Environment

Liquibase Version: 4.18.0

Liquibase Integration & Version: GitHub Actions

Liquibase Extension(s) & Version: n/a

Database Vendor & Version: Snowflake

Operating System Type & Version: Linux

Infrastructure Type/Provider: GitHub Actions

Description

Trying to use the db-doc action to generate database documentation against a Snowflake database. Alas, I get this error instead:

Error getting jdbc:snowflake://blahblah-connection.snowflakecomputing.com:443/ view with liquibase.statement.core.GetViewDefinitionStatement@40cb95c1

  • Caused by: Expected single row from liquibase.statement.core.GetViewDefinitionStatement@6516181f but got 0
  • Caused by: Empty result set, expected one row

The full stack trace is:

[2022-12-27 23:43:14] SEVERE [liquibase.integration] Error getting jdbc:snowflake://blahblah-connection.snowflakecomputing.com:443/ view with liquibase.statement.core.GetViewDefinitionStatement@40cb95c1
  - Caused by: Expected single row from liquibase.statement.core.GetViewDefinitionStatement@6516181f but got 0
  - Caused by: Empty result set, expected one row
liquibase.exception.CommandExecutionException: liquibase.exception.DatabaseException: Error getting jdbc:snowflake://blahblah-connection.snowflakecomputing.com:443/ view with liquibase.statement.core.GetViewDefinitionStatement@40cb95c1
	at liquibase.command.CommandScope.execute(CommandScope.java:178)
	at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:55)
	at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:24)
	at picocli.CommandLine.executeUserObject(CommandLine.java:2041)
	at picocli.CommandLine.access$1500(CommandLine.java:148)
	at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2461)
	at picocli.CommandLine$RunLast.handle(CommandLine.java:2453)
	at picocli.CommandLine$RunLast.handle(CommandLine.java:2415)
	at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2273)
	at picocli.CommandLine$RunLast.execute(CommandLine.java:2417)
	at picocli.CommandLine.execute(CommandLine.java:2170)
	at liquibase.integration.commandline.LiquibaseCommandLine.lambda$execute$1(LiquibaseCommandLine.java:[352](https://github.com/MyOrg/edp-snowflake/actions/runs/3790586318/jobs/6445309629#step:12:353))
	at liquibase.Scope.child(Scope.java:189)
	at liquibase.Scope.child(Scope.java:165)
	at liquibase.integration.commandline.LiquibaseCommandLine.execute(LiquibaseCommandLine.java:317)
	at liquibase.integration.commandline.LiquibaseCommandLine.main(LiquibaseCommandLine.java:84)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
	at liquibase.integration.commandline.LiquibaseLauncher.main(LiquibaseLauncher.java:107)
Caused by: liquibase.exception.DatabaseException: Error getting jdbc:snowflake://blahblah-connection.snowflakecomputing.com:443/ view with liquibase.statement.core.GetViewDefinitionStatement@40cb95c1
	at liquibase.snapshot.jvm.ViewSnapshotGenerator.snapshotObject(ViewSnapshotGenerator.java:132)
	at liquibase.snapshot.jvm.JdbcSnapshotGenerator.snapshot(JdbcSnapshotGenerator.java:66)
	at liquibase.snapshot.SnapshotGeneratorChain.snapshot(SnapshotGeneratorChain.java:49)
	at liquibase.snapshot.jvm.JdbcSnapshotGenerator.snapshot(JdbcSnapshotGenerator.java:69)
	at liquibase.snapshot.SnapshotGeneratorChain.snapshot(SnapshotGeneratorChain.java:49)
	at liquibase.snapshot.jvm.JdbcSnapshotGenerator.snapshot(JdbcSnapshotGenerator.java:69)
	at liquibase.snapshot.SnapshotGeneratorChain.snapshot(SnapshotGeneratorChain.java:49)
	at liquibase.snapshot.jvm.JdbcSnapshotGenerator.snapshot(JdbcSnapshotGenerator.java:69)
	at liquibase.snapshot.SnapshotGeneratorChain.snapshot(SnapshotGeneratorChain.java:49)
	at liquibase.snapshot.jvm.JdbcSnapshotGenerator.snapshot(JdbcSnapshotGenerator.java:69)
	at liquibase.snapshot.SnapshotGeneratorChain.snapshot(SnapshotGeneratorChain.java:49)
	at liquibase.snapshot.jvm.JdbcSnapshotGenerator.snapshot(JdbcSnapshotGenerator.java:69)
	at liquibase.snapshot.SnapshotGeneratorChain.snapshot(SnapshotGeneratorChain.java:49)
	at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:312)
	at liquibase.snapshot.DatabaseSnapshot.replaceObject(DatabaseSnapshot.java:445)
	at liquibase.snapshot.DatabaseSnapshot.replaceObject(DatabaseSnapshot.java:467)
	at liquibase.snapshot.DatabaseSnapshot.includeNestedObjects(DatabaseSnapshot.java:[373](https://github.com/MyOrg/edp-snowflake/actions/runs/3790586318/jobs/6445309629#step:12:374))
	at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:335)
	at liquibase.snapshot.DatabaseSnapshot.init(DatabaseSnapshot.java:105)
	at liquibase.snapshot.DatabaseSnapshot.<init>(DatabaseSnapshot.java:58)
	at liquibase.snapshot.JdbcDatabaseSnapshot.<init>(JdbcDatabaseSnapshot.java:34)
	at liquibase.snapshot.SnapshotGeneratorFactory.createSnapshot(SnapshotGeneratorFactory.java:215)
	at liquibase.snapshot.SnapshotGeneratorFactory.createSnapshot(SnapshotGeneratorFactory.java:188)
	at liquibase.snapshot.SnapshotGeneratorFactory.createSnapshot(SnapshotGeneratorFactory.java:155)
	at liquibase.changelog.visitor.DBDocVisitor.writeHTML(DBDocVisitor.java:127)
	at liquibase.Liquibase$20.run(Liquibase.java:2297)
	at liquibase.Scope.lambda$child$0(Scope.java:180)
	at liquibase.Scope.child(Scope.java:189)
	at liquibase.Scope.child(Scope.java:179)
	at liquibase.Scope.child(Scope.java:158)
	at liquibase.Liquibase.runInScope(Liquibase.java:2445)
	at liquibase.Liquibase.generateDocumentation(Liquibase.java:2273)
	at liquibase.Liquibase.generateDocumentation(Liquibase.java:2268)
	at liquibase.integration.commandline.Main.doMigration(Main.java:1826)
	at liquibase.integration.commandline.Main$1.lambda$run$0(Main.java:396)
	at liquibase.Scope.lambda$child$0(Scope.java:180)
	at liquibase.Scope.child(Scope.java:189)
	at liquibase.Scope.child(Scope.java:179)
	at liquibase.Scope.child(Scope.java:158)
	at liquibase.integration.commandline.Main$1.run(Main.java:[395](https://github.com/MyOrg/edp-snowflake/actions/runs/3790586318/jobs/6445309629#step:12:396))
	at liquibase.integration.commandline.Main$1.run(Main.java:217)
	at liquibase.Scope.child(Scope.java:189)
	at liquibase.Scope.child(Scope.java:165)
	at liquibase.integration.commandline.Main.run(Main.java:217)
	at liquibase.command.AbstractCliWrapperCommandStep.run(AbstractCliWrapperCommandStep.java:33)
	at liquibase.command.CommandScope.execute(CommandScope.java:172)
	... 20 more
Caused by: liquibase.exception.DatabaseException: Expected single row from liquibase.statement.core.GetViewDefinitionStatement@6516181f but got 0
	at liquibase.executor.jvm.JdbcExecutor.queryForObject(JdbcExecutor.java:222)
	at liquibase.executor.jvm.JdbcExecutor.queryForObject(JdbcExecutor.java:233)
	at liquibase.executor.jvm.JdbcExecutor.queryForObject(JdbcExecutor.java:228)
	at liquibase.database.AbstractJdbcDatabase.getViewDefinition(AbstractJdbcDatabase.java:907)
	at liquibase.snapshot.jvm.ViewSnapshotGenerator.snapshotObject(ViewSnapshotGenerator.java:84)
	... 65 more
Caused by: liquibase.exception.DatabaseException: Empty result set, expected one row
	at liquibase.util.JdbcUtil.requiredSingleResult(JdbcUtil.java:132)
	at liquibase.executor.jvm.JdbcExecutor.queryForObject(JdbcExecutor.java:220)
	... 69 more

Steps To Reproduce

To reproduce, add a step like this to a Github Workflow:

    - name: Generate Liquibase Database Documentation
      uses: liquibase-github-actions/db-doc@v4.18.0
      with:
        headless: true
        classpath: liquibase
        changeLogFile: liquibase/changelog.snowflake.yml
        outputDirectory: liquibase/db-doc
        url: ${{ SECRET.DB_URL }}
        username: ${{ SECRET.DB_USERNAME }}
        password: ${{ SECRET.DB_PASSWORD }}
        logLevel: INFO

Additional Context

My Snowflake database does have some Views and Materialized Views defined. I am unclear if there is a specific object that is breaking this process.
@awhitford-cip
Copy link
Author

Note that if GetViewDefinitionStatement had a toString method, then this would be easier to debug because we are seeing output like this:

liquibase.statement.core.GetViewDefinitionStatement@40cb95c1

which doesn't show the viewName property.

@awhitford-cip
Copy link
Author

I confirmed that a Materialized View is breaking dbDoc.

I traced the SQL to something like:

select view_definition from information_schema.views
where table_name=? and table_schema=? and table_catalog=?

In the case of a Materialized View, the result set has 0 rows. It appears that Snowflake does not list Materialized views in INFORMATION_SCHEMA.VIEWS. (This is likely a bug.)

An alternative solution is to use the GET_DDL function:

select GET_DDL('view', 'CATALOG.SCHEMA.VIEW_NAME', true) as view_definition

It basically gives the expected result except that the view_definition is a create or replace statement rather than a create statement.

@awhitford-cip
Copy link
Author

Another alternative is to use show views:

show views like 'VIEW_NAME' in schema CATALOG.SCHEMA

This returns a result set with a bunch of columns. The text column is the view definition. Note that this value is a create statement, not a create or replace statement.

@awhitford-cip
Copy link
Author

Another workaround is to query SNOWFLAKE.ACCOUNT_USAGE.VIEWS:

select view_definition from SNOWFLAKE.ACCOUNT_USAGE.VIEWS
where table_name='VIEW_NAME' and table_schema='SCHEMA' and table_catalog='CATALOG' and DELETED is null

I like this option the least because:

  1. It is natural to have access to INFORMATION_SCHEMA, but access to SNOWFLAKE.ACCOUNT_USAGE requires a special grant
  2. It returns multiple rows; the extra and DELETED is null clause ensures that you are getting the latest view_definition
  3. It seems like a slow query

@FBurguer
Copy link

Hello! Thanks for the detailed report! I see that you have some insight on the matter, do you minde submiting a PR with a possible fix? It would help speeding up the process . Let us now. Thanks!

@awhitford-cip
Copy link
Author

I'm open to submitting a PR, but I am honestly unclear as to how to best resolve this. I feel like the discrepancy from Snowflake is unexpected, but I doubt that they will resolve it anytime soon. 🤔

@filipelautert
Copy link
Collaborator

Hello! I think that PR #3794 that is present on 4.21.1 may have fixed it.. could you give it a try?

@FBurguer
Copy link

FBurguer commented Jun 6, 2023

This issue got stale, closing it for now, feel free to re-open if needed

@FBurguer FBurguer closed this as not planned Won't fix, can't repro, duplicate, stale Jun 6, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
DBSnowflake TypeBug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@filipelautert @FBurguer @awhitford-cip