How to upload(write) data(dataframe) to azure SQL datastore from azure machine learning(azureML) using SDK

Question

From the documentation I could find ways to read data from Azure SQL database registered as datastore in azureML,but not ways to upload or write output data to azure SQL database from azureML.Can anyone please guide me on the same? Also can SQL datastore be used as output for the batch inference step

Answer 1

Thanks for reaching out. You need to register your storage as a datastore. Then write dataframe to a local file and upload to datastore as shown below (refer to this post as well):

from azureml.core import Workspace, Dataset  
  
subscription_id = 'id'  
resource_group = 'resource group'  
workspace_name = 'workspace name'  
  
ws = Workspace(subscription_id, resource_group, workspace_name)  
  
#write dataframe to a local file (e.g. csv, parquet)  
local_path = 'data/prepared.csv'  
df.to_csv(local_path)  
  
# get the datastore to upload prepared data  
datastore = ws.get_default_datastore()  
  
# upload the local file from src_dir to the target_path in datastore  
datastore.upload(src_dir='data', target_path='data')  

If you continue to experience errors, please share your code so we can investigate further, thanks.

Answer 2

@Jothy Babu Thanks, Please follow the below repo and docker-instructions to use tools such as pyodbc and SQLAlchemy for writeback within an experiment, ParallelRunStep, etc without the need for multi-step pipelines.

Here is the repo for write to sql.

20383-docker-instructions.pdf

Answer 3

Thanks, Please follow the below pointers for Reading and updating Azure SQL Database from Azure ML pipeline.
Doc: https://learn.microsoft.com/en-us/python/api/azureml-pipeline-steps/azureml.pipeline.steps.data_transfer_step.datatransferstep?view=azure-ml-py
Example: https://github.com/Azure/MachineLearningNotebooks/blob/master/how-to-use-azureml/machine-learning-pipelines/intro-to-pipelines/aml-pipelines-data-transfer.ipynb

Note: When copying data to an Azure SQL Database, data will be appended to an existing table. We also expect the source file to have a header row and the names should exactly match with column names in destination table.