Create OneLake shortcuts in a KQL database

OneLake is a single, unified, logical data lake for Microsoft Fabric 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:

OneLake shortcut

Prerequisites

• A workspace with a Microsoft Fabric-enabled capacity
• A KQL database with editing permissions
• A Lakehouse

To access the data in your KQL database in other Microsoft Fabric experiences, see One logical copy.

Create shortcut

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.

1. Browse to an existing KQL database.

2. Select New > OneLake shortcut.

   

Select a source

1. Under Internal sources, select Microsoft OneLake.

   

2. Select the data source you want to connect to, and then select Next.

   

3. Expand Tables, and select a specific table to connect to.

   

4. 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.

Azure Data Lake Storage Gen2

Prerequisites

• A workspace with a Microsoft Fabric-enabled capacity
• A KQL database with editing permissions
• An Azure Data Lake Storage Gen2 account.

Create shortcut

1. Browse to an existing KQL database.

2. Select New > OneLake shortcut.

   

Select a source

1. Under External sources, select Azure Data Lake Storage Gen2.

2. Enter the Connection settings according to the following table:

   

   Field	Description	Value
   URL	The connection string for your delta container.	https://StorageAccountName.dfs.core.windows.net
   Connection	Previously defined connections for the specified storage location appear in the drop-down. If no connections exist, create a new connection.	Create new connection.
   Connection name	The Azure Data Lake Storage Gen2 connection name.	A name for your connection.
   Authentication kind	The authorization model. The supported models are: Organizational account, Account key, Shared Access Signature (SAS), and Service principal. For more information, see ADLS shortcuts.	Dependent on the authorization model. Once you select an authentication kind, fill in the required credentials.

3. Select Next.

4. Browse to the target location for the shortcut.

   

   If you just used the storage account in the connection URL, all of your available containers appear in the left navigation view. If you specified a container in connection URL, only the specified container and its contents appear in the navigation view.

   Navigate the storage account by selecting a folder or clicking on the expansion carrot next to a folder.

   In this view, you can select one or more shortcut target locations. Choose target locations by clicking the checkbox next a folder in the left navigation view.

5. Select Next

   

   The review page allows you to verify all of your selections. Here you can see each shortcut that will be created. In the action column, you can click the pencil icon to edit the shortcut name. You can click the trash can icon to delete shortcut.

6. Select Create.

Amazon S3

Prerequisites

• A workspace with a Microsoft Fabric-enabled capacity
• A KQL database with editing permissions
• An Amazon S3 account. For more information, see What is Amazon S3?

Create shortcut

1. Browse to an existing KQL database.

2. Select New > OneLake shortcut.

   

Select a source

1. Under External sources, select Amazon S3.

2. Enter the Connection settings according to the following table:

   

   Field	Description	Value
   URL	The connection string for your Amazon S3 bucket.	https://BucketName.s3.RegionCode.amazonaws.com
   Connection	Previously defined connections for the specified storage location appear in the drop-down. If no connections exist, create a new connection.	Create new connection
   Connection name	The Amazon S3 connection name.	A name for your connection.
   Authentication kind	The Identity and Access Management (IAM) policy. The policy must have read and list permissions. For more information, see IAM users.	Dependent on the bucket policy.
   Access Key ID	The Identity and Access Management (IAM) user key. For more information, see Manage access keys for IAM users.	Your access key.
   Secret Access Key	The Identity and Access Management (IAM) secret key.	Your secret key.

Select Next.

1. Browse to the target location for the shortcut.

   

   If you used the global endpoint in the connection URL, all of your available buckets appear in the left navigation view. If you used a bucket specific endpoint in the connection URL, only the specified bucket and its contents appear in the navigation view.

   Navigate the storage account by selecting a folder or clicking on the expansion carrot next to a folder.

   In this view, you can select one or more shortcut target locations. Choose target locations by clicking the checkbox next a folder in the left navigation view.

2. Select Next

   

   The review page allows you to verify all of your selections. Here you can see each shortcut that will be created. In the action column, you can click the pencil icon to edit the shortcut name. You can click the trash can icon to delete shortcut.

3. Select Create.

The database refreshes automatically. The shortcut appears under Shortcuts in the Explorer pane.

The OneLake shortcut has been created. You can now query this data.

Query data

To query data from the OneLake shortcut, use the external_table() function.

1. 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.
2. Replace the table name placeholder with external_table('Shortcut name').
3. Select Run or press Shift + Enter to run a selected query.

Data types mapping

Delta parquet to Event house data types mapping

Delta primitive data types are mapped to Event house scalar data types using the following rules. For more information on Event house 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

Related content

• Query data in a KQL queryset
• external_table() function