topic Re: Error in Data Engineering 2.2 notebook when executing SQL statement in Training offerings

Error in Data Engineering 2.2 notebook when executing SQL statement

leandrob13 — Wed, 19 Jul 2023 23:46:55 GMT

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...

Re: Error in Data Engineering 2.2 notebook when executing SQL statement

jonathanchcc — Fri, 02 Feb 2024 16:22:49 GMT

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

Re: Error in Data Engineering 2.2 notebook when executing SQL statement

jonathanchcc — Wed, 14 Feb 2024 01:56:36 GMT

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

Re: Error in Data Engineering 2.2 notebook when executing SQL statement

Dibs — Fri, 30 Aug 2024 01:57:01 GMT

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"

)

Re: Error in Data Engineering 2.2 notebook when executing SQL statement

hebied — Fri, 25 Oct 2024 07:24:09 GMT

Thanks ..It really helped.