@Sylvia VB​ :

Here are some suggestions and considerations to help you navigate through the issues:

1. Accessing the MySQL datadumps:

• As you mentioned, obtaining direct view access to the source MySQL database would be the ideal solution. This would allow you to perform incremental updates instead of relying on weekly datadumps.
  • If you are unable to get direct access, you can continue with the approach of ingesting the datadumps from Azure Blob Storage.

1. Accessing Azure Blob Storage:

• Since you have upgraded to Gen 2 Blob storage and resolved the authentication issue, you can now access the files in the storage account using Databricks.
  • The code snippet you provided, listing and copying files, should work fine for accessing the modified files.

1. Processing the MySQL datadumps:

• It seems you are decompressing the SQL dump file and planning to execute the SQL statements using SQL Server tools like sqlcmd.
  • However, executing the SQL statements directly in Spark may be a more efficient approach, as it eliminates the need for an intermediate SQL Server setup.
  • You can read the SQL dump file in Spark and execute the statements using the JDBC driver for MySQL. This approach allows you to leverage the distributed processing capabilities of Spark and avoid the overhead of setting up and managing temporary SQL servers.