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.

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

Snowflake Tables Don't Show #9511

Open
asignor opened this issue Mar 5, 2019 · 12 comments
Open

Snowflake Tables Don't Show #9511

asignor opened this issue Mar 5, 2019 · 12 comments

Comments

@asignor
Copy link

@asignor asignor commented Mar 5, 2019

Hi guys,

I connected Snowflake and I see no error, but none of tables or views show.
I see the very similar issue #8864 , and after following that thread my problem persists. In my case, the database is called the same in Metabase and in Snowflake, so even if that same bu were occurring, it could not be causing the problem.

  • Your browser and the version: Chrome Version 72.0.3626.119 (Official Build) (64-bit)

  • Your operating system: OS X 10,

  • Your databases: Snowflake

  • Metabase version: You're on version v0.31.2

  • Metabase hosting environment: Heroku

  • Metabase internal database: not sure, Postgres, I think

  • steps to reproduce issue:

  • Log in, navigate to database

  • Add new Snowflake in the Admin console, get successful connection test, but no tables or schemas appear

@flamber

This comment has been minimized.

Copy link
Member

@flamber flamber commented Mar 5, 2019

Hi @asignor
Can you supply the Metabase log? What do you see any errors in the log, if you force the database sync?
There's an issue with case sensitivity, which will be fixed in 0.32 - #9041

@asignor

This comment has been minimized.

Copy link
Author

@asignor asignor commented Mar 6, 2019

Interestingly, it does not seem like I am getting an error. Unless I am reading this wrong.
image

@asignor

This comment has been minimized.

Copy link
Author

@asignor asignor commented Mar 6, 2019

Full text of log below:

