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)), )