Azure Machine Learning Export Data to specific schema in Azure SQL Database

Question

Azure Machine Learning Export Data to specific schema in Azure SQL Database

Angus McKay 0

I'm setting up a pipeline to export data from Azure Machine Learning Studio to an Azure SQL Database. However, I can't see anywhere to specify the schema in the database that I would like to write to. I've seen examples online which all seem to write to the default 'dbo' schema. Is there no way to specify a specific schema to write to?

Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-04-01T02:48:02.8833333+00:00
Hi @Angus Mckay

Greetings!

when we export data from Azure Machine Learning Studio to an Azure SQL Database, it typically writes to the default dbo schema. Unfortunately, the built-in export functionality in Azure Machine Learning Studio does not provide a direct way to specify a different schema (other than dbo) through the UI or pipeline setup.

However, there are some workarounds to achieve this:

**SQL Query in Azure SQL Database**

After exporting the data to the default dbo schema, you can write a custom SQL script to move the data to another schema within your Azure SQL Database.

You can do this by running a INSERT INTO or SELECT INTO SQL query to transfer the data from the default schema to the target schema.
NSERT INTO [TargetSchema].[YourTable] SELECT * FROM [dbo].[YourTable];
Use Azure Data Factory (ADF) for More Control

If you need more control over your data movement and transformations, consider using Azure Data Factory (ADF). It allows you to specify the schema when writing data to an Azure SQL Database.

In ADF, you can define the schema for each table and customize the data flow as per your requirements. I hope, this response will address your query and helped you to overcome on your challenges.
Angus McKay 0 Reputation points

2025-04-01T08:04:21.87+00:00

Hi @Mallaiah Sangi

Thanks very much for your response. Good to know I wasn't missing something obvious! Seems like a pretty obvious flaw in the ML Studio design, surely it would have been very straightforward to allow a direct write to a selected schema.

Is there a way I can execute the SQL INSERT statement directly from the Azure ML Studio? Or does this need to be coordinated to run in a separate resource?

Thanks

Angus
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-04-01T11:07:25.56+00:00
Hi @Angus Mckay

Thank you for reaching out for your issues.

You can integrate your machine learning workflows with SQL databases by using external resources and services. Here’s how you can coordinate the execution of an SQL INSERT statement:

Using Azure ML to Execute SQL Commands via External Resources:

Azure ML with Azure SQL Database: You can connect to an Azure SQL Database by writing Python or R scripts within Azure ML Studio. These scripts can execute SQL commands (like INSERT, UPDATE, etc.) via the pyodbc, SQLAlchemy, or other database connectors in Python

After exporting the data into Azure SQL Database: you can change the default schema by connecting to the database server Step-by-Step Guide Connect to Your Azure SQL Database:
- Use SQL Server Management Studio (SSMS), Azure Data Studio, or any other SQL client tool to connect to your Azure SQL Database. **Identify the Object to be Moved**: - Determine the object (table, view, stored procedure, etc.) that you want to move to a different schema. **Use the `ALTER SCHEMA` Statement**: - Execute the following SQL command to change the schema of the object:

ALTER

I hope, this response will address your query and helped you to overcome on your challenges.

Please reach out to me if you need any assistance.
Angus McKay 0 Reputation points

2025-04-01T15:06:37.6333333+00:00

Thanks again @Mallaiah Sangi

I was having problems with authentication errors when using pyodbc and sqlalchemy to connect to the SQL Database. I was only able to connect to it to import data to ML Studio using the Workspace, Datastore and Dataset modules from azureml.core

That would definitely be the neatest solution though so I'll look into that again and see if I can solve it.

Thanks

Angus
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-04-02T13:37:54.7766667+00:00

Hi @Angus Mckay

Please let me know if your issues have been resolved. If you need further assistance, we are happy to help.
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-04-03T09:07:49.71+00:00

Hi @Angus Mckay

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Angus McKay 0 Reputation points

2025-04-03T09:17:18.13+00:00

Hi @Mallaiah Sangi

Thanks for checking in on this. I'm focusing just now on getting a pipeline deployed which will dump the output predictions to blob storage which can then be picked up and moved to the SQL database as needed. I know the different parts of this process work which is why I want to get this in place before going back to testing out other methods that I'm less sure of but would ultimately be a better outcome if they work out (in particular the direct database writes and edits from ML Studio).

I'll make sure to update the thread once I've had the chance to look into that.

Thanks

Angus
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-04-07T09:22:04.2566667+00:00

Hi @Angus Mckay

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Angus McKay 0 Reputation points

2025-04-07T12:54:06.07+00:00

Hi @Mallaiah Sangi

No solution fully in operation yet. We've been having issues configuring the environment as we trained a model in a notebook using the default environment that existed on a compute resource, but then using a conda environment.yaml to replicate the environment when launching as part of a prediction pipeline kept failing. We really had to scale back the environment to the bare essentials to get it working.

