Search in sources :

Example 41 with QuerySummary

use of org.apache.drill.test.QueryBuilder.QuerySummary in project drill by apache.

the class TestJdbcWriterWithMySQL method testWithLargeFile.

@Test
public void testWithLargeFile() throws Exception {
    String query = "CREATE TABLE mysql.`drill_mysql_test`.test (id,first_name,last_name,email,gender,ip_address) AS " + "SELECT id,first_name,last_name,email,gender,ip_address FROM cp.`csv/large_csv.csvh`";
    QuerySummary insertResults = queryBuilder().sql(query).run();
    assertTrue(insertResults.succeeded());
    query = "SELECT COUNT(*) FROM mysql.`drill_mysql_test`.test";
    long rowCount = queryBuilder().sql(query).singletonLong();
    assertEquals(6000, rowCount);
    // Now drop the table
    String dropQuery = "DROP TABLE mysql.`drill_mysql_test`.`test`";
    QuerySummary dropResults = queryBuilder().sql(dropQuery).run();
    assertTrue(dropResults.succeeded());
}
Also used : QuerySummary(org.apache.drill.test.QueryBuilder.QuerySummary) ClusterTest(org.apache.drill.test.ClusterTest) Test(org.junit.Test) JdbcStorageTest(org.apache.drill.categories.JdbcStorageTest)

Example 42 with QuerySummary

use of org.apache.drill.test.QueryBuilder.QuerySummary in project drill by apache.

the class TestJdbcWriterWithMySQL method testBasicCTASWithSpacesInFieldNames.

@Test
public void testBasicCTASWithSpacesInFieldNames() throws Exception {
    String query = "CREATE TABLE mysql.`drill_mysql_test`.`test table` (`My id`, `My name`) AS SELECT * FROM (VALUES(1,2), (3,4))";
    // Create the table and insert the values
    QuerySummary insertResults = queryBuilder().sql(query).run();
    assertTrue(insertResults.succeeded());
    // Query the table to see if the insertion was successful
    String testQuery = "SELECT * FROM  mysql.`drill_mysql_test`.`test table`";
    DirectRowSet results = queryBuilder().sql(testQuery).rowSet();
    TupleMetadata expectedSchema = new SchemaBuilder().add("My id", MinorType.BIGINT, DataMode.OPTIONAL).add("My name", MinorType.BIGINT, DataMode.OPTIONAL).buildSchema();
    RowSet expected = new RowSetBuilder(client.allocator(), expectedSchema).addRow(1L, 2L).addRow(3L, 4L).build();
    RowSetUtilities.verify(expected, results);
    // Now drop the table
    String dropQuery = "DROP TABLE mysql.`drill_mysql_test`.`test table`";
    QuerySummary dropResults = queryBuilder().sql(dropQuery).run();
    assertTrue(dropResults.succeeded());
}
Also used : RowSetBuilder(org.apache.drill.exec.physical.rowSet.RowSetBuilder) QuerySummary(org.apache.drill.test.QueryBuilder.QuerySummary) TupleMetadata(org.apache.drill.exec.record.metadata.TupleMetadata) DirectRowSet(org.apache.drill.exec.physical.rowSet.DirectRowSet) SchemaBuilder(org.apache.drill.exec.record.metadata.SchemaBuilder) DirectRowSet(org.apache.drill.exec.physical.rowSet.DirectRowSet) RowSet(org.apache.drill.exec.physical.rowSet.RowSet) ClusterTest(org.apache.drill.test.ClusterTest) Test(org.junit.Test) JdbcStorageTest(org.apache.drill.categories.JdbcStorageTest)

Example 43 with QuerySummary

use of org.apache.drill.test.QueryBuilder.QuerySummary in project drill by apache.

the class TestJdbcWriterWithMySQL method testWithReallyLongFile.

@Test
@Ignore("This is a slow test.  Please run manually.")
public void testWithReallyLongFile() throws Exception {
    Path generatedFile = null;
    try {
        generatedFile = JdbcTestUtils.generateCsvFile("csv/very_large_file.csvh", 10, 100000);
    } catch (IOException e) {
        logger.error(e.getMessage());
        fail();
    }
    // Query the table to see if the insertion was successful
    String testQuery = "SELECT COUNT(*) FROM dfs.`csv/very_large_file.csvh`";
    long resultsCount = queryBuilder().sql(testQuery).singletonLong();
    assertEquals(100000, resultsCount);
    String ctasQuery = "CREATE TABLE mysql.`drill_mysql_test`.`test_big_table` AS " + "SELECT * FROM dfs.`csv/very_large_file.csvh`";
    QuerySummary insertResults = queryBuilder().sql(ctasQuery).run();
    assertTrue(insertResults.succeeded());
    // Query the table to see if the insertion was successful
    testQuery = "SELECT COUNT(*) FROM mysql.`drill_mysql_test`.`test_big_table`";
    resultsCount = queryBuilder().sql(testQuery).singletonLong();
    assertEquals(100000, resultsCount);
    String dropQuery = "DROP TABLE mysql.`drill_mysql_test`.`test_big_table`";
    QuerySummary dropResults = queryBuilder().sql(dropQuery).run();
    assertTrue(dropResults.succeeded());
    boolean deletedFile = JdbcTestUtils.deleteCsvFile(String.valueOf(generatedFile));
    if (!deletedFile) {
        fail();
    }
}
Also used : Path(org.apache.hadoop.fs.Path) QuerySummary(org.apache.drill.test.QueryBuilder.QuerySummary) IOException(java.io.IOException) Ignore(org.junit.Ignore) ClusterTest(org.apache.drill.test.ClusterTest) Test(org.junit.Test) JdbcStorageTest(org.apache.drill.categories.JdbcStorageTest)

Example 44 with QuerySummary

use of org.apache.drill.test.QueryBuilder.QuerySummary in project drill by apache.

the class TestJdbcWriterWithH2 method testBasicCTASWithSpacesInTableName.

@Test
public void testBasicCTASWithSpacesInTableName() throws Exception {
    String query = "CREATE TABLE h2.tmp.`drill_h2_test`.`test table` (ID, NAME) AS SELECT * FROM (VALUES(1,2), (3,4))";
    // Create the table and insert the values
    QuerySummary insertResults = queryBuilder().sql(query).run();
    assertTrue(insertResults.succeeded());
    try {
        // Query the table to see if the insertion was successful
        String testQuery = "SELECT * FROM h2.tmp.`drill_h2_test`.`test table`";
        DirectRowSet results = queryBuilder().sql(testQuery).rowSet();
        TupleMetadata expectedSchema = new SchemaBuilder().add("ID", MinorType.BIGINT, DataMode.OPTIONAL).add("NAME", MinorType.BIGINT, DataMode.OPTIONAL).buildSchema();
        RowSet expected = new RowSetBuilder(client.allocator(), expectedSchema).addRow(1L, 2L).addRow(3L, 4L).build();
        RowSetUtilities.verify(expected, results);
    } finally {
        String dropQuery = "DROP TABLE  h2.tmp.`drill_h2_test`.`test table`";
        QuerySummary dropResults = queryBuilder().sql(dropQuery).run();
        assertTrue(dropResults.succeeded());
    }
}
Also used : RowSetBuilder(org.apache.drill.exec.physical.rowSet.RowSetBuilder) QuerySummary(org.apache.drill.test.QueryBuilder.QuerySummary) TupleMetadata(org.apache.drill.exec.record.metadata.TupleMetadata) DirectRowSet(org.apache.drill.exec.physical.rowSet.DirectRowSet) SchemaBuilder(org.apache.drill.exec.record.metadata.SchemaBuilder) DirectRowSet(org.apache.drill.exec.physical.rowSet.DirectRowSet) RowSet(org.apache.drill.exec.physical.rowSet.RowSet) ClusterTest(org.apache.drill.test.ClusterTest) Test(org.junit.Test)

