Error in Data Engineering 2.2 notebook when executing SQL statement

[leandrob13]

This notebook is plagued with errors. After solving the one described here I ran into another one when trying to execute the SQL statement in the "Extracting Data from SQL Databases" section:

DROP TABLE IF EXISTS users_jdbc;

CREATE TABLE users_jdbc
USING JDBC
OPTIONS (
  url = "jdbc:sqlite:${DA.paths.ecommerce_db}",
  dbtable = "users"
)

I get this error:

Error in SQL statement: SQLException: path to '/dbfs/mnt/dbacademy-users/{my-user}/data-engineer-learning-path/ecommerce.db': '/dbfs/mnt' does not exist

After commenting the portion of the code shown below:

# Move the temp db to the final location
dbutils.fs.mv(f"file:{db_temp_path}", DA.paths.ecommerce_db)
DA.paths.ecommerce_db = DA.paths.ecommerce_db #.replace("dbfs:/", "/dbfs/")

I get this error: 

Error in SQL statement: SQLException: path to 'dbfs:/mnt/dbacademy-users/{my-user}/data-engineer-learning-path/ecommerce.db': '/databricks/driver/dbfs:' does not exist

I have no idea how to debug this one. If anyone knows how to solve this, I will really appreciate it. 

Databricks should update this notebooks...

[jonathanchcc]

I have the same issue any tip to resolve the issue?

[jonathanchcc]

Hello @leandrob13  I have found a post that helped me with this link 

It seems that the Databricks community edition has some limitations

"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:"

This is my code

/dfs dbutils.fs.cp("dbfs:/mnt/dbacademy-users/my-user/data-engineer-learning-path/ecommerce.db", "file:/tmp/ecommerce.db")

 And then modify the load of data 

DROP TABLE IF EXISTS users_jdbc;

CREATE TABLE users_jdbc
USING JDBC
OPTIONS (
  url = "jdbc:sqlite:/tmp/ecommerce.db",
  dbtable = "users"
)

[Dibs]

I had to change to 

%python

dbutils.fs.cp("dbfs:/mnt/dbacademy-users/YOURUSERID/data-engineering-with-databricks/ecommerce.db", "file:/tmp/ecommerce.db")

DROP TABLE IF EXISTS users_jdbc;

CREATE TABLE users_jdbc

USING JDBC

OPTIONS (

url = "jdbc:sqlite:/tmp/ecommerce.db",

dbtable = "users"

)

[hebied]

Thanks ..It really helped.