Something that would be significantly better is if the exact compute resource and environment that was used to train a model could just be used when it comes to scheduling batch prediction jobs, instead of launching inside a docker container and having to recreate the environment each time (it would save problems like I was having with the environment build, and it would also save time for each prediction run as the environment wouldn't need to be built each time).

Anyway, we're almost there with it now. Our process will just be to dump the output to blob storage and then have a separate process pick it up and move it to SQL server. I can update with specific details once it's done.

Thanks again

Angus
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-04-09T11:16:34.2533333+00:00

Hi @Angus Mckay

Please let me know if your issues have been resolved. If you need further assistance, we are happy to help.
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-04-10T11:24:47.7933333+00:00

Hi @Angus Mckay

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-04-11T08:39:38.58+00:00

Hi @Angus Mckay

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Your answer

Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-04-01T02:48:02.8833333+00:00

Hi @Angus Mckay

Greetings!

when we export data from Azure Machine Learning Studio to an Azure SQL Database, it typically writes to the default dbo schema. Unfortunately, the built-in export functionality in Azure Machine Learning Studio does not provide a direct way to specify a different schema (other than dbo) through the UI or pipeline setup.

However, there are some workarounds to achieve this:

**SQL Query in Azure SQL Database**

After exporting the data to the default dbo schema, you can write a custom SQL script to move the data to another schema within your Azure SQL Database.

You can do this by running a INSERT INTO or SELECT INTO SQL query to transfer the data from the default schema to the target schema.
NSERT INTO [TargetSchema].[YourTable] SELECT * FROM [dbo].[YourTable];
Use Azure Data Factory (ADF) for More Control

If you need more control over your data movement and transformations, consider using Azure Data Factory (ADF). It allows you to specify the schema when writing data to an Azure SQL Database.

In ADF, you can define the schema for each table and customize the data flow as per your requirements. I hope, this response will address your query and helped you to overcome on your challenges.
Angus McKay 0 Reputation points

2025-04-01T08:04:21.87+00:00

Hi @Mallaiah Sangi

Thanks very much for your response. Good to know I wasn't missing something obvious! Seems like a pretty obvious flaw in the ML Studio design, surely it would have been very straightforward to allow a direct write to a selected schema.

Is there a way I can execute the SQL INSERT statement directly from the Azure ML Studio? Or does this need to be coordinated to run in a separate resource?

Thanks

Angus
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-04-01T11:07:25.56+00:00

Hi @Angus Mckay

Thank you for reaching out for your issues.

You can integrate your machine learning workflows with SQL databases by using external resources and services. Here’s how you can coordinate the execution of an SQL INSERT statement:

Using Azure ML to Execute SQL Commands via External Resources:

Azure ML with Azure SQL Database: You can connect to an Azure SQL Database by writing Python or R scripts within Azure ML Studio. These scripts can execute SQL commands (like INSERT, UPDATE, etc.) via the pyodbc, SQLAlchemy, or other database connectors in Python

After exporting the data into Azure SQL Database: you can change the default schema by connecting to the database server Step-by-Step Guide Connect to Your Azure SQL Database:
- Use SQL Server Management Studio (SSMS), Azure Data Studio, or any other SQL client tool to connect to your Azure SQL Database. **Identify the Object to be Moved**: - Determine the object (table, view, stored procedure, etc.) that you want to move to a different schema. **Use the `ALTER SCHEMA` Statement**: - Execute the following SQL command to change the schema of the object:

ALTER

I hope, this response will address your query and helped you to overcome on your challenges.

Please reach out to me if you need any assistance.
Angus McKay 0 Reputation points

2025-04-01T15:06:37.6333333+00:00

Thanks again @Mallaiah Sangi

I was having problems with authentication errors when using pyodbc and sqlalchemy to connect to the SQL Database. I was only able to connect to it to import data to ML Studio using the Workspace, Datastore and Dataset modules from azureml.core

That would definitely be the neatest solution though so I'll look into that again and see if I can solve it.

Thanks

Angus
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-04-02T13:37:54.7766667+00:00

Hi @Angus Mckay

Please let me know if your issues have been resolved. If you need further assistance, we are happy to help.
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-04-03T09:07:49.71+00:00

Hi @Angus Mckay

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Angus McKay 0 Reputation points

2025-04-03T09:17:18.13+00:00

Hi @Mallaiah Sangi

Thanks for checking in on this. I'm focusing just now on getting a pipeline deployed which will dump the output predictions to blob storage which can then be picked up and moved to the SQL database as needed. I know the different parts of this process work which is why I want to get this in place before going back to testing out other methods that I'm less sure of but would ultimately be a better outcome if they work out (in particular the direct database writes and edits from ML Studio).

I'll make sure to update the thread once I've had the chance to look into that.

Thanks

Angus
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-04-07T09:22:04.2566667+00:00

Hi @Angus Mckay

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Angus McKay 0 Reputation points

2025-04-07T12:54:06.07+00:00

Hi @Mallaiah Sangi

No solution fully in operation yet. We've been having issues configuring the environment as we trained a model in a notebook using the default environment that existed on a compute resource, but then using a conda environment.yaml to replicate the environment when launching as part of a prediction pipeline kept failing. We really had to scale back the environment to the bare essentials to get it working.

Something that would be significantly better is if the exact compute resource and environment that was used to train a model could just be used when it comes to scheduling batch prediction jobs, instead of launching inside a docker container and having to recreate the environment each time (it would save problems like I was having with the environment build, and it would also save time for each prediction run as the environment wouldn't need to be built each time).

Anyway, we're almost there with it now. Our process will just be to dump the output to blob storage and then have a separate process pick it up and move it to SQL server. I can update with specific details once it's done.

Thanks again

Angus
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-04-09T11:16:34.2533333+00:00

Hi @Angus Mckay

Please let me know if your issues have been resolved. If you need further assistance, we are happy to help.
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-04-10T11:24:47.7933333+00:00

Hi @Angus Mckay

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-04-11T08:39:38.58+00:00

Hi @Angus Mckay

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

Azure Machine Learning Export Data to specific schema in Azure SQL Database

Your answer