topic Re: Ingesting on premise data with notebooks in Pipelines

Ingesting on premise data with notebooks

Anonymous — Thu, 29 Feb 2024 09:31:16 GMT

Hi, I have an on premise database with a running PowerBI gateway and this is how we connected to these sources previously. Now, the requierement is that we would recreate our ETL process in Microsoft Fabric using notebooks and Spark, but currently from what I read it seems like there is no way to do this due to notebooks note being compatible with the on premise gateway.

If this is the case, are there any other alternatives that I have, other than using Dataflows which are very slow and have performance issues. Ideally I would want to use a work around with Notebooks, that can retrieve this data.
Thanks in advance.

Re: Ingesting on premise data with notebooks

Anonymous — Thu, 29 Feb 2024 14:53:36 GMT

Hi Anonymous
Thanks for using Fabric Community.
Currently, Microsoft Fabric notebooks do not directly support connecting to on-premise databases through the Power BI Gateway. Fabric Spark doesn’t do the integration with PBI gateway.

As a workaround you can create a Shortcut in Fabric Lakehouse for your external data. This way you can query the data using Notebooks. For more information regarding Shortcuts please refer to this link:
https://learn.microsoft.com/en-us/fabric/onelake/create-onelake-shortcut

Hope this helps. Please let me know if you have any further queries.

Re: Ingesting on premise data with notebooks

Anonymous — Thu, 29 Feb 2024 17:00:48 GMT

How would shortcuts help me in this case? It can externally connect only to S3 and ADLS. The data is stored on local databases.

Re: Ingesting on premise data with notebooks

Anonymous — Thu, 29 Feb 2024 17:02:43 GMT

Hi Anonymous
Which database are you referring here? Can you please specify?

Re: Ingesting on premise data with notebooks

Anonymous — Thu, 29 Feb 2024 17:03:57 GMT

Multiple local PG instances

Re: Ingesting on premise data with notebooks

Anonymous — Thu, 29 Feb 2024 17:32:37 GMT

Hi Anonymous
You should use a Data Pipeline to copy the data to lakehouse. Fabric data pipelines will be added to the On Prem data gateway in the next few months.
What's new and planned for Data Factory in Microsoft Fabric - Microsoft Fabric | Microsoft Learn

If you need something sooner, you should use Azure Data Factory Pipelines with the Self-Hosted Integration Runtime. For more information please refer to these links:
Copy on-premises data using the Azure Copy Data tool - Azure Data Factory | Microsoft Learn
How to ingest data into Fabric using the Azure Data Factory Copy activity - Microsoft Fabric | Microsoft Learn

Hope this helps. Please let me know if you have any further queries.

Re: Ingesting on premise data with notebooks

Anonymous — Thu, 29 Feb 2024 17:58:46 GMT

I know about the roadmap, but it's still couple month, we need it now.
In regards to the COPY activity, I am pretty sure you can't use it to build incremental ETL (to append), it re-copies all the data every time you run it, so this is not suitable for us.

Re: Ingesting on premise data with notebooks

Anonymous — Thu, 29 Feb 2024 18:04:23 GMT

Hi Anonymous
There is no other work around in Microsoft Fabric for ingesting on-premise data. I have shared all the possible options.

Re: Ingesting on premise data with notebooks

Anonymous — Fri, 01 Mar 2024 18:15:26 GMT

Hi Anonymous

We haven’t heard from you on the last response and was just checking back to see if you got some insights regarding your query. Otherwise, will respond back with the more details and we will try to help.
Thanks

Re: Ingesting on premise data with notebooks

Anonymous — Mon, 04 Mar 2024 06:57:15 GMT

Hi @egons11

Re: Ingesting on premise data with notebooks

Anonymous — Tue, 05 Mar 2024 14:54:22 GMT

There are no insights to gain, the functionality doesn't exist, hopefully it's "yet" that it doesn't and will be added soon.

Re: Ingesting on premise data with notebooks

MattiaColuccia — Thu, 09 May 2024 15:17:17 GMT

Hello,

I'm coming back to this topic to understand if the integration of On-prem gateways in Fabric Spark is on the roadmap for future implementation.

It would be really nice to be able to script ingestion of On-Prem sources (SQL server in my case) through notebooks. Right now I have a complex metadata driven pipeline with multiple steps which are really hard to maintain and could be all replaced by a notebook, if this feature gets implemented.

Thanks 🙂

Re: Ingesting on premise data with notebooks

tmjones2 — Sun, 09 Jun 2024 12:54:43 GMT

So we have to manually create pipeline activities for each table we want? There's really no way to do it in code?

Re: Ingesting on premise data with notebooks

joakimfenno — Thu, 20 Jun 2024 14:24:52 GMT

I agree
I want to do my data ingestion using notebooks (and not data factory)
Is it still nog possbile to reference an on premise data gateway from a notebook?

Re: Ingesting on premise data with notebooks

JUNPEI — Thu, 08 Aug 2024 21:56:47 GMT

So as of today, there is no way to ingest on premise data using notebook,

The only way is to use data factory and copy activty to ingest on-premise data to a lake house. Am i right?

Re: Ingesting on premise data with notebooks

smeetsh — Tue, 20 Aug 2024 01:36:44 GMT

We ingest data from an on prem MSSQL server by having created a view on the on-prem server that gets us the information we need.

That view is than called from a pipeline, using a gateway, which allows us to enter the data into a "lakehouse raw" table and next we do the ETL from there. We use the sql endpoint for the ETL, but I assume you could also use a notebook to do it.

Re: Ingesting on premise data with notebooks

PeterDaniels — Wed, 21 Aug 2024 17:01:17 GMT

Catching up to this thread. I can use an on-prem DG connection to a Folder in a pipeline, but I can't use a dynamic connection name. When I choose dynamic connection name, it forces me to use one of three types of connections: Lakehouse, Warehouse, and KQL DB. I'd like to see the other connection types added.

Re: Ingesting on premise data with notebooks

smeetsh — Wed, 21 Aug 2024 21:24:40 GMT

Create views on your on prem server, joining the tables you need, if there is a relation between then of course.

Next use a copy data action in a pipeline to call that view and ingest it into a lakehouse. Thats how we do it for multiple tables at my work.

I'm not realy sure where the advantage would be to do it with a notebook, since you would (depending on your database structure) have to either call multiple tables in a notebook, use multiple notebooks, or create a join between tables in a notebook.

Cheers
Hans

Re: Ingesting on premise data with notebooks

smeetsh — Wed, 21 Aug 2024 22:57:43 GMT

you should be able to use and on-prem gateway to build a connection to connect to your local sql server. Why would you want a dynamic connection name?

The connector we use for this can talk to any DB and its tables, we have given it access to on the on prem sql server.

Re: Ingesting on premise data with notebooks

PeterDaniels — Wed, 21 Aug 2024 23:50:45 GMT

Asking me "why" is not helpful, but the answer is that I want to use different connections in different environments (dev, test, prod)