Forum Discussion

ToddChitt's avatar
ToddChitt
Icon for Super User rankSuper User
2 years ago

Save PySpark datafram to Fabric Warehouse via T-SQL type MERGE operation

I am building out a solution that will need to process lots of JSON files. I initially set it up to save the JSON file to a known location and known file name in the Lakehouse, then use a Dataflow Ge...
data engineering
data warehouse
ToddChitt's avatar
ToddChitt
Icon for Super User rankSuper User
2 years ago

Hello Element115 and thanks for the quick response.

Not sure I understand what you are suggesting. I wish to AVOID the Lakehouse staging tables as they get truncated and reloaded for each JSON file to be processed. That means that the files must be processed sequentially. I want to be able to run them in parallel, like dozens of files at the same time (and each file will have its own Client Id additional data element so I can't simply just take all the files in a folder and treat them as one.

 

I was hoping for a way to connect directly to the Warehouse, but I don't see that option for a Notebook.

  • Element115's avatar
    Element115
    Icon for Memorable Member rankMemorable Member
    2 years ago

    If the problem is to extract data from JSON files, and you are proficient in SQL and sp, then why not do it inside your DB (SQL Server has all the functionality for that), and create the tables or views from that in your DB?  Then use a pipeline Copy data activity to get the data from these tables or views into a Fabric WH.

     

    If it's a matter of figuring out where is best to park the JSON files (not in a LH), then I guess on some filesystem or even a DB. I did persist a bunch of JSON from a web REST API once--SQL Server has all the functionality needed for that. 

     

    Let's say you persist your JSON in SQL Server. Then it's a piece of cake to just use a pipeline to access the DB, retrieve the JSON with a SQL query (or in the above scenario, retrieve the data from tables or views), and continue processing with the pipeline. The pipeline let's you process stuff concurrently.  Don't know if you can read from a FS from a PL though.

     

    Or perhaps you could use Databricks, also available within a pipeline. Here are the activities available in a pipeline:

     

     

    Note sure why you would necessarily need a Notebook.  When you say MERGE, do you mean like APPEND?  Because if so, you can use Copy data from an on-prem SQL Server in append mode.  But you need to implement Incremental refresh manually (just finished doing that in fact for a test project).