Is DBFS required when connecting to Databricks?

[AnoopShahi]

Hello,
So I was running cs_tools searchable metadata for databricks in a unity catalog enabled environment and ran into some permission errors. The code below (FROM 'dbfs:/FileStore/cs_tools/TMP_STAGE_ts_cluster_a36e3.csv') references dbfs, and after speaking to someone that mangaes databricks from our side wanted to know if this is required for cs_tools to run. If it is, where can we find at what step the connection is established in the whole process.

DatabaseError: (databricks.sql.exc.ServerOperationError) [INSUFFICIENT_PERMISSIONS] Insufficient privileges:
User does not have permission SELECT on any file. SQLSTATE: 42501
[SQL:
COPY INTO ts_cluster
FROM 'dbfs:/FileStore/cs_tools/TMP_STAGE_ts_cluster_a36e3.csv'
FILEFORMAT = CSV
FORMAT_OPTIONS (
'header' = 'true',
'inferSchema' = 'true',
'delimiter' = '|'
)
COPY_OPTIONS ('mergeSchema' = 'true')
]

[justin-gerolami]

Does the user who generated the API key for Databricks have select privileges on the catalog/schema used for storing the metadata? I also looked at getting Databricks working. It looks like DBFS approach is the only way which is unfortunate. I think I'm going to just use the REST API instead.

[AnoopShahi]

Hey Justin, no the user does not, I tried to get access but alot of hoops to jump through.

[boonhapus]

@AnoopShahi @justin-gerolami can you try adding use_legacy_dataload=true to your syncer definitions?

This will fall back to JDBC-style inserts instead of using the dbfs API + upsert strategy. It's much slower.. but it should be functional.

[AnoopShahi]

@boonhapus Awesome this works! Also does adding use_legacy_dataload = true only work in Unity Catalog environment? Asking because using it in a non UC enabled environment is a bit easier within my team.

[boonhapus]

@AnoopShahi this should work in hive metastore too, but it's a considerably slower implementation.

---

The newer approach uses a few dbfs apis to PUT / stage data to /FileStore/cs_tools/TMP_STAGE_{{ tablename }}_{{ some_random_uuid }}.csv temporarily (https://docs.databricks.com/aws/en/files#do-i-need-to-provide-a-uri-scheme-to-access-data).

• https://docs.databricks.com/api/workspace/dbfs/create
• https://docs.databricks.com/api/workspace/dbfs/addblock
• https://docs.databricks.com/api/workspace/dbfs/close

Then runs a MERGE INTO {{ tablename }} or COPY INTO {{ tablename }} statement, depending on if you are using load_strategy=UPSERT or not, respectively.