You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@kylin.apache.org by Ankur Kapoor <an...@gmail.com> on 2016/11/08 09:33:44 UTC
Duplicate Column! Error coming while executing the query- Using
Kyline 1.6 Snapshot version
Hi,
I am executing the following query on my cube ( Kyline 1.6 Snapshot
version):
select distinct count(*) from fulfill_order fo left join
fulfill_order_status fos on ( fo.fulfill_order_id = fos.fulfill_order_id
and fo.fulfill_order_status_code = fos.fulfill_order_status_code
and fo.last_update_ts= fos.status_update_timestamp
) where fo.fulfill_order_status_code in (7,8) and fo.pick_due_ts >=
fo.last_update_ts;
This is giving me the following error below:
/Error while executing SQL "select distinct count(*) from fulfill_order fo
left join fulfill_order_status fos on ( fo.fulfill_order_id =
fos.fulfill_order_id and fo.fulfill_order_status_code =
fos.fulfill_order_status_code and fo.last_update_ts=
fos.status_update_timestamp ) where fo.fulfill_order_status_code in (7,8)
and fo.pick_due_ts >= fo.last_update_ts LIMIT 50000": Duplicate columns! old
is PICK_DUE_TS and new is LAST_UPDATE_TS/
Following is the JSON of my Model which is getting used :
**********************************************************
{
"uuid": "b7713db6-0e6e-423f-a806-c70e1ba3cc3f",
"last_modified": 1478585626966,
"version": "1.6.0",
"name": "PickDueDate",
"owner": "ADMIN",
"description": "Pick Due Date modeling for Kylin",
"fact_table": "DEFAULT.FULFILL_ORDER",
"lookups": [
{
"table": "DEFAULT.FULFILL_ORDER_STATUS",
"join": {
"type": "left",
"primary_key": [
"FULFILL_ORDER_ID",
"FULFILL_ORDER_STATUS_CODE",
"STATUS_UPDATE_TIMESTAMP"
],
"foreign_key": [
"FULFILL_ORDER_ID",
"FULFILL_ORDER_STATUS_CODE",
"LAST_UPDATE_TS"
]
}
}
],
"dimensions": [
{
"table": "DEFAULT.FULFILL_ORDER",
"columns": [
"FULFILL_ORDER_ID",
"ALT_CONTACT_PICKUP_IND",
"BASKET_ID",
"CREATE_TS",
"CREATE_USERID",
"DSPNS_TO_FIRST_NAME",
"DSPNS_TO_LAST_NAME",
"DSPNS_TO_MIDDLE_NAME",
"DSPNS_TYPE_CD",
"EARLIEST_ORDER_FULFILL_TS",
"FULFILL_ORDER_DSPNS_USERID",
"FULFILL_ORDER_DISPNS_TS",
"FULFILL_ORDER_RELEASE_TS",
"FULFILL_ORDER_STATUS_CODE",
"INSERT_TS",
"LAST_UPDATE_TS",
"LAST_UPDATE_USERID",
"ORD_FULFILL_STATUS_RSN_TXT",
"ORDER_DUE_TS",
"PICK_DUE_TS",
"POS_TRANSACT_NBR",
"REGION_NAME",
"SRC_COUNTRY_CODE",
"SRC_ESTIMATED_SHIP_TS",
"SRC_FULFILL_STORE_NBR",
"FULFILL_TYPE_CODE",
"SRC_ORDER_CUST_CNFRM_IND",
"SRC_PROGRAM_NAME",
"SRC_FULFILL_ORDER_ID",
"TOTAL_SALE_AMT",
"TOTAL_TAX_AMT",
"SRC_PKG_ID"
]
},
{
"table": "DEFAULT.FULFILL_ORDER_STATUS",
"columns": [
"FULFILL_ORDER_ID",
"FULFILL_ORDER_STATUS_CODE",
"STATUS_UPDATE_TIMESTAMP",
"INSERT_TS"
]
}
],
"metrics": [
"FULFILL_ORDER_ID",
"CREATE_TS",
"EARLIEST_ORDER_FULFILL_TS",
"FULFILL_ORDER_DSPNS_USERID",
"INSERT_TS",
"LAST_UPDATE_TS",
"ORDER_DUE_TS",
"PICK_DUE_TS",
"TOTAL_SALE_AMT",
"TOTAL_TAX_AMT"
],
"filter_condition": "",
"partition_desc": {
"partition_date_column": null,
"partition_time_column": null,
"partition_date_start": 0,
"partition_date_format": "yyyy-MM-dd",
"partition_time_format": "HH:mm:ss",
"partition_type": "APPEND",
"partition_condition_builder":
"org.apache.kylin.metadata.model.PartitionDesc$DefaultPartitionConditionBuilder"
},
"capacity": "MEDIUM"
}
**********************************************************
Following is the json of my Cube which is getting used :
**********************************************************
{
"uuid": "83b85622-71ea-4db9-902c-9d97f48e55fd",
"last_modified": 1478593909517,
"version": "1.6.0",
"name": "PickKeyCube",
"model_name": "PickDueDate",
"description": "",
"null_string": null,
"dimensions": [
{
"name": "FULFILL_ORDER_ID",
"table": "DEFAULT.FULFILL_ORDER",
"column": "FULFILL_ORDER_ID",
"derived": null
},
{
"name": "PICK_DUE_TS",
"table": "DEFAULT.FULFILL_ORDER",
"column": "PICK_DUE_TS",
"derived": null
},
{
"name": "FULFILL_ORDER_STATUS_CODE",
"table": "DEFAULT.FULFILL_ORDER_STATUS",
"column": null,
"derived": [
"FULFILL_ORDER_STATUS_CODE"
]
},
{
"name": "STATUS_UPDATE_TIMESTAMP",
"table": "DEFAULT.FULFILL_ORDER_STATUS",
"column": null,
"derived": [
"STATUS_UPDATE_TIMESTAMP"
]
}
],
"measures": [
{
"name": "_COUNT_",
"function": {
"expression": "COUNT",
"parameter": {
"type": "constant",
"value": "1",
"next_parameter": null
},
"returntype": "bigint"
},
"dependent_measure_ref": null
},
{
"name": "DISTINCT COUNT",
"function": {
"expression": "COUNT_DISTINCT",
"parameter": {
"type": "column",
"value": "FULFILL_ORDER_ID",
"next_parameter": null
},
"returntype": "hllc(12)"
},
"dependent_measure_ref": null
}
],
"dictionaries": [],
"rowkey": {
"rowkey_columns": [
{
"column": "FULFILL_ORDER_ID",
"encoding": "dict",
"isShardBy": false
},
{
"column": "PICK_DUE_TS",
"encoding": "time",
"isShardBy": false
},
{
"column": "FULFILL_ORDER_STATUS_CODE",
"encoding": "dict",
"isShardBy": false
},
{
"column": "LAST_UPDATE_TS",
"encoding": "time",
"isShardBy": false
}
]
},
"hbase_mapping": {
"column_family": [
{
"name": "F1",
"columns": [
{
"qualifier": "M",
"measure_refs": [
"_COUNT_"
]
}
]
},
{
"name": "F2",
"columns": [
{
"qualifier": "M",
"measure_refs": [
"DISTINCT COUNT"
]
}
]
}
]
},
"aggregation_groups": [
{
"includes": [
"FULFILL_ORDER_ID",
"PICK_DUE_TS",
"FULFILL_ORDER_STATUS_CODE",
"LAST_UPDATE_TS"
],
"select_rule": {
"hierarchy_dims": [],
"mandatory_dims": [],
"joint_dims": []
}
}
],
"signature": "183jVyBU+hGDoXO6ydljxw==",
"notify_list": [],
"status_need_notify": [
"ERROR",
"DISCARDED",
"SUCCEED"
],
"partition_date_start": 0,
"partition_date_end": 3153600000000,
"auto_merge_time_ranges": [
604800000,
2419200000
],
"retention_range": 0,
"engine_type": 2,
"storage_type": 2,
"override_kylin_properties": {}
}
***********************************************************
Any clue what is wrong with my Query/Model/Cube . Please help.
--
View this message in context: http://apache-kylin.74782.x6.nabble.com/Duplicate-Column-Error-coming-while-executing-the-query-Using-Kyline-1-6-Snapshot-version-tp6230.html
Sent from the Apache Kylin mailing list archive at Nabble.com.
Re: 答复: Duplicate Column! Error coming while executing the query- Using Kyline 1.6 Snapshot version
Posted by Li Yang <li...@apache.org>.
Em.. this worth a JIRA. https://issues.apache.org/jira/browse/KYLIN-2206
On Wed, Nov 9, 2016 at 8:18 PM, roger shi <ro...@hotmail.com>
wrote:
> Hi Ankur,
>
> Kylin doesn't support this kind of query " ... where fo.pick_due_ts >=
> fo.last_update_ts ... ". You work around by creating view of the origin
> table with a new column, for example larger_pick. The column's value is 1
> for pick_due_ts >= last_update_ts, otherwise the value is 0.
>
> Your query can be this:
> select distinct count(*) from fulfill_order fo left join
> fulfill_order_status fos on ( fo.fulfill_order_id = fos.fulfill_order_id
> and fo.fulfill_order_status_code = fos.fulfill_order_status_code
> and fo.last_update_ts= fos.status_update_timestamp
> ) where fo.fulfill_order_status_code in (7,8) and fo.larger_pick = 1;
> ________________________________
> 发件人: Ankur Kapoor <an...@gmail.com>
> 发送时间: 2016年11月9日 12:33:06
> 收件人: dev@kylin.apache.org
> 主题: Re: Duplicate Column! Error coming while executing the query- Using
> Kyline 1.6 Snapshot version
>
> Guys, please help me on this, I am kind of stuck!
>
> --
> View this message in context: http://apache-kylin.74782.x6.
> nabble.com/Duplicate-Column-Error-coming-while-executing-
> the-query-Using-Kyline-1-6-Snapshot-version-tp6230p6237.html
> Sent from the Apache Kylin mailing list archive at Nabble.com.
>
答复: Duplicate Column! Error coming while executing the query- Using Kyline 1.6 Snapshot version
Posted by roger shi <ro...@hotmail.com>.
Hi Ankur,
Kylin doesn't support this kind of query " ... where fo.pick_due_ts >= fo.last_update_ts ... ". You work around by creating view of the origin table with a new column, for example larger_pick. The column's value is 1 for pick_due_ts >= last_update_ts, otherwise the value is 0.
Your query can be this:
select distinct count(*) from fulfill_order fo left join
fulfill_order_status fos on ( fo.fulfill_order_id = fos.fulfill_order_id
and fo.fulfill_order_status_code = fos.fulfill_order_status_code
and fo.last_update_ts= fos.status_update_timestamp
) where fo.fulfill_order_status_code in (7,8) and fo.larger_pick = 1;
________________________________
发件人: Ankur Kapoor <an...@gmail.com>
发送时间: 2016年11月9日 12:33:06
收件人: dev@kylin.apache.org
主题: Re: Duplicate Column! Error coming while executing the query- Using Kyline 1.6 Snapshot version
Guys, please help me on this, I am kind of stuck!
--
View this message in context: http://apache-kylin.74782.x6.nabble.com/Duplicate-Column-Error-coming-while-executing-the-query-Using-Kyline-1-6-Snapshot-version-tp6230p6237.html
Sent from the Apache Kylin mailing list archive at Nabble.com.
Re: Duplicate Column! Error coming while executing the query- Using
Kyline 1.6 Snapshot version
Posted by Ankur Kapoor <an...@gmail.com>.
Guys, please help me on this, I am kind of stuck!
--
View this message in context: http://apache-kylin.74782.x6.nabble.com/Duplicate-Column-Error-coming-while-executing-the-query-Using-Kyline-1-6-Snapshot-version-tp6230p6237.html
Sent from the Apache Kylin mailing list archive at Nabble.com.