Hi @szymon_dybczak
I'm not sure why my question looks like this, I thought I fixed it.
I've connected to Netsuite via Suite Analytics JDBC connection in a Databricks cluster. I'm seeing a NullPointerException because of the lastmodifieddate column in my netsuite classification table (ie. if I do Select * from classification I see the NullPointerException and I've narrowed it down to seeing this issue because of the lastmodifieddate TIMESTAMP column), I noticed the error was occuring at this column after writing out each column in the select statement.
I"ve tested with other tables as well (entity, department…), and this seems to be happening with all TIMESTAMP columns not just lastmodifieddate.
Also , that when I query the classification table in dbeaver I see NO null rows and I see no errors when using the below test queries. If I add coalesce or cast or etc. to lastmodifieddate field I see the "failed to retrieve data" error below:
(Note I have some test code commented out )
This is my code:
jdbc_url = "jdbc:ns://x.connect.api.netsuite.com:1708;ServerDataSource=NetSuite2.com;Encrypted=1;NegotiateSSLClose=false;CustomProperties=(AccountID=x;RoleID=1030)"
driver = "com.netsuite.jdbc.openaccess.OpenAccessDriver"
secret_scope = "fl-da-kv-app-scope"
secret_key_password = "netsuite-password"
user = "user"
password = dbutils.secrets.get(scope=secret_scope, key=secret_key_password)
netsuite_query = """
SELECT
lastmodifieddate
# CAST(lastmodifieddate AS VARCHAR(255)) AS lastmodifieddate_str
# COALESCE(lastmodifieddate, TO_DATE('1900-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) AS lastmodifieddate
FROM "Fl - Accountant".classification
"""
# netsuite_query = """
# SELECT
# custrecord_nspbcs_class_planning_cat,
# custrecord_lmry_class_code,
# externalid,
# fullname,
# id,
# includechildren,
# isinactive,
# name,
# parent,
# subsidiary
# FROM "Fl - Accountant".classification
# """
# netsuite_query = """SELECT custrecord_lmry_class_code FROM "Fl- Accountant".classification"""
# netsuite_query = """
# SELECT
# COALESCE(custrecord_lmry_class_code, 'a') AS custrecord_lmry_class_code,
# COALESCE(custrecord_nspbcs_class_planning_cat, 0) AS custrecord_nspbcs_class_planning_cat,
# id as id
# FROM "Fl - Accountant".classification
# """
output_path_csv = "/tmp/netsuite_classification_full1.csv"
# output_path_delta = "/tmp/delta/netsuite_classification"
try:
print("Attempting to read full table from NetSuite...")
print(f"Executing query: {netsuite_query}")
df = spark.read \
.format("jdbc") \
.option("url", jdbc_url) \
.option("query", netsuite_query) \
.option("user", user) \
.option("password", password) \
.option("driver", driver) \
.load()
print("\nSUCCESS: Read data from NetSuite into a DataFrame.")
# if theres an error w. the count that means the query is wrong.
row_count = df.count()
print(f"SUCCESS: Read {row_count} rows from NetSuite... ")
# df.show(5)
print(f"\nAttempting to write data to CSV at: {output_path_csv}")
# df_cleaned = df.na.fill('')
# print('cleaned the df')
df.write \
.format("csv") \
.option("header", "true") \
.mode("overwrite") \
.save(output_path_csv)
print(f"SUCCESS: Wrote data to CSV.")
# # --- Write to Delta Table ---
# print(f"\nAttempting to write data to Delta table at: {output_path_delta}")
# df.write \
# .format("delta") \
# .mode("overwrite") \
# .save(output_path_delta)
# print(f"SUCCESS: Wrote data to Delta table.")
except Exception as e:
print("\nERROR: An exception occurred.")
# The full error will be raised, giving you the complete stack trace.
raise e
