Synapse Analytics Lakehouse Schema Creation Error

Saravanan Kumar 0

Hi All,

I'm working in Synapse Analytics Lakehouse Database to create table with new schema, I have already a Database Core when i try to execute below code for creating my new table in new schema its not allowing me to create the table

PySpark Code:

df.write.mode("overwrite").saveAsTable(s"core.Deal.$TABLE_NAME_NEW")

Error Message : org.apache.spark.sql.AnalysisException: Couldn't find a catalog to handle the identifier core.Deal.stg_tbl_DEAL_BULK_Opportunities.

Could you please let me know like how to create table under new schema instead of creating in dbo.

Smaran Thoomu 15,845 Reputation points Microsoft Vendor

2024-04-09T09:10:33.1766667+00:00

@Saravanan 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

Smaran Thoomu 15,845 Reputation points Microsoft Vendor

2024-04-08T08:17:55.6666667+00:00
Hi @Saravanan Kumar

Thank you for reaching out to the community forum with your query.

As I understand you’re encountering an issue while creating a table in a new schema within Synapse Analytics Lakehouse Database. Let’s address this!

The error message you received indicates that the catalog couldn’t find the specified identifier (core.Deal.stg_tbl_DEAL_BULK_Opportunities). This could be due to incorrect schema or table names. Let’s address this!

Creating a Table in a New Schema: To create a table in a new schema, you can follow these steps: Step 1: Define Your Schema: Decide on the name of your new schema. For example, let’s call it my_new_schema. Step 2: Create the Table: Use the following PySpark code snippet to create your table in the desired schema:
# Replace 'my_new_schema' and 'my_table_name' with your actual schema and table names df.write.mode("overwrite").saveAsTable("my_new_schema.my_table_name")
Step 3: verify the Table: After executing the code, verify that the table has been created in the specified schema.

Ensure that the schema exists before creating the table.

Double-check the schema and table names for typos or inconsistencies.

Reference:

How-to: Create an empty lake database

User-defined schemas within Synapse SQL

Create lake database.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Please sign in to rate this answer.

1 person found this answer helpful.
Saravanan Kumar 0 Reputation points

2024-04-08T11:05:14.8766667+00:00

Hi @Smaran Thoomu

I have tired in the way you have mentioned but when i have only schema name its considering as Database and throwing below error.

Code I have used to create table

hist_table.write.mode("overwrite").saveAsTable(s"Deal.$TABLE_NAME")

org.apache.spark.sql.catalyst.analysis.NoSuchDatabaseException: Database 'deal' not found

Smaran Thoomu 15,845 Reputation points Microsoft Vendor

2024-04-08T18:07:41.5+00:00

Hi Saravanan Kumar,

The error message you received indicates that the specified database (deal) does not exist.

To create a table in a new schema, you need to create the schema first and then create the table in that schema. Here's an updated PySpark code snippet that you can use to create a table in a new schema:

# Replace 'my_new_schema' and 'my_table_name' with your actual schema and table names hist_table.write.mode("overwrite").saveAsTable("my_new_schema.my_table_name")

Make sure to replace "my_new_schema" and "my_table_name" with your actual schema and table names.

If you haven't created the schema yet, you can create it using the following PySpark code snippet:

# Replace 'my_new_schema' with your actual schema name spark.sql("CREATE SCHEMA my_new_schema")

Once you have created the schema, you can use the first code snippet to create your table in the desired schema.

I hope this helps! Let me know if you have any further questions.

Saravanan Kumar 0 Reputation points

2024-04-09T09:57:35.8433333+00:00

Thanks @Smaran Thoomu for the code you have provided its creating new database instead of Schema in existing DB.
For example currently I have DB name called core, I need to create schema under this DB, but it doesn't allow to create, its creating under DBO schema.

when i execute below code its creating new DB, is there any other way to create schema for particular DB in Lake database?

spark.sql("CREATE SCHEMA my_new_schema")

Smaran Thoomu 15,845 Reputation points Microsoft Vendor

2024-04-10T12:20:14.7566667+00:00

Hi Saravanan Kumar

I apologize for the confusion in my previous response. In Synapse Analytics Lakehouse Database, schemas are not supported at the database level. Instead, schemas are supported at the table level.

To create a table in a new schema within an existing database, you can use the following PySpark code snippet:

# Replace 'my_new_schema' and 'my_table_name' with your actual schema and table names hist_table.write.mode("overwrite").saveAsTable("core.my_new_schema.my_table_name")

This code snippet will create a new table called my_table_name in a new schema called my_new_schema within the existing database called core.

I apologize for any confusion my previous response may have caused. Thank you for bringing this to my attention. Let me know if you have any further questions.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Synapse Analytics Lakehouse Schema Creation Error

1 answer

Your answer