Re: How to execute .sql file in volume

lauraxyz · ‎11-21-2024

I have giant queries (SELECT.. FROM) that i store in .sql files. I want to put those files in the Volume, and run the queries from a workflow task.

I can load the file content into a 'text' format string, then run the query. My question is, is there another option, where I don't need to load the file content, but directly execute the .sql file and store the results in a Dataframe?

JAHNAVI · ‎11-21-2024

Hi @lauraxyz , We can load the SQL file using dbutils.fs from volumes and then we can create a dataframe using spark.sql()

Example:

sql_query = dbutils.fs.head("/Volumes/jahnavi/datasets/data/test.sql")

result_df = spark.sql(sql_query)

display(result_df)

Jahnavi N

lauraxyz · ‎11-22-2024

Thanks Jahnavi! That's what i'm doing now, was wondering if there's a way that I don't need to parse the content of the file but directly execute it. Another example is if i have a python notebook in Volume, and I want to directly execute this notebook without parsing the content, would I be able to do that?

JAHNAVI · ‎11-22-2024

@lauraxyz For SQL there is no direct way to run the file without parsing it. However, for Python, we can use %run to run the file from volumes.

Example:

%python

%run /Volumes/jahnavi/datasets/data/test.py

Jahnavi N

lauraxyz · ‎11-27-2024

Thank you @JAHNAVI
How about Python Notebooks? can we directly run .ipynb files?

JAHNAVI · ‎12-02-2024

Hi @lauraxyz , Good Day!

We can run the below command to un .ipynb files

%python

%run /Volumes/jahnavi/datasets/data/test12.ipynb

Jahnavi N

lauraxyz · ‎12-02-2024

Thanks gonna give it a try!

lauraxyz · ‎12-03-2024

.ipynb cannot be applied due to ModuleNotFoundError: No module named 'nbformat'.

.py command seemed passed but the insertion was never executed, therefore it's a silent fail.

lauraxyz · ‎12-03-2024

issue resolved:

for .py, i was using spark, and I have to explicitly create the spark session so that it can be run properly and insert data.