I created a virtual dataset consisting of an alter table command.
alter table physical-data-set refresh metadata
I can run this query (edit and click preview) and it works fine. But if I try to execute the query via “query” it fails while expanding the query.
Can you share the query profile and error you see?
The plan doesn’t complete, so it just says failed. Here is the error, it cut off the table from the name and is trying to treat the folder as the table.
View SQL alter table datasource.bucket.user.folder.tbl refresh metadata
(org.apache.calcite.runtime.CalciteContextException) From line 1, column 13 to line 1, column 68: Table ‘datasource.bucket.user.folder’ not found
sun.reflect.NativeConstructorAccessorImpl.newInstance0():-2
sun.reflect.NativeConstructorAccessorImpl.newInstance():62
sun.reflect.DelegatingConstructorAccessorImpl.newInstance():45
java.lang.reflect.Constructor.newInstance():423
org.apache.calcite.runtime.Resources$ExInstWithCause.ex():463
org.apache.calcite.sql.SqlUtil.newContextException():803
org.apache.calcite.sql.SqlUtil.newContextException():788
org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():4470
org.apache.calcite.sql.validate.DelegatingScope.fullyQualify():324
org.apache.calcite.sql.validate.SqlValidatorImpl.validateIdentifier():2795
org.apache.calcite.sql.SqlIdentifier.validateExpr():324
org.apache.calcite.sql.SqlOperator.validateCall():407
org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall():4900
org.apache.calcite.sql.SqlCall.validate():115
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():899
org.apache.calcite.sql.validate.SqlValidatorImpl.validate():609
com.dremio.exec.planner.sql.SqlConverter.validate():168
com.dremio.exec.planner.sql.DremioSqlToRelConverter.expandView():83
com.dremio.exec.planner.sql.ExtendedToRelContext.expandView():70
com.dremio.exec.planner.logical.ViewTable.toRel():116
com.dremio.exec.catalog.DremioPrepareTable.toRel():89
org.apache.calcite.sql2rel.RelStructuredTypeFlattener.rewriteRel():657
sun.reflect.GeneratedMethodAccessor210.invoke():-1
sun.reflect.DelegatingMethodAccessorImpl.invoke():43
java.lang.reflect.Method.invoke():498
org.apache.calcite.util.ReflectUtil.invokeVisitorInternal():257
org.apache.calcite.util.ReflectUtil.invokeVisitor():214
org.apache.calcite.util.ReflectUtil$1.invokeVisitor():464
org.apache.calcite.sql2rel.RelStructuredTypeFlattener$RewriteRelVisitor.visit():721
org.apache.calcite.rel.SingleRel.childrenAccept():72
org.apache.calcite.rel.RelVisitor.visit():44
org.apache.calcite.sql2rel.RelStructuredTypeFlattener$RewriteRelVisitor.visit():716
org.apache.calcite.sql2rel.RelStructuredTypeFlattener.rewrite():177
com.dremio.exec.planner.sql.DremioSqlToRelConverter.flattenTypes():66
com.dremio.exec.planner.sql.SqlConverter.toConvertibleRelRoot():251
com.dremio.exec.planner.sql.handlers.PrelTransformer.toConvertibleRelRoot():717
com.dremio.exec.planner.sql.handlers.PrelTransformer.convertToRelRootAndJdbc():727
com.dremio.exec.planner.sql.handlers.PrelTransformer.convertToRel():786
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():168
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():162
com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():43
com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():69
com.dremio.exec.work.foreman.AttemptManager.run():291
java.util.concurrent.ThreadPoolExecutor.runWorker():1149
java.util.concurrent.ThreadPoolExecutor$Worker.run():624
java.lang.Thread.run():748
Caused By (org.apache.calcite.sql.validate.SqlValidatorException) Table ‘datasource.bucket.user.folder’ not found
sun.reflect.NativeConstructorAccessorImpl.newInstance0():-2
sun.reflect.NativeConstructorAccessorImpl.newInstance():62
sun.reflect.DelegatingConstructorAccessorImpl.newInstance():45
java.lang.reflect.Constructor.newInstance():423
org.apache.calcite.runtime.Resources$ExInstWithCause.ex():463
org.apache.calcite.runtime.Resources$ExInst.ex():572
org.apache.calcite.sql.SqlUtil.newContextException():803
org.apache.calcite.sql.SqlUtil.newContextException():788
org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():4470
org.apache.calcite.sql.validate.DelegatingScope.fullyQualify():324
org.apache.calcite.sql.validate.SqlValidatorImpl.validateIdentifier():2795
org.apache.calcite.sql.SqlIdentifier.validateExpr():324
org.apache.calcite.sql.SqlOperator.validateCall():407
org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall():4900
org.apache.calcite.sql.SqlCall.validate():115
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():899
org.apache.calcite.sql.validate.SqlValidatorImpl.validate():609
com.dremio.exec.planner.sql.SqlConverter.validate():168
com.dremio.exec.planner.sql.DremioSqlToRelConverter.expandView():83
com.dremio.exec.planner.sql.ExtendedToRelContext.expandView():70
com.dremio.exec.planner.logical.ViewTable.toRel():116
com.dremio.exec.catalog.DremioPrepareTable.toRel():89
org.apache.calcite.sql2rel.RelStructuredTypeFlattener.rewriteRel():657
sun.reflect.GeneratedMethodAccessor210.invoke():-1
sun.reflect.DelegatingMethodAccessorImpl.invoke():43
java.lang.reflect.Method.invoke():498
org.apache.calcite.util.ReflectUtil.invokeVisitorInternal():257
org.apache.calcite.util.ReflectUtil.invokeVisitor():214
org.apache.calcite.util.ReflectUtil$1.invokeVisitor():464
org.apache.calcite.sql2rel.RelStructuredTypeFlattener$RewriteRelVisitor.visit():721
org.apache.calcite.rel.SingleRel.childrenAccept():72
org.apache.calcite.rel.RelVisitor.visit():44
org.apache.calcite.sql2rel.RelStructuredTypeFlattener$RewriteRelVisitor.visit():716
org.apache.calcite.sql2rel.RelStructuredTypeFlattener.rewrite():177
com.dremio.exec.planner.sql.DremioSqlToRelConverter.flattenTypes():66
com.dremio.exec.planner.sql.SqlConverter.toConvertibleRelRoot():251
com.dremio.exec.planner.sql.handlers.PrelTransformer.toConvertibleRelRoot():717
com.dremio.exec.planner.sql.handlers.PrelTransformer.convertToRelRootAndJdbc():727
com.dremio.exec.planner.sql.handlers.PrelTransformer.convertToRel():786
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():168
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():162
com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():43
com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():69
com.dremio.exec.work.foreman.AttemptManager.run():291
java.util.concurrent.ThreadPoolExecutor.runWorker():1149
java.util.concurrent.ThreadPoolExecutor$Worker.run():624
java.lang.Thread.run():748
@swarren we actually don’t officially support saving commands as a part of VDS definitions – so I wouldn’t recommend relying on this long-term. In this example, the problem might be caused by the VDS context. You can experiment with this in the UI by opening the SQL editor and clicking on context on the lower right hand side of the editor.
Behaves the same regardless of context. Steps to reproduce:
click “new query”
enter ‘alter table datasource.bucket.tbl refresh metadata’
click “save as”
click “dataset”
click “query” on the new VDS.
You get the error immediately.
In terms of needing this feature, my goal is to make it so I don’t reference PDSs directly in REST API requests. Currently the only way to force a metadata refresh is with “alter table” and I was hoping to embed the command in a VDS so I could invoke that command using the REST API without having to directly reference the PDS. When you invoke it from the “Run” or “Preview” button it works, but invoking it externally does not (through the query button or “select * from refresh_tbl”)
If the ability to save this command as a VDS goes away, what is the suggested way to do what I’m requesting? If I execute “alter table VDS refresh metadata”… will/should that refresh the metadata on PDSs that are used by the VDS?
@swarren thanks for the details and the feedback.
What’s driving your decision to not reference PDSs directly in REST API? Is it convenience, security or something else? We are thinking of a few ways to make it simpler to refresh all relevant PDS metadata for a given VDS without users having to figure it out (similar to your example).
The main driver is that I want a clean “API” for requests sent via the REST API. Currently all requests can come in to published VDSs which then map to PDSs (which I am free to change as long as I don’t change the VDS definitions). For example, I may move some PDSs from S3 to EBS for performance reasons, or vice versa for cost reasons.
I need to make “refresh metadata” requests through the “API” because there is no other way to achieve a metadata refresh “on parquet file added”.