tRedshiftInput not working after building the Job

Anonymous — Sat, 16 Nov 2024 11:35:42 GMT

Hi all,
Im facing a issue from some time ago when using the tRedshiftInput component.
The Job needs to read some tables from staging area and dimensions in Redshift and later insert data in a dimension table also located in the same Redshift server.
The job runs perfectly when ruining from DI Open Studio, it not takes more than 5 minutes normally.
But the job starts and never ends (I waited for more than 5 hours) when running it after building the job. After some investigation I realise that the Job never ends querying the data (its a quite plane simple query) the redshift table with component tRedshiftInput.
So I compared the logs redshift-jdbc.log using the option of both jobs (the build and the pre-build from DI Open Studio) and I got this:
In the DI Open Studio, the job works fine and the log redshift-jdbc.log looks like (showing only first 50 lines):

DriverManager.getConnection("jdbc:postgresql://xxxxx.cgnifbeifavu.ap-southeast-1.redshift.amazonaws.com:5439/dev?prepareThreshold=100&loglevel=7&protocolVersion=3")
    trying driver
16:44:00.449 (driver) Loading driver configuration via classloader sun.misc.Launcher$AppClassLoader@558fe7c3
16:44:00.449 (driver) Connecting with URL: jdbc:postgresql://xxxxx.cgnifbeifavu.ap-southeast-1.redshift.amazonaws.com:5439/dev?prepareThreshold=100&loglevel=7&protocolVersion=3
16:44:00.454 (1) PostgreSQL 8.4 JDBC4 (build 703)
16:44:00.457 (1) Trying to establish a protocol version 3 connection to xxxxx.cgnifbeifavu.ap-southeast-1.redshift.amazonaws.com:5439
16:44:01.162 (1)  FE=> StartupPacket(user=xxxxx, database=dev, client_encoding=UNICODE, DateStyle=ISO, extra_float_digits=2)
16:44:01.189 (1)  <=BE AuthenticationReqMD5(salt=1720827a)
16:44:01.190 (1)  FE=> Password(md5digest=password)
16:44:01.222 (1)  <=BE AuthenticationOk
16:44:01.230 (1)  <=BE ParameterStatus(client_encoding = UNICODE)
16:44:01.230 (1)  <=BE ParameterStatus(datestyle = ISO, MDY)
16:44:01.230 (1)  <=BE ParameterStatus(gconf_case_sensitive = on)
16:44:01.230 (1)  <=BE ParameterStatus(integer_datetimes = on)
16:44:01.230 (1)  <=BE ParameterStatus(is_superuser = off)
16:44:01.230 (1)  <=BE ParameterStatus(server_encoding = UNICODE)
16:44:01.231 (1)  <=BE ParameterStatus(server_version = 8.0.2)
16:44:01.231 (1)  <=BE ParameterStatus(session_authorization = rsuser)
16:44:01.231 (1)  <=BE ParameterStatus(TimeZone = UTC)
16:44:01.231 (1)  <=BE ParameterStatus(timezone_abbreviations = Default)
16:44:01.231 (1)  <=BE ParameterStatus(padb_version = .)
16:44:01.231 (1)  <=BE ParameterStatus(padb_revision = )
16:44:01.231 (1)  <=BE ParameterStatus(max_numeric_precision = 38)
16:44:01.231 (1)  <=BE ParameterStatus(max_varchar_size = 65535)
16:44:01.231 (1)  <=BE BackendKeyData(pid=11899,ckey=1300355240)
16:44:01.231 (1)  <=BE ReadyForQuery(I)
16:44:01.231 (1)     compatible = 8.4
16:44:01.231 (1)     loglevel = 7
16:44:01.231 (1)     prepare threshold = 100
getConnection returning driver
16:44:01.243 (1) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@165973ea, maxRows=0, fetchSize=0, flags=17
16:44:01.243 (1)  FE=> Parse(stmt=null,query="SELECT *
FROM	dim_agent 
WHERE country_code = 'SG'
AND scd_is_current = 1
 ",oids={})
16:44:01.244 (1)  FE=> Bind(stmt=null,portal=null)
16:44:01.244 (1)  FE=> Describe(portal=null)
16:44:01.244 (1)  FE=> Execute(portal=null,limit=0)
16:44:01.244 (1)  FE=> Sync
16:44:01.325 (1)  <=BE ParseComplete 
16:44:01.326 (1)  <=BE BindComplete 
16:44:01.326 (1)  <=BE RowDescription(34)
16:44:01.327 (1)  <=BE DataRow
16:44:01.327 (1)  <=BE DataRow
16:44:01.328 (1)  <=BE DataRow
16:44:01.328 (1)  <=BE DataRow
16:44:01.329 (1)  <=BE DataRow
16:44:01.329 (1)  <=BE DataRow
...

After building the Job, the job never ends and the log redshift-jdbc.log looks like (showing all lines):

DriverManager.getConnection("jdbc:postgresql://xxxxx.cgnifbeifavu.ap-southeast-1.redshift.amazonaws.com:5439/dev?prepareThreshold=100&loglevel=7&protocolVersion=3")
    trying driver
16:22:05.516 (driver) Loading driver configuration via classloader sun.misc.Launcher$AppClassLoader@138d107f
16:22:05.517 (driver) Connecting with URL: jdbc:postgresql://xxxxx.cgnifbeifavu.ap-southeast-1.redshift.amazonaws.com:5439/dev?prepareThreshold=100&loglevel=7&protocolVersion=3
16:22:05.522 (1) PostgreSQL 8.4 JDBC4 (build 703)
16:22:05.525 (1) Trying to establish a protocol version 3 connection to xxxxx.cgnifbeifavu.ap-southeast-1.redshift.amazonaws.com:5439
16:22:05.800 (1)  FE=> StartupPacket(user=xxxxx, database=dev, client_encoding=UNICODE, DateStyle=ISO, extra_float_digits=2)
16:22:05.803 (1)  <=BE AuthenticationReqMD5(salt=1fe3d16b)
16:22:05.804 (1)  FE=> Password(md5digest=password)
16:22:05.813 (1)  <=BE AuthenticationOk
16:22:05.820 (1)  <=BE ParameterStatus(client_encoding = UNICODE)
16:22:05.820 (1)  <=BE ParameterStatus(datestyle = ISO, MDY)
16:22:05.820 (1)  <=BE ParameterStatus(gconf_case_sensitive = on)
16:22:05.820 (1)  <=BE ParameterStatus(integer_datetimes = on)
16:22:05.820 (1)  <=BE ParameterStatus(is_superuser = off)
16:22:05.820 (1)  <=BE ParameterStatus(server_encoding = UNICODE)
16:22:05.820 (1)  <=BE ParameterStatus(server_version = 8.0.2)
16:22:05.820 (1)  <=BE ParameterStatus(session_authorization = rsuser)
16:22:05.820 (1)  <=BE ParameterStatus(TimeZone = UTC)
16:22:05.821 (1)  <=BE ParameterStatus(timezone_abbreviations = Default)
16:22:05.821 (1)  <=BE ParameterStatus(padb_version = .)
16:22:05.821 (1)  <=BE ParameterStatus(padb_revision = )
16:22:05.821 (1)  <=BE ParameterStatus(max_numeric_precision = 38)
16:22:05.821 (1)  <=BE ParameterStatus(max_varchar_size = 65535)
16:22:05.821 (1)  <=BE BackendKeyData(pid=11169,ckey=1006961730)
16:22:05.821 (1)  <=BE ReadyForQuery(I)
16:22:05.821 (1)     compatible = 8.4
16:22:05.821 (1)     loglevel = 7
16:22:05.821 (1)     prepare threshold = 100
getConnection returning driver
16:22:05.834 (1) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@1d5a0305, maxRows=0, fetchSize=0, flags=17
16:22:05.834 (1)  FE=> Parse(stmt=null,query="SELECT *
FROM    dim_agent
WHERE country_code = 'SG'
AND scd_is_current = 1
 ",oids={})
16:22:05.835 (1)  FE=> Bind(stmt=null,portal=null)
16:22:05.835 (1)  FE=> Describe(portal=null)
16:22:05.835 (1)  FE=> Execute(portal=null,limit=0)
16:22:05.835 (1)  FE=> Sync

So seems like there is a problem when querying from the redshift table.

I will Appreciate some help.
Right now Im evaluating to use the postgres component (still didn't test) for querying the redshift tables.

Re: tRedshiftInput not working after building the Job

Anonymous — Tue, 08 Jul 2014 07:41:40 GMT

Hi,
What's your query in your second testing? Could you please show us some screenshots of your job design?
Best regards
Sabrina

Re: tRedshiftInput not working after building the Job

Anonymous — Tue, 08 Jul 2014 09:34:34 GMT

Hi Sabrina,
Thanks for you answer!
You can find attached the a screenshot of the job and other with the configuration of the tRedshiftInput component.
Note that the query is the same in both tests, I think its quite simple query, should not be any performance issue, it has around 5000 rows.

SELECT *
FROM	dim_agent 
WHERE country_code = 'MY'
AND scd_is_current = 1

The Job works perfectly when I run it from the DI studio, the problem is only after building the Job
The job is build with the options selected by default, you can find also an attached image with it.
Im developing from a OSX 10.9.4 and the DI version is 5.5.1 (also tried with pervious versions)
Please let me know if I can provide more info.
Thank you very much!
Regards,
Oscar

Re: tRedshiftInput not working after building the Job

Anonymous — Wed, 30 Jul 2014 05:37:32 GMT

Hi all,
I still didn't find a solution for this.
I tried using the Postgres component but Im still having the same problem (sometimes, issue doesn't seems to be consistent) after building the Job. Note that I never had this problem when running the job from the TOS_DI, it only happens after building.
I have the feeling that this may be related with the build functionality and only for cases where access to redshift is required.
Also not sure if OSX (last current version) is fully supported or tested with TOS_DI, Im thinking to start using different operating system to give a try.
Btw, (maybe out of topic) our server is running with Redhut and openJDK, should be more convenient to use the Oracle Java distribution instead?
Thanks!
Oscar

topic tRedshiftInput not working after building the Job in Talend Studio

tRedshiftInput not working after building the Job

Re: tRedshiftInput not working after building the Job

Re: tRedshiftInput not working after building the Job

Re: tRedshiftInput not working after building the Job