SparkR or sparklyr not showing history

[Sagas]

Hi,

for some reason Azure Databricks doesn't show History if the data is saved with SparkR (2 in the figure below) or Sparklyr (3), but it does show it with Data Ingestion (0) or with PySpark (1). Is this a known bug or am I doing something wrong? Is it possible to save data with R while getting the UserId and Username?

PySpark code:

test_data = [[4,"d",30],[5,"e",70]]
df = spark.createDataFrame(test_data,['a','b','c']) df.write.mode('append').format("delta").saveAsTable("catalog.schema.table")

SparkR df and save to table:

Sparklyr df and save to table:

[Kaniz]

Hi @Sagas, Let’s address your questions regarding Azure Databricks, SparkR, and Sparklyr.

1. History in Azure Databricks:

   • Each operation that modifies a Delta Lake table creates a new table version. You can use history information to audit operations, rollback a table, or query a table at a specific point in time using time travel.
   • To retrieve information about operations, users, and timestamps for each write to a Delta table, you can run the history command. The operations are returned in reverse chronological order.
   • The history schema includes columns such as version, timestamp, userId, userName, operation, operati....
   • Keep in mind that Databricks does not recommend using Delta Lake table history as a long-term backup solution for data archival. It’s best suited for auditing and time travel purposes¹.

2. SparkR and Sparklyr:

   • SparkR:
     • SparkR provides functions for SparkSQL tables and Spark DataFrames. It doesn’t translate dplyr functions into SQL query plans like sparklyr does.
     • You can use SparkR to save data to Delta tables, but it won’t automatically capture the userId and userName.
   • Sparklyr:
     • Sparklyr translates dplyr functions (e.g., arrange) into SQL query plans used by SparkSQL.
     • When using sparklyr, you’ll see Spark progress bars in the notebook after each command that triggers Spark jobs.
     • You can combine SparkR and sparklyr in the same notebook or job. However, be cautious about potential masking of functions between the two²³.

3. Saving Data with R and Capturing UserId/Username:

   • To save data with R while capturing the userId and userName, consider the following approach:
     • Use SparkR or sparklyr to save data to a Delta table.
     • Before saving, manually add a column for userId and userName (e.g., df$userId <- "your_user_id").
     • Ensure that you populate these columns with appropriate values.
     • Save the modified DataFrame to the Delta table.
   • This way, you can associate the data with the user who performed the operation.

Remember to adjust your approach based on your specific requirements and security considerations. If you encounter any issues, feel free to seek further assistance or explore additional resources¹²³.

[Sagas]

Thank you for your response! I'll consider capturing UserId for each row. But do you mean that also sparklyr can't automatically capture the userId for the Delta table history?