Mar 06 16:50:00 DEBUG metabase.sync.util :: Sync operations in flight: {:sync-metadata #{1}}
Mar 06 16:50:00 INFO metabase.sync.util :: STARTING: Sync metadata for h2 Database 1 'Sample Dataset'
Mar 06 16:50:00 DEBUG metabase.sync.util :: STARTING: step 'sync-timezone' for h2 Database 1 'Sample Dataset'
Mar 06 16:50:00 DEBUG metabase.sync.util :: FINISHED: step 'sync-timezone' for h2 Database 1 'Sample Dataset' (9 ms)
Mar 06 16:50:00 DEBUG metabase.sync.util :: STARTING: step 'sync-tables' for h2 Database 1 'Sample Dataset'
Mar 06 16:50:00 INFO metabase.sync.sync-metadata.tables :: Updating description for tables: (Table 'PUBLIC.PEOPLE' Table 'PUBLIC.REVIEWS' Table 'PUBLIC.ORDERS' Table 'PUBLIC.PRODUCTS')
Mar 06 16:50:00 DEBUG metabase.sync.util :: FINISHED: step 'sync-tables' for h2 Database 1 'Sample Dataset' (28 ms)
Mar 06 16:50:00 DEBUG metabase.sync.util :: STARTING: step 'sync-fields' for h2 Database 1 'Sample Dataset'
Mar 06 16:50:00 DEBUG metabase.sync.sync-metadata.fields :: Hash of 'Table 3 'PUBLIC.PEOPLE'' matches stored hash, skipping fields sync for table
Mar 06 16:50:00 DEBUG metabase.sync.sync-metadata.fields :: Hash of 'Table 2 'PUBLIC.ORDERS'' matches stored hash, skipping fields sync for table
Mar 06 16:50:00 DEBUG metabase.sync.sync-metadata.fields :: Hash of 'Table 1 'PUBLIC.PRODUCTS'' matches stored hash, skipping fields sync for table
Mar 06 16:50:00 DEBUG metabase.sync.sync-metadata.fields :: Hash of 'Table 4 'PUBLIC.REVIEWS'' matches stored hash, skipping fields sync for table
Mar 06 16:50:00 DEBUG metabase.sync.util :: FINISHED: step 'sync-fields' for h2 Database 1 'Sample Dataset' (89 ms)
Mar 06 16:50:00 DEBUG metabase.sync.util :: STARTING: step 'sync-fks' for h2 Database 1 'Sample Dataset'
Mar 06 16:50:00 DEBUG metabase.sync.util :: FINISHED: step 'sync-fks' for h2 Database 1 'Sample Dataset' (14 ms)
Mar 06 16:50:00 DEBUG metabase.sync.util :: STARTING: step 'sync-metabase-metadata' for h2 Database 1 'Sample Dataset'
Mar 06 16:50:00 DEBUG metabase.sync.util :: FINISHED: step 'sync-metabase-metadata' for h2 Database 1 'Sample Dataset' (281 ms)
Mar 06 16:50:00 DEBUG metabase.sync.util :: 
#################################################################
# Completed sync on Sample Dataset
# Start: 2019-03-06T16:50:00.040Z
# End: 2019-03-06T16:50:00.464Z
# Duration: 424 ms
# ---------------------------------------------------------------
# Completed step 'sync-timezone'
# Start: 2019-03-06T16:50:00.040Z
# End: 2019-03-06T16:50:00.049Z
# Duration: 9 ms
# Found timezone id UTC
# ---------------------------------------------------------------
# Completed step 'sync-tables'
# Start: 2019-03-06T16:50:00.049Z
# End: 2019-03-06T16:50:00.078Z
# Duration: 29 ms
# Total number of tables sync'd 4, number of tables updated 0
# ---------------------------------------------------------------
# Completed step 'sync-fields'
# Start: 2019-03-06T16:50:00.078Z
# End: 2019-03-06T16:50:00.168Z
# Duration: 90 ms
# Total number of fields sync'd 36, number of fields updated 0
# ---------------------------------------------------------------
# Completed step 'sync-fks'
# Start: 2019-03-06T16:50:00.168Z
# End: 2019-03-06T16:50:00.183Z
# Duration: 15 ms
# Total number of foreign keys sync'd 3, 0 updated and 0 tables failed to update
# ---------------------------------------------------------------
# Completed step 'sync-metabase-metadata'
# Start: 2019-03-06T16:50:00.183Z
# End: 2019-03-06T16:50:00.464Z
# Duration: 281 ms
#################################################################

Mar 06 16:50:00 INFO metabase.sync.util :: FINISHED: Sync metadata for h2 Database 1 'Sample Dataset' (448 ms)
Mar 06 16:50:00 DEBUG metabase.sync.util :: Sync operations in flight: {:analyze #{1}}
Mar 06 16:50:00 INFO metabase.sync.util :: STARTING: Analyze data for h2 Database 1 'Sample Dataset'
Mar 06 16:50:00 DEBUG metabase.sync.util :: STARTING: step 'fingerprint-fields' for h2 Database 1 'Sample Dataset'
Mar 06 16:50:00 INFO metabase.sync.analyze :: fingerprint-fields Analyzed [*******···········································] 😞 15% Table 2 'PUBLIC.ORDERS'
Mar 06 16:50:00 INFO metabase.sync.analyze :: fingerprint-fields Analyzed [***************···································] 😕 31% Table 4 'PUBLIC.REVIEWS'
Mar 06 16:50:00 DEBUG metabase.sync.util :: FINISHED: step 'fingerprint-fields' for h2 Database 1 'Sample Dataset' (22 ms)
Mar 06 16:50:00 DEBUG metabase.sync.util :: STARTING: step 'classify-fields' for h2 Database 1 'Sample Dataset'
Mar 06 16:50:00 INFO metabase.sync.analyze :: classify-fields Analyzed [***********************···························] 😬 46% Table 2 'PUBLIC.ORDERS'
Mar 06 16:50:00 INFO metabase.sync.analyze :: classify-fields Analyzed [******************************····················] 😌 62% Table 4 'PUBLIC.REVIEWS'
Mar 06 16:50:00 DEBUG metabase.sync.util :: FINISHED: step 'classify-fields' for h2 Database 1 'Sample Dataset' (8 ms)
Mar 06 16:50:00 DEBUG metabase.sync.util :: STARTING: step 'classify-tables' for h2 Database 1 'Sample Dataset'
Mar 06 16:50:00 INFO metabase.sync.analyze :: classify-tables Analyzed [**************************************············] 😋 77% Table 2 'PUBLIC.ORDERS'
Mar 06 16:50:00 INFO metabase.sync.analyze :: classify-tables Analyzed [**********************************************····] 😍 92% Table 4 'PUBLIC.REVIEWS'
Mar 06 16:50:00 DEBUG metabase.sync.util :: FINISHED: step 'classify-tables' for h2 Database 1 'Sample Dataset' (4 ms)
Mar 06 16:50:00 DEBUG metabase.sync.util :: 
#################################################################
# Completed analyze on Sample Dataset
# Start: 2019-03-06T16:50:00.491Z
# End: 2019-03-06T16:50:00.528Z
# Duration: 37 ms
# ---------------------------------------------------------------
# Completed step 'fingerprint-fields'
# Start: 2019-03-06T16:50:00.491Z
# End: 2019-03-06T16:50:00.514Z
# Duration: 23 ms
# Fingerprint updates attempted 0, updated 0, no data found 0, failed 0
# ---------------------------------------------------------------
# Completed step 'classify-fields'
# Start: 2019-03-06T16:50:00.514Z
# End: 2019-03-06T16:50:00.523Z
# Duration: 9 ms
# Total number of fields classified 0, 0 failed
# ---------------------------------------------------------------
# Completed step 'classify-tables'
# Start: 2019-03-06T16:50:00.523Z
# End: 2019-03-06T16:50:00.528Z
# Duration: 5 ms
# Total number of tables classified 4, 0 updated
#################################################################

Mar 06 16:50:00 INFO metabase.sync.util :: FINISHED: Analyze data for h2 Database 1 'Sample Dataset' (76 ms)
Mar 06 16:50:02 DEBUG metabase.middleware :: GET /api/database/5 200 (11 ms) (3 DB calls). Jetty threads: 8/50 (5 busy, 2 idle, 0 queued)
Mar 06 16:50:05 DEBUG metabase.middleware :: POST /api/database/5/sync_schema 200 (3 ms) (1 DB calls). Jetty threads: 8/50 (5 busy, 2 idle, 0 queued)
Mar 06 16:50:05 DEBUG metabase.sync.util :: Sync operations in flight: {:sync-metadata #{5}}
Mar 06 16:50:05 INFO metabase.sync.util :: STARTING: Sync metadata for snowflake Database 5 'coupasphere'
Mar 06 16:50:05 DEBUG metabase.sync.util :: STARTING: step 'sync-timezone' for snowflake Database 5 'coupasphere'
Mar 06 16:50:08 DEBUG metabase.middleware :: POST /api/database/5/rescan_values 200 (3 ms) (1 DB calls). Jetty threads: 8/50 (5 busy, 2 idle, 0 queued)
Mar 06 16:50:08 DEBUG metabase.sync.util :: Sync operations in flight: {:sync-metadata #{5}, :cache-field-values #{5}}
Mar 06 16:50:08 INFO metabase.sync.util :: STARTING: Cache field values in snowflake Database 5 'coupasphere'
Mar 06 16:50:08 DEBUG metabase.sync.util :: STARTING: step 'update-field-values' for snowflake Database 5 'coupasphere'
Mar 06 16:50:08 DEBUG metabase.sync.util :: FINISHED: step 'update-field-values' for snowflake Database 5 'coupasphere' (2 ms)
Mar 06 16:50:08 DEBUG metabase.sync.util :: 
#################################################################
# Completed field values scanning on coupasphere
# Start: 2019-03-06T16:50:08.114Z
# End: 2019-03-06T16:50:08.117Z
# Duration: 3 ms
# ---------------------------------------------------------------
# Completed step 'update-field-values'
# Start: 2019-03-06T16:50:08.114Z
# End: 2019-03-06T16:50:08.117Z
# Duration: 3 ms
# Updated null field value sets, created null, deleted null with null errors
#################################################################

Mar 06 16:50:08 INFO metabase.sync.util :: FINISHED: Cache field values in snowflake Database 5 'coupasphere' (10 ms)
Mar 06 16:50:09 DEBUG metabase.sync.util :: FINISHED: step 'sync-timezone' for snowflake Database 5 'coupasphere' (4 s)
Mar 06 16:50:09 DEBUG metabase.sync.util :: STARTING: step 'sync-tables' for snowflake Database 5 'coupasphere'
Mar 06 16:50:09 DEBUG metabase.sync.util :: FINISHED: step 'sync-tables' for snowflake Database 5 'coupasphere' (627 ms)
Mar 06 16:50:09 DEBUG metabase.sync.util :: STARTING: step 'sync-fields' for snowflake Database 5 'coupasphere'
Mar 06 16:50:09 DEBUG metabase.sync.util :: FINISHED: step 'sync-fields' for snowflake Database 5 'coupasphere' (1 ms)
Mar 06 16:50:09 DEBUG metabase.sync.util :: STARTING: step 'sync-fks' for snowflake Database 5 'coupasphere'
Mar 06 16:50:09 DEBUG metabase.sync.util :: FINISHED: step 'sync-fks' for snowflake Database 5 'coupasphere' (2 ms)
Mar 06 16:50:09 DEBUG metabase.sync.util :: STARTING: step 'sync-metabase-metadata' for snowflake Database 5 'coupasphere'
Mar 06 16:50:10 DEBUG metabase.sync.util :: FINISHED: step 'sync-metabase-metadata' for snowflake Database 5 'coupasphere' (826 ms)
Mar 06 16:50:10 DEBUG metabase.sync.util :: 
#################################################################
# Completed sync on coupasphere
# Start: 2019-03-06T16:50:05.271Z
# End: 2019-03-06T16:50:10.682Z
# Duration: 5 s
# ---------------------------------------------------------------
# Completed step 'sync-timezone'
# Start: 2019-03-06T16:50:05.271Z
# End: 2019-03-06T16:50:09.224Z
# Duration: 4 s
# Found timezone id UTC
# ---------------------------------------------------------------
# Completed step 'sync-tables'
# Start: 2019-03-06T16:50:09.224Z
# End: 2019-03-06T16:50:09.851Z
# Duration: 627 ms
# Total number of tables sync'd 0, number of tables updated 0
# ---------------------------------------------------------------
# Completed step 'sync-fields'
# Start: 2019-03-06T16:50:09.851Z
# End: 2019-03-06T16:50:09.853Z
# Duration: 2 ms
# Total number of fields sync'd null, number of fields updated null
# ---------------------------------------------------------------
# Completed step 'sync-fks'
# Start: 2019-03-06T16:50:09.853Z
# End: 2019-03-06T16:50:09.856Z
# Duration: 3 ms
# Total number of foreign keys sync'd 0, 0 updated and 0 tables failed to update
# ---------------------------------------------------------------
# Completed step 'sync-metabase-metadata'
# Start: 2019-03-06T16:50:09.856Z
# End: 2019-03-06T16:50:10.682Z
# Duration: 826 ms
#################################################################

Mar 06 16:50:10 INFO metabase.sync.util :: FINISHED: Sync metadata for snowflake Database 5 'coupasphere' (5 s)
Mar 06 16:50:10 DEBUG metabase.sync.util :: Sync operations in flight: {:analyze #{5}}
Mar 06 16:50:10 INFO metabase.sync.util :: STARTING: Analyze data for snowflake Database 5 'coupasphere'
Mar 06 16:50:10 DEBUG metabase.sync.util :: STARTING: step 'fingerprint-fields' for snowflake Database 5 'coupasphere'
Mar 06 16:50:10 DEBUG metabase.sync.util :: FINISHED: step 'fingerprint-fields' for snowflake Database 5 'coupasphere' (485 µs)
Mar 06 16:50:10 DEBUG metabase.sync.util :: STARTING: step 'classify-fields' for snowflake Database 5 'coupasphere'
Mar 06 16:50:10 DEBUG metabase.sync.util :: FINISHED: step 'classify-fields' for snowflake Database 5 'coupasphere' (190 µs)
Mar 06 16:50:10 DEBUG metabase.sync.util :: STARTING: step 'classify-tables' for snowflake Database 5 'coupasphere'
Mar 06 16:50:10 DEBUG metabase.sync.util :: FINISHED: step 'classify-tables' for snowflake Database 5 'coupasphere' (187 µs)
Mar 06 16:50:10 DEBUG metabase.sync.util :: 
#################################################################
# Completed analyze on coupasphere
# Start: 2019-03-06T16:50:10.706Z
# End: 2019-03-06T16:50:10.709Z
# Duration: 3 ms
# ---------------------------------------------------------------
# Completed step 'fingerprint-fields'
# Start: 2019-03-06T16:50:10.706Z
# End: 2019-03-06T16:50:10.707Z
# Duration: 1000 µs
# Fingerprint updates attempted null, updated null, no data found null, failed null
# ---------------------------------------------------------------
# Completed step 'classify-fields'
# Start: 2019-03-06T16:50:10.707Z
# End: 2019-03-06T16:50:10.708Z
# Duration: 1000 µs
# Total number of fields classified 0, 0 failed
# ---------------------------------------------------------------
# Completed step 'classify-tables'
# Start: 2019-03-06T16:50:10.708Z
# End: 2019-03-06T16:50:10.709Z
# Duration: 1000 µs
# Total number of tables classified 0, 0 updated
#################################################################

Mar 06 16:50:10 INFO metabase.sync.util :: FINISHED: Analyze data for snowflake Database 5 'coupasphere' (18 ms)```
@flamber

This comment has been minimized.

Copy link
Member

@flamber flamber commented Mar 6, 2019

@asignor
Hmmm.... it's sync'ing 0 tables and 0 fields. Do you see Metabase doing connection and queries in the Snowflake log?

@asignor

This comment has been minimized.

Copy link
Author

@asignor asignor commented Mar 6, 2019

I do, here is an example of a query that ran using data warehouse metabase_wh:

show /* JDBC:DatabaseMetaData.getTables() */ objects in schema "coupasphere"."SALESFORCE"

@asignor

This comment has been minimized.

Copy link
Author

@asignor asignor commented Mar 6, 2019

I think I found the problem. When I look at the log, some of the entries have an error status (the red X).
Here is the error:
image

@asignor

This comment has been minimized.

Copy link
Author

@asignor asignor commented Mar 6, 2019

It seems that query language is incorrect. When I run as is,

show /* JDBC:DatabaseMetaData.getTables() */ objects in schema "coupasphere"."SALESFORCE" 

and context matching the metabase environment, I get an error, but with the same context, if I run

show /* JDBC:DatabaseMetaData.getTables() */ objects in schema "SALESFORCE" 

I get the expected result.

So it seems like metabase is literally not finding the views in the schema.

@mattarderne

This comment has been minimized.

Copy link

@mattarderne mattarderne commented Mar 26, 2019

I'm experiencing similiar.

Settings:

  • Your browser and the version: Firefox 66.0 (64-bit)
  • Your operating system: Mac mojave 10.14.3,
  • Your databases: Snowflake
  • Metabase version: You're running Metabase 0.31.0 which is the latest and greatest!
  • Metabase hosting environment: Docker
  • Metabase internal database: h2
  • Steps to reproduce issue: Log in, navigate to database, add new Snowflake in the Admin console, get successful connection test, but no tables or schemas appear

Symptoms:

  • Select statements from the SQL window DO work
  • I can't see anything in the Our Data view

EDIT: My tables are now showing, I'm not sure why, the only thing I did differently was to run docker image using the full command instead: docker run -d -p 3000:3000 -v ~/metabase-data:/metabase-data -e "MB_DB_FILE=/metabase-data/metabase.db" --name metabase metabase/metabase

But starting a new image on a different port now also works.

Not particularly useful contribution... sorry

@grayzman

This comment has been minimized.

Copy link

@grayzman grayzman commented May 10, 2019

I have exactly the same symptoms with the latest Metabase release ( v0.32.7) and Snowflake as a datasource, metadata backend is Postgres.
All queries in dashboard run fine, but no FiledFilter are available, and no table under DataModel
admin/datamodel/database/1

Screenshot (34)_LI

@grayzman

This comment has been minimized.

Copy link

@grayzman grayzman commented May 10, 2019

Figured out the overall issue.
Snowflake metadata is case-sensitive and Snowflake stores metadata internally in UPPERCASE.
When a new "Database" data source is added the following fields "Warehouse", "Database name", and "Schema" need to be entered in uppercase

@camsaul camsaul added this to the 0.32.8 milestone May 11, 2019
@camsaul camsaul self-assigned this May 11, 2019
@camsaul camsaul modified the milestones: 0.32.8, 0.33 May 13, 2019
@camsaul

This comment has been minimized.

Copy link
Member

@camsaul camsaul commented May 13, 2019

@grayzman @asignor it seems like we can fix this issue by automatically uppercasing the warehouse, database name, and schema. Can you think of any situations where doing that would break something else?

@grayzman

This comment has been minimized.

Copy link

@grayzman grayzman commented May 16, 2019

Hi Cam,
I think that your proposal is correct and don't see any issue with the implementation.
Thanks!

@camsaul camsaul removed their assignment Aug 1, 2019
@sbelak sbelak self-assigned this Sep 17, 2019
@camsaul camsaul modified the milestones: 0.33, 0.34.0 Sep 18, 2019
@camsaul camsaul modified the milestones: 0.34.0, 0.35.0 Jan 7, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
7 participants
You can’t perform that action at this time.