Tutorial: Create cross-warehouse queries with the SQL query editor
Applies to: SQL analytics endpoint and Warehouse in Microsoft Fabric
In this tutorial, learn about how you can easily create and execute T-SQL queries with the SQL query editor across multiple warehouse, including joining together data from a SQL analytics endpoint and a Warehouse in Microsoft Fabric.
Add multiple warehouses to the Explorer
Select the
Data Warehouse Tutorial
workspace in the navigation menu.Select the
WideWorldImporters
warehouse item.In the Explorer, select the + Warehouses button.
Select the SQL analytics endpoint of the lakehouse you created using shortcuts previously, named
ShortcutExercise
. Both items are added to the query.Your selected warehouses now show the same Explorer pane.
Execute a cross-warehouse query
In this example, you can see how easily you can run T-SQL queries across the WideWorldImporters
warehouse and ShortcutExercise
SQL analytics endpoint. You can write cross-database queries using three-part naming to reference the database.schema.table
, as in SQL Server.
From the ribbon, select New SQL query.
In the query editor, copy and paste the following T-SQL code.
SELECT Sales.StockItemKey, Sales.Description, SUM(CAST(Sales.Quantity AS int)) AS SoldQuantity, c.Customer FROM [dbo].[fact_sale] AS Sales, [ShortcutExercise].[dbo].[dimension_customer] AS c WHERE Sales.CustomerKey = c.CustomerKey GROUP BY Sales.StockItemKey, Sales.Description, c.Customer;
Select the Run button to execute the query. After the query is completed, you will see the results.
Rename the query for reference later. Right-click on
SQL query 1
in the Explorer and select Rename.Type
Cross-warehouse query
to change the name of the query.Press Enter on the keyboard or select anywhere outside the tab to save the change.
Execute a cross-warehouse cross-workspace query
To query data from Warehouse A residing in another workspace than your Warehouse B, follow these steps:
- Create a lakehouse in the same workspace as your Warehouse B.
- In that lakehouse, create a shortcut pointing to the required databases or tables from Warehouse A.
- Through the previous cross-warehouse sample query, you can now query tables in that lakehouse which are just a shortcut to Warehouse A. For example:
SELECT * FROM [lakehouse].[dbo].[table_shortcuted_from_warehouse_A]
Note
Cross-warehouse cross-workspace querying is currently limited for queries within the same region.
Next step
प्रतिक्रिया
https://aka.ms/ContentUserFeedback.
जल्द आ रहा है: 2024 के दौरान हम सामग्री के लिए फीडबैक तंत्र के रूप में GitHub मुद्दों को चरणबद्ध तरीके से समाप्त कर देंगे और इसे एक नई फीडबैक प्रणाली से बदल देंगे. अधिक जानकारी के लिए, देखें:के लिए प्रतिक्रिया सबमिट करें और देखें