Sensitivity to mismatched schema between Hive table definition and the hive.sql.query: *** In Postgres: beeline -n e1080619 -p Welcome1 -u 'jdbc:postgresql://hophadedge02.protegentdev.fisglobal.corp/daved' DROP TABLE dd_table_jdbc; CREATE TABLE dd_table_jdbc( col1 text, col2 timestamp(9) -- n decimal places on seconds part. Limit is microseconds ); INSERT INTO dd_table_jdbc VALUES('a0', '2019-04-03 15:54:21.543'); INSERT INTO dd_table_jdbc VALUES('a1', '2019-04-03 15:54:21.000123456789'); INSERT INTO dd_table_jdbc VALUES('a2', '2019-04-03 15:54:21.151'); INSERT INTO dd_table_jdbc VALUES('a3', '2019-04-03 15:54:21.499'); INSERT INTO dd_table_jdbc VALUES('a4', '2019-04-03 15:54:21.950'); INSERT INTO dd_table_jdbc VALUES('a5', '2019-04-03 15:54:21.9998765432'); INSERT INTO dd_table_jdbc VALUES('a5', '2019-04-03 15:54:21.99954321'); SELECT * FROM dd_table_jdbc; +-------+-----------------------------+ | col1 | col2 | +-------+-----------------------------+ | a0 | 2019-04-03 15:54:21.543 | | a1 | 2019-04-03 15:54:21.000123 | | a2 | 2019-04-03 15:54:21.151 | | a3 | 2019-04-03 15:54:21.499 | | a4 | 2019-04-03 15:54:21.95 | | a5 | 2019-04-03 15:54:21.999877 | | a5 | 2019-04-03 15:54:21.999543 | +-------+-----------------------------+ 7 rows selected (0.012 seconds) *** In Hive: DROP TABLE dd_table_jdbc; CREATE EXTERNAL TABLE dd_table_jdbc( col1 string, col2 timestamp ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( "hive.sql.database.type" = "POSTGRES", "hive.sql.jdbc.driver" = "org.postgresql.Driver", "hive.sql.jdbc.url" = "jdbc:postgresql://hophadedge02.protegentdev.fisglobal.corp/daved", "hive.sql.dbcp.username" = "e1080619", "hive.sql.dbcp.password" = "Welcome1", "hive.sql.dbcp.maxActive" = "1", "hive.sql.query" = "SELECT col1, col2 AT TIME ZONE 'UTC' FROM dd_table_jdbc" ); Initially, Hive definition matches hive.sql.query, so all good: SELECT * FROM dd_table_jdbc; +---------------------+-----------------------------+ | dd_table_jdbc.col1 | dd_table_jdbc.col2 | +---------------------+-----------------------------+ | a0 | 2019-04-03 15:54:21.543 | | a1 | 2019-04-03 15:54:21.000123 | | a2 | 2019-04-03 15:54:21.151 | | a3 | 2019-04-03 15:54:21.499 | | a4 | 2019-04-03 15:54:21.95 | | a5 | 2019-04-03 15:54:21.999877 | | a5 | 2019-04-03 15:54:21.999543 | +---------------------+-----------------------------+ Select an additional column in jdbc "hive.sql.query" = "SELECT col1, col2 AT TIME ZONE 'UTC', col1 AS col3 FROM dd_table_jdbc" seems OK Do column aliases make a difference? "hive.sql.query" = "SELECT col1 AS column_1, col2 AT TIME ZONE 'UTC' AS column_2 FROM dd_table_jdbc" No problem, seems OK However, the following do cause problems. There is *NO ERROR* reported. Instead Hive just returns zero rows, making it difficult to spot blunders, which are easy to make when trying to match the JDBC query with the Hive table definition. Swap column order in hive.sql.query: "hive.sql.query" = "SELECT col2 AT TIME ZONE 'UTC' , col1 FROM dd_table_jdbc" SELECT * FROM dd_table_jdbc; +---------------------+---------------------+ | dd_table_jdbc.col1 | dd_table_jdbc.col2 | +---------------------+---------------------+ +---------------------+---------------------+ No rows selected (0.419 seconds) Omit a column in hive.sql.query: "hive.sql.query" = "SELECT col2 AT TIME ZONE 'UTC' FROM dd_table_jdbc" SELECT * FROM dd_table_jdbc; +---------------------+---------------------+ | dd_table_jdbc.col1 | dd_table_jdbc.col2 | +---------------------+---------------------+ +---------------------+---------------------+ No rows selected (0.423 seconds) Try to select an extra column in Hive DROP TABLE dd_table_jdbc; CREATE EXTERNAL TABLE dd_table_jdbc( col1 string, col2 timestamp, col3 string ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( "hive.sql.database.type" = "POSTGRES", "hive.sql.jdbc.driver" = "org.postgresql.Driver", "hive.sql.jdbc.url" = "jdbc:postgresql://hophadedge02.protegentdev.fisglobal.corp/daved", "hive.sql.dbcp.username" = "e1080619", "hive.sql.dbcp.password" = "Welcome1", "hive.sql.dbcp.maxActive" = "1", "hive.sql.query" = "SELECT col1, col2 AT TIME ZONE 'UTC' FROM dd_table_jdbc" ); +---------------------+---------------------+---------------------+ | dd_table_jdbc.col1 | dd_table_jdbc.col2 | dd_table_jdbc.col3 | +---------------------+---------------------+---------------------+ +---------------------+---------------------+---------------------+ No rows selected (0.443 seconds) Mismatched data types? DROP TABLE dd_table_jdbc; CREATE EXTERNAL TABLE dd_table_jdbc( col1 string, col2 string ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( "hive.sql.database.type" = "POSTGRES", "hive.sql.jdbc.driver" = "org.postgresql.Driver", "hive.sql.jdbc.url" = "jdbc:postgresql://hophadedge02.protegentdev.fisglobal.corp/daved", "hive.sql.dbcp.username" = "e1080619", "hive.sql.dbcp.password" = "Welcome1", "hive.sql.dbcp.maxActive" = "1", "hive.sql.query" = "SELECT col1, col2 AT TIME ZONE 'UTC' FROM dd_table_jdbc" ); SELECT * FROM dd_table_jdbc; Happy this way round (timestamp coerced to string), but what about the other way? DROP TABLE dd_table_jdbc; CREATE EXTERNAL TABLE dd_table_jdbc( col1 timestamp, col2 timestamp ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( "hive.sql.database.type" = "POSTGRES", "hive.sql.jdbc.driver" = "org.postgresql.Driver", "hive.sql.jdbc.url" = "jdbc:postgresql://hophadedge02.protegentdev.fisglobal.corp/daved", "hive.sql.dbcp.username" = "e1080619", "hive.sql.dbcp.password" = "Welcome1", "hive.sql.dbcp.maxActive" = "1", "hive.sql.query" = "SELECT col1, col2 AT TIME ZONE 'UTC' FROM dd_table_jdbc" ); SELECT * FROM dd_table_jdbc; +---------------------+---------------------+ | dd_table_jdbc.col1 | dd_table_jdbc.col2 | +---------------------+---------------------+ +---------------------+---------------------+ No rows selected (0.377 seconds)