[
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:*
*Redshift:*
1. create redshift connector, and create remote database with it.
{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");
{code}
2. Create a test table and insert a row to redshit db through a jdbc client.
{code:java}
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')");{code}
3. Execute select query on test table from beeline. NULL values are shown for
the columns that are not mapped to hive data types.
{code:java}
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>
{code}
*Postgres:*
1. create postgres connector, and create remote database with it.
{code:java}
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");{code}
2. Create a test table and insert a row to postgre through a jdbc client.
{code:java}
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')");{code}
3. Execute select query on test table from beeline. NULL values are shown for
the columns that are not mapped to hive data types.
{code:java}
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)
{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:*
*Redshift:*
1. create redshift connector, and create remote database with it.
{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");
{code}
2. Create a test table and insert a row to redshit db through a jdbc client.
{code:java}
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')");{code}
3. Execute select query on test table from beeline. NULL values are shown for
the columns that are not mapped to hive data types.
{code:java}
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>
{code}
*Postgres:*
1. create postgres connector, and create remote database with it.
{code:java}
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");{code}
2. Create a test table and insert a row to redshit db through a jdbc client.
{code:java}
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')");{code}
> 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:*
> *Redshift:*
> 1. create redshift connector, and create remote database with it.
> {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");
> {code}
> 2. Create a test table and insert a row to redshit db through a jdbc client.
> {code:java}
> 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')");{code}
> 3. Execute select query on test table from beeline. NULL values are shown for
> the columns that are not mapped to hive data types.
> {code:java}
> 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>
> {code}
>
> *Postgres:*
> 1. create postgres connector, and create remote database with it.
> {code:java}
> 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");{code}
> 2. Create a test table and insert a row to postgre through a jdbc client.
> {code:java}
> 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')");{code}
> 3. Execute select query on test table from beeline. NULL values are shown for
> the columns that are not mapped to hive data types.
>
> {code:java}
> 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)
> {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
