[
https://issues.apache.org/jira/browse/HIVE-27316?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Venugopal Reddy K updated HIVE-27316:
-------------------------------------
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:*
1. create connectors for postgres,redshift, and create remote database with the
respective connectors.
{code:java}
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");
create connector pscon1 type 'postgres' url
'jdbc:postgresql://postgre.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");
{code}
2. Create a table and insert a row to redshit db.
{code:java}
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')");{code}
2. Execute select query on test table(in remote db) having int2, int4, float4,
float8, bool, character columns shows NULL values.
{code:java}
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>
{code}
was:
*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:*
1. create connectors for postgres,redshift, and create remote database with the
respective connectors.
{code:java}
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'='cloudera','hive.sql.dbcp.password'='Cloudera#123','hive.sql.jdbc.driver'='com.amazon.redshift.jdbc.Driver','hive.sql.schema'
= 'public');
create REMOTE database localdev1 using rscon1 with
DBPROPERTIES("connector.remoteDbName"="dev");
create connector pscon1 type 'postgres' url
'jdbc:postgresql://postgre.us-east-2.rds.amazonaws.com:5432/postgres?ssl=false&tcpKeepAlive=true'
WITH DCPROPERTIES
('hive.sql.dbcp.username'='postgres','hive.sql.dbcp.password'='Cloudera#123','hive.sql.jdbc.driver'='org.postgresql.Driver','hive.sql.schema'
= 'public');
create REMOTE database localdevps1 using pscon1 with
DBPROPERTIES("connector.remoteDbName"="postgres");
{code}
2. Execute select query on table(in remote db) having int2, int4, float4,
float8, bool, character columns shows NULL values.
> Select query on table with remote database returns NULL values with
> postgreSQL and Redshift data connectors
> -----------------------------------------------------------------------------------------------------------
>
> Key: HIVE-27316
> URL: https://issues.apache.org/jira/browse/HIVE-27316
> Project: Hive
> Issue Type: Bug
> Reporter: Venugopal Reddy K
> Assignee: Venugopal Reddy K
> Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> *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:*
> 1. create connectors for postgres,redshift, and create remote database with
> the respective connectors.
>
> {code:java}
> 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");
> create connector pscon1 type 'postgres' url
> 'jdbc:postgresql://postgre.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");
> {code}
> 2. Create a table and insert a row to redshit db.
> {code:java}
> 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')");{code}
> 2. Execute select query on test table(in remote db) having int2, int4,
> float4, float8, bool, character columns shows NULL values.
>
> {code:java}
> 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>
> {code}
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
