Dataphin即席查询报错“Metering data exceed max value”

问题描述

Dataphin即席查询报错“Metering data exceed max value”。

Tracking Os Job [1/1] Process:
--------------------------------Original SQL(Formatted)------------------------------
SELECT ds, unique_id, uid, template_id, join_id
 , mobile_sha256, message_id, business_event_id, business_plan_id, account_code
 , template_code, template_version, sms_content, sms_sign, channel_code
 , status, sms_format_type, sms_content_type, recieve_status, recieve_code
 , recieve_status_comments, received_provider, has_url_flag, tiny_url, click_time
 , comments, message_length, sms_count, cost, crt_time
 , crt_by, upd_time, upd_by, rcd_time, crt_date
 , is_poll, poll_sms_task_id, is_last_poll
FROM LD_LOAN.FCT_OP_SMS_SEND_DF
WHERE ds = '20220302'
LIMIT 10
--------------------------------Original SQL(Formatted)------------------------------
Translating to Physical SQL .....................................................Done
------------------------------------Physical SQL-------------------------------------
SELECT DS, UNIQUE_ID, UID, TEMPLATE_ID, JOIN_ID
 , MOBILE_SHA256, MESSAGE_ID, BUSINESS_EVENT_ID, BUSINESS_PLAN_ID, ACCOUNT_CODE
 , TEMPLATE_CODE, TEMPLATE_VERSION, SMS_CONTENT, SMS_SIGN, CHANNEL_CODE
 , STATUS, SMS_FORMAT_TYPE, SMS_CONTENT_TYPE, RECIEVE_STATUS, RECIEVE_CODE
 , RECIEVE_STATUS_COMMENTS, RECEIVED_PROVIDER, HAS_URL_FLAG, TINY_URL, CLICK_TIME
 , COMMENTS, MESSAGE_LENGTH, SMS_COUNT, COST, CRT_TIME
 , CRT_BY, UPD_TIME, UPD_BY, RCD_TIME, CRT_DATE
 , IS_POLL, POLL_SMS_TASK_ID, IS_LAST_POLL
FROM (
 SELECT CHANNEL_CODE AS CHANNEL_CODE, SMS_CONTENT AS SMS_CONTENT, BUSINESS_EVENT_ID AS BUSINESS_EVENT_ID, CLICK_TIME AS CLICK_TIME, UNIQUE_ID AS UNIQUE_ID
  , TEMPLATE_VERSION AS TEMPLATE_VERSION, DS AS DS, ACCOUNT_CODE AS ACCOUNT_CODE, UID AS UID
  , ddSelfMask(TINY_URL, '1,0') AS TINY_URL, SMS_SIGN AS SMS_SIGN
  , JOIN_ID AS JOIN_ID, MOBILE_SHA256 AS MOBILE_SHA256, RECIEVE_STATUS AS RECIEVE_STATUS, TEMPLATE_CODE AS TEMPLATE_CODE, RCD_TIME AS RCD_TIME
  , CRT_DATE AS CRT_DATE, RECEIVED_PROVIDER AS RECEIVED_PROVIDER, BUSINESS_PLAN_ID AS BUSINESS_PLAN_ID, MESSAGE_LENGTH AS MESSAGE_LENGTH
  , ddBankCard(MESSAGE_ID) AS MESSAGE_ID, IS_POLL AS IS_POLL
  , ddSelfMask(COST, '1,0') AS COST, HAS_URL_FLAG AS HAS_URL_FLAG
  , ddSelfMask(UPD_BY, '1,0') AS UPD_BY, SMS_CONTENT_TYPE AS SMS_CONTENT_TYPE
  , UPD_TIME AS UPD_TIME, SMS_COUNT AS SMS_COUNT, POLL_SMS_TASK_ID AS POLL_SMS_TASK_ID, COMMENTS AS COMMENTS, STATUS AS STATUS
  , CRT_BY AS CRT_BY, ddBankCard(RECIEVE_STATUS_COMMENTS) AS RECIEVE_STATUS_COMMENTS, IS_LAST_POLL AS IS_LAST_POLL, RECIEVE_CODE AS RECIEVE_CODE
  , CRT_TIME AS CRT_TIME, SMS_FORMAT_TYPE AS SMS_FORMAT_TYPE, TEMPLATE_ID AS TEMPLATE_ID
 FROM DP_CDM_PROD.FCT_OP_SMS_SEND_DF_OD000_V1
 WHERE FCT_OP_SMS_SEND_DF_OD000_V1.DS >= '20120305'
  AND FCT_OP_SMS_SEND_DF_OD000_V1.DS <= '20220302'
) FCT_OP_SMS_SEND_DF
WHERE DS = '20220302'
LIMIT 10
------------------------------------Physical SQL-------------------------------------

waiting...
com.aliyun.odps.OdpsException: Metering data exceed max value. Input: 146.167008GB, Complexity: 1.0, Max Value: 67
 at com.aliyun.odps.Instance.waitForSuccess(Instance.java:797)
 at com.aliyun.odps.Instance.waitForSuccess(Instance.java:774)
 at com.alibaba.dt.oneservice.sdk.OdpsTask.runTask(OdpsTask.java:112)
 at com.alibaba.dt.oneservice.sdk.task.BaseOsTask.run(BaseOsTask.java:111)
 at com.alibaba.dt.oneservice.sdk.job.OsJob.run(OsJob.java:185)
 at com.alibaba.dt.oneservice.sdk.executor.BaseOsExecutor.lambda$run$0(BaseOsExecutor.java:42)
 at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
 at java.util.concurrent.FutureTask.run(FutureTask.java:266)
 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1152)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:627)
 at java.lang.Thread.run(Thread.java:882)
Job failed
2022-03-03 17:52:35 No outputData produced.

问题原因

 目前Dataphin如果表开了脱敏，生成的内层过滤条件确实是这样的。

从发出来的 logview，外层ds的过滤，是已经上推到内层里。MaxCompute SQL的物理计划里只有一个分区，仅仅这一个分区的数据量就超过限制了。目前对一个分区limit 10进行数据探查，分区粒度已经足够小，但是某个分区数据量特别大还是会导致该问题。

解决方案

• 除非要改表结构，比如 hash cluster 表且查询条件能够命中 cluster by 的 column 过滤，能够减少一些输入，要不然输入数据还是这么大。
• 放松单SQL的费用限制，如果用户想跑这条SQL，需要调高SQL费用限制。

更多信息

消费监控告警

适用于

• Dataphin