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?
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?
@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.
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.