topic Re: Hive to Redshift in Talend Studio

Hive to Redshift

Anonymous — Mon, 08 Jul 2019 22:14:07 GMT

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....

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

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

[statistics] disconnected

Any reason why?

Re: Hive to Redshift

nfz11 — Tue, 09 Jul 2019 05:43:00 GMT

This was already asked and answered here:

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

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.

Re: Hive to Redshift

Anonymous — Tue, 09 Jul 2019 19:10:34 GMT

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)

Re: Hive to Redshift

nfz11 — Tue, 09 Jul 2019 19:16:08 GMT

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.

Re: Hive to Redshift

Anonymous — Tue, 09 Jul 2019 20:32:25 GMT

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.

Re: Hive to Redshift

Anonymous — Tue, 09 Jul 2019 21:33:14 GMT

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