This is an automated email from the ASF dual-hosted git repository.
dkuzmenko pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/hive.git
The following commit(s) were added to refs/heads/master by this push:
new 407ae674bf3 HIVE-28451: JDBC: TableName matcher fix in
GenericJdbcDatabaseAccessor#addBoundaryToQuery (Denys Kuzmenko, reviewed by
Simhadri Govindappa)
407ae674bf3 is described below
commit 407ae674bf3786bc9312d7a5d59cd1db7d0abd0f
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
---
.../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