Ah, I see your point!! It's expected.
We don't need "lookuptable2.col3" because its value always equals to "facttable.col3". Note "facttable.col3" is already pulled. On Fri, Jan 8, 2016 at 5:41 AM, Zhang, Zhong <[email protected]> wrote: > Hi Yang, > > > > The generated SQL query for supplement. > > > > SELECT > > ... > > ,facttable.col3 > > ... > > ,lookuptable2.h1 > > ,lookuptable2.h2 > > ,lookuptable2.h3 > > ,lookuptable2.h4 > > ,lookuptable2.h5 > > FROM FACT.facttable as facttable > > INNER JOIN FACT.lookuptable1 as lookuptable1 > > ON facttable.col6 = lookuptable1.p1 > > INNER JOIN FACT.lookuptable2 as lookuptable2 > > ON facttable.col3 = lookuptable2.col3 > > > > There is no field “col3” in lookuptable2. > > > > Best regards, > > Zhong > > > > *From:* Zhang, Zhong [mailto:[email protected]] > *Sent:* Thursday, January 07, 2016 4:25 PM > *To:* [email protected] > *Subject:* RE: missing a field in the SQL when creating cube > > > > 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] <[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]> 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]> 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]> > 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 > > > > > > > > >
