You are viewing a plain text version of this content. The canonical link for it is here.
Posted to gitbox@hive.apache.org by GitBox <gi...@apache.org> on 2021/10/28 16:58:13 UTC
[GitHub] [hive] zabetak opened a new pull request #2759: HIVE-25591: CREATE EXTERNAL TABLE fails for JDBC tables stored in non-default schema
zabetak opened a new pull request #2759:
URL: https://github.com/apache/hive/pull/2759
The tests rely on HIVE-25594 for which there is a separate pull request (https://github.com/apache/hive/pull/2742). Please do not review https://github.com/apache/hive/commit/cb3026b4db9454c12d5376c71a28eb34b35d783d here. If there are remarks please comment on https://github.com/apache/hive/pull/2742 instead.
### What changes were proposed in this pull request?
1. Remove getOriQueryToExecute method in favor of getQueryToExecute
2. Move getQueryToExecute method into GenericJdbcDatabaseAccessor to improve encapsulation since the method is only used in this class.
3. Include hive.sql.schema if available when generating the SQL query.
4. Add tests/usage samples of hive.sql.schema property in different DBMS.
### Why are the changes needed?
1. Avoid failures when the table is in non-default schema.
2. Demonstrate how hive.sql.schema can be used in different DBMS.
3. Minor encapsulation improvement.
### Does this PR introduce _any_ user-facing change?
Fixes a failure.
### How was this patch tested?
`mvn test -Dtest=TestMiniLlapLocalCliDriver -Dqfile_regex="jdbc_table_with_schema.*" -Dtest.output.overwrite`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
[GitHub] [hive] zabetak closed pull request #2759: HIVE-25591: CREATE EXTERNAL TABLE fails for JDBC tables stored in non-default schema
Posted by GitBox <gi...@apache.org>.
zabetak closed pull request #2759:
URL: https://github.com/apache/hive/pull/2759
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
[GitHub] [hive] zabetak closed pull request #2759: HIVE-25591: CREATE EXTERNAL TABLE fails for JDBC tables stored in non-default schema
Posted by GitBox <gi...@apache.org>.
zabetak closed pull request #2759:
URL: https://github.com/apache/hive/pull/2759
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
[GitHub] [hive] zabetak commented on pull request #2759: HIVE-25591: CREATE EXTERNAL TABLE fails for JDBC tables stored in non-default schema
Posted by GitBox <gi...@apache.org>.
zabetak commented on pull request #2759:
URL: https://github.com/apache/hive/pull/2759#issuecomment-958326467
Hey @kasakrisz , thanks a lot for the review! Can you please give another look on https://github.com/apache/hive/pull/2759/commits/e1c78c6b89785a34553c68b1a7eb872ccaa3dc7f? I changed slightly the approach to fix some failures and cover `INSERT` statements as well.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
[GitHub] [hive] zabetak commented on pull request #2759: HIVE-25591: CREATE EXTERNAL TABLE fails for JDBC tables stored in non-default schema
Posted by GitBox <gi...@apache.org>.
zabetak commented on pull request #2759:
URL: https://github.com/apache/hive/pull/2759#issuecomment-954030659
Hey @cravani please have a look as well and let me know what you think.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
[GitHub] [hive] kasakrisz commented on a change in pull request #2759: HIVE-25591: CREATE EXTERNAL TABLE fails for JDBC tables stored in non-default schema
Posted by GitBox <gi...@apache.org>.
kasakrisz commented on a change in pull request #2759:
URL: https://github.com/apache/hive/pull/2759#discussion_r741675549
##########
File path: ql/src/test/queries/clientpositive/jdbc_table_with_schema_mssql.q
##########
@@ -0,0 +1,54 @@
+--! qt:database:mssql:q_test_country_table_with_schema.mssql.sql
+-- Microsoft SQL server allows multiple schemas per database so to disambiguate between tables in different schemas it
+-- is necessary to set the hive.sql.schema property properly.
+
+-- Some JDBC APIs require the catalog, schema, and table names to be passed exactly as they are stored in the database.
+-- MSSQL stores unquoted identifiers by first converting them to lowercase thus the hive.sql.schema and
+-- hive.sql.table properties below are specified in lowercase.
+
+CREATE EXTERNAL TABLE country_0 (id int, name varchar(20))
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "MSSQL",
+ "hive.sql.jdbc.driver" = "com.microsoft.sqlserver.jdbc.SQLServerDriver",
+ "hive.sql.jdbc.url" = "jdbc:sqlserver://localhost:1433;DatabaseName=world;",
+ "hive.sql.dbcp.username" = "sa",
+ "hive.sql.dbcp.password" = "Its-a-s3cret",
+ "hive.sql.schema" = "bob",
+ "hive.sql.table" = "country");
+
+EXPLAIN CBO SELECT COUNT(*) FROM country_0;
+SELECT COUNT(*) FROM country_0;
+
+CREATE EXTERNAL TABLE country_1 (id int, name varchar(20))
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "MSSQL",
+ "hive.sql.jdbc.driver" = "com.microsoft.sqlserver.jdbc.SQLServerDriver",
+ "hive.sql.jdbc.url" = "jdbc:sqlserver://localhost:1433;DatabaseName=world;",
+ "hive.sql.dbcp.username" = "sa",
+ "hive.sql.dbcp.password" = "Its-a-s3cret",
+ "hive.sql.schema" = "alice",
+ "hive.sql.table" = "country");
+
+EXPLAIN CBO SELECT COUNT(*) FROM country_1;
+SELECT COUNT(*) FROM country_1;
+
+-- Test DML statements are working fine when accessing table in non-default schema
+INSERT INTO country_1 VALUES (8, 'Hungary');
Review comment:
I'm not familiar how dml statements are treated when the target is a JDBC table. I guess a `HiveJdbcConverter` shows up in the plan.
Could you please add an
```
explain cbo insert into country_1...
```
statement. Feel free to ignore my comment if we already have one.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
[GitHub] [hive] zabetak commented on a change in pull request #2759: HIVE-25591: CREATE EXTERNAL TABLE fails for JDBC tables stored in non-default schema
Posted by GitBox <gi...@apache.org>.
zabetak commented on a change in pull request #2759:
URL: https://github.com/apache/hive/pull/2759#discussion_r741798099
##########
File path: ql/src/test/queries/clientpositive/jdbc_table_with_schema_mssql.q
##########
@@ -0,0 +1,54 @@
+--! qt:database:mssql:q_test_country_table_with_schema.mssql.sql
+-- Microsoft SQL server allows multiple schemas per database so to disambiguate between tables in different schemas it
+-- is necessary to set the hive.sql.schema property properly.
+
+-- Some JDBC APIs require the catalog, schema, and table names to be passed exactly as they are stored in the database.
+-- MSSQL stores unquoted identifiers by first converting them to lowercase thus the hive.sql.schema and
+-- hive.sql.table properties below are specified in lowercase.
+
+CREATE EXTERNAL TABLE country_0 (id int, name varchar(20))
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "MSSQL",
+ "hive.sql.jdbc.driver" = "com.microsoft.sqlserver.jdbc.SQLServerDriver",
+ "hive.sql.jdbc.url" = "jdbc:sqlserver://localhost:1433;DatabaseName=world;",
+ "hive.sql.dbcp.username" = "sa",
+ "hive.sql.dbcp.password" = "Its-a-s3cret",
+ "hive.sql.schema" = "bob",
+ "hive.sql.table" = "country");
+
+EXPLAIN CBO SELECT COUNT(*) FROM country_0;
+SELECT COUNT(*) FROM country_0;
+
+CREATE EXTERNAL TABLE country_1 (id int, name varchar(20))
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "MSSQL",
+ "hive.sql.jdbc.driver" = "com.microsoft.sqlserver.jdbc.SQLServerDriver",
+ "hive.sql.jdbc.url" = "jdbc:sqlserver://localhost:1433;DatabaseName=world;",
+ "hive.sql.dbcp.username" = "sa",
+ "hive.sql.dbcp.password" = "Its-a-s3cret",
+ "hive.sql.schema" = "alice",
+ "hive.sql.table" = "country");
+
+EXPLAIN CBO SELECT COUNT(*) FROM country_1;
+SELECT COUNT(*) FROM country_1;
+
+-- Test DML statements are working fine when accessing table in non-default schema
+INSERT INTO country_1 VALUES (8, 'Hungary');
Review comment:
I added some plans in https://github.com/apache/hive/pull/2759/commits/5aa343db3cb36a5aca6c4ef0cfb94263fba38585
##########
File path: ql/src/test/queries/clientpositive/jdbc_table_with_schema_mssql.q
##########
@@ -0,0 +1,54 @@
+--! qt:database:mssql:q_test_country_table_with_schema.mssql.sql
+-- Microsoft SQL server allows multiple schemas per database so to disambiguate between tables in different schemas it
+-- is necessary to set the hive.sql.schema property properly.
+
+-- Some JDBC APIs require the catalog, schema, and table names to be passed exactly as they are stored in the database.
+-- MSSQL stores unquoted identifiers by first converting them to lowercase thus the hive.sql.schema and
+-- hive.sql.table properties below are specified in lowercase.
+
+CREATE EXTERNAL TABLE country_0 (id int, name varchar(20))
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "MSSQL",
+ "hive.sql.jdbc.driver" = "com.microsoft.sqlserver.jdbc.SQLServerDriver",
+ "hive.sql.jdbc.url" = "jdbc:sqlserver://localhost:1433;DatabaseName=world;",
+ "hive.sql.dbcp.username" = "sa",
+ "hive.sql.dbcp.password" = "Its-a-s3cret",
+ "hive.sql.schema" = "bob",
+ "hive.sql.table" = "country");
+
+EXPLAIN CBO SELECT COUNT(*) FROM country_0;
+SELECT COUNT(*) FROM country_0;
+
+CREATE EXTERNAL TABLE country_1 (id int, name varchar(20))
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "MSSQL",
+ "hive.sql.jdbc.driver" = "com.microsoft.sqlserver.jdbc.SQLServerDriver",
+ "hive.sql.jdbc.url" = "jdbc:sqlserver://localhost:1433;DatabaseName=world;",
+ "hive.sql.dbcp.username" = "sa",
+ "hive.sql.dbcp.password" = "Its-a-s3cret",
+ "hive.sql.schema" = "alice",
+ "hive.sql.table" = "country");
+
+EXPLAIN CBO SELECT COUNT(*) FROM country_1;
+SELECT COUNT(*) FROM country_1;
+
+-- Test DML statements are working fine when accessing table in non-default schema
+INSERT INTO country_1 VALUES (8, 'Hungary');
Review comment:
I added some plans in https://github.com/apache/hive/pull/2759/commits/5aa343db3cb36a5aca6c4ef0cfb94263fba38585
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
[GitHub] [hive] zabetak commented on a change in pull request #2759: HIVE-25591: CREATE EXTERNAL TABLE fails for JDBC tables stored in non-default schema
Posted by GitBox <gi...@apache.org>.
zabetak commented on a change in pull request #2759:
URL: https://github.com/apache/hive/pull/2759#discussion_r741798099
##########
File path: ql/src/test/queries/clientpositive/jdbc_table_with_schema_mssql.q
##########
@@ -0,0 +1,54 @@
+--! qt:database:mssql:q_test_country_table_with_schema.mssql.sql
+-- Microsoft SQL server allows multiple schemas per database so to disambiguate between tables in different schemas it
+-- is necessary to set the hive.sql.schema property properly.
+
+-- Some JDBC APIs require the catalog, schema, and table names to be passed exactly as they are stored in the database.
+-- MSSQL stores unquoted identifiers by first converting them to lowercase thus the hive.sql.schema and
+-- hive.sql.table properties below are specified in lowercase.
+
+CREATE EXTERNAL TABLE country_0 (id int, name varchar(20))
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "MSSQL",
+ "hive.sql.jdbc.driver" = "com.microsoft.sqlserver.jdbc.SQLServerDriver",
+ "hive.sql.jdbc.url" = "jdbc:sqlserver://localhost:1433;DatabaseName=world;",
+ "hive.sql.dbcp.username" = "sa",
+ "hive.sql.dbcp.password" = "Its-a-s3cret",
+ "hive.sql.schema" = "bob",
+ "hive.sql.table" = "country");
+
+EXPLAIN CBO SELECT COUNT(*) FROM country_0;
+SELECT COUNT(*) FROM country_0;
+
+CREATE EXTERNAL TABLE country_1 (id int, name varchar(20))
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "MSSQL",
+ "hive.sql.jdbc.driver" = "com.microsoft.sqlserver.jdbc.SQLServerDriver",
+ "hive.sql.jdbc.url" = "jdbc:sqlserver://localhost:1433;DatabaseName=world;",
+ "hive.sql.dbcp.username" = "sa",
+ "hive.sql.dbcp.password" = "Its-a-s3cret",
+ "hive.sql.schema" = "alice",
+ "hive.sql.table" = "country");
+
+EXPLAIN CBO SELECT COUNT(*) FROM country_1;
+SELECT COUNT(*) FROM country_1;
+
+-- Test DML statements are working fine when accessing table in non-default schema
+INSERT INTO country_1 VALUES (8, 'Hungary');
Review comment:
I added some plans in https://github.com/apache/hive/pull/2759/commits/5aa343db3cb36a5aca6c4ef0cfb94263fba38585
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
[GitHub] [hive] kasakrisz commented on a change in pull request #2759: HIVE-25591: CREATE EXTERNAL TABLE fails for JDBC tables stored in non-default schema
Posted by GitBox <gi...@apache.org>.
kasakrisz commented on a change in pull request #2759:
URL: https://github.com/apache/hive/pull/2759#discussion_r741675549
##########
File path: ql/src/test/queries/clientpositive/jdbc_table_with_schema_mssql.q
##########
@@ -0,0 +1,54 @@
+--! qt:database:mssql:q_test_country_table_with_schema.mssql.sql
+-- Microsoft SQL server allows multiple schemas per database so to disambiguate between tables in different schemas it
+-- is necessary to set the hive.sql.schema property properly.
+
+-- Some JDBC APIs require the catalog, schema, and table names to be passed exactly as they are stored in the database.
+-- MSSQL stores unquoted identifiers by first converting them to lowercase thus the hive.sql.schema and
+-- hive.sql.table properties below are specified in lowercase.
+
+CREATE EXTERNAL TABLE country_0 (id int, name varchar(20))
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "MSSQL",
+ "hive.sql.jdbc.driver" = "com.microsoft.sqlserver.jdbc.SQLServerDriver",
+ "hive.sql.jdbc.url" = "jdbc:sqlserver://localhost:1433;DatabaseName=world;",
+ "hive.sql.dbcp.username" = "sa",
+ "hive.sql.dbcp.password" = "Its-a-s3cret",
+ "hive.sql.schema" = "bob",
+ "hive.sql.table" = "country");
+
+EXPLAIN CBO SELECT COUNT(*) FROM country_0;
+SELECT COUNT(*) FROM country_0;
+
+CREATE EXTERNAL TABLE country_1 (id int, name varchar(20))
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "MSSQL",
+ "hive.sql.jdbc.driver" = "com.microsoft.sqlserver.jdbc.SQLServerDriver",
+ "hive.sql.jdbc.url" = "jdbc:sqlserver://localhost:1433;DatabaseName=world;",
+ "hive.sql.dbcp.username" = "sa",
+ "hive.sql.dbcp.password" = "Its-a-s3cret",
+ "hive.sql.schema" = "alice",
+ "hive.sql.table" = "country");
+
+EXPLAIN CBO SELECT COUNT(*) FROM country_1;
+SELECT COUNT(*) FROM country_1;
+
+-- Test DML statements are working fine when accessing table in non-default schema
+INSERT INTO country_1 VALUES (8, 'Hungary');
Review comment:
I'm not familiar how dml statements are treated when the target is a JDBC table. I guess a `HiveJdbcConverter` shows up in the plan.
Could you please add an
```
explain cbo insert into country_1...
```
statement. Feel free to ignore my comment if we already have one.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
[GitHub] [hive] kasakrisz commented on a change in pull request #2759: HIVE-25591: CREATE EXTERNAL TABLE fails for JDBC tables stored in non-default schema
Posted by GitBox <gi...@apache.org>.
kasakrisz commented on a change in pull request #2759:
URL: https://github.com/apache/hive/pull/2759#discussion_r741675549
##########
File path: ql/src/test/queries/clientpositive/jdbc_table_with_schema_mssql.q
##########
@@ -0,0 +1,54 @@
+--! qt:database:mssql:q_test_country_table_with_schema.mssql.sql
+-- Microsoft SQL server allows multiple schemas per database so to disambiguate between tables in different schemas it
+-- is necessary to set the hive.sql.schema property properly.
+
+-- Some JDBC APIs require the catalog, schema, and table names to be passed exactly as they are stored in the database.
+-- MSSQL stores unquoted identifiers by first converting them to lowercase thus the hive.sql.schema and
+-- hive.sql.table properties below are specified in lowercase.
+
+CREATE EXTERNAL TABLE country_0 (id int, name varchar(20))
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "MSSQL",
+ "hive.sql.jdbc.driver" = "com.microsoft.sqlserver.jdbc.SQLServerDriver",
+ "hive.sql.jdbc.url" = "jdbc:sqlserver://localhost:1433;DatabaseName=world;",
+ "hive.sql.dbcp.username" = "sa",
+ "hive.sql.dbcp.password" = "Its-a-s3cret",
+ "hive.sql.schema" = "bob",
+ "hive.sql.table" = "country");
+
+EXPLAIN CBO SELECT COUNT(*) FROM country_0;
+SELECT COUNT(*) FROM country_0;
+
+CREATE EXTERNAL TABLE country_1 (id int, name varchar(20))
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "MSSQL",
+ "hive.sql.jdbc.driver" = "com.microsoft.sqlserver.jdbc.SQLServerDriver",
+ "hive.sql.jdbc.url" = "jdbc:sqlserver://localhost:1433;DatabaseName=world;",
+ "hive.sql.dbcp.username" = "sa",
+ "hive.sql.dbcp.password" = "Its-a-s3cret",
+ "hive.sql.schema" = "alice",
+ "hive.sql.table" = "country");
+
+EXPLAIN CBO SELECT COUNT(*) FROM country_1;
+SELECT COUNT(*) FROM country_1;
+
+-- Test DML statements are working fine when accessing table in non-default schema
+INSERT INTO country_1 VALUES (8, 'Hungary');
Review comment:
I'm not familiar how dml statements are treated when the target is a JDBC table. I guess a `HiveJdbcConverter` shows up in the plan.
Could you please add an
```
explain cbo insert into country_1...
```
statement. Feel free to ignore my comment if we already have one.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
[GitHub] [hive] zabetak commented on a change in pull request #2759: HIVE-25591: CREATE EXTERNAL TABLE fails for JDBC tables stored in non-default schema
Posted by GitBox <gi...@apache.org>.
zabetak commented on a change in pull request #2759:
URL: https://github.com/apache/hive/pull/2759#discussion_r741798099
##########
File path: ql/src/test/queries/clientpositive/jdbc_table_with_schema_mssql.q
##########
@@ -0,0 +1,54 @@
+--! qt:database:mssql:q_test_country_table_with_schema.mssql.sql
+-- Microsoft SQL server allows multiple schemas per database so to disambiguate between tables in different schemas it
+-- is necessary to set the hive.sql.schema property properly.
+
+-- Some JDBC APIs require the catalog, schema, and table names to be passed exactly as they are stored in the database.
+-- MSSQL stores unquoted identifiers by first converting them to lowercase thus the hive.sql.schema and
+-- hive.sql.table properties below are specified in lowercase.
+
+CREATE EXTERNAL TABLE country_0 (id int, name varchar(20))
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "MSSQL",
+ "hive.sql.jdbc.driver" = "com.microsoft.sqlserver.jdbc.SQLServerDriver",
+ "hive.sql.jdbc.url" = "jdbc:sqlserver://localhost:1433;DatabaseName=world;",
+ "hive.sql.dbcp.username" = "sa",
+ "hive.sql.dbcp.password" = "Its-a-s3cret",
+ "hive.sql.schema" = "bob",
+ "hive.sql.table" = "country");
+
+EXPLAIN CBO SELECT COUNT(*) FROM country_0;
+SELECT COUNT(*) FROM country_0;
+
+CREATE EXTERNAL TABLE country_1 (id int, name varchar(20))
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "MSSQL",
+ "hive.sql.jdbc.driver" = "com.microsoft.sqlserver.jdbc.SQLServerDriver",
+ "hive.sql.jdbc.url" = "jdbc:sqlserver://localhost:1433;DatabaseName=world;",
+ "hive.sql.dbcp.username" = "sa",
+ "hive.sql.dbcp.password" = "Its-a-s3cret",
+ "hive.sql.schema" = "alice",
+ "hive.sql.table" = "country");
+
+EXPLAIN CBO SELECT COUNT(*) FROM country_1;
+SELECT COUNT(*) FROM country_1;
+
+-- Test DML statements are working fine when accessing table in non-default schema
+INSERT INTO country_1 VALUES (8, 'Hungary');
Review comment:
I added some plans in https://github.com/apache/hive/pull/2759/commits/5aa343db3cb36a5aca6c4ef0cfb94263fba38585
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
[GitHub] [hive] kasakrisz commented on a change in pull request #2759: HIVE-25591: CREATE EXTERNAL TABLE fails for JDBC tables stored in non-default schema
Posted by GitBox <gi...@apache.org>.
kasakrisz commented on a change in pull request #2759:
URL: https://github.com/apache/hive/pull/2759#discussion_r741675549
##########
File path: ql/src/test/queries/clientpositive/jdbc_table_with_schema_mssql.q
##########
@@ -0,0 +1,54 @@
+--! qt:database:mssql:q_test_country_table_with_schema.mssql.sql
+-- Microsoft SQL server allows multiple schemas per database so to disambiguate between tables in different schemas it
+-- is necessary to set the hive.sql.schema property properly.
+
+-- Some JDBC APIs require the catalog, schema, and table names to be passed exactly as they are stored in the database.
+-- MSSQL stores unquoted identifiers by first converting them to lowercase thus the hive.sql.schema and
+-- hive.sql.table properties below are specified in lowercase.
+
+CREATE EXTERNAL TABLE country_0 (id int, name varchar(20))
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "MSSQL",
+ "hive.sql.jdbc.driver" = "com.microsoft.sqlserver.jdbc.SQLServerDriver",
+ "hive.sql.jdbc.url" = "jdbc:sqlserver://localhost:1433;DatabaseName=world;",
+ "hive.sql.dbcp.username" = "sa",
+ "hive.sql.dbcp.password" = "Its-a-s3cret",
+ "hive.sql.schema" = "bob",
+ "hive.sql.table" = "country");
+
+EXPLAIN CBO SELECT COUNT(*) FROM country_0;
+SELECT COUNT(*) FROM country_0;
+
+CREATE EXTERNAL TABLE country_1 (id int, name varchar(20))
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "MSSQL",
+ "hive.sql.jdbc.driver" = "com.microsoft.sqlserver.jdbc.SQLServerDriver",
+ "hive.sql.jdbc.url" = "jdbc:sqlserver://localhost:1433;DatabaseName=world;",
+ "hive.sql.dbcp.username" = "sa",
+ "hive.sql.dbcp.password" = "Its-a-s3cret",
+ "hive.sql.schema" = "alice",
+ "hive.sql.table" = "country");
+
+EXPLAIN CBO SELECT COUNT(*) FROM country_1;
+SELECT COUNT(*) FROM country_1;
+
+-- Test DML statements are working fine when accessing table in non-default schema
+INSERT INTO country_1 VALUES (8, 'Hungary');
Review comment:
I'm not familiar how dml statements are treated when the target is a JDBC table. I guess a `HiveJdbcConverter` shows up in the plan.
Could you please add an
```
explain cbo insert into country_1...
```
statement. Feel free to ignore my comment if we already have one.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
[GitHub] [hive] zabetak closed pull request #2759: HIVE-25591: CREATE EXTERNAL TABLE fails for JDBC tables stored in non-default schema
Posted by GitBox <gi...@apache.org>.
zabetak closed pull request #2759:
URL: https://github.com/apache/hive/pull/2759
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org