Is there a limit to the number of tables in a Synapse Lake Database?

Question

Is there a limit to the number of tables in a Synapse Lake Database?

Marcus Farrugia 1

I cannot see any tables in a lake data base after 20 have been created. A database can be queried however it does not appear in the database list. This makes it difficult to manage the lake data base, I am wondering if this is a limitation or a setting.
Thanks,

Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-13T19:33:20.507+00:00

Hello @Marcus Farrugia ,
Welcome to the MS Q&A platform.

As far as I know, there is no limit to the number of tables in the Synapse lake Database.

Please correct me if my understanding is wrong. You cannot see any tables in the lake database if the table limit on any database is more than 20, but you can query the tables. You can see the tables if your table limit is less than 20.

Could you please answer the below questions?
Are you using SSMS/datastudio to connect to the Serverless endpoint to query the tables?
What type of authentication are you using to query the tables? (SQL or AAD)
Is this the issue with all lake databases or with any specific database?
Is the behavior is same across all users or with any specific user account?

I am looking forward to hearing from you.
Marcus Farrugia 1 Reputation point

2022-09-13T19:49:58.673+00:00

Hi BhargavaGunnam, thank you for taking time to reply. That is correct after 20 tables have been created in a lake data base they do not appear visible in the database. However; when I run a select data does return successfully.

Could you please answer the below questions?
Are you using SSMS/datastudio to connect to the Serverless endpoint to query the tables?
-No I am connecting through the Synapse web portal
What type of authentication are you using to query the tables? (SQL or AAD)
-I am authenticating with my AAD cred and MFA
Is this the issue with all lake databases or with any specific database?
-all lake databases have this behaviour
Is the behavior is same across all users or with any specific user account?
-all users are experiencing this (reader & contributor)
Marcus Farrugia 1 Reputation point

2022-09-13T19:50:43.11+00:00

@Bhargava-MSFT
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-13T19:59:05.2+00:00

Hello @Marcus Farrugia ,
Thank you for the quick reply. Let me do some research and get back to you on this.
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-13T20:42:44.58+00:00

Hello @Marcus Farrugia ,

I have tested the three scenarios below, and in all of my cases, I can see the tables from the synapse studio.

Were you able to see the tables if the list is less than 20? What is your Synapse region? How did you create your lake tables?(using custom or from template or from data lake)

Scenario 1:

Created more than 20 tables using "Custom"

Scenario 2:

Created more than 20 tables using "from template"

Scenario 3:

Created more than 20 tables using 'from data lake'
Marcus Farrugia 1 Reputation point

2022-09-13T20:54:52.073+00:00

Hi @Bhargava-MSFT I can see tables 1-20 however anything over 20 I cannot see. We have been creating tables using pyspark to create tables from parquet files in an adls container:
from pyspark.sql import *
from pyspark.sql.types import *

schema = StructType([StructField('col1',IntegerType(),True),
StructField('col2',IntegerType(),True),
StructField('col3',StringType(),True),
StructField('StartMonth',IntegerType(),True),
StructField('EndMonth',IntegerType(),True),
StructField('Description',StringType(),True),
StructField('col4',FloatType(),True),
StructField('col5',StringType(), True),
StructField('col6',DateType(), True)
])

ws = spark.read.parquet('abfss://******@stbrzdlz001cc001.dfs.core.windows.net/API/APIParquet/Table22.parquet/')

ws.write.format("parquet").mode("overwrite").saveAsTable("schema.Table22")
Marcus Farrugia 1 Reputation point

2022-09-13T21:01:45.19+00:00

@Bhargava-MSFT the images for scenario 2&3 didn't upload correctly can you repost?
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-13T21:07:07.817+00:00

Hello @Marcus Farrugia ,
sorry about that. I have updated the images.
Thanks for providing these details. Let me test this scenario from my end and get back to you.
Marcus Farrugia 1 Reputation point

2022-09-13T21:08:03.557+00:00

@Bhargava-MSFT thanks for updating images 2 and 3. In all 3 scenarios there are 20 or less tables. Are you able to create more > 20 tables?
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-13T21:30:40.233+00:00

Hello @Marcus Farrugia ,
I have tested your scenario by creating more than 20 tables on my default lake database using the pyspark. I can able to see them all.
I guess the issue might be specific to your synapse. Can you please let me know your synapse region?
Marcus Farrugia 1 Reputation point

2022-09-14T10:21:22.9+00:00

Hi @Bhargava-MSFT we are in Canada Central (redundant in Canada East)
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-14T19:43:28.88+00:00

Hi @Marcus Farrugia ,
Thank you for the update.
Sorry, I couldn't be able to repro the issue on the Canada central region too.
The best approach is to file a support ticket, and a support engineer can take a deeper look to troubleshoot the issue further. If you don't have a support plan, I can enable one-time free support for you to work closely on this matter.
I am looking forward to hearing from you.
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-15T20:46:15.187+00:00

Hi @Marcus Farrugia ,
I am checking in to see if you need help with the support request.

1 answer

Your answer

Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-13T19:33:20.507+00:00

Hello @Marcus Farrugia ,
Welcome to the MS Q&A platform.

