Hello,
We are using rest api to create virtual datasets, which allows us to set sqlContext. However we found, that when sql from clause contains a full path to a dataset, the set context is ignored. The expectation was, that when a context is set, from clause can only reference tables under that context and that dataset creation fails if the from clause contains a path outside the context
Is it expected behaviour? If yes, is there a way to enforce the sql context?
Thank you, Igor
Context is used for relative path resolution, primarily to keep sql readible.
Thank you for your response, @doron
So I guess, if we wanted to enforce specific context on a sql used to create a vds, we would have to pars it and inspect the from clause? Or is there another way to do it?
Cheers, Igor.
@igorsechyn Yes, enforcing a context is important so your VDS is created under the right space/folder
@balaji.ramaswamy thank you for your message. Enforcing the space and folder is not an issue for us. It is more the sql context, e.g. If a sql is provided by external user, we want to make sure it is only referencing datasets under a specific data source, e.g. under a connected Postgres. So my idea was, that by setting sql context in the api request, would enforce this.
@igorsechyn very interesting idea. However, I believe you should do this by duplicate the whole VDS to new folder and change the source of that VDS.
AFAIK, SQL context is bounded to VDS definition, not in runtime. So you have to build new VDS with definition binding new context.
Also, you can give READ-only permission to prevent your user to not change the context of VDS too, if that may partly help to enforce SQL context.
@chulucninh09 thank you for your reply
That’s what I am trying to confirm. From my tests this was not the case. At Definition time, if I set sql context to a Postgres datasource, I can still reference tables from other sources, like s3, in Definition sql, as Long as the from clause contains a full path, in this case sql context is just ignored.
Yes I faced the same behavior too. But this behavior only works if you refer full path of the dataset.
I would like to perceive this as local context and global context in programming. If you call variable or class without full path, the context has to contain that variable/class. If you want to call the variable/class outside of the context, use the full path.
With that in mind, I still believe you cannot prevent calling out of context dataset. If you really want to enforce context, maybe you need to restrict users to not use global context syntax.
Still wait for @balaji.ramaswamy to have an official answer from Dremio team.