cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Row INSERT into table does not persist

burusam
New Contributor

I have this script: 

 

from databricks import sql
import os
import pandas as pd

databricksToken = os.environ.get('DATABRICKS_TOKEN')

connection = sql.connect(server_hostname = "",
                 http_path       = "",
                 access_token    = databricksToken)

def databricks_write(name, racf, action):
  global connection  # Use the global connection variable
  cursor = connection.cursor()
  df = pd.DataFrame({'operatorName': name, 'racf': racf, 'action': action, 'datetime': [pd.Timestamp.now()]})
  columns = ', '.join(df.columns)
  print(columns)
  values = ', '.join([f"'{value}'" for value in df.values[0]])
  print(values)
  query = f"INSERT INTO okta_log.log ({columns}) VALUES ({values})"
  cursor.execute(query)
  connection.commit()

 

 

 This does successfully add a row to the table and I can see subsequent rows also getting added after running this function each time. But when I reload the table and run the select * again I don't see my rows of data. It seems that the row of data persists only for a short duration and is not permanent

How do I fix this?

2 REPLIES 2

Emil_Kaminski
Contributor

HI, 

are you closing connection at the end?

cursor.close()
connection.close()

Also, can you elaborate what you mean by saying "when I reload the table"

Cheers

** You might also want to subscribe to Warsaw Databricks YT channel: https://www.youtube.com/channel/UC1-u_2nI97cNHtu_FQ3HP_A

Kaniz_Fatma
Community Manager
Community Manager

Hi @burusam , Your script successfully adds rows to the table, but the data doesn’t seem to persist permanently. 

 

Let’s address this issue:

 

Temporary vs. Permanent Tables:

  • The behavior you’re observing might be due to the type of table you’re using.
  • By default, temporary tables in Databricks are session-specific and exist only for the duration of the session. They don’t persist across different sessions or after the session ends.

Solution: Use a Permanent Table:

  • To make your data persist permanently, consider using a permanent table (also known as a Delta Lake table).

Benefits of Delta Lake:

  • Delta Lake provides features like ACID transactions, schema evolution, and time travel.
  • It ensures data consistency and durability, making it suitable for long-term data storage.

 

Connect with Databricks Users in Your Area

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