[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

[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