How to CREATE VIEW using Azure Data Factory pipeline activities

Question

Hello everybody!

I am quite new to Azure Data Factory, and I was wondering if there is a way to run SQL queries on my pipeline. In other words, which activity or activities should I use in my pipeline?

What me and my team aim to do is to run a CREATE VIEW query right after requesting data from a REST API and saving it in a folder located in our Data Lake (Gen2), all in the same pipeline.
The request part of the pipeline is already covered, and we need to complete the rest.

In addition, we would also want our pipeline to previously check if a view with the same name already exists, and to create the aforementioned view if it did not exist.

Thank you in advance, and please do not hesitate to ask me questions in order to clarify what we need to achieve.
Kind regards,
Mateo

Answer 1

Hi,

I was wondering if there is a way to run SQL queries on my pipeline.

Option 1: use a "LookUp" activity

It's a type of a trick, since officially Lookup activity meant to retrieve a dataset, but in fact using LookUp you can run any query directly including UPDATE or CREATE for example, as long as you close it with something like SELECT. You can have an UPDATE and use "SELECT 0 as id".

https://learn.microsoft.com/azure/data-factory/control-flow-lookup-activity?WT.mc_id=DP-MVP-5001699

Option 2 (more common probably): use a stored procedure

Simply use the Stored Procedure action

https://www.sqlshack.com/executing-stored-procedures-from-data-pipelines-in-azure-data-factory/

https://learn.microsoft.com/azure/data-factory/transform-data-using-stored-procedure?WT.mc_id=DP-MVP-5001699

Option 3: Script activity

You can also use Script activity to run your script

https://learn.microsoft.com/azure/data-factory/transform-data-using-script?WT.mc_id=DP-MVP-5001699