Table not found error in TSQL component

Highlighted
Seven Stars

Table not found error in TSQL component

Hi,

 

I have created a Bigdata joblet as shown below

Joblet.png

 

I have written a custom query in the Tsql as shown below

 

 

"SELECT inEnrichedOrdShp.*,CAST(inBuisnessAreaDivisionMapping.BusinessAreaHierarchyID AS STRING),inBuisnessAreaHierarchy.ValVolumeInd,inBuisnessAreaHierarchy.BusinessGroupCode LkpBusinessGroupCode,
inBuisnessAreaHierarchy.ExternalSegmentCode,inBuisnessAreaHierarchy.GlobalBusinessUnitCode LkpGlobalBusinessUnitCode,inBuisnessAreaHierarchy.FinanceProductCategoryCode,inBuisnessAreaHierarchy1.BusinessAreaCode LkpBusinessAreaCode,
inProductHierarchy.TypeCode,inProductHierarchy.LineCode,inProductHierarchy.FamilyCode
FROM inEnrichedOrdShp LEFT OUTER JOIN
inBuisnessAreaDivisionMapping ON
inEnrichedOrdShp.Product_Line = inBuisnessAreaDivisionMapping.DivisionCode AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,"+"'dd-MM-yy'"+") >= unix_timestamp(inBuisnessAreaDivisionMapping.StartDate,"+"'dd-MM-yy'"+") AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,"+"'dd-MM-yy'"+") <= unix_timestamp(inBuisnessAreaDivisionMapping.EndDate,"+"'dd-MM-yy'"+")
LEFT OUTER JOIN inBuisnessAreaHierarchy ON
inEnrichedOrdShp.Business_Area_Code = inBuisnessAreaHierarchy.BusinessAreaCode AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,"+"'dd-MM-yy'"+") >= unix_timestamp(inBuisnessAreaHierarchy.StartDate,"+"'dd-MM-yy'"+") AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,"+"'dd-MM-yy'"+") <= unix_timestamp(inBuisnessAreaHierarchy.EndDate,"+"'dd-MM-yy'"+")
LEFT OUTER JOIN inProductHierarchy ON
inEnrichedOrdShp.Product_Part_Number = inProductHierarchy.SKUCode AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,"+"'dd-MM-yy'"+") >= unix_timestamp(inProductHierarchy.StartDate,"+"'dd-MM-yy'"+") AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,"+"'dd-MM-yy'"+") <= unix_timestamp(inProductHierarchy.EndDate,"+"'dd-MM-yy'"+")
LEFT OUTER JOIN inBuisnessAreaHierarchy inBuisnessAreaHierarchy1 ON
inBuisnessAreaDivisionMapping.BusinessAreaHierarchyID = inBuisnessAreaHierarchy1.BusinessAreaHierarchyID AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,"+"'dd-MM-yy'"+") >= unix_timestamp(inBuisnessAreaHierarchy1.StartDate,"+"'dd-MM-yy'"+") AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,"+"'dd-MM-yy'"+") <= unix_timestamp(inBuisnessAreaHierarchy1.EndDate,"+"'dd-MM-yy'"+")"

 

 

 

I am using this joblet in a big data job as shown below

 

mainJob.png

 But when i run the job I am getting table not found error

 

Parsing command: SELECT inEnrichedOrdShp.*,CAST(inBuisnessAreaDivisionMapping.BusinessAreaHierarchyID AS STRING) BusinessAreaHierarchyID,inBuisnessAreaHierarchy.ValVolumeInd,inBuisnessAreaHierarchy.BusinessGroupCode LkpBusinessGroupCode,
inBuisnessAreaHierarchy.ExternalSegmentCode,inBuisnessAreaHierarchy.GlobalBusinessUnitCode LkpGlobalBusinessUnitCode,inBuisnessAreaHierarchy.FinanceProductCategoryCode,inBuisnessAreaHierarchy1.BusinessAreaCode LkpBusinessAreaCode,
inProductHierarchy.TypeCode,inProductHierarchy.LineCode,inProductHierarchy.FamilyCode
FROM inEnrichedOrdShp LEFT OUTER JOIN
inBuisnessAreaDivisionMapping ON
inEnrichedOrdShp.Product_Line = inBuisnessAreaDivisionMapping.DivisionCode AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') >= unix_timestamp(inBuisnessAreaDivisionMapping.StartDate,'dd-MM-yy') AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') <= unix_timestamp(inBuisnessAreaDivisionMapping.EndDate,'dd-MM-yy')
LEFT OUTER JOIN inBuisnessAreaHierarchy ON
inEnrichedOrdShp.Business_Area_Code = inBuisnessAreaHierarchy.BusinessAreaCode AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') >= unix_timestamp(inBuisnessAreaHierarchy.StartDate,'dd-MM-yy') AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') <= unix_timestamp(inBuisnessAreaHierarchy.EndDate,'dd-MM-yy')
LEFT OUTER JOIN inProductHierarchy ON
inEnrichedOrdShp.Product_Part_Number = inProductHierarchy.SKUCode AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') >= unix_timestamp(inProductHierarchy.StartDate,'dd-MM-yy') AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') <= unix_timestamp(inProductHierarchy.EndDate,'dd-MM-yy')
LEFT OUTER JOIN inBuisnessAreaHierarchy inBuisnessAreaHierarchy1 ON
inBuisnessAreaDivisionMapping.BusinessAreaHierarchyID = inBuisnessAreaHierarchy1.BusinessAreaHierarchyID AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') >= unix_timestamp(inBuisnessAreaHierarchy1.StartDate,'dd-MM-yy') AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') <= unix_timestamp(inBuisnessAreaHierarchy1.EndDate,'dd-MM-yy')
17/07/26 17:26:40 INFO ParseDriver: Parse Completed
org.apache.spark.sql.AnalysisException: Table not found: inEnrichedOrdShp; line 4 pos 5
	at org.apache.spark.sql.catalyst.analysis.package$AnalysisErrorAt.failAnalysis(package.scala:42)
	at org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveRelations$.getTable(Analyzer.scala:305)
	at org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveRelations$$anonfun$apply$9.applyOrElse(Analyzer.scala:314)

 

