[SIP] Ability to connect to postgres with different schema with superset_config #29582
Comments
|
I successfully connected to another schema using: SQLALCHEMY_ENGINE_OPTIONS = {
'connect_args': {'options': '-csearch_path=newtest'}
}but now I recieve: |
|
Hey there, @justmike1! I'm Dosu, a bot here to help you with bugs, questions, and becoming a contributor. Let's squash those bugs together! 🤖 To resolve the error "relation 'ab_user' does not exist" when trying to connect to another schema using SQLAlchemy, you need to ensure that the schema is correctly specified in your SQLAlchemy connection string or in your query. The Here is an example of how you can specify the schema in your SQLAlchemy connection: from sqlalchemy import create_engine, MetaData, Table
# Example connection string with schema specified
engine = create_engine('postgresql://user:password@localhost/dbname')
# Reflect the tables from the specified schema
metadata = MetaData(schema='your_schema_name')
ab_user_table = Table('ab_user', metadata, autoload_with=engine)
# Now you can use the ab_user_table for your queries
with engine.connect() as connection:
result = connection.execute(ab_user_table.select())
for row in result:
print(row)In this example, replace Additionally, ensure that the sqlatable_user = sa.Table(
"sqlatable_user",
sa.MetaData(),
sa.Column("id", sa.Integer, primary_key=True),
sa.Column("user_id", sa.Integer, sa.ForeignKey("ab_user.id")),
sa.Column("table_id", sa.Integer, sa.ForeignKey("tables.id")),
)
druiddatasource_user = sa.Table(
"druiddatasource_user",
sa.MetaData(),
sa.Column("id", sa.Integer, primary_key=True),
sa.Column("user_id", sa.Integer, sa.ForeignKey("ab_user.id")),
sa.Column("datasource_id", sa.Integer, sa.ForeignKey("datasources.id")),
)Make sure that these relationships are properly set up in your database schema to avoid any issues with missing relations [1][2]. |
justmike1
changed the title
|
@justmike1 the error you encountered To resolve this, you should connect to your database and move the tables from the export FLASK_APP=superset
superset db upgrade
superset init |
|
@Habeeb556 but why they were created in public in the first place that’s what I am wondering? As I have deployed a clean deployment. I can’t do this in production as I don’t have access to public schema and need superset to connect to another one I will create for it |
|
@justmike1 I believe this is because it's the default schema, and the Superset deployment is based on it without specifying a schema. The question is, why do you need to change the schema if you don't have access to the database? What difference would it make for you? |
|
We have an internal policy which we can't use the default schema, which is public in postgres, so I need to be able to run migrations and connect and run on a schema which is the name of the applicaiton, so it will look like: develop PSQL -> bitools (database name) -> superset (schema name) |
|
I tried simulating this setup, and it worked for me using the following configuration in # Metadata Repository
SQLALCHEMY_DATABASE_URI = 'postgresql://username:password@localhost/databasename?options=-c%20search_path=schemaname'Ensure you connect to the database and create the new schema: \c databasename
CREATE SCHEMA schemaname;
GRANT ALL ON SCHEMA schemaname TO username;Then, run the following commands to upgrade and initialize the new configuration on this schema: export FLASK_APP=superset
superset db upgrade
superset initThis will initialize a new configuration, not migrate from the old schema to the new schema. If you want to migrate production data, I recommend setting the new schema name in ALTER SCHEMA schema_name RENAME TO new_name;
|
|
@Habeeb556 Great, thanks! currently it is running in testing environment which we are looking to move to production, thats how I found this need, thanks! Is it possible to run the migration in different user as well? I have also a production requirement to run the migrations in a user which can create/delete tables, but another runs on runtime which can write/read on the tables |
|
You're welcome. If it's resolved, we can close this for now and continue with thread #29570. |
|
Removing the SIP label, and we won't number this since it's effectively solved. Thanks again @Habeeb556, keep up the great work! |
Please make sure you are familiar with the SIP process documented
here. The SIP will be numbered by a committer upon acceptance.
[SIP] Proposal for Ability to connect to postgres with different schema with superset_config
Motivation
I need to connect to the same database as my application but use a different schema name than public
Proposed Change
Currently we have:
SQLALCHEMY_DATABASE_URI = f"postgresql+psycopg2://{env('DB_USER')}:{env('DB_PASS')}@{env('DB_HOST')}:{env('DB_PORT')}/{env('DB_NAME')}"
I suggest adding:
SQLALCHEMY_DATABASE_CONNECT_ARGS = {}
New or Changed Public Interfaces
NONE
New dependencies
NONE
Migration Plan and Compatibility
NONE as default would be
{}Rejected Alternatives
NONE
The text was updated successfully, but these errors were encountered: