use of org.apache.drill.exec.record.metadata.TupleMetadata in project drill by apache.
the class TestJdbcWriterWithMySQL method testCTASWithDuplicateTable.
@Test
public void testCTASWithDuplicateTable() throws Exception {
String query = "CREATE TABLE mysql.`drill_mysql_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());
// 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 = "CREATE TABLE IF NOT EXISTS mysql.`drill_mysql_test`.`test_table` (ID, NAME) AS SELECT * FROM (VALUES(1,2), (3,4))";
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 [mysql.drill_mysql_test]").build();
RowSetUtilities.verify(expected, results);
}
use of org.apache.drill.exec.record.metadata.TupleMetadata in project drill by apache.
the class TestJdbcWriterWithMySQL method testCTASFromFileWithNulls.
@Test
public void testCTASFromFileWithNulls() throws Exception {
String sql = "CREATE TABLE mysql.drill_mysql_test.`t1` AS SELECT int_field, float_field, varchar_field, boolean_field FROM cp.`json/dataTypes.json`";
QuerySummary insertResults = queryBuilder().sql(sql).run();
assertTrue(insertResults.succeeded());
sql = "SELECT * FROM mysql.drill_mysql_test.`t1`";
DirectRowSet results = queryBuilder().sql(sql).rowSet();
TupleMetadata expectedSchema = new SchemaBuilder().addNullable("int_field", MinorType.BIGINT, 19).addNullable("float_field", MinorType.FLOAT8, 22).addNullable("varchar_field", MinorType.VARCHAR, 38, 0).addNullable("boolean_field", MinorType.BIT, 1).build();
RowSet expected = new RowSetBuilder(client.allocator(), expectedSchema).addRow(1L, 1.0, "foo1", true).addRow(null, null, null, null).addRow(2L, 2.0, "foo2", false).build();
RowSetUtilities.verify(expected, results);
String dropQuery = "DROP TABLE mysql.`drill_mysql_test`.`t1`";
QuerySummary dropResults = queryBuilder().sql(dropQuery).run();
assertTrue(dropResults.succeeded());
}
use of org.apache.drill.exec.record.metadata.TupleMetadata in project drill by apache.
the class TestJdbcWriterWithMySQL method testBasicCTASWithSpacesInTableName.
@Test
public void testBasicCTASWithSpacesInTableName() throws Exception {
String query = "CREATE TABLE mysql.`drill_mysql_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());
// 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("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);
// Now drop the table
String dropQuery = "DROP TABLE mysql.`drill_mysql_test`.`test table`";
QuerySummary dropResults = queryBuilder().sql(dropQuery).run();
assertTrue(dropResults.succeeded());
}
use of org.apache.drill.exec.record.metadata.TupleMetadata in project drill by apache.
the class TestJdbcWriterWithMySQL method testCTASFromFileWithUglyData.
@Test
public void testCTASFromFileWithUglyData() throws Exception {
String sql = "CREATE TABLE mysql.drill_mysql_test.`t2` AS SELECT ugly1, ugly2 FROM cp.`json/uglyData.json`";
QuerySummary insertResults = queryBuilder().sql(sql).run();
assertTrue(insertResults.succeeded());
sql = "SELECT * FROM mysql.drill_mysql_test.`t2`";
DirectRowSet results = queryBuilder().sql(sql).rowSet();
TupleMetadata expectedSchema = new SchemaBuilder().addNullable("ugly1", MinorType.VARCHAR, 38).addNullable("ugly2", MinorType.VARCHAR, 38).build();
RowSet expected = new RowSetBuilder(client.allocator(), expectedSchema).addRow("O'Malley", "Abraham Lincoln's best speech started with: \"Four score and seven years ago...").build();
RowSetUtilities.verify(expected, results);
String dropQuery = "DROP TABLE mysql.`drill_mysql_test`.`t2`";
QuerySummary dropResults = queryBuilder().sql(dropQuery).run();
assertTrue(dropResults.succeeded());
}
use of org.apache.drill.exec.record.metadata.TupleMetadata in project drill by apache.
the class TestJdbcWriterWithMySQL method testBasicCTASWithDataTypes.
@Test
public void testBasicCTASWithDataTypes() throws Exception {
String query = "CREATE TABLE mysql.drill_mysql_test.`data_types` AS " + "SELECT CAST(1 AS INTEGER) AS int_field," + "CAST(2 AS BIGINT) AS bigint_field," + "CAST(3.0 AS FLOAT) AS float4_field," + "CAST(4.0 AS DOUBLE) AS float8_field," + "'5.0' AS varchar_field," + "CAST('2021-01-01' AS DATE) as date_field," + "CAST('12:00:00' AS TIME) as time_field, " + "CAST('2015-12-30 22:55:55.23' AS TIMESTAMP) as timestamp_field, true AS boolean_field " + "FROM (VALUES(1))";
// 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`.`data_types`";
DirectRowSet results = queryBuilder().sql(testQuery).rowSet();
TupleMetadata expectedSchema = new SchemaBuilder().addNullable("int_field", MinorType.INT, 10).addNullable("bigint_field", MinorType.BIGINT, 19).addNullable("float4_field", MinorType.FLOAT8, 12).addNullable("float8_field", MinorType.FLOAT8, 22).addNullable("varchar_field", MinorType.VARCHAR, 38).addNullable("date_field", MinorType.DATE, 10).addNullable("time_field", MinorType.TIME, 10).addNullable("timestamp_field", MinorType.TIMESTAMP, 19).addNullable("boolean_field", MinorType.BIT).buildSchema();
RowSet expected = new RowSetBuilder(client.allocator(), expectedSchema).addRow(1, 2L, 3.0, 4.0, "5.0", LocalDate.parse("2021-01-01"), LocalTime.parse("12:00"), 1451516155000L, true).build();
RowSetUtilities.verify(expected, results);
// Now drop the table
String dropQuery = "DROP TABLE mysql.`drill_mysql_test`.`data_types`";
QuerySummary dropResults = queryBuilder().sql(dropQuery).run();
assertTrue(dropResults.succeeded());
}
Aggregations