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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
