add tpch schema for mysql.
Project: http://git-wip-us.apache.org/repos/asf/tajo/repo Commit: http://git-wip-us.apache.org/repos/asf/tajo/commit/4803b35b Tree: http://git-wip-us.apache.org/repos/asf/tajo/tree/4803b35b Diff: http://git-wip-us.apache.org/repos/asf/tajo/diff/4803b35b Branch: refs/heads/TAJO-1730 Commit: 4803b35b938049119a1d95a49ea7f2dd577d0356 Parents: 953e625 Author: Hyunsik Choi <[email protected]> Authored: Sun Aug 16 11:09:01 2015 +0900 Committer: Hyunsik Choi <[email protected]> Committed: Sun Aug 16 11:09:01 2015 +0900 ---------------------------------------------------------------------- tajo-project/pom.xml | 6 ++ tajo-storage/tajo-storage-jdbc/pom.xml | 9 +- .../storage/jdbc/JdbcTablespaceTestBase.java | 25 +++++- tajo-storage/tajo-storage-mysql/pom.xml | 14 +++ .../storage/jdbc/TestMysqlJdbcTableSpace.java | 89 ++++++++++---------- .../src/test/resources/tpch/customer.sql | 10 +++ .../src/test/resources/tpch/lineitem.sql | 18 ++++ .../src/test/resources/tpch/nation.sql | 6 ++ .../src/test/resources/tpch/orders.sql | 11 +++ .../src/test/resources/tpch/part.sql | 11 +++ .../src/test/resources/tpch/partsupp.sql | 7 ++ .../src/test/resources/tpch/region.sql | 5 ++ .../src/test/resources/tpch/supplier.sql | 9 ++ 13 files changed, 173 insertions(+), 47 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/tajo/blob/4803b35b/tajo-project/pom.xml ---------------------------------------------------------------------- diff --git a/tajo-project/pom.xml b/tajo-project/pom.xml index 1512103..afbc2ba 100644 --- a/tajo-project/pom.xml +++ b/tajo-project/pom.xml @@ -786,6 +786,12 @@ </dependency> <dependency> <groupId>org.apache.tajo</groupId> + <artifactId>tajo-storage-jdbc</artifactId> + <version>${tajo.version}</version> + <type>test-jar</type> + </dependency> + <dependency> + <groupId>org.apache.tajo</groupId> <artifactId>tajo-pullserver</artifactId> <version>${tajo.version}</version> </dependency> http://git-wip-us.apache.org/repos/asf/tajo/blob/4803b35b/tajo-storage/tajo-storage-jdbc/pom.xml ---------------------------------------------------------------------- diff --git a/tajo-storage/tajo-storage-jdbc/pom.xml b/tajo-storage/tajo-storage-jdbc/pom.xml index 11c827a..d45ec2f 100644 --- a/tajo-storage/tajo-storage-jdbc/pom.xml +++ b/tajo-storage/tajo-storage-jdbc/pom.xml @@ -63,8 +63,7 @@ <artifactId>apache-rat-plugin</artifactId> <configuration> <excludes> - <exclude>src/main/resources/*.json</exclude> - <exclude>src/test/resources/*.json</exclude> + <exclude>src/test/resources/*.sql</exclude> </excludes> </configuration> <executions> @@ -188,6 +187,12 @@ <artifactId>tajo-storage-common</artifactId> <scope>provided</scope> </dependency> + <dependency> + <groupId>org.apache.tajo</groupId> + <artifactId>tajo-cluster-tests</artifactId> + <type>test-jar</type> + <scope>test</scope> + </dependency> <dependency> <groupId>com.google.protobuf</groupId> http://git-wip-us.apache.org/repos/asf/tajo/blob/4803b35b/tajo-storage/tajo-storage-jdbc/src/test/java/org/apache/tajo/storage/jdbc/JdbcTablespaceTestBase.java ---------------------------------------------------------------------- diff --git a/tajo-storage/tajo-storage-jdbc/src/test/java/org/apache/tajo/storage/jdbc/JdbcTablespaceTestBase.java b/tajo-storage/tajo-storage-jdbc/src/test/java/org/apache/tajo/storage/jdbc/JdbcTablespaceTestBase.java index 5dfe4c3..c8ebe7a 100644 --- a/tajo-storage/tajo-storage-jdbc/src/test/java/org/apache/tajo/storage/jdbc/JdbcTablespaceTestBase.java +++ b/tajo-storage/tajo-storage-jdbc/src/test/java/org/apache/tajo/storage/jdbc/JdbcTablespaceTestBase.java @@ -18,5 +18,28 @@ package org.apache.tajo.storage.jdbc; -public class JdbcTablespaceTestBase { +import org.apache.tajo.QueryTestCaseBase; +import org.apache.tajo.storage.TablespaceManager; +import org.apache.tajo.util.FileUtil; +import org.junit.AfterClass; +import org.junit.BeforeClass; + +import java.io.IOException; +import java.sql.SQLException; +import java.sql.Statement; + +public abstract class JdbcTablespaceTestBase extends QueryTestCaseBase { + + @BeforeClass + public static void setUp() throws Exception { + } + + @AfterClass + public static void tearDown() { + } + + protected void prepareDefaultTables(Statement statement) throws IOException, SQLException { + statement.addBatch(FileUtil.readTextFileFromResource("table_ddl/all_types")); + statement.executeBatch(); + } } http://git-wip-us.apache.org/repos/asf/tajo/blob/4803b35b/tajo-storage/tajo-storage-mysql/pom.xml ---------------------------------------------------------------------- diff --git a/tajo-storage/tajo-storage-mysql/pom.xml b/tajo-storage/tajo-storage-mysql/pom.xml index c2157fa..f0a072d 100644 --- a/tajo-storage/tajo-storage-mysql/pom.xml +++ b/tajo-storage/tajo-storage-mysql/pom.xml @@ -54,6 +54,7 @@ <excludes> <exclude>src/main/resources/*.json</exclude> <exclude>src/test/resources/*.json</exclude> + <exclude>src/test/resources/tpch/*.sql</exclude> </excludes> </configuration> <executions> @@ -120,6 +121,19 @@ <dependency> <groupId>org.apache.tajo</groupId> <artifactId>tajo-storage-jdbc</artifactId> + <type>jar</type> + <scope>provided</scope> + </dependency> + <dependency> + <groupId>org.apache.tajo</groupId> + <artifactId>tajo-storage-jdbc</artifactId> + <type>test-jar</type> + <scope>provided</scope> + </dependency> + <dependency> + <groupId>org.apache.tajo</groupId> + <artifactId>tajo-cluster-tests</artifactId> + <type>test-jar</type> <scope>provided</scope> </dependency> <dependency> http://git-wip-us.apache.org/repos/asf/tajo/blob/4803b35b/tajo-storage/tajo-storage-mysql/src/test/java/org/apache/tajo/storage/jdbc/TestMysqlJdbcTableSpace.java ---------------------------------------------------------------------- diff --git a/tajo-storage/tajo-storage-mysql/src/test/java/org/apache/tajo/storage/jdbc/TestMysqlJdbcTableSpace.java b/tajo-storage/tajo-storage-mysql/src/test/java/org/apache/tajo/storage/jdbc/TestMysqlJdbcTableSpace.java index ed25d06..44eb6c4 100644 --- a/tajo-storage/tajo-storage-mysql/src/test/java/org/apache/tajo/storage/jdbc/TestMysqlJdbcTableSpace.java +++ b/tajo-storage/tajo-storage-mysql/src/test/java/org/apache/tajo/storage/jdbc/TestMysqlJdbcTableSpace.java @@ -27,28 +27,26 @@ import org.apache.tajo.catalog.TableDesc; import org.apache.tajo.conf.TajoConf; import org.apache.tajo.storage.TablespaceManager; import org.apache.tajo.storage.mysql.MySQLTablespace; +import org.apache.tajo.util.FileUtil; import org.junit.BeforeClass; import org.junit.Test; +import java.io.IOException; import java.net.URI; -import java.sql.Connection; -import java.sql.DriverManager; -import java.sql.Statement; +import java.sql.*; import java.util.Set; import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertFalse; import static org.junit.Assert.assertTrue; -public class TestMysqlJdbcTableSpace { +public class TestMysqlJdbcTableSpace extends JdbcTablespaceTestBase { static TestingMySqlServer server; - @BeforeClass - public static void setUp() throws Exception { + public TestMysqlJdbcTableSpace() throws Exception { server = new TestingMySqlServer("testuser", "testpass", - "meta_test", - "create_table", - "drop_table" + "tpch" ); MySQLTablespace mysqlTablespace = new MySQLTablespace("mysql_cluster", URI.create(server.getJdbcUrl()), null); @@ -56,6 +54,23 @@ public class TestMysqlJdbcTableSpace { TablespaceManager.addTableSpaceForTest(mysqlTablespace); } + @BeforeClass + public static void setUp() throws IOException, SQLException { + prepareTables(); + } + + @Test + public void testGeneral() { + assertTrue(server.isRunning()); + assertTrue(server.isReadyForConnections()); + assertEquals(server.getMySqlVersion(), "5.5.9"); + assertEquals(server.getDatabases(), ImmutableSet.of("basic")); + assertEquals(server.getUser(), "testuser"); + assertEquals(server.getPassword(), "testpass"); + assertEquals(server.getJdbcUrl().substring(0, 5), "jdbc:"); + assertEquals(server.getPort(), URI.create(server.getJdbcUrl().substring(5)).getPort()); + } + @Test public void testTablespaceHandler() throws Exception { assertTrue((TablespaceManager.getByName("mysql_cluster").get()) instanceof MySQLTablespace); @@ -67,45 +82,31 @@ public class TestMysqlJdbcTableSpace { assertEquals(server.getJdbcUrl(), TablespaceManager.get(server.getJdbcUrl()).get().getUri().toASCIIString()); } - @Test - public void testMetadataProvider() throws Exception { - + static final String [] TPCH_TABLES = { + "customer", "lineitem", "nation", "orders", "part", "partsupp", "region", "supplier" + }; - try (TestingMySqlServer server = new TestingMySqlServer("testuser", "testpass", "db1", "db2")) { - assertTrue(server.isRunning()); - assertTrue(server.isReadyForConnections()); - assertEquals(server.getMySqlVersion(), "5.5.9"); - assertEquals(server.getDatabases(), ImmutableSet.of("db1", "db2")); - assertEquals(server.getUser(), "testuser"); - assertEquals(server.getPassword(), "testpass"); - assertEquals(server.getJdbcUrl().substring(0, 5), "jdbc:"); - assertEquals(server.getPort(), URI.create(server.getJdbcUrl().substring(5)).getPort()); - - try (Connection connection = DriverManager.getConnection(server.getJdbcUrl())) { - connection.setCatalog("db1"); - - try (Statement statement = connection.createStatement()) { - statement.execute("CREATE TABLE t1 (c1 bigint PRIMARY KEY)"); - statement.execute("CREATE TABLE t2 (c1 int PRIMARY KEY, c2 VARCHAR(20), c3 TIME)"); - } + private static void prepareTables() throws SQLException, IOException { + try (Connection connection = DriverManager.getConnection(server.getJdbcUrl())) { + connection.setCatalog("tpch"); + try (Statement statement = connection.createStatement()) { + statement.executeUpdate(FileUtil.readTextFileFromResource("tpch/" + TPCH_TABLES + ".sql")); } + } + } - System.out.println(server.getJdbcUrl()); - MySQLTablespace tablespace = new MySQLTablespace("mysql", URI.create(server.getJdbcUrl()), null); - - URI uri = tablespace.getTableUri("abc", "table1"); - ConnectionInfo c1 = ConnectionInfo.fromURI(uri); - assertEquals("table1", c1.table()); - - MetadataProvider provider = tablespace.getMetadataProvider(); - Set<String> tables = Sets.newHashSet(provider.getTables(null, null)); - assertEquals(Sets.newHashSet("t1", "t2"), tables); + @Test + public void testMetadataProvider() throws Exception { + MySQLTablespace tablespace = (MySQLTablespace) TablespaceManager.get(server.getJdbcUrl()).get(); + MetadataProvider provider = tablespace.getMetadataProvider(); - TableDesc desc = provider.getTableDescriptor("", "t2"); - assertEquals(tablespace.getUri() + "&table=t2", desc.getUri().toASCIIString()); + Set<String> tables = Sets.newHashSet(provider.getTables(null, null)); + assertEquals(Sets.newHashSet(TPCH_TABLES), tables); - Schema schema = desc.getSchema(); - System.out.println(">>> " + schema); - } +// TableDesc desc = provider.getTableDescriptor("", "t2"); +// assertEquals(tablespace.getUri() + "&table=t2", desc.getUri().toASCIIString()); +// +// Schema schema = desc.getSchema(); +// System.out.println(">>> " + schema); } } http://git-wip-us.apache.org/repos/asf/tajo/blob/4803b35b/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/customer.sql ---------------------------------------------------------------------- diff --git a/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/customer.sql b/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/customer.sql new file mode 100644 index 0000000..35b1861 --- /dev/null +++ b/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/customer.sql @@ -0,0 +1,10 @@ +CREATE TABLE CUSTOMER ( + C_CUSTKEY INTEGER NOT NULL, + C_NAME VARCHAR(25) NOT NULL, + C_ADDRESS VARCHAR(40) NOT NULL, + C_NATIONKEY INTEGER NOT NULL, + C_PHONE CHAR(15) NOT NULL, + C_ACCTBAL DECIMAL(15,2) NOT NULL, + C_MKTSEGMENT CHAR(10) NOT NULL, + C_COMMENT VARCHAR(117) NOT NULL +); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/4803b35b/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/lineitem.sql ---------------------------------------------------------------------- diff --git a/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/lineitem.sql b/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/lineitem.sql new file mode 100644 index 0000000..a7f61bb --- /dev/null +++ b/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/lineitem.sql @@ -0,0 +1,18 @@ +CREATE TABLE LINEITEM ( + L_ORDERKEY INTEGER NOT NULL, + L_PARTKEY INTEGER NOT NULL, + L_SUPPKEY INTEGER NOT NULL, + L_LINENUMBER INTEGER NOT NULL, + L_QUANTITY DECIMAL(15,2) NOT NULL, + L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL, + L_DISCOUNT DECIMAL(15,2) NOT NULL, + L_TAX DECIMAL(15,2) NOT NULL, + L_RETURNFLAG CHAR(1) NOT NULL, + L_LINESTATUS CHAR(1) NOT NULL, + L_SHIPDATE DATE NOT NULL, + L_COMMITDATE DATE NOT NULL, + L_RECEIPTDATE DATE NOT NULL, + L_SHIPINSTRUCT CHAR(25) NOT NULL, + L_SHIPMODE CHAR(10) NOT NULL, + L_COMMENT VARCHAR(44) NOT NULL +); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/4803b35b/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/nation.sql ---------------------------------------------------------------------- diff --git a/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/nation.sql b/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/nation.sql new file mode 100644 index 0000000..f7ecda8 --- /dev/null +++ b/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/nation.sql @@ -0,0 +1,6 @@ +CREATE TABLE NATION ( + N_NATIONKEY INTEGER NOT NULL, + N_NAME CHAR(25) NOT NULL, + N_REGIONKEY INTEGER NOT NULL, + N_COMMENT VARCHAR(152) +); http://git-wip-us.apache.org/repos/asf/tajo/blob/4803b35b/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/orders.sql ---------------------------------------------------------------------- diff --git a/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/orders.sql b/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/orders.sql new file mode 100644 index 0000000..220d576 --- /dev/null +++ b/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/orders.sql @@ -0,0 +1,11 @@ +CREATE TABLE ORDERS ( + O_ORDERKEY INTEGER NOT NULL, + O_CUSTKEY INTEGER NOT NULL, + O_ORDERSTATUS CHAR(1) NOT NULL, + O_TOTALPRICE DECIMAL(15,2) NOT NULL, + O_ORDERDATE DATE NOT NULL, + O_ORDERPRIORITY CHAR(15) NOT NULL, + O_CLERK CHAR(15) NOT NULL, + O_SHIPPRIORITY INTEGER NOT NULL, + O_COMMENT VARCHAR(79) NOT NULL +); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/4803b35b/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/part.sql ---------------------------------------------------------------------- diff --git a/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/part.sql b/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/part.sql new file mode 100644 index 0000000..e66f73c --- /dev/null +++ b/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/part.sql @@ -0,0 +1,11 @@ +CREATE TABLE PART ( + P_PARTKEY INTEGER NOT NULL, + P_NAME VARCHAR(55) NOT NULL, + P_MFGR CHAR(25) NOT NULL, + P_BRAND CHAR(10) NOT NULL, + P_TYPE VARCHAR(25) NOT NULL, + P_SIZE INTEGER NOT NULL, + P_CONTAINER CHAR(10) NOT NULL, + P_RETAILPRICE DECIMAL(15,2) NOT NULL, + P_COMMENT VARCHAR(23) NOT NULL +); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/4803b35b/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/partsupp.sql ---------------------------------------------------------------------- diff --git a/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/partsupp.sql b/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/partsupp.sql new file mode 100644 index 0000000..1f61331 --- /dev/null +++ b/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/partsupp.sql @@ -0,0 +1,7 @@ +CREATE TABLE PARTSUPP ( + PS_PARTKEY INTEGER NOT NULL, + PS_SUPPKEY INTEGER NOT NULL, + PS_AVAILQTY INTEGER NOT NULL, + PS_SUPPLYCOST DECIMAL(15,2) NOT NULL, + PS_COMMENT VARCHAR(199) NOT NULL +); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/4803b35b/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/region.sql ---------------------------------------------------------------------- diff --git a/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/region.sql b/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/region.sql new file mode 100644 index 0000000..c47e26e --- /dev/null +++ b/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/region.sql @@ -0,0 +1,5 @@ +CREATE TABLE REGION ( + R_REGIONKEY INTEGER NOT NULL, + R_NAME CHAR(25) NOT NULL, + R_COMMENT VARCHAR(152) +); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/tajo/blob/4803b35b/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/supplier.sql ---------------------------------------------------------------------- diff --git a/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/supplier.sql b/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/supplier.sql new file mode 100644 index 0000000..a4d35d3 --- /dev/null +++ b/tajo-storage/tajo-storage-mysql/src/test/resources/tpch/supplier.sql @@ -0,0 +1,9 @@ +CREATE TABLE SUPPLIER ( + S_SUPPKEY INTEGER NOT NULL, + S_NAME CHAR(25) NOT NULL, + S_ADDRESS VARCHAR(40) NOT NULL, + S_NATIONKEY INTEGER NOT NULL, + S_PHONE CHAR(15) NOT NULL, + S_ACCTBAL DECIMAL(15,2) NOT NULL, + S_COMMENT VARCHAR(101) NOT NULL +); \ No newline at end of file