As far as I know, there is no limit to the number of tables in the Synapse lake Database.

Please correct me if my understanding is wrong. You cannot see any tables in the lake database if the table limit on any database is more than 20, but you can query the tables. You can see the tables if your table limit is less than 20.

Could you please answer the below questions?
Are you using SSMS/datastudio to connect to the Serverless endpoint to query the tables?
What type of authentication are you using to query the tables? (SQL or AAD)
Is this the issue with all lake databases or with any specific database?
Is the behavior is same across all users or with any specific user account?

I am looking forward to hearing from you.
Marcus Farrugia 1 Reputation point

2022-09-13T19:49:58.673+00:00

Hi BhargavaGunnam, thank you for taking time to reply. That is correct after 20 tables have been created in a lake data base they do not appear visible in the database. However; when I run a select data does return successfully.

Could you please answer the below questions?
Are you using SSMS/datastudio to connect to the Serverless endpoint to query the tables?
-No I am connecting through the Synapse web portal
What type of authentication are you using to query the tables? (SQL or AAD)
-I am authenticating with my AAD cred and MFA
Is this the issue with all lake databases or with any specific database?
-all lake databases have this behaviour
Is the behavior is same across all users or with any specific user account?
-all users are experiencing this (reader & contributor)
Marcus Farrugia 1 Reputation point

2022-09-13T19:50:43.11+00:00

@Bhargava-MSFT
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-13T19:59:05.2+00:00

Hello @Marcus Farrugia ,
Thank you for the quick reply. Let me do some research and get back to you on this.
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-13T20:42:44.58+00:00

Hello @Marcus Farrugia ,

I have tested the three scenarios below, and in all of my cases, I can see the tables from the synapse studio.

Were you able to see the tables if the list is less than 20? What is your Synapse region? How did you create your lake tables?(using custom or from template or from data lake)

Scenario 1:

Created more than 20 tables using "Custom"

Scenario 2:

Created more than 20 tables using "from template"

Scenario 3:

Created more than 20 tables using 'from data lake'
Marcus Farrugia 1 Reputation point

2022-09-13T20:54:52.073+00:00

Hi @Bhargava-MSFT I can see tables 1-20 however anything over 20 I cannot see. We have been creating tables using pyspark to create tables from parquet files in an adls container:
from pyspark.sql import *
from pyspark.sql.types import *

schema = StructType([StructField('col1',IntegerType(),True),
StructField('col2',IntegerType(),True),
StructField('col3',StringType(),True),
StructField('StartMonth',IntegerType(),True),
StructField('EndMonth',IntegerType(),True),
StructField('Description',StringType(),True),
StructField('col4',FloatType(),True),
StructField('col5',StringType(), True),
StructField('col6',DateType(), True)
])

ws = spark.read.parquet('abfss://******@stbrzdlz001cc001.dfs.core.windows.net/API/APIParquet/Table22.parquet/')

ws.write.format("parquet").mode("overwrite").saveAsTable("schema.Table22")
Marcus Farrugia 1 Reputation point

2022-09-13T21:01:45.19+00:00

@Bhargava-MSFT the images for scenario 2&3 didn't upload correctly can you repost?
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-13T21:07:07.817+00:00

Hello @Marcus Farrugia ,
sorry about that. I have updated the images.
Thanks for providing these details. Let me test this scenario from my end and get back to you.
Marcus Farrugia 1 Reputation point

2022-09-13T21:08:03.557+00:00

@Bhargava-MSFT thanks for updating images 2 and 3. In all 3 scenarios there are 20 or less tables. Are you able to create more > 20 tables?
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-13T21:30:40.233+00:00

Hello @Marcus Farrugia ,
I have tested your scenario by creating more than 20 tables on my default lake database using the pyspark. I can able to see them all.
I guess the issue might be specific to your synapse. Can you please let me know your synapse region?
Marcus Farrugia 1 Reputation point

2022-09-14T10:21:22.9+00:00

Hi @Bhargava-MSFT we are in Canada Central (redundant in Canada East)
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-14T19:43:28.88+00:00

Hi @Marcus Farrugia ,
Thank you for the update.
Sorry, I couldn't be able to repro the issue on the Canada central region too.
The best approach is to file a support ticket, and a support engineer can take a deeper look to troubleshoot the issue further. If you don't have a support plan, I can enable one-time free support for you to work closely on this matter.
I am looking forward to hearing from you.
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-15T20:46:15.187+00:00

Hi @Marcus Farrugia ,
I am checking in to see if you need help with the support request.

Answer 1

CoffeeCanuck 26

Hi @Bhargava-MSFT after a few days, the tables did appear in the Data Object list in the Data Lake, still unclear why they cut off at 20 for some time; however, the issue seems to be resolved now, without understanding why. I appreciate all of the effort you put into looking into this and the offer to support in more detail. This thread can be closed.

Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-19T16:49:36.807+00:00

Hi @CoffeeCanuck ,
Really appreciate getting back and update on the thread.

Share via

Is there a limit to the number of tables in a Synapse Lake Database?

1 answer

Your answer