Can't query with Superset meta database

[kalimalrazif]

Hi,

I was able to add the metadata database and named it "meta", but now I try to do the example from :

SELECT * FROM "meta.examples%2Ebirth_names";

And it gives me this error:

Superset meta database Error
superset error: Unsupported table: meta.examples%2Ebirth_names


This may be triggered by:
Issue 1002 - The database returned an unexpected error. 

See less

Examples db is included at allowed_dbs

[dosubot[bot]]

To resolve the error "Unsupported table: meta.examples%2Ebirth_names" when querying the Superset meta database, ensure that the meta database has access to the required tables. You can do this by configuring the allowed databases in the database configuration.

Add the following configuration under "Advanced" -> "Other" -> "ENGINE PARAMETERS":

{"allowed_dbs":["examples"]}

This will allow the meta database to access the examples database, which includes the birth_names table ^[1].

To continue talking to Dosu, mention @dosu.

[kalimalrazif]

@dosu, already did that, this is my "ENGINE PARAMETERS" on meta database:

{"allowed_dbs":["examples","peering_data","peering_support"]}

[dosubot[bot]]

Ensure that the "birth_names" table is properly loaded in the "examples" database. You can use the load_birth_names function from the superset/examples/birth_names.py script to load the dataset:

def load_birth_names(
    only_metadata: bool = False, force: bool = False, sample: bool = False
) -> None:
    """Loading birth name dataset from a zip file in the repo"""
    database = get_example_database()
    with database.get_sqla_engine() as engine:
        schema = inspect(engine).default_schema_name

    tbl_name = "birth_names"
    table_exists = database.has_table(Table(tbl_name, schema))

    if not only_metadata and (not table_exists or force):
        load_data(tbl_name, database, sample=sample)

    table = get_table_connector_registry()
    obj = db.session.query(table).filter_by(table_name=tbl_name, schema=schema).first()
    if not obj:
        print(f"Creating table [{tbl_name}] reference")
        obj = table(table_name=tbl_name, schema=schema)
        db.session.add(obj)

    _set_table_metadata(obj, database)
    _add_table_metrics(obj)

    slices, _ = create_slices(obj)
    create_dashboard(slices)

Make sure to run this function to ensure the "birth_names" table is available in the "examples" database ^[1].

Additionally, verify that the table and its columns are correctly defined and loaded in the "examples" database. This should resolve the error when querying the "examples.birth_names" table.

To continue talking to Dosu, mention @dosu.

[kalimalrazif]

@dosu I can query the birth_names table from SQL Lab and see the data without problem.

[dosubot[bot]]

Since you can query the birth_names table from SQL Lab without any issues, the problem might be related to the configuration of the Superset meta database itself.

Ensure that the ENABLE_SUPERSET_META_DB feature flag is set to true in your Superset configuration. Here are the steps to verify and configure this:

1. Enable the Superset Meta Database:
   Set the ENABLE_SUPERSET_META_DB feature flag to true in your Superset configuration file (usually superset_config.py).

   FEATURE_FLAGS = {
       "ENABLE_SUPERSET_META_DB": True,
   }

2. Add a New Database of Type "Superset Meta Database":
   Ensure that you have added a new database with the SQLAlchemy URI "superset://".

3. Configure Allowed Databases:
   In the database configuration, under "Advanced" -> "Other" -> "ENGINE PARAMETERS", add the allowed databases. Your configuration should look like this:

   {
       "allowed_dbs": ["examples", "peering_data", "peering_support"]
   }

By following these steps, you should be able to query the meta.examples%2Ebirth_names table without encountering the "Unsupported table" error ^[1].

To continue talking to Dosu, mention @dosu.

[kalimalrazif]

@dosu already did that too.

I was able to create the Superset Meta database without problems following those same instructions.

[dosubot[bot]]

@rusackas, this one's a bit out of my wheelhouse, could you step in and provide your guidance?

To continue talking to Dosu, mention @dosu.

[kalimalrazif]

Sorry I forgot to mention that the version I am using is 4.0.2

[rusackas]

You might have a few things going on here...

First is the %2e... that should probably just be a .
Second, f you do so, the pattern is databaseName.schemaName.tableName so if your database is called "meta" and your schema is "examples" then what you have ought to work. For most folks, it's examples.public.birth_names in this case.
Third, if you have the above all correct, you probably don't want quotes around it.

[kalimalrazif]

The meta database is the Superset Meta DB, I am following this documentation for Querying across databases

[rusackas]

Oh, then @betodealmeida might have more insight here.

[kalimalrazif]

Hello, I did a test and it worked!!!!

In my query I only removed the meta. part and it worked like a charm.

But now I have another issue when I query the database I need, it gives me the following error :-( the database is druid:

Superset meta database Error
superset error: 'str' object has no attribute 'tzinfo'

See more

This is the table schema:

__time : TIMESTAMP
asn : BIGINT
asn_name : VARCHAR
calculated_throughput : DOUBLE
delta_sec : BIGINT
int_name : VARCHAR
interface : VARCHAR
ip : VARCHAR
name : VARCHAR
router_ip : VARCHAR
time_zone : VARCHAR
traffic_dir : VARCHAR
volume : BIGINT

[rinaldo-urbint]

@kalimalrazif Are you able to query other database with the same format as well? I'm facing the same issue, and the documentation doesn't help. 🙁 I've tried the following variations, and I'm still unable to connect to my postgresql table (which works fine via sql-lab when I run it separately with that db selected instead of the meta db)

• examples.birth_names works fine.
• examples%2Ebirth_names throws the error superset error: Unsupported table: examples%2Ebirth_names
• meta.examples.birth_names throws the error superset error: Unsupported table: meta.examples.birth_names
• my_db.my_table throws the error superset error: Database not found: my_db
• my_db%2Emy_table, meta.my_db.my_table, meta.my_db%2emy_table throws the error superset error: Unsupported table: ...

Any help would be appreciated. Thanks!

[rinaldo-urbint]

For anyone else going through the same issue as mine above - It should be my_db_display_name.my_schema.my_table which works!

You don't have to use %2E or the meta database connection name. Just straightaway query the schema and table, but specify your database connection name (the one that you already created on the Database connections earlier). Don't use the database name. Use the display name you used.