class TestTable(self.base): date = Column(types.Date, primary_key=True) x = Column(types.Int32) y = Column(types.String) __table_args__ = (engines.MergeTree(partition_by=(date, x), order_by='date'), )
def test_update_unsupported(self): t1 = Table('t1', self.metadata(), Column('x', types.Int32, primary_key=True), engines.MergeTree(order_by=(func.tuple(), ))) t1.drop(if_exists=True) t1.create() with self.assertRaises(exc.CompileError) as ex: dialect = self.session.bind.dialect with mock_object_attr(dialect, 'supports_update', False): self.session.execute( t1.update().where(t1.c.x == 25).values(x=5)) self.assertEqual( str(ex.exception), 'ALTER UPDATE is not supported by this server version') with self.assertRaises(exc.CompileError) as ex: dialect = self.session.bind.dialect with mock_object_attr(dialect, 'supports_update', False): self.session.execute( update(t1).where(t1.c.x == 25).values(x=5)) self.assertEqual( str(ex.exception), 'ALTER UPDATE is not supported by this server version')
class DetailsTraffic(CkBase): __tablename__ = 'details_traffic' id = Column(types.String, primary_key=True, default=uuid.uuid4()) # 主键id req_time = Column(types.DateTime) # 请求时间 10位时间戳 req_date = Column(types.Date) # 请求时间 Y-m-d req_hour = Column(types.Int) # 请求时间 H plat = Column(types.Int8) # 平台 agent_type = Column(types.Int8) # 设备来源: 1/PC 2/WAP 3/公众号 4/IOS 5/Android website = Column(types.String) # 网页标题 url = Column(types.String) # url host = Column(types.String) # 域名 ref_url = Column(types.String) # 上级url ip = Column(types.String) # ip visit_id = Column(types.String) # 唯一vid loading_time = Column(types.Int64) # 网页打开完成时间 req_status = Column(types.Int8) # 打开状态 1/成功 2/失败 extra = Column(types.String) # 额外信息 market_source = Column(types.String) # 来源 market_medium = Column(types.String) # 媒介 market_campaign = Column(types.String) # 系列 market_content = Column(types.String) # 内容 market_term = Column(types.String) # 关键字 add_date = Column(types.Date, default=Func.get_date()) # 添加日期 add_time = Column( types.DateTime, default=Func.get_date(time_format='%Y-%m-%d %H:%M:%S')) # 添加时间 update_time = Column( types.DateTime, default=Func.get_date(time_format='%Y-%m-%d %H:%M:%S')) # 更新时间 __table_args__ = (engines.MergeTree(add_date, (plat, req_time, agent_type), index_granularity=8192), )
class TestTable(self.base): date = Column(types.Date, primary_key=True) x = Column(types.Int32) y = Column(types.String) __table_args__ = (engines.MergeTree('date', ('date', func.intHash32(x)), sample=func.intHash32(x)), )
class TestTable(self.base): date = Column(types.Date, primary_key=True) x = Column(types.Int32) y = Column(types.String) __table_args__ = ( engines.MergeTree(date, (date, x), index_granularity=4096), )
class TestTable(self.base): date = Column(types.Date, primary_key=True) x = Column(types.Int32) y = Column(types.String) __table_args__ = ( engines.MergeTree('date', ('date', 'x')), )
class TestTable(self.base): date = Column(types.Date, primary_key=True) x = Column(types.Int32) __table_args__ = (engines.MergeTree(ttl=[ ttl_delete(date + func.toIntervalDay(1)), ttl_to_disk(date + func.toIntervalDay(1), 'hdd'), ttl_to_volume(date + func.toIntervalDay(1), 'slow'), ], ), )
class TestTable(self.base): date = Column(types.Date, primary_key=True) x = Column(types.Int32) __table_args__ = ( engines.MergeTree( ttl=ttl_delete(date + func.toIntervalDay(1)), ), )
def test_merge_tree(self): engine = engines.MergeTree(partition_by='x', order_by='x', primary_key='x') with self._test_table(engine) as (table, engine): self.assertIsInstance(engine, engines.MergeTree) self.assertEqual(engine.partition_by.columns, [table.c.x]) self.assertEqual(engine.order_by.columns, [table.c.x]) self.assertEqual(engine.primary_key.columns, [table.c.x])
def test_merge_tree_param_expressions(self): engine = engines.MergeTree(partition_by=text('toYYYYMM(toDate(x))'), order_by='x', primary_key='x') with self._test_table(engine) as (table, engine): self.assertIsInstance(engine, engines.MergeTree) self.assertEqual(str(engine.partition_by.expressions[0]), 'toYYYYMM(toDate(x))') self.assertEqual(engine.order_by.columns, [table.c.x]) self.assertEqual(engine.primary_key.columns, [table.c.x])
class TestTable(self.base): date = Column(types.Date, primary_key=True) x = Column(types.Int32) y = Column(types.String) __table_args__ = (engines.MergeTree(partition_by=date, order_by=(date, x), primary_key=(x, y), sample_by=func.hashFunc(x), setting1=2, setting2=5), )
def temp_create_table2(self, database): ' create a table using sqlalchmey ' host, port = get_clickhouse_host_port() engine = get_clickhouse_engine(host, port, database) meta = sa.sql.schema.MetaData() temp = sa_schema.Table('temp5', meta) temp.append_column(sa_schema.Column('id', ch_types.Int64)) temp.append_column( sa_schema.Column('grp_code', ch_types.Nullable(ch_types.Int64))) temp.append_column(engines.MergeTree(order_by=('id', ))) temp.create(engine)
def test_delete(self): t1 = Table('t1', self.metadata(), Column('x', types.Int32, primary_key=True), engines.MergeTree('x', order_by=('x', ))) query = t1.delete().where(t1.c.x == 25) statement = self.compile(query, literal_binds=True) self.assertEqual(statement, 'ALTER TABLE t1 DELETE WHERE x = 25') query = delete(t1).where(t1.c.x == 25) statement = self.compile(query, literal_binds=True) self.assertEqual(statement, 'ALTER TABLE t1 DELETE WHERE x = 25')
def test_text_engine_columns(self): table = Table( 't1', self.metadata(), Column('date', types.Date, primary_key=True), Column('x', types.Int32), Column('y', types.String), engines.MergeTree('date', ('date', 'x')), ) self.assertEqual( self.compile(CreateTable(table)), 'CREATE TABLE t1 (date Date, x Int32, y String) ' 'ENGINE = MergeTree(date, (date, x), 8192)' )
def test_text_engine_columns(self): table = Table( 't1', self.metadata(), Column('date', types.Date, primary_key=True), Column('x', types.Int32), Column('y', types.String), engines.MergeTree(partition_by='date', order_by=('date', 'x')), ) self.assertEqual( self.compile(CreateTable(table)), 'CREATE TABLE t1 (date Date, x Int32, y String) ' 'ENGINE = MergeTree() ' 'PARTITION BY date ' 'ORDER BY (date, x)')
def test_delete_without_where(self): t1 = Table('t1', self.metadata(), Column('x', types.Int32, primary_key=True), engines.MergeTree('x', order_by=('x', ))) query = t1.delete() with self.assertRaises(exc.CompileError) as ex: self.compile(query, literal_binds=True) self.assertEqual(str(ex.exception), 'WHERE clause is required') query = delete(t1) with self.assertRaises(exc.CompileError) as ex: self.compile(query, literal_binds=True) self.assertEqual(str(ex.exception), 'WHERE clause is required')
def test_create_reflected(self): metadata = self.metadata() table = Table('test_reflect', metadata, Column('x', types.Int32), engines.MergeTree(partition_by='x', order_by='x')) with self.create_table(table): metadata.clear() # reflect from clean state self.assertFalse(metadata.tables) table = Table('test_reflect', metadata, autoload=True) exists_query = 'EXISTS TABLE test_reflect' table.drop() exists = self.session.execute(exists_query).fetchall() self.assertEqual(exists, [(0, )]) table.create() exists = self.session.execute(exists_query).fetchall() self.assertEqual(exists, [(1, )])
def temp_create_table_from_metadata(self, metadata_file, database): ' create a table ' meta_path = pathlib.Path(metadata_file) if not meta_path.exists() or not meta_path.is_file(): sys.exit('{} is not a valid file'.format(meta_path)) metadata = yaml.load(meta_path.open(), Loader=yaml.SafeLoader) def get_clickhouse_type(sa_type): clickhouse_types = { 'BOOLEAN': ch_types.UInt8, 'TINYINT': ch_types.Int8, 'SMALLINT': ch_types.Int16, 'INTEGER': ch_types.Int32, 'BIGINT': ch_types.Int64, 'FLOAT': ch_types.Float64, 'VARCHAR': ch_types.String } return clickhouse_types.get(sa_type, None) def get_clickhouse_sa_columns(metadata): columns = metadata['columns'] ch_columns = [] for idx, col in enumerate(columns): name = col['name'] col_type = col['type'], ch_type = get_clickhouse_type(col['type']) nullable = col['nullable'] # make the first column non-nullable (needed for Clickhouse) if idx == 0: tbl_col = sa_schema.Column(name, ch_type) else: tbl_col = sa_schema.Column(name, ch_types.Nullable(ch_type)) ch_columns.append(tbl_col) return ch_columns host, port = get_clickhouse_host_port() engine = get_clickhouse_engine(host, port, database) ch_columns = get_clickhouse_sa_columns(metadata) first_col_name = ch_columns[0].name meta = sa.sql.schema.MetaData() # temp = sa_schema.Table(metadata['table'], meta) new_table = sa_schema.Table(metadata['table'], meta) for idx, col in enumerate(ch_columns): new_table.append_column(col) new_table.append_column(engines.MergeTree(order_by=(first_col_name, ))) new_table.create(engine) return temp.append_column(sa_schema.Column('id', ch_types.Int64)) temp.append_column( sa_schema.Column('grp_code', ch_types.Nullable(ch_types.Int64))) temp.append_column(engines.MergeTree(order_by=('id', ))) temp.create(engine) return sql = ''' create table temp4 ( `id` Int64, `grp_code` Int64 ) ENGINE = MergeTree() ORDER BY id ''' host, port = get_clickhouse_host_port() database = 'default' execute_clickhouse_sql(host, port, database, sql)