[HIVE-27316] Select query on table with remote database returns NULL values with postgreSQL and Redshift data connectors

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.0.0-beta-1
• Labels: pull-request-available
• Assignee: Venugopal Reddy K
• Reporter: Venugopal Reddy K
• Created: 04/May/23 11:20
• Updated: 15/Aug/23 06:33
• Resolved: 12/May/23 00:24

Description

Brief Description:

Few datatypes are not mapped from postgres/redshift to hive data types. Thus values for unmapped columns are shown as null.

 

Steps to reproduce:

Redshift:

1. create redshift connector, and create remote database with it.

create connector rscon1 type 'postgres' url 'jdbc:redshift://redshift.us-east-2.redshift.amazonaws.com:5439/dev?ssl=false&tcpKeepAlive=true' WITH DCPROPERTIES ('hive.sql.dbcp.username'='venu','hive.sql.dbcp.password'='Mypassword123','hive.sql.jdbc.driver'='com.amazon.redshift.jdbc.Driver','hive.sql.schema' = 'public');

create REMOTE database localdev1 using rscon1 with DBPROPERTIES("connector.remoteDbName"="dev");

2. Create a test table and insert a row to redshit db through a jdbc client.

Class.forName("com.amazon.redshift.jdbc.Driver");
con = DriverManager.getConnection(
    "jdbc:redshift://redshift.us-east-2.redshift.amazonaws.com:5439/dev?ssl=false&tcpKeepAlive=true",
    "venu", "Mypassword123");

stmt = con.createStatement();
stmt.executeUpdate("create table test (intvar int, int4var int4, integervar integer, smallintvar smallint, int2var int2, bigintvar bigint, int8var int8, boolvar bool, booleanvar boolean, floatvar float, float4var float4, realvar real, float8var float8, doubleprecisionvar double precision, numericvar numeric(8,3), charactervar character(14), ncharvar nchar(10), varcharvar varchar(30), charactervaryingvar character varying(20))");
stmt.executeUpdate("insert into test (intvar, int4var, integervar, smallintvar, int2var, bigintvar, int8var, boolvar, booleanvar, floatvar, float4var, realvar, float8var, doubleprecisionvar, numericvar, charactervar, ncharvar, varcharvar, charactervaryingvar) values (1, 10, 100, 2, 20, 3, 30, true, true, 1.234, 2.345, 3.456, 4.567, 5.678, 6.789, 'charactervar', 'ncharvar', 'varcharvar', 'charactervaryingvar')");

3. Execute select query on test table from beeline. NULL values are shown for the columns that are not mapped to hive data types.

0: jdbc:hive2://localhost:10000> use localdev1;
No rows affected (0.138 seconds)
0: jdbc:hive2://localhost:10000> select * from test;
+--------------+---------------+------------------+-------------------+---------------+-----------------+---------------+---------------+------------------+----------------+-----------------+---------------+-----------------+--------------------------+------------------+--------------------+----------------+------------------+---------------------------+
| test.intvar  | test.int4var  | test.integervar  | test.smallintvar  | test.int2var  | test.bigintvar  | test.int8var  | test.boolvar  | test.booleanvar  | test.floatvar  | test.float4var  | test.realvar  | test.float8var  | test.doubleprecisionvar  | test.numericvar  | test.charactervar  | test.ncharvar  | test.varcharvar  | test.charactervaryingvar  |
+--------------+---------------+------------------+-------------------+---------------+-----------------+---------------+---------------+------------------+----------------+-----------------+---------------+-----------------+--------------------------+------------------+--------------------+----------------+------------------+---------------------------+
| NULL         | NULL          | NULL             | NULL              | NULL          | 3               | 30            | NULL          | NULL             | NULL           | NULL            | NULL          | NULL            | NULL                     | 7                | NULL               | NULL           | varcharvar       | charactervaryingvar       |
+--------------+---------------+------------------+-------------------+---------------+-----------------+---------------+---------------+------------------+----------------+-----------------+---------------+-----------------+--------------------------+------------------+--------------------+----------------+------------------+---------------------------+
1 row selected (24.839 seconds)
0: jdbc:hive2://localhost:10000> 

 

Postgres:

1. create postgres connector, and create remote database with it.

create connector pscon1 type 'postgres' url 'jdbc:postgresql://postgres.us-east-2.rds.amazonaws.com:5432/postgres?ssl=false&tcpKeepAlive=true' WITH DCPROPERTIES ('hive.sql.dbcp.username'='venu','hive.sql.dbcp.password'='Mypassword123','hive.sql.jdbc.driver'='org.postgresql.Driver','hive.sql.schema' = 'public');

create REMOTE database localdevps1 using pscon1 with DBPROPERTIES("connector.remoteDbName"="postgres");

2. Create a test table and insert a row to postgre through a jdbc client.

Class.forName("org.postgresql.Driver");
con = DriverManager.getConnection("jdbc:postgresql://postgres.us-east-2.rds.amazonaws.com:5432/postgres","venu", "Mypassword123");

stmt = con.createStatement();
stmt.executeUpdate("create table test (intvar int, int4var int4, integervar integer, smallintvar smallint, int2var int2, bigintvar bigint, int8var int8, boolvar bool, booleanvar boolean, floatvar float, float4var float4, realvar real, float8var float8, doubleprecisionvar double precision, numericvar numeric(8,3), charactervar character(14), ncharvar nchar(10), varcharvar varchar(30), charactervaryingvar character varying(20))");
stmt.executeUpdate("insert into test (intvar, int4var, integervar, smallintvar, int2var, bigintvar, int8var, boolvar, booleanvar, floatvar, float4var, realvar, float8var, doubleprecisionvar, numericvar, charactervar, ncharvar, varcharvar, charactervaryingvar) values (1, 10, 100, 2, 20, 3, 30, true, true, 1.234, 2.345, 3.456, 4.567, 5.678, 6.789, 'charactervar', 'ncharvar', 'varcharvar', 'charactervaryingvar')");

3. Execute select query on test table from beeline. NULL values are shown for the columns that are not mapped to hive data types.

 

0: jdbc:hive2://localhost:10000> use localdevps1;
0: jdbc:hive2://localhost:10000> select * from test;
+--------------+---------------+------------------+-------------------+---------------+-----------------+---------------+---------------+------------------+----------------+-----------------+---------------+-----------------+--------------------------+------------------+--------------------+----------------+------------------+---------------------------+
| test.intvar  | test.int4var  | test.integervar  | test.smallintvar  | test.int2var  | test.bigintvar  | test.int8var  | test.boolvar  | test.booleanvar  | test.floatvar  | test.float4var  | test.realvar  | test.float8var  | test.doubleprecisionvar  | test.numericvar  | test.charactervar  | test.ncharvar  | test.varcharvar  | test.charactervaryingvar  |
+--------------+---------------+------------------+-------------------+---------------+-----------------+---------------+---------------+------------------+----------------+-----------------+---------------+-----------------+--------------------------+------------------+--------------------+----------------+------------------+---------------------------+
| NULL         | NULL          | NULL             | NULL              | NULL          | 3               | 30            | NULL          | NULL             | NULL           | NULL            | NULL          | NULL            | NULL                     | 7                | charactervar       | ncharvar       | varcharvar       | charactervaryingvar       |
+--------------+---------------+------------------+-------------------+---------------+-----------------+---------------+---------------+------------------+----------------+-----------------+---------------+-----------------+--------------------------+------------------+--------------------+----------------+------------------+---------------------------+
1 row selected (69.075 seconds)

 

Issue Links

• links to: GitHub Pull Request #4300