Create a shortcut for eventhouse table in lake house using fabric api

[ChiragDBB]

Hi
I want to create a shortcut for a eventhouse table in lakehouse using the fabric api.
I have figured out to create shortcuts using the fabric api.

But I have two issues with it.

1. The shortcut is created for all tables and not one table. I want to create only for one table.

2. The shortcut fetches the data in parquet format. I want to store the data as a delta table

for someone more visual. below is what i want.

I want to make this using the api (this is what i get when i create the shortcut using the ui)

this is what is currently created by using the fabric api

both are inside the "Tables" in the lakehouse.

[ChiragDBB · Accepted solution]

import json
import requests
from requests import status_codes
import sempy.fabric as fabric
import time

# lakehouse_id = fabric.get_lakehouse_id()
# workspace_id = fabric.get_workspace_id()
table_name = "###"
workspace_id = "###"
lakehouse_id = '###'
tenant_id = "###"
client_id =  "###"
client_secret = "###"

auth_url = f"https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token"
auth_data = {
    'grant_type': 'client_credentials',
    'client_id': client_id,
    'client_secret': client_secret,
    'scope': 'https://analysis.windows.net/powerbi/api/.default'   
}

response = requests.post(auth_url, data=auth_data)
fabric_access_token = response.json().get("access_token")

request_headers = {
    "Authorization": "Bearer " + fabric_access_token,
    "Content-Type": "application/json"
}
# action, shortcut_path, shortcut_name, target
request_body = {
        "path": "Tables",
        "name": table_name,
        "target": {
            "OneLake": {
                "workspaceId": workspace_id,
                "itemId": "###",
                "path": "Tables/" + table_name 
            }
        }
    }

response = requests.request(
    method = "POST", 
    url = f'https://api.fabric.microsoft.com/v1/workspaces/{workspace_id}/items/{lakehouse_id}/shortcuts?shortcutConflictPolicy=Abort', 
    headers = request_headers, 
    json = request_body)

 

docs - OneLake Shortcuts - Create Shortcut - REST API (Core) | Microsoft Learn

[v-venuppu]

Hi @ChiragDBB ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @andrewsommer for the prompt response. 

To address your issues with creating shortcuts for a single table in a lakehouse using the Fabric API and ensuring the data is stored as a Delta table, here are some steps:

• When creating a shortcut using the Fabric API, you need to specify the exact table you want to reference. The API allows you to create shortcuts to specific data items, including individual tables. Ensure you are using the correct parameters to target a single table rather than all tables.
• By default, Fabric stores data in Delta format, which is an open-source storage layer that brings ACID transactions to big data and analytics workloads. To ensure your data is stored as a Delta table, you need to configure the API to handle Delta Lake features.

  Steps to resolve the issue:

  • When creating the shortcut, ensure you are specifying the table's unique identifier in the API call. This will prevent the creation of shortcuts for all tables.
  • Ensure that the API call includes parameters to store the data in Delta format. This might involve setting specific flags or configurations in the API request.

    Here is an example of how you might structure your API call to create a shortcut for a single table and store the data as a Delta table:

    {
    "shortcutName": "EventhouseTableShortcut",
    "source": {
    "type": "table",
    "id": "unique_table_id"
    },
    "destination": {
    "format": "delta"
    }
    }

    
    For more detailed instructions and examples, you can refer to the following resources:

    Delta Lake table format interoperability - Microsoft Fabric | Microsoft Learn

    Referencing data to a Lakehouse using shortcuts - Microsoft Fabric | Microsoft Learn

    Regards,

    Rama U.

[ChiragDBB]

hi @v-venuppu 
thanks for the reply, but unfortunately the solution you provided did not work.
but fortunately i found a solution for it

[andrewsommer]

Currently, shortcuts are directory-level. If the Eventhouse stores tables as subfolders (like /Tables/fact_device_data), you can point the shortcut directly to that table's folder path using the Fabric API.

{
  "name": "fact_device_data_shortcut",
  "properties": {
    "target": {
      "type": "lakehouse",
      "workspaceId": "<source_workspace_id>",
      "itemId": "<eventhouse_item_id>",
      "subPath": "Tables/fact_device_data"  // important: specific path to the table
    }
  }
}

A shortcut does not ingest or convert the data; it just references it. So to convert it to a Delta table, you must materialize it.

-- Read from shortcut (Parquet format)
df = spark.read.format("parquet").load("/Tables/fact_device_data_shortcut")

-- Write as Delta table into your Lakehouse
df.write.format("delta").mode("overwrite").save("/Tables/fact_device_data_delta")

register it as a delta lakehouse table:

CREATE TABLE fact_device_data_delta
USING DELTA
LOCATION '/Tables/fact_device_data_delta'

Then it is queryable in the lakehouse sql endpoint

Please mark this post as solution if it helps you. Appreciate Kudos.

[ChiragDBB]

hi @andrewsommer 
are you sure there is no way to create a table level and not a directory level shortcut??

and if you are sure, can we request this feature??

[ChiragDBB]

please help me 🙏🙏🙏