Error when accessing 'num_inserted_rows' in Spark SQL (DBR 15.4 LTS)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-30-2024 08:00 PM
Hello Databricks Community,
I've encountered an issue while trying to capture the number of rows inserted after executing an SQL insert statement in Databricks (DBR 15.4 LTS). My code is attempting to access the number of inserted rows as follows:
rows_insert = spark.sql(insert_statement).first()['num_inserted_rows']
However, I am receiving the following error:
TypeError: 'NoneType' object is not subscriptable
Upon further investigation, I noticed that in the SQL execution result, the fields num_affected_rows and num_inserted_rows are shown, but no rows are actually returned. This can be seen in the screenshots I've attached:
- The first image shows the result of my SQL insert, which displays the columns num_affected_rows and num_inserted_rows but with no actual rows returned.
- The second image shows the subsequent Python error when I try to access num_inserted_rows from the result.
Has anyone else faced a similar issue? I would greatly appreciate any insights or suggestions on how to work around this problem.
Thanks in advance for your help!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-07-2024 08:34 AM
@Buranapat Can you please try this check as a workaround?
result = spark.sql(insert_statement).first()
if result is not None and 'num_inserted_rows' in result:
num_inserted_rows = result['num_inserted_rows']
else:
num_inserted_rows = 0
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-10-2024 05:04 PM
Thank you for the suggestion! The workaround seems like a helpful way to quickly check insert success, though I’ve noticed that it sometimes returns None even when records appear to be successfully inserted. Do you know if this is a known issue, or if there are any plans to improve this behavior in future updates?
Thanks again for your help!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-13-2024 09:58 AM
Hey all, commenting here for my own experience.
I am also experiencing the same issue but the problem seems to be Liquid Clustering that is removing all metadata from an inserted rows statement. Whenever I insert rows, it always returns None and some of my error handling fails. Is this a known bug?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
4 weeks ago
Hi,
we are experiencing the same issue. We also turned on liquid clustering on table and we had additional checks on the inserted data information, which was really helpful.
@GavinReeves3 did you manage to solve the issue?
@MuthuLakshmi any idea? 🙂
Thank you in advance
George

