Problem Description

A Job is designed with a tSqoopImport component to import data from Oracle to HDFS. The Job hangs without errors or warnings.

Root Cause

Running the jstack utility on the Job process to collect the stack trace, shows that the read call of Oracle is active all the time:

"main" prio=6 tid=0x0000000001375000 nid=0x9ac4 runnable [0x00000000012ce000]
java.lang.Thread.State: RUNNABLE
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(SocketInputStream.java:152)
at java.net.SocketInputStream.read(SocketInputStream.java:122)
at oracle.net.ns.Packet.receive(Packet.java:311)
at oracle.net.ns.DataPacket.receive(DataPacket.java:105)
at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:305)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:249)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:171)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:89)
at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:123)
at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:79)
at oracle.jdbc.driver.T4CMAREngineStream.unmarshalUB1(T4CMAREngineStream.java:426)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:390)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:202)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:45)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:766)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1244)
- locked <0x00000000ef836ca0> (a oracle.jdbc.driver.T4CConnection)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:420)

Solution

The Job is waiting on an Oracle read call. This is an Oracle database performance issue and is not related to Talend, contact your Database Administrator for assistance.

Note:

To further isolate the issue with database and Talend server components, perform the following tests:

1. Use the telnet, ping, and traceroute commands from the Talend Job server to the database server, and verify that the communication with the database is healthy and remove any latency issues.

2. Verify that there are no firewall issues. There could be idle sockets established by JDBC connections to the database, which could lead to the socket used by the JDBC driver not closing.

3. Check the blocking sessions at the database level by using a v$session table. The following query returns a list of active blocking sessions and the sessions that they are blocking:

   select blocking_session,sid,serial#, wait_class,seconds_in_wait from v$session where blocking_session is not NULL order by blocking_session;

4. Reproduce this outside of Talend by running SQL queries on the Job server machine. You can run the queries using the Oracle sqlplus utility.

Problem Description

When executing a Job with a tHiveLoad component, the following error is observed in the Job log:

Error while compiling statement: FAILED: SemanticException [Error 10028]: Line 1:17 Path is not legal ''/user/talend/loader/X_CUSTOMER_FRMK_POC_1000_INSERT'': Move from: hdfs://HOSTNAME: 8020/user/talend/loader/X_CUSTOMER_FRMK_POC_1000_INSERT to: hdfs://ttspoc/user/hive/warehouse/giw_framework.db/talend_hive_test1 is not valid. Please check that values for params "default.fs.name" and "hive.metastore.warehouse.dir" do not conflict.

Root Cause

The values for the Hadoop properties: default.fs.name and hive.metastore.warehouse.dir in the tHiveConnection component are incorrect.

Solution

In the tHiveConnection component, set the Hadoop properties default.fs.name and hive.metastore.warehouse.dir with the correct values. You can find the correct values in the *-site.xml files of the Hadoop cluster.

Problem Description

The output file generated by the tHMapFile component in Spark batch Job has an extra newline character after every record.

Root Cause

Creating a Flat representation adds an __osdtTerminator element at the end of the file. Also, in a Big Data environment, each output record is seen as its own file by the Flat representation. As a result, you get an extra newline character.

Solution

Remove the __osdtTerminatorelement from the file representation and clear the Output as delimited? check box in Flat representation.

Overview

This article explains how to use Talend Hive components in MapR Spark Batch Jobs to read from Hive MapR-DB tables. As MapR provides the ability to query MapR-DB tables through a Hive View, this article also covers how to set up Talend Jobs to read from a Hive View of MapR-DB table.

Environment

• Talend Studio 6.5.1
• MapR 6.0.1

Prerequisites

Setting up MapR

1. Set up the MapR Client 6.0.1 to connect with your MapR cluster on the system you are using to run your Job. For more information on setting up a MapR Client, see the MapR 6.1 documentation, Installing the MapR Client page.

2. After setting up the MapR Client, generate a MapR ticket that your Job can utilize to communicate with the cluster:

   

    

Setting up Studio

1. Ensure that your Studio has access to all of the Cluster nodes, and that they can reach back to your Studio per the Spark Security documentation, since Talend utilizes the YARN-Client paradigm that has the Spark driver spun up at the same location as the Job it is run from.