At first i Thought this is some spelling mistake .But its not. The link name is correct. 

 

Any idea why this is happening?

 

PS: I have copied the joblet code into a big data job and it is working fine. when The code is put  in the joblet, its is not working.

Seven Stars

Re: Table not found error in TSQL component

I have managed to solve the error which I am getting

 

1.JPG2.JPG

 

Give same name to the row link(input) in main job and the row link in the joblet.

 

 

Now I am getting a new error. If you see the my joblet mapping it contains lookups. TSQL component is not able to identify the lookup table .

 

The following is the error

 

17/07/28 11:33:24 INFO ParseDriver: Parsing command: SELECT inEnrichedOrdShp.*,CAST(inBuisnessAreaDivisionMapping.BusinessAreaHierarchyID AS STRING) BusinessAreaHierarchyID,inBuisnessAreaHierarchy.ValVolumeInd,inBuisnessAreaHierarchy.BusinessGroupCode LkpBusinessGroupCode,
inBuisnessAreaHierarchy.ExternalSegmentCode,inBuisnessAreaHierarchy.GlobalBusinessUnitCode LkpGlobalBusinessUnitCode,inBuisnessAreaHierarchy.FinanceProductCategoryCode,inBuisnessAreaHierarchy1.BusinessAreaCode LkpBusinessAreaCode,
inProductHierarchy.TypeCode,inProductHierarchy.LineCode,inProductHierarchy.FamilyCode
FROM inEnrichedOrdShp LEFT OUTER JOIN
inBuisnessAreaDivisionMapping ON
inEnrichedOrdShp.Product_Line = inBuisnessAreaDivisionMapping.DivisionCode AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') >= unix_timestamp(inBuisnessAreaDivisionMapping.StartDate,'dd-MM-yy') AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') <= unix_timestamp(inBuisnessAreaDivisionMapping.EndDate,'dd-MM-yy')
LEFT OUTER JOIN inBuisnessAreaHierarchy ON
inEnrichedOrdShp.Business_Area_Code = inBuisnessAreaHierarchy.BusinessAreaCode AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') >= unix_timestamp(inBuisnessAreaHierarchy.StartDate,'dd-MM-yy') AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') <= unix_timestamp(inBuisnessAreaHierarchy.EndDate,'dd-MM-yy')
LEFT OUTER JOIN inProductHierarchy ON
inEnrichedOrdShp.Product_Part_Number = inProductHierarchy.SKUCode AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') >= unix_timestamp(inProductHierarchy.StartDate,'dd-MM-yy') AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') <= unix_timestamp(inProductHierarchy.EndDate,'dd-MM-yy')
LEFT OUTER JOIN inBuisnessAreaHierarchy inBuisnessAreaHierarchy1 ON
inBuisnessAreaDivisionMapping.BusinessAreaHierarchyID = inBuisnessAreaHierarchy1.BusinessAreaHierarchyID AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') >= unix_timestamp(inBuisnessAreaHierarchy1.StartDate,'dd-MM-yy') AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') <= unix_timestamp(inBuisnessAreaHierarchy1.EndDate,'dd-MM-yy')
17/07/28 11:33:25 INFO ParseDriver: Parse Completed
org.apache.spark.sql.AnalysisException: Table not found: inBuisnessAreaDivisionMapping; line 5 pos 0
	at org.apache.spark.sql.catalyst.analysis.package$AnalysisErrorAt.failAnalysis(package.scala:42)

 

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

Put Massive Amounts of Data to Work

Learn how to make your data more available, reduce costs and cut your build time

Watch Now

How OTTO Utilizes Big Data to Deliver Personalized Experiences

Read about OTTO's experiences with Big Data and Personalized Experiences

Blog

Talend Integration with Databricks

Take a look at this video about Talend Integration with Databricks

Watch Now