Querying data with SQL

1. Querying data with SQL

We know where a lakehouse keeps its tables. Now let's query them. In this video, you'll use SQL in Fabric to select data from a lakehouse table and narrow it to the rows you actually need.

2. What is SQL used for?

Think about a large online shop. We'd never inspect every product one by one. We search, filter, and sort until the catalog shows what we're after. A Fabric table works the same way. SQL is the language we use to search, filter, and sort it, and it works the same in a lakehouse and a warehouse.

3. SQL analytics endpoint structure

To run SQL against a lakehouse, we use its SQL analytics endpoint, which Fabric creates automatically. Think of the lakehouse as the storage and its Tables area as an organized catalog. The SQL analytics endpoint is then the search interface over that catalog.

4. Accessing the SQL analytics endpoint

We open it from the "Analyze data with" menu in the lakehouse UI. The endpoint is created along with the lakehouse and may take a moment to appear.

5. Accessing the SQL query interface

From there, open the New SQL query dropdown on the ribbon and choose New SQL query. That opens a blank editor where we type a query and run it against the lakehouse tables. The same dropdown also offers New visual query, which we'll come back to later in the chapter.

6. SELECT

Queries that read data start with SELECT. Our first query is SELECT star FROM Sales. Sales is a table in the lakehouse Tables area, and the star tells Fabric to return every column in it. This is like opening the complete Sales catalog. It's usually the first query we run against an unfamiliar table, because it reveals the table's full shape.

7. Selecting specific columns

An online catalog may show dozens of product details, but perhaps we only need the date, product name, and revenue. Naming columns after SELECT does the same for a lakehouse table. Fabric returns only those columns, and the result is much easier to read.

8. WHERE

The WHERE clause works like an online-shop filter. Instead of every region, we choose North, and only matching records come back. Column selection and WHERE combine freely. We name the columns we want, add a WHERE clause to limit the rows, and Fabric returns a table narrowed in both directions.

9. Results of a SQL query

Results come back in the Fabric query editor as a new table, separate from the one we queried. A SELECT with no conditions returns every row in the table. If we add a WHERE clause, the result contains only the rows that match our condition. If we name specific columns, the result contains only those columns. That result is only displayed in the editor. It is not saved as a new item in the lakehouse, and the table we queried remains unchanged.

10. Practicing with a sample lakehouse

A sample lakehouse is a quick way to get data to experiment with. It's created from the Data Engineering workload and comes pre-populated with a ready-made dataset, so there's nothing to ingest first. During creation, we can choose which dataset it contains, such as the public holidays dataset.

11. Let's practice!

Now let's practice querying a lakehouse with SQL.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.