Don't see tables in external schemas (Redshift Spectrum)


#1

Hi Everyone,
I can write query from my table using New Query/Native Question but i don’t see my external table and schema in New Query/Custom or Data Reference option. Why?

Thank you


#2

Need some more information - at the very least the version of Metabase.

Has the table been created recently? If so, have you synched the schema?


#3

Hi and welcome! :slight_smile:

Phew, I see @AndrewMBaines beat me to a first response … again … anyways:

Hard to give a lot of qualified input on apart from “probably not supported with your specific database/Metabase version/your configuration” without at least knowing what database you have connected to? My best guess is that support for external schemas (like linked databases in SQL Server) hasn’t gotten any consistent kind of love - yet… so ymmv

I know you’re not reporting a bug - but in general Please read me before reporting a bug! has good advice on what info is useful for others to jump in and reproduce any “experience” and give better responses in general.


#4

I have tested on two different versions: v0.29.3 and 0.30.2. It’s going on Redshift database. Offcourse i have synced the schema. No result.


#5

Is there anything different about the table you can’t see? Can you create a view on the table, if so, is the view visible?
What happens if you add a 2nd copy of the same database?


#6

I can create view but i can’t access it because of “Invalid operation: Nested tables do not support ‘*’ in the SELECT clause.”
My external table based on S3 gzip files.


#7

Ah, ding! I think the coin may have dropped for me now. So, with “external table” are you referring to Redshift Spectrum like this: https://docs.aws.amazon.com/redshift/latest/dg/c-spectrum-external-tables.html ??

Not sure how well that is supported. Think I saw some open GitHub issues regarding that…

edit, yep:


#8

Nested data are already supported by Redhisft Spectrum so is not a problem. O created view and i can access it already. But would like to access my external table through Metabase but still i can’t see it (using New Query/Custom or Data Reference). With Native Question everything works.


#9

@deny yes, I understand that Redshift (and Redshift Spectrum) in itself supports this. That’s also why you have it working with a native/raw SQL query. Just that it’s not supported by Metabase GUI or Custom questions - which rely on the metadata scan.

That’s also why I linked to the known Metabase GitHub issue above. It seems to be the same problem you are reporting - right?

Here’s text taken from https://github.com/metabase/metabase/issues/7833 pointed to above:

How to reproduce:

  • Create new datasource connection to redshift, with redshift spectrum available.
  • create a new Custom question
  • Schema/table for redshift spectrum does not appear.