Hello,
When using a tOracleInput in a Spark Batch Job, not only the Query provided (in the Query field) is executed but also a query that uses the Table Name field of the tOracleInput.
Therefore, it the Table Name contains an inadequate value (for instance XXX), a query against
the wrong value will be sent to Oracle Database Server (here , it will be select * from XXX where 1=0) .
In order to identify which query is leading to ORA-00942,
an Oracle Event can be set at Oracle database server :
alter system set events '942 trace name errorstack level 1';
The solution consists of ensuring that the Table Name contains
a correct name that can be accessed using the connection configured
in the Spark Batch .
As example, here are the steps done to access the emp table under Oracle scott schema :
A/ In Talend Studio Metadata :
1/ Create an Oracle database connection
(just check that you can access from your hadoop cluster)
2/ Create Hadoop Cluster
3/ Create Hdfs connection under the hadoop cluster
B/ Into Talend Studio designer panel :
4/ Drag and Drop from MetaData Hdfs Connection and choose tHDFSConfiguration
5/ Drag and Drop from MetaData Oracle DbConnection and choose tOracleConfiguration
6/ Add tOracleInput
Basic settings : Use existing connection select tOracleConfiguration connection
Table Name = "EMP"
Query : "SELECT EMPNO, ENAME FROM EMP"
Schema : Edit schema
Enter EMPNO column (integer)
ENAME column (String)
7/ add tLogRow component
Connect tOracleInput and tLogRow .
8/ Click on Run tab
Spark configuration :
- for the cluster : chosse repository and the metdata hdfs connection
- Check Define the driver hostname or Ip address (in my case , i have set the ip address of my desktop)
- Check define the hadoop home directory "C:/1_Thoan/Samples/Test_QA/winutils"
where is bin/winutils.exe
Please feel free to let us know it helps.
Best regards
Sabrina
