Read sqlite db file in Databricks using PySpark, facing path not exists error in Community edition Databricks

Question

I tried reading with the following code :

df = spark.read.format('jdbc') \
          .options(driver='org.sqlite.JDBC', dbtable='employees',
                   url='jdbc:sqlite:/dbfs/FileStore/tables/chinook-1.db').load()

Its throwing the error like :

java.sql.SQLException: path to '/FileStore/tables/chinook-1.db': '/FileStore' does not exist

I have tried to give different methods of giving filepaths like :

/FileStore/tables/chinook-1.db 
dbfs:/FileStore/tables/chinook-1.db

dosenot work ,giving the same error.

Answer 1

The community edition doesn't support DBFS fuse, so you can't use /dbfs file path. To access your sqlite database you need to copy file to the local disk using dbutils and then access it from there using the local file path:

dbutils.fs.cp("dbfs:/FileStore/tables/chinook-1.db", "file:/tmp/chinook-1.db")

df = spark.read.format('jdbc') \
      .options(driver='org.sqlite.JDBC', dbtable='employees',
      url='jdbc:sqlite:/tmp/chinook-1.db').load()