How to load a file from Azure Blob storage to Azure synapse analytics on real time.

Question

We have one IICS pipeline to extract the data from source (Oracle Cloud) and generate the export/file on Azure storage account in every 2 min. Now I want to load the file on real time (Once the export created on azure blob storage) to Azure synapse analytics database table with some basic transformation.

Answer 1

You can use a combination of ADF or Azure Synapse pipelines, along with event-driven triggers. Below are the steps to set up this process:

1. Set Up Azure Blob Storage Event Trigger

First, you need to create an event trigger that responds to new files being uploaded to your Azure Blob Storage.

1. Navigate to Azure Data Factory or Azure Synapse Pipelines:
   • Go to the Azure portal.
   • Open your Azure Data Factory or Azure Synapse workspace.
2. Create an Event-Based Trigger:
   • In the Azure Data Factory or Synapse workspace, go to the Manage tab and select Triggers.
   • Click on + New to create a new trigger.
   • Choose Event as the trigger type.
   • Specify the Blob Storage account and the container where your files are being uploaded.
   • Set the Event Type to BlobCreated.
   • Optionally, you can configure filters to only trigger on specific file types or names.

2. Create a Data Pipeline in Azure Data Factory or Synapse Pipelines

Now, you need to create a pipeline that will be triggered when a new file is detected.

1. Create a New Pipeline:
   • In the Azure Data Factory or Synapse workspace, go to the Author tab and create a new pipeline.
2. Add a Blob Storage Source:
   • Add a Copy Data activity to the pipeline.
   • Configure the source to read the file from the Azure Blob Storage container. You may need to create a linked service that connects to your Blob Storage account.
3. Add a Transformation (Optional):
   • If you need to perform basic transformations, you can use the Mapping Data Flow in the pipeline to apply transformations such as filtering, mapping, or aggregating the data.
   • Alternatively, you can write a custom script using the Data Flow Script or Custom Activity.
4. Configure the Synapse Analytics Sink:
   • Set the destination (sink) to your Azure Synapse Analytics database.
   • Define the schema mapping from the source file to the destination table in Synapse.
5. Connect the Trigger to the Pipeline:
   • Go back to the trigger you created earlier.
   • Associate the event-based trigger with the pipeline you just created.