01-12-2022 11:45 PM
Consider we have two tables A & B.
qry = """
INSERT INTO Table A
Select * from Table B where Id is null
"""
spark.sql(qry)
I need to get the number of records inserted after running this in databricks.
01-14-2022 02:52 AM
@@ROWCOUNT is rather T-SQL function not Spark SQL. I haven't found something like that in documentation but there is other way as every insert anyway return num_affected_rows and num_inserted_rows fields.
So you can for example use
df.first()['num_inserted_rows']
or subquery and select in sql syntax.
I am including example screenshots.
01-13-2022 04:39 AM
01-13-2022 05:06 AM
Hi @Kaniz Fatma ,
I have tried the way you have mentioned but it still throws an error.
01-13-2022 05:46 AM
Hi ,
My requirement here is I will be creating a function using Python code to perform insert operation to a Delta table , that is why I am running it in an Python cell.
I will be passing a table name to that function and I need to get the number of records inserted into the table once the function is executed.
So any solution to achieve this?
01-14-2022 02:52 AM
@@ROWCOUNT is rather T-SQL function not Spark SQL. I haven't found something like that in documentation but there is other way as every insert anyway return num_affected_rows and num_inserted_rows fields.
So you can for example use
df.first()['num_inserted_rows']
or subquery and select in sql syntax.
I am including example screenshots.
01-14-2022 06:21 AM
Hi @Hubert Dudek
Your approach is working for me.
Thank you.
01-14-2022 06:54 AM
Great! Please when you can select as best answer.
06-14-2023 03:38 PM
@Hubert Dudek, when I execute a similar piece of code in VSCode executed through databricks-connect, the dataframe contains 1 row with no columns, which is a problem. Executing the same code in a notebook on the same cluster works as you stated. Is this possibly a bug in databricks-connect?
06-15-2023 01:27 AM
Almost same advice than Hubert, I use the history of the delta table :
df_history.select(F.col('operationMetrics')).collect()[0].operationMetrics['numOutputRows']
You can find also other 'operationMetrics' values, like 'numTargetRowsDeleted'.
Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.
If there isn’t a group near you, start one and help create a community that brings people together.
Request a New Group