Search code examples
azureazure-synapse-analyticsazure-synapse-pipeline

Auto Detect Data Types with Azure Synapse Analytics Copy Data (Import)

I have an Azure Data Lake Storage Gen2 account linked to an Azure Synapse Analytics (ASA) Workspace. The storage account has TIOBE Test data that looks like this:

PartitionKey,Year,Month,Language,Rating
2023-04,2023,4,Python,14.51
2023-04,2023,4,C,14.41
2023-04,2023,4,Java,13.23
...

ASA Workspaces have a built in Copy Data Tool. When I preview my TIOBE Data via that tool, everything looks fine: Preview Screenshot for TIOBE Data

Unfortunately, when I get to the Destination / Configuration step, the Column Mappings are all hard wired to String and there is no way for me to change them: ASA Copy Tool Auto Mappings

I asked someone at Microsoft about this, and I understood him to say it's easier to create the destination (sink) table with correct data types first and then use the Copy Data tool to import the data.

Should I continue to create my sink tables before using the ASA Copy Data Tool or is there an easier way to specify correct data types when importing data?

NOTE: I just asked this on SuperUser and it was closed as off topic, so I deleted that question and am now asking here.

Solution

  • As per my Understanding Yes, creating the destination table with the correct data types before is a good approach. The reason is because that when you use the Azure Synapse Analytics Copy Data Tool, it automatically infers the data types based on the first few rows of the source data.

    For example I have created a dataset in ADLS and using the copy activity I am moving the data. Source: enter image description here Source Preview:

    enter image description here

    Sink: enter image description here

    Creating the SQL Table in the Sink: enter image description here Mapping after creating the table at the sink: enter image description here Output: enter image description here