topic Schema on lakehouse sql endpoint in Data Engineering

Schema on lakehouse sql endpoint

DennesTorres — Sat, 05 Aug 2023 18:58:25 GMT

Hi,

A SQL Endpoint in a lake house allow us to create schemas. The tables from the lakehouse are automatically in the DBO schema, but we can create new schemas.

Is there anyway to include the tables in custom schemas, either using notebooks or the UI ? If not, are we only able to include custom objects, such as views, in custom schemas ?

Kind Regards,

Dennes

Re: Schema on lakehouse sql endpoint

puneetvijwani — Sat, 12 Aug 2023 17:57:02 GMT

@DennesTorres It seems to be same limitations that we use to have in Synpase analytics -- lake databases
Reference Text:

Custom SQL objects in lake databases

Lake databases allow creation of custom T-SQL objects, such as schemas, procedures, views, and the inline table-value functions (iTVFs). In order to create custom SQL objects, you MUST create a schema where you will place the objects. Custom SQL objects cannot be placed in dbo schema because it is reserved for the lake tables that are defined in Spark, database designer, or Dataverse.

Link :
https://learn.microsoft.com/en-us/azure/synapse-analytics/metadata/database

Re: Schema on lakehouse sql endpoint

GeethaT-MSFT — Sun, 13 Aug 2023 20:05:26 GMT

Hi @DennesTorres Spark doesn't support schemas, and Lakehouse, tables in the SQL endpoint are synchronized from the Spark catalog. So currently, not all lakehouse tables will appear in the dbo schema

Regards

Geetha

Re: Schema on lakehouse sql endpoint

yllsuarez76 — Fri, 13 Oct 2023 20:28:46 GMT

Schemas are available at the SQL endpoint. I transfered a table from dbo to a custom schema, which sounds like an extended property. Is there a way (or will be) to define the schema while creating the table in fabric? That's benfecial for permissions granted via SQL at schema level instead of each table.

Re: Schema on lakehouse sql endpoint

DennesTorres — Fri, 13 Oct 2023 21:27:10 GMT

Hi,

Could you provide more details about how you managed to transfer a table to a different schema and it you still could access this table on a notebook?

Kind Regards,

Denned

Re: Schema on lakehouse sql endpoint

yllsuarez76 — Fri, 13 Oct 2023 22:30:34 GMT

you can move a table from schema while using the SQL Point and querying via tsql (I did in SSMS). This looks to be nothing more than an extended property to the underlying delta table.

However, from the notebook you still querying using spark which doesnt support schema, as mentioned by the support team.

alter schema conformed transfer [dbo].[sales_internal]

select count(*) from [conformed].[sales_internal]

Re: Schema on lakehouse sql endpoint

Anonymous — Thu, 09 Nov 2023 09:56:53 GMT

@puneetvijwani We can create the custom SQL objects in the dbo schema as well, like views and procedure. We just can't create the function only.