时间戳

首页 > 解决方案 > 使用 MergeTree 引擎 Clickhouse 创建分区键时出错

python-3.x - 使用 MergeTree 引擎 Clickhouse 创建分区键时出错

问题描述

我一直在尝试使用 infi.clickhouse_orm 创建模型，但分区键存在问题

我的模型：

from infi.clickhouse_orm import Model,  UInt16Field, Float32Field, StringField, MergeTree,DateField

class OHLC(Model):

__tablename__ = 'ohlc'

id = UInt16Field()
min = Float32Field()
max = Float32Field()


start_date = DateField()
interval = StringField()

engine = MergeTree(partition_key=['id'])

我得到错误：

DB::Exception：语法错误：.. 设置 index_granularity=8192。预期之一：箭头、标记、非空括号表达式列表

错误

创建我的数据库

""" SqlAlchemy ClickHouse database session maker """

db = Database('test', db_url=os.environ['TEST_CONNECTION'],
              username=os.environ['CLICKHOUSE_USER'], password=os.environ['CLICKHOUSE_PASSWORD'])
db.create_database()
db.create_table(OHLC)

标签： python-3.xdocker-composesqlalchemyclickhouse

解决方案

MergeTree引擎需要在order_by参数中传递的表声明中的主键：

..
engine = MergeTree(partition_key=['id'], order_by=['id'])
..

from infi.clickhouse_orm.engines import MergeTree
from infi.clickhouse_orm.fields import UInt16Field, Float32Field, StringField, DateField
from infi.clickhouse_orm.models import Model
from sqlalchemy import create_engine


class OHLC(Model):
    __tablename__ = 'ohlc'
    id = UInt16Field()
    min = Float32Field()
    max = Float32Field()
    start_date = DateField()
    interval = StringField()
    engine = MergeTree(partition_key=['id'], order_by=['id'])


engine = create_engine('clickhouse://default:@localhost/test_001')

with engine.connect() as conn:
    conn.connection.create_database()
    conn.connection.create_table(OHLC)

要求.txt

sqlalchemy==1.3.18
sqlalchemy-clickhouse==0.1.5.post0
infi.clickhouse_orm==1.3.0

使用id作为分区键看起来很可疑，考虑将其定义为toYYYYMM(start_date)或类似的东西：

class OHLC(Model):
    __tablename__ = 'ohlc'
    id = UInt16Field()
    min = Float32Field()
    max = Float32Field()
    start_date = DateField()
    interval = StringField()
    engine = MergeTree(partition_key=['toYYYYMM(start_date)'], order_by=['id'])

推荐阅读