topic Querying lakehouse files from warehouse in Pipelines

Querying lakehouse files from warehouse

joakimfenno — Wed, 10 Jul 2024 13:53:33 GMT

I want to create views in my fabric warehouse that are sources from lakehouse files (parquet/json)

I want to do soemthing similar to this but not sure exactly how to write query

select *

from 'https://onelake....URL';

WITH (FILE_TYPE = 'PARQUET');

Re: Querying lakehouse files from warehouse

Anonymous — Thu, 11 Jul 2024 03:59:02 GMT

Hi joakimfenno

To create a view from a lakehouse file (Parquet/JSON) in the Fabric repository, you can follow these steps:

Confirm the file location. Make sure you have the URL for the Parquet or JSON file. For example, https://onelake.... URL

Connect to the Fabric warehouse. Connect to the Fabric repository using your database client or SQL tool.

Write SQL queries. Write SQL queries based on the file type to create the view. For example,

CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;

Query the SQL analytics endpoint or Warehouse - Microsoft Fabric | Microsoft Learn

Create reports - Microsoft Fabric | Microsoft Learn

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Querying lakehouse files from warehouse

joakimfenno — Thu, 11 Jul 2024 05:38:57 GMT

thank you but those link does not provide any information about how to query lakehouse files from the SQL endpoint

Re: Querying lakehouse files from warehouse

Anonymous — Thu, 11 Jul 2024 06:30:48 GMT

Hi joakimfenno

I have misunderstood your question. Please allow me to answer your question again.

The document states:

https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-overview#lakehouse-sql-analytics-endpoint

What is the SQL analytics endpoint for a lakehouse? - Microsoft Fabric | Microsoft Learn

Saving data in the Lakehouse using capabilities such as Load to Tables or methods described in Options to get data into the Fabric Lakehouse, all data is saved in Delta format.

Lakehouse and Delta tables - Microsoft Fabric | Microsoft Learn

Write the SQL query as follows:

CREATE VIEW view_name AS SELECT * FROM tablename

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Querying lakehouse files from warehouse

joakimfenno — Thu, 11 Jul 2024 07:51:17 GMT

thanks!
It might be that I have to load the files to delta tables before processing it to the next layer

could external tables be an option?

Re: Querying lakehouse files from warehouse

Anonymous — Thu, 11 Jul 2024 08:11:24 GMT

Hi joakimfenno

I think you can try using an external table to load Parquet data into Lakehouse.

You can use the Spark API in your notebook to read Parquet files and load them into Lakehouse. For example:

df = spark.read.parquet("location to read from") # Keep it if you want to save dataframe as Parquet files to Files section of the default lakehouse df.write.mode("overwrite").format("parquet").save("Files/" + parquet_table_name)

You can view the link below for more details:

Load data into your lakehouse with a notebook - Microsoft Fabric | Microsoft Learn

Hope this helps you.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Querying lakehouse files from warehouse

Anonymous — Tue, 16 Jul 2024 08:22:54 GMT

Hi joakimfenno

Can you tell me if your problem is solved? If yes, please accept it as solution.

Regards,

Nono Chen