This is an automated email from the ASF dual-hosted git repository. stigahuang pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/impala.git
commit c0507c02cd0d81c4133820b96d45002048257c00 Author: wzhou-code <[email protected]> AuthorDate: Mon Mar 18 00:01:18 2024 -0700 IMPALA-12896 (Part 2): JDBC table must be created as external table In some of the deployment environments, default table type is transactional. In these scenarios, JDBC tables which are created as non external table are not accepted by HMS due to strict managed table check failures. This patch forces JDBC tables to be created as external table, and requires at least 1 column for JDBC tables. Testing: - Updated frontend unit tests and end-to-end unit tests to create JDBC tables as external tables. - Passed core tests Change-Id: Ib5533b52434cdf1c430e30ac28a0146ab4d9d4b9 Reviewed-on: http://gerrit.cloudera.org:8080/21159 Reviewed-by: Joe McDonnell <[email protected]> Tested-by: Impala Public Jenkins <[email protected]> --- .../apache/impala/analysis/CreateTableStmt.java | 5 ++++- .../org/apache/impala/analysis/AnalyzeDDLTest.java | 23 +++++++++++++++++----- .../apache/impala/customcluster/LdapHS2Test.java | 7 ++++--- testdata/bin/create-ext-data-source-table.sql | 4 ++-- .../impala-ext-jdbc-tables-predicates.test | 2 +- .../queries/QueryTest/impala-ext-jdbc-tables.test | 4 ++-- .../QueryTest/jdbc-data-source-with-keystore.test | 4 ++-- .../queries/QueryTest/jdbc-data-source.test | 6 +++--- .../queries/QueryTest/mysql-ext-jdbc-tables.test | 4 ++-- 9 files changed, 38 insertions(+), 21 deletions(-) diff --git a/fe/src/main/java/org/apache/impala/analysis/CreateTableStmt.java b/fe/src/main/java/org/apache/impala/analysis/CreateTableStmt.java index 6d74a669d..8899bbd8f 100644 --- a/fe/src/main/java/org/apache/impala/analysis/CreateTableStmt.java +++ b/fe/src/main/java/org/apache/impala/analysis/CreateTableStmt.java @@ -269,7 +269,7 @@ public class CreateTableStmt extends StatementBase { // schema. Likewise for external Kudu tables, the schema can be read from Kudu. if (getColumnDefs().isEmpty() && getFileFormat() != THdfsFileFormat.AVRO && getFileFormat() != THdfsFileFormat.KUDU && getFileFormat() != - THdfsFileFormat.ICEBERG && getFileFormat() != THdfsFileFormat.JDBC) { + THdfsFileFormat.ICEBERG) { throw new AnalysisException("Table requires at least 1 column"); } if (getRowFormat() != null) { @@ -896,6 +896,9 @@ public class CreateTableStmt extends StatementBase { * HMS. */ private void analyzeJdbcSchema(Analyzer analyzer) throws AnalysisException { + if (!isExternal()) { + throw new AnalysisException("JDBC table must be created as external table"); + } for (ColumnDef col: getColumnDefs()) { if (!DataSourceTable.isSupportedColumnType(col.getType())) { throw new AnalysisException("Tables stored by JDBC do not support the column " + diff --git a/fe/src/test/java/org/apache/impala/analysis/AnalyzeDDLTest.java b/fe/src/test/java/org/apache/impala/analysis/AnalyzeDDLTest.java index 02a3aebdb..a1ea8cd9a 100644 --- a/fe/src/test/java/org/apache/impala/analysis/AnalyzeDDLTest.java +++ b/fe/src/test/java/org/apache/impala/analysis/AnalyzeDDLTest.java @@ -2966,18 +2966,31 @@ public class AnalyzeDDLTest extends FrontendTestBase { "' '", "URI path cannot be empty."); // Create JDBC tables - AnalyzesOk("CREATE TABLE Foo (i int) STORED BY JDBC " + + AnalyzesOk("CREATE EXTERNAL TABLE Foo (i int) STORED BY JDBC " + "TBLPROPERTIES ('database.type'='a', 'jdbc.url'='b', " + "'jdbc.driver'='c', 'driver.url'='d', 'dbcp.username'='e', " + "'dbcp.password'='f', 'table'='g')"); - AnalysisError("CREATE TABLE Foo (i int) STORED BY JDBC TBLPROPERTIES ('a'='b')", + AnalysisError("CREATE TABLE Foo (i int) STORED BY JDBC " + + "TBLPROPERTIES ('database.type'='a', 'jdbc.url'='b', " + + "'jdbc.driver'='c', 'driver.url'='d', 'dbcp.username'='e', " + + "'dbcp.password'='f', 'table'='g')", + "JDBC table must be created as external table"); + AnalysisError("CREATE EXTERNAL TABLE Foo STORED BY JDBC " + + "TBLPROPERTIES ('database.type'='a', 'jdbc.url'='b', " + + "'jdbc.driver'='c', 'driver.url'='d', 'dbcp.username'='e', " + + "'dbcp.password'='f', 'table'='g')", + "Table requires at least 1 column"); + AnalysisError("CREATE EXTERNAL TABLE Foo (i int) STORED BY JDBC " + + "TBLPROPERTIES ('a'='b')", "Cannot create table 'Foo': Required JDBC config 'database.type' is not " + "present in table properties."); - AnalysisError("CREATE TABLE Foo (i int) STORED BY JDBC CACHED IN 'testPool'", + AnalysisError("CREATE EXTERNAL TABLE Foo (i int) STORED BY JDBC " + + "CACHED IN 'testPool'", "A JDBC table cannot be cached in HDFS."); - AnalysisError("CREATE TABLE Foo (i int) STORED BY JDBC LOCATION " + + AnalysisError("CREATE EXTERNAL TABLE Foo (i int) STORED BY JDBC LOCATION " + "'/test-warehouse/new_table'", "LOCATION cannot be specified for a JDBC table."); - AnalysisError("CREATE TABLE Foo (i int) PARTITIONED BY (d decimal) STORED BY JDBC ", + AnalysisError("CREATE EXTERNAL TABLE Foo (i int) PARTITIONED BY (d decimal) " + + "STORED BY JDBC ", "PARTITIONED BY cannot be used in a JDBC table."); // Create table PRODUCED BY DATA SOURCE diff --git a/fe/src/test/java/org/apache/impala/customcluster/LdapHS2Test.java b/fe/src/test/java/org/apache/impala/customcluster/LdapHS2Test.java index 3385d7c19..5831d01df 100644 --- a/fe/src/test/java/org/apache/impala/customcluster/LdapHS2Test.java +++ b/fe/src/test/java/org/apache/impala/customcluster/LdapHS2Test.java @@ -741,7 +741,8 @@ public class LdapHS2Test { String dropTableQuery = "DROP TABLE IF EXISTS %s"; // Set JDBC authentication mechanisms as LDAP (3) with username/password as // TEST_USER_1/TEST_PASSWORD_1. - String createTableQuery = String.format("CREATE TABLE impala_jdbc_ext_test_table (" + + String createTableQuery = + String.format("CREATE EXTERNAL TABLE impala_jdbc_ext_test_table (" + "id INT, bool_col BOOLEAN, tinyint_col TINYINT, smallint_col SMALLINT, " + "int_col INT, bigint_col BIGINT, float_col FLOAT, double_col DOUBLE, " + "date_string_col STRING, string_col STRING, timestamp_col TIMESTAMP) " + @@ -758,7 +759,7 @@ public class LdapHS2Test { internalListenHost, fileSystemPrefix, TEST_USER_1, TEST_PASSWORD_1); // Set JDBC authentication mechanisms as LDAP with wrong password. String createTableWithWrongPassword = - String.format("CREATE TABLE impala_jdbc_tbl_wrong_password (" + + String.format("CREATE EXTERNAL TABLE impala_jdbc_tbl_wrong_password (" + "id INT, bool_col BOOLEAN, tinyint_col TINYINT, smallint_col SMALLINT, " + "int_col INT, bigint_col BIGINT, float_col FLOAT, double_col DOUBLE, " + "date_string_col STRING, string_col STRING, timestamp_col TIMESTAMP) " + @@ -775,7 +776,7 @@ public class LdapHS2Test { internalListenHost, fileSystemPrefix, TEST_USER_1); // Set JDBC authentication mechanisms as LDAP without AuthMech. String createTableWithoutAuthMech = - String.format("CREATE TABLE impala_jdbc_tbl_without_auth_mech (" + + String.format("CREATE EXTERNAL TABLE impala_jdbc_tbl_without_auth_mech (" + "id INT, bool_col BOOLEAN, tinyint_col TINYINT, smallint_col SMALLINT, " + "int_col INT, bigint_col BIGINT, float_col FLOAT, double_col DOUBLE, " + "date_string_col STRING, string_col STRING, timestamp_col TIMESTAMP) " + diff --git a/testdata/bin/create-ext-data-source-table.sql b/testdata/bin/create-ext-data-source-table.sql index 30930142b..3a2134373 100644 --- a/testdata/bin/create-ext-data-source-table.sql +++ b/testdata/bin/create-ext-data-source-table.sql @@ -47,7 +47,7 @@ CREATE TABLE alltypes_datasource ( PRODUCED BY DATA SOURCE AllTypesDataSource("TestInitString"); DROP TABLE IF EXISTS alltypes_jdbc_datasource; -CREATE TABLE alltypes_jdbc_datasource ( +CREATE EXTERNAL TABLE alltypes_jdbc_datasource ( id INT, bool_col BOOLEAN, tinyint_col TINYINT, @@ -70,7 +70,7 @@ TBLPROPERTIES ( "table"="alltypes"); DROP TABLE IF EXISTS alltypes_jdbc_datasource_2; -CREATE TABLE alltypes_jdbc_datasource_2 ( +CREATE EXTERNAL TABLE alltypes_jdbc_datasource_2 ( id INT, bool_col BOOLEAN, tinyint_col TINYINT, diff --git a/testdata/workloads/functional-query/queries/QueryTest/impala-ext-jdbc-tables-predicates.test b/testdata/workloads/functional-query/queries/QueryTest/impala-ext-jdbc-tables-predicates.test index 69267209b..edd42c2a2 100644 --- a/testdata/workloads/functional-query/queries/QueryTest/impala-ext-jdbc-tables-predicates.test +++ b/testdata/workloads/functional-query/queries/QueryTest/impala-ext-jdbc-tables-predicates.test @@ -2,7 +2,7 @@ ---- QUERY # Create external JDBC DataSource table DROP TABLE IF EXISTS alltypes_jdbc_datasource; -CREATE TABLE alltypes_jdbc_datasource ( +CREATE EXTERNAL TABLE alltypes_jdbc_datasource ( id INT, bool_col BOOLEAN, tinyint_col TINYINT, diff --git a/testdata/workloads/functional-query/queries/QueryTest/impala-ext-jdbc-tables.test b/testdata/workloads/functional-query/queries/QueryTest/impala-ext-jdbc-tables.test index 526423296..3715d8071 100644 --- a/testdata/workloads/functional-query/queries/QueryTest/impala-ext-jdbc-tables.test +++ b/testdata/workloads/functional-query/queries/QueryTest/impala-ext-jdbc-tables.test @@ -2,7 +2,7 @@ ---- QUERY # Create external JDBC DataSource table DROP TABLE IF EXISTS alltypes_jdbc_datasource; -CREATE TABLE alltypes_jdbc_datasource ( +CREATE EXTERNAL TABLE alltypes_jdbc_datasource ( id INT, bool_col BOOLEAN, tinyint_col TINYINT, @@ -31,7 +31,7 @@ TBLPROPERTIES ( ---- QUERY # Create external JDBC DataSource table DROP TABLE IF EXISTS alltypes_jdbc_datasource_2; -CREATE TABLE alltypes_jdbc_datasource_2 ( +CREATE EXTERNAL TABLE alltypes_jdbc_datasource_2 ( id INT, bool_col BOOLEAN, tinyint_col TINYINT, diff --git a/testdata/workloads/functional-query/queries/QueryTest/jdbc-data-source-with-keystore.test b/testdata/workloads/functional-query/queries/QueryTest/jdbc-data-source-with-keystore.test index b3f8d0a26..d8896ca1d 100644 --- a/testdata/workloads/functional-query/queries/QueryTest/jdbc-data-source-with-keystore.test +++ b/testdata/workloads/functional-query/queries/QueryTest/jdbc-data-source-with-keystore.test @@ -2,7 +2,7 @@ ---- QUERY # Create external JDBC DataSource table with username, key and keystore DROP TABLE IF EXISTS alltypes_jdbc_datasource_keystore; -CREATE TABLE alltypes_jdbc_datasource_keystore ( +CREATE EXTERNAL TABLE alltypes_jdbc_datasource_keystore ( id INT, bool_col BOOLEAN, tinyint_col TINYINT, @@ -54,7 +54,7 @@ DROP TABLE alltypes_jdbc_datasource_keystore; ---- QUERY # Create external JDBC DataSource table with username and keystore DROP TABLE IF EXISTS alltypes_jdbc_datasource_keystore; -CREATE TABLE alltypes_jdbc_datasource_keystore ( +CREATE EXTERNAL TABLE alltypes_jdbc_datasource_keystore ( id INT, bool_col BOOLEAN, tinyint_col TINYINT, diff --git a/testdata/workloads/functional-query/queries/QueryTest/jdbc-data-source.test b/testdata/workloads/functional-query/queries/QueryTest/jdbc-data-source.test index 8f74016e9..9d6486168 100644 --- a/testdata/workloads/functional-query/queries/QueryTest/jdbc-data-source.test +++ b/testdata/workloads/functional-query/queries/QueryTest/jdbc-data-source.test @@ -2,7 +2,7 @@ ---- QUERY # Create external JDBC DataSource table DROP TABLE IF EXISTS alltypes_jdbc_datasource; -CREATE TABLE alltypes_jdbc_datasource ( +CREATE EXTERNAL TABLE alltypes_jdbc_datasource ( id INT, bool_col BOOLEAN, tinyint_col TINYINT, @@ -30,7 +30,7 @@ TBLPROPERTIES ( ---- QUERY # Create external JDBC DataSource table DROP TABLE IF EXISTS alltypes_jdbc_datasource_2; -CREATE TABLE alltypes_jdbc_datasource_2 ( +CREATE EXTERNAL TABLE alltypes_jdbc_datasource_2 ( id INT, bool_col BOOLEAN, tinyint_col TINYINT, @@ -436,7 +436,7 @@ DROP TABLE alltypes_jdbc_datasource_2; ---- QUERY # Create JDBC table with unknown database type. DROP TABLE IF EXISTS jdbc_test; -CREATE TABLE jdbc_test ( +CREATE EXTERNAL TABLE jdbc_test ( id INT, bool_col BOOLEAN, int_col INT) diff --git a/testdata/workloads/functional-query/queries/QueryTest/mysql-ext-jdbc-tables.test b/testdata/workloads/functional-query/queries/QueryTest/mysql-ext-jdbc-tables.test index 273f83b68..909c394a3 100644 --- a/testdata/workloads/functional-query/queries/QueryTest/mysql-ext-jdbc-tables.test +++ b/testdata/workloads/functional-query/queries/QueryTest/mysql-ext-jdbc-tables.test @@ -2,7 +2,7 @@ ---- QUERY # Create external JDBC DataSource table DROP TABLE IF EXISTS alltypes_jdbc_datasource; -CREATE TABLE alltypes_jdbc_datasource ( +CREATE EXTERNAL TABLE alltypes_jdbc_datasource ( id INT, bool_col BOOLEAN, tinyint_col TINYINT, @@ -30,7 +30,7 @@ TBLPROPERTIES ( ---- QUERY # Create external JDBC DataSource table DROP TABLE IF EXISTS alltypes_jdbc_datasource_2; -CREATE TABLE alltypes_jdbc_datasource_2 ( +CREATE EXTERNAL TABLE alltypes_jdbc_datasource_2 ( id INT, bool_col BOOLEAN, tinyint_col TINYINT,
