[
https://issues.apache.org/jira/browse/HIVE-27316?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zhihua Deng resolved HIVE-27316.
--------------------------------
Fix Version/s: 4.0.0
Resolution: Fixed
> 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
> Fix For: 4.0.0
>
> Time Spent: 40m
> 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)
