shillelagh error: unable to open database file when using shillelagh

[zinginging]

using shillelagh in docker demo from apache/superset:2.1.0

install shillelagh dep in superset.Dockerfile

FROM apache/superset:2.1.0
USER root
RUN pip install 'shillelagh[all]'
USER superset

# build image
docker build . -t superset:0.1test -f ./superset.Dockerfile

# run 
docker run -d -p 8088:8088 -e "SUPERSET_SECRET_KEY=123456" --name superset superset:0.1test

# Setup local admin account
docker exec -it superset superset fab create-admin \
              --username admin \
              --firstname Superset \
              --lastname Admin \
              --email admin@superset.com \
              --password admin

# Migrate local DB to latest
docker exec -it superset superset db upgrade

# Setup roles
docker exec -it superset superset init

config shillelagh in superset

display name: Shillelagh
sqlallchemy uri: shillelagh+safe://

Advanced -> Other -> Engine Parameters
{
  "connect_args": {
    "adapters": [
      "datasetteapi",
      "genericjsonapi",
      "githubapi",
      "htmltableapi",
      "s3selectapi",
      "socrataapi",
      "weatherapi"
    ]
  }
}

run sql in SQLlab

SELECT
 date, administered_dose1_recip_4
FROM "https://data.cdc.gov/resource/unsk-b7fc.json"
WHERE location = 'US'
ORDER BY date DESC
LIMIT 10;

Followed the doc https://preset.io/blog/accessing-apis-with-superset/ from @betodealmeida get errors as below. Have you ever had a similar problem as beloow? Thanks a lot.

