[Feature Request][SQL DDL] CREATE SCHEMA

Hi all !
I have the projet to add a Dremio adapter to dbt,
as an easy solution to recreate needed datasets on the datalineage DAG after a dataset change.
Currently there is no way to create a schema (path) but to use the api.
Could it be possible to have a CREATE SCHEMA command ?

Best regards from the french west coast,
Fabrice

Any chance to have this implemented ? It would be pleasant, once connected to the SQL bridge, not to have to issue an api call to create a schema.

Hi all,
sorry to say that, but I cannot understand the way dremio is working as a community.

Thank you @patricker for providing an implementation of the feature :

As this could open up new horizons for dataset automation, in order to use it, I would have to patch the oss edition and rebuild at each new release.

So sad for such a powerful tool !

In order to provide CI/CD features to dremio, I am trying to leverage dbt on dremio, and had a little success. In datalakes, CREATE TABLE will silently create the folder, but nope for CREATE VIEW in spaces. Now dremio is really missing standard CREATE/DROP SCHEMA DDL commands.With these commands, we could use dbt to fully automate shared or user spaces upgrades, version control entire dremio projects, and even use a CI/CD approach.
Could it be possible to see these commands available in near future ?

CREATE [IF NOT EXISTS] SCHEMA|FOLDER “my_space”.“my_subspace”
DROP [IF EXISTS] SCHEMA|FOLDER “my_space”.“my_subspace” [CASCADE]

Once again, thank you for providing us with such a great tool !

@fetanchaud

We have these feature requests in but currently not prioritized, below are the currently existing ones

http://docs.dremio.com/sql-reference/sql-commands/

Hi ! As a data lake engine, dremio would really deserve a CREATE SCHEMA !

1 Like

Agreed - this is a massive hole in the fucntionality I think (as I’m using your adapter :slight_smile: )

2 Likes

@spireite What is the exact reason for the need for “CREATE SCHEMA”, by schema do you mean a space and folder?

@balaji.ramaswamy Correct, that’s exactly what I mean (and @fetanchaud means that also)

For example, in SQLServer I could create a Schema (Space in your terms) via TSQL, then create views in that Schema (space) in TSQL. At that point, I can have everything in one place/technology

It’s (to me and others I’m sure) inconsistent to have to create the space via a REST call from some other script.

I am trying the dbt Dremio adapter from @fetanchaud which actually would address many deployment issues to different environments in CI context.

…but that aside, it remains nonsensical to me that you cannot create Spaces via a SQL statement.

Hi @spireite and @balaji.ramaswamy , thank you for your comments !

@patricker provided an implementation for such a command more than one year ago, but it was not incorporated in dremio code. It’s imho sad because others oss projects do not hesitate in incorporating contributions (dbt :heart:)

As a workaround, CREATE VIEW could implicitly create the (possibly inexisting) SPACE the view is located in.

I strongly feel that dremio+dbt could be an amazing open source solution for enterprise data transformations !

Happy new year, and a lot of courage for 2022 !

1 Like