none
I want to copy a specific row from my Azure Table Storage to my SQL Database Table using Azure Data Factory

    Question

  • Hello, I have created a Pipeline that can copy a table from my Azure Table Storage to my SQL Database Table with Azure Data Factory UI.

    I have been able to filter the columns I want with Schema and mapping however I want to be able to chose to only copy a certain row from my Azure Table Storage table to my SQL Database. I tried to do this by going to source in my Copy Dataflow and selecting Use Query and I specified my Query as 

    SELECT RowKey, Timestamp, Data
    FROM botdata
    WHERE RowKey = 'userData';


    However I get this error 

    Activity CopyTabletoSQL failed: Failure happened on 'Source' side. ErrorCode=FailedStorageOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A storage operation failed with the following error 'The remote server returned an error: (400) Bad Request.'.,Source=,''Type=Microsoft.WindowsAzure.Storage.StorageException,Message=The remote server returned an error: (400) Bad Request.,Source=Microsoft.WindowsAzure.Storage,StorageExtendedMessage=Syntax error at position 8 in 'SELECT * FROM botdata
    WHERE RowKey='userData';'.
    RequestId:fb1bfa16-d002-00d5-5ac2-19550f000000
    Time:2018-07-12T09:24:09.9202510Z,,''Type=System.Net.WebException,Message=The remote server returned an error: (400) Bad Request.,Source=System,'
    Which leads me to believe I have misunderstood something. How would I go about specifying to only copy a certain row from my table using ADF?
    Thursday, July 12, 2018 12:27 PM

Answers

  • Hi, 

    Azure Table uses a different syntax than SQL.

    In your case, it should be "RowKey eq 'userData'". By the way, I suppose you should also filter by PartitionKey. Otherwise it would be very inefficient.

    • Marked as answer by bergbergen Thursday, July 12, 2018 1:24 PM
    Thursday, July 12, 2018 12:47 PM