OneLake Table REST API's For Microsoft Fabric

OneLake now offers a REST API to interact with Delta table on Microsoft Fabric. With these API's now its possible to fetch metadata of underlying tables.

https://youtu.be/HYvJUevlHU8

These API's are a bit different compared to your standard Fabric REST API's that you would normally use to query your underlying Fabric ecosystem.

So one of thing regarding these API's, is that the operations through these API's are compatible with Unity Catalog API standards while your Fabric REST API's are not.

https://www.unitycatalog.io/

The major limitations of these standards are that the name of the underlying objects cannot have any special characters in them,

Say for example you have an Workspace name or Lakehouse name that has a special character in it. For instance a lakehouse having name concatenated with a underscore followed by say datetimestamp .Running operations of such objects are nt supported through these API's. The way around is to replace the object names with the underlying object id's.

I had penned down an article more than a year ago on how to fetch some extensive details of these tables. You can find that article here. In that article I had used Parequet.Net library to read the Parquet files metadata which I was able to access through ADLS Gen2 API's .

I was expecting these APIs to provide details similar to what I was able to demonstrate through my article.

Below is some sample of that information

As you can see the above details are very extensive.

There are three supported operations in the OneLake API , they are List schemas, List tables,Get table.

Table details exposed by OneLake API's is as follows

But one advantage with OneLake RESTL API's are that, it exposes the column level details of the table but with my approach that was not possible.

SetUp

Install the following Nuget Packages in your C#.Net console application

dotnet add package Newtonsoft.Json
dotnet add package Microsoft.Extensions.Configuration.Json
dotnet add package Microsoft.Extensions.Configuration
dotnet add package Spectre.Console
dotnet add package Microsoft.Identity.Client

We will read the configuration details from appsettings.json file.

Appsettings.json

{ 
 "Logging": 
    { "LogLevel": { 
        "Default": "Information", 
        "Microsoft": "Warning", 
        "Microsoft.Hosting.Lifetime": "Information" 
   } 
  }, 
 "AllowedHosts": "*", 
 "ClientId": "Service Principal Client Id", 
 "CsvLocation": "Export Location", 
 "WorkSpace": "Your Workspace", 
 "LakeHouse": "Your Lakehouse" 
}

The code will analyze all the tables of the lakehouse mentioned in the appsettings.jsonfile and export the details to defined location mentioned in the appsettings file

Export :

Service Principal permissions >> Grant the following API permissions to the service principal

Code

I have three classes in the project

Program.cs being the entry point into the application.

Authentication.cs returns the bearer token

HttpMethods.cs containing the various Http methods

HttpMethods class inherits Authentication class

Authentication.cs

