flaskflask-sqlalchemyapache-supersetsql-parserapache-pinot

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


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


Solution

  • Superset allows configuration of two types of database connection:

    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.