Lesson 32 – Using SQL analytics endpoint with Lakehouse

In the dynamic field of data management, Microsoft Fabrics introduces a powerful tool to streamline your analytical workflows—the SQL Analytics Endpoint within the Lakehouse framework.

This new solution makes it easy to use SQL for analysing data stored in delta tables, providing a smooth and efficient experience for exploring and analysing data.

The SQL analytics endpoint with Lakehouse offers the following features

• Query data in Lakehouse delta tables using T-SQL language and TDS protocol.
• Save functions, generate views, and apply SQL security effortlessly with the SQL analytics endpoint.
• Operates in a secure read-only mode over Lakehouse delta tables, ensuring data safety.
• For data modification, easily switch to Lakehouse mode and leverage the strength of Apache Spark.
• Analyse data stored in external storage accounts by creating shortcuts referencing folders in Azure Data Lake or Amazon S3.

How to access SQL analytics endpoint in Microsoft Fabric?

When you create a Lakehouse, it automatically establishes a SQL analytics endpoint, forming a connection to the storage of delta tables within the Lakehouse. Once you’ve created a delta table within the Lakehouse, it becomes immediately accessible for querying through the SQL analytics endpoint, providing a swift and seamless experience for data exploration.

Follow the steps to access SQL analytics endpoint

• Launch https://app.fabric.microsoft.com//.
• To create a lakehouse quickly ,Click on “Create” then choose “Lakehouse” A window will pop up, just like before, asking you to enter the name you want for the lakehouse you’re creating.

• A prompt window will appear, type the desired name for the lakehouse you want to create and click “Create”.

• After creating the lakehouse, you’ll be directed to the Lakehouse Editor page, where you can begin loading and working with data.
• Once a Lakehouse is created, it automatically sets up a SQL analytics endpoint, forming a connection to the storage of delta tables within the Lakehouse.

• At the top right corner of the Lakehouse editor page, click on the Lakehouse. It will display two options: “Lakehouse” and “SQL analytics endpoint,” allowing you to seamlessly switch between the two modes.
• You can use the SQL query editor or the Visual query editor in the Microsoft Fabric portal to run queries interactively.

• Functions and views can be saved, and SQL object-level security can be configured.
• To make changes to data in Lakehouse delta tables, switch to lakehouse mode and utilize Apache Spark.
• Make external delta tables visible to the SQL analytics endpoint by using shortcuts in the Table space.

SQL Analytics Endpoint Security

Ensuring data security is a priority. With the SQL analytics endpoint, you can establish object-level security rules for accessing data. It’s important to note that these security measures are specifically designed for accessing data through the SQL analytics endpoint. To ensure that your data stays secure and inaccessible through other methods, it’s crucial to configure workspace roles and permissions.