Metadata queries yield incorrect results when user space named after an e-mail address is targeted

[Marnixvdb]

When targeting the personal space of a user, using their e-mail address as the identifier of the space, many of the queries generated by the macros in https://github.com/dremio/dbt-dremio/blob/main/dbt/include/dremio/macros/adapters/metadata.sql yield incorrect results. The queries consistently assume there are no dots in the database name, which is not true for the personal user spaces.

I'm unsure of what the actual impact is, because I was able to perform most operations against my user space anyway (which we do for development purposes), but it's still something that I think should be fixed.

[ravjotbrar]

Thanks for raising this issue @Marnixvdb. I'll do a deeper dive into this and take action accordingly

[fabrice-etanchaud]

Hi @Marnixvdb ,

You are right, as written here : https://github.com/fabrice-etanchaud/dbt-dremio#databases, because in information_schema tables, you cannot split the schema field in space+folder, as '.' is the separator.

[Marnixvdb]

Thanks for the clarification, @fabrice-etanchaud!

You're right: in information_schema it isn't possible, but if we'd query the sys."tables" (software) / sys.project."tables" (cloud) instead, it is possible. The sys."tables" keeps the path in an array, making making it trivial to select the database/space and the rest of the path.

[fabrice-etanchaud]

Hi @Marnixvdb , I am afraid, on my dremio CE 23.1.0-202211250136090978-a79618c7, I cannot find any sys."tables" :

select * from information_schema."tables"
where table_schema like 'sys%'
order by table_name

[Marnixvdb]

That's odd. On Dremio Cloud they're there and according to the reference they should also be present in the software edition. I'll set up a software instance to get a more complete picture of the possibilities.

[Marnixvdb]

in the Dremio software version, the tables in sys appear only when they are used. For example sys.version wasn't listed by infromation_schema.tables until I queried it for the first time. However, sys.views and sys.tables weren't queryable at all, not even after defining a couple of views and creating a table. That means using the system tables isn't an option either. When I have time I'll take another look at the code, because sometimes the value of the space (captured as database) is actually available when the query is composed, so instead of taking the substring of the schema_name after the first dot, we could use the length of the database value to determine the correct position of the split.