Not able to create schema in Synapse Lake database using SPARK.

Question

Prayag kumar 20

Tried below query to create schema using synapse spark notebook, but instead of creating Schema its creating another lake database.

%%sql CREATE SCHEMA Test

How can we create different schemas in a single lake database?

PRADEEPCHEEKATLA 91,496 Reputation points Moderator

2023-06-26T08:50:56.61+00:00

@Prayag kumar - Thanks for the question and using MS Q&A paltform.

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.

You must create custom SQL schema where you will place your SQL objects. The custom SQL objects cannot be placed in the dbo schema. The dbo schema is reserved for the lake tables that are originally created in Spark or database designer.

For more details, refer to Custom SQL objects in lake databases.

Hope this helps. Do let us know if you any further queries.
PRADEEPCHEEKATLA 91,496 Reputation points Moderator

2023-06-29T09:50:01.7566667+00:00

@Prayag kumar - We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

1 answer

PRADEEPCHEEKATLA 91,496 Reputation points Moderator

2023-06-26T08:50:56.61+00:00

@Prayag kumar - Thanks for the question and using MS Q&A paltform.

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.

You must create custom SQL schema where you will place your SQL objects. The custom SQL objects cannot be placed in the dbo schema. The dbo schema is reserved for the lake tables that are originally created in Spark or database designer.

For more details, refer to Custom SQL objects in lake databases.

Hope this helps. Do let us know if you any further queries.
PRADEEPCHEEKATLA 91,496 Reputation points Moderator

2023-06-29T09:50:01.7566667+00:00

@Prayag kumar - We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Answer 1

Prayag kumar 20

@PRADEEPCHEEKATLA , provided answer didn't help. As a workaround, we are creating objects like schema_ObjectName.