| title | description | ms.reviewer | ms.author | author | ms.topic | ms.custom | ms.date | ||
|---|---|---|---|---|---|---|---|---|---|
Create OneLake shortcuts in a KQL database |
Learn how to create a OneLake shortcut in a KQL database to query data from internal and external sources. |
tzgitlin |
yaschust |
YaelSchuster |
how-to |
|
04/03/2024 |
OneLake is a single, unified, logical data lake for [!INCLUDE product-name] to store lakehouses, warehouses, KQL databases, and other items. Shortcuts are embedded references within OneLake that point to other files' store locations without moving the original data. The embedded reference makes it appear as though the files and folders are stored locally but in reality; they exist in another storage location. Shortcuts can be updated or removed from your items, but these changes don't affect the original data and its source.
In this article, you learn how to create a OneLake shortcut in a KQL database that points to internal Fabric or external sources. This kind of shortcut is later accessed for query in KQL querysets by using the external_table() function. Shortcuts created in a KQL database can't be renamed, and only one shortcut can be created at a time.
In addition to creating shortcuts from a KQL database, shortcuts can also be created from other Fabric items. These shortcuts also point to data stored in internal Fabric or external sources, but have different limitations and are accessed differently. For more information, see OneLake shortcuts.
Note
Use OneLake shortcuts when you want to infrequently run queries on historical data without partitioning or indexing the data. If you want to run queries frequently and accelerate performance, import the data directly into your KQL database.
Select the tab that corresponds to the shortcut you'd like to create:
[!INCLUDE onelake-shortcut-prerequisites]
Note
The following flow shows how to create a shortcut that points to data in a Lakehouse in Fabric. Similarly, you can create shortcuts that point to Data Warehouses or other KQL databases.
-
Browse to an existing KQL database.
-
Select New > OneLake shortcut.
:::image type="content" source="media/onelake-shortcuts/onelake-shortcut/home-tab.png" alt-text="Screenshot of the Home tab showing the dropdown of the New button. The option titled OneLake shortcut is highlighted.":::
-
Under Internal sources, select Microsoft OneLake.
:::image type="content" source="media/onelake-shortcuts/onelake-shortcut/new-shortcut.png" alt-text="Screenshot of the New shortcut window showing the two methods for creating a shortcut. The option titled OneLake is highlighted." lightbox="media/onelake-shortcuts/onelake-shortcut/new-shortcut-expanded.png":::
-
Select the data source you want to connect to, and then select Next.
:::image type="content" source="media/onelake-shortcuts/onelake-shortcut/data-source.png" alt-text="Screenshot of the Select a data source type window showing the available data sources to use with the shortcut. The Next button is highlighted." lightbox="media/onelake-shortcuts/onelake-shortcut/data-source.png":::
-
Expand Tables, and select a specific table to connect to.
:::image type="content" source="media/onelake-shortcuts/onelake-shortcut/create-shortcut.png" alt-text="Screenshot of the New shortcut window showing the data in the LakeHouse. The subfolder titled StrmSC and the Create button are highlighted." lightbox="media/onelake-shortcuts/onelake-shortcut/create-shortcut.png":::
-
Select Create.
Note
You can only connect to one subfolder or table per shortcut. To connect to more data, repeat these steps and create additional shortcuts.
[!INCLUDE adlsgen2-prerequisites]
-
Browse to an existing KQL database.
-
Select New > OneLake shortcut.
:::image type="content" source="media/onelake-shortcuts/onelake-shortcut/home-tab.png" alt-text="Screenshot of the Home tab showing the dropdown of the New button. The option titled OneLake shortcut is highlighted.":::
[!INCLUDE adls-gen2-shortcut]
[!INCLUDE amazons3-prerequisites]
-
Browse to an existing KQL database.
-
Select New > OneLake shortcut.
:::image type="content" source="media/onelake-shortcuts/onelake-shortcut/home-tab.png" alt-text="Screenshot of the Home tab showing the dropdown of the New button. The option titled OneLake shortcut is highlighted.":::
[!INCLUDE amazon-s3-shortcut]
The database refreshes automatically. The shortcut appears under Shortcuts in the Explorer pane.
:::image type="content" source="media/onelake-shortcuts/adls-gen2-shortcut/data-tree.png" alt-text="Screenshot of the Explorer pane showing the new shortcut.":::
The OneLake shortcut has been created. You can now query this data.
To query data from the OneLake shortcut, use the external_table() function.
- On the rightmost side of your database, select Explore your data. The window opens with a few example queries you can run to get an initial look at your data.
- Replace the table name placeholder with
external_table('Shortcut name'). - Select Run or press Shift + Enter to run a selected query.
:::image type="content" source="media/onelake-shortcuts/amazon-s3-shortcut/query-shortcut.png" alt-text="Screenshot of the Explore your data window showing the results of an example query." lightbox="media/onelake-shortcuts/amazon-s3-shortcut/query-shortcut.png":::
Delta primitive data types are mapped to Eventhouse scalar data types using the following rules. For more information on Eventhouse data types, see Scalar data types.
| Delta Type | Eventhouse Scalar Data Type |
|---|---|
string |
string |
long |
long |
integer |
int |
short |
int |
byte |
real |
float |
real |
double |
real |
decimal |
decimal |
boolean |
bool |
binary |
string |
date |
datetime |
timestamp_ntz (without time zone) |
datetime |
struct |
dynamic |
array |
dynamic |
map |
dynamic |