Hive to Redshift

Highlighted
Six Stars

Hive to Redshift

Hello,

 

Working on loading data to Redshift reading from Hive. 

 

Initially started with a reading table and printing the results. Used Amazon EMR Version EMR 5.15 and 5.8 

 

tHiveRow ---main----> tLogRow

 

The connection was successful but the results its showing me as just one row with all NULL's

 

 

[statistics] connecting to socket on port 3611

[statistics] connected

 

Column Headers....

=----------+---------+----------+----+-----------------+--------------------+---------------+------------------+---------------------+------------+----------+-----------------+----------+------------+---------------+----------+------------+---------------+---------+------------+----------+--------------+------------=|

|null       |null     |null      |null|null             |null                |null           |null              |null                 |null        |null      |null             |null      |null        |null           |null      |null        |null           |null     |null        |null      |null          |null         |

'-----------+---------+----------+----+-----------------+--------------------+---------------+------------------+---------------------+------------+----------+-----------------+----------+------------+---------------+----------+------------+---------------+---------+------------+----------+--------------+-------------'

 

[statistics] disconnected

 

Any reason why? 

 

Tags (2)

Accepted Solutions
Six Stars

Re: Hive to Redshift

I got it. tHiveInout worked for me. Initially casting the data types was the problem.  Thanks nfz11.

View solution in original post


All Replies
Nine Stars

Re: Hive to Redshift

This was already asked and answered here:

https://community.talend.com/t5/Design-and-Development/tHiveRow-with-tLogRow-no-data-is-displayed/td...

 

You need a tParseRecordSet after the tHiveRow:

https://help.talend.com/reader/wDRBNUuxk629sNcI0dNYaA/SgJtwV2lbrJhkT1jjdzwjA

 

You need to define the record set in the tHiveRow component and parse it with a tParseRecordSet afterwards.  This is true of all TDBRow implementations if you are selecting data.

--
Please give Kudos and mark topics as solved where appropriate.
Six Stars

Re: Hive to Redshift

Thanks, but no luck. 

 

changed the job as 

tHiveRow ---main---->tParseRecordSet ----main---> tLogRow

 

I see following error, not sure if i'm using RecordSet properly or not.

Exception in component tParseRecordSet_1 (Hive_to_RD)

java.sql.SQLException

at org.apache.hive.jdbc.HiveBaseResultSet.findColumn(HiveBaseResultSet.java:89)

at org.apache.hive.jdbc.HiveBaseResultSet.getString(HiveBaseResultSet.java:530)

at testproject.hive_to_rd_0_1.Hive_to_RD.tHiveRow_2Process(Hive_to_RD.java:3016)

at testproject.hive_to_rd_0_1.Hive_to_RD.tHiveConnection_1Process(Hive_to_RD.java:997)

at testproject.hive_to_rd_0_1.Hive_to_RD.runJobInTOS(Hive_to_RD.java:7593)

at testproject.hive_to_rd_0_1.Hive_to_RD.main(Hive_to_RD.java:7323)

[DEBUG]: org.apache.hive.jdbc.HiveQueryResultSet - Fetched row string:

[FATAL]: testproject.hive_to_rd_0_1.Hive_to_RD - tParseRecordSet_1 null

java.sql.SQLException

at org.apache.hive.jdbc.HiveBaseResultSet.findColumn(HiveBaseResultSet.java:89)

at org.apache.hive.jdbc.HiveBaseResultSet.getString(HiveBaseResultSet.java:530)

at testproject.hive_to_rd_0_1.Hive_to_RD.tHiveRow_2Process(Hive_to_RD.java:3016)

at testproject.hive_to_rd_0_1.Hive_to_RD.tHiveConnection_1Process(Hive_to_RD.java:997)

at testproject.hive_to_rd_0_1.Hive_to_RD.runJobInTOS(Hive_to_RD.java:7593)

at testproject.hive_to_rd_0_1.Hive_to_RD.main(Hive_to_RD.java:7323)

 

Nine Stars

Re: Hive to Redshift

Did you put an object in your tHiveRow schema to capture the record set and pass that to the parse record set?  See this example referenced from the first link that shows the same thing in MySQLRow:

https://help.talend.com/reader/KxVIhxtXBBFymmkkWJ~O4Q/ypSGaMxbolMjIJbuXjCFQA

 

Please show screenshots of the details of your components if you are having problems.

--
Please give Kudos and mark topics as solved where appropriate.
Six Stars

Re: Hive to Redshift

nfz11,

 

Yes, I followed the same steps but not sure what I'm missing. Maybe I'm overlooking something. 

 

 Initially, I just want to see the table data after that, I will map using TMap to load into Redshift. 

 

Screen Shot 2019-07-09 at 3.23.01 PM.png

 

 

Screen Shot 2019-07-09 at 3.25.30 PM.pngScreen Shot 2019-07-09 at 3.26.58 PM.pngScreen Shot 2019-07-09 at 3.27.52 PM.png

Six Stars

Re: Hive to Redshift

I got it. tHiveInout worked for me. Initially casting the data types was the problem.  Thanks nfz11.

View solution in original post

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog