class Microregion(datasets.Entity):
"""Entity class for microregions."""
_name = 'microregion'
__table__ = schema.Table(
'microregions', datasets.Entity.metadata,
schema.Column('id', types.Integer, nullable=False, primary_key=True),
schema.Column('mesoregion_id',
types.SmallInteger,
schema.ForeignKey('mesoregions.id', use_alter=True),
nullable=False,
index=True),
schema.Column('state_id',
types.SmallInteger,
schema.ForeignKey('states.id', use_alter=True),
nullable=False,
index=True),
schema.Column('name', types.String(64), nullable=False, index=True))
# Relationships
state = orm.relationship('State', back_populates='microregions')
mesoregion = orm.relationship('Mesoregion', back_populates='microregions')
municipalities =\
orm.relationship('Municipality', back_populates='microregion')
districts = orm.relationship('District', back_populates='microregion')
subdistricts =\
orm.relationship('Subdistrict', back_populates='microregion')
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)
class State(datasets.Entity):
"""Entity class for states."""
_name = 'state'
__table__ = schema.Table(
'states', datasets.Entity.metadata,
schema.Column('id',
types.SmallInteger,
nullable=False,
primary_key=True),
schema.Column('name', types.String(32), nullable=False, index=True))
# Relationships
mesoregions = orm.relationship('Mesoregion', back_populates='state')
microregions = orm.relationship('Microregion', back_populates='state')
municipalities = orm.relationship('Municipality', back_populates='state')
districts = orm.relationship('District', back_populates='state')
subdistricts = orm.relationship('Subdistrict', back_populates='state')
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)