Comments (6)
According to error from ClickHouse server you should use different label for the second column with name "user_id".
from clickhouse-sqlalchemy.
I'm blocked too. That seems an anomaly cause as in the nature of sqlalchemy, I'm not supposed to use labellization for the join.
Here is the error that I'm getting:
Received exception from server (version 20.1.2):
Code: 352. DB::Exception: Received from localhost:XXXX. DB::Exception: Column 'ref_id' is ambiguous.
from clickhouse-sqlalchemy.
OK. Please provide sample of code that reproduces the problem.
from clickhouse-sqlalchemy.
Here is the code that does the job.
class Equipments(Base):
"""
Clickhouse model for Equipments
"""
__tablename__ = "equipments"
id = Column(types.Int64, primary_key=True)
product_id = Column(types.Int64, nullable=False)
ref_id = Column(types.Int64, nullable=False)
creation_date = Column(types.DateTime, nullable=False)
__table_args__ = (engines.MergeTree(partition_by=product_id, primary_key=id, order_by=(id, creation_date)),)
class References(Base):
"""
Clickhouse model for References
"""
__tablename__ = "references"
ref_id = Column(types.Int64, primary_key=True)
ref_name = Column(types.Nullable(types.String))
__table_args__ = (engines.MergeTree(partition_by=ref_id, primary_key=ref_id, order_by=(ref_id)),)
The file, which contains the base object:
import os
from contextlib import contextmanager
from clickhouse_sqlalchemy import make_session
from sqlalchemy import create_engine, MetaData
from sqlalchemy.ext.declarative import declarative_base
CLICK_HOUSE_HOST = os.getenv("CLICK_HOUSE_HOST", "XXXX")
CLICK_HOUSE_USER = os.getenv("CLICK_HOUSE_USER", "XXXX")
CLICK_HOUSE_PASSWORD = os.getenv("CLICK_HOUSE_PASSWORD", "XXXX")
CLICK_HOUSE_DB_NAME = os.getenv("CLICK_HOUSE_DB_NAME", "XXXX")
CLICK_HOUSE_URI = f"clickhouse://{CLICK_HOUSE_USER}:{CLICK_HOUSE_PASSWORD}@{CLICK_HOUSE_HOST}/{CLICK_HOUSE_DB_NAME}"
# SQLAlchemy configuration
ENGINE = create_engine(CLICK_HOUSE_URI)
METADATA = MetaData(bind=ENGINE)
Base = declarative_base(metadata=METADATA)
@contextmanager
def get_db_session():
"""
Return a thread-safe new DB session.
"""
session = make_session(ENGINE)
yield session
session.close()
How to reproduce it:
from sqlalchemy.orm import Session
def get_value(session: Session):
res = (
session.query(Equipments, References.ref_name)
.join(References, Equipments.ref_id == References.ref_id)
.all()
)
return res
if __name__ == "__main__":
with get_clickhouse_db_session() as session:
res = get_value(session)
Here is the query that is runned when I execute my code with clickhouse_sqlalchemy
SELECT
id AS equipments_id,
product_id AS equipments_product_id,
ref_id AS equipments_ref_id,
creation_date AS equipments_creation_date,
ref_name AS references_ref_name
FROM equipments
INNER JOIN references ON ref_id = ref_id
Received exception from server (version 20.1.2):
Code: 352. DB::Exception: Received from localhost:XXXX. DB::Exception: Column 'ref_id' is ambiguous.
from clickhouse-sqlalchemy.
@kdiri thanks for detailed clarification. The thing was while joining on columns with the same name.
Fix is pushed into latest master.
Columns are now prefixed with table name.
from clickhouse-sqlalchemy.
@xzkostyan That's awesome. Thanks for the fix.
from clickhouse-sqlalchemy.
Related Issues (20)
- Alembic migration autogenerate not working HOT 2
- Table name included in CRUD update while ClickHouse does not accept it
- Bulk update fails on ClickHouse
- Native driver fails with stream results
- Alembic couldn't create table alembic_version HOT 14
- Collate is not generating a correct query
- Error connecting with the database when password contains a special character (+%...) with native engine. HOT 1
- Does `clickhouse-sqlalchemy` 3.0.0 support `sqlalchemy` of the version 1.4.*? HOT 5
- Create a cluster table orm class with engines.Distributed which has a logs attribute, how to use variable to indicate it?
- alembic does not autogenerate engine for clickhouse table HOT 4
- Patreon does not work HOT 1
- Add support for Date32 HOT 2
- Handling Clickhouse Alembic Migrations for Clickhouse cluster HOT 2
- Support/example for creating views HOT 2
- `create_all` for all the `MaterializedView`
- With chdb this clickhouse downsized memory database, can clickhouse-sqlalchemy support it or not?
- Support VariantType
- Table reflection for DateTime64 timezone will be extra quoted
- Alembic_Version Records disappered after clickhouse alembic migration HOT 5
- using http mode, connecting database failed when account password ends with @ HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from clickhouse-sqlalchemy.