Forum Discussion
Use Dataflow Gen2 for multiple tables ingest dinamically
Sorry, maybe I didn't ask the question correctly. Yes, I know that with DFg2 I can do ingests via gateway, in fact, that's how I'm doing it now and I have everything configured to work.
My question was, since you tell me to do the ingest with notebooks because otherwise I can't parameterise this process, as the number of tables is variable, can I do the ingest using gateway by notebooks? I think the answer is no, and in that case I understand that the solution you gave me of doing the whole process from the ingestion with notebooks does not work for me.
Am I wrong?
Oh! I see what you mean. I just asked Bing Copilot and it says it's possible from a PySpark notebook. But, as one commenter says on the LinkedIn page below, this would mean opening your DB port 1433 to incoming traffic, and if it's a production DB, it is doubtful your sysadmin will agree due to the security risk. Here' what Copilot said:
Yes, it is possible to connect to an on-premises database from a Fabric PySpark Notebook1. You would typically use JDBC to establish the connection and PySpark to read the data2. However, you should ensure that the necessary ports are open for remote access1 and securely manage your connection details1. Please note that the exact steps may vary based on your specific environment and database.
Here’s the Python import statement for PySpark and JDBC:
from pyspark.sql import SparkSession
This will import the SparkSession module, which is the entry point to any functionality in Spark. You can then use it to create a SparkSession and connect to your database using JDBC. Please replace 'jdbc:your_database' and 'your_table' with your actual database JDBC URL and table name.
spark = SparkSession.builder.getOrCreate() df = spark.read.format('jdbc').options( url='jdbc:your_database', dbtable='your_table', ).load()
Remember to also include your driver, user, and password options as needed. Please consult your database documentation for the exact details.
If the on-premises database is behind a firewall, you would need to ensure that the necessary ports are open for remote access1. This is essential for data transfer and communication from external sources1.
In some cases, you might need to set up a secure or DMZ subnet within your network, which is separate from existing private and public subnets2. You would then create an additional route in the custom route table to 0.0.0.0/0 and set the Next hop type to “Virtual Appliance”2.
Remember, the exact steps may vary based on your specific environment and firewall settings. Always ensure you’re following best practices for security and compliance in your organization.