Error when trying to Create a STREAMING TABLE using Databricks SQL

[giuseppe_esq]

Hi all,

I am currently using the Databricks Free Edition, and trying to create a streaming table, using the following SQL script:

%sql

CREATE OR REFRESH STREAMING TABLE sql_csv_autoloader

SCHEDULE EVERY 1 WEEK

AS

SELECT *

FROM STREAM read_files(

  '/Volumes/workspace/sql_using_auto_loader/autoloader_source',

  format => 'CSV',

  sep => ',',

  header => true

);

However, when I execute the script, I get the following error message:

UnsupportedOperationException: Public DBFS root is disabled. Access is denied on path: /local_disk0/tmp/autoloader_schemas_DLTAnalysisID-5771

Does anyone have any workarounds for this please?

Thanks

Giuseppe

[WayneRevenite · Accepted solution]

Hi Giuseppe, I've tried to recreate this and am unable to. Was your volume created as managed or external?

My successful steps:

1. Created a new Volume in workspace.default called "example_volume"
2. Created a new folder in that volume called "example_folder" and uploaded a CSV to the folder
3. Ran the following in a query window, using the Serverless Starter Warehouse: 

CREATE OR REFRESH STREAMING TABLE sql_csv_autoloader
SCHEDULE EVERY 1 WEEK
AS
SELECT *
FROM STREAM read_files(
  '/Volumes/workspace/default/example_volume/example_folder/',
  format => 'CSV',
  sep => ',',
  header => true
);

That successed with sql_csv_autoloaded table created under workspace.default ("The operation was successfully executed.") with the data loaded.

[giuseppe_esq]

Hi,

Thanks for your response.  To confirm, I created my volumes as Managed.  

If I need to used External volumes to execute this script, will I need to create an external storage location (for S3 storage) in Amazon?  If so, is this free?

Kinds regards

Giuseppe

[giuseppe_esq]

Sorry, did you also manage to run this script using the Databricks Free Edition?

Thanks again

Giuseppe

[WayneRevenite]

Yes - I was able to do it no problem on Free, with the exact steps above, using the default supplied compute.

[giuseppe_esq]

Hi again,

Thanks for your response.

I was trying to execute the %SQL script (hence the error), in a workspace notebook, however, you were able to execute the script in SQL Editor, that worked.

Kind regards

Giuseppe

[AdrianK]

I have exactly same behavior: it doesn't work in notebook (no error message, the success message instead), it does work in sql editor.

Apparently streaming tables work only with serverless warehouses.