Hi Yang,
Thanks so much for your reply.
Let me give a little bit introduction. There is a column “col3” in the
facttable and lookuptable2.
“col3” is the primary key and foreign key in the lookuptable2. “col3” is also
the missing field
in the SQL query.
JSON(Cube):
{
"uuid": "14834977-626c-4f24-8d78-adf73cbf8cae",
"name": "missing_a_field_cube",
"description": "",
"dimensions": [
{
"id": 1,
"name": "col1",
"table": "FACT.facttable",
"column": [
"col1"
],
"derived": null,
"hierarchy": false
},
{
"id": 2,
"name": "col2",
"table": "FACT.facttable",
"column": [
"col2"
],
"derived": null,
"hierarchy": false
},
{
"id": 3,
"name": "col3",
"table": "FACT.facttable",
"column": [
"col3"
],
"derived": null,
"hierarchy": false
},
{
"id": 4,
"name": "col4",
"table": "FACT.facttable",
"column": [
"col4"
],
"derived": null,
"hierarchy": false
},
{
"id": 5,
"name": "col5",
"table": "FACT.facttable",
"column": [
"col5"
],
"derived": null,
"hierarchy": false
},
{
"id": 6,
"name": "col6",
"table": "FACT.facttable",
"column": [
"col6"
],
"derived": null,
"hierarchy": false
},
{
"id": 7,
"name": "col7",
"table": "FACT.facttable",
"column": [
"col7"
],
"derived": null,
"hierarchy": false
},
{
"id": 8,
"name": "col8",
"table": "FACT.facttable",
"column": [
"col8"
],
"derived": null,
"hierarchy": false
},
{
"id": 9,
"name": "col9",
"table": "FACT.facttable",
"column": [
"col9"
],
"derived": null,
"hierarchy": false
},
{
"id": 10,
"name": "col10",
"table": "FACT.facttable",
"column": [
"col10"
],
"derived": null,
"hierarchy": false
},
{
"id": 11,
"name": "L_col1",
"table": "FACT.lookuptable1",
"column": [
"L_col1"
],
"derived": null,
"hierarchy": false
},
{
"id": 12,
"name": "hier_dim",
"table": "FACT.lookuptable2",
"column": [
"col3",
"h1",
"h2",
"h3",
"h4",
"h5"
],
"derived": null,
"hierarchy": true
}
],
"measures": [
{
"id": 1,
"name": "_COUNT_",
"function": {
"expression": "COUNT",
"parameter": {
"type": "constant",
"value": "1"
},
"returntype": "bigint"
},
"dependent_measure_ref": null
},
{
"id": 2,
"name": "SUMcol9",
"function": {
"expression": "SUM",
"parameter": {
"type": "column",
"value": "col9"
},
"returntype": "bigint"
},
"dependent_measure_ref": null
},
{
"id": 3,
"name": "SUMcol10",
"function": {
"expression": "SUM",
"parameter": {
"type": "column",
"value": "col10"
},
"returntype": "bigint"
},
"dependent_measure_ref": null
}
],
"rowkey": {
"rowkey_columns": [
{
"column": "col1",
"length": 0,
"dictionary": "true",
"mandatory": false
},
{
"column": "col2",
"length": 0,
"dictionary": "true",
"mandatory": false
},
{
"column": "col3",
"length": 0,
"dictionary": "true",
"mandatory": false
},
{
"column": "col4",
"length": 0,
"dictionary": "true",
"mandatory": false
},
{
"column": "col5",
"length": 0,
"dictionary": "true",
"mandatory": false
},
{
"column": "col6",
"length": 0,
"dictionary": "true",
"mandatory": false
},
{
"column": "col7",
"length": 0,
"dictionary": "true",
"mandatory": false
},
{
"column": "col8",
"length": 0,
"dictionary": "true",
"mandatory": false
},
{
"column": "col9",
"length": 0,
"dictionary": "true",
"mandatory": false
},
{
"column": "col10",
"length": 0,
"dictionary": "true",
"mandatory": false
},
{
"column": "l_col1",
"length": 0,
"dictionary": "true",
"mandatory": false
},
{
"column": "h1",
"length": 0,
"dictionary": "true",
"mandatory": false
},
{
"column": "h2",
"length": 0,
"dictionary": "true",
"mandatory": false
},
{
"column": "h3",
"length": 0,
"dictionary": "true",
"mandatory": false
},
{
"column": "h4",
"length": 0,
"dictionary": "true",
"mandatory": false
},
{
"column": "h5",
"length": 0,
"dictionary": "true",
"mandatory": false
}
],
"aggregation_groups": [
[
"col1",
"col2",
"col4",
"col5",
"col6",
"col7",
"col8",
"col9",
"col10",
"l_col1"
],
[
"col3",
"h1",
"h2",
"h3",
"h4",
"h5"
]
]
},
"signature": "ZGOjdHfxChhWpdXXAIbImQ==",
"last_modified": 1452199211425,
"model_name": "missing_a_field_cube",
"null_string": null,
"hbase_mapping": {
"column_family": [
{
"name": "F1",
"columns": [
{
"qualifier": "M",
"measure_refs": [
"_COUNT_",
"SUMcol9",
"SUMcol10"
]
}
]
}
]
},
"notify_list": [],
"auto_merge_time_ranges": [
604800000,
2419200000
],
"retention_range": 0
}
========================================
JSON(Model):
{
"uuid": null,
"name": "missing_a_field_cube",
"lookups": [
{
"table": "FACT.lookuptable1",
"join": {
"type": "inner",
"primary_key": [
"p1"
],
"foreign_key": [
"col6"
]
}
},
{
"table": "FACT.lookuptable2",
"join": {
"type": "inner",
"primary_key": [
"col3"
],
"foreign_key": [
"col3"
]
}
}
],
"capacity": "LARGE",
"last_modified": 1452199211170,
"fact_table": "FACT.facttable",
"filter_condition": "",
"partition_desc": {
"partition_date_column": "FACT.facttable.SPT_DATE",
"partition_date_start": 1325289600000,
"partition_type": "APPEND",
"partition_condition_builder":
"org.apache.kylin.metadata.model.PartitionDesc$DefaultPartitionConditionBuilder"
}
}
Best regards,
Zhong
From: Li Yang [mailto:[email protected]]
Sent: Thursday, January 07, 2016 3:03 AM
To: Li Yang <[email protected]>
Cc: [email protected]; Jones, James <[email protected]>
Subject: Re: missing a field in the SQL when creating cube
I mean need the two json files "model json and cube json" to further
troubleshoot.
On Thu, Jan 7, 2016 at 4:02 PM, Li Yang
<[email protected]<mailto:[email protected]>> wrote:
Em... derived dimensions are not in extraction hive SQL, but hierarchy
dimensions should appear.
If you could both the model json and cube json files, we could try to reproduce
and debug.
On Wed, Jan 6, 2016 at 8:16 AM, Luke Han
<[email protected]<mailto:[email protected]>> wrote:
it is by design, hierarchy/derived dimension will not be included in generated
SQL which read data from Hive, which associate with PK/FK.
Thanks.
Best Regards!
---------------------
Luke Han
On Wed, Jan 6, 2016 at 3:07 AM, Zhang, Zhong
<[email protected]<mailto:[email protected]>> wrote:
Hi All,
I’ve encountered a kind of weird problem. I just normally created a cube step
by step.
Based on my understanding, after filling all the information in cube info, data
model,
dimensions, and etc, a SQL will be generated in the SQL column. The following
is the
generated SQL:
SELECT
...
SPENDTRIPINC.POSTALCD
,FLATGEO2.TOWN
,FLATGEO2.DMA_CODE
,FLATGEO2.PROVINCECD
,FLATGEO2.REGIONNAME
,FLATGEO2.MSA
...
FROM FACT.SPENDTRIPINC as SPENDTRIPINC
INNER JOIN FACT.CALENDARDATES as CALENDARDATES
ON SPENDTRIPINC.DIMDATEKEY = CALENDARDATES.CALENDARDAYID
INNER JOIN FACT.FLATGEO2 as FLATGEO2
ON SPENDTRIPINC.POSTALCD = FLATGEO2.POSTALCD
I do put fields "POSTALCD","TOWN","DMA_CODE","PROVINCECD","REGIONNAME" and "MSA"
from table FLATGEO2 as a hierarchy dimension. There should be a filed POSTALCD
in the SQL. But unfortunately it is not there just as you see.
The weird thing is that if checking at JSON(cube) column, POSTALCD is there:
{
"id": 12,
"name": "FACTGEO",
"table": "FACT.FLATGEO2",
"column": [
"POSTALCD",
"TOWN",
"DMA_CODE",
"PROVINCECD",
"REGIONNAME",
"MSA"
],
"derived": null,
"hierarchy": true
}
Best regards,
Zhong
