search

Azure / azure-sdk-for-python

This repository is for active development of the Azure SDK for Python. For consumers of the SDK we recommend visiting our public developer docs at https://learn.microsoft.com/python/azure/ or our versioned developer docs at https://azure.github.io/azure-sdk-for-python.
MIT License
4.61k stars 2.83k forks source link

How to set query param instead of table for ADF copy activity? #2161

Closed devbrian closed 5 years ago

devbrian commented 6 years ago

here is my code, I can't seem to find a way to switch the source to use a query instead of table format for accessing the data.

act_name = 'copyBlobtoBlob'
blob_source = AzureMySqlSource()
blob_sink = AzureDataLakeStoreSink()
dsin_ref = DatasetReference("AzureSql_input")
dsOut_ref = DatasetReference("Adls_output")
copy_activity = CopyActivity(act_name,
                             inputs=[dsin_ref],
                             outputs=[dsOut_ref],
                             source=blob_source,
                             sink=blob_sink)

If I set the query param

blob_source = AzureMySqlSource("select * from SalesLT.Customer WHERE Title = 'Mr.'")

This does nothing and still returns the entire table

lmazuel commented 6 years ago

@hvermis to the rescue :)

hvermis commented 6 years ago

try this: blob_source = AzureMySqlSource(query = "select * from SalesLT.Customer WHERE Title = 'Mr.'")

devbrian commented 6 years ago

@hvermis same results, however I figured this out, for anyone that's interested:

AzureMySqlSource's query has no affect from what I could tell, you have to declare it as a SqlSource (no matter what the source actually is) and then pass the sql as a parameter for it to work. Not sure why the query parameter is an option for AzureMySqlSource, perhaps it's deprecated? Not sure.

linda33wj commented 6 years ago

@devbrian to confirm, are you copying data from Azure SQL DB or Azure Database for MySQL? SqlSource is for the former and AzureMySqlSource is for the latter - you can see the property name for query is different, see documentation at https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-sql-database#copy-activity-properties and https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-database-for-mysql#copy-activity-properties.

More info: please check each connector topic on which object type to use, in majority case it varies per connector.