Git Integration with Databricks Query Files and Azure DevOps

[dbengineer516]

I’ve been trying to develop a solution for our team to be able to have Git integration between Databricks and Azure DevOps. However, the “query” file type/workspace item on Databricks can’t be committed and pushed to a Git repo, only regular file types and notebooks. My thought was to automate a process/create a Python script that would be able to go into a workspace, along with its subfolders, and change over query files into .sql files so that they can be pushed to a Git repo. I’ve tried accomplishing this by using get /api/2.0/workspace/list, but this doesn’t report query object types, so then I tried using get /api/2.0/preview/sql/queries to first list all query files and then specify a parent folder in a function to pass along to convert query files in a specified parent folder. However, after trying this on my home directory first, the API call would only return back certain query files and walk through some subfolders and not others. I was wondering if there is a better and more efficient solution for this and how to go about accomplishing this so that we can easily commit query files to a Git repo/convert query files into .sql files so that they can be committed.

Thank you!

[Yeshwanth · Accepted solution]

@dbengineer516 Good day!

As per the Databricks documentation, only certain Databricks asset types are supported by Git folders. These include Files, Notebooks, and Folders. Databricks asset types that are currently not supported in Git folders include DBSQL queries, Alerts, and Dashboards (including Lakeview dashboards). Although you can move existing unsupported assets into a Git folder, you cannot commit changes to these assets back to the repo, nor can you create new unsupported assets in a Git folder. 

Documentation: https://docs.databricks.com/en/repos/manage-assets.html

Please let us know if you have any further questions or concerns.

Best regards,

Yesh

[dbengineer516]

Thank you for the clarification Yesh! Since DBSQL queries aren't currently supported, could I convert DBSQL query files into normal .sql files to commit into a Git repo? Is there a way to accomplish this using an API call, such as /api/2.0/preview/sql/queries, to first gather a list of DBSQL query files and automatically convert DBSQL query files in a workspace to .sql files? Manually, I could go through each workspace and copy the contents in each DBSQL query file into a new .sql file to be able to commit, but it would create a lot of overhead. Please let me know if there is a possibility to automate this process.

Thank you!

[Yeshwanth · Accepted solution]

@dbengineer516 I'm sorry, but based on the usecase, there isn't a documented method to automatically convert DBSQL query files into standard .sql files for Git commits using an API call like /api/2.0/preview/sql/queries or any other automated approach within Databricks.

[dbengineer516]

Thank you for your help Yesh!