{"id":98641,"date":"2023-10-30T17:00:57","date_gmt":"2023-10-30T17:00:57","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=98641"},"modified":"2024-09-03T20:15:17","modified_gmt":"2024-09-03T20:15:17","slug":"sql-endpoints-and-lakehouse-ui-differences-and-access-secrets","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/sql-endpoints-and-lakehouse-ui-differences-and-access-secrets\/","title":{"rendered":"SQL Endpoints and Lakehouse UI Differences and Access Secrets"},"content":{"rendered":"

There are some differences and secrets between the UI of a SQL Endpoint<\/strong> and the UI of a Lakehouse<\/strong>.<\/p>\n

I believe the lakehouse<\/strong> UI was changed recently, otherwise you can blame me for being distracted to this level. Let’s analyze the differences and pending points between these UI’s.<\/p>\n

SQL Endpoints<\/h2>\n

When using a SQL Endpoint<\/strong>, we can add multiple lakehouses<\/strong> to the explorer. They\u00a0are included in the vertical, one above another. This is one of the differences between a SQL Endpoint<\/strong> and the lakehouse<\/strong>.<\/p>\n

\"A<\/p>\n

On the top of Explorer, the button is called “+ Warehouses”.<\/em> This may be a bit confusing because we are working with a lakehouse<\/strong>. The fact is we can add either a lakehouse<\/strong> or data warehouses<\/strong> to the explorer.<\/p>\n

Domains and Workspaces<\/h2>\n

The Microsoft Fabric<\/strong> portal has the domain feature in preview. Domains are a concept from the Data Mesh<\/strong> architecture, but this is a subject for a different and longer article.<\/p>\n

What’s important to understand is that domains allow us to create a set of workspaces and make some limited management tasks to the entire set.<\/p>\n

The access to the domains management is done through the Power BI Admin portal<\/strong>.<\/p>\n

\"A<\/p>\n

On the left side of the window, we click on Domains<\/strong>.<\/p>\n

\"A<\/p>\n

In my environment, I already have many domains created.<\/p>\n

\"A<\/p>\n

During the preview stage, there are very few configurations we can manage:<\/p>\n

    \n
  • The Domain Admins<\/li>\n
  • The Domain Contributors<\/li>\n
  • Workspaces belonging to the domain<\/li>\n
  • Data certification rules for the domain<\/li>\n<\/ul>\n

    \"A<\/p>\n

    \"A<\/p>\n

    The fact of whether a workspace is linked to a domain or not affects how the SQL Endpoint<\/strong> and Lakehouse<\/strong> window works.<\/p>\n

    Adding a Storage to the SQL Endpoint<\/h2>\n

    There are some curious behaviors in relation to the SQL Endpoint<\/strong> and the domain management.<\/p>\n

    If the workspace doesn’t belong to a domain, on the top right of the window we only see a “Filter”<\/em> dropdown. Using this limited option, we can only add objects, either data warehouse<\/strong> or lakehouses<\/strong>, which are present in the same workspace.<\/p>\n

    \"A<\/p>\n

     <\/p>\n

    On the other hand, if the workspace belongs to a domain, we can see an additional dropdown intended to include a list of domains.<\/p>\n

    \"A<\/p>\n

     <\/p>\n

    However, this dropdown has some very singular behaviors:<\/p>\n

      \n
    • Only the domain of the current workspace is listed, we can’t see a list of other existing domains.<\/li>\n
    • The “All Domains”<\/em> is the default option, but we don’t see objects from all domains by default. We need to change the dropdown to one domain and change back to “All Domains” in order to see all the objects.<\/li>\n
    • The “All Domains”<\/em> option includes objects not linked to any domain. The objects not linked to any domain can’t see the other ones, but the other ones can see them.<\/li>\n<\/ul>\n

       <\/p>\n

      \"A<\/p>\n

      Queries on the SQL Endpoint<\/h2>\n

      Although we can add objects from different workspaces in the Explorer, we can’t query them. Any attempt to make a cross workspace query will result in an error.<\/p>\n

      \"A<\/p>\n

      Cross lakehouse<\/strong> work is a long subject for another article. But we can make a conclusion: Either there is some missing feature being produced, or the possibility to add <\/em>lakehouses <\/em><\/strong>to the explorer <\/em>but not being able to query them is a small method to make cross <\/em>lakehouse<\/em><\/strong> queries easier (but not much).<\/em><\/p>\n

      The lakehouse explorer<\/h2>\n

      We can consider there are two lakehouse<\/strong> explorers:<\/p>\n

        \n
      • One is accessible directly from the lakehouse<\/strong>. On this one, only the lakehouse<\/strong> can be accessible.<\/li>\n
      • The second one is accessible when we open a notebook. In this one we can add multiple lakehouses<\/strong>.<\/li>\n<\/ul>\n

        The window to add a lakehouse<\/strong> to this explorer is very similar to the one explained in relation to the SQL Endpoints<\/strong> but some behaviors are different:<\/p>\n

          \n
        • All the domains are visible in this window, while on the SQL Endpoint<\/strong> only the workspace domain is visible.<\/li>\n
        • The domain dropdown is available independently if the workspace is linked to a domain or not.<\/li>\n<\/ul>\n

          \"A<\/p>\n

           <\/p>\n

          My developer mind keeps wondering why a window which should be the same has different behaviors depending on where it’s called from one place or another, without a clear reason for this. I would love to read your thoughts on the comments.<\/p>\n

          The default lakehouse<\/h2>\n

          The first lakehouse<\/strong> included becomes the default one for the notebook.<\/p>\n

          The notebook uses a feature called Live Pool<\/strong>. This means when we run the notebook the spark pool is created automatically for us.<\/p>\n

          The spark pool creation process includes mounting the default lakehouse<\/strong> as a folder on file system. The path for the default lakehouse<\/strong> will be \/lakehouse\/default<\/em>, with the option to access files on \/lakehouse\/default\/files<\/em> or access tables on \/lakehouse\/default\/tables<\/em>.<\/p>\n

          We can use the following code to list the tables from the default lakehouse<\/strong>:<\/p>\n

          <\/p>\n

          \n
          import<\/span> os<\/span>\r\n\r\nfull_tables =<\/span> os.<\/span>listdir('\/lakehouse\/default\/Tables'<\/span>)\r\n\r\nfor<\/span> table in<\/span> full_tables:\r\n     print<\/span>(table)\r\n<\/pre>\n<\/div>\n
          \"A<\/p>\n
          Multiple Lakehouses on the Explorer<\/h2>\n
          When we click on the two arrows icon, we can add new lakehouses <\/strong>to the explorer.<\/p>\n
          \"A<\/p>\n
          The lakehouses<\/strong> are not included vertically, like on the SQL Endpoint<\/strong> explorer. They are only visible when we click again on the two arrows icon.<\/p>\n
          \"A<\/p>\n
          The menu shows which one is the default lakehouse<\/strong> and allows us to change it. Changing the default lakehouse<\/strong> changes the content mounted on the folder on \/lakehouse\/default<\/em>.<\/p>\n
          The Notebook Access to the lakehouse<\/h2>\n
          The notebook has two different ways to access the lakehouse<\/strong>:<\/p>\n
            \n
          • Access the mounted lakehouse<\/strong> using the OS path<\/li>\n
          • Use Spark.SQL<\/strong> to access the lakehouses<\/strong> contained in the same workspace as the default one<\/li>\n<\/ul>\n
            There is no method for the notebook to iterate through the lakehouses<\/strong> added to the explorer. The presence of the lakehouses<\/strong> on the explorer only helps us choose the default lakehouse<\/strong>, nothing else.<\/p>\n
            This makes me wonder if this is only the starting stage of new features about to be released until the Microsoft Fabric<\/strong> GA version.<\/p>\n
            Spark.SQL on the notebook<\/h2>\n
            Spark SQL<\/strong> can access different lakehouses<\/strong>, but the ones\u00a0available to Spark SQL <\/strong>are totally independent of the lakehouses<\/strong> included in the lakehouse<\/strong> explorer.<\/p>\n
            In summary, Spark.SQL<\/strong> can access all the lakehouses<\/strong> contained in the same workspace as the default lakehouse<\/strong>. It doesn’t matter if they\u00a0are included in the lakehouse<\/strong> explorer or not.<\/p>\n
            We can use the following code to list the available lakehouses<\/strong> to spark.sql<\/strong>:<\/p>\n
            <\/p>\n
            \n
            lakehouses =<\/span> spark.<\/span>catalog.<\/span>listDatabases()\r\n\r\nlakehouse_list =<\/span> []\r\n\r\nfor<\/span> lakehouse in<\/span> lakehouses:\r\n     lakehouse_list.<\/span>append(lakehouse.<\/span>name)\r\n\r\nprint<\/span>(lakehouse_list)\r\n<\/pre>\n<\/div>\n
            \"A<\/p>\n
            As you may notice in this example, the lakehouses<\/strong> listed are the ones in the same workspace as the default. This includes a lakehouse<\/strong> not in the Explorer and ignores others in it.<\/p>\n
            We can go further and use spark sql<\/strong> to list the tables in the lakehouses<\/strong> using the following code:<\/p>\n
            <\/p>\n
            \n
            for<\/span> lake in<\/span> lakehouse_list:\r\n    tables=<\/span>spark.<\/span>sql(f'SHOW TABLES IN {lake}'<\/span>)\r\n    tablenames=<\/span>list<\/span>(tables.<\/span>toPandas()['tableName'<\/span>])\r\n    for<\/span> tb in<\/span> tablenames:\r\n        print<\/span>(tb)\r\n<\/pre>\n<\/div>\n
            \"A<\/p>\n
            Summary<\/h2>\n
            The UI is full of small tricks and unexpected differences which make us wonder which new features are coming on the GA version to justify these inconsistencies without a clear reason.<\/p>\n
            Meanwhile, we need to navigate these very small inconsistencies. The better we understand the relation between the lakehouse<\/strong> explorers, OS path and spark SQL, the better we will be able to make a great use of the environment.<\/p>\n
             <\/p>\n","protected":false},"excerpt":{"rendered":"
            There are some differences and secrets between the UI of a SQL Endpoint and the UI of a Lakehouse. I believe the lakehouse UI was changed recently, otherwise you can blame me for being distracted to this level. Let’s analyze the differences and pending points between these UI’s. SQL Endpoints When using a SQL Endpoint,……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528,53,159164,159166],"tags":[123646,158998,158997,101611],"coauthors":[6810],"class_list":["post-98641","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","category-featured","category-microsoft-fabric","category-powerbi","tag-data-lake","tag-lakehouse","tag-microsoft-fabric","tag-power-bi"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/98641","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=98641"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/98641\/revisions"}],"predecessor-version":[{"id":98971,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/98641\/revisions\/98971"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=98641"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=98641"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=98641"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=98641"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}