topic Re: How to execute .sql file in volume in Data Engineering

How to execute .sql file in volume

lauraxyz — Thu, 21 Nov 2024 19:50:11 GMT

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?

Re: How to execute .sql file in volume

JAHNAVI — Fri, 22 Nov 2024 06:11:29 GMT

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)

Re: How to execute .sql file in volume

lauraxyz — Fri, 22 Nov 2024 18:33:21 GMT

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?

Re: How to execute .sql file in volume

JAHNAVI — Sat, 23 Nov 2024 07:27:10 GMT

@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

Re: How to execute .sql file in volume

lauraxyz — Wed, 27 Nov 2024 17:26:28 GMT

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

Re: How to execute .sql file in volume

JAHNAVI — Mon, 02 Dec 2024 16:27:33 GMT

Hi @lauraxyz , Good Day!

We can run the below command to un .ipynb files

%python

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

Re: How to execute .sql file in volume

lauraxyz — Mon, 02 Dec 2024 17:12:07 GMT

Thanks gonna give it a try!

Re: How to execute .sql file in volume

lauraxyz — Tue, 03 Dec 2024 20:33:24 GMT

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

Re: How to execute .sql file in volume

lauraxyz — Wed, 04 Dec 2024 01:01:55 GMT

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.