class Test(TestBase): def setup(self): datatypes = lambda *types: [validate_data_type(t) for t in types] schema = Schema.from_lists( ['name', 'id', 'fid', 'isMale', 'scale', 'birth'], datatypes('string', 'int64', 'float64', 'boolean', 'decimal', 'datetime'), ['ds'], datatypes('string')) table = MockTable(name='pyodps_test_expr_table', schema=schema) self.expr = CollectionExpr(_source_data=table, _schema=Schema(columns=schema.columns)) table1 = MockTable(name='pyodps_test_expr_table1', schema=schema) self.expr1 = CollectionExpr(_source_data=table1, _schema=Schema(columns=schema.columns)) table2 = MockTable(name='pyodps_test_expr_table2', schema=schema) self.expr2 = CollectionExpr(_source_data=table2, _schema=Schema(columns=schema.columns)) schema2 = Schema.from_lists(['name', 'id', 'fid'], datatypes('string', 'int64', 'float64'), ['part1', 'part2'], datatypes('string', 'int64')) table3 = MockTable(name='pyodps_test_expr_table2', schema=schema2) self.expr3 = CollectionExpr(_source_data=table3, _schema=Schema(columns=schema2.columns)) def testProjectPrune(self): expr = self.expr.select('name', 'id') new_expr = ColumnPruning(expr.to_dag()).prune() self.assertIsInstance(new_expr, ProjectCollectionExpr) self.assertIsNotNone(new_expr.input._source_data) expected = 'SELECT t1.`name`, t1.`id` \n' \ 'FROM mocked_project.`pyodps_test_expr_table` t1' self.assertEqual(expected, ODPSEngine(self.odps).compile(expr, prettify=False)) expr = self.expr[Scalar(3).rename('const'), NullScalar('string').rename('string_const'), self.expr.id] expected = 'SELECT 3 AS `const`, CAST(NULL AS STRING) AS `string_const`, t1.`id` \n' \ 'FROM mocked_project.`pyodps_test_expr_table` t1' self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.select( pt=BuiltinFunction('max_pt', args=(self.expr._source_data.name, ))) expected = "SELECT max_pt('pyodps_test_expr_table') AS `pt` \n" \ "FROM mocked_project.`pyodps_test_expr_table` t1" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testApplyPrune(self): @output(['name', 'id'], ['string', 'string']) def h(row): yield row[0], row[1] expr = self.expr[self.expr.fid < 0].apply(h, axis=1)['id', ] new_expr = ColumnPruning(expr.to_dag()).prune() self.assertIsInstance(new_expr, ProjectCollectionExpr) self.assertIsInstance(new_expr.input.input, FilterCollectionExpr) self.assertIsNotNone(new_expr.input.input.input._source_data) def testFilterPrune(self): expr = self.expr.filter(self.expr.name == 'name1') expr = expr['name', 'id'] new_expr = ColumnPruning(expr.to_dag()).prune() self.assertIsInstance(new_expr.input, FilterCollectionExpr) self.assertNotIsInstance(new_expr.input.input, ProjectCollectionExpr) self.assertIsNotNone(new_expr.input.input._source_data) expected = 'SELECT t1.`name`, t1.`id` \n' \ 'FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ 'WHERE t1.`name` == \'name1\'' self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.filter(self.expr.name == 'name1') new_expr = ColumnPruning(expr.to_dag()).prune() self.assertIsInstance(new_expr, FilterCollectionExpr) self.assertIsNotNone(new_expr.input._source_data) expr = self.expr.filter(self.expr.id.isin(self.expr3.id)) expected = 'SELECT * \n' \ 'FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ 'WHERE t1.`id` IN (SELECT t3.`id` FROM ( ' \ 'SELECT t2.`id` FROM mocked_project.`pyodps_test_expr_table2` t2 ) t3)' self.assertTrue( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testFilterPartitionPrune(self): expr = self.expr.filter_partition('ds=today')[lambda x: x.fid < 0][ 'name', lambda x: x.id + 1] new_expr = ColumnPruning(expr.to_dag()).prune() self.assertEqual(set(new_expr.input.input.schema.names), set(['name', 'id', 'fid'])) expected = "SELECT t2.`name`, t2.`id` + 1 AS `id` \n" \ "FROM (\n" \ " SELECT t1.`name`, t1.`id`, t1.`fid` \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ " WHERE t1.`ds` == 'today' \n" \ ") t2 \n" \ "WHERE t2.`fid` < 0" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testSlicePrune(self): expr = self.expr.filter(self.expr.fid < 0)[:4]['name', lambda x: x.id + 1] new_expr = ColumnPruning(expr.to_dag()).prune() self.assertIsNotNone(new_expr.input.input.input._source_data) expected = "SELECT t1.`name`, t1.`id` + 1 AS `id` \n" \ "FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ "WHERE t1.`fid` < 0 \n" \ "LIMIT 4" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testGroupbyPrune(self): expr = self.expr.groupby('name').agg(id=self.expr.id.max()) expr = expr[expr.id < 0]['name', ] expected = "SELECT t1.`name` \n" \ "FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ "GROUP BY t1.`name` \n" \ "HAVING MAX(t1.`id`) < 0" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.groupby('name').agg(id=self.expr.id.max()) expr = expr[expr.id < 0]['id', ] expected = "SELECT MAX(t1.`id`) AS `id` \n" \ "FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ "GROUP BY t1.`name` \n" \ "HAVING MAX(t1.`id`) < 0" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testMutatePrune(self): expr = self.expr[self.expr.exclude('birth'), self.expr.fid.astype('int').rename('new_id')] expr = expr[expr, expr.groupby('name'). mutate(lambda x: x.new_id.cumsum().rename('new_id_sum'))] expr = expr[expr.new_id, expr.new_id_sum] expected = "SELECT t2.`new_id`, t2.`new_id_sum` \n" \ "FROM (\n" \ " SELECT CAST(t1.`fid` AS BIGINT) AS `new_id`, " \ "SUM(CAST(t1.`fid` AS BIGINT)) OVER (PARTITION BY t1.`name`) AS `new_id_sum` \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ ") t2" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testValueCountsPrune(self): expr = self.expr.name.value_counts()['count', ] new_expr = ColumnPruning(expr.to_dag()).prune() self.assertIsInstance(new_expr.input.input, ProjectCollectionExpr) self.assertEqual(set(new_expr.input.input.schema.names), set(['name'])) def testSortPrune(self): expr = self.expr[self.expr.exclude('name'), self.expr.name.rename('name2')].sort('name2')['id', 'fid'] expected = "SELECT t2.`id`, t2.`fid` \n" \ "FROM (\n" \ " SELECT t1.`id`, t1.`fid`, t1.`name` AS `name2` \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ " ORDER BY name2 \n" \ " LIMIT 10000\n" \ ") t2" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testDistinctPrune(self): expr = self.expr.distinct(self.expr.id + 1, self.expr.name)['name', ] expected = "SELECT t2.`name` \n" \ "FROM (\n" \ " SELECT DISTINCT t1.`id` + 1 AS `id`, t1.`name` \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ ") t2" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testSamplePrune(self): expr = self.expr['name', 'id'].sample(parts=5)['id', ] expected = "SELECT t1.`id` \n" \ "FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ "WHERE SAMPLE(5, 1)" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testJoinPrune(self): left = self.expr.select(self.expr, type='normal') right = self.expr3[:4] joined = left.left_join(right, on='id') expr = joined.id_x.rename('id') expected = "SELECT t2.`id` \n" \ "FROM (\n" \ " SELECT t1.`id` \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1\n" \ ") t2 \n" \ "LEFT OUTER JOIN \n" \ " (\n" \ " SELECT t3.`id` \n" \ " FROM mocked_project.`pyodps_test_expr_table2` t3 \n" \ " LIMIT 4\n" \ " ) t4\n" \ "ON t2.`id` == t4.`id`" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) joined = self.expr.join(self.expr2, 'name') expected = 'SELECT t1.`name`, t1.`id` AS `id_x`, t1.`fid` AS `fid_x`, ' \ 't1.`isMale` AS `isMale_x`, t1.`scale` AS `scale_x`, ' \ 't1.`birth` AS `birth_x`, t1.`ds` AS `ds_x`, t2.`id` AS `id_y`, ' \ 't2.`fid` AS `fid_y`, t2.`isMale` AS `isMale_y`, t2.`scale` AS `scale_y`, ' \ 't2.`birth` AS `birth_y`, t2.`ds` AS `ds_y` \n' \ 'FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ 'INNER JOIN \n' \ ' mocked_project.`pyodps_test_expr_table2` t2\n' \ 'ON t1.`name` == t2.`name`' self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(joined, prettify=False))) joined = self.expr.join(self.expr2, on=[self.expr.name == self.expr2.name]) joined2 = joined.join(self.expr, on=[joined.id_x == self.expr.id]) expected = 'SELECT t1.`name` AS `name_x`, t1.`id` AS `id_x`, ' \ 't1.`fid` AS `fid_x`, t1.`isMale` AS `isMale_x`, ' \ 't1.`scale` AS `scale_x`, t1.`birth` AS `birth_x`, ' \ 't1.`ds` AS `ds_x`, t2.`id` AS `id_y`, t2.`fid` AS `fid_y`, ' \ 't2.`isMale` AS `isMale_y`, t2.`scale` AS `scale_y`, ' \ 't2.`birth` AS `birth_y`, t2.`ds` AS `ds_y`, ' \ 't3.`name` AS `name_y`, t3.`id`, t3.`fid`, t3.`isMale`, ' \ 't3.`scale`, t3.`birth`, t3.`ds` \n' \ 'FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ 'INNER JOIN \n' \ ' mocked_project.`pyodps_test_expr_table2` t2\n' \ 'ON t1.`name` == t2.`name` \n' \ 'INNER JOIN \n' \ ' mocked_project.`pyodps_test_expr_table` t3\n' \ 'ON t1.`id` == t3.`id`' self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(joined2, prettify=False))) joined = self.expr.join(self.expr2, on=[self.expr.name == self.expr2.name], mapjoin=True) joined2 = joined.join(self.expr, on=[joined.id_x == self.expr.id], mapjoin=True) expected = 'SELECT /*+mapjoin(t2,t3)*/ t1.`name` AS `name_x`, t1.`id` AS `id_x`, ' \ 't1.`fid` AS `fid_x`, t1.`isMale` AS `isMale_x`, t1.`scale` AS `scale_x`, ' \ 't1.`birth` AS `birth_x`, t1.`ds` AS `ds_x`, t2.`id` AS `id_y`, ' \ 't2.`fid` AS `fid_y`, t2.`isMale` AS `isMale_y`, t2.`scale` AS `scale_y`, ' \ 't2.`birth` AS `birth_y`, t2.`ds` AS `ds_y`, t3.`name` AS `name_y`, ' \ 't3.`id`, t3.`fid`, t3.`isMale`, t3.`scale`, t3.`birth`, t3.`ds` \n' \ 'FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ 'INNER JOIN \n' \ ' mocked_project.`pyodps_test_expr_table2` t2\n' \ 'ON t1.`name` == t2.`name` \n' \ 'INNER JOIN \n' \ ' mocked_project.`pyodps_test_expr_table` t3\n' \ 'ON t1.`id` == t3.`id`' self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(joined2, prettify=False))) def testUnionPrune(self): left = self.expr.select('name', 'id') right = self.expr3.select( self.expr3.fid.astype('int').rename('id'), self.expr3.name) expr = left.union(right)['id'] expected = "SELECT t3.`id` \n" \ "FROM (\n" \ " SELECT t1.`id` \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ " UNION ALL\n" \ " SELECT CAST(t2.`fid` AS BIGINT) AS `id` \n" \ " FROM mocked_project.`pyodps_test_expr_table2` t2\n" \ ") t3" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.union(self.expr2) expected = 'SELECT * \n' \ 'FROM (\n' \ ' SELECT * \n' \ ' FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ ' UNION ALL\n' \ ' SELECT * \n' \ ' FROM mocked_project.`pyodps_test_expr_table2` t2\n' \ ') t3' self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False)))
class Test(TestBase): def setup(self): datatypes = lambda *types: [validate_data_type(t) for t in types] schema = Schema.from_lists( ['name', 'id', 'fid', 'isMale', 'scale', 'birth'], datatypes('string', 'int64', 'float64', 'boolean', 'decimal', 'datetime'), ['ds'], datatypes('string')) table = MockTable(name='pyodps_test_expr_table', schema=schema) self.expr = CollectionExpr(_source_data=table, _schema=schema) table1 = MockTable(name='pyodps_test_expr_table1', schema=schema) self.expr1 = CollectionExpr(_source_data=table1, _schema=schema) table2 = MockTable(name='pyodps_test_expr_table2', schema=schema) self.expr2 = CollectionExpr(_source_data=table2, _schema=schema) schema2 = Schema.from_lists(['name', 'id', 'fid'], datatypes('string', 'int64', 'float64'), ['part1', 'part2'], datatypes('string', 'int64')) table3 = MockTable(name='pyodps_test_expr_table2', schema=schema2) self.expr3 = CollectionExpr(_source_data=table3, _schema=schema2) schema3 = Schema.from_lists(['id', 'name', 'relatives', 'hobbies'], datatypes('int64', 'string', 'dict<string, string>', 'list<string>')) table4 = MockTable(name='pyodps_test_expr_table', schema=schema) self.expr4 = CollectionExpr(_source_data=table4, _schema=schema3) self.maxDiff = None def testFilterPushdownThroughProjection(self): expr = self.expr[self.expr.id + 1, 'name'][lambda x: x.id < 10] expected = 'SELECT t1.`id` + 1 AS `id`, t1.`name` \n' \ 'FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ 'WHERE (t1.`id` + 1) < 10' self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr[ self.expr.id + 1, 'name', self.expr.name.isnull().rename('is_null')][lambda x: x.is_null] expected = 'SELECT t1.`id` + 1 AS `id`, t1.`name`, t1.`name` IS NULL AS `is_null` \n' \ 'FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ 'WHERE t1.`name` IS NULL' self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr['name', self.expr.id ** 2]\ .filter(lambda x: x.name == 'name1').filter(lambda x: x.id < 3) expected = "SELECT t1.`name`, CAST(POW(t1.`id`, 2) AS BIGINT) AS `id` \n" \ "FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ "WHERE (t1.`name` == 'name1') AND ((CAST(POW(t1.`id`, 2) AS BIGINT)) < 3)" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr['name', self.expr.id + 1].filter( lambda x: x.name == 'name1')[lambda x: 'tt' + x.name, 'id'].filter(lambda x: x.id < 3) expected = "SELECT CONCAT('tt', t1.`name`) AS `name`, t1.`id` + 1 AS `id` \n" \ "FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ "WHERE (t1.`name` == 'name1') AND ((t1.`id` + 1) < 3)" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.filter(self.expr.name == 'name1').select( 'name', lambda x: (x.id + 1) * 2)[lambda x: 'tt' + x.name, 'id'].filter(lambda x: x.id < 3) expected = "SELECT CONCAT('tt', t1.`name`) AS `name`, (t1.`id` + 1) * 2 AS `id` \n" \ "FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ "WHERE (t1.`name` == 'name1') AND (((t1.`id` + 1) * 2) < 3)" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.filter( self.expr.id.between(2, 6), self.expr.name.lower().contains('pyodps', regex=False)).name.nunique() expected = "SELECT COUNT(DISTINCT t2.`name`) AS `name_nunique` \n" \ "FROM (\n" \ " SELECT t1.`name`, t1.`id` \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ " WHERE ((t1.`id` >= 2) AND (t1.`id` <= 6)) AND INSTR(TOLOWER(t1.`name`), 'pyodps') > 0 \n" \ ") t2" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testFilterPushDownThroughJoin(self): expr = self.expr.join(self.expr3, on='name') expr = expr[(expr.id_x < 10) & (expr.fid_y > 3)] expected = 'SELECT t2.`name`, t2.`id` AS `id_x`, t2.`fid` AS `fid_x`, ' \ 't2.`isMale`, t2.`scale`, t2.`birth`, t2.`ds`, t4.`id` AS `id_y`, ' \ 't4.`fid` AS `fid_y`, t4.`part1`, t4.`part2` \n' \ 'FROM (\n' \ ' SELECT * \n' \ ' FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ ' WHERE t1.`id` < 10\n' \ ') t2 \n' \ 'INNER JOIN \n' \ ' (\n' \ ' SELECT * \n' \ ' FROM mocked_project.`pyodps_test_expr_table2` t3 \n' \ ' WHERE t3.`fid` > 3\n' \ ' ) t4\n' \ 'ON t2.`name` == t4.`name`' self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.join(self.expr3, on='name') expr = expr[(expr.id_x < 10) & (expr.fid_y > 3) & (expr.id_x > 3)] expected = 'SELECT t2.`name`, t2.`id` AS `id_x`, t2.`fid` AS `fid_x`, ' \ 't2.`isMale`, t2.`scale`, t2.`birth`, t2.`ds`, t4.`id` AS `id_y`, ' \ 't4.`fid` AS `fid_y`, t4.`part1`, t4.`part2` \n' \ 'FROM (\n' \ ' SELECT * \n' \ ' FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ ' WHERE (t1.`id` < 10) AND (t1.`id` > 3)\n' \ ') t2 \n' \ 'INNER JOIN \n' \ ' (\n' \ ' SELECT * \n' \ ' FROM mocked_project.`pyodps_test_expr_table2` t3 \n' \ ' WHERE t3.`fid` > 3\n' \ ' ) t4\n' \ 'ON t2.`name` == t4.`name`' self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr[self.expr.name, self.expr.id + 1] expr2 = self.expr3['tt' + self.expr3.name, self.expr3.id.rename('id2')] expr = expr.join(expr2, on='name') expr = expr[((expr.id < 10) | (expr.id > 100)) & (expr.id2 > 3)] expected = "SELECT t2.`name`, t2.`id`, t4.`id2` \n" \ "FROM (\n" \ " SELECT t1.`name`, t1.`id` + 1 AS `id` \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ " WHERE ((t1.`id` + 1) < 10) OR ((t1.`id` + 1) > 100)\n" \ ") t2 \n" \ "INNER JOIN \n" \ " (\n" \ " SELECT CONCAT('tt', t3.`name`) AS `name`, t3.`id` AS `id2` \n" \ " FROM mocked_project.`pyodps_test_expr_table2` t3 \n" \ " WHERE t3.`id` > 3\n" \ " ) t4\n" \ "ON t2.`name` == t4.`name`" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.join(self.expr3, on='name') expr = expr[(expr.id_x + expr.id_y < 10) & (expr.id_x > 3)] expected = "SELECT * \n" \ "FROM (\n" \ " SELECT t2.`name`, t2.`id` AS `id_x`, t2.`fid` AS `fid_x`, t2.`isMale`, " \ "t2.`scale`, t2.`birth`, t2.`ds`, t3.`id` AS `id_y`, " \ "t3.`fid` AS `fid_y`, t3.`part1`, t3.`part2` \n" \ " FROM (\n" \ " SELECT * \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ " WHERE t1.`id` > 3\n" \ " ) t2 \n" \ " INNER JOIN \n" \ " mocked_project.`pyodps_test_expr_table2` t3\n" \ " ON t2.`name` == t3.`name` \n" \ ") t4 \n" \ "WHERE (t4.`id_x` + t4.`id_y`) < 10" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.outer_join(self.expr3, on='name') expr = expr[(expr.id_x + expr.id_y < 10) & (expr.id_x > 3)] expected = "SELECT * \n" \ "FROM (\n" \ " SELECT t1.`name` AS `name_x`, t1.`id` AS `id_x`, t1.`fid` AS `fid_x`, " \ "t1.`isMale`, t1.`scale`, t1.`birth`, t1.`ds`, t2.`name` AS `name_y`, " \ "t2.`id` AS `id_y`, t2.`fid` AS `fid_y`, t2.`part1`, t2.`part2` \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ " FULL OUTER JOIN \n" \ " mocked_project.`pyodps_test_expr_table2` t2\n" \ " ON t1.`name` == t2.`name` \n" \ ") t3 \n" \ "WHERE ((t3.`id_x` + t3.`id_y`) < 10) AND (t3.`id_x` > 3)" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.join(self.expr3, on=[ 'name', self.expr.id == self.expr3.id, self.expr.id < 10, self.expr3.name == 'name1', self.expr.id > 5 ]) expected = 'SELECT t2.`name`, t2.`id`, t2.`fid` AS `fid_x`, t2.`isMale`, ' \ 't2.`scale`, t2.`birth`, t2.`ds`, t4.`fid` AS `fid_y`, t4.`part1`, t4.`part2` \n' \ 'FROM (\n' \ ' SELECT * \n' \ ' FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ ' WHERE (t1.`id` < 10) AND (t1.`id` > 5)\n' \ ') t2 \n' \ 'INNER JOIN \n' \ ' (\n' \ ' SELECT * \n' \ ' FROM mocked_project.`pyodps_test_expr_table2` t3 \n' \ ' WHERE t3.`name` == \'name1\'\n' \ ' ) t4\n' \ 'ON (t2.`name` == t4.`name`) AND (t2.`id` == t4.`id`)' self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.left_join(self.expr3, on=[ 'name', self.expr.id == self.expr3.id, self.expr.id < 10, self.expr3.name == 'name1', self.expr.id > 5 ]) expected = 'SELECT t1.`name` AS `name_x`, t1.`id` AS `id_x`, t1.`fid` AS `fid_x`, t1.`isMale`, ' \ 't1.`scale`, t1.`birth`, t1.`ds`, t2.`name` AS `name_y`, t2.`id` AS `id_y`, ' \ 't2.`fid` AS `fid_y`, t2.`part1`, t2.`part2` \n' \ 'FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ 'LEFT OUTER JOIN \n' \ ' mocked_project.`pyodps_test_expr_table2` t2\n' \ 'ON ((((t1.`name` == t2.`name`) AND (t1.`id` == t2.`id`)) ' \ "AND (t1.`id` < 10)) AND (t2.`name` == 'name1')) AND (t1.`id` > 5)" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testFilterPushdownThroughUnion(self): expr = self.expr['name', 'id'].union(self.expr2['id', 'name']) expr = expr.filter(expr.id + 1 < 3) expected = 'SELECT * \n' \ 'FROM (\n' \ ' SELECT t1.`name`, t1.`id` \n' \ ' FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ ' WHERE (t1.`id` + 1) < 3 \n' \ ' UNION ALL\n' \ ' SELECT t2.`name`, t2.`id` \n' \ ' FROM mocked_project.`pyodps_test_expr_table2` t2 \n' \ ' WHERE (t2.`id` + 1) < 3\n' \ ') t3' self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr1 = self.expr.filter(self.expr.id == 1)['name', 'id'] expr2 = self.expr.filter(self.expr.id == 0)['id', 'name'] expr = expr1.union(expr2) expected = 'SELECT * \n' \ 'FROM (\n' \ ' SELECT t1.`name`, t1.`id` \n' \ ' FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ ' WHERE t1.`id` == 1 \n' \ ' UNION ALL\n' \ ' SELECT t2.`name`, t2.`id` \n' \ ' FROM mocked_project.`pyodps_test_expr_table` t2 \n' \ ' WHERE t2.`id` == 0\n' \ ') t3' self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testGroupbyProjection(self): expr = self.expr['id', 'name', 'fid'] expr2 = expr.groupby('name').agg(count=expr.count(), id=expr.id.sum()) expr3 = expr2['count', 'id'] expected = "SELECT COUNT(1) AS `count`, SUM(t1.`id`) AS `id` \n" \ "FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ "GROUP BY t1.`name`" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr3, prettify=False))) expr = self.expr['id', 'name', 'fid'].filter(self.expr.id < 10)['name', 'id'] expr2 = expr.groupby('name').agg(count=expr.count(), id=expr.id.sum(), name2=expr.name.max()) expr3 = expr2[expr2.count + 1, 'id'] expected = "SELECT COUNT(1) + 1 AS `count`, SUM(t1.`id`) AS `id` \n" \ "FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ "WHERE t1.`id` < 10 \n" \ "GROUP BY t1.`name`" self.assertEqual(expected, ODPSEngine(self.odps).compile(expr3, prettify=False)) def testFilterPushdownThroughMultipleProjection(self): schema = Schema.from_lists(list('abcde'), ['string'] * 5) table = MockTable(name='pyodps_test_expr_table3', schema=schema) tab = CollectionExpr(_source_data=table, _schema=odps_schema_to_df_schema(schema)) labels2 = [] bins2 = [] for i in range(0, 30): a = str(7 * i) + '-' + str(7 * (i + 1)) b = 7 * i bins2.append(b) labels2.append(a) p1 = tab.select( tab.a, tab.c.astype('int').cut(bins2, labels=labels2, include_over=True).rename('c_cut'), tab.e.astype('int').rename('e'), tab.c.astype('int').rename('c')) p1['f'] = p1['e'] / p1['c'] t = [] l = [] for i in range(0, 20): a = 1 * i b = str(a) t.append(a) l.append(b) p2 = p1.select( p1.a, p1.c_cut, p1.f.cut(bins=t, labels=l, include_over=True).rename('f_cut')) expected = "SELECT t1.`a`, CASE WHEN (0 < CAST(t1.`c` AS BIGINT)) " \ "AND (CAST(t1.`c` AS BIGINT) <= 7) THEN '0-7' " \ "WHEN (7 < CAST(t1.`c` AS BIGINT)) " \ "AND (CAST(t1.`c` AS BIGINT) <= 14) " \ "THEN '7-14' WHEN (14 < CAST(t1.`c` AS BIGINT)) " \ "AND (CAST(t1.`c` AS BIGINT) <= 21) THEN '14-21' " \ "WHEN (21 < CAST(t1.`c` AS BIGINT)) " \ "AND (CAST(t1.`c` AS BIGINT) <= 28) " \ "THEN '21-28' WHEN (28 < CAST(t1.`c` AS BIGINT)) " \ "AND (CAST(t1.`c` AS BIGINT) <= 35) THEN '28-35' " \ "WHEN (35 < CAST(t1.`c` AS BIGINT)) " \ "AND (CAST(t1.`c` AS BIGINT) <= 42) THEN '35-42' " \ "WHEN (42 < CAST(t1.`c` AS BIGINT)) " \ "AND (CAST(t1.`c` AS BIGINT) <= 49) THEN '42-49' " \ "WHEN (49 < CAST(t1.`c` AS BIGINT)) " \ "AND (CAST(t1.`c` AS BIGINT) <= 56) " \ "THEN '49-56' WHEN (56 < CAST(t1.`c` AS BIGINT)) " \ "AND (CAST(t1.`c` AS BIGINT) <= 63) THEN '56-63' " \ "WHEN (63 < CAST(t1.`c` AS BIGINT)) " \ "AND (CAST(t1.`c` AS BIGINT) <= 70) THEN '63-70' " \ "WHEN (70 < CAST(t1.`c` AS BIGINT)) AND (CAST(t1.`c` AS BIGINT) <= 77) " \ "THEN '70-77' WHEN (77 < CAST(t1.`c` AS BIGINT)) " \ "AND (CAST(t1.`c` AS BIGINT) <= 84) " \ "THEN '77-84' WHEN (84 < CAST(t1.`c` AS BIGINT)) " \ "AND (CAST(t1.`c` AS BIGINT) <= 91) THEN '84-91' " \ "WHEN (91 < CAST(t1.`c` AS BIGINT)) AND (CAST(t1.`c` AS BIGINT) <= 98) " \ "THEN '91-98' WHEN (98 < CAST(t1.`c` AS BIGINT)) " \ "AND (CAST(t1.`c` AS BIGINT) <= 105) THEN '98-105' " \ "WHEN (105 < CAST(t1.`c` AS BIGINT)) AND (CAST(t1.`c` AS BIGINT) <= 112) " \ "THEN '105-112' WHEN (112 < CAST(t1.`c` AS BIGINT)) " \ "AND (CAST(t1.`c` AS BIGINT) <= 119) THEN '112-119' " \ "WHEN (119 < CAST(t1.`c` AS BIGINT)) AND (CAST(t1.`c` AS BIGINT) <= 126) " \ "THEN '119-126' WHEN (126 < CAST(t1.`c` AS BIGINT)) " \ "AND (CAST(t1.`c` AS BIGINT) <= 133) THEN '126-133' " \ "WHEN (133 < CAST(t1.`c` AS BIGINT)) AND (CAST(t1.`c` AS BIGINT) <= 140) " \ "THEN '133-140' WHEN (140 < CAST(t1.`c` AS BIGINT)) " \ "AND (CAST(t1.`c` AS BIGINT) <= 147) THEN '140-147' " \ "WHEN (147 < CAST(t1.`c` AS BIGINT)) AND (CAST(t1.`c` AS BIGINT) <= 154) " \ "THEN '147-154' WHEN (154 < CAST(t1.`c` AS BIGINT)) " \ "AND (CAST(t1.`c` AS BIGINT) <= 161) THEN '154-161' " \ "WHEN (161 < CAST(t1.`c` AS BIGINT)) AND (CAST(t1.`c` AS BIGINT) <= 168) " \ "THEN '161-168' WHEN (168 < CAST(t1.`c` AS BIGINT)) " \ "AND (CAST(t1.`c` AS BIGINT) <= 175) THEN '168-175' " \ "WHEN (175 < CAST(t1.`c` AS BIGINT)) AND (CAST(t1.`c` AS BIGINT) <= 182) " \ "THEN '175-182' WHEN (182 < CAST(t1.`c` AS BIGINT)) " \ "AND (CAST(t1.`c` AS BIGINT) <= 189) THEN '182-189' " \ "WHEN (189 < CAST(t1.`c` AS BIGINT)) AND (CAST(t1.`c` AS BIGINT) <= 196) " \ "THEN '189-196' WHEN (196 < CAST(t1.`c` AS BIGINT)) " \ "AND (CAST(t1.`c` AS BIGINT) <= 203) THEN '196-203' " \ "WHEN 203 < CAST(t1.`c` AS BIGINT) THEN '203-210' END AS `c_cut`, " \ "CASE WHEN (0 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 1) THEN '0' " \ "WHEN (1 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 2) " \ "THEN '1' WHEN (2 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 3) THEN '2' " \ "WHEN (3 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 4) " \ "THEN '3' WHEN (4 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 5) THEN '4' " \ "WHEN (5 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 6) THEN '5' " \ "WHEN (6 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 7) " \ "THEN '6' WHEN (7 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 8) THEN '7' " \ "WHEN (8 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 9) THEN '8' " \ "WHEN (9 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 10) " \ "THEN '9' WHEN (10 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 11) THEN '10' " \ "WHEN (11 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 12) " \ "THEN '11' WHEN (12 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 13) THEN '12' " \ "WHEN (13 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 14) THEN '13' " \ "WHEN (14 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 15) THEN '14' " \ "WHEN (15 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 16) THEN '15' " \ "WHEN (16 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 17) THEN '16' " \ "WHEN (17 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 18) " \ "THEN '17' WHEN (18 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 19) THEN '18' " \ "WHEN 19 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) THEN '19' END AS `f_cut` \n" \ "FROM mocked_project.`pyodps_test_expr_table3` t1 \n" \ "WHERE (CASE WHEN (0 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 1) THEN '0' " \ "WHEN (1 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 2) " \ "THEN '1' WHEN (2 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 3) THEN '2' " \ "WHEN (3 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 4) THEN '3' " \ "WHEN (4 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 5) THEN '4' " \ "WHEN (5 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 6) THEN '5' " \ "WHEN (6 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 7) THEN '6' " \ "WHEN (7 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 8) THEN '7' " \ "WHEN (8 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 9) THEN '8' " \ "WHEN (9 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 10) THEN '9' " \ "WHEN (10 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 11) THEN '10' " \ "WHEN (11 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 12) THEN '11' " \ "WHEN (12 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 13) THEN '12' " \ "WHEN (13 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 14) THEN '13' " \ "WHEN (14 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 15) THEN '14' " \ "WHEN (15 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 16) THEN '15' " \ "WHEN (16 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 17) THEN '16' " \ "WHEN (17 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 18) THEN '17' " \ "WHEN (18 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT))) " \ "AND ((CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) <= 19) THEN '18' " \ "WHEN 19 < (CAST(t1.`e` AS BIGINT) / CAST(t1.`c` AS BIGINT)) THEN '19' END) == '9'" self.assertEqual( str(expected), str( ODPSEngine(self.odps).compile(p2[p2.f_cut == '9'], prettify=False))) def testFilterPushdownThroughLateralView(self): expr = self.expr4[self.expr4.id, self.expr4.name, self.expr4.hobbies.explode('hobby')] expr2 = expr[expr.hobby.notnull() & (expr.id < 4)] expected = 'SELECT * \n' \ 'FROM (\n' \ ' SELECT t2.`id`, t2.`name`, t3.`hobby` \n' \ ' FROM (\n' \ ' SELECT * \n' \ ' FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ ' WHERE t1.`id` < 4\n' \ ' ) t2 \n' \ ' LATERAL VIEW EXPLODE(t2.`hobbies`) t3 AS `hobby` \n' \ ') t4 \n' \ 'WHERE t4.`hobby` IS NOT NULL' self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr2, prettify=False))) expected = 'SELECT * \n' \ 'FROM (\n' \ ' SELECT t1.`id`, t1.`name`, t2.`hobby` \n' \ ' FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ ' LATERAL VIEW EXPLODE(t1.`hobbies`) t2 AS `hobby` \n' \ ') t3 \n' \ 'WHERE (t3.`hobby` IS NOT NULL) OR (t3.`id` < 4)' expr3 = expr[expr.hobby.notnull() | (expr.id < 4)] self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr3, prettify=False))) def testFilterPushdownJoinFilterProjection(self): expr = self.expr.join(self.expr3, on='id') expr2 = expr[expr.scale < 5] expr3 = expr2['name_x', 'id', 'fid_y'] expr4 = expr3.query('name_x > "b" and fid_y < 3') expected = "SELECT t2.`name` AS `name_x`, t2.`id`, t4.`fid` AS `fid_y` \n" \ "FROM (\n" \ " SELECT t1.`name`, t1.`id`, t1.`scale` \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ " WHERE (t1.`name` > 'b') AND (t1.`scale` < 5)\n" \ ") t2 \n" \ "INNER JOIN \n" \ " (\n" \ " SELECT t3.`id`, t3.`fid` \n" \ " FROM mocked_project.`pyodps_test_expr_table2` t3 \n" \ " WHERE t3.`fid` < 3\n" \ " ) t4\n" \ "ON t2.`id` == t4.`id`" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr4, prettify=False))) def testFilterPushdownJoinUnion(self): expr = self.expr.union(self.expr1) expr2 = expr.join(self.expr3, on='name', suffixes=('', '_2')) expr3 = expr2.filter(expr2.id < 3, expr2.fid > 4, expr2.isMale) expected = "SELECT t3.`name`, t3.`id`, t3.`fid`, t3.`isMale`, t3.`scale`, t3.`birth`, " \ "t4.`id` AS `id_2`, t4.`fid` AS `fid_2`, t4.`part1`, t4.`part2` \n" \ "FROM (\n" \ " SELECT * \n" \ " FROM (\n" \ " SELECT * \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ " WHERE ((t1.`fid` > 4) AND (t1.`id` < 3)) AND t1.`isMale` \n" \ " UNION ALL\n" \ " SELECT * \n" \ " FROM mocked_project.`pyodps_test_expr_table1` t2 \n" \ " WHERE ((t2.`fid` > 4) AND (t2.`id` < 3)) AND t2.`isMale`\n" \ " ) t3\n" \ ") t3 \n" \ "INNER JOIN \n" \ " mocked_project.`pyodps_test_expr_table2` t4\n" \ "ON t3.`name` == t4.`name`" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr3, prettify=False)))
class Test(TestBase): def setup(self): datatypes = lambda *types: [validate_data_type(t) for t in types] schema = Schema.from_lists( ['name', 'id', 'fid', 'isMale', 'scale', 'birth'], datatypes('string', 'int64', 'float64', 'boolean', 'decimal', 'datetime'), ['ds'], datatypes('string')) table = MockTable(name='pyodps_test_expr_table', schema=schema) self.expr = CollectionExpr(_source_data=table, _schema=schema) table1 = MockTable(name='pyodps_test_expr_table1', schema=schema) self.expr1 = CollectionExpr(_source_data=table1, _schema=schema) table2 = MockTable(name='pyodps_test_expr_table2', schema=schema) self.expr2 = CollectionExpr(_source_data=table2, _schema=schema) schema2 = Schema.from_lists(['name', 'id', 'fid'], datatypes('string', 'int64', 'float64'), ['part1', 'part2'], datatypes('string', 'int64')) table3 = MockTable(name='pyodps_test_expr_table2', schema=schema2) self.expr3 = CollectionExpr(_source_data=table3, _schema=schema2) self.maxDiff = None def testFilterPushdownThroughProjection(self): expr = self.expr[self.expr.id + 1, 'name'][lambda x: x.id < 10] expected = 'SELECT t1.`id` + 1 AS `id`, t1.`name` \n' \ 'FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ 'WHERE (t1.`id` + 1) < 10' self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr['name', self.expr.id ** 2]\ .filter(lambda x: x.name == 'name1').filter(lambda x: x.id < 3) expected = "SELECT t1.`name`, CAST(POW(t1.`id`, 2) AS BIGINT) AS `id` \n" \ "FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ "WHERE (t1.`name` == 'name1') AND ((CAST(POW(t1.`id`, 2) AS BIGINT)) < 3)" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr['name', self.expr.id + 1].filter( lambda x: x.name == 'name1')[lambda x: 'tt' + x.name, 'id'].filter(lambda x: x.id < 3) expected = "SELECT CONCAT('tt', t1.`name`) AS `name`, t1.`id` + 1 AS `id` \n" \ "FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ "WHERE (t1.`name` == 'name1') AND ((t1.`id` + 1) < 3)" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.filter(self.expr.name == 'name1').select( 'name', lambda x: (x.id + 1) * 2)[lambda x: 'tt' + x.name, 'id'].filter(lambda x: x.id < 3) expected = "SELECT CONCAT('tt', t1.`name`) AS `name`, (t1.`id` + 1) * 2 AS `id` \n" \ "FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ "WHERE (((t1.`id` + 1) * 2) < 3) AND (t1.`name` == 'name1')" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.filter( self.expr.id.between(2, 6), self.expr.name.lower().contains('pyodps', regex=False)).name.nunique() expected = "SELECT COUNT(DISTINCT t2.`name`) AS `name_nunique` \n" \ "FROM (\n" \ " SELECT t1.`name`, t1.`id` \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ " WHERE ((t1.`id` >= 2) AND (t1.`id` <= 6)) AND INSTR(TOLOWER(t1.`name`), 'pyodps') > 0 \n" \ ") t2" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testFilterPushDownThroughJoin(self): expr = self.expr.join(self.expr3, on='name') expr = expr[(expr.id_x < 10) & (expr.fid_y > 3)] expected = 'SELECT t2.`name`, t2.`id` AS `id_x`, t2.`fid` AS `fid_x`, ' \ 't2.`isMale`, t2.`scale`, t2.`birth`, t2.`ds`, t4.`id` AS `id_y`, ' \ 't4.`fid` AS `fid_y`, t4.`part1`, t4.`part2` \n' \ 'FROM (\n' \ ' SELECT * \n' \ ' FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ ' WHERE t1.`id` < 10\n' \ ') t2 \n' \ 'INNER JOIN \n' \ ' (\n' \ ' SELECT * \n' \ ' FROM mocked_project.`pyodps_test_expr_table2` t3 \n' \ ' WHERE t3.`fid` > 3\n' \ ' ) t4\n' \ 'ON t2.`name` == t4.`name`' self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.join(self.expr3, on='name') expr = expr[(expr.id_x < 10) & (expr.fid_y > 3) & (expr.id_x > 3)] expected = 'SELECT t2.`name`, t2.`id` AS `id_x`, t2.`fid` AS `fid_x`, ' \ 't2.`isMale`, t2.`scale`, t2.`birth`, t2.`ds`, t4.`id` AS `id_y`, ' \ 't4.`fid` AS `fid_y`, t4.`part1`, t4.`part2` \n' \ 'FROM (\n' \ ' SELECT * \n' \ ' FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ ' WHERE (t1.`id` < 10) AND (t1.`id` > 3)\n' \ ') t2 \n' \ 'INNER JOIN \n' \ ' (\n' \ ' SELECT * \n' \ ' FROM mocked_project.`pyodps_test_expr_table2` t3 \n' \ ' WHERE t3.`fid` > 3\n' \ ' ) t4\n' \ 'ON t2.`name` == t4.`name`' self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr[self.expr.name, self.expr.id + 1] expr2 = self.expr3['tt' + self.expr3.name, self.expr3.id.rename('id2')] expr = expr.join(expr2, on='name') expr = expr[((expr.id < 10) | (expr.id > 100)) & (expr.id2 > 3)] expected = "SELECT t2.`name`, t2.`id`, t4.`id2` \n" \ "FROM (\n" \ " SELECT t1.`name`, t1.`id` + 1 AS `id` \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ " WHERE ((t1.`id` + 1) < 10) OR ((t1.`id` + 1) > 100)\n" \ ") t2 \n" \ "INNER JOIN \n" \ " (\n" \ " SELECT CONCAT('tt', t3.`name`) AS `name`, t3.`id` AS `id2` \n" \ " FROM mocked_project.`pyodps_test_expr_table2` t3 \n" \ " WHERE t3.`id` > 3\n" \ " ) t4\n" \ "ON t2.`name` == t4.`name`" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.join(self.expr3, on='name') expr = expr[(expr.id_x + expr.id_y < 10) & (expr.id_x > 3)] expected = "SELECT * \n" \ "FROM (\n" \ " SELECT t2.`name`, t2.`id` AS `id_x`, t2.`fid` AS `fid_x`, t2.`isMale`, " \ "t2.`scale`, t2.`birth`, t2.`ds`, t3.`id` AS `id_y`, " \ "t3.`fid` AS `fid_y`, t3.`part1`, t3.`part2` \n" \ " FROM (\n" \ " SELECT * \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ " WHERE t1.`id` > 3\n" \ " ) t2 \n" \ " INNER JOIN \n" \ " mocked_project.`pyodps_test_expr_table2` t3\n" \ " ON t2.`name` == t3.`name` \n" \ ") t4 \n" \ "WHERE (t4.`id_x` + t4.`id_y`) < 10" self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.join(self.expr3, on=[ 'name', self.expr.id == self.expr3.id, self.expr.id < 10, self.expr3.name == 'name1', self.expr.id > 5 ]) expected = 'SELECT t2.`name`, t2.`id`, t2.`fid` AS `fid_x`, t2.`isMale`, ' \ 't2.`scale`, t2.`birth`, t2.`ds`, t4.`fid` AS `fid_y`, t4.`part1`, t4.`part2` \n' \ 'FROM (\n' \ ' SELECT * \n' \ ' FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ ' WHERE (t1.`id` < 10) AND (t1.`id` > 5)\n' \ ') t2 \n' \ 'INNER JOIN \n' \ ' (\n' \ ' SELECT * \n' \ ' FROM mocked_project.`pyodps_test_expr_table2` t3 \n' \ ' WHERE t3.`name` == \'name1\'\n' \ ' ) t4\n' \ 'ON (t2.`name` == t4.`name`) AND (t2.`id` == t4.`id`)' self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testFilterPushdownThroughUnion(self): expr = self.expr['name', 'id'].union(self.expr2['id', 'name']) expr = expr.filter(expr.id + 1 < 3) expected = 'SELECT * \n' \ 'FROM (\n' \ ' SELECT t1.`name`, t1.`id` \n' \ ' FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ ' WHERE (t1.`id` + 1) < 3 \n' \ ' UNION ALL\n' \ ' SELECT t2.`name`, t2.`id` \n' \ ' FROM mocked_project.`pyodps_test_expr_table2` t2 \n' \ ' WHERE (t2.`id` + 1) < 3\n' \ ') t3' self.assertEqual( to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False)))
class Test(TestBase): def setup(self): datatypes = lambda *types: [validate_data_type(t) for t in types] schema = Schema.from_lists(['name', 'id', 'fid', 'isMale', 'scale', 'birth'], datatypes('string', 'int64', 'float64', 'boolean', 'decimal', 'datetime'), ['ds'], datatypes('string')) table = MockTable(name='pyodps_test_expr_table', schema=schema) self.expr = CollectionExpr(_source_data=table, _schema=Schema(columns=schema.columns)) table1 = MockTable(name='pyodps_test_expr_table1', schema=schema) self.expr1 = CollectionExpr(_source_data=table1, _schema=Schema(columns=schema.columns)) table2 = MockTable(name='pyodps_test_expr_table2', schema=schema) self.expr2 = CollectionExpr(_source_data=table2, _schema=Schema(columns=schema.columns)) schema2 = Schema.from_lists(['name', 'id', 'fid'], datatypes('string', 'int64', 'float64'), ['part1', 'part2'], datatypes('string', 'int64')) table3 = MockTable(name='pyodps_test_expr_table2', schema=schema2) self.expr3 = CollectionExpr(_source_data=table3, _schema=Schema(columns=schema2.columns)) def testProjectPrune(self): expr = self.expr.select('name', 'id') new_expr = ColumnPruning(expr.to_dag()).prune() self.assertIsInstance(new_expr, ProjectCollectionExpr) self.assertIsNotNone(new_expr.input._source_data) expected = 'SELECT t1.`name`, t1.`id` \n' \ 'FROM mocked_project.`pyodps_test_expr_table` t1' self.assertEqual(expected, ODPSEngine(self.odps).compile(expr, prettify=False)) expr = self.expr[Scalar(3).rename('const'), NullScalar('string').rename('string_const'), self.expr.id] expected = 'SELECT 3 AS `const`, CAST(NULL AS STRING) AS `string_const`, t1.`id` \n' \ 'FROM mocked_project.`pyodps_test_expr_table` t1' self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.select(pt=BuiltinFunction('max_pt', args=(self.expr._source_data.name,))) expected = "SELECT max_pt('pyodps_test_expr_table') AS `pt` \n" \ "FROM mocked_project.`pyodps_test_expr_table` t1" self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testApplyPrune(self): @output(['name', 'id'], ['string', 'string']) def h(row): yield row[0], row[1] expr = self.expr[self.expr.fid < 0].apply(h, axis=1)['id', ] new_expr = ColumnPruning(expr.to_dag()).prune() self.assertIsInstance(new_expr, ProjectCollectionExpr) self.assertIsInstance(new_expr.input.input, FilterCollectionExpr) self.assertIsNotNone(new_expr.input.input.input._source_data) def testFilterPrune(self): expr = self.expr.filter(self.expr.name == 'name1') expr = expr['name', 'id'] new_expr = ColumnPruning(expr.to_dag()).prune() self.assertIsInstance(new_expr.input, FilterCollectionExpr) self.assertNotIsInstance(new_expr.input.input, ProjectCollectionExpr) self.assertIsNotNone(new_expr.input.input._source_data) expected = 'SELECT t1.`name`, t1.`id` \n' \ 'FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ 'WHERE t1.`name` == \'name1\'' self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.filter(self.expr.name == 'name1') new_expr = ColumnPruning(expr.to_dag()).prune() self.assertIsInstance(new_expr, FilterCollectionExpr) self.assertIsNotNone(new_expr.input._source_data) expr = self.expr.filter(self.expr.id.isin(self.expr3.id)) expected = 'SELECT * \n' \ 'FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ 'WHERE t1.`id` IN (SELECT t3.`id` FROM ( ' \ 'SELECT t2.`id` FROM mocked_project.`pyodps_test_expr_table2` t2 ) t3)' self.assertTrue(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testFilterPartitionPrune(self): expr = self.expr.filter_partition('ds=today')[lambda x: x.fid < 0][ 'name', lambda x: x.id + 1] new_expr = ColumnPruning(expr.to_dag()).prune() self.assertEqual(set(new_expr.input.input.schema.names), set(['name', 'id', 'fid'])) expected = "SELECT t2.`name`, t2.`id` + 1 AS `id` \n" \ "FROM (\n" \ " SELECT t1.`name`, t1.`id`, t1.`fid` \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ " WHERE t1.`ds` == 'today' \n" \ ") t2 \n" \ "WHERE t2.`fid` < 0" self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testSlicePrune(self): expr = self.expr.filter(self.expr.fid < 0)[:4]['name', lambda x: x.id + 1] new_expr = ColumnPruning(expr.to_dag()).prune() self.assertIsNotNone(new_expr.input.input.input._source_data) expected = "SELECT t1.`name`, t1.`id` + 1 AS `id` \n" \ "FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ "WHERE t1.`fid` < 0 \n" \ "LIMIT 4" self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testGroupbyPrune(self): expr = self.expr.groupby('name').agg(id=self.expr.id.max()) expr = expr[expr.id < 0]['name', ] expected = "SELECT t1.`name` \n" \ "FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ "GROUP BY t1.`name` \n" \ "HAVING MAX(t1.`id`) < 0" self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.groupby('name').agg(id=self.expr.id.max()) expr = expr[expr.id < 0]['id',] expected = "SELECT MAX(t1.`id`) AS `id` \n" \ "FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ "GROUP BY t1.`name` \n" \ "HAVING MAX(t1.`id`) < 0" self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testMutatePrune(self): expr = self.expr[self.expr.exclude('birth'), self.expr.fid.astype('int').rename('new_id')] expr = expr[expr, expr.groupby('name').mutate(lambda x: x.new_id.cumsum().rename('new_id_sum'))] expr = expr[expr.new_id, expr.new_id_sum] expected = "SELECT t2.`new_id`, t2.`new_id_sum` \n" \ "FROM (\n" \ " SELECT CAST(t1.`fid` AS BIGINT) AS `new_id`, " \ "SUM(CAST(t1.`fid` AS BIGINT)) OVER (PARTITION BY t1.`name`) AS `new_id_sum` \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ ") t2" self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testValueCountsPrune(self): expr = self.expr.name.value_counts()['count', ] new_expr = ColumnPruning(expr.to_dag()).prune() self.assertIsInstance(new_expr.input.input, ProjectCollectionExpr) self.assertEqual(set(new_expr.input.input.schema.names), set(['name'])) def testSortPrune(self): expr = self.expr[self.expr.exclude('name'), self.expr.name.rename('name2')].sort('name2')['id', 'fid'] expected = "SELECT t2.`id`, t2.`fid` \n" \ "FROM (\n" \ " SELECT t1.`id`, t1.`fid`, t1.`name` AS `name2` \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ " ORDER BY name2 \n" \ " LIMIT 10000\n" \ ") t2" self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testDistinctPrune(self): expr = self.expr.distinct(self.expr.id + 1, self.expr.name)['name', ] expected = "SELECT t2.`name` \n" \ "FROM (\n" \ " SELECT DISTINCT t1.`id` + 1 AS `id`, t1.`name` \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ ") t2" self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testSamplePrune(self): expr = self.expr['name', 'id'].sample(parts=5)['id', ] expected = "SELECT t1.`id` \n" \ "FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ "WHERE SAMPLE(5, 1)" self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testJoinPrune(self): left = self.expr.select(self.expr, type='normal') right = self.expr3[:4] joined = left.left_join(right, on='id') expr = joined.id_x.rename('id') expected = "SELECT t2.`id` \n" \ "FROM (\n" \ " SELECT t1.`id` \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1\n" \ ") t2 \n" \ "LEFT OUTER JOIN \n" \ " (\n" \ " SELECT t3.`id` \n" \ " FROM mocked_project.`pyodps_test_expr_table2` t3 \n" \ " LIMIT 4\n" \ " ) t4\n" \ "ON t2.`id` == t4.`id`" self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) joined = self.expr.join(self.expr2, 'name') expected = 'SELECT t1.`name`, t1.`id` AS `id_x`, t1.`fid` AS `fid_x`, ' \ 't1.`isMale` AS `isMale_x`, t1.`scale` AS `scale_x`, ' \ 't1.`birth` AS `birth_x`, t1.`ds` AS `ds_x`, t2.`id` AS `id_y`, ' \ 't2.`fid` AS `fid_y`, t2.`isMale` AS `isMale_y`, t2.`scale` AS `scale_y`, ' \ 't2.`birth` AS `birth_y`, t2.`ds` AS `ds_y` \n' \ 'FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ 'INNER JOIN \n' \ ' mocked_project.`pyodps_test_expr_table2` t2\n' \ 'ON t1.`name` == t2.`name`' self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(joined, prettify=False))) def testUnionPrune(self): left = self.expr.select('name', 'id') right = self.expr3.select(self.expr3.fid.astype('int').rename('id'), self.expr3.name) expr = left.union(right)['id'] expected = "SELECT t3.`id` \n" \ "FROM (\n" \ " SELECT t1.`id` \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ " UNION ALL\n" \ " SELECT CAST(t2.`fid` AS BIGINT) AS `id` \n" \ " FROM mocked_project.`pyodps_test_expr_table2` t2\n" \ ") t3" self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.union(self.expr2) expected = 'SELECT * \n' \ 'FROM (\n' \ ' SELECT * \n' \ ' FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ ' UNION ALL\n' \ ' SELECT * \n' \ ' FROM mocked_project.`pyodps_test_expr_table2` t2\n' \ ') t3' self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False)))
class Test(TestBase): def setup(self): datatypes = lambda *types: [validate_data_type(t) for t in types] schema = Schema.from_lists( ['name', 'id', 'fid', 'isMale', 'birth', 'scale'][:5], datatypes('string', 'int64', 'float64', 'boolean', 'datetime', 'decimal')[:5]) self.schema = df_schema_to_odps_schema(schema) table_name = tn('pyodps_test_%s' % str(uuid.uuid4()).replace('-', '_')) self.odps.delete_table(table_name, if_exists=True) self.table = self.odps.create_table(name=table_name, schema=self.schema) self.expr = CollectionExpr(_source_data=self.table, _schema=schema) self.engine = SeahawksEngine(self.odps) class FakeBar(object): def update(self, *args, **kwargs): pass def inc(self, *args, **kwargs): pass def status(self, *args, **kwargs): pass self.faked_bar = FakeBar() def teardown(self): self.table.drop() def _gen_data(self, rows=None, data=None, nullable_field=None, value_range=None): if data is None: data = [] for _ in range(rows): record = [] for t in self.schema.types: method = getattr(self, '_gen_random_%s' % t.name) if t.name == 'bigint': record.append(method(value_range=value_range)) else: record.append(method()) data.append(record) if nullable_field is not None: j = self.schema._name_indexes[nullable_field] for i, l in enumerate(data): if i % 2 == 0: data[i][j] = None self.odps.write_table(self.table, 0, data) return data def testAsync(self): data = self._gen_data(10, value_range=(-1000, 1000)) expr = self.expr.id.sum() future = self.engine.execute(expr, async=True) self.assertFalse(future.done()) res = future.result() self.assertEqual(sum(it[1] for it in data), res) def testCache(self): data = self._gen_data(10, value_range=(-1000, 1000)) expr = self.expr[self.expr.id < 10].cache() cnt = expr.count() dag = self.engine.compile(expr) self.assertEqual(len(dag.nodes()), 2) res = self.engine.execute(cnt) self.assertEqual(len([it for it in data if it[1] < 10]), res) self.assertTrue(context.is_cached(expr)) table = context.get_cached(expr) self.assertIsInstance(table, SeahawksTable) def testBatch(self): data = self._gen_data(10, value_range=(-1000, 1000)) expr = self.expr[self.expr.id < 10].cache() expr1 = expr.id.sum() expr2 = expr.id.mean() dag = self.engine.compile([expr1, expr2]) self.assertEqual(len(dag.nodes()), 3) self.assertEqual(sum(len(v) for v in dag._graph.values()), 2) expect1 = sum(d[1] for d in data if d[1] < 10) length = len([d[1] for d in data if d[1] < 10]) expect2 = (expect1 / float(length)) if length > 0 else 0.0 res = self.engine.execute([expr1, expr2], n_parallel=2) self.assertEqual(res[0], expect1) self.assertAlmostEqual(res[1], expect2) self.assertTrue(context.is_cached(expr)) # test async and timeout expr = self.expr[self.expr.id < 10] expr1 = expr.id.sum() expr2 = expr.id.mean() fs = self.engine.execute([expr, expr1, expr2], n_parallel=2, async=True, timeout=1) self.assertEqual(len(fs), 3) self.assertEqual(fs[1].result(), expect1) self.assertAlmostEqual(fs[2].result(), expect2) self.assertTrue(context.is_cached(expr)) def testBase(self): data = self._gen_data(10, value_range=(-1000, 1000)) expr = self.expr[self.expr.id < 10]['name', lambda x: x.id] result = self._get_result(self.engine.execute(expr).values) self.assertEqual(len([it for it in data if it[1] < 10]), len(result)) if len(result) > 0: self.assertEqual(2, len(result[0])) expr = self.expr[Scalar(3).rename('const'), self.expr.id, (self.expr.id + 1).rename('id2')] res = self.engine.execute(expr) result = self._get_result(res.values) self.assertEqual([c.name for c in res.columns], ['const', 'id', 'id2']) self.assertTrue(all(it[0] == 3 for it in result)) self.assertEqual(len(data), len(result)) self.assertEqual([it[1] + 1 for it in data], [it[2] for it in result]) expr = self.expr.sort('id')[:5] res = self.engine.execute(expr) result = self._get_result(res.values) self.assertListAlmostEqual( sorted(data, key=lambda it: it[1])[:5], [r[:-1] + [r[-1].replace(tzinfo=None)] for r in result], only_float=False, delta=.001) expr = self.expr[:1].filter(lambda x: x.name == data[1][0]) res = self.engine.execute(expr) self.assertEqual(len(res), 0) def testChinese(self): data = [ ['中文', 4, 5.3, None, None], ['\'中文2', 2, 3.5, None, None], ] self._gen_data(data=data) expr = self.expr.filter(self.expr.name == '中文') res = self.engine.execute(expr) self.assertEqual(len(res), 1) expr = self.expr.filter(self.expr.name == '\'中文2') res = self.engine.execute(expr) self.assertEqual(len(res), 1) expr = self.expr.filter(self.expr.name == u'中文') res = self.engine.execute(expr) self.assertEqual(len(res), 1) def testElement(self): data = self._gen_data(5, nullable_field='name') fields = [ self.expr.name.isnull().rename('name1'), self.expr.name.notnull().rename('name2'), self.expr.name.fillna('test').rename('name3'), self.expr.id.isin([1, 2, 3]).rename('id1'), self.expr.id.isin(self.expr.fid.astype('int')).rename('id2'), self.expr.id.notin([1, 2, 3]).rename('id3'), self.expr.id.notin(self.expr.fid.astype('int')).rename('id4'), self.expr.id.between(self.expr.fid, 3).rename('id5'), self.expr.name.fillna('test').switch( 'test', 'test' + self.expr.name.fillna('test'), 'test2', 'test2' + self.expr.name.fillna('test'), default=self.expr.name).rename('name4'), self.expr.name.fillna('test').switch('test', 1, 'test2', 2).rename('name5'), self.expr.id.cut([100, 200, 300], labels=['xsmall', 'small', 'large', 'xlarge'], include_under=True, include_over=True).rename('id6'), self.expr.id.between(self.expr.fid, 3, inclusive=False).rename('id7'), ] expr = self.expr[fields] res = self.engine.execute(expr) result = self._get_result(res) self.assertEqual(len(data), len(result)) self.assertEqual(len([it for it in data if it[0] is None]), len([it[0] for it in result if it[0]])) self.assertEqual(len([it[0] for it in data if it[0] is not None]), len([it[1] for it in result if it[1]])) self.assertEqual([(it[0] if it[0] is not None else 'test') for it in data], [it[2] for it in result]) self.assertEqual([(it[1] in (1, 2, 3)) for it in data], [it[3] for it in result]) fids = [int(it[2]) for it in data] self.assertEqual([(it[1] in fids) for it in data], [it[4] for it in result]) self.assertEqual([(it[1] not in (1, 2, 3)) for it in data], [it[5] for it in result]) self.assertEqual([(it[1] not in fids) for it in data], [it[6] for it in result]) self.assertEqual([(it[2] <= it[1] <= 3) for it in data], [it[7] for it in result]) self.assertEqual( [to_str('testtest' if it[0] is None else it[0]) for it in data], [to_str(it[8]) for it in result]) self.assertEqual([to_str(1 if it[0] is None else None) for it in data], [to_str(it[9]) for it in result]) def get_val(val): if val <= 100: return 'xsmall' elif 100 < val <= 200: return 'small' elif 200 < val <= 300: return 'large' else: return 'xlarge' self.assertEqual([to_str(get_val(it[1])) for it in data], [to_str(it[10]) for it in result]) self.assertEqual([(it[2] < it[1] < 3) for it in data], [it[11] for it in result]) def testArithmetic(self): data = self._gen_data(5, value_range=(-1000, 1000)) fields = [ (self.expr.id + 1).rename('id1'), (self.expr.fid - 1).rename('fid1'), (self.expr.id / 2).rename('id2'), (self.expr.id**2).rename('id3'), abs(self.expr.id).rename('id4'), (~self.expr.id).rename('id5'), (-self.expr.fid).rename('fid2'), (~self.expr.isMale).rename('isMale1'), (-self.expr.isMale).rename('isMale2'), (self.expr.id // 2).rename('id6'), (self.expr.id % 2).rename('id7'), ] expr = self.expr[fields] res = self.engine.execute(expr) result = self._get_result(res) self.assertEqual(len(data), len(result)) self.assertEqual([it[1] + 1 for it in data], [it[0] for it in result]) self.assertListAlmostEqual([it[2] - 1 for it in data], [it[1] for it in result], delta=.001) self.assertListAlmostEqual([float(it[1]) / 2 for it in data], [it[2] for it in result], delta=.001) self.assertEqual([int(it[1]**2) for it in data], [it[3] for it in result]) self.assertEqual([abs(it[1]) for it in data], [it[4] for it in result]) self.assertEqual([~it[1] for it in data], [it[5] for it in result]) self.assertListAlmostEqual([-it[2] for it in data], [it[6] for it in result], delta=.001) self.assertEqual([not it[3] for it in data], [it[7] for it in result]) self.assertEqual([it[1] // 2 for it in data], [it[9] for it in result]) self.assertEqual([it[1] % 2 for it in data], [it[10] for it in result]) def testMath(self): # TODO: test sinh, cosh..., and acosh, asinh... data = self._gen_data(5, value_range=(1, 90)) if hasattr(math, 'expm1'): expm1 = math.expm1 else: expm1 = lambda x: 2 * math.exp(x / 2.0) * math.sinh(x / 2.0) methods_to_fields = [ (math.sin, self.expr.id.sin()), (math.cos, self.expr.id.cos()), (math.tan, self.expr.id.tan()), (math.log, self.expr.id.log()), (lambda v: math.log(v, 2), self.expr.id.log2()), (math.log10, self.expr.id.log10()), (math.log1p, self.expr.id.log1p()), (math.exp, self.expr.id.exp()), (expm1, self.expr.id.expm1()), (math.atan, self.expr.id.arctan()), (math.sqrt, self.expr.id.sqrt()), (abs, self.expr.id.abs()), (math.ceil, self.expr.id.ceil()), (math.floor, self.expr.id.floor()), (math.trunc, self.expr.id.trunc()), ] fields = [ it[1].rename('id' + str(i)) for i, it in enumerate(methods_to_fields) ] expr = self.expr[fields] res = self.engine.execute(expr) result = self._get_result(res) for i, it in enumerate(methods_to_fields): mt = it[0] def method(v): try: return mt(v) except ValueError: return float('nan') first = [method(it[1]) for it in data] second = [it[i] for it in result] self.assertEqual(len(first), len(second)) for it1, it2 in zip(first, second): not_valid = lambda x: \ x is None or (isinstance(x, float) and (math.isnan(x) or math.isinf(x))) if not_valid(it1) and not_valid(it2): continue if isinstance(it1, float) and it1 > 1.0e15: scale = 0.1**(int(math.log10(it1)) - 15) self.assertAlmostEqual(it1 * scale, it2 * scale, delta=8) else: self.assertAlmostEqual(it1, it2, delta=2) def testString(self): data = self._gen_data(5) methods_to_fields = [ (lambda s: s.capitalize(), self.expr.name.capitalize()), (lambda s: data[0][0] in s, self.expr.name.contains(data[0][0], regex=False)), (lambda s: s[0] + '|' + str(s[1]), self.expr.name.cat(self.expr.id.astype('string'), sep='|')), (lambda s: s.endswith(data[0][0]), self.expr.name.endswith(data[0][0])), (lambda s: s.startswith(data[0][0]), self.expr.name.startswith(data[0][0])), (lambda s: s.replace(data[0][0], 'test'), self.expr.name.replace(data[0][0], 'test', regex=False)), (lambda s: s[0], self.expr.name.get(0)), (lambda s: len(s), self.expr.name.len()), (lambda s: s.ljust(10), self.expr.name.ljust(10)), (lambda s: s.ljust(20, '*'), self.expr.name.ljust(20, fillchar='*')), (lambda s: s.rjust(10), self.expr.name.rjust(10)), (lambda s: s.rjust(20, '*'), self.expr.name.rjust(20, fillchar='*')), (lambda s: s * 4, self.expr.name.repeat(4)), (lambda s: s[1:], self.expr.name.slice(1)), (lambda s: s[1:6], self.expr.name.slice(1, 6)), (lambda s: s.title(), self.expr.name.title()), (lambda s: s.rjust(20, '0'), self.expr.name.zfill(20)), ] fields = [ it[1].rename('id' + str(i)) for i, it in enumerate(methods_to_fields) ] expr = self.expr[fields] res = self.engine.execute(expr) result = self._get_result(res) for i, it in enumerate(methods_to_fields): method = it[0] if i != 2: first = [method(it[0]) for it in data] else: # cat first = [method(it) for it in data] second = [it[i] for it in result] self.assertEqual(first, second) def testDatetime(self): data = self._gen_data(5) def date_value(sel): if isinstance(sel, six.string_types): fun = lambda v: getattr(v, sel) else: fun = sel col_id = [ idx for idx, col in enumerate(self.schema.names) if col == 'birth' ][0] return [fun(row[col_id]) for row in data] methods_to_fields = [ (partial(date_value, 'year'), self.expr.birth.year), (partial(date_value, 'month'), self.expr.birth.month), (partial(date_value, 'day'), self.expr.birth.day), (partial(date_value, 'hour'), self.expr.birth.hour), (partial(date_value, 'minute'), self.expr.birth.minute), (partial(date_value, 'second'), self.expr.birth.second), (partial(date_value, lambda d: d.isocalendar()[1]), self.expr.birth.weekofyear), (partial(date_value, lambda d: d.weekday()), self.expr.birth.dayofweek), (partial(date_value, lambda d: d.weekday()), self.expr.birth.weekday), (partial(date_value, lambda d: time.mktime(d.timetuple())), self.expr.birth.unix_timestamp), (partial( date_value, lambda d: datetime.combine(d.date(), datetime.min.time())), self.expr.birth.date), ] fields = [ it[1].rename('birth' + str(i)) for i, it in enumerate(methods_to_fields) ] expr = self.expr[fields] res = self.engine.execute(expr) result = self._get_result(res) for i, it in enumerate(methods_to_fields): method = it[0] first = method() try: import pandas as pd def conv(v): if isinstance(v, pd.Timestamp): v = v.to_datetime() if isinstance(v, datetime): return v.replace(tzinfo=None) return v except ImportError: conv = lambda v: v second = [conv(it[i]) for it in result] self.assertEqual(first, second) def testSortDistinct(self): data = [ ['name1', 4, None, None, None], ['name2', 2, None, None, None], ['name1', 4, None, None, None], ['name1', 3, None, None, None], ] self._gen_data(data=data) expr = self.expr.sort(['name', -self.expr.id ]).distinct(['name', lambda x: x.id + 1])[:50] res = self.engine.execute(expr) result = self._get_result(res) self.assertEqual(len(result), 3) expected = [['name1', 5], ['name1', 4], ['name2', 3]] self.assertEqual(sorted(expected), sorted(result)) def testPivotTable(self): data = [['name1', 1, 1.0, True, None], ['name1', 1, 5.0, True, None], ['name1', 2, 2.0, True, None], ['name2', 1, 3.0, False, None], ['name2', 3, 4.0, False, None]] self._gen_data(data=data) expr = self.expr expr1 = expr.pivot_table(rows='name', values='fid') res = self.engine.execute(expr1) result = self._get_result(res) expected = [ ['name1', 8.0 / 3], ['name2', 3.5], ] self.assertListAlmostEqual(sorted(result), sorted(expected), only_float=False) expr2 = expr.pivot_table(rows='name', values='fid', aggfunc=['mean', 'sum']) res = self.engine.execute(expr2) result = self._get_result(res) expected = [ ['name1', 8.0 / 3, 8.0], ['name2', 3.5, 7.0], ] self.assertEqual(res.schema.names, ['name', 'fid_mean', 'fid_sum']) self.assertListAlmostEqual(sorted(result), sorted(expected), only_float=False) expr5 = expr.pivot_table(rows='id', values='fid', columns='name', aggfunc=['mean', 'sum']) expr6 = expr5['name1_fid_mean', expr5.groupby(Scalar(1)).sort('name1_fid_mean'). name1_fid_mean.astype('float').cumsum()] k = lambda x: list(0 if it is None else it for it in x) expected = [[2, 2], [3, 5], [None, 5]] res = self.engine.execute(expr6) result = self._get_result(res) self.assertEqual(sorted(result, key=k), sorted(expected, key=k)) expr3 = expr.pivot_table(rows='id', values='fid', columns='name', fill_value=0).distinct() res = self.engine.execute(expr3) result = self._get_result(res) expected = [ [2, 0, 2.0], [3, 4.0, 0], [1, 3.0, 3.0], ] self.assertEqual(res.schema.names, ['id', 'name2_fid_mean', 'name1_fid_mean']) self.assertEqual(result, expected) expr7 = expr.pivot_table(rows='id', values='fid', columns='name', aggfunc=['mean', 'sum']).cache() self.assertEqual(len(self.engine.execute(expr7)), 3) expr8 = self.expr.pivot_table(rows='id', values='fid', columns='name') self.assertEqual(len(self.engine.execute(expr8)), 3) self.assertNotIsInstance(expr8.schema, DynamicSchema) expr9 = (expr8['name1_fid_mean'] - expr8['name2_fid_mean']).rename('substract') self.assertEqual(len(self.engine.execute(expr9)), 3) expr10 = expr8.distinct() self.assertEqual(len(self.engine.execute(expr10)), 3) def testGroupbyAggregation(self): data = [ ['name1', 4, 5.3, None, None], ['name2', 2, 3.5, None, None], ['name1', 4, 4.2, None, None], ['name1', 3, 2.2, None, None], ['name1', 3, 4.1, None, None], ] self._gen_data(data=data) field = self.expr.groupby('name').sort(['id', -self.expr.fid]).row_number() expr = self.expr['name', 'id', 'fid', field] res = self.engine.execute(expr) result = self._get_result(res) expected = [ ['name1', 3, 4.1, 1], ['name1', 3, 2.2, 2], ['name1', 4, 5.3, 3], ['name1', 4, 4.2, 4], ['name2', 2, 3.5, 1], ] result = sorted(result, key=lambda k: (k[0], k[1], -k[2])) self.assertEqual(expected, result) expr = self.expr.name.value_counts(dropna=True)[:25] expected = [['name1', 4], ['name2', 1]] res = self.engine.execute(expr) result = self._get_result(res) self.assertEqual(expected, result) expr = self.expr.name.topk(25) res = self.engine.execute(expr) result = self._get_result(res) self.assertEqual(expected, result) expr = self.expr.groupby('name').count() res = self.engine.execute(expr) result = self._get_result(res) self.assertEqual(sorted([it[1:] for it in expected]), sorted(result)) expected = [['name1', 2], ['name2', 1]] expr = self.expr.groupby('name').id.nunique() res = self.engine.execute(expr) result = self._get_result(res) self.assertEqual([it[1:] for it in expected], result) expr = self.expr[self.expr['id'] > 2].name.value_counts()[:25] expected = [['name1', 4]] res = self.engine.execute(expr) result = self._get_result(res) self.assertEqual(expected, result) expr = self.expr.groupby('name', Scalar(1).rename('constant')) \ .agg(id=self.expr.id.sum()) expected = [['name1', 1, 14], ['name2', 1, 2]] res = self.engine.execute(expr) result = self._get_result(res) self.assertEqual(expected, result) expr = self.expr[:1] expr = expr.groupby('name').agg(expr.id.sum()) res = self.engine.execute(expr) result = self._get_result(res) expected = [['name1', 4]] self.assertEqual(expected, result) def testProjectionGroupbyFilter(self): data = [ ['name1', 4, 5.3, None, None], ['name2', 2, 3.5, None, None], ['name1', 4, 4.2, None, None], ['name1', 3, 2.2, None, None], ['name1', 3, 4.1, None, None], ] self._gen_data(data=data) df = self.expr.copy() df['id'] = df.id + 1 df2 = df.groupby('name').agg( id=df.id.sum())[lambda x: x.name == 'name2'] expected = [['name2', 3]] res = self.engine.execute(df2) result = self._get_result(res) self.assertEqual(expected, result) def testJoinGroupby(self): data = [ ['name1', 4, 5.3, None, None], ['name2', 2, 3.5, None, None], ['name1', 4, 4.2, None, None], ['name1', 3, 2.2, None, None], ['name1', 3, 4.1, None, None], ] schema2 = Schema.from_lists(['name', 'id2', 'id3'], [types.string, types.bigint, types.bigint]) table_name = tn('pyodps_test_engine_table2') self.odps.delete_table(table_name, if_exists=True) table2 = self.odps.create_table(name=table_name, schema=schema2) expr2 = CollectionExpr(_source_data=table2, _schema=odps_schema_to_df_schema(schema2)) self._gen_data(data=data) data2 = [['name1', 4, -1], ['name2', 1, -2]] self.odps.write_table(table2, 0, data2) expr = self.expr.join(expr2, on='name')[self.expr] expr = expr.groupby('id').agg(expr.fid.sum()) res = self.engine.execute(expr) result = self._get_result(res) id_idx = [ idx for idx, col in enumerate(self.expr.schema.names) if col == 'id' ][0] fid_idx = [ idx for idx, col in enumerate(self.expr.schema.names) if col == 'fid' ][0] expected = [[k, sum( v[fid_idx] for v in row)] for k, row in itertools.groupby( sorted(data, key=lambda r: r[id_idx]), lambda r: r[id_idx])] for it in zip(sorted(expected, key=lambda it: it[0]), sorted(result, key=lambda it: it[0])): self.assertAlmostEqual(it[0][0], it[1][0]) self.assertAlmostEqual(it[0][1], it[1][1]) def testFilterGroupby(self): data = [ ['name1', 4, 5.3, None, None], ['name2', 2, 3.5, None, None], ['name1', 4, 4.2, None, None], ['name1', 3, 2.2, None, None], ['name1', 3, 4.1, None, None], ] self._gen_data(data=data) expr = self.expr.groupby( ['name']).agg(id=self.expr.id.max())[lambda x: x.id > 3] res = self.engine.execute(expr) result = self._get_result(res) self.assertEqual(len(result), 1) expected = [['name1', 4]] self.assertEqual(expected, result) def testWindowFunction(self): data = [ ['name1', 4, 5.3, None, None], ['name2', 2, 3.5, None, None], ['name1', 4, 4.2, None, None], ['name1', 3, 2.2, None, None], ['name1', 3, 6.1, None, None], ] self._gen_data(data=data) expr = self.expr.groupby('name').id.cumsum() res = self.engine.execute(expr) result = self._get_result(res) expected = [[14]] * 4 + [[2]] self.assertEqual(sorted(expected), sorted(result)) expr = self.expr.groupby('name').sort('fid').id.cummax() res = self.engine.execute(expr) result = self._get_result(res) expected = [[3], [4], [4], [4], [2]] self.assertEqual(sorted(expected), sorted(result)) expr = self.expr[ self.expr.groupby('name', 'id').sort('fid').id.cummean(), ] res = self.engine.execute(expr) result = self._get_result(res) expected = [[3], [3], [4], [4], [2]] self.assertEqual(sorted(expected), sorted(result)) expr = self.expr.groupby('name').mutate( id2=lambda x: x.id.cumcount(), fid2=lambda x: x.fid.cummin(sort='id')) res = self.engine.execute(expr['name', 'id2', 'fid2']) result = self._get_result(res) expected = [ ['name1', 4, 2.2], ['name1', 4, 2.2], ['name1', 4, 2.2], ['name1', 4, 2.2], ['name2', 1, 3.5], ] self.assertEqual(sorted(expected), sorted(result)) expr = self.expr[ self.expr.id, self.expr.groupby('name').rank('id'), self.expr.groupby('name').dense_rank('fid', ascending=False), self.expr.groupby('name'). row_number(sort=['id', 'fid'], ascending=[True, False]), self.expr.groupby('name').percent_rank('id'), ] res = self.engine.execute(expr) result = self._get_result(res) expected = [[4, 3, 2, 3, float(2) / 3], [2, 1, 1, 1, 0.0], [4, 3, 3, 4, float(2) / 3], [3, 1, 4, 2, float(0) / 3], [3, 1, 1, 1, float(0) / 3]] [ self.assertListAlmostEqual(l, r) for l, r in zip(sorted(expected), sorted(result)) ] expr = self.expr[ self.expr.id, self.expr.groupby('name').id. lag(offset=3, default=0, sort=['id', 'fid']).rename('id2'), self.expr.groupby('name').id.lead(offset=1, default=-1, sort=['id', 'fid'], ascending=[False, False] ).rename('id3'), ] res = self.engine.execute(expr) result = self._get_result(res) expected = [[4, 3, 4], [2, 0, -1], [4, 0, 3], [3, 0, -1], [3, 0, 3]] self.assertEqual(sorted(expected), sorted(result)) def testWindowRewrite(self): data = [ ['name1', 4, 5.3, None, None], ['name2', 2, 3.5, None, None], ['name1', 4, 4.2, None, None], ['name1', 3, 2.2, None, None], ['name1', 3, 4.1, None, None], ] self._gen_data(data=data) expr = self.expr[self.expr.id - self.expr.id.mean() < 10][[ lambda x: x.id - x.id.max() ]][[lambda x: x.id - x.id.min()]][lambda x: x.id - x.id.std() > 0] res = self.engine.execute(expr) result = self._get_result(res) id_idx = [ idx for idx, col in enumerate(self.expr.schema.names) if col == 'id' ][0] expected = [r[id_idx] for r in data] maxv = max(expected) expected = [v - maxv for v in expected] minv = min(expected) expected = [v - minv for v in expected] meanv = sum(expected) * 1.0 / len(expected) meanv2 = sum([v**2 for v in expected]) * 1.0 / len(expected) std = math.sqrt(meanv2 - meanv**2) expected = [v for v in expected if v > std] self.assertEqual(expected, [it[0] for it in result]) def testReduction(self): data = self._gen_data(rows=5, value_range=(-100, 100)) def stats(col, func): col_idx = [ idx for idx, cn in enumerate(self.expr.schema.names) if cn == col ][0] return func([r[col_idx] for r in data]) def var(vct, ddof=0): meanv = mean(vct) meanv2 = mean([v**2 for v in vct]) return (meanv2 - meanv**2) * len(vct) / (len(vct) - ddof) def moment(vct, order, central=False, absolute=False): abs_fun = abs if absolute else lambda x: x if central: m = mean(vct) return mean([abs_fun(v - m)**order for v in vct]) else: return mean([abs_fun(v)**order for v in vct]) def skew(vct): n = len(vct) return moment(vct, 3, central=True) / (std( vct, 1)**3) * (n**2) / (n - 1) / (n - 2) def kurtosis(vct): n = len(vct) m4 = moment(vct, 4, central=True) m2 = var(vct, 0) return 1.0 / (n - 2) / (n - 3) * ((n * n - 1.0) * m4 / m2**2 - 3 * (n - 1)**2) mean = lambda v: sum(v) * 1.0 / len(v) std = lambda v, ddof=0: math.sqrt(var(v, ddof)) nunique = lambda v: len(set(v)) cat = lambda v: len([it for it in v if it is not None]) methods_to_fields = [ (partial(stats, 'id', mean), self.expr.id.mean()), (partial(len, data), self.expr.count()), (partial(stats, 'id', var), self.expr.id.var(ddof=0)), (partial(stats, 'id', lambda x: var(x, 1)), self.expr.id.var(ddof=1)), (partial(stats, 'id', std), self.expr.id.std(ddof=0)), (partial(stats, 'id', lambda x: moment(x, 3, central=True)), self.expr.id.moment(3, central=True)), (partial(stats, 'id', skew), self.expr.id.skew()), (partial(stats, 'id', kurtosis), self.expr.id.kurtosis()), (partial(stats, 'id', sum), self.expr.id.sum()), (partial(stats, 'id', min), self.expr.id.min()), (partial(stats, 'id', max), self.expr.id.max()), (partial(stats, 'isMale', min), self.expr.isMale.min()), (partial(stats, 'isMale', sum), self.expr.isMale.sum()), (partial(stats, 'isMale', any), self.expr.isMale.any()), (partial(stats, 'isMale', all), self.expr.isMale.all()), (partial(stats, 'name', nunique), self.expr.name.nunique()), (partial(stats, 'name', cat), self.expr.name.cat(sep='|')), (partial(stats, 'id', lambda x: len(x)), self.expr.id.count()), ] fields = [ it[1].rename('f' + str(i)) for i, it in enumerate(methods_to_fields) ] expr = self.expr[fields] res = self.engine.execute(expr) result = self._get_result(res) for i, it in enumerate(methods_to_fields): method = it[0] first = method() second = [it[i] for it in result][0] if i == len(methods_to_fields) - 2: # cat second = len(second.split('|')) if isinstance(first, float): self.assertAlmostEqual(first, second) else: if first != second: pass self.assertEqual(first, second) def testJoin(self): data = [ ['name1', 4, 5.3, None, None], ['name2', 2, 3.5, None, None], ['name1', 4, 4.2, None, None], ['name1', 3, 2.2, None, None], ['name1', 3, 4.1, None, None], ] schema2 = Schema.from_lists(['name', 'id2', 'id3'], [types.string, types.bigint, types.bigint]) table_name = tn('pyodps_test_engine_table2') self.odps.delete_table(table_name, if_exists=True) table2 = self.odps.create_table(name=table_name, schema=schema2) expr2 = CollectionExpr(_source_data=table2, _schema=odps_schema_to_df_schema(schema2)) self._gen_data(data=data) data2 = [['name1', 4, -1], ['name2', 1, -2]] self.odps.write_table(table2, 0, data2) try: expr = self.expr.join(expr2)['name', 'id2'] res = self.engine.execute(expr) result = self._get_result(res) self.assertEqual(len(result), 5) expected = [[to_str('name1'), 4], [to_str('name2'), 1]] self.assertTrue(all(it in expected for it in result)) expr = self.expr.join(expr2, on=['name', ('id', 'id2')])[self.expr.name, expr2.id2] res = self.engine.execute(expr) result = self._get_result(res) self.assertEqual(len(result), 2) expected = [to_str('name1'), 4] self.assertTrue(all(it == expected for it in result)) expr = self.expr.left_join(expr2, on=['name', ('id', 'id2')])[self.expr.name, expr2.id2] res = self.engine.execute(expr) result = self._get_result(res) expected = [['name1', 4], ['name2', None], ['name1', 4], ['name1', None], ['name1', None]] self.assertEqual(len(result), 5) self.assertTrue(all(it in expected for it in result)) expr = self.expr.right_join(expr2, on=['name', ('id', 'id2')])[self.expr.name, expr2.id2] res = self.engine.execute(expr) result = self._get_result(res) expected = [ ['name1', 4], ['name1', 4], [None, 1], ] self.assertEqual(len(result), 3) self.assertTrue(all(it in expected for it in result)) expr = self.expr.outer_join(expr2, on=['name', ('id', 'id2')])[self.expr.name, expr2.id2] res = self.engine.execute(expr) result = self._get_result(res) expected = [ ['name1', 4], ['name1', 4], ['name2', None], ['name1', None], ['name1', None], [None, 1], ] self.assertEqual(len(result), 6) self.assertTrue(all(it in expected for it in result)) grouped = self.expr.groupby('name').agg( new_id=self.expr.id.sum()).cache() self.engine.execute(self.expr.join(grouped, on='name')) expr = self.expr.join(expr2, on=[ 'name', ('id', 'id2') ])[lambda x: x.groupby(Scalar(1)).sort('name').row_number(), ] self.engine.execute(expr) finally: table2.drop() def testUnion(self): data = [ ['name1', 4, 5.3, None, None], ['name2', 2, 3.5, None, None], ['name1', 4, 4.2, None, None], ['name1', 3, 2.2, None, None], ['name1', 3, 4.1, None, None], ] schema2 = Schema.from_lists(['name', 'id2', 'id3'], [types.string, types.bigint, types.bigint]) table_name = tn('pyodps_test_engine_table2') self.odps.delete_table(table_name, if_exists=True) table2 = self.odps.create_table(name=table_name, schema=schema2) expr2 = CollectionExpr(_source_data=table2, _schema=odps_schema_to_df_schema(schema2)) self._gen_data(data=data) data2 = [['name3', 5, -1], ['name4', 6, -2]] self.odps.write_table(table2, 0, data2) try: expr = self.expr['name', 'id'].distinct().union( expr2[expr2.id2.rename('id'), 'name']) res = self.engine.execute(expr) result = self._get_result(res) expected = [['name1', 4], ['name1', 3], ['name2', 2], ['name3', 5], ['name4', 6]] result = sorted(result) expected = sorted(expected) self.assertEqual(len(result), len(expected)) for e, r in zip(result, expected): self.assertEqual([to_str(t) for t in e], [to_str(t) for t in r]) finally: table2.drop() def testScaleValue(self): data = [ ['name1', 4, 5.3], ['name2', 2, 3.5], ['name1', 4, 4.2], ['name1', 3, 2.2], ['name1', 3, 4.1], ] schema = Schema.from_lists(['name', 'id', 'fid'], [types.string, types.bigint, types.double]) table_name = tn('pyodps_test_engine_scale_table') self.odps.delete_table(table_name, if_exists=True) table = self.odps.create_table(name=table_name, schema=schema) self.odps.write_table(table_name, 0, data) expr_input = CollectionExpr(_source_data=table, _schema=odps_schema_to_df_schema(schema)) expr = expr_input.min_max_scale(columns=['fid']) res = self.engine.execute(expr) result = self._get_result(res) expected = [['name1', 4, 1.0], ['name2', 2, 0.41935483870967744], ['name1', 4, 0.6451612903225807], ['name1', 3, 0.0], ['name1', 3, 0.6129032258064515]] result = sorted(result) expected = sorted(expected) for first, second in zip(result, expected): self.assertEqual(len(first), len(second)) for it1, it2 in zip(first, second): self.assertAlmostEqual(it1, it2) expr = expr_input.std_scale(columns=['fid']) res = self.engine.execute(expr) result = self._get_result(res) expected = [['name1', 4, 1.4213602653434203], ['name2', 2, -0.3553400663358544], ['name1', 4, 0.3355989515394193], ['name1', 3, -1.6385125281042194], ['name1', 3, 0.23689337755723686]] result = sorted(result) expected = sorted(expected) for first, second in zip(result, expected): self.assertEqual(len(first), len(second)) for it1, it2 in zip(first, second): self.assertAlmostEqual(it1, it2) def testPersist(self): data = [ ['name1', 4, 5.3, None, None], ['name2', 2, 3.5, None, None], ['name1', 4, 4.2, None, None], ['name1', 3, 2.2, None, None], ['name1', 3, 4.1, None, None], ] self._gen_data(data=data) table_name = tn('pyodps_test_engine_persist_seahawks_table') try: df = self.engine.persist(self.expr, table_name) res = self.engine.execute(df) result = self._get_result(res) self.assertEqual(len(result), 5) self.assertEqual(data, result) finally: self.odps.delete_table(table_name, if_exists=True) try: schema = Schema.from_lists(self.schema.names, self.schema.types, ['ds'], ['string']) self.odps.create_table(table_name, schema) df = self.engine.persist(self.expr, table_name, partition='ds=today', create_partition=True) res = self.engine.execute(df) result = self._get_result(res) self.assertEqual(len(result), 5) self.assertEqual(data, [r[:-1] for r in result]) finally: self.odps.delete_table(table_name, if_exists=True) try: self.engine.persist(self.expr, table_name, partitions=['name']) t = self.odps.get_table(table_name) self.assertEqual(2, len(list(t.partitions))) with t.open_reader(partition='name=name1', reopen=True) as r: self.assertEqual(4, r.count) with t.open_reader(partition='name=name2', reopen=True) as r: self.assertEqual(1, r.count) finally: self.odps.delete_table(table_name, if_exists=True) def testMakeKV(self): from odps import types as odps_types data = [ ['name1', 1.0, 3.0, None, 10.0, None, None], ['name1', None, 3.0, 5.1, None, None, None], ['name1', 7.1, None, None, None, 8.2, None], ['name2', None, 1.2, 1.5, None, None, None], ['name2', None, 1.0, None, None, None, 1.1], ] kv_cols = ['k1', 'k2', 'k3', 'k5', 'k7', 'k9'] schema = Schema.from_lists(['name'] + kv_cols, [odps_types.string] + [odps_types.double] * 6) table_name = tn('pyodps_test_engine_make_kv') self.odps.delete_table(table_name, if_exists=True) table = self.odps.create_table(name=table_name, schema=schema) expr = CollectionExpr(_source_data=table, _schema=odps_schema_to_df_schema(schema)) try: self.odps.write_table(table, 0, data) expr1 = expr.to_kv(columns=kv_cols, kv_delim='=') res = self.engine.execute(expr1) result = self._get_result(res) expected = [ ['name1', 'k1=1,k2=3,k5=10'], ['name1', 'k2=3,k3=5.1'], ['name1', 'k1=7.1,k7=8.2'], ['name2', 'k2=1.2,k3=1.5'], ['name2', 'k2=1,k9=1.1'], ] self.assertListEqual(result, expected) finally: table.drop() def testFilterOrder(self): table_name = tn('pyodps_test_division_error') self.odps.delete_table(table_name, if_exists=True) table = self.odps.create_table(table_name, 'divided bigint, divisor bigint', lifecycle=1) try: self.odps.write_table(table_name, [[2, 0], [1, 1], [1, 2], [5, 1], [5, 0]]) df = CollectionExpr(_source_data=table, _schema=odps_schema_to_df_schema(table.schema)) fdf = df[df.divisor > 0] ddf = fdf[(fdf.divided / fdf.divisor).rename('result'), ] expr = ddf[ddf.result > 1] res = self.engine.execute(expr) result = self._get_result(res) self.assertEqual(result, [[ 5, ]]) finally: table.drop() def testAXFException(self): import sqlalchemy data = [ ['name1', 4, 5.3, None, None], ['name2', 2, 3.5, None, None], ['name1', 4, 4.2, None, None], ['name1', 3, 2.2, None, None], ['name1', 3, 4.1, None, None], ] self._gen_data(data=data) table_name = tn('pyodps_test_engine_axf_seahawks_table') try: schema = Schema.from_lists(self.schema.names, self.schema.types, ['ds'], ['string']) self.odps.create_table(table_name, schema) df = self.engine.persist(self.expr, table_name, partition='ds=today', create_partition=True) with self.assertRaises(sqlalchemy.exc.DatabaseError): self.engine.execute(df.input) finally: self.odps.delete_table(table_name, if_exists=True)
class Test(TestBase): def setup(self): datatypes = lambda *types: [validate_data_type(t) for t in types] schema = Schema.from_lists(['name', 'id', 'fid', 'isMale', 'scale', 'birth'], datatypes('string', 'int64', 'float64', 'boolean', 'decimal', 'datetime'), ['ds'], datatypes('string')) table = MockTable(name='pyodps_test_expr_table', schema=schema) self.expr = CollectionExpr(_source_data=table, _schema=schema) table1 = MockTable(name='pyodps_test_expr_table1', schema=schema) self.expr1 = CollectionExpr(_source_data=table1, _schema=schema) table2 = MockTable(name='pyodps_test_expr_table2', schema=schema) self.expr2 = CollectionExpr(_source_data=table2, _schema=schema) schema2 = Schema.from_lists(['name', 'id', 'fid'], datatypes('string', 'int64', 'float64'), ['part1', 'part2'], datatypes('string', 'int64')) table3 = MockTable(name='pyodps_test_expr_table2', schema=schema2) self.expr3 = CollectionExpr(_source_data=table3, _schema=schema2) self.maxDiff = None def testFilterPushdownThroughProjection(self): expr = self.expr[self.expr.id + 1, 'name'][lambda x: x.id < 10] expected = 'SELECT t1.`id` + 1 AS `id`, t1.`name` \n' \ 'FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ 'WHERE (t1.`id` + 1) < 10' self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr['name', self.expr.id ** 2]\ .filter(lambda x: x.name == 'name1').filter(lambda x: x.id < 3) expected = "SELECT t1.`name`, CAST(POW(t1.`id`, 2) AS BIGINT) AS `id` \n" \ "FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ "WHERE (t1.`name` == 'name1') AND ((CAST(POW(t1.`id`, 2) AS BIGINT)) < 3)" self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr['name', self.expr.id + 1].filter(lambda x: x.name == 'name1')[ lambda x: 'tt' + x.name, 'id' ].filter(lambda x: x.id < 3) expected = "SELECT CONCAT('tt', t1.`name`) AS `name`, t1.`id` + 1 AS `id` \n" \ "FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ "WHERE (t1.`name` == 'name1') AND ((t1.`id` + 1) < 3)" self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.filter(self.expr.name == 'name1').select('name', lambda x: (x.id + 1) * 2)[ lambda x: 'tt' + x.name, 'id' ].filter(lambda x: x.id < 3) expected = "SELECT CONCAT('tt', t1.`name`) AS `name`, (t1.`id` + 1) * 2 AS `id` \n" \ "FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ "WHERE (((t1.`id` + 1) * 2) < 3) AND (t1.`name` == 'name1')" self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.filter(self.expr.id.between(2, 6), self.expr.name.lower().contains('pyodps', regex=False)).name.nunique() expected = "SELECT COUNT(DISTINCT t2.`name`) AS `name_nunique` \n" \ "FROM (\n" \ " SELECT t1.`id`, t1.`name` \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ " WHERE ((t1.`id` >= 2) AND (t1.`id` <= 6)) AND INSTR(TOLOWER(t1.`name`), 'pyodps') > 0 \n" \ ") t2" self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testFilterPushDownThroughJoin(self): expr = self.expr.join(self.expr3, on='name') expr = expr[(expr.id_x < 10) & (expr.fid_y > 3)] expected = 'SELECT t2.`name`, t2.`id` AS `id_x`, t2.`fid` AS `fid_x`, ' \ 't2.`isMale`, t2.`scale`, t2.`birth`, t2.`ds`, t4.`id` AS `id_y`, ' \ 't4.`fid` AS `fid_y`, t4.`part1`, t4.`part2` \n' \ 'FROM (\n' \ ' SELECT * \n' \ ' FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ ' WHERE t1.`id` < 10\n' \ ') t2 \n' \ 'INNER JOIN \n' \ ' (\n' \ ' SELECT * \n' \ ' FROM mocked_project.`pyodps_test_expr_table2` t3 \n' \ ' WHERE t3.`fid` > 3\n' \ ' ) t4\n' \ 'ON t2.`name` == t4.`name`' self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.join(self.expr3, on='name') expr = expr[(expr.id_x < 10) & (expr.fid_y > 3) & (expr.id_x > 3)] expected = 'SELECT t2.`name`, t2.`id` AS `id_x`, t2.`fid` AS `fid_x`, ' \ 't2.`isMale`, t2.`scale`, t2.`birth`, t2.`ds`, t4.`id` AS `id_y`, ' \ 't4.`fid` AS `fid_y`, t4.`part1`, t4.`part2` \n' \ 'FROM (\n' \ ' SELECT * \n' \ ' FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ ' WHERE (t1.`id` < 10) AND (t1.`id` > 3)\n' \ ') t2 \n' \ 'INNER JOIN \n' \ ' (\n' \ ' SELECT * \n' \ ' FROM mocked_project.`pyodps_test_expr_table2` t3 \n' \ ' WHERE t3.`fid` > 3\n' \ ' ) t4\n' \ 'ON t2.`name` == t4.`name`' self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr[self.expr.name, self.expr.id + 1] expr2 = self.expr3['tt' + self.expr3.name, self.expr3.id.rename('id2')] expr = expr.join(expr2, on='name') expr = expr[((expr.id < 10) | (expr.id > 100)) & (expr.id2 > 3)] expected = "SELECT t2.`name`, t2.`id`, t4.`id2` \n" \ "FROM (\n" \ " SELECT t1.`name`, t1.`id` + 1 AS `id` \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ " WHERE ((t1.`id` + 1) < 10) OR ((t1.`id` + 1) > 100)\n" \ ") t2 \n" \ "INNER JOIN \n" \ " (\n" \ " SELECT CONCAT('tt', t3.`name`) AS `name`, t3.`id` AS `id2` \n" \ " FROM mocked_project.`pyodps_test_expr_table2` t3 \n" \ " WHERE t3.`id` > 3\n" \ " ) t4\n" \ "ON t2.`name` == t4.`name`" self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.join(self.expr3, on='name') expr = expr[(expr.id_x + expr.id_y < 10) & (expr.id_x > 3)] expected = "SELECT * \n" \ "FROM (\n" \ " SELECT t2.`name`, t2.`id` AS `id_x`, t2.`fid` AS `fid_x`, t2.`isMale`, " \ "t2.`scale`, t2.`birth`, t2.`ds`, t3.`id` AS `id_y`, " \ "t3.`fid` AS `fid_y`, t3.`part1`, t3.`part2` \n" \ " FROM (\n" \ " SELECT * \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ " WHERE t1.`id` > 3\n" \ " ) t2 \n" \ " INNER JOIN \n" \ " mocked_project.`pyodps_test_expr_table2` t3\n" \ " ON t2.`name` == t3.`name` \n" \ ") t4 \n" \ "WHERE (t4.`id_x` + t4.`id_y`) < 10" self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.outer_join(self.expr3, on='name') expr = expr[(expr.id_x + expr.id_y < 10) & (expr.id_x > 3)] expected = "SELECT * \n" \ "FROM (\n" \ " SELECT t1.`name` AS `name_x`, t1.`id` AS `id_x`, t1.`fid` AS `fid_x`, " \ "t1.`isMale`, t1.`scale`, t1.`birth`, t1.`ds`, t2.`name` AS `name_y`, " \ "t2.`id` AS `id_y`, t2.`fid` AS `fid_y`, t2.`part1`, t2.`part2` \n" \ " FROM mocked_project.`pyodps_test_expr_table` t1 \n" \ " FULL OUTER JOIN \n" \ " mocked_project.`pyodps_test_expr_table2` t2\n" \ " ON t1.`name` == t2.`name` \n" \ ") t3 \n" \ "WHERE ((t3.`id_x` + t3.`id_y`) < 10) AND (t3.`id_x` > 3)" self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.join(self.expr3, on=['name', self.expr.id == self.expr3.id, self.expr.id < 10, self.expr3.name == 'name1', self.expr.id > 5]) expected = 'SELECT t2.`name`, t2.`id`, t2.`fid` AS `fid_x`, t2.`isMale`, ' \ 't2.`scale`, t2.`birth`, t2.`ds`, t4.`fid` AS `fid_y`, t4.`part1`, t4.`part2` \n' \ 'FROM (\n' \ ' SELECT * \n' \ ' FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ ' WHERE (t1.`id` < 10) AND (t1.`id` > 5)\n' \ ') t2 \n' \ 'INNER JOIN \n' \ ' (\n' \ ' SELECT * \n' \ ' FROM mocked_project.`pyodps_test_expr_table2` t3 \n' \ ' WHERE t3.`name` == \'name1\'\n' \ ' ) t4\n' \ 'ON (t2.`name` == t4.`name`) AND (t2.`id` == t4.`id`)' self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr = self.expr.left_join(self.expr3, on=['name', self.expr.id == self.expr3.id, self.expr.id < 10, self.expr3.name == 'name1', self.expr.id > 5]) expected = 'SELECT t1.`name` AS `name_x`, t1.`id` AS `id_x`, t1.`fid` AS `fid_x`, t1.`isMale`, ' \ 't1.`scale`, t1.`birth`, t1.`ds`, t2.`name` AS `name_y`, t2.`id` AS `id_y`, ' \ 't2.`fid` AS `fid_y`, t2.`part1`, t2.`part2` \n' \ 'FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ 'LEFT OUTER JOIN \n' \ ' mocked_project.`pyodps_test_expr_table2` t2\n' \ 'ON ((((t1.`name` == t2.`name`) AND (t1.`id` == t2.`id`)) ' \ "AND (t1.`id` < 10)) AND (t2.`name` == 'name1')) AND (t1.`id` > 5)" self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) def testFilterPushdownThroughUnion(self): expr = self.expr['name', 'id'].union(self.expr2['id', 'name']) expr = expr.filter(expr.id + 1 < 3) expected = 'SELECT * \n' \ 'FROM (\n' \ ' SELECT t1.`name`, t1.`id` \n' \ ' FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ ' WHERE (t1.`id` + 1) < 3 \n' \ ' UNION ALL\n' \ ' SELECT t2.`name`, t2.`id` \n' \ ' FROM mocked_project.`pyodps_test_expr_table2` t2 \n' \ ' WHERE (t2.`id` + 1) < 3\n' \ ') t3' self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False))) expr1 = self.expr.filter(self.expr.id == 1)['name', 'id'] expr2 = self.expr.filter(self.expr.id == 0)['id', 'name'] expr = expr1.union(expr2) expected = 'SELECT * \n' \ 'FROM (\n' \ ' SELECT t1.`name`, t1.`id` \n' \ ' FROM mocked_project.`pyodps_test_expr_table` t1 \n' \ ' WHERE t1.`id` == 1 \n' \ ' UNION ALL\n' \ ' SELECT t2.`name`, t2.`id` \n' \ ' FROM mocked_project.`pyodps_test_expr_table` t2 \n' \ ' WHERE t2.`id` == 0\n' \ ') t3' self.assertEqual(to_str(expected), to_str(ODPSEngine(self.odps).compile(expr, prettify=False)))