KYLIN-1875 Rollback sample data back to star schema
Project: http://git-wip-us.apache.org/repos/asf/kylin/repo Commit: http://git-wip-us.apache.org/repos/asf/kylin/commit/ee47f857 Tree: http://git-wip-us.apache.org/repos/asf/kylin/tree/ee47f857 Diff: http://git-wip-us.apache.org/repos/asf/kylin/diff/ee47f857 Branch: refs/heads/yang22 Commit: ee47f8572941735c443d034b548e4494f70f749e Parents: 7699973 Author: Li Yang <liy...@apache.org> Authored: Tue Dec 6 15:09:51 2016 +0800 Committer: Li Yang <liy...@apache.org> Committed: Tue Dec 6 15:09:51 2016 +0800 ---------------------------------------------------------------------- examples/sample_cube/create_sample_tables.sql | 77 +- .../sample_cube/data/DEFAULT.KYLIN_ACCOUNT.csv | 10000 --------- .../sample_cube/data/DEFAULT.KYLIN_COUNTRY.csv | 244 - .../sample_cube/data/DEFAULT.KYLIN_SALES.csv | 20000 ++++++++--------- .../template/cube_desc/kylin_sales_cube.json | 157 +- .../template/model_desc/kylin_sales_model.json | 50 +- .../template/table/DEFAULT.KYLIN_ACCOUNT.json | 28 - .../table/DEFAULT.KYLIN_CATEGORY_GROUPINGS.json | 4 +- .../template/table/DEFAULT.KYLIN_COUNTRY.json | 24 - .../template/table/DEFAULT.KYLIN_SALES.json | 22 +- 10 files changed, 10081 insertions(+), 20525 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/kylin/blob/ee47f857/examples/sample_cube/create_sample_tables.sql ---------------------------------------------------------------------- diff --git a/examples/sample_cube/create_sample_tables.sql b/examples/sample_cube/create_sample_tables.sql index bdfc4d7..bc78802 100644 --- a/examples/sample_cube/create_sample_tables.sql +++ b/examples/sample_cube/create_sample_tables.sql @@ -20,11 +20,11 @@ DROP TABLE IF EXISTS DEFAULT.KYLIN_CAL_DT; CREATE TABLE DEFAULT.KYLIN_CAL_DT ( -CAL_DT date COMMENT 'Date, PK' -,YEAR_BEG_DT date COMMENT 'YEAR Begin Date' -,QTR_BEG_DT date COMMENT 'Quarter Begin Date' -,MONTH_BEG_DT date COMMENT 'Month Begin Date' -,WEEK_BEG_DT date COMMENT 'Week Begin Date' +CAL_DT date +,YEAR_BEG_DT date +,QTR_BEG_DT date +,MONTH_BEG_DT date +,WEEK_BEG_DT date ,AGE_FOR_YEAR_ID smallint ,AGE_FOR_QTR_ID smallint ,AGE_FOR_MONTH_ID smallint @@ -121,7 +121,6 @@ CAL_DT date COMMENT 'Date, PK' ,KYLIN_CAL_DT_UPD_DATE string ,KYLIN_CAL_DT_UPD_USER string ) -COMMENT 'Date Dimension Table' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE; @@ -129,28 +128,28 @@ DROP TABLE IF EXISTS DEFAULT.KYLIN_CATEGORY_GROUPINGS; CREATE TABLE DEFAULT.KYLIN_CATEGORY_GROUPINGS ( -LEAF_CATEG_ID bigint COMMENT 'Category ID, PK' +LEAF_CATEG_ID bigint ,LEAF_CATEG_NAME string -,SITE_ID int COMMENT 'Site ID, PK' +,SITE_ID int ,CATEG_BUSN_MGR string ,CATEG_BUSN_UNIT string ,REGN_CATEG string -,USER_DEFINED_FIELD1 string COMMENT 'User Defined Field1' -,USER_DEFINED_FIELD3 string COMMENT 'User Defined Field3' +,USER_DEFINED_FIELD1 string +,USER_DEFINED_FIELD3 string ,KYLIN_GROUPINGS_CRE_DATE string -,KYLIN_GROUPINGS_UPD_DATE string COMMENT 'Last Updated Date' +,KYLIN_GROUPINGS_UPD_DATE string ,KYLIN_GROUPINGS_CRE_USER string -,KYLIN_GROUPINGS_UPD_USER string COMMENT 'Last Updated User' +,KYLIN_GROUPINGS_UPD_USER string ,META_CATEG_ID decimal -,META_CATEG_NAME string COMMENT 'Level1 Category' +,META_CATEG_NAME string ,CATEG_LVL2_ID decimal ,CATEG_LVL3_ID decimal ,CATEG_LVL4_ID decimal ,CATEG_LVL5_ID decimal ,CATEG_LVL6_ID decimal ,CATEG_LVL7_ID decimal -,CATEG_LVL2_NAME string COMMENT 'Level2 Category' -,CATEG_LVL3_NAME string COMMENT 'Level3 Category' +,CATEG_LVL2_NAME string +,CATEG_LVL3_NAME string ,CATEG_LVL4_NAME string ,CATEG_LVL5_NAME string ,CATEG_LVL6_NAME string @@ -166,32 +165,6 @@ LEAF_CATEG_ID bigint COMMENT 'Category ID, PK' ,SRC_ID tinyint ,BSNS_VRTCL_NAME string ) -COMMENT 'Detail category inforamtion, Dimension Table' -ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' -STORED AS TEXTFILE; - -DROP TABLE IF EXISTS DEFAULT.KYLIN_COUNTRY; - -CREATE TABLE DEFAULT.KYLIN_COUNTRY -( -COUNTRY string -,LATITUDE double -,LONGITUDE double -,NAME string -) -ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' -STORED AS TEXTFILE; - -DROP TABLE IF EXISTS DEFAULT.KYLIN_ACCOUNT; - -CREATE TABLE DEFAULT.KYLIN_ACCOUNT -( -ACCOUNT_ID bigint -,ACCOUNT_BUYER_LEVEL int COMMENT 'Account Buyer Level' -,ACCOUNT_SELLER_LEVEL int COMMENT 'Account Seller Level' -,ACCOUNT_COUNTRY string COMMENT 'Account Country' -,ACCOUNT_CONTACT string COMMENT 'Account Contact Info' -) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE; @@ -200,24 +173,20 @@ DROP TABLE IF EXISTS DEFAULT.KYLIN_SALES; CREATE TABLE DEFAULT.KYLIN_SALES ( TRANS_ID bigint -,PART_DT date COMMENT 'Order Date' -,LSTG_FORMAT_NAME string COMMENT 'Order Transaction Type' -,LEAF_CATEG_ID bigint COMMENT 'Category ID' -,LSTG_SITE_ID int COMMENT 'Site ID' +,PART_DT date +,LSTG_FORMAT_NAME string +,LEAF_CATEG_ID bigint +,LSTG_SITE_ID int ,SLR_SEGMENT_CD smallint -,PRICE decimal(19,4) COMMENT 'Order Price' -,ITEM_COUNT bigint COMMENT 'Number of Purchased Goods' -,SELLER_ID bigint COMMENT 'Seller ID' -,BUYER_ID bigint COMMENT 'Buyer ID' -,OPS_USER_ID string COMMENT 'System User ID' -,OPS_REGION string COMMENT 'System User Region' +,PRICE decimal(19,4) +,ITEM_COUNT bigint +,SELLER_ID bigint +,USER_ID string +,REGION string ) -COMMENT 'Sales order table, fact table' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE; LOAD DATA INPATH '/tmp/kylin/sample_cube/data/DEFAULT.KYLIN_SALES.csv' OVERWRITE INTO TABLE DEFAULT.KYLIN_SALES; -LOAD DATA INPATH '/tmp/kylin/sample_cube/data/DEFAULT.KYLIN_ACCOUNT.csv' OVERWRITE INTO TABLE DEFAULT.KYLIN_ACCOUNT; -LOAD DATA INPATH '/tmp/kylin/sample_cube/data/DEFAULT.KYLIN_COUNTRY.csv' OVERWRITE INTO TABLE DEFAULT.KYLIN_COUNTRY; LOAD DATA INPATH '/tmp/kylin/sample_cube/data/DEFAULT.KYLIN_CAL_DT.csv' OVERWRITE INTO TABLE DEFAULT.KYLIN_CAL_DT; LOAD DATA INPATH '/tmp/kylin/sample_cube/data/DEFAULT.KYLIN_CATEGORY_GROUPINGS.csv' OVERWRITE INTO TABLE DEFAULT.KYLIN_CATEGORY_GROUPINGS;