SQL Stored Procedures - Notebook to always run the CREATE query

[DM0341]

I have a stored procedure that is saved as a query file. I can run it and the proc is created. However I want to take this one step further. I want my notebook to run the query file called sp_Remit.sql so if there is any changes to the proc between the runs the Proc will update in gold schema first and then once its updated I can CALL the Proc. I don't want to have to manually create / replace the proc each time via a deployment but instead have the notebook run the query. I was not able to get this to work but I found a work around but don't want to use. I change from a query to a file and executed the file but this removed my ability to run segments of code in the file like I can in a query. 

[mynameiskevin · Accepted solution]

Something like this?

import os

query_name = "test_query.sql"
query_path = os.path.abspath(query_name)

# Read query contents
with open(query_path, "r") as f:
    query_str = f.read()

# Run it
spark.sql(query_str)

You can read the script from the sql file and assuming the file is in the same directory as the notebook you are running. 

[DM0341]

Thank you. I did find this about an hour after I posted. Thank you Kevin