Azure Datafactory: How to implement nested sql query in transformation data flow

Question

[![enter image description here][1]][1] I have two streams customer and customercontact. I am new to azure data factory. I just want to know which activity in data flow transformation will achieve the below sql query result.

(SELECT * FROM customercontact WHERE customerid IN (SELECT customerid FROM customer) ORDER BY timestamp DESC LIMIT 1)

I can utilize Exist transformation for inner query but I am need some help on how I can fetch the first row after sorting customer contact data.So , basically I am looking for a way to add limit/Top/Offset clause in dataflow.

The query you have will only return the most recent customer's contact.., what you intend to do with the returned contact record (s)? — Anand Sowmithiran
– Anand Sowmithiran, Commented Dec 5, 2021 at 12:42
Yes , it will return most recent customer contact. I have join this other table. I need data flow transformation for above mentioned query. — suraj_123
– suraj_123, Commented Dec 5, 2021 at 13:39

Abhishek Khandave · Accepted Answer · 2021-12-07 14:32:12Z

0

You can achieve transformation for a given query in data flow with different transformation.

For sorting you can use Sort transformation. Here you can select Order Ascending or descending.

For top few records you can use Rank transformation.

For “IN” clause you can use Exists transformation.

Refer - https://learn.microsoft.com/en-us/azure/data-factory/data-flow-rank

Here is my sample data in SQL as Source

I have used Rank transformation.

After rank transformation one more column i.e. RankColumn got added.

Now to select only top 1 record I have used Filter Row Modifier. I used equals(RankColumn,1) expression to select Top 1 record.

Now finally use Sink activity and run pipeline.

answered Dec 7, 2021 at 14:32

Abhishek Khandave

3,2621 gold badge9 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Azure Datafactory: How to implement nested sql query in transformation data flow

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related