question

KrishnaMuraliShyamGNMSEXP3XC-2760 avatar image
1 Vote"
KrishnaMuraliShyamGNMSEXP3XC-2760 asked KrishnaMuraliShyamGNMSEXP3XC-2760 commented

SqlDataReference usage fails from yaml based AzureML pipeline

I am trying to deploy a DataTransferStep with azureml pipeline

source -> SQL database
sink -> Azure Blob

using yaml pipelines

additonal comments:


This is built similar to pipeline class as in
https://docs.microsoft.com/en-us/python/api/azureml-pipeline-core/azureml.pipeline.core.pipeline.pipeline?view=azure-ml-py
Note: I have deployed successfully yaml other pipelines that uses PythonScriptStep successfully previously


my pipeline fails as below
162851-s1.png



The above one is built using following yaml snippet163053-snippet.png

it fails mentioning "The SQL Source payload is invalid: Cannot specify 'sqlReaderQuery', 'storedProcedureParameters' at the same time". whereas only sqlReaderQuery is only provided by me in yaml. storedProcedureParameters takes a default value of None as per https://docs.microsoft.com/en-us/python/api/azureml-core/azureml.data.sql_data_reference.sqldatareference?view=azure-ml-py . I have debugged inside azureml code and verified storedProcedureParameters is None too.

162814-image.png

in executionlogs.txt its also found that an additional stored procedure parameters: 0 () is printed.
Copy source: SQL server database: xxxxxxxx, servername: xxxxxxxxxxxxx, serverUri: xxxxxxx-dev.database.windows.netAuthentication: AuthencationType=SqlAuthentication, table: dummy, query: SELECT TOP (100) * FROM dml.annotations, stored procedure parameters: 0 ()


162837-sqlq.png


I tried numerous combinations to avoid this default value of 0 () coming for stored procedure parameters since it is the summary of the issue as per logs and nothing worked.

additional analysis:


I tried to implement same as direct code in azureml notebook ( here yaml is not present ) i see that the data transfer from sql to blob works perfectly fine in notebook.


so question is when written via yaml why is it not working and creating a error by adding a default value to stored procedure parameters of SqlDataReference, how to fix it ???*

Also tried in various software versions:
azureml-core 1.26 and 1.36


azure-sql-databaseazure-machine-learning
s1.png (15.2 KiB)
snippet.png (32.2 KiB)
image.png (59.9 KiB)
sqlq.png (37.2 KiB)
snippet.png (45.3 KiB)
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@KrishnaMuraliShyamGNMSEXP3XC-2760 I would recommend to report the error along with a screen shot from the azure ml portal using the smiley icon on the top right corner. This would ensure the service team look at the logs of your run and advise what can be done to correct it if possible.

Based on some previous issues of similar nature I think the query parameter is probably causing the issue. One user previously tried to query the table by giving table name and it seems to work for them by using the SDK.

 adls_data_ref = DataReference(
     datastore=adls_datastore,
     data_reference_name="adls_test_data",
     path_on_datastore="path_to_file")
    
    
    
    
 sql_data_ref = SqlDataReference(
     datastore=sql_datastore,
     data_reference_name="sql_data_1",
     sql_table='table_name', 
     sql_query=None, 
     sql_stored_procedure=None, 
     sql_stored_procedure_params=None)
0 Votes 0 ·

Thanks For the info, i will try to get the feedback as you suggested.

however considering the suggestion of using sql_table parameter i modified it to these combinations and they did not work.

data_references:
DataReference:
datastore: workspaceblobstore
path_on_datastore: azureml/f1dfa450-5941-4929-903f-e1dbe2401345
SqlDataReference:
datastore: mysqldatastore2
data_reference_name: sql_query_data_ref
sql_query: SELECT * FROM
sql_table: dml.annotations

but however the error still persists.

i also tried to add None value as suggested
using null or None

 SqlDataReference:
    datastore: mysqldatastore2
    data_reference_name: sqlrefname
    sql_query: SELECT * FROM
    sql_table: dml.annotations
    sql_stored_procedure: null 
    sql_stored_procedure_params: null

and this would give build errors
163049-noneerror.png




Thanks once again.

0 Votes 0 ·
noneerror.png (21.5 KiB)
romungi-MSFT avatar image romungi-MSFT KrishnaMuraliShyamGNMSEXP3XC-2760 ·

Ok. Not sure if the sql_query parameter might be an issue. Have you tried to use only the following combination with others as None?

      datastore=sql_datastore,
      data_reference_name="sql_data_1",
      sql_table='table_name', 
0 Votes 0 ·
Show more comments

0 Answers