question

sakuraime avatar image
0 Votes"
sakuraime asked sakuraime commented

Data factory COPY activity to Azure synapse

I would like to use the following features to copy data from parquet to Synapse :

129872-image.png


Question _1 : It does not support wilcard file ,what if I have multiple parquet files in a folder for a table ?? Now each copy activity can only have one parquet file
129881-image.png






Quesion_2 : Using COPY , the command is auto generated in the following :

COPY INTO [dbo].[FACT_sales_from_df_parquet] ([Region] 1,[Country] 2,[Item_Type] 3,[Sales_Channel] 4,[Order_Priority] 5,[Order_Date] 6,[Order_ID] 7,[Ship_Date] 8,[Units_Sold] 9,[Unit_Price] 10,[Unit_Cost] 11,[Total_Revenue] 12,[Total_Cost] 13,[Total_Profit] 14) FROM 'https://****dfs.core.windows.net:443/ingest/outputtable/day1/sql_dbo_ext_sales_csv_Order_ID_406666631_703333262_1.parquet' WITH (IDENTITY_INSERT='OFF',CREDENTIAL=(IDENTITY='Storage Account Key',SECRET=''*),FILE_TYPE='PARQUET',COMPRESSION='Snappy') OPTION (LABEL='ADF Activity ID: 3e8b03ad-bd8c-4412-9276-eac79cd90441')

I believe Storage account key can't be used from Synapse to blob storage account , unless the blob storage account has no firewall restriction (allow all network).... unfortunately , most of the customer has their blob storage account enable firewall..... Any idea on how to fix it ?

azure-data-factory
image.png (10.3 KiB)
image.png (21.2 KiB)
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.

1 Answer

HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered sakuraime commented

Hello @sakuraime,
Thanks for the ask and using the Microsoft Q&A platform .
Please enable the staging option and it should work .

130336-image.png

I am sharing my implementation below HTH .

130316-synapsecopy.gif




Please do let me know how it goes .
Thanks
Himanshu
Please do consider clicking on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members



image.png (54.0 KiB)
synapsecopy.gif (1.5 MiB)
· 3
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.

thanks . but why I still need a staging ? I am copy from a parquet files already.

0 Votes 0 ·

Hello @sakuraime ,

The main reason of using the blob staging to to increase the load performance when you have multiple files ( siinve you are using wildcard ) .
https://docs.microsoft.com/en-us/azure/data-factory/copy-activity-performance-features#staged-copy

Thanks
Himanshu

0 Votes 0 ·

it's a bit confusing , because the parquet is already on the blob , why I still need to copy another set as staging for COPY ?


if I run tsql 'COPY into table ' directly from Azure synapse , it won't need me to have another staging ....


I am suppose the COPY command in ADF is the same as 'COPY into table' .......(where COPY into table also support wilcard ).


0 Votes 0 ·