Example 45 with QuerySummary

use of org.apache.drill.test.QueryBuilder.QuerySummary in project drill by apache.

the class TestJdbcWriterWithH2 method testCTASWithDuplicateTable.

@Test
public void testCTASWithDuplicateTable() throws Exception {
    String query = String.format("CREATE TABLE %s (ID, NAME) AS SELECT * FROM (VALUES(1,2), (3,4))", TEST_TABLE);
    // Create the table and insert the values
    QuerySummary insertResults = queryBuilder().sql(query).run();
    assertTrue(insertResults.succeeded());
    try {
        // Run the query again, should fail.
        try {
            queryBuilder().sql(query).run();
            fail();
        } catch (UserRemoteException e) {
            assertTrue(e.getMessage().contains("VALIDATION ERROR"));
        }
        // Try again with IF NOT EXISTS, Should not do anything, but not throw an exception
        query = String.format("CREATE TABLE IF NOT EXISTS %s (ID, NAME) AS SELECT * FROM (VALUES(1,2), (3,4))", TEST_TABLE);
        DirectRowSet results = queryBuilder().sql(query).rowSet();
        TupleMetadata expectedSchema = new SchemaBuilder().add("ok", MinorType.BIT).add("summary", MinorType.VARCHAR, DataMode.OPTIONAL).buildSchema();
        RowSet expected = new RowSetBuilder(client.allocator(), expectedSchema).addRow(false, "A table or view with given name [test_table] already exists in schema [h2.tmp.drill_h2_test]").build();
        RowSetUtilities.verify(expected, results);
    } finally {
        QuerySummary dropResults = queryBuilder().sql(DROP_TEST_TABLE).run();
        assertTrue(dropResults.succeeded());
    }
}
Also used : RowSetBuilder(org.apache.drill.exec.physical.rowSet.RowSetBuilder) UserRemoteException(org.apache.drill.common.exceptions.UserRemoteException) QuerySummary(org.apache.drill.test.QueryBuilder.QuerySummary) TupleMetadata(org.apache.drill.exec.record.metadata.TupleMetadata) DirectRowSet(org.apache.drill.exec.physical.rowSet.DirectRowSet) SchemaBuilder(org.apache.drill.exec.record.metadata.SchemaBuilder) DirectRowSet(org.apache.drill.exec.physical.rowSet.DirectRowSet) RowSet(org.apache.drill.exec.physical.rowSet.RowSet) ClusterTest(org.apache.drill.test.ClusterTest) Test(org.junit.Test)

Aggregations

QuerySummary (org.apache.drill.test.QueryBuilder.QuerySummary)49 Test (org.junit.Test)47 ClusterTest (org.apache.drill.test.ClusterTest)40 RowSet (org.apache.drill.exec.physical.rowSet.RowSet)27 RowSetBuilder (org.apache.drill.exec.physical.rowSet.RowSetBuilder)26 SchemaBuilder (org.apache.drill.exec.record.metadata.SchemaBuilder)26 TupleMetadata (org.apache.drill.exec.record.metadata.TupleMetadata)26 DirectRowSet (org.apache.drill.exec.physical.rowSet.DirectRowSet)25 JdbcStorageTest (org.apache.drill.categories.JdbcStorageTest)21 PlannerTest (org.apache.drill.categories.PlannerTest)6 SlowTest (org.apache.drill.categories.SlowTest)6 PlanFragment (org.apache.drill.exec.proto.BitControl.PlanFragment)4 QueryPlanFragments (org.apache.drill.exec.proto.UserProtos.QueryPlanFragments)4 Ignore (org.junit.Ignore)4 IOException (java.io.IOException)3 UserRemoteException (org.apache.drill.common.exceptions.UserRemoteException)3 Path (org.apache.hadoop.fs.Path)3 File (java.io.File)2 RootAllocator (org.apache.drill.exec.memory.RootAllocator)2 DrillbitEndpoint (org.apache.drill.exec.proto.CoordinationProtos.DrillbitEndpoint)2