Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have created a Bigdata joblet as shown below
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
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.
I have managed to solve the error which I am getting
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)
