topic Re: Error when accessing 'num_inserted_rows' in Spark SQL (DBR 15.4 LTS) in Data Engineering

Error when accessing 'num_inserted_rows' in Spark SQL (DBR 15.4 LTS)

Buranapat — Tue, 01 Oct 2024 03:00:07 GMT

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!

Re: Error when accessing 'num_inserted_rows' in Spark SQL (DBR 15.4 LTS)

MuthuLakshmi — Thu, 07 Nov 2024 16:34:27 GMT

@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

Re: Error when accessing 'num_inserted_rows' in Spark SQL (DBR 15.4 LTS)

Buranapat — Mon, 11 Nov 2024 01:04:47 GMT

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!

Re: Error when accessing 'num_inserted_rows' in Spark SQL (DBR 15.4 LTS)

GavinReeves3 — Fri, 13 Dec 2024 17:58:49 GMT

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?

@MuthuLakshmi

Re: Error when accessing 'num_inserted_rows' in Spark SQL (DBR 15.4 LTS)

GeorgeP1 — Tue, 28 Jan 2025 11:24:48 GMT

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