>> Since I require two scopes, one for one lake storage(https://storage.azure.com/.default) and second for Fabric Apis(https://api.fabric.microsoft.com/.default), I created a scope variable of type IEnumerable and based on the type passed to the function ReturnAuthenticationResult the token was generated for the underlying resources.

using Microsoft.Identity.Client;

namespace Security 
{
  internal class Authentication 
   {
public static bool istokencached = false;
public static string clientId = "";
private static IEnumerable<string> scopes = new List<string> { "https://storage.azure.com/.default", "https://api.fabric.microsoft.com/.default" };
private static string Authority = "https://login.microsoftonline.com/organizations";
private static string RedirectURI = "http://localhost";

public async static Task<AuthenticationResult> ReturnAuthenticationResult(string type)
{
    string AccessToken;
    PublicClientApplicationBuilder PublicClientAppBuilder =
        PublicClientApplicationBuilder.Create(clientId)
        .WithAuthority(Authority)
        .WithCacheOptions(CacheOptions.EnableSharedCacheOptions)
        .WithRedirectUri(RedirectURI);

    IPublicClientApplication PublicClientApplication = PublicClientAppBuilder.Build();
    var accounts = await PublicClientApplication.GetAccountsAsync();
    AuthenticationResult result;
    var selectedScopes = type == "fabric" ? new[] { scopes.ElementAt(1) } : new[] { scopes.ElementAt(0) };
    try
    {
        
        result = await PublicClientApplication.AcquireTokenSilent(selectedScopes, accounts.First())
                         .ExecuteAsync()
                         .ConfigureAwait(false);

    }
    catch
    {
        result = await PublicClientApplication.AcquireTokenInteractive(selectedScopes)
                         .ExecuteAsync()
                         .ConfigureAwait(false);

    }
    istokencached = true;
    return result;

    }

   }
}

HttpMethods.cs

using System.Net.Http.Headers; 
using Microsoft.Identity.Client;

namespace Http 
{ 

internal class HttpMethods : Security.Authentication 
{ 

    public static string access_token = ""; 
    public static double currentfilelength=0; 
    public static readonly HttpClient client = new HttpClient(); 
    protected HttpClient Client => client;    
    public async static Task<string> GetAsync(string url)
    {
        AuthenticationResult result = await ReturnAuthenticationResult(url.Contains("onelake") ? "storage" : "fabric");
        client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", result.AccessToken);
        access_token = result.AccessToken;
        HttpResponseMessage response = await client.GetAsync(url);

        try
        {
            response.EnsureSuccessStatusCode();
            return await response.Content.ReadAsStringAsync();
        }
        catch
        {
            Console.WriteLine(response.Content.ReadAsStringAsync().Result);
            return null;
        }

    }

    public async static Task<Stream> SendAsync(HttpRequestMessage httprequestMessage)
    {
        AuthenticationResult result = await ReturnAuthenticationResult();

        client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", result.AccessToken);

        HttpResponseMessage response = await client.SendAsync(httprequestMessage);
        response.EnsureSuccessStatusCode();
        try
        {
            currentfilelength = (double)response.Content.Headers.ContentLength;
            return await response.Content.ReadAsStreamAsync();
        }
        catch
        {
            Console.WriteLine(response.Content.ReadAsByteArrayAsync().Result);
            return null;
        }
    }

 }
}

Note : As mentioned in my other article, you will have the issues with converting the date values from responses of the One Lake Table API's

I had explained the reason for this issue in my other article. See the screenshot below from that article.

Program.cs

   using Microsoft.Extensions.Configuration; 
   using Newtonsoft.Json.Linq; 
   using Spectre.Console; 
   using File = System.IO.File;

   namespace AnaylzeDeltaTables 
   { 
     internal class Program 
    {
    private static string workSpace = "";//"Medallion Architecture
    private static string lakeHouse = "";
    private static string CsvLocation = "";
    private static string dfsendpoint = "";
    private static string responsename = "";

    static async Task Main(string[] args)
    {
        ReadConfig();
        await GetTableDetails();          
        AnsiConsole.MarkupLine("");
        AnsiConsole.MarkupLine($"[Blue]Process completed successfully..[/]");
        AnsiConsole.MarkupLine("");
        AnsiConsole.MarkupLine($"Csv's generated at location [Red]{CsvLocation}[/]");
        Thread.Sleep(5000);
    }

    public static void ReadConfig()
    {
        var builder = new ConfigurationBuilder()
        .AddJsonFile($"Appsettings.json", true, true);
        var config = builder.Build();
        Security.Authentication.clientId = config["ClientId"];
        workSpace = config["WorkSpace"];
        lakeHouse = config["LakeHouse"];
        CsvLocation = config["CsvLocation"];
    }

    public static async Task<string> GetTableDetails()
    {
        JObject jObject;
        JArray jArray;
        JObject jObject_schemas;
        JArray jArray_schemas;
        JObject jObject_tables;
        JArray jArray_tables;
        JObject jObject_columns;
        JArray jArray_columns;

        string workSpaceId = "";
        string lakeHouseId = "";
        string baseUrl = $"https://api.fabric.microsoft.com/v1/admin/workspaces";
        string response = await Http.HttpMethods.GetAsync(baseUrl);
        AnsiConsole.MarkupLine("");
        AnsiConsole.MarkupLine($"Fetching workspaceid for workspace [Red]{workSpace}[/]...Please wait.");
        Thread.Sleep(1000);
        jObject = JObject.Parse(response);
        jArray = (JArray)jObject["workspaces"];
        foreach (JObject path in jArray)
        {
            if (path["name"].ToString() == workSpace)
            {
                workSpaceId = path["id"].ToString();
                break;

            }
        }
        ;
        AnsiConsole.MarkupLine("");
        AnsiConsole.MarkupLine($"Workspaceid of workspace [Red]{workSpace}[/] is [Green]{workSpaceId}[/]");
        AnsiConsole.MarkupLine("");
        AnsiConsole.MarkupLine($"Fetching lakehouseid for lakehouse [Red]{lakeHouse}[/]...Please wait.");
        Thread.Sleep(1000);
        AnsiConsole.MarkupLine("");

        baseUrl = $"https://api.fabric.microsoft.com/v1/workspaces/{workSpaceId}/lakehouses";
        response = await Http.HttpMethods.GetAsync(baseUrl);
        jObject = JObject.Parse(response);
        jArray = (JArray)jObject["value"];

        foreach (JObject path in jArray)
        {

            if (path["displayName"].ToString() == lakeHouse.Replace(".LakeHouse", ""))
            {
                lakeHouseId = path["id"].ToString();
                break;

            }
        }
        ;

        AnsiConsole.MarkupLine($"Lakehouseid of lakehouse [Red]{lakeHouse}[/] is [Green]{lakeHouseId}[/]");

        dfsendpoint = $"https://onelake.table.fabric.microsoft.com/delta/{workSpaceId}/{lakeHouseId}/api/2.1/unity-catalog/schemas?catalog_name={lakeHouseId}";
        response = await Http.HttpMethods.GetAsync(dfsendpoint);
        jObject_schemas = JObject.Parse(response);
        jArray_schemas = (JArray)jObject_schemas["schemas"];

        AnsiConsole.MarkupLine("");
        AnsiConsole.MarkupLine($"Fetching schemas for lakehouse [Red]{lakeHouse}[/] in workspace [Red]{workSpace}[/]...Please wait.");
        Thread.Sleep(1000);
        AnsiConsole.MarkupLine("");

        foreach (JObject path in jArray_schemas)
        {
            dfsendpoint = $"https://onelake.table.fabric.microsoft.com/delta/{workSpaceId}/{lakeHouseId}/api/2.1/unity-catalog/tables?catalog_name={lakeHouseId}&schema_name={path["name"]}";
            response = await Http.HttpMethods.GetAsync(dfsendpoint);
            AnsiConsole.MarkupLine($"Processing schema [Green]{path["name"]}[/] of lakehouse [Red]{lakeHouse}[/]...Please wait.");
            Thread.Sleep(1000);
            AnsiConsole.MarkupLine("");
            AnsiConsole.MarkupLine($"Schema Processing completed");
            jObject_tables = JObject.Parse(response);
            jArray_tables = (JArray)jObject_tables["tables"];
            Thread.Sleep(1000);

            foreach (JObject path_tables in jArray_tables)
            {
                dfsendpoint = $"https://onelake.table.fabric.microsoft.com/delta/{workSpaceId}/{lakeHouseId}/api/2.1/unity-catalog/tables/{lakeHouseId}.{path["name"]}.{path_tables["name"]}";
                response = await Http.HttpMethods.GetAsync(dfsendpoint);
                AnsiConsole.MarkupLine("-------------------------------------------------------------------------------------------------");
                AnsiConsole.MarkupLine($"Processing table [Green]{path_tables["name"]}[/] of schema [Red]{path["name"]}[/]...Please wait.");
                Thread.Sleep(1000);
                AnsiConsole.MarkupLine("");
                AnsiConsole.MarkupLine($"Table Processing completed");

                jObject_columns = JObject.Parse(response);
                jArray_columns = (JArray)jObject_columns["columns"];
                AnsiConsole.MarkupLine("");
                AnsiConsole.MarkupLine($"Exporting details for table [Green]{path_tables["name"]}[/] of schema [Red]{path["name"]}[/] to location [Red]{String.Concat(CsvLocation, "\\", "Table_Details_", workSpace, "_", lakeHouse, ".csv")}[/]...Please wait.");
                WriteTableDetailsToCsv(String.Concat(CsvLocation, "\\", "Table_Details_", workSpace, "_", lakeHouse, ".csv"), "Workspace||" + workSpace + "~LakeHouse||" + lakeHouse + "~Table||" + jObject_columns.Property("name").Value.ToString() + "~Table Id||" + jObject_columns.Property("table_id").Value.ToString() + "~Table Type||" + jObject_columns.Property("table_type").Value.ToString() + "~Data Source Format||" + jObject_columns.Property("data_source_format").Value.ToString() + "~Storage Location||" + jObject_columns.Property("storage_location").Value.ToString() + "~Created At||" + await ConvertTicksToDateTime((Int64)jObject_columns.Property("created_at").Value) + "~Updated At||" + await ConvertTicksToDateTime((Int64)jObject_columns.Property("updated_at").Value) + "~Created By||" + jObject_columns.Property("created_by").Value + "~Updated By||" + jObject_columns.Property("updated_by").Value);
                foreach (JObject path_columns in jArray_columns)
                {
                    WriteCoumnsDetailsToCsv(String.Concat(CsvLocation, "\\", "Column_Details_", workSpace, "_", lakeHouse, "_", jObject_columns.Property("name").Value.ToString(), ".csv"), "Workspace||" + workSpace + "~LakeHouse||" + lakeHouse + "~Table||" + jObject_columns.Property("name").Value.ToString() + "~Column||" + path_columns["name"] + "~Type Precision||" + path_columns["type_precision"] + "~Type Scale||" + path_columns["type_scale"] + "~Interval Type||" + path_columns["type_interval_type"] + "~Comment||" + path_columns["comment"] + "~Partition Index||" + path_columns["partition_index"] + "~Position||" + path_columns["position"] + "~Nullable||" + path_columns["nullable"]);
                }
                AnsiConsole.MarkupLine("");
                AnsiConsole.MarkupLine($"Export completed");
            }

        }

        return "";

    }

    public static async void WriteCoumnsDetailsToCsv(string path, string values)
    {
        string delimiter = ", ";
        string[] parts = values.Split('~');
        if (!File.Exists(path))
        {
            string createText = "Workspace " + delimiter + "LakeHouse " + delimiter + "Table " + delimiter + "Type Name" + delimiter + "Type Precision" + delimiter + "Type Scale " + delimiter + "Interval Type" + delimiter + "Comment " + delimiter + "Partition Index " + delimiter + "Position " + delimiter + "Nullable " + delimiter + Environment.NewLine;

            File.WriteAllText(path, createText);
        }
        string appendText = parts[0].Split("||")[1].ToString() /*LakeHouse*/ + delimiter + await ReplaceInvalidValues(parts[1].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[2].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[3].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[4].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[5].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[6].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[7].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[8].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[9].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[10].Split("||")[1].ToString()) + Environment.NewLine;
        File.AppendAllText(path, appendText);
    }
    public static async void WriteTableDetailsToCsv(string path, string values)
    {
        string delimiter = ", ";
        string[] parts = values.Split('~');
        if (!File.Exists(path))
        {
            string createText = "Workspace " + delimiter + "LakeHouse " + delimiter + "Table " + delimiter + "Table Id" + delimiter + "Table Type " + delimiter + "Data Source Format " + delimiter + "Storage Location " + delimiter + "Created At " + delimiter + "Updated At " + delimiter + "Created By " + delimiter + "Updated By " + delimiter + Environment.NewLine;

            File.WriteAllText(path, createText);
        }
        string appendText = parts[0].Split("||")[1].ToString() /*LakeHouse*/ + delimiter + await ReplaceInvalidValues(parts[1].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[2].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[3].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[4].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[5].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[6].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[7].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[8].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[9].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[10].Split("||")[1].ToString()) + Environment.NewLine;
        File.AppendAllText(path, appendText);
    }

    public async static Task<string> ConvertTicksToDateTime(long ticks)
    {
        DateTime epoch = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Local);
        DateTime dateTime = epoch.AddMilliseconds(ticks);
        return dateTime.ToString(("dd-MM-yyyy HH:mm:ss"));
    }

    public static async Task<string> ReplaceInvalidValues(string s)
    {
        if (s == ("") || s == ("[]"))
            return "NA";
        else
            return s;
    }

 }
}

Output from the above code

Table Details >>

Column Details >>

Conclusion

In this article ,I tried to highlight the pros and cons of using the OneLake table API and how you could leverage them to fetch high level metadata from the underlying Fabric objects. My upcoming article will focus on combining the outputs from both sources, one from my previous article and the output from One Lake Table API's.

Till then stay tuned !!!