Repository: oozie Updated Branches: refs/heads/master 740778d86 -> a3bcad6c0
OOZIE-1717 amend Add indexes to speed up db queries (asasvari) Project: http://git-wip-us.apache.org/repos/asf/oozie/repo Commit: http://git-wip-us.apache.org/repos/asf/oozie/commit/a3bcad6c Tree: http://git-wip-us.apache.org/repos/asf/oozie/tree/a3bcad6c Diff: http://git-wip-us.apache.org/repos/asf/oozie/diff/a3bcad6c Branch: refs/heads/master Commit: a3bcad6c06ad5059bb5693ebfe8d772ef69d9dfe Parents: 740778d Author: Attila Sasvari <[email protected]> Authored: Tue Feb 6 12:06:08 2018 +0100 Committer: Attila Sasvari <[email protected]> Committed: Tue Feb 6 12:06:08 2018 +0100 ---------------------------------------------------------------------- .../java/org/apache/oozie/tools/OozieDBCLI.java | 36 +++++------ .../org/apache/oozie/tools/TestOozieDBCLI.java | 64 +++++++++++++++++++- 2 files changed, 81 insertions(+), 19 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/oozie/blob/a3bcad6c/tools/src/main/java/org/apache/oozie/tools/OozieDBCLI.java ---------------------------------------------------------------------- diff --git a/tools/src/main/java/org/apache/oozie/tools/OozieDBCLI.java b/tools/src/main/java/org/apache/oozie/tools/OozieDBCLI.java index 4bb6dd8..ce18e1c 100644 --- a/tools/src/main/java/org/apache/oozie/tools/OozieDBCLI.java +++ b/tools/src/main/java/org/apache/oozie/tools/OozieDBCLI.java @@ -746,6 +746,23 @@ public class OozieDBCLI { } } + static String[] getIndexStatementsFor50() { + return new String[]{"CREATE INDEX I_WF_JOBS_STATUS_CREATED_TIME ON WF_JOBS (status, created_time)", + + "CREATE INDEX I_COORD_ACTIONS_JOB_ID_STATUS ON COORD_ACTIONS (job_id, status)", + + "CREATE INDEX I_COORD_JOBS_STATUS_CREATED_TIME ON COORD_JOBS (status, created_time)", + "CREATE INDEX I_COORD_JOBS_STATUS_LAST_MODIFIED_TIME ON COORD_JOBS (status, last_modified_time)", + "CREATE INDEX I_COORD_JOBS_PENDING_DONE_MATERIALIZATION_LAST_MODIFIED_TIME ON COORD_JOBS " + + "(pending, done_materialization, last_modified_time)", + "CREATE INDEX I_COORD_JOBS_PENDING_LAST_MODIFIED_TIME ON COORD_JOBS (pending, last_modified_time)", + + "CREATE INDEX I_BUNLDE_JOBS_STATUS_CREATED_TIME ON BUNDLE_JOBS (status, created_time)", + "CREATE INDEX I_BUNLDE_JOBS_STATUS_LAST_MODIFIED_TIME ON BUNDLE_JOBS (status, last_modified_time)", + + "CREATE INDEX I_BUNLDE_ACTIONS_PENDING_LAST_MODIFIED_TIME ON BUNDLE_ACTIONS (pending, last_modified_time)"}; + } + private void ddlTweaksFor50(final String sqlFile, final boolean run) throws Exception { System.out.println("Creating composite indexes"); try (final Connection conn = createConnection(); @@ -753,26 +770,11 @@ public class OozieDBCLI { final Statement stmt = conn.createStatement()) { writer.println(); - - final String[] createCoveringIndexStatements = { - "CREATE INDEX I_WF_JOBS_STATUS_CREATED_TIME ON WF_JOBS (status, created_time)", - - "CREATE INDEX I_COORD_ACTIONS_JOB_ID_STATUS ON COORD_ACTIONS (job_id, status)", - - "CREATE INDEX I_COORD_JOBS_STATUS_CREATED_TIME ON COORD_JOBS (status, created_time)", - "CREATE INDEX I_COORD_JOBS_STATUS_LAST_MODIFIED_TIME ON COORD_JOBS (status, last_modified_time)", - "CREATE INDEX I_COORD_JOBS_PENDING_DONE_MATERIALIZATION_LAST_MODIFIED_TIME ON COORD_JOBS " + - "(pending, done_materialization, last_modified_time)", - "CREATE INDEX I_COORD_JOBS_PENDING_LAST_MODIFIED_TIME ON COORD_JOBS (pending, last_modified_time)", - - "CREATE INDEX I_BUNLDE_JOBS_STATUS_CREATED_TIME ON BUNDLE_JOBS (status, created_time)", - "CREATE INDEX I_BUNLDE_JOBS_STATUS_LAST_MODIFIED_TIME ON BUNDLE_JOBS (status, last_modified_time)", - - "CREATE INDEX I_BUNLDE_ACTIONS_PENDING_LAST_MODIFIED_TIME ON BUNDLE_ACTIONS (pending, last_modified_time)"}; + final String[] createCoveringIndexStatements = getIndexStatementsFor50(); for (final String query : createCoveringIndexStatements) { writer.println(query + ";"); - if (!run) { + if (run) { stmt.executeUpdate(query); } } http://git-wip-us.apache.org/repos/asf/oozie/blob/a3bcad6c/tools/src/test/java/org/apache/oozie/tools/TestOozieDBCLI.java ---------------------------------------------------------------------- diff --git a/tools/src/test/java/org/apache/oozie/tools/TestOozieDBCLI.java b/tools/src/test/java/org/apache/oozie/tools/TestOozieDBCLI.java index 1009f90..96ea1f9 100644 --- a/tools/src/test/java/org/apache/oozie/tools/TestOozieDBCLI.java +++ b/tools/src/test/java/org/apache/oozie/tools/TestOozieDBCLI.java @@ -22,14 +22,24 @@ package org.apache.oozie.tools; import org.apache.hadoop.fs.FileUtil; import org.apache.oozie.test.XTestCase; import org.junit.AfterClass; +import org.junit.Assert; import org.junit.BeforeClass; import java.io.ByteArrayOutputStream; import java.io.File; +import java.io.IOException; import java.io.PrintStream; +import java.nio.charset.Charset; +import java.nio.file.Files; import java.sql.Connection; +import java.sql.DatabaseMetaData; import java.sql.DriverManager; +import java.sql.ResultSet; +import java.sql.SQLException; import java.sql.Statement; +import java.util.ArrayList; +import java.util.Arrays; +import java.util.List; /** * Test OozieDBCLI for data base derby @@ -50,7 +60,7 @@ public class TestOozieDBCLI extends XTestCase { System.setProperty("oozie.test.config.file", oozieConfig.getAbsolutePath()); Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); - Connection conn = DriverManager.getConnection(url, "sa", ""); + Connection conn = getConnection(); conn.close(); super.setUp(false); @@ -71,7 +81,7 @@ public class TestOozieDBCLI extends XTestCase { private void execSQL(String sql) throws Exception { Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); - Connection conn = DriverManager.getConnection(url, "sa", ""); + Connection conn = getConnection(); Statement st = conn.createStatement(); st.executeUpdate(sql); @@ -87,6 +97,7 @@ public class TestOozieDBCLI extends XTestCase { int result = execOozieDBCLICommands(argsCreate); assertEquals(0, result); assertTrue(createSql.exists()); + verifyIndexesCreated(); ByteArrayOutputStream data = new ByteArrayOutputStream(); PrintStream oldOut = System.out; @@ -128,12 +139,61 @@ public class TestOozieDBCLI extends XTestCase { String[] argsUpgrade = { "upgrade", "-sqlfile", upgrade.getAbsolutePath(), "-run" }; assertEquals(0, execOozieDBCLICommands(argsUpgrade)); + verifyUpgradeScriptContainsIndexStatements(upgrade); assertTrue(upgrade.exists()); + File postUpgrade = new File(getTestCaseConfDir() + File.separator + "postUpdate.sql"); String[] argsPostUpgrade = { "postupgrade", "-sqlfile", postUpgrade.getAbsolutePath(), "-run" }; assertEquals(0, execOozieDBCLICommands(argsPostUpgrade)); } + private void verifyUpgradeScriptContainsIndexStatements(final File upgrade) throws IOException { + final Charset charset = Charset.defaultCharset(); + final List<String> stringList = Files.readAllLines(upgrade.toPath(), charset); + final List<String> actualIndexStatements = Arrays.asList(stringList.toArray(new String[]{})); + final String[] expectedIndexStatements = OozieDBCLI.getIndexStatementsFor50(); + + for (final String indexStmt : expectedIndexStatements) { + Assert.assertTrue(actualIndexStatements.contains(indexStmt + ";")); + } + } + + private void verifyIndexesCreated() throws SQLException { + final List<String> indexes = getIndexes(); + for (final String indexStmt : OozieDBCLI.getIndexStatementsFor50()) { + final String index = indexStmt.split(" ")[2]; + Assert.assertTrue(indexes.contains(index)); + } + } + + private List<String> getIndexes() throws SQLException { + final List<String> indexes = new ArrayList<>(); + try (final Connection connection = getConnection(); + final ResultSet rs = connection.getMetaData().getTables(null, "SA", "%", null)) { + while (rs.next()) { + final String tableName = rs.getString(3); + indexes.addAll(getIndexesForTable(connection, tableName)); + } + } + return indexes; + } + + private List<String> getIndexesForTable(final Connection connection, final String tableName) throws SQLException { + final List<String> indexes = new ArrayList<>(); + final DatabaseMetaData metaData = connection.getMetaData(); + try (final ResultSet rs = metaData.getIndexInfo(null, "SA", tableName, false, true)) { + while (rs.next()) { + final String indexName = rs.getString(6); + indexes.add(indexName); + } + } + return indexes; + } + + private Connection getConnection() throws SQLException { + return DriverManager.getConnection(url, "sa", ""); + } + private int execOozieDBCLICommands(String[] args) { try { OozieDBCLI.main(args);
