Want to store .sql files to azure blob using adf

Question

Want to store .sql files to azure blob using adf

Amar Agnihotri 926

Hi ,
I am using third party tool which exports .sql scripts daily . I want to store those .sql scripts in azure blob (if possible without using azure mysql db instance) using adf in the form of tables and then i will use those tables for analytical purpose. I don't know how to achieve that. I don't want to use any snaypse analytics or azure databricks. Kindly suggest me the steps to achieve this . Is this possible to do without creating azure mysql db instance . FYI we are not using any on premise my sql server.

Thanks

Accepted answer

1 additional answer

Your answer

Answer 1

Nandan Hegde 36,146 MVP Volunteer Moderator

Hey,
Can you please clarify what are the SQL scripts like (DDL definitions or insert scripts etc)

Note: Assuming the 3rd part vendor uploads the SQL scripts in a location which can act as a source in ADF, you can use copy activity to copy the SQL files from the source to blob.
You would need to use Binary dataset

Amar Agnihotri 926 Reputation points

2022-04-14T06:35:47.633+00:00

@Nandan Hegde Thanks for the reply. Actually the script is containing DDL as well as DML statements.

It first check the existing table and if table exists it drops the table and then creates new table and then insert data to table. I can use the copy activity to store those scripts to blob but my concern is how to use these scripts for analysis because i want to use the data in Power bi. Does the scripts stored in the blob as binary will behave as a table or do i need Mysql db instance to store that script .

I am attaching the snap of scripts of a single file in continuation. It will help you understand --
Nandan Hegde 36,146 Reputation points MVP Volunteer Moderator

2022-04-20T11:31:50.36+00:00

Hey @Amar Agnihotri
Sorry to have missed out on this thread.

Unfortunately the scripts in blob wont behave as a table and you would have to explicitly execute that query into a database and consume into powerbi from there.
Amar Agnihotri 926 Reputation points

2022-04-20T13:41:23.963+00:00

Thanks @Nandan Hegde

I have understood this and good to tell you that i have used Mysql db on my machine to get those script loaded and then created an adf pipeline to dump those tables to blob.

Mysql db has 310 tables. The only thing that i am struggling with is the lookup table to load only 50 tables out of 310. I don't want to create any lookup table in MySql db. I want to create a lookup .csv file and want to store it in blob. The lookup activity will look that file for the desired filename. The video link that you shared is showing the process of creating a lookup table in database. Can you please share something which could help me create the lookup .csv file in blob and then tread from that file. I am not able to get that process now. It would be great if you could help me out on that.
Nandan Hegde 36,146 Reputation points MVP Volunteer Moderator

2022-04-20T13:51:26.99+00:00

Hey,
You can upload a file in blob and then leverage Lookup activity :
learn.microsoft.com/en-us/azure/data-factory/control-flow-lookup-activity

to read the data from that file and use it in for loop activity.

Note: Below is the restriction
Amar Agnihotri 926 Reputation points

2022-04-20T14:04:55.883+00:00

Thanks @Nandan Hegde
I am having tables more than 26000 rows and these are total 30-35 tables. As per the restriction shown i believe that storing look up file in blob and then use lookup activity on top of that won't work. So can you suggest any other way to achieve that
Amar Agnihotri 926 Reputation points

2022-04-20T14:08:08.973+00:00

@Nandan Hegde ,

I want to use the lookup file only for the table name and schema name and then grabbing those table name only those tables will be copied from Mysql to blob.
Nandan Hegde 36,146 Reputation points MVP Volunteer Moderator

2022-04-20T14:11:04.14+00:00

Hey.
@Amar Agnihotri , sorry in case if I confused you.

Let me reiterate it over here:

You need a lookup file that would contain the list of table that you need to copy, so here in this case it is 50 table names in the file (meaning 50 rows in file)

Then you can leverage lookup activity to read that file.

So for your current case the lookup activity would work but just wanted to highlight that there are restrictions which might come for you in future if the number of table list or file size explicitly increases.
Amar Agnihotri 926 Reputation points

2022-04-20T14:15:12.883+00:00

Thanks @Nandan Hegde Now i got the point. I will try to achieve this using the documentation link you provided. Thanks once again. I want to accept this as a solution . How can i do that because i am not seeing any such option in ahead of your answer
Nandan Hegde 36,146 Reputation points MVP Volunteer Moderator

2022-04-20T14:19:57.107+00:00

Hey,
Glad it helped :)

You need to go to the top of this thread to upvote and mark it as answer :)
Amar Agnihotri 926 Reputation points

2022-04-21T06:31:42.263+00:00

@Nandan Hegde Thanks a lot. i did it

Answer 2

Normally SQL scripts are just text files with a .sql suffix as a type indicator. You can open these files with anything that will read a text file. If I understand your problem correctly, then storing the file as a blob should not be a problem.
There are two questions here, and they relate to the types of data you are trying to get into Power BI

Do you want to parse the sql scripts and report on the table names, field names ?.
Are you interested in the data tables that the scripts create and want to know counts of tables, records, fields ?

Its not clear from your description which of these you are trying to solve.

Share via

Want to store .sql files to azure blob using adf

1 additional answer

Your answer