Pinotdb error (pinotdb.exceptions.DatabaseError) {'errorCode': 150... when trying to initialize apache-pinot db from apache-superset

Question

while setting up Apache-Superset (I installed Superset on Ubuntu 22.04 over pypi, for the connector im using pinotdb, to connect Superset to a apache-pinot db.) i encountered following error after executing following command:

superset db upgrade

2024-07-05 08:39:44,620:ERROR:flask_appbuilder.security.sqla.manager:DB Creation and initialization failed: (pinotdb.exceptions.DatabaseError) {'errorCode': 150,
 'message': 'SQLParsingError:\n'
            'org.apache.pinot.sql.parsers.SqlCompilationException: Caught '
            'exception while parsing query: CREATE TABLE ab_permission (\n'
            '\tid NUMERIC NOT NULL, \n'
            '\t"name" VARCHAR NOT NULL, \n'
            '\tPRIMARY KEY (id), \n'
            '\tUNIQUE ("name")\n'
            '...\n'
            'Caused by: org.apache.pinot.sql.parsers.parser.ParseException: '
            'Encountered "" at line 1, column 1.\n'
            '\tat '
            'org.apache.pinot.sql.parsers.parser.SqlParserImpl.generateParseException(SqlParserImpl.java:38633)\n'
            '\tat '
            'org.apache.pinot.sql.parsers.parser.SqlParserImpl.jj_consume_token(SqlParserImpl.java:38430)\n'
            '\tat '
            'org.apache.pinot.sql.parsers.parser.SqlParserImpl.SqlStmt(SqlParserImpl.java:931)\n'
            '\tat '
            'org.apache.pinot.sql.parsers.parser.SqlParserImpl.SqlStmtsEof(SqlParserImpl.java:1017)'}
[SQL:
CREATE TABLE ab_permission (
        id NUMERIC NOT NULL,
        "name" VARCHAR NOT NULL,
        PRIMARY KEY (id),
        UNIQUE ("name")
)

]
(Background on this error at: https://sqlalche.me/e/14/4xp6)

the error also occurs when trying following commands: superset init or superset fab create-admin

Answer 1

Superset allows configuration of two types of database connection:

• the metadata database (Superset's internal configuration, such as users and persmissions), which is configured in config.py
• databases for querying data for datasets and dashboards, which are configured in the database settings UI.

Supported metadata backends are SQLite, MySQL and PostgreSQL. It looks as if you have configured pinotdb as the metadata database.

I would suggest leaving the metadata database URL as its default value in config.py to begin with:

# to enforce single-threaded access, which may be problematic in some edge cases
SQLALCHEMY_DATABASE_URI = 'sqlite:////path/to/superset.db?check_same_thread=false'

And configuring the pinotdb connection via the UI once superset is up and running.