2. Configure the Hadoop Cluster connection in metadata in Studio.

   1. Right-click Hadoop Cluster, then click Create Hadoop Cluster.

   2. Select the distribution and version of your Hadoop cluster, then select Import configuration from local files. Click Next.

      

       

   3. Ensure your system has a local copy of the hive-site.xml, mapred-site.xml and yarn-site.xml files to import in to the Hadoop metadata wizard.

   4. Import the cluster configuration files.

      

       

   5. Notice that after the configuration files are imported, not all of the information on the next screen is populated, and it gives you a warning that the Resource Manager needs to be specified. This is because there are no specific hostnames included in the configuration files for the Resource Manager and CLDB nodes. You need them though later in this article, as they contain properties that will help with utilizing the Resource Manager HA.

   6. To fully utilize the CLDB and Resource Manager HA, complete the wizard as shown below:

      

       

   7. Once the cluster information is populated, click Check Services to ensure that Studio can connect successfully to the cluster.

      

       

Building the Job

1. Right-click Job Designs, click Create Big Data Batch Job, then give it a name.

2. From the Hadoop Cluster connection you created earlier, drag the HDFS connection to the canvas, then select to enter a tHDFSConfiguration component. Notice that it populates in right away, and in the Run tab, the Spark Configuration information is completed for you. This information tells the Job how to communicate with Spark.

   

    

3. Again, using the Hadoop Cluster connection you created earlier, drag the Hive Connection to the canvas, then select to enter a tHiveConfiguration component.

   

    

4. For each of the following libraries, use a tLibraryLoad component referencing each one. The Hive components use these libraries to retrieve the data from the Hive view of your MapR-DB table:

1. hbase-common-1.1.8-mapr-1710.jar

2. hbase-client-1.1.8-mapr-1710.jar

3. hbase-server-1.1.8-mapr-1710.jar

4. hbase-spark-1.1.8-mapr-1710.jar

5. hbase-protocol-1.1.1-mapr-1710.jar

6. hive-hbase-handler-2.1.1-mapr-1710.jar

7. mapr-hbase-6.0.1-mapr.jar

8. maprdb-6.0.1-mapr.jar

5. Add a tHiveInput component and configure it to read from the Hive View of your MapR-DB table.

   

    

6. Configure this component to output the values of the table to a tLogRow to ensure you can successfully read the table.

   

    

7. The complete Job should look like this:

   

    

Running the Job

Run the Job to see if you successfully connected to the Hive View, and can read the MapR-DB table data.

Additional notes

The same Job design will work for MapR 5.2.0 and above.

You can utilize MapR 6.0.1 in Talend 6.5.1 through a patch, available from Talend Support, that adds it as a supported version.

A Talend Standard Job uses a tOracleInput component to execute the following SQL query:

"select * from actions where d_majenreg <= TO_DATE('2018-03-21 15:54:26' ,'yyyy-mm-dd HH24:MI:SS')
and d_majenreg > TO_DATE('2018-03-21 15:43:57' ,'yyyy-mm-dd HH24:MI:SS')",
rows are returned.

Cause

The query in the tOracleInput component within a Big Data Spark Job is not an Oracle SQL query, but a Spark SQL query that parses by Spark SQL framework. Moreover, the used the to_date () function in an Oracle SQL query has no equivalent in Spark SQL. Therefore, using the to_date() function gives unexpected results in a Talend Spark Job that uses a tOracleInput component.

Solution

Use Spark SQL functions, for example, from_unixtime and unix_timestamp, as shown below:

" select * from action as where d_majenreg <= from_unixtime(unix_timestamp('2018-03-21 15:54:26' ,'yyyy-MM-dd HH:mm:ss'))
and d_majenreg >  from_unixtime(unix_timestamp('2018-03-21 15:43:57','yyyy-MM-dd HH:mm:ss'))".

Problem

When using a tHiveInput component in a standard Job, a query containing lowercase/uppercase column names returns all columns, even if the schema only contains lowercase column names. The same query in tHiveInput with the same schema does not return the column with the name given in uppercase in the query when run in a Big Data Batch.

Root Cause

