| /* |
| * 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.exec.store.jdbc; |
| |
| import org.apache.drill.common.exceptions.UserRemoteException; |
| import org.apache.drill.common.logical.security.PlainCredentialsProvider; |
| import org.apache.drill.common.logical.StoragePluginConfig.AuthMode; |
| import org.apache.drill.common.types.TypeProtos.DataMode; |
| import org.apache.drill.common.types.TypeProtos.MinorType; |
| import org.apache.drill.exec.physical.rowSet.DirectRowSet; |
| import org.apache.drill.exec.physical.rowSet.RowSet; |
| import org.apache.drill.exec.physical.rowSet.RowSetBuilder; |
| import org.apache.drill.exec.record.metadata.SchemaBuilder; |
| import org.apache.drill.exec.record.metadata.TupleMetadata; |
| import org.apache.drill.exec.store.enumerable.plan.EnumMockPlugin; |
| import org.apache.drill.test.ClusterFixture; |
| import org.apache.drill.test.ClusterTest; |
| import org.apache.drill.test.QueryBuilder.QuerySummary; |
| import org.apache.drill.test.rowSet.RowSetUtilities; |
| import org.apache.hadoop.fs.Path; |
| import org.h2.tools.RunScript; |
| import org.hamcrest.CoreMatchers; |
| import org.hamcrest.MatcherAssert; |
| import org.junit.BeforeClass; |
| import org.junit.Ignore; |
| import org.junit.Test; |
| |
| |
| import java.io.FileReader; |
| import java.io.IOException; |
| import java.net.URL; |
| import java.nio.file.Paths; |
| import java.sql.Connection; |
| import java.sql.DriverManager; |
| import java.time.LocalDate; |
| import java.time.LocalTime; |
| import java.util.HashMap; |
| import java.util.Map; |
| import java.util.TimeZone; |
| |
| import static org.junit.Assert.assertEquals; |
| import static org.junit.Assert.assertNotNull; |
| import static org.junit.Assert.assertTrue; |
| import static org.junit.Assert.fail; |
| |
| public class TestJdbcWriterWithH2 extends ClusterTest { |
| |
| public static final String TEST_TABLE = "h2.tmp.drill_h2_test.test_table"; |
| |
| public static final String DROP_TEST_TABLE = String.format("DROP TABLE %s", TEST_TABLE); |
| |
| @BeforeClass |
| public static void init() throws Exception { |
| startCluster(ClusterFixture.builder(dirTestWatcher)); |
| // Force timezone to UTC for these tests. |
| TimeZone.setDefault(TimeZone.getTimeZone("UTC")); |
| dirTestWatcher.copyResourceToRoot(Paths.get("")); |
| |
| Class.forName("org.h2.Driver"); |
| String connString = "jdbc:h2:" + dirTestWatcher.getTmpDir().getCanonicalPath(); |
| URL scriptFile = TestJdbcPluginWithH2IT.class.getClassLoader().getResource("h2-test-data.sql"); |
| assertNotNull("Script for test tables generation 'h2-test-data.sql' cannot be found in test resources", scriptFile); |
| try (Connection connection = DriverManager.getConnection(connString, "root", "root"); |
| FileReader fileReader = new FileReader(scriptFile.getFile())) { |
| RunScript.execute(connection, fileReader); |
| } |
| |
| Map<String, String> credentials = new HashMap<>(); |
| credentials.put("username", "root"); |
| credentials.put("password", "root"); |
| PlainCredentialsProvider credentialsProvider = new PlainCredentialsProvider(credentials); |
| |
| Map<String, Object> sourceParameters = new HashMap<>(); |
| sourceParameters.put("minimumIdle", 1); |
| JdbcStorageConfig jdbcStorageConfig = new JdbcStorageConfig("org.h2.Driver", connString, |
| "root", "root", true, true, sourceParameters, credentialsProvider, AuthMode.SHARED_USER.name(), 10000); |
| jdbcStorageConfig.setEnabled(true); |
| |
| JdbcStorageConfig jdbcStorageConfigNoWrite = new JdbcStorageConfig("org.h2.Driver", connString, |
| "root", "root", true, false, sourceParameters, credentialsProvider, AuthMode.SHARED_USER.name(), 10000); |
| jdbcStorageConfig.setEnabled(true); |
| jdbcStorageConfigNoWrite.setEnabled(true); |
| |
| cluster.defineStoragePlugin("h2", jdbcStorageConfig); |
| cluster.defineStoragePlugin("h2_unwritable", jdbcStorageConfigNoWrite); |
| |
| EnumMockPlugin.EnumMockStoragePluginConfig config = new EnumMockPlugin.EnumMockStoragePluginConfig(); |
| config.setEnabled(true); |
| cluster.defineStoragePlugin("mocked_enum", config); |
| } |
| |
| @Test |
| public void testBasicCTAS() 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(); |
| |
| try { |
| assertTrue(insertResults.succeeded()); |
| |
| // Query the table to see if the insertion was successful |
| String testQuery = String.format("SELECT * FROM %s", TEST_TABLE); |
| DirectRowSet results = queryBuilder().sql(testQuery).rowSet(); |
| |
| TupleMetadata expectedSchema = new SchemaBuilder() |
| .add("ID", MinorType.INT, DataMode.OPTIONAL) |
| .add("NAME", MinorType.INT, DataMode.OPTIONAL) |
| .buildSchema(); |
| |
| RowSet expected = new RowSetBuilder(client.allocator(), expectedSchema) |
| .addRow(1, 2) |
| .addRow(3, 4) |
| .build(); |
| |
| RowSetUtilities.verify(expected, results); |
| } finally { |
| QuerySummary dropResults = queryBuilder().sql(DROP_TEST_TABLE).run(); |
| assertTrue(dropResults.succeeded()); |
| } |
| } |
| |
| @Test |
| public void testBasicCTASWithDataTypes() throws Exception { |
| String query = String.format("CREATE TABLE %s AS ", TEST_TABLE) + |
| "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()); |
| |
| try { |
| // Query the table to see if the insertion was successful |
| String testQuery = String.format("SELECT * FROM %s", TEST_TABLE); |
| DirectRowSet results = queryBuilder().sql(testQuery).rowSet(); |
| |
| TupleMetadata expectedSchema = new SchemaBuilder() |
| .addNullable("int_field", MinorType.INT, 32) |
| .addNullable("bigint_field", MinorType.BIGINT, 38) |
| .addNullable("float4_field", MinorType.FLOAT4, 38) |
| .addNullable("float8_field", MinorType.FLOAT8, 38) |
| .addNullable("varchar_field", MinorType.VARCHAR, 3) |
| .addNullable("date_field", MinorType.DATE, 10) |
| .addNullable("time_field", MinorType.TIME, 12, 3) |
| .addNullable("timestamp_field", MinorType.TIMESTAMP, 23, 3) |
| .addNullable("boolean_field", MinorType.BIT, 1) |
| .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"), 1451516155230L, true) |
| .build(); |
| |
| RowSetUtilities.verify(expected, results); |
| } finally { |
| QuerySummary dropResults = queryBuilder().sql(DROP_TEST_TABLE).run(); |
| assertTrue(dropResults.succeeded()); |
| } |
| } |
| |
| @Test |
| public void testBasicCTASWithSpacesInFieldNames() throws Exception { |
| String query = String.format("CREATE TABLE %s (`My id`, `My 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 { |
| // Query the table to see if the insertion was successful |
| String testQuery = String.format("SELECT * FROM %s", TEST_TABLE); |
| DirectRowSet results = queryBuilder().sql(testQuery).rowSet(); |
| |
| TupleMetadata expectedSchema = new SchemaBuilder() |
| .add("My id", MinorType.INT, DataMode.OPTIONAL) |
| .add("My name", MinorType.INT, DataMode.OPTIONAL) |
| .buildSchema(); |
| |
| RowSet expected = new RowSetBuilder(client.allocator(), expectedSchema) |
| .addRow(1, 2) |
| .addRow(3, 4) |
| .build(); |
| |
| RowSetUtilities.verify(expected, results); |
| } finally { |
| QuerySummary dropResults = queryBuilder().sql(DROP_TEST_TABLE).run(); |
| assertTrue(dropResults.succeeded()); |
| } |
| } |
| |
| @Test |
| public void testCTASFromFileWithNulls() throws Exception { |
| String sql = String.format("CREATE TABLE %s AS SELECT int_field, float_field, varchar_field, boolean_field FROM cp.`json/dataTypes.json`", TEST_TABLE); |
| QuerySummary insertResults = queryBuilder().sql(sql).run(); |
| assertTrue(insertResults.succeeded()); |
| |
| try { |
| sql = String.format("SELECT * FROM %s", TEST_TABLE); |
| DirectRowSet results = queryBuilder().sql(sql).rowSet(); |
| |
| TupleMetadata expectedSchema = new SchemaBuilder() |
| .addNullable("int_field", MinorType.BIGINT, 38) |
| .addNullable("float_field", MinorType.FLOAT8, 38) |
| .addNullable("varchar_field", MinorType.VARCHAR, 38) |
| .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); |
| } finally { |
| QuerySummary dropResults = queryBuilder().sql(DROP_TEST_TABLE).run(); |
| assertTrue(dropResults.succeeded()); |
| } |
| } |
| |
| @Test |
| public void testDropNonExistentTable() throws Exception { |
| String dropQuery = "DROP TABLE h2.tmp.`drill_h2_test`.`none_shall_pass`"; |
| try { |
| queryBuilder().sql(dropQuery).run(); |
| fail(); |
| } catch (UserRemoteException e) { |
| assertTrue(e.getMessage().contains("VALIDATION ERROR: Table [none_shall_pass] not found")); |
| } |
| } |
| |
| @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.INT, DataMode.OPTIONAL) |
| .add("NAME", MinorType.INT, DataMode.OPTIONAL) |
| .buildSchema(); |
| |
| RowSet expected = new RowSetBuilder(client.allocator(), expectedSchema) |
| .addRow(1, 2) |
| .addRow(3, 4) |
| .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()); |
| } |
| } |
| |
| @Test |
| public void testBasicCTASIfNotExists() throws Exception { |
| String query = String.format("CREATE TABLE IF NOT EXISTS %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 { |
| // Query the table to see if the insertion was successful |
| String testQuery = String.format("SELECT * FROM %s", TEST_TABLE); |
| DirectRowSet results = queryBuilder().sql(testQuery).rowSet(); |
| |
| TupleMetadata expectedSchema = new SchemaBuilder() |
| .add("ID", MinorType.INT, DataMode.OPTIONAL) |
| .add("NAME", MinorType.INT, DataMode.OPTIONAL) |
| .buildSchema(); |
| |
| RowSet expected = new RowSetBuilder(client.allocator(), expectedSchema) |
| .addRow(1, 2) |
| .addRow(3, 4) |
| .build(); |
| |
| RowSetUtilities.verify(expected, results); |
| } finally { |
| QuerySummary dropResults = queryBuilder().sql(DROP_TEST_TABLE).run(); |
| assertTrue(dropResults.succeeded()); |
| } |
| } |
| |
| @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()); |
| } |
| } |
| |
| @Test |
| public void testWithComplexData() throws Exception { |
| // JDBC Writer does not support writing complex types at this time. |
| try { |
| String sql = String.format("CREATE TABLE %s AS SELECT * FROM cp.`json/complexData.json`", TEST_TABLE); |
| queryBuilder().sql(sql).run(); |
| fail(); |
| } catch (UserRemoteException e) { |
| assertTrue(e.getMessage().contains("DATA_WRITE ERROR: Drill does not support writing complex fields to JDBC data sources.")); |
| } |
| } |
| |
| @Test |
| public void testCTASFromFileWithUglyData() throws Exception { |
| String sql = String.format("CREATE TABLE %s AS SELECT ugly1, ugly2 FROM cp.`json/uglyData.json`", TEST_TABLE); |
| QuerySummary insertResults = queryBuilder().sql(sql).run(); |
| assertTrue(insertResults.succeeded()); |
| |
| try { |
| sql = String.format("SELECT * FROM %s", TEST_TABLE); |
| 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); |
| } finally { |
| QuerySummary dropResults = queryBuilder().sql(DROP_TEST_TABLE).run(); |
| assertTrue(dropResults.succeeded()); |
| } |
| } |
| |
| @Test |
| public void testWithArrayField() throws Exception { |
| // JDBC Writer does not support writing arrays at this time. |
| try { |
| String sql = String.format("CREATE TABLE %s AS SELECT * FROM cp.`json/repeatedData.json`", TEST_TABLE); |
| queryBuilder().sql(sql).run(); |
| fail(); |
| } catch (UserRemoteException e) { |
| MatcherAssert.assertThat(e.getMessage(), |
| CoreMatchers.containsString("DATA_WRITE ERROR: Drill does not yet support writing arrays to JDBC. repeated_field is an array.")); |
| } |
| } |
| |
| @Test |
| public void testWithLargeFile() throws Exception { |
| String query = String.format("CREATE TABLE %s (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`", TEST_TABLE); |
| QuerySummary insertResults = queryBuilder().sql(query).run(); |
| assertTrue(insertResults.succeeded()); |
| |
| try { |
| query = String.format("SELECT COUNT(*) FROM %s", TEST_TABLE); |
| long rowCount = queryBuilder().sql(query).singletonLong(); |
| assertEquals(6000, rowCount); |
| } finally { |
| QuerySummary dropResults = queryBuilder().sql(DROP_TEST_TABLE).run(); |
| assertTrue(dropResults.succeeded()); |
| } |
| } |
| |
| @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) { |
| 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 = String.format("CREATE TABLE %s AS ", TEST_TABLE) + |
| "SELECT * FROM dfs.`csv/very_large_file.csvh`"; |
| QuerySummary insertResults = queryBuilder().sql(ctasQuery).run(); |
| assertTrue(insertResults.succeeded()); |
| |
| try { |
| // Query the table to see if the insertion was successful |
| testQuery = String.format("SELECT COUNT(*) FROM %s", TEST_TABLE); |
| resultsCount = queryBuilder().sql(testQuery).singletonLong(); |
| assertEquals(100000, resultsCount); |
| } finally { |
| QuerySummary dropResults = queryBuilder().sql(DROP_TEST_TABLE).run(); |
| assertTrue(dropResults.succeeded()); |
| |
| boolean deletedFile = JdbcTestUtils.deleteCsvFile(String.valueOf(generatedFile)); |
| if (!deletedFile) { |
| fail(); |
| } |
| } |
| } |
| |
| @Test |
| public void testUnwritableConnection() throws Exception { |
| try { |
| String query = "CREATE TABLE IF NOT EXISTS h2_unwritable.tmp.`test_table` (ID, NAME) AS SELECT * FROM (VALUES(1,2), (3,4))"; |
| queryBuilder().sql(query).run(); |
| fail(); |
| } catch (UserRemoteException e) { |
| assertTrue(e.getMessage().contains("VALIDATION ERROR: Unable to create or drop objects. Schema [h2_unwritable.tmp] is immutable.")); |
| } |
| |
| try { |
| String query = "CREATE TABLE h2_unwritable.tmp.`test_table` (ID, NAME) AS SELECT * FROM (VALUES(1,2), (3,4))"; |
| queryBuilder().sql(query).run(); |
| fail(); |
| } catch (UserRemoteException e) { |
| assertTrue(e.getMessage().contains("VALIDATION ERROR: Unable to create or drop objects. Schema [h2_unwritable.tmp] is immutable.")); |
| } |
| } |
| } |
| |
| |
| |