kylin-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Zhang, Zhong" <zzh...@cardlytics.com>
Subject RE: missing a field in the SQL when creating cube
Date Thu, 07 Jan 2016 21:41:30 GMT
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:zzhang@cardlytics.com]
Sent: Thursday, January 07, 2016 4:25 PM
To: user@kylin.apache.org
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:liyang@apache.org]
Sent: Thursday, January 07, 2016 3:03 AM
To: Li Yang <liyang@apache.org<mailto:liyang@apache.org>>
Cc: user@kylin.apache.org<mailto:user@kylin.apache.org>; Jones, James <jjones@cardlytics.com<mailto:jjones@cardlytics.com>>
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 <liyang@apache.org<mailto:liyang@apache.org>>
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 <luke.hq@gmail.com<mailto:luke.hq@gmail.com>>
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 <zzhang@cardlytics.com<mailto:zzhang@cardlytics.com>>
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




Mime
View raw message