SupersetErrorsException
Traceback (most recent call last):
File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1517, in full_dispatch_request
rv = self.dispatch_request()
File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1503, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
File "/usr/local/lib/python3.8/site-packages/flask_appbuilder/security/decorators.py", line 95, in wraps
return f(self, *args, **kwargs)
File "/app/superset/views/base_api.py", line 122, in wraps
raise ex
File "/app/superset/views/base_api.py", line 113, in wraps
duration, response = time_function(f, self, *args, **kwargs)
File "/app/superset/utils/core.py", line 1586, in time_function
response = func(*args, **kwargs)
File "/app/superset/views/base_api.py", line 85, in wraps
return f(self, *args, **kwargs)
File "/app/superset/utils/log.py", line 266, in wrapper
value = f(*args, **kwargs)
File "/app/superset/sqllab/api.py", line 256, in execute_sql_query
command_result: CommandResult = command.run()
File "/app/superset/sqllab/commands/execute.py", line 120, in run
raise ex
File "/app/superset/sqllab/commands/execute.py", line 102, in run
status = self._run_sql_json_exec_from_scratch()
File "/app/superset/sqllab/commands/execute.py", line 157, in _run_sql_json_exec_from_scratch
raise ex
File "/app/superset/sqllab/commands/execute.py", line 152, in _run_sql_json_exec_from_scratch
return self._sql_json_executor.execute(
File "/app/superset/sqllab/sql_json_executer.py", line 111, in execute
raise SupersetErrorsException(
superset.exceptions.SupersetErrorsException: [SupersetError(message='shillelagh error: unable to open database file', error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'Shillelagh', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})]
2023-04-17 12:19:46,273:WARNING:superset.views.base:SupersetErrorsException
Traceback (most recent call last):
File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1517, in full_dispatch_request
rv = self.dispatch_request()
File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1503, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
File "/usr/local/lib/python3.8/site-packages/flask_appbuilder/security/decorators.py", line 95, in wraps
return f(self, *args, **kwargs)
File "/app/superset/views/base_api.py", line 122, in wraps
raise ex
File "/app/superset/views/base_api.py", line 113, in wraps
duration, response = time_function(f, self, *args, **kwargs)
File "/app/superset/utils/core.py", line 1586, in time_function
response = func(*args, **kwargs)
File "/app/superset/views/base_api.py", line 85, in wraps
return f(self, *args, **kwargs)
File "/app/superset/utils/log.py", line 266, in wrapper
value = f(*args, **kwargs)
File "/app/superset/sqllab/api.py", line 256, in execute_sql_query
command_result: CommandResult = command.run()
File "/app/superset/sqllab/commands/execute.py", line 120, in run
raise ex
File "/app/superset/sqllab/commands/execute.py", line 102, in run
status = self._run_sql_json_exec_from_scratch()
File "/app/superset/sqllab/commands/execute.py", line 157, in _run_sql_json_exec_from_scratch
raise ex
File "/app/superset/sqllab/commands/execute.py", line 152, in _run_sql_json_exec_from_scratch
return self._sql_json_executor.execute(
File "/app/superset/sqllab/sql_json_executer.py", line 111, in execute
raise SupersetErrorsException(
superset.exceptions.SupersetErrorsException: [SupersetError(message='shillelagh error: unable to open database file', error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'Shillelagh', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})]

[betodealmeida]

It looks like the administered_dose1_recip_4 is no longer there. This query works for me:

SELECT
*
FROM "https://data.cdc.gov/resource/unsk-b7fc.json"
WHERE location = 'US'
ORDER BY date DESC
LIMIT 10;

[betodealmeida]

@JanDoerrenhaus sorry, I didn't have time last week and this week I'm on vacation.

I think the problem is caused because Shillelagh uses SQLite, and even for an in-memory database it still needs access to a directory like /tmp. I suspect the Docker configuration is preventing the file from being created.

Can you try setting the env variable SQLITE_TMPDIR inside Docker and have it point to a directory that exists? Maybe /app/superset/tmp?

[JanDoerrenhaus]

@betodealmeida Very sorry to bother you during your vacation.

I adapted my Dockerfile like so:

FROM apache/superset:latest
USER root
RUN pip install 'shillelagh[all]'
RUN pip install 'holidays==0.25'
RUN mkdir /app/superset/tmp
RUN chown superset /app/superset/tmp
USER superset
ENV SQLITE_TMPDIR=/app/superset/tmp

Unfortunately, that did not change anything about the error.

shillelagh error: unable to open database file

[vigodeltoro]

Hi over there.
I can also verify @JanDoerrenhaus issue. I'm also needing shillelagh running for a PoC.

Superset 3.1.3
shillelagh 1.2.10
holidays 0.25

Running Superset in Docker/Kubernetes

With adding an ENV variable I'm running in the same issue.
I also tested the html function

SELECT *
FROM "https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population"
WHERE "UN Region" = 'Oceania'
LIMIT 5

Same issue..

Any help would be very appreciated :)..
best and thanks

[vigodeltoro]

Hi,

I can confirm that shillelagh is functional without docker. I setup a vm and installed Superset (3.1.3) via PyPi. Shillelagh 1.2.19 was installed automatically. I added pip3 install shillelagh[all].

And tried :
SELECT * FROM "https://data.cdc.gov/resource/unsk-b7fc.json" WHERE location = 'US' ORDER BY date DESC LIMIT 10;

Setting the location of the shillelagh sqlite cache file via sqlite3_temp_directory env variable is also working.

BUT: Everything with SQLITE dev Metadatabase.

If I try to transfer that to productional Superset with Postgres Metadatabase I got the error ( with and without env variable for setting location of cache sqlite file):

shillelagh error: unable to open database file

So, any help would be appreciated

best and thanks

[vigodeltoro]

Was able to reproduce the issue in docker and Postgres meta database. If supererset is not running as root than it can't write the file socrata.cache.sqlite to /app (which seems to be the default). If it's running as root it is working. So it's a permission denied error.

I wasn't able to configure a working env variable for the sqlite tmp files (sqlite3_temp_directory, SQLITE_TMPDIR) yet.

[vigodeltoro]

Okay.. I was able to fix it.. but only with a modified superset docker image.
@JanDoerrenhaus what you have to do is in your docker file:

FROM apache/superset:latest
USER root
RUN pip install 'shillelagh[all]'
RUN pip install 'holidays==0.25'
RUN chown superset:superset /app
USER superset

best regards, hope that helps you..