Dataset metadata/deployment features

1

Hi,

I was wondering if the following features are available in Dremio:

Is it possible to query the metadata of virtual datasets to retrieve:
when the dataset was created (and who did it?)?
when the dataset was updated (underlying SQL statement) (and who did it?)?

Is it possible to export the definition of virtual datasets (in bulk or one-by-one) and import it in another folder or Dremio instance without having to manually copy the SQL of the virtual dataset, manually click ‘new query’, chose the right context, paste the sql, save the dataset with the correct name, … (and even also preferably with command line interface)?

Is it possible to refer in a virtual dataset to another virtual dataset using relative folder names (like in unix style: …/folderX/folderY/datasetZ? or ./folderX/folderY/datasetW?

Thanks for your feedback.

Krgds,

Danny

2

@DannyPannemans

Currently we do not have a way to find out the below
when the dataset was created (and who did it?)?
when the dataset was updated (underlying SQL statement) (and who did it?)?

You can run a create or replace VDS script by querying information_schema.views and generate a dynamic script to recreate in another instance

No unix style, its only “.”

Thanks
@balaji.ramaswamy

3

Hi @DannyPannemans ,
Did you end up finding out how to export virtual dataset SQL in bulk? We would also like to do this.

Thanks,
David

4

@dhowe Would downloading “information_schema.views” help?

5

That’s exactly what I was after!
Thank-you @balaji.ramaswamy

6

Unfortunately it doesn’t provide you with the context of the virtual dataset… So you won’t have all the metadata information but only the plain sql.

7

@DannyPannemans TABLE_SCHEMA column should tell the space/folder path for the VDS

8

For one of our use case, we used external_query feature to get metadata on physical DB (oracle) to get native metadata on schema (columns comments, native datatypes…). Then we joined information_schema with external_query. It helps for semantic layer.
I hope it could help.

9

Hi,

The table_schema column does provide the space/folder where the VDS resides but does not tell you the context of the VDS. If you hit the ‘edit query’ button on a VDS you can change the context of the query (see right button grey area in the sql editor) which can be different from the location of the VDS. So where can we find the ‘context’ of the VDS?
Krgds,

Danny

10

@DannyPannemans Context is only for that session, do you want to know which context the job was executed? Either you need to qualify your datasets with the fully qualified name or set context to where the VDS is saved, example certain space and folder, without context or fully qualified dataset name, the query will not run, during job submission via an API, you can set context

http://docs.dremio.com/rest-api/sql/post-sql/

Also using the GET CATALOG by path API, we can get the context for the VDS (If this is what you are looking for)

http://docs.dremio.com/rest-api/catalog/get-catalog-path/

"sqlContext": [
        "localmongo",
        "testdb"
    ],