topic Re: Can not access SQL files in the Shared workspace in Data Engineering

Can not access SQL files in the Shared workspace

nyehia — Wed, 26 Apr 2023 14:35:36 GMT

Hey,

we have an issue in that we can access the SQL files whenever the notebook is in the repo path but whenever the CICD pipeline imports the repo notebooks and SQL files to the shared workspace, we can list the SQL files but can not read them.

we changed the file permissions and still getting the same error.

the error is OSError: [Errno 95] Operation not supported

Thanks

Re: Can not access SQL files in the Shared workspace

karthik_p — Thu, 27 Apr 2023 12:36:40 GMT

@Nermin Yehia looks there was a small confusion, thought sql files are data files. Ignore my previous comment. Did you get a chance to check below article which shows need to end to config for ADF to Databricks

https://learn.microsoft.com/en-us/azure/data-factory/solution-template-databricks-notebook

what ever path you add in ADF level pipeline config that will be picked, can you please make sure how config has been setted

learn.microsoft.com

Run a Databricks Notebook with the activity - Azure Data Factory

Learn how you can use the Databricks Notebook Activity in an Azure data factory to run a Databricks notebook against the databricks jobs cluster.

Re: Can not access SQL files in the Shared workspace

nyehia — Thu, 27 Apr 2023 12:50:40 GMT

@karthik p Thank you so much.

there is no issue in ADF running the notebook.

the main issue is that we can access the SQL files from Workspace/Repos but can not open the file whenever it is imported to the /Workspace/Shared. seems like a bug in the Workspace but we don't know how to fix it.

Thanks

Re: Can not access SQL files in the Shared workspace

karthik_p — Thu, 27 Apr 2023 14:04:14 GMT

@Nermin Yehia can you post command that you are using to read data. also some where in pipeline are you passing /workspace/shared path to place your files. if so please change that to your folder in /users and try to read, if files are being placed in shared that might be security concern as per governance if that need to be visible to you that should be in your users. if that should be accessed by every one that can be in shared

Re: Can not access SQL files in the Shared workspace

nyehia — Thu, 27 Apr 2023 14:08:11 GMT

here are the commands

Re: Can not access SQL files in the Shared workspace

karthik_p — Thu, 27 Apr 2023 14:28:07 GMT

@Nermin Yehia can you use this command to copy you files from shared

dbutils.fs.cp ("file:/workspace/shared/.....", "dbfs:/tmp/sqls/*")

and to read try to use

display(dbutils.fs.ls("file:/tmp/sqls/*"))

Re: Can not access SQL files in the Shared workspace

nyehia — Thu, 27 Apr 2023 14:35:49 GMT

@karthik p I tested the following. uploaded the sql files to /User and it worked fine. so a notebook in the /Shared folder can access the sql in the /User folder but can not access in the ?Shared folder.

i used cp just to test. but in the original code, i'm using dbutils.fs.ls("file:/ the sql path")

Re: Can not access SQL files in the Shared workspace

nyehia — Thu, 27 Apr 2023 14:40:19 GMT

@karthik p posted a file.

Re: Can not access SQL files in the Shared workspace

nyehia — Thu, 27 Apr 2023 14:43:57 GMT

@karthik p

Re: Can not access SQL files in the Shared workspace

karthik_p — Thu, 27 Apr 2023 15:36:12 GMT

@Nermin Yehia Try this

with open("/Workspace/Shared/test.sql") as queryFile:

queryText = queryFile.read()

display(queryText)

Re: Can not access SQL files in the Shared workspace

nyehia — Thu, 27 Apr 2023 15:42:41 GMT

@karthik p this is the way i read the files and causing the error

Re: Can not access SQL files in the Shared workspace

karthik_p — Thu, 27 Apr 2023 15:46:21 GMT

@Nermin Yehia looks .sql is missing for u file (problem_list_history_weekly is .sql file right)

Re: Can not access SQL files in the Shared workspace

nyehia — Thu, 27 Apr 2023 15:53:27 GMT

@karthik p yes. it is showing up in the /User(read is working fine) but not in the /Shared(read operation cause the error). we use databricks workspace import_dir command to copy notebooks and sql files from /User to /Shared using CICD.

when i tried to find the reason for the extension disappearance I found that in the doc

https://docs.databricks.com/dev-tools/cli/workspace-cli.html

Re: Can not access SQL files in the Shared workspace

karthik_p — Thu, 27 Apr 2023 16:26:28 GMT

@Nermin Yehia that's great, if you add that extension and re-execute are we able to read sql data

Re: Can not access SQL files in the Shared workspace

nyehia — Thu, 27 Apr 2023 17:18:22 GMT

@karthik p I tried to move the files manually and i got that warning

Re: Can not access SQL files in the Shared workspace

karthik_p — Thu, 27 Apr 2023 17:29:09 GMT

@Nermin Yehia yes, as you are moving files to different location manually , just update as can manage permissions in target and that should take care of everything

Re: Can not access SQL files in the Shared workspace

nyehia — Thu, 27 Apr 2023 18:03:01 GMT

@karthik p how can i copy sql files with extensions?

the workspace cli documentation says extensions are excluded

Re: Can not access SQL files in the Shared workspace

nyehia — Thu, 27 Apr 2023 18:20:57 GMT

I moved the files using import_dir, we need to automate the process.

when i list using workspace ls command i get this

Re: Can not access SQL files in the Shared workspace

karthik_p — Thu, 27 Apr 2023 18:24:20 GMT

@Nermin Yehia looks it's a bug, can you try to upgrade databricks utility to new version if you have not and see, that may help as you do not want to opt manual export or import

Re: Can not access SQL files in the Shared workspace

karthik_p — Thu, 27 Apr 2023 18:28:17 GMT

@Nermin Yehia yes that seems to be correct behavior, as you are seeing type of your files, but can you also upgrade cli to new version and see, else that is current behavior