You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by dk...@apache.org on 2024/09/23 11:46:02 UTC
(hive) 01/02: HIVE-28451: JDBC: TableName matcher fix in GenericJdbcDatabaseAccessor#addBoundaryToQuery (Denys Kuzmenko, reviewed by Simhadri Govindappa)
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 <dk...@apache.org>
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