Forum Discussion
Skilled SharerUse Dataflow Gen2 for multiple tables ingest dinamically
Skilled SharerHi Element115 . yes, for months_to_realod it would be ok. In fact, I already have a warehouse table with the list of tables I need to ingest and with a column months_to_reload (table_name, months_to_reload). The problem I still have is that the tables I need to ingest I want them to be dynamic. For now, I have to define then in the dataflow one by one, I want a Foreach activity to iterate through my warehouse with tables list and ingest all the tables I have in the warehouse table.
Memorable Memberamaaiia How about this approach:
CREATE OR ALTER VIEW vTABLE_NAMES
AS
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
;
If you run this SQL script from your SQL analytics endpoint, it will create a view in your LH. Then from you DF, all you have to do is create a query that uses this view as its source. The view return a column of all the tables in the dbo schema of the LH as a table. Now your M code can reference this table and Bob's your uncle.
v-cboorla-msftMicrosoft Employee
Hi amaaiia
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others.
Otherwise, will respond back with the more details and we will try to help.
Thanks.- amaaiia
Hi Element115
So imagine my view has 10 rows (10 tables to ingest from my SQL Server). Once my query (let's say Q1) gets the 10 rows, how can I tell DF to create a new query from each row of the result of Q1? So each new query (QN) gets data from my SQL Server and saves it in a Lakehouse table in append mode?
- Element115
amaaiia Before I answer, let me clarify something...
1__do you want to perform an incremental refresh on the tables?
2__are these tables always going to be the same? in other words, once you have created your 10 tables, it will
alsosorry--always be these 10 tables receiving data or, in future, would you have to create 1 or more new tables?- amaaiia
Hi Element115
1. Yes
2. No, more tables can be added
Just to sum up, I have a source_tables table in a warehouse with all the necessary information about my tables, as follows:
Then in my Pipeline, firstly I ingest all tables in one DFg2 (have to add them manually one by one, and whenever there is a new table to ingets, I have to update de DFg2 to add it in a new query), an then I iterate over each row from source_tables to ingest data and then do some other operations (notebooks and other activities) to each table one by one. It's like I have to synchronise both DFg2 and source_tables table to have the same tables inside. What happens is that DFg2 is not inside my ForEach ctivity because I can't pass the source table name, the destination info (lakehouse, table_name, conneciton parameters...), etc by parameter, so I only have the rest of the activities inside ForEach (not a good practise I think, because DFg2 and source_tables are working separately)
I really would like to automate tables ingest, so if I added a new table on my source_tables table, the Pipeline would automatically load it, and DFg2 could check the tables list from source_tables so the queries to ingest data would be created automatically.