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)