You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "Krisztian Kasa (Jira)" <ji...@apache.org> on 2021/06/09 04:00:00 UTC
[jira] [Updated] (HIVE-25220) Query with union fails CBO with OOM
[ https://issues.apache.org/jira/browse/HIVE-25220?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Krisztian Kasa updated HIVE-25220:
----------------------------------
Description:
{code}
explain cbo
with meod AS (select max(data_as_of_date) data_as_of_date from governed.cc_forecast_pnl),
daod as (select min(f.cob_date) data_as_of_date from governed.cc_forecast_pnl f, meod where f.data_as_of_date = meod.data_as_of_date),
me_rates as (
SELECT
refRateFX.to_currency_code,
refRateFX.from_currency_code,
cast(refRateFX.exchange_rate as decimal(38,18)) exchange_rate,
cast('GC2' AS string) currency_label
FROM
(SELECT MAX(fx.data_as_of_date) data_as_of_date
FROM governed.standard_fx_rates fx, daod
WHERE fx.data_as_of_date LIKE '%_MCR_MTD'
and fx.data_as_of_date <= concat(daod.data_as_of_date, '_MCR_MTD')) fx, -- get most recent rates not later than the delivery period
governed.standard_fx_rates refRateFX
WHERE refRateFX.data_as_of_date = fx.data_as_of_date
AND refRateFX.to_currency_code = 'USD'
UNION ALL
SELECT
refRateFX2.from_currency_code to_currency_code,
refRateFX1.from_currency_code,
cast(cast(refRateFX1.exchange_rate as double)/cast(refRateFX2.exchange_rate as double) as decimal(38,18)) exchange_rate,
CAST('GC1' AS string) currency_label
FROM
(SELECT MAX(fx.data_as_of_date) data_as_of_date
FROM governed.standard_fx_rates fx, daod
WHERE fx.data_as_of_date LIKE '%_MCR_MTD'
and fx.data_as_of_date <= concat(daod.data_as_of_date, '_MCR_MTD')) fx, -- get most recent rates not later than the delivery period
governed.standard_fx_rates refRateFX1,
governed.standard_fx_rates refRateFX2
WHERE refRateFX1.data_as_of_date = fx.data_as_of_date
AND refRateFX2.data_as_of_date = fx.data_as_of_date
AND refRateFX1.to_currency_code = 'USD'
AND refRateFX2.from_currency_code = 'CHF'
AND refRateFX2.to_currency_code = 'USD'
),
cc_func_hier_filter as(
SELECT DISTINCT LEVEL10 FUNCTION_CD
FROM GOVERNED.CC_CYBOS_HIER_FUNCTION
WHERE DATA_AS_OF_DATE in
(SELECT MAX(DATA_AS_OF_DATE) FROM GOVERNED.CC_CYBOS_HIER_FUNCTION)
AND LEVEL2='N14954'
),
cc_unified_acc_hier_filter as(
SELECT DISTINCT LEVEL14 GROUP_ACCOUNT_CD
FROM governed.cc_cybos_hier_acct
WHERE DATA_AS_OF_DATE in (SELECT MAX(DATA_AS_OF_DATE) FROM governed.cc_cybos_hier_acct)
AND LEVEL1='U00000' AND LEVEL6 = 'U52000'
),
cc_sign_reversal as(
SELECT DISTINCT LEVEL14 GROUP_ACCOUNT_CD, CAST(-1 AS DECIMAL(38,18)) reverse_sign
FROM governed.cc_cybos_hier_acct
WHERE DATA_AS_OF_DATE in (SELECT MAX(DATA_AS_OF_DATE) FROM governed.cc_cybos_hier_acct)
AND ((LEVEL1='U00000' AND LEVEL5 = 'U30175') OR (LEVEL2 = 'EAR90006'))
),
cc_unified_acc_hier as(
SELECT DISTINCT TRIM(level14) level14
FROM provision.cc_hier_unified_acct_vw
WHERE level5_desc = 'Total operating expense'
AND TRIM(level14) NOT IN
(SELECT group_account_cd FROM governed.cc_temp_reg_exclude_rules
WHERE data_as_of_date IN (SELECT MAX(data_as_of_date) from governed.cc_temp_reg_exclude_rules))
),
tempreg as(
SELECT function_cd, tt_cd
FROM governed.cc_temp_reg_rules
WHERE data_as_of_date IN (SELECT MAX(data_as_of_date) FROM governed.cc_temp_reg_rules)
),
gov as(
select cob_date, count(*) as gov_count, sum(case when measure_amt <> 0 then 1 else 0 end) gov_non_zero_count, sum(MEASURE_AMT) as gov_amt
from (
select pnl.cob_date,
CASE WHEN tr.function_cd IS NOT NULL AND h.level14 IS NOT NULL THEN tr.TT_CD ELSE NULL END AS PERFORMANCE_VIEW_TYPE,
pnl.company_code,
pnl.function_code,
pnl.group_account_code,
pnl.gaap_code,
'Actual Rate' AS CURRENCY_TYPE,
me.to_currency_code AS CURRENCY_CODE,
pnl.group_account_code MEASURE_ID,
sum(CAST(cast((cast(pnl.posting_lc_amt as double) * cast(NVL(sr.reverse_sign, 1) as double)) as double) * cast(me.exchange_rate as double) as decimal(38,18))) as MEASURE_AMT,
'FORECAST' AS PROJECTION_TYPE,
CASE WHEN GROUP_ACCOUNT_CODE LIKE 'EAR%' THEN 'RETAINED_EARNINGS' ELSE 'PNL' END AS MACRO_MEASURE,
me.currency_label AS MACRO_MEASURE_SUB_TYPE,
pnl.cob_date AS partition_date_key
from governed.cc_forecast_pnl pnl,
me_rates me
left outer join cc_func_hier_filter fHier
on pnl.function_code = fHier.FUNCTION_CD
left outer join cc_unified_acc_hier_filter aHier
on pnl.group_account_code = aHier.group_account_cd
left outer join cc_sign_reversal sr
on pnl.group_account_code = sr.group_account_cd
left outer join tempreg tr
on pnl.function_code = tr.function_cd
left outer join cc_unified_acc_hier h
on pnl.group_account_code = h.level14
WHERE me.from_currency_code = (CASE WHEN pnl.local_currency_code LIKE '____AR' THEN SUBSTR(pnl.local_currency_code, 1, 3) ELSE pnl.local_currency_code END) and data_as_of_date in
(select max(data_as_of_date) from governed.cc_forecast_pnl)
AND (fHier.FUNCTION_CD IS NOT NULL OR aHier.group_account_cd IS NOT NULL)
group by pnl.cob_date,CASE WHEN tr.function_cd IS NOT NULL AND h.level14 IS NOT NULL THEN tr.TT_CD ELSE NULL END,
pnl.company_code,pnl.function_code,pnl.group_account_code,pnl.gaap_code,me.to_currency_code,me.currency_label)a
group by cob_date
),
prov as(
select cob_date, count(*) as prov_count, sum(case when measure_amt <> 0 then 1 else 0 end) prov_non_zero_count, sum(MEASURE_AMT) as prov_amt
from (
select cob_date, performance_view_type, company_code, function_code, group_account_code, gaap_code, currency_type, currency_code, measure_id,
sum(measure_amt) as measure_amt, projection_type, macro_measure, macro_measure_sub_type, partition_date_key
from provision.corp_center_fact_merged
where macro_measure in ('PNL','RETAINED_EARNINGS') and projection_type = 'FORECAST' and currency_type = 'Actual Rate' and group_account_code <> 'TM1-5460' and data_as_of_date in (select max(data_as_of_date) from provision.corp_center_fact_merged
where macro_measure in ('PNL','RETAINED_EARNINGS') and projection_type = 'FORECAST' and currency_type = 'Actual Rate')
group by cob_date, performance_view_type, company_code, function_code, group_account_code, gaap_code, currency_type, currency_code, measure_id,
projection_type, macro_measure, macro_measure_sub_type, partition_date_key )b
group by cob_date
)
SELECT * from gov, prov
where gov.cob_date = prov.cob_date;
{code}
{code}
2021-06-08T08:15:14,450 ERROR [6241f234-77e0-4e63-9873-6eb9d655421c HiveServer2-Handler-Pool: Thread-79] parse.CalcitePlanner: CBO failed, skipping CBO.
java.lang.RuntimeException: java.lang.OutOfMemoryError: Java heap space
at org.apache.hadoop.hive.ql.parse.CalcitePlanner.rethrowCalciteException(CalcitePlanner.java:1728) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hadoop.hive.ql.parse.CalcitePlanner.logicalPlan(CalcitePlanner.java:1564) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hadoop.hive.ql.parse.CalcitePlanner.genOPTree(CalcitePlanner.java:538) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:12680) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hadoop.hive.ql.parse.CalcitePlanner.analyzeInternal(CalcitePlanner.java:428) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:288) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hadoop.hive.ql.parse.ExplainSemanticAnalyzer.analyzeInternal(ExplainSemanticAnalyzer.java:170) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:288) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hadoop.hive.ql.Compiler.analyze(Compiler.java:221) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hadoop.hive.ql.Compiler.compile(Compiler.java:104) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:188) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:600) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:546) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:540) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hadoop.hive.ql.reexec.ReExecDriver.compileAndRespond(ReExecDriver.java:127) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:199) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:260) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hive.service.cli.operation.Operation.run(Operation.java:274) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:565) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:551) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_262]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_262]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_262]
at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_262]
at org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:78) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hive.service.cli.session.HiveSessionProxy.access$000(HiveSessionProxy.java:36) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hive.service.cli.session.HiveSessionProxy$1.run(HiveSessionProxy.java:63) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at java.security.AccessController.doPrivileged(Native Method) ~[?:1.8.0_262]
at javax.security.auth.Subject.doAs(Subject.java:422) ~[?:1.8.0_262]
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1729) ~[hadoop-common-3.1.1.jar:?]
at org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:59) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at com.sun.proxy.$Proxy39.executeStatementAsync(Unknown Source) ~[?:?]
at org.apache.hive.service.cli.CLIService.executeStatementAsync(CLIService.java:315) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:567) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1557) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1542) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.hive.service.auth.TSetIpAddressProcessor.process(TSetIpAddressProcessor.java:56) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:286) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_262]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_262]
at java.lang.Thread.run(Thread.java:748) [?:1.8.0_262]
Caused by: java.lang.OutOfMemoryError: Java heap space
at java.util.Arrays.copyOf(Arrays.java:3332) ~[?:1.8.0_262]
at java.lang.AbstractStringBuilder.ensureCapacityInternal(AbstractStringBuilder.java:124) ~[?:1.8.0_262]
at java.lang.AbstractStringBuilder.append(AbstractStringBuilder.java:448) ~[?:1.8.0_262]
at java.lang.StringBuilder.append(StringBuilder.java:136) ~[?:1.8.0_262]
at java.lang.StringBuilder.append(StringBuilder.java:131) ~[?:1.8.0_262]
at org.apache.calcite.rex.RexCall.appendOperands(RexCall.java:109) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
at org.apache.calcite.rex.RexCall.computeDigest(RexCall.java:166) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
at org.apache.calcite.rex.RexCall.toString(RexCall.java:183) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
at org.apache.calcite.rex.RexCall.hashCode(RexCall.java:268) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
at java.util.HashMap.hash(HashMap.java:339) ~[?:1.8.0_262]
at java.util.HashMap.put(HashMap.java:612) ~[?:1.8.0_262]
at java.util.HashSet.add(HashSet.java:220) ~[?:1.8.0_262]
at java.util.AbstractCollection.addAll(AbstractCollection.java:344) ~[?:1.8.0_262]
at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:459) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
{code}
> Query with union fails CBO with OOM
> -----------------------------------
>
> Key: HIVE-25220
> URL: https://issues.apache.org/jira/browse/HIVE-25220
> Project: Hive
> Issue Type: Bug
> Components: CBO
> Reporter: Krisztian Kasa
> Assignee: Krisztian Kasa
> Priority: Major
> Fix For: 4.0.0
>
>
> {code}
> explain cbo
> with meod AS (select max(data_as_of_date) data_as_of_date from governed.cc_forecast_pnl),
> daod as (select min(f.cob_date) data_as_of_date from governed.cc_forecast_pnl f, meod where f.data_as_of_date = meod.data_as_of_date),
> me_rates as (
> SELECT
> refRateFX.to_currency_code,
> refRateFX.from_currency_code,
> cast(refRateFX.exchange_rate as decimal(38,18)) exchange_rate,
> cast('GC2' AS string) currency_label
> FROM
> (SELECT MAX(fx.data_as_of_date) data_as_of_date
> FROM governed.standard_fx_rates fx, daod
> WHERE fx.data_as_of_date LIKE '%_MCR_MTD'
> and fx.data_as_of_date <= concat(daod.data_as_of_date, '_MCR_MTD')) fx, -- get most recent rates not later than the delivery period
> governed.standard_fx_rates refRateFX
> WHERE refRateFX.data_as_of_date = fx.data_as_of_date
> AND refRateFX.to_currency_code = 'USD'
> UNION ALL
> SELECT
> refRateFX2.from_currency_code to_currency_code,
> refRateFX1.from_currency_code,
> cast(cast(refRateFX1.exchange_rate as double)/cast(refRateFX2.exchange_rate as double) as decimal(38,18)) exchange_rate,
> CAST('GC1' AS string) currency_label
> FROM
> (SELECT MAX(fx.data_as_of_date) data_as_of_date
> FROM governed.standard_fx_rates fx, daod
> WHERE fx.data_as_of_date LIKE '%_MCR_MTD'
> and fx.data_as_of_date <= concat(daod.data_as_of_date, '_MCR_MTD')) fx, -- get most recent rates not later than the delivery period
> governed.standard_fx_rates refRateFX1,
> governed.standard_fx_rates refRateFX2
> WHERE refRateFX1.data_as_of_date = fx.data_as_of_date
> AND refRateFX2.data_as_of_date = fx.data_as_of_date
> AND refRateFX1.to_currency_code = 'USD'
> AND refRateFX2.from_currency_code = 'CHF'
> AND refRateFX2.to_currency_code = 'USD'
> ),
> cc_func_hier_filter as(
> SELECT DISTINCT LEVEL10 FUNCTION_CD
> FROM GOVERNED.CC_CYBOS_HIER_FUNCTION
> WHERE DATA_AS_OF_DATE in
> (SELECT MAX(DATA_AS_OF_DATE) FROM GOVERNED.CC_CYBOS_HIER_FUNCTION)
> AND LEVEL2='N14954'
> ),
> cc_unified_acc_hier_filter as(
> SELECT DISTINCT LEVEL14 GROUP_ACCOUNT_CD
> FROM governed.cc_cybos_hier_acct
> WHERE DATA_AS_OF_DATE in (SELECT MAX(DATA_AS_OF_DATE) FROM governed.cc_cybos_hier_acct)
> AND LEVEL1='U00000' AND LEVEL6 = 'U52000'
> ),
> cc_sign_reversal as(
> SELECT DISTINCT LEVEL14 GROUP_ACCOUNT_CD, CAST(-1 AS DECIMAL(38,18)) reverse_sign
> FROM governed.cc_cybos_hier_acct
> WHERE DATA_AS_OF_DATE in (SELECT MAX(DATA_AS_OF_DATE) FROM governed.cc_cybos_hier_acct)
> AND ((LEVEL1='U00000' AND LEVEL5 = 'U30175') OR (LEVEL2 = 'EAR90006'))
> ),
> cc_unified_acc_hier as(
> SELECT DISTINCT TRIM(level14) level14
> FROM provision.cc_hier_unified_acct_vw
> WHERE level5_desc = 'Total operating expense'
> AND TRIM(level14) NOT IN
> (SELECT group_account_cd FROM governed.cc_temp_reg_exclude_rules
> WHERE data_as_of_date IN (SELECT MAX(data_as_of_date) from governed.cc_temp_reg_exclude_rules))
> ),
> tempreg as(
> SELECT function_cd, tt_cd
> FROM governed.cc_temp_reg_rules
> WHERE data_as_of_date IN (SELECT MAX(data_as_of_date) FROM governed.cc_temp_reg_rules)
> ),
> gov as(
> select cob_date, count(*) as gov_count, sum(case when measure_amt <> 0 then 1 else 0 end) gov_non_zero_count, sum(MEASURE_AMT) as gov_amt
> from (
> select pnl.cob_date,
> CASE WHEN tr.function_cd IS NOT NULL AND h.level14 IS NOT NULL THEN tr.TT_CD ELSE NULL END AS PERFORMANCE_VIEW_TYPE,
> pnl.company_code,
> pnl.function_code,
> pnl.group_account_code,
> pnl.gaap_code,
> 'Actual Rate' AS CURRENCY_TYPE,
> me.to_currency_code AS CURRENCY_CODE,
> pnl.group_account_code MEASURE_ID,
> sum(CAST(cast((cast(pnl.posting_lc_amt as double) * cast(NVL(sr.reverse_sign, 1) as double)) as double) * cast(me.exchange_rate as double) as decimal(38,18))) as MEASURE_AMT,
> 'FORECAST' AS PROJECTION_TYPE,
> CASE WHEN GROUP_ACCOUNT_CODE LIKE 'EAR%' THEN 'RETAINED_EARNINGS' ELSE 'PNL' END AS MACRO_MEASURE,
> me.currency_label AS MACRO_MEASURE_SUB_TYPE,
> pnl.cob_date AS partition_date_key
> from governed.cc_forecast_pnl pnl,
> me_rates me
> left outer join cc_func_hier_filter fHier
> on pnl.function_code = fHier.FUNCTION_CD
> left outer join cc_unified_acc_hier_filter aHier
> on pnl.group_account_code = aHier.group_account_cd
> left outer join cc_sign_reversal sr
> on pnl.group_account_code = sr.group_account_cd
> left outer join tempreg tr
> on pnl.function_code = tr.function_cd
> left outer join cc_unified_acc_hier h
> on pnl.group_account_code = h.level14
> WHERE me.from_currency_code = (CASE WHEN pnl.local_currency_code LIKE '____AR' THEN SUBSTR(pnl.local_currency_code, 1, 3) ELSE pnl.local_currency_code END) and data_as_of_date in
> (select max(data_as_of_date) from governed.cc_forecast_pnl)
> AND (fHier.FUNCTION_CD IS NOT NULL OR aHier.group_account_cd IS NOT NULL)
> group by pnl.cob_date,CASE WHEN tr.function_cd IS NOT NULL AND h.level14 IS NOT NULL THEN tr.TT_CD ELSE NULL END,
> pnl.company_code,pnl.function_code,pnl.group_account_code,pnl.gaap_code,me.to_currency_code,me.currency_label)a
> group by cob_date
> ),
> prov as(
> select cob_date, count(*) as prov_count, sum(case when measure_amt <> 0 then 1 else 0 end) prov_non_zero_count, sum(MEASURE_AMT) as prov_amt
> from (
> select cob_date, performance_view_type, company_code, function_code, group_account_code, gaap_code, currency_type, currency_code, measure_id,
> sum(measure_amt) as measure_amt, projection_type, macro_measure, macro_measure_sub_type, partition_date_key
> from provision.corp_center_fact_merged
> where macro_measure in ('PNL','RETAINED_EARNINGS') and projection_type = 'FORECAST' and currency_type = 'Actual Rate' and group_account_code <> 'TM1-5460' and data_as_of_date in (select max(data_as_of_date) from provision.corp_center_fact_merged
> where macro_measure in ('PNL','RETAINED_EARNINGS') and projection_type = 'FORECAST' and currency_type = 'Actual Rate')
> group by cob_date, performance_view_type, company_code, function_code, group_account_code, gaap_code, currency_type, currency_code, measure_id,
> projection_type, macro_measure, macro_measure_sub_type, partition_date_key )b
> group by cob_date
> )
> SELECT * from gov, prov
> where gov.cob_date = prov.cob_date;
> {code}
> {code}
> 2021-06-08T08:15:14,450 ERROR [6241f234-77e0-4e63-9873-6eb9d655421c HiveServer2-Handler-Pool: Thread-79] parse.CalcitePlanner: CBO failed, skipping CBO.
> java.lang.RuntimeException: java.lang.OutOfMemoryError: Java heap space
> at org.apache.hadoop.hive.ql.parse.CalcitePlanner.rethrowCalciteException(CalcitePlanner.java:1728) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hadoop.hive.ql.parse.CalcitePlanner.logicalPlan(CalcitePlanner.java:1564) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hadoop.hive.ql.parse.CalcitePlanner.genOPTree(CalcitePlanner.java:538) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:12680) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hadoop.hive.ql.parse.CalcitePlanner.analyzeInternal(CalcitePlanner.java:428) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:288) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hadoop.hive.ql.parse.ExplainSemanticAnalyzer.analyzeInternal(ExplainSemanticAnalyzer.java:170) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:288) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hadoop.hive.ql.Compiler.analyze(Compiler.java:221) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hadoop.hive.ql.Compiler.compile(Compiler.java:104) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:188) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:600) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:546) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:540) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hadoop.hive.ql.reexec.ReExecDriver.compileAndRespond(ReExecDriver.java:127) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:199) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:260) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hive.service.cli.operation.Operation.run(Operation.java:274) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:565) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:551) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_262]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_262]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_262]
> at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_262]
> at org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:78) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hive.service.cli.session.HiveSessionProxy.access$000(HiveSessionProxy.java:36) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hive.service.cli.session.HiveSessionProxy$1.run(HiveSessionProxy.java:63) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at java.security.AccessController.doPrivileged(Native Method) ~[?:1.8.0_262]
> at javax.security.auth.Subject.doAs(Subject.java:422) ~[?:1.8.0_262]
> at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1729) ~[hadoop-common-3.1.1.jar:?]
> at org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:59) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at com.sun.proxy.$Proxy39.executeStatementAsync(Unknown Source) ~[?:?]
> at org.apache.hive.service.cli.CLIService.executeStatementAsync(CLIService.java:315) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:567) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1557) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1542) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.hive.service.auth.TSetIpAddressProcessor.process(TSetIpAddressProcessor.java:56) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:286) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_262]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_262]
> at java.lang.Thread.run(Thread.java:748) [?:1.8.0_262]
> Caused by: java.lang.OutOfMemoryError: Java heap space
> at java.util.Arrays.copyOf(Arrays.java:3332) ~[?:1.8.0_262]
> at java.lang.AbstractStringBuilder.ensureCapacityInternal(AbstractStringBuilder.java:124) ~[?:1.8.0_262]
> at java.lang.AbstractStringBuilder.append(AbstractStringBuilder.java:448) ~[?:1.8.0_262]
> at java.lang.StringBuilder.append(StringBuilder.java:136) ~[?:1.8.0_262]
> at java.lang.StringBuilder.append(StringBuilder.java:131) ~[?:1.8.0_262]
> at org.apache.calcite.rex.RexCall.appendOperands(RexCall.java:109) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
> at org.apache.calcite.rex.RexCall.computeDigest(RexCall.java:166) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
> at org.apache.calcite.rex.RexCall.toString(RexCall.java:183) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
> at org.apache.calcite.rex.RexCall.hashCode(RexCall.java:268) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
> at java.util.HashMap.hash(HashMap.java:339) ~[?:1.8.0_262]
> at java.util.HashMap.put(HashMap.java:612) ~[?:1.8.0_262]
> at java.util.HashSet.add(HashSet.java:220) ~[?:1.8.0_262]
> at java.util.AbstractCollection.addAll(AbstractCollection.java:344) ~[?:1.8.0_262]
> at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:459) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
> at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
> at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
> at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
> at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
> at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
> at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
> at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
> at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
> at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
> at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
> at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
> at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
> at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
> at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
> at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
> at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
> at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
> at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1]
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)