def test_create_table_nullable_nested_nullable(self):
table = Table(
't1', self.metadata(), Column('x', types.Int32, primary_key=True),
Column('y',
types.Nullable(types.Array(types.Nullable(types.String)))),
engines.Memory())
self.assertEqual(
self.compile(CreateTable(table)), 'CREATE TABLE t1 '
'(x Int32, y Nullable(Array(Nullable(String)))) '
'ENGINE = Memory')
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 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
def metadata_transform(metadata: MetaData) -> MetaData:
# Just need to remove autoincrement cols
new_metadata = MetaData(schema=metadata.schema)
for table in metadata.tables.values():
cols = [
c for c in table.columns.values()
if c.autoincrement is not True
]
new_cols = []
for col in cols:
typ = types.Nullable(type_lookup[type(col.type)])
new_cols.append(Column(col.name, typ))
t = Table(table.name, new_metadata, *new_cols)
t.engine = Memory()
return new_metadata
def test_nullable(self):
coltype = self._type_round_trip(types.Nullable(types.Int32))[0]
self.assertIsInstance(coltype, types.Nullable)
self.assertEqual(coltype.nested_type, types.Int32)
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)
def test_columns_compilation(self):
# should not raise UnsupportedCompilationError
col = Column('x', types.Nullable(types.Int32))
self.assertEqual(str(col.type), 'Nullable(Int32)')
def test_nullable(self):
col = self._type_round_trip(types.Nullable(types.Int32))[0]
self.assertIsInstance(col['type'], types.Nullable)
self.assertTrue(col['nullable'])
self.assertEqual(col['type'].nested_type, types.Int32)
class WeblogEntry(Base):
__tablename__ = "weblog_entries"
raw_text = Column(types.Nullable(types.String), primary_key=True)
remote_address = Column(types.String)
remote_user = Column(types.Nullable(types.String))
created_on = Column(types.DateTime)
method = Column(types.String)
request_uri = Column(types.String)
http_version = Column(types.Nullable(types.String))
response_status = Column(types.Nullable(types.Int))
response_bytes_sent = Column(types.Nullable(types.Int))
http_referrer = Column(types.Nullable(types.String))
http_user_agent = Column(types.Nullable(types.String))
forwarded_for_ips = Column(types.Nullable(types.String))
hostname = Column(types.Nullable(types.String))
server_name = Column(types.Nullable(types.String))
request_time = Column(types.Nullable(types.Decimal(precision=10, scale=4)))
upstream_status = Column(types.Nullable(types.Int))
upstream_response_time = Column(
types.Nullable(types.Decimal(precision=10, scale=4)))
upstream_response_length = Column(types.Nullable(types.Int))
clientip = Column(types.Nullable(types.String))
user_id = Column(types.Nullable(types.String))
session_id = Column(types.Nullable(types.String))
__table_args__ = (engines.ReplacingMergeTree(
primary_key=(created_on, remote_address, method, request_uri),
order_by=(created_on, remote_address, method, request_uri)), )
