Java tutorial
/** * Licensed to the Apache Software Foundation (ASF) under one * or more contributor license agreements. See the NOTICE file * distributed with this work for additional information * regarding copyright ownership. The ASF licenses this file * to you under the Apache License, Version 2.0 (the * "License"); you may not use this file except in compliance * with the License. You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package org.apache.drill.jdbc.test; import static org.junit.Assert.assertTrue; import java.io.File; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; import org.apache.commons.io.FileUtils; import org.apache.drill.exec.store.hive.HiveTestDataGenerator; import org.apache.drill.test.DrillAssert; import org.junit.BeforeClass; import org.junit.Ignore; import org.junit.Test; import com.google.common.base.Function; /** Contains tests for creating/droping and using views in Drill. */ public class TestViews extends JdbcTestQueryBase { @BeforeClass public static void generateHive() throws Exception { new HiveTestDataGenerator().generateTestData(); // delete tmp workspace directory File f = new File("/tmp/drilltest"); if (f.exists()) { FileUtils.cleanDirectory(f); FileUtils.forceDelete(f); } } /** Helper test method for view tests */ private void testViewHelper(final String viewCreate, final String viewName, final String viewQuery, final String queryResult) throws Exception { JdbcAssert.withNoDefaultSchema().withConnection(new Function<Connection, Void>() { public Void apply(Connection connection) { try { Statement statement = connection.createStatement(); // change default schema statement.executeQuery("USE dfs_test.tmp"); // create view ResultSet resultSet = statement.executeQuery(viewCreate); String result = JdbcAssert.toString(resultSet).trim(); resultSet.close(); String viewCreateResult = "ok=true; summary=View '" + viewName + "' created successfully in 'dfs_test.tmp' schema"; assertTrue( String.format("Generated string:\n%s\ndoes not match:\n%s", result, viewCreateResult), viewCreateResult.equals(result)); // query from view resultSet = statement.executeQuery(viewQuery); result = JdbcAssert.toString(resultSet).trim(); resultSet.close(); assertTrue(String.format("Generated string:\n%s\ndoes not match:\n%s", result, queryResult), queryResult.equals(result)); statement.executeQuery("drop view " + viewName).close(); statement.close(); return null; } catch (Exception e) { throw new RuntimeException(e); } } }); } @Test public void testView1() throws Exception { testViewHelper("CREATE VIEW testview1 AS SELECT * FROM cp.`region.json`", "testview1", "SELECT * FROM testview1 LIMIT 1", "region_id=0; sales_city=None; sales_state_province=None; sales_district=No District; " + "sales_region=No Region; sales_country=No Country; sales_district_id=0"); } @Test public void testView2() throws Exception { testViewHelper("CREATE VIEW testview2 AS SELECT region_id, sales_city FROM cp.`region.json`", "testview2", "SELECT * FROM testview2 LIMIT 2", "region_id=0; sales_city=None\nregion_id=1; sales_city=San Francisco"); } @Test public void testView3() throws Exception { testViewHelper( "CREATE VIEW testview3(regionid, salescity) AS SELECT region_id, sales_city FROM cp.`region.json`", "testview3", "SELECT * FROM testview3 LIMIT 2", "regionid=0; salescity=None\nregionid=1; salescity=San Francisco"); } @Test @Ignore // See DRILL-595 - can't project columns from inner query. public void testView4() throws Exception { testViewHelper("CREATE VIEW testview1 AS SELECT * FROM cp.`region.json`", "testview1", "SELECT region_id, sales_city FROM testview1 LIMIT 2", ""); } @Test public void testView5() throws Exception { testViewHelper("CREATE VIEW testview2 AS SELECT region_id, sales_city FROM cp.`region.json`", "testview2", "SELECT region_id, sales_city FROM testview2 LIMIT 2", "region_id=0; sales_city=None\nregion_id=1; sales_city=San Francisco"); } @Test public void testView6() throws Exception { testViewHelper("CREATE VIEW testview2 AS SELECT region_id, sales_city FROM cp.`region.json`", "testview2", "SELECT sales_city FROM testview2 LIMIT 2", "sales_city=None\nsales_city=San Francisco"); } @Test public void testView7() throws Exception { testViewHelper( "CREATE VIEW testview3(regionid, salescity) AS SELECT region_id, sales_city FROM cp.`region.json` LIMIT 2", "testview3", "SELECT regionid, salescity FROM testview3", "regionid=0; salescity=None\nregionid=1; salescity=San Francisco"); } @Test public void testView8() throws Exception { testViewHelper( "CREATE VIEW testview3(regionid, salescity) AS " + "SELECT region_id, sales_city FROM cp.`region.json` ORDER BY region_id DESC", "testview3", "SELECT regionid FROM testview3 LIMIT 2", "regionid=109\nregionid=108"); } @Test @Ignore // Query on testview2 fails with CannotPlanException. Seems to be an issue with Union. public void testView9() throws Exception { testViewHelper( "CREATE VIEW testview2 AS " + "SELECT region_id FROM cp.`region.json` " + "UNION " + "SELECT employee_id FROM cp.`employee.json`", "testview2", "SELECT sales_city FROM testview2 LIMIT 2", "sales_city=None\nsales_city=San Francisco"); } @Test public void testViewOnHiveTable1() throws Exception { testViewHelper("CREATE VIEW hiveview AS SELECT * FROM hive_test.kv", "hiveview", "SELECT * FROM hiveview LIMIT 1", "key=1; value= key_1"); } @Test public void testViewOnHiveTable2() throws Exception { testViewHelper("CREATE VIEW hiveview AS SELECT * FROM hive_test.kv", "hiveview", "SELECT key, `value` FROM hiveview LIMIT 1", "key=1; value= key_1"); } @Test public void testViewOnHiveTable3() throws Exception { testViewHelper("CREATE VIEW hiveview AS SELECT * FROM hive_test.kv", "hiveview", "SELECT `value` FROM hiveview LIMIT 1", "value= key_1"); } @Test public void testViewOnHiveTable4() throws Exception { testViewHelper("CREATE VIEW hiveview AS SELECT key, `value` FROM hive_test.kv", "hiveview", "SELECT * FROM hiveview LIMIT 1", "key=1; value= key_1"); } @Test public void testViewOnHiveTable5() throws Exception { testViewHelper("CREATE VIEW hiveview AS SELECT key, `value` FROM hive_test.kv", "hiveview", "SELECT key, `value` FROM hiveview LIMIT 1", "key=1; value= key_1"); } @Test public void testViewWithCompoundIdentifiersInSchema() throws Exception { String query = String.format("CREATE VIEW nationview AS SELECT " + "cast(columns[0] AS int) n_nationkey, " + "cast(columns[1] AS CHAR(25)) n_name, " + "cast(columns[2] AS INT) n_regionkey, " + "cast(columns[3] AS VARCHAR(152)) n_comment " + "FROM dfs_test.`%s/src/test/resources/nation`", WORKING_PATH); testViewHelper(query, "nationview", "SELECT * FROM nationview LIMIT 1", "n_nationkey=0; n_name=ALGERIA; n_regionkey=0; n_comment= haggle. carefully final deposits detect slyly agai"); } @Test public void testDropView() throws Exception { JdbcAssert.withNoDefaultSchema().withConnection(new Function<Connection, Void>() { public Void apply(Connection connection) { try { Statement statement = connection.createStatement(); // change default schema statement.executeQuery("USE dfs_test.tmp"); // create view statement.executeQuery( "CREATE VIEW testview3(regionid) AS SELECT region_id FROM cp.`region.json`"); // query from view ResultSet resultSet = statement.executeQuery("SELECT regionid FROM testview3 LIMIT 1"); String result = JdbcAssert.toString(resultSet).trim(); resultSet.close(); String expected = "regionid=0"; assertTrue(String.format("Generated string:\n%s\ndoes not match:\n%s", result, expected), expected.equals(result)); resultSet = statement.executeQuery("DROP VIEW testview3"); result = JdbcAssert.toString(resultSet).trim(); resultSet.close(); expected = "ok=true; summary=View 'testview3' deleted successfully from 'dfs_test.tmp' schema"; assertTrue(String.format("Generated string:\n%s\ndoes not match:\n%s", result, expected), expected.equals(result)); statement.close(); return null; } catch (Exception e) { throw new RuntimeException(e); } } }); } @Test public void testInfoSchemaWithView() throws Exception { JdbcAssert.withNoDefaultSchema().withConnection(new Function<Connection, Void>() { public Void apply(Connection connection) { try { Statement statement = connection.createStatement(); // change default schema statement.executeQuery("USE dfs_test.tmp"); // create view statement.executeQuery("CREATE VIEW testview3 AS SELECT * FROM hive_test.kv"); // show tables on view ResultSet resultSet = statement.executeQuery("SHOW TABLES like 'testview3'"); String result = JdbcAssert.toString(resultSet).trim(); resultSet.close(); String expected = "TABLE_SCHEMA=dfs_test.tmp; TABLE_NAME=testview3"; assertTrue(String.format("Generated string:\n%s\ndoes not match:\n%s", result, expected), expected.equals(result)); // test record in INFORMATION_SCHEMA.VIEWS resultSet = statement.executeQuery( "SELECT * FROM INFORMATION_SCHEMA.VIEWS " + "WHERE TABLE_NAME = 'testview3'"); result = JdbcAssert.toString(resultSet).trim(); resultSet.close(); expected = "TABLE_CATALOG=DRILL; TABLE_SCHEMA=dfs_test.tmp; TABLE_NAME=testview3; VIEW_DEFINITION=SELECT *\nFROM `hive_test`.`kv`"; DrillAssert.assertMultiLineStringEquals( String.format("Generated string:\n%s\ndoes not match:\n%s", result, expected), expected, result); // test record in INFORMATION_SCHEMA.TABLES resultSet = statement.executeQuery( "SELECT * FROM INFORMATION_SCHEMA.`TABLES` " + "WHERE TABLE_NAME = 'testview3'"); result = JdbcAssert.toString(resultSet).trim(); resultSet.close(); expected = "TABLE_CATALOG=DRILL; TABLE_SCHEMA=dfs_test.tmp; TABLE_NAME=testview3; TABLE_TYPE=VIEW"; assertTrue(String.format("Generated string:\n%s\ndoes not match:\n%s", result, expected), expected.equals(result)); // describe a view resultSet = statement.executeQuery("DESCRIBE dfs_test.tmp.testview3"); result = JdbcAssert.toString(resultSet).trim(); resultSet.close(); expected = "COLUMN_NAME=key; DATA_TYPE=INTEGER; IS_NULLABLE=NO\n" + "COLUMN_NAME=value; DATA_TYPE=VARCHAR; IS_NULLABLE=NO"; assertTrue(String.format("Generated string:\n%s\ndoes not match:\n%s", result, expected), expected.equals(result)); statement.executeQuery("drop view testview3").close(); statement.close(); return null; } catch (Exception e) { throw new RuntimeException(e); } } }); } @Test public void testInfoSchemaWithHiveView() throws Exception { JdbcAssert.withFull("hive_test.default") .sql("SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'hiveview'") .returns("TABLE_CATALOG=DRILL; TABLE_SCHEMA=hive_test.default; TABLE_NAME=hiveview; " + "VIEW_DEFINITION=SELECT `kv`.`key`, `kv`.`value` FROM `default`.`kv`"); } @Test public void testViewWithFullSchemaIdentifier() throws Exception { JdbcAssert.withNoDefaultSchema().withConnection(new Function<Connection, Void>() { public Void apply(Connection connection) { try { Statement statement = connection.createStatement(); // change default schema statement.executeQuery("USE cp"); // create a view with full schema identifier ResultSet resultSet = statement .executeQuery("CREATE VIEW dfs_test.tmp.testview AS SELECT * FROM hive_test.kv"); String result = JdbcAssert.toString(resultSet).trim(); resultSet.close(); String expected = "ok=true; summary=View 'testview' created successfully in 'dfs_test.tmp' schema"; assertTrue(String.format("Generated string:\n%s\ndoes not match:\n%s", result, expected), expected.equals(result)); // query from view resultSet = statement.executeQuery("SELECT key FROM dfs_test.tmp.testview LIMIT 1"); result = JdbcAssert.toString(resultSet).trim(); resultSet.close(); expected = "key=1"; assertTrue(String.format("Generated string:\n%s\ndoes not match:\n%s", result, expected), expected.equals(result)); statement.executeQuery("drop view dfs_test.tmp.testview").close(); statement.close(); return null; } catch (Exception e) { throw new RuntimeException(e); } } }); } @Test public void testViewWithPartialSchemaIdentifier() throws Exception { JdbcAssert.withNoDefaultSchema().withConnection(new Function<Connection, Void>() { public Void apply(Connection connection) { try { Statement statement = connection.createStatement(); // change default schema statement.executeQuery("USE dfs_test"); // create a view with partial schema identifier ResultSet resultSet = statement .executeQuery("CREATE VIEW tmp.testview AS SELECT * FROM hive_test.kv"); String result = JdbcAssert.toString(resultSet).trim(); resultSet.close(); String expected = "ok=true; summary=View 'testview' created successfully in 'dfs_test.tmp' schema"; assertTrue(String.format("Generated string:\n%s\ndoes not match:\n%s", result, expected), expected.equals(result)); // query from view resultSet = statement.executeQuery("SELECT key FROM tmp.testview LIMIT 1"); result = JdbcAssert.toString(resultSet).trim(); resultSet.close(); expected = "key=1"; assertTrue(String.format("Generated string:\n%s\ndoes not match:\n%s", result, expected), expected.equals(result)); // change the default schema and query statement.executeQuery("USE dfs_test.tmp"); resultSet = statement.executeQuery("SELECT key FROM testview LIMIT 1"); result = JdbcAssert.toString(resultSet).trim(); resultSet.close(); assertTrue(String.format("Generated string:\n%s\ndoes not match:\n%s", result, expected), expected.equals(result)); statement.executeQuery("drop view tmp.testview").close(); statement.close(); return null; } catch (Exception e) { throw new RuntimeException(e); } } }); } @Test public void testViewResolvingTablesInWorkspaceSchema() throws Exception { JdbcAssert.withNoDefaultSchema().withConnection(new Function<Connection, Void>() { public Void apply(Connection connection) { try { Statement statement = connection.createStatement(); // change default schema statement.executeQuery("USE cp"); // create a view with full schema identifier ResultSet resultSet = statement .executeQuery("CREATE VIEW dfs_test.tmp.testViewResolvingTablesInWorkspaceSchema AS " + "SELECT region_id, sales_city FROM `region.json`"); String result = JdbcAssert.toString(resultSet).trim(); resultSet.close(); String expected = "ok=true; summary=View 'testViewResolvingTablesInWorkspaceSchema' " + "created successfully in 'dfs_test.tmp' schema"; assertTrue(String.format("Generated string:\n%s\ndoes not match:\n%s", result, expected), expected.equals(result)); // query from view resultSet = statement.executeQuery( "SELECT region_id FROM dfs_test.tmp.testViewResolvingTablesInWorkspaceSchema LIMIT 1"); result = JdbcAssert.toString(resultSet).trim(); resultSet.close(); expected = "region_id=0"; assertTrue(String.format("Generated string:\n%s\ndoes not match:\n%s", result, expected), expected.equals(result)); statement.executeQuery("drop view dfs_test.tmp.testViewResolvingTablesInWorkspaceSchema") .close(); statement.close(); return null; } catch (Exception e) { throw new RuntimeException(e); } } }); } @Test public void testCreateViewWhenViewAlreadyExists() throws Exception { JdbcAssert.withFull("dfs_test.tmp").withConnection(new Function<Connection, Void>() { public Void apply(Connection connection) { try { Statement statement = connection.createStatement(); // create a view ResultSet resultSet = statement.executeQuery( "CREATE VIEW testCreateViewWhenViewAlreadyExists AS SELECT region_id, sales_city FROM cp.`region.json`"); String result = JdbcAssert.toString(resultSet).trim(); resultSet.close(); String expected = "ok=true; summary=View 'testCreateViewWhenViewAlreadyExists' " + "created successfully in 'dfs_test.tmp' schema"; assertTrue(String.format("Generated string:\n%s\ndoes not match:\n%s", result, expected), expected.equals(result)); // try to create the view with same name resultSet = statement.executeQuery( "CREATE VIEW testCreateViewWhenViewAlreadyExists AS SELECT region_id FROM cp.`region.json`"); result = JdbcAssert.toString(resultSet).trim(); resultSet.close(); expected = "ok=false; summary=View with given name already exists in current schema"; assertTrue(String.format("Generated string:\n%s\ndoes not match:\n%s", result, expected), expected.equals(result)); // try creating the view with same name but with a OR REPLACE clause resultSet = statement.executeQuery( "CREATE OR REPLACE VIEW testCreateViewWhenViewAlreadyExists AS SELECT region_id FROM cp.`region.json`"); result = JdbcAssert.toString(resultSet).trim(); resultSet.close(); expected = "ok=true; summary=View 'testCreateViewWhenViewAlreadyExists' " + "replaced successfully in 'dfs_test.tmp' schema"; assertTrue(String.format("Generated string:\n%s\ndoes not match:\n%s", result, expected), expected.equals(result)); statement.executeQuery("drop view dfs_test.tmp.testCreateViewWhenViewAlreadyExists").close(); statement.close(); return null; } catch (Exception e) { throw new RuntimeException(e); } } }); } private void createViewHelper(Statement statement, String schema, String viewName, String query) throws Exception { ResultSet resultSet = statement.executeQuery(query); String result = JdbcAssert.toString(resultSet).trim(); resultSet.close(); String expected = String.format("ok=true; summary=View '%s' created successfully in '%s' schema", viewName, schema); assertTrue(String.format("Generated string:\n%s\ndoes not match:\n%s", result, expected), expected.equals(result)); } private void queryView(Statement statement, String query, String expectedResult) throws Exception { ResultSet resultSet = statement.executeQuery(query); String actualResult = JdbcAssert.toString(resultSet).trim(); resultSet.close(); assertTrue(String.format("Generated string:\n%s\ndoes not match:\n%s", actualResult, expectedResult), expectedResult.equals(actualResult)); } private void dropView(Statement statement, String schema, String viewName) throws Exception { if (schema != null && !schema.isEmpty()) { viewName = schema + "." + viewName; } statement.executeQuery("drop view innerView").close(); } @Test public void testViewCreatedFromView() throws Exception { final String schema = "dfs_test.tmp"; JdbcAssert.withFull(schema).withConnection(new Function<Connection, Void>() { public Void apply(Connection connection) { try { Statement statement = connection.createStatement(); // create a view String createInnerView = "CREATE VIEW innerView AS SELECT region_id, sales_city FROM cp.`region.json`"; String innerViewName = "innerView"; createViewHelper(statement, schema, "innerView", createInnerView); // create another view from above created view String createOuterView = "CREATE VIEW outerView AS SELECT region_id FROM innerView"; String outerViewName = "outerView"; createViewHelper(statement, schema, outerViewName, createOuterView); // query on outer view String queryView = "SELECT region_id FROM outerView LIMIT 1"; String expectedResult = "region_id=0"; queryView(statement, queryView, expectedResult); dropView(statement, schema, outerViewName); dropView(statement, schema, innerViewName); statement.close(); return null; } catch (Exception e) { throw new RuntimeException(e); } } }); } }