ydb-platform / ydb-sqlalchemy

YQL Dialect for SQLAlchemy
Apache License 2.0
18 stars 5 forks source link

Add ydb_table_path_prefix parameter #44

Closed LuckySting closed 2 months ago

LuckySting commented 2 months ago

Problem

Now the dialect supports only flat structure of tables, when YDB provides a powerful filesystem-like approach to organize tables.

Proposal

Add an engine level parameter, which specifies a common tables prefix using "PRAGMA", which allows several application to work with same database, but in different folders.

Example

engine = sa.create_engine(url, connect_args={"ydb_table_path_prefix": "/cluster/database/some_dir/nested_dir"})

table = sa.Table("table", sa.MetaData(), sa.Column("id", sa.Integer, primary_key=True))

abs_table = sa.Table("/cluster/local/table", sa.MetaData(), sa.Column("id", sa.Integer, primary_key=True))

table.create(engine)

is rendered as

PRAGMA TablePathPrefix = "/cluster/database/some_dir/nested_dir";

CREATE TABLE `table`(id Int64, PRIMARY KEY id); --> /cluster/database/some_dir/nested_dir/table

CREATE TABLE `/cluster/local/table`(id Int64, PRIMARY KEY id); --> /cluster/local/table