This is a known issue and Hive is not at fault here. Hive is not case sensitive and always uses lowercase independently of the case used in the Studio. However, the difference between a DI Job and a Spark Job is the use of Avro for Spark.

With Hive, you can request fields with any case but Spark, uses Avro and it is case sensitive. Moreover, Avro field names are created with the Hive query case but fields are retrieved with Studio case in other components.

This means that if in your Hive query field names are not the same as the Studio schema, they are retrieved from Hive, but not from the Avro payload.

Example:

1. Studio schema: col1, COL2

2. In Hive, it gives: col1, col2

3. Back in Studio with a Hive query of "SELECT col1, col2 FROM ..." retrieves col1, col2 columns

4. Creation of an Avro payload with col1, col2 fields

5. Then Studio tries to find col1, COL2 fields

6. col1, null

Doing it the Avro way:

1. Studio schema: col1, COL2

2. In Hive, it gives: col1, col2

3. Back in Studio with a Hive query of "SELECT col1, COL2 FROM ..." retrieves col1, col2 columns

4. Creation of an Avro payload with col1, COL2 fields

5. Then Studio tries to find col1, COL2 fields

6. col1, COL2

Solution

In a Spark Job, the case for column names in the query must match case in the schema used.

Workaround

The other workaround is to use only lowercase letters.

Problem Description

Beeline and Hive connectivity causes a connection reset error in Studio logs.

Solution

There are two possible solutions:

1. Configure timeout length for client and server:

   1. Client-side configuration:

      In your Job, change the timeout length for the client-side configuration by adding the following parameter to your Hive connection:

      hive.metastore.client.socket.timeout

      This is measured in seconds, and should be set to a value longer than the duration of each individual Hive connection query. 60 minutes is a good starting point.

   2. Server-side configurations:

      hive.server.thrift.socket.timeout=1000
      hive.client.thrift.socket.timeout=1000

2. If the resolution above does not work, perform the following steps:

   1. Back up the Hive database.
   2. Remove Hive from the node.
   3. Add a new node and install Hive.
   4. Restore the Hive database.

Problem Description

When compiling a Talend 6.4.1 Spark Hive Job with schema BigDecimal type columns, because the generated Job Java code contains local variables (here, the local variable is scale_3) that have been declared several times, it fails with the following message:

Duplicate local variable scale_3

In this case, the batch Spark Job contains a tUniqRow component with two links to a tHiveOutput component:

tUniqRow > (uniques) > tHiveOutput

tUniqRow > (duplicates) > tHiveOutput

The components schema contains BigDecimal type columns.

Note: This problem does not occur when using a non-BigDecimal column type in the components schema.

Root Cause

This is a known bug.

Solution

From Talend Studio 6.4.1 apply the patch: Patch_20170804_TPS-2077_v1-6.4.1, available from Talend Support.

To apply the patch manually, perform the steps indicated in the readme.txt included in the patch file (Patch_20170804_TPS-2077_v1-6.4.1.zip).

Problem Description

When using more than one tELTHiveOutput component in a Talend 6.5.1 standard Job, the Job cannot be built, and fails with the following compilation error:

"
org.talend.commons.exception.PersistenceException: org.talend.designer.runprocess.ProcessorException: Job compile errors
At least job "J00200_PRO_CDA_CONTACT2" has a compile errors, please fix and export again.
Error Line: 1051
Detail Message: Duplicate local variable queryIdentifier
"

Root Cause

Checking the generated code show the variables dateStrFormat and queryIdentifier are duplicated:

"
java.text.DateFormat dateStrFormat = new java.text.SimpleDateFormat(
"yyyyMMddHHmmss");
final String queryIdentifier = projectName + "_" + jobName
+ "" + jobVersion.replace(".", "")
+ "tELTHiveOutput_2"
+ dateStrFormat.format(new Date(startTime));
"

Solution

This issue is fixed.

Against Talend 6.5.1, the solution consists of applying Patch_20180712_TPS-2591_v1-6.5.1.zip.

1. Contact Talend Support to request Patch_20180712_TPS-2591_v1-6.5.1.zip.
2. Use the patch Readme file steps (embedded in the patch zip file) to apply the patch.