This is an automated email from the ASF dual-hosted git repository. dkuzmenko pushed a commit to branch branch-4.0 in repository https://gitbox.apache.org/repos/asf/hive.git
commit a7d0a18601451836dede4bcc6adece5075403f83 Author: Denys Kuzmenko <[email protected]> AuthorDate: Fri Aug 23 14:01:59 2024 +0200 HIVE-28451: JDBC: TableName matcher fix in GenericJdbcDatabaseAccessor#addBoundaryToQuery (Denys Kuzmenko, reviewed by Simhadri Govindappa) Closes #5396 (cherry picked from commit 407ae674bf3786bc9312d7a5d59cd1db7d0abd0f) --- .../jdbc/dao/GenericJdbcDatabaseAccessor.java | 23 +- .../queries/clientpositive/external_jdbc_view.q | 89 +++++++ .../clientpositive/llap/external_jdbc_view.q.out | 292 +++++++++++++++++++++ 3 files changed, 386 insertions(+), 18 deletions(-) diff --git a/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/dao/GenericJdbcDatabaseAccessor.java b/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/dao/GenericJdbcDatabaseAccessor.java index d6a36f0b729..1b98075e859 100644 --- a/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/dao/GenericJdbcDatabaseAccessor.java +++ b/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/dao/GenericJdbcDatabaseAccessor.java @@ -62,7 +62,7 @@ public class GenericJdbcDatabaseAccessor implements DatabaseAccessor { protected static final int DEFAULT_FETCH_SIZE = 1000; protected static final Logger LOGGER = LoggerFactory.getLogger(GenericJdbcDatabaseAccessor.class); private DataSource dbcpDataSource = null; - static final Pattern fromPattern = Pattern.compile("(.*?\\sfrom\\s)(.*+)", Pattern.CASE_INSENSITIVE|Pattern.DOTALL); + static final Pattern fromPattern = Pattern.compile("(.*?\\sfrom\\s+)([^\\s]+)(.*?)", Pattern.CASE_INSENSITIVE|Pattern.DOTALL); private static ColumnMetadataAccessor<TypeInfo> typeInfoTranslator = (meta, col) -> { JDBCType type = JDBCType.valueOf(meta.getColumnType(col)); @@ -196,7 +196,7 @@ public class GenericJdbcDatabaseAccessor implements DatabaseAccessor { return rs.getInt(1); } else { - LOGGER.warn("The count query did not return any results.", countQuery); + LOGGER.warn("The count query {} did not return any results.", countQuery); throw new HiveJdbcDatabaseAccessException("Count query did not return any results."); } } @@ -361,26 +361,13 @@ public class GenericJdbcDatabaseAccessor implements DatabaseAccessor { String result; if (tableName != null) { // Looking for table name in from clause, replace with the boundary query - // TODO consolidate this - // Currently only use simple string match, this should be improved by looking - // for only table name in from clause - String tableString = null; Matcher m = fromPattern.matcher(sql); Preconditions.checkArgument(m.matches()); - String queryBeforeFrom = m.group(1); - String queryAfterFrom = " " + m.group(2) + " "; - - Character[] possibleDelimits = new Character[] {'`', '\"', ' '}; - for (Character possibleDelimit : possibleDelimits) { - if (queryAfterFrom.contains(possibleDelimit + tableName + possibleDelimit)) { - tableString = possibleDelimit + tableName + possibleDelimit; - break; - } - } - if (tableString == null) { + + if (!tableName.equals(m.group(2).replaceAll("[`\"]", ""))) { throw new RuntimeException("Cannot find " + tableName + " in sql query " + sql); } - result = queryBeforeFrom + queryAfterFrom.replace(tableString, " (" + boundaryQuery + ") " + tableName + " "); + result = String.format("%s (%s) tmptable %s", m.group(1), boundaryQuery, m.group(3)); } else { result = boundaryQuery; } diff --git a/ql/src/test/queries/clientpositive/external_jdbc_view.q b/ql/src/test/queries/clientpositive/external_jdbc_view.q new file mode 100644 index 00000000000..9575cabaac0 --- /dev/null +++ b/ql/src/test/queries/clientpositive/external_jdbc_view.q @@ -0,0 +1,89 @@ +CREATE TEMPORARY FUNCTION dboutput AS 'org.apache.hadoop.hive.contrib.genericudf.example.GenericUDFDBOutput'; + +SELECT +dboutput ( 'jdbc:derby:;databaseName=${system:test.tmp.dir}/test_jdbc_view;create=true','','', +'CREATE TABLE person ("id" INTEGER, "name" VARCHAR(25), "jid" INTEGER, "cid" INTEGER)' ); + +SELECT +dboutput ( 'jdbc:derby:;databaseName=${system:test.tmp.dir}/test_jdbc_view;create=true','','', +'CREATE TABLE country ("id" INTEGER, "name" VARCHAR(25))' ); + +SELECT +dboutput ( 'jdbc:derby:;databaseName=${system:test.tmp.dir}/test_jdbc_view;create=true','','', +'CREATE TABLE job ("id" INTEGER, "title" VARCHAR(20))' ); + + +CREATE EXTERNAL TABLE person +( + id int, + name varchar(25), + jid int, + cid int +) +STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' +TBLPROPERTIES ( + "hive.sql.database.type" = "DERBY", + "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", + "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_jdbc_view;create=true;collation=TERRITORY_BASED:PRIMARY", + "hive.sql.dbcp.username" = "APP", + "hive.sql.dbcp.password" = "mine", + "hive.sql.table" = "PERSON", + "hive.sql.dbcp.maxActive" = "1" +); +INSERT INTO `person` VALUES (1, 'Laszlo', 1, 1); + +CREATE EXTERNAL TABLE country +( + id int, + name varchar(25) +) +STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' +TBLPROPERTIES ( + "hive.sql.database.type" = "DERBY", + "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", + "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_jdbc_view;create=true;collation=TERRITORY_BASED:PRIMARY", + "hive.sql.dbcp.username" = "APP", + "hive.sql.dbcp.password" = "mine", + "hive.sql.table" = "COUNTRY", + "hive.sql.dbcp.maxActive" = "1", + "hive.sql.numPartitions"="2", + "hive.sql.partitionColumn"="id" +); +INSERT INTO `country` VALUES (1, 'Hungary'); +INSERT INTO `country` VALUES (2, 'Poland'); +INSERT INTO `country` VALUES (3, 'Germany'); + +CREATE EXTERNAL TABLE job +( + id int, + title varchar(20) +) +STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' +TBLPROPERTIES ( + "hive.sql.database.type" = "DERBY", + "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", + "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_jdbc_view;create=true;collation=TERRITORY_BASED:PRIMARY", + "hive.sql.dbcp.username" = "APP", + "hive.sql.dbcp.password" = "mine", + "hive.sql.schema" = "APP", + "hive.sql.table" = "JOB", + "hive.sql.dbcp.maxActive" = "1", + "hive.sql.numPartitions"="2", + "hive.sql.partitionColumn"="id" +); +INSERT INTO `job` VALUES (1, 'Software Engineer'); +INSERT INTO `job` VALUES (2, 'Architect'); +INSERT INTO `job` VALUES (3, 'Quality Assurance'); + +CREATE VIEW v1 AS +SELECT id FROM job WHERE title = 'Software Engineer'; + +select * from v1; + +CREATE VIEW v2 AS +SELECT person.* FROM person + JOIN v1 job on person.jid = job.id + JOIN country on person.cid = country.id +WHERE country.name = 'Hungary'; + +select * from v2; diff --git a/ql/src/test/results/clientpositive/llap/external_jdbc_view.q.out b/ql/src/test/results/clientpositive/llap/external_jdbc_view.q.out new file mode 100644 index 00000000000..4094a8a80a9 --- /dev/null +++ b/ql/src/test/results/clientpositive/llap/external_jdbc_view.q.out @@ -0,0 +1,292 @@ +PREHOOK: query: CREATE TEMPORARY FUNCTION dboutput AS 'org.apache.hadoop.hive.contrib.genericudf.example.GenericUDFDBOutput' +PREHOOK: type: CREATEFUNCTION +PREHOOK: Output: dboutput +POSTHOOK: query: CREATE TEMPORARY FUNCTION dboutput AS 'org.apache.hadoop.hive.contrib.genericudf.example.GenericUDFDBOutput' +POSTHOOK: type: CREATEFUNCTION +POSTHOOK: Output: dboutput +PREHOOK: query: SELECT +#### A masked pattern was here #### +'CREATE TABLE person ("id" INTEGER, "name" VARCHAR(25), "jid" INTEGER, "cid" INTEGER)' ) +PREHOOK: type: QUERY +PREHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +POSTHOOK: query: SELECT +#### A masked pattern was here #### +'CREATE TABLE person ("id" INTEGER, "name" VARCHAR(25), "jid" INTEGER, "cid" INTEGER)' ) +POSTHOOK: type: QUERY +POSTHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +0 +PREHOOK: query: SELECT +#### A masked pattern was here #### +'CREATE TABLE country ("id" INTEGER, "name" VARCHAR(25))' ) +PREHOOK: type: QUERY +PREHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +POSTHOOK: query: SELECT +#### A masked pattern was here #### +'CREATE TABLE country ("id" INTEGER, "name" VARCHAR(25))' ) +POSTHOOK: type: QUERY +POSTHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +0 +PREHOOK: query: SELECT +#### A masked pattern was here #### +'CREATE TABLE job ("id" INTEGER, "title" VARCHAR(20))' ) +PREHOOK: type: QUERY +PREHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +POSTHOOK: query: SELECT +#### A masked pattern was here #### +'CREATE TABLE job ("id" INTEGER, "title" VARCHAR(20))' ) +POSTHOOK: type: QUERY +POSTHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +0 +PREHOOK: query: CREATE EXTERNAL TABLE person +( + id int, + name varchar(25), + jid int, + cid int +) +STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' +TBLPROPERTIES ( + "hive.sql.database.type" = "DERBY", + "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", +#### A masked pattern was here #### + "hive.sql.dbcp.username" = "APP", + "hive.sql.dbcp.password" = "mine", + "hive.sql.table" = "PERSON", + "hive.sql.dbcp.maxActive" = "1" +) +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:default +PREHOOK: Output: default@person +POSTHOOK: query: CREATE EXTERNAL TABLE person +( + id int, + name varchar(25), + jid int, + cid int +) +STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' +TBLPROPERTIES ( + "hive.sql.database.type" = "DERBY", + "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", +#### A masked pattern was here #### + "hive.sql.dbcp.username" = "APP", + "hive.sql.dbcp.password" = "mine", + "hive.sql.table" = "PERSON", + "hive.sql.dbcp.maxActive" = "1" +) +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:default +POSTHOOK: Output: default@person +PREHOOK: query: INSERT INTO `person` VALUES (1, 'Laszlo', 1, 1) +PREHOOK: type: QUERY +PREHOOK: Input: _dummy_database@_dummy_table +PREHOOK: Output: default@person +POSTHOOK: query: INSERT INTO `person` VALUES (1, 'Laszlo', 1, 1) +POSTHOOK: type: QUERY +POSTHOOK: Input: _dummy_database@_dummy_table +POSTHOOK: Output: default@person +PREHOOK: query: CREATE EXTERNAL TABLE country +( + id int, + name varchar(25) +) +STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' +TBLPROPERTIES ( + "hive.sql.database.type" = "DERBY", + "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", +#### A masked pattern was here #### + "hive.sql.dbcp.username" = "APP", + "hive.sql.dbcp.password" = "mine", + "hive.sql.table" = "COUNTRY", + "hive.sql.dbcp.maxActive" = "1", + "hive.sql.numPartitions"="2", + "hive.sql.partitionColumn"="id" +) +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:default +PREHOOK: Output: default@country +POSTHOOK: query: CREATE EXTERNAL TABLE country +( + id int, + name varchar(25) +) +STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' +TBLPROPERTIES ( + "hive.sql.database.type" = "DERBY", + "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", +#### A masked pattern was here #### + "hive.sql.dbcp.username" = "APP", + "hive.sql.dbcp.password" = "mine", + "hive.sql.table" = "COUNTRY", + "hive.sql.dbcp.maxActive" = "1", + "hive.sql.numPartitions"="2", + "hive.sql.partitionColumn"="id" +) +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:default +POSTHOOK: Output: default@country +PREHOOK: query: INSERT INTO `country` VALUES (1, 'Hungary') +PREHOOK: type: QUERY +PREHOOK: Input: _dummy_database@_dummy_table +PREHOOK: Output: default@country +POSTHOOK: query: INSERT INTO `country` VALUES (1, 'Hungary') +POSTHOOK: type: QUERY +POSTHOOK: Input: _dummy_database@_dummy_table +POSTHOOK: Output: default@country +PREHOOK: query: INSERT INTO `country` VALUES (2, 'Poland') +PREHOOK: type: QUERY +PREHOOK: Input: _dummy_database@_dummy_table +PREHOOK: Output: default@country +POSTHOOK: query: INSERT INTO `country` VALUES (2, 'Poland') +POSTHOOK: type: QUERY +POSTHOOK: Input: _dummy_database@_dummy_table +POSTHOOK: Output: default@country +PREHOOK: query: INSERT INTO `country` VALUES (3, 'Germany') +PREHOOK: type: QUERY +PREHOOK: Input: _dummy_database@_dummy_table +PREHOOK: Output: default@country +POSTHOOK: query: INSERT INTO `country` VALUES (3, 'Germany') +POSTHOOK: type: QUERY +POSTHOOK: Input: _dummy_database@_dummy_table +POSTHOOK: Output: default@country +PREHOOK: query: CREATE EXTERNAL TABLE job +( + id int, + title varchar(20) +) +STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' +TBLPROPERTIES ( + "hive.sql.database.type" = "DERBY", + "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", +#### A masked pattern was here #### + "hive.sql.dbcp.username" = "APP", + "hive.sql.dbcp.password" = "mine", + "hive.sql.schema" = "APP", + "hive.sql.table" = "JOB", + "hive.sql.dbcp.maxActive" = "1", + "hive.sql.numPartitions"="2", + "hive.sql.partitionColumn"="id" +) +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:default +PREHOOK: Output: default@job +POSTHOOK: query: CREATE EXTERNAL TABLE job +( + id int, + title varchar(20) +) +STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' +TBLPROPERTIES ( + "hive.sql.database.type" = "DERBY", + "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", +#### A masked pattern was here #### + "hive.sql.dbcp.username" = "APP", + "hive.sql.dbcp.password" = "mine", + "hive.sql.schema" = "APP", + "hive.sql.table" = "JOB", + "hive.sql.dbcp.maxActive" = "1", + "hive.sql.numPartitions"="2", + "hive.sql.partitionColumn"="id" +) +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:default +POSTHOOK: Output: default@job +PREHOOK: query: INSERT INTO `job` VALUES (1, 'Software Engineer') +PREHOOK: type: QUERY +PREHOOK: Input: _dummy_database@_dummy_table +PREHOOK: Output: default@job +POSTHOOK: query: INSERT INTO `job` VALUES (1, 'Software Engineer') +POSTHOOK: type: QUERY +POSTHOOK: Input: _dummy_database@_dummy_table +POSTHOOK: Output: default@job +PREHOOK: query: INSERT INTO `job` VALUES (2, 'Architect') +PREHOOK: type: QUERY +PREHOOK: Input: _dummy_database@_dummy_table +PREHOOK: Output: default@job +POSTHOOK: query: INSERT INTO `job` VALUES (2, 'Architect') +POSTHOOK: type: QUERY +POSTHOOK: Input: _dummy_database@_dummy_table +POSTHOOK: Output: default@job +PREHOOK: query: INSERT INTO `job` VALUES (3, 'Quality Assurance') +PREHOOK: type: QUERY +PREHOOK: Input: _dummy_database@_dummy_table +PREHOOK: Output: default@job +POSTHOOK: query: INSERT INTO `job` VALUES (3, 'Quality Assurance') +POSTHOOK: type: QUERY +POSTHOOK: Input: _dummy_database@_dummy_table +POSTHOOK: Output: default@job +PREHOOK: query: CREATE VIEW v1 AS +SELECT id FROM job WHERE title = 'Software Engineer' +PREHOOK: type: CREATEVIEW +PREHOOK: Input: default@job +PREHOOK: Output: database:default +PREHOOK: Output: default@v1 +POSTHOOK: query: CREATE VIEW v1 AS +SELECT id FROM job WHERE title = 'Software Engineer' +POSTHOOK: type: CREATEVIEW +POSTHOOK: Input: default@job +POSTHOOK: Output: database:default +POSTHOOK: Output: default@v1 +POSTHOOK: Lineage: v1.id SIMPLE [(job)job.FieldSchema(name:id, type:int, comment:from deserializer), ] +PREHOOK: query: select * from v1 +PREHOOK: type: QUERY +PREHOOK: Input: default@job +PREHOOK: Input: default@v1 +#### A masked pattern was here #### +POSTHOOK: query: select * from v1 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@job +POSTHOOK: Input: default@v1 +#### A masked pattern was here #### +1 +PREHOOK: query: CREATE VIEW v2 AS +SELECT person.* FROM person + JOIN v1 job on person.jid = job.id + JOIN country on person.cid = country.id +WHERE country.name = 'Hungary' +PREHOOK: type: CREATEVIEW +PREHOOK: Input: default@country +PREHOOK: Input: default@job +PREHOOK: Input: default@person +PREHOOK: Input: default@v1 +PREHOOK: Output: database:default +PREHOOK: Output: default@v2 +POSTHOOK: query: CREATE VIEW v2 AS +SELECT person.* FROM person + JOIN v1 job on person.jid = job.id + JOIN country on person.cid = country.id +WHERE country.name = 'Hungary' +POSTHOOK: type: CREATEVIEW +POSTHOOK: Input: default@country +POSTHOOK: Input: default@job +POSTHOOK: Input: default@person +POSTHOOK: Input: default@v1 +POSTHOOK: Output: database:default +POSTHOOK: Output: default@v2 +POSTHOOK: Lineage: v2.cid SIMPLE [(person)person.FieldSchema(name:cid, type:int, comment:from deserializer), ] +POSTHOOK: Lineage: v2.id SIMPLE [(person)person.FieldSchema(name:id, type:int, comment:from deserializer), ] +POSTHOOK: Lineage: v2.jid SIMPLE [(person)person.FieldSchema(name:jid, type:int, comment:from deserializer), ] +POSTHOOK: Lineage: v2.name SIMPLE [(person)person.FieldSchema(name:name, type:varchar(25), comment:from deserializer), ] +PREHOOK: query: select * from v2 +PREHOOK: type: QUERY +PREHOOK: Input: default@country +PREHOOK: Input: default@job +PREHOOK: Input: default@person +PREHOOK: Input: default@v1 +PREHOOK: Input: default@v2 +#### A masked pattern was here #### +POSTHOOK: query: select * from v2 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@country +POSTHOOK: Input: default@job +POSTHOOK: Input: default@person +POSTHOOK: Input: default@v1 +POSTHOOK: Input: default@v2 +#### A masked pattern was here #### +1 Laszlo 1 1
