[jira] [Commented] (KYLIN-4038) Lookup tables is empty when build on ORC tables.
[
https://issues.apache.org/jira/browse/KYLIN-4038?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16865005#comment-16865005
]
Colin Luo commented on KYLIN-4038:
--
HDP 3.1, if delete a dimension value, build error at step 4,
org.apache.kylin.engine.mr.exception.HadoopShellException: java.io.IOException:
java.lang.RuntimeException: ORC split generation failed with exception: Found
unexpected deltas: [Delta(?,16,16,[0])] in hdfs://
> Lookup tables is empty when build on ORC tables.
>
>
> Key: KYLIN-4038
> URL: https://issues.apache.org/jira/browse/KYLIN-4038
> Project: Kylin
> Issue Type: Bug
>Affects Versions: v2.6.2
> Environment: HDP 3 cluster.
>Reporter: Alexander
>Assignee: XiaoXiang Yu
>Priority: Blocker
> Attachments: image-2019-06-11-16-12-09-472.png
>
>
> Create tables, simple copy of learn_kylin project.
> {code:java}
> // Create tables
> create table kylin_sales_sida (trans_id bigint,part_dt date,lstg_format_name
> string,leaf_categ_id bigint,lstg_site_id int,slr_segment_cd smallint,price
> decimal(19,4),item_count bigint,seller_id bigint,buyer_id bigint,ops_user_id
> string,ops_region string) stored as orc tblproperties
> ("orc.compress"="SNAPPY");
> insert into table kylin_sales_sida select * from kylin_sales;
> create table kylin_country_sida (country string,latitude double,longitude
> double,name string) stored as orc tblproperties ("orc.compress"="SNAPPY");
> insert into table kylin_country_sida select * from kylin_country;
> create table kylin_category_groupings_sida (leaf_categ_id
> bigint,leaf_categ_name string,site_id int,categ_busn_mgr
> string,categ_busn_unit string,regn_categ string,user_defined_field1
> string,user_defined_field3 string,kylin_groupings_cre_date
> string,kylin_groupings_upd_date string,kylin_groupings_cre_user
> string,kylin_groupings_upd_user string,meta_categ_id
> decimal(10,0),meta_categ_name string,categ_lvl2_id
> decimal(10,0),categ_lvl3_id decimal(10,0),categ_lvl4_id
> decimal(10,0),categ_lvl5_id decimal(10,0),categ_lvl6_id
> decimal(10,0),categ_lvl7_id decimal(10,0),categ_lvl2_name
> string,categ_lvl3_name string,categ_lvl4_name string,categ_lvl5_name
> string,categ_lvl6_name string,categ_lvl7_name string,categ_flags
> decimal(10,0),adult_categ_yn string,domain_id
> decimal(10,0),user_defined_field5 string,vcs_id decimal(10,0),gcs_id
> decimal(10,0),move_to decimal(10,0),sap_category_id decimal(10,0),src_id
> tinyint,bsns_vrtcl_name string) stored as orc tblproperties
> ("orc.compress"="SNAPPY");
> insert into table kylin_category_groupings_sida select * from
> kylin_category_groupings;
> create table kylin_cal_dt_sida (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,age_for_week_id
> smallint,age_for_dt_id smallint,age_for_rtl_year_id
> smallint,age_for_rtl_qtr_id smallint,age_for_rtl_month_id
> smallint,age_for_rtl_week_id smallint,age_for_cs_week_id
> smallint,day_of_cal_id int,day_of_year_id smallint,day_of_qtr_id
> smallint,day_of_month_id smallint,day_of_week_id int,week_of_year_id
> tinyint,week_of_cal_id int,month_of_qtr_id tinyint,month_of_year_id
> tinyint,month_of_cal_id smallint,qtr_of_year_id tinyint,qtr_of_cal_id
> smallint,year_of_cal_id smallint,year_end_dt string,qtr_end_dt
> string,month_end_dt string,week_end_dt string,cal_dt_name string,cal_dt_desc
> string,cal_dt_short_name string,ytd_yn_id tinyint,qtd_yn_id tinyint,mtd_yn_id
> tinyint,wtd_yn_id tinyint,season_beg_dt string,day_in_year_count
> smallint,day_in_qtr_count tinyint,day_in_month_count
> tinyint,day_in_week_count tinyint,rtl_year_beg_dt string,rtl_qtr_beg_dt
> string,rtl_month_beg_dt string,rtl_week_beg_dt string,cs_week_beg_dt
> string,cal_date string,day_of_week string,month_id string,prd_desc
> string,prd_flag string,prd_id string,prd_ind string,qtr_desc string,qtr_id
> string,qtr_ind string,retail_week string,retail_year string,retail_start_date
> string,retail_wk_end_date string,week_ind string,week_num_desc
> string,week_beg_date string,week_end_date string,week_in_year_id
> string,week_id string,week_beg_end_desc_mdy string,week_beg_end_desc_md
> string,year_id string,year_ind string,cal_dt_mns_1year_dt
> string,cal_dt_mns_2year_dt string,cal_dt_mns_1qtr_dt
> string,cal_dt_mns_2qtr_dt string,cal_dt_mns_1month_dt
> string,cal_dt_mns_2month_dt string,cal_dt_mns_1week_dt
> string,cal_dt_mns_2week_dt string,curr_cal_dt_mns_1year_yn_id
> tinyint,curr_cal_dt_mns_2year_yn_id tinyint,curr_cal_dt_mns_1qtr_yn_id
> tinyint,curr_cal_dt_mns_2qtr_yn_id tinyint,curr_cal_dt_mns_1month_yn_id
> tinyint,curr_cal_dt_mns_2month_yn_id tinyint,curr_cal_dt_mns_1week_yn_ind
> tinyint,curr_cal_dt_mns_2week_yn_ind
[jira] [Commented] (KYLIN-4038) Lookup tables is empty when build on ORC tables.
[
https://issues.apache.org/jira/browse/KYLIN-4038?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16864999#comment-16864999
]
XiaoXiang Yu commented on KYLIN-4038:
-
Hadoop 2.X is OK.
> Lookup tables is empty when build on ORC tables.
>
>
> Key: KYLIN-4038
> URL: https://issues.apache.org/jira/browse/KYLIN-4038
> Project: Kylin
> Issue Type: Bug
>Affects Versions: v2.6.2
> Environment: HDP 3 cluster.
>Reporter: Alexander
>Assignee: XiaoXiang Yu
>Priority: Blocker
> Attachments: image-2019-06-11-16-12-09-472.png
>
>
> Create tables, simple copy of learn_kylin project.
> {code:java}
> // Create tables
> create table kylin_sales_sida (trans_id bigint,part_dt date,lstg_format_name
> string,leaf_categ_id bigint,lstg_site_id int,slr_segment_cd smallint,price
> decimal(19,4),item_count bigint,seller_id bigint,buyer_id bigint,ops_user_id
> string,ops_region string) stored as orc tblproperties
> ("orc.compress"="SNAPPY");
> insert into table kylin_sales_sida select * from kylin_sales;
> create table kylin_country_sida (country string,latitude double,longitude
> double,name string) stored as orc tblproperties ("orc.compress"="SNAPPY");
> insert into table kylin_country_sida select * from kylin_country;
> create table kylin_category_groupings_sida (leaf_categ_id
> bigint,leaf_categ_name string,site_id int,categ_busn_mgr
> string,categ_busn_unit string,regn_categ string,user_defined_field1
> string,user_defined_field3 string,kylin_groupings_cre_date
> string,kylin_groupings_upd_date string,kylin_groupings_cre_user
> string,kylin_groupings_upd_user string,meta_categ_id
> decimal(10,0),meta_categ_name string,categ_lvl2_id
> decimal(10,0),categ_lvl3_id decimal(10,0),categ_lvl4_id
> decimal(10,0),categ_lvl5_id decimal(10,0),categ_lvl6_id
> decimal(10,0),categ_lvl7_id decimal(10,0),categ_lvl2_name
> string,categ_lvl3_name string,categ_lvl4_name string,categ_lvl5_name
> string,categ_lvl6_name string,categ_lvl7_name string,categ_flags
> decimal(10,0),adult_categ_yn string,domain_id
> decimal(10,0),user_defined_field5 string,vcs_id decimal(10,0),gcs_id
> decimal(10,0),move_to decimal(10,0),sap_category_id decimal(10,0),src_id
> tinyint,bsns_vrtcl_name string) stored as orc tblproperties
> ("orc.compress"="SNAPPY");
> insert into table kylin_category_groupings_sida select * from
> kylin_category_groupings;
> create table kylin_cal_dt_sida (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,age_for_week_id
> smallint,age_for_dt_id smallint,age_for_rtl_year_id
> smallint,age_for_rtl_qtr_id smallint,age_for_rtl_month_id
> smallint,age_for_rtl_week_id smallint,age_for_cs_week_id
> smallint,day_of_cal_id int,day_of_year_id smallint,day_of_qtr_id
> smallint,day_of_month_id smallint,day_of_week_id int,week_of_year_id
> tinyint,week_of_cal_id int,month_of_qtr_id tinyint,month_of_year_id
> tinyint,month_of_cal_id smallint,qtr_of_year_id tinyint,qtr_of_cal_id
> smallint,year_of_cal_id smallint,year_end_dt string,qtr_end_dt
> string,month_end_dt string,week_end_dt string,cal_dt_name string,cal_dt_desc
> string,cal_dt_short_name string,ytd_yn_id tinyint,qtd_yn_id tinyint,mtd_yn_id
> tinyint,wtd_yn_id tinyint,season_beg_dt string,day_in_year_count
> smallint,day_in_qtr_count tinyint,day_in_month_count
> tinyint,day_in_week_count tinyint,rtl_year_beg_dt string,rtl_qtr_beg_dt
> string,rtl_month_beg_dt string,rtl_week_beg_dt string,cs_week_beg_dt
> string,cal_date string,day_of_week string,month_id string,prd_desc
> string,prd_flag string,prd_id string,prd_ind string,qtr_desc string,qtr_id
> string,qtr_ind string,retail_week string,retail_year string,retail_start_date
> string,retail_wk_end_date string,week_ind string,week_num_desc
> string,week_beg_date string,week_end_date string,week_in_year_id
> string,week_id string,week_beg_end_desc_mdy string,week_beg_end_desc_md
> string,year_id string,year_ind string,cal_dt_mns_1year_dt
> string,cal_dt_mns_2year_dt string,cal_dt_mns_1qtr_dt
> string,cal_dt_mns_2qtr_dt string,cal_dt_mns_1month_dt
> string,cal_dt_mns_2month_dt string,cal_dt_mns_1week_dt
> string,cal_dt_mns_2week_dt string,curr_cal_dt_mns_1year_yn_id
> tinyint,curr_cal_dt_mns_2year_yn_id tinyint,curr_cal_dt_mns_1qtr_yn_id
> tinyint,curr_cal_dt_mns_2qtr_yn_id tinyint,curr_cal_dt_mns_1month_yn_id
> tinyint,curr_cal_dt_mns_2month_yn_id tinyint,curr_cal_dt_mns_1week_yn_ind
> tinyint,curr_cal_dt_mns_2week_yn_ind tinyint,rtl_month_of_rtl_year_id
> string,rtl_qtr_of_rtl_year_id tinyint,rtl_week_of_rtl_year_id
> tinyint,season_of_year_id tinyint,ytm_yn_id tinyint,ytq_yn_id
> tinyint,ytw_yn_id tinyint,kylin_cal_dt_cre_date string,kylin_cal_dt_cre_user
>
[jira] [Commented] (KYLIN-4038) Lookup tables is empty when build on ORC tables.
[
https://issues.apache.org/jira/browse/KYLIN-4038?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16862896#comment-16862896
]
Colin Luo commented on KYLIN-4038:
--
KYLIN-3990 maybe a similar problem.
> Lookup tables is empty when build on ORC tables.
>
>
> Key: KYLIN-4038
> URL: https://issues.apache.org/jira/browse/KYLIN-4038
> Project: Kylin
> Issue Type: Bug
>Affects Versions: v2.6.2
> Environment: HDP 3 cluster.
>Reporter: Alexander
>Assignee: XiaoXiang Yu
>Priority: Blocker
> Attachments: image-2019-06-11-16-12-09-472.png
>
>
> Create tables, simple copy of learn_kylin project.
> {code:java}
> // Create tables
> create table kylin_sales_sida (trans_id bigint,part_dt date,lstg_format_name
> string,leaf_categ_id bigint,lstg_site_id int,slr_segment_cd smallint,price
> decimal(19,4),item_count bigint,seller_id bigint,buyer_id bigint,ops_user_id
> string,ops_region string) stored as orc tblproperties
> ("orc.compress"="SNAPPY");
> insert into table kylin_sales_sida select * from kylin_sales;
> create table kylin_country_sida (country string,latitude double,longitude
> double,name string) stored as orc tblproperties ("orc.compress"="SNAPPY");
> insert into table kylin_country_sida select * from kylin_country;
> create table kylin_category_groupings_sida (leaf_categ_id
> bigint,leaf_categ_name string,site_id int,categ_busn_mgr
> string,categ_busn_unit string,regn_categ string,user_defined_field1
> string,user_defined_field3 string,kylin_groupings_cre_date
> string,kylin_groupings_upd_date string,kylin_groupings_cre_user
> string,kylin_groupings_upd_user string,meta_categ_id
> decimal(10,0),meta_categ_name string,categ_lvl2_id
> decimal(10,0),categ_lvl3_id decimal(10,0),categ_lvl4_id
> decimal(10,0),categ_lvl5_id decimal(10,0),categ_lvl6_id
> decimal(10,0),categ_lvl7_id decimal(10,0),categ_lvl2_name
> string,categ_lvl3_name string,categ_lvl4_name string,categ_lvl5_name
> string,categ_lvl6_name string,categ_lvl7_name string,categ_flags
> decimal(10,0),adult_categ_yn string,domain_id
> decimal(10,0),user_defined_field5 string,vcs_id decimal(10,0),gcs_id
> decimal(10,0),move_to decimal(10,0),sap_category_id decimal(10,0),src_id
> tinyint,bsns_vrtcl_name string) stored as orc tblproperties
> ("orc.compress"="SNAPPY");
> insert into table kylin_category_groupings_sida select * from
> kylin_category_groupings;
> create table kylin_cal_dt_sida (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,age_for_week_id
> smallint,age_for_dt_id smallint,age_for_rtl_year_id
> smallint,age_for_rtl_qtr_id smallint,age_for_rtl_month_id
> smallint,age_for_rtl_week_id smallint,age_for_cs_week_id
> smallint,day_of_cal_id int,day_of_year_id smallint,day_of_qtr_id
> smallint,day_of_month_id smallint,day_of_week_id int,week_of_year_id
> tinyint,week_of_cal_id int,month_of_qtr_id tinyint,month_of_year_id
> tinyint,month_of_cal_id smallint,qtr_of_year_id tinyint,qtr_of_cal_id
> smallint,year_of_cal_id smallint,year_end_dt string,qtr_end_dt
> string,month_end_dt string,week_end_dt string,cal_dt_name string,cal_dt_desc
> string,cal_dt_short_name string,ytd_yn_id tinyint,qtd_yn_id tinyint,mtd_yn_id
> tinyint,wtd_yn_id tinyint,season_beg_dt string,day_in_year_count
> smallint,day_in_qtr_count tinyint,day_in_month_count
> tinyint,day_in_week_count tinyint,rtl_year_beg_dt string,rtl_qtr_beg_dt
> string,rtl_month_beg_dt string,rtl_week_beg_dt string,cs_week_beg_dt
> string,cal_date string,day_of_week string,month_id string,prd_desc
> string,prd_flag string,prd_id string,prd_ind string,qtr_desc string,qtr_id
> string,qtr_ind string,retail_week string,retail_year string,retail_start_date
> string,retail_wk_end_date string,week_ind string,week_num_desc
> string,week_beg_date string,week_end_date string,week_in_year_id
> string,week_id string,week_beg_end_desc_mdy string,week_beg_end_desc_md
> string,year_id string,year_ind string,cal_dt_mns_1year_dt
> string,cal_dt_mns_2year_dt string,cal_dt_mns_1qtr_dt
> string,cal_dt_mns_2qtr_dt string,cal_dt_mns_1month_dt
> string,cal_dt_mns_2month_dt string,cal_dt_mns_1week_dt
> string,cal_dt_mns_2week_dt string,curr_cal_dt_mns_1year_yn_id
> tinyint,curr_cal_dt_mns_2year_yn_id tinyint,curr_cal_dt_mns_1qtr_yn_id
> tinyint,curr_cal_dt_mns_2qtr_yn_id tinyint,curr_cal_dt_mns_1month_yn_id
> tinyint,curr_cal_dt_mns_2month_yn_id tinyint,curr_cal_dt_mns_1week_yn_ind
> tinyint,curr_cal_dt_mns_2week_yn_ind tinyint,rtl_month_of_rtl_year_id
> string,rtl_qtr_of_rtl_year_id tinyint,rtl_week_of_rtl_year_id
> tinyint,season_of_year_id tinyint,ytm_yn_id tinyint,ytq_yn_id
> tinyint,ytw_yn_id tinyint,kylin_cal_dt_cre_date
[jira] [Commented] (KYLIN-4038) Lookup tables is empty when build on ORC tables.
[
https://issues.apache.org/jira/browse/KYLIN-4038?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16862825#comment-16862825
]
Alexander commented on KYLIN-4038:
--
Hello,
With external tables etherything is ok.
But in our business case, the data can be changed on 7 last days. To handle
this case, we try to use ACID tables, because they can be updated.
Our case is next:
# Load new data into hive.
# update data from (current date - 7 days) till now - that why ACID needed.
# Drop last partitions on Kylin cube. (last 7 days segments)
# Run buid cube for last 7 days.
If there is no way to use ACID, maybe any best practicies to handle this case?
> Lookup tables is empty when build on ORC tables.
>
>
> Key: KYLIN-4038
> URL: https://issues.apache.org/jira/browse/KYLIN-4038
> Project: Kylin
> Issue Type: Bug
>Affects Versions: v2.6.2
> Environment: HDP 3 cluster.
>Reporter: Alexander
>Assignee: XiaoXiang Yu
>Priority: Blocker
> Attachments: image-2019-06-11-16-12-09-472.png
>
>
> Create tables, simple copy of learn_kylin project.
> {code:java}
> // Create tables
> create table kylin_sales_sida (trans_id bigint,part_dt date,lstg_format_name
> string,leaf_categ_id bigint,lstg_site_id int,slr_segment_cd smallint,price
> decimal(19,4),item_count bigint,seller_id bigint,buyer_id bigint,ops_user_id
> string,ops_region string) stored as orc tblproperties
> ("orc.compress"="SNAPPY");
> insert into table kylin_sales_sida select * from kylin_sales;
> create table kylin_country_sida (country string,latitude double,longitude
> double,name string) stored as orc tblproperties ("orc.compress"="SNAPPY");
> insert into table kylin_country_sida select * from kylin_country;
> create table kylin_category_groupings_sida (leaf_categ_id
> bigint,leaf_categ_name string,site_id int,categ_busn_mgr
> string,categ_busn_unit string,regn_categ string,user_defined_field1
> string,user_defined_field3 string,kylin_groupings_cre_date
> string,kylin_groupings_upd_date string,kylin_groupings_cre_user
> string,kylin_groupings_upd_user string,meta_categ_id
> decimal(10,0),meta_categ_name string,categ_lvl2_id
> decimal(10,0),categ_lvl3_id decimal(10,0),categ_lvl4_id
> decimal(10,0),categ_lvl5_id decimal(10,0),categ_lvl6_id
> decimal(10,0),categ_lvl7_id decimal(10,0),categ_lvl2_name
> string,categ_lvl3_name string,categ_lvl4_name string,categ_lvl5_name
> string,categ_lvl6_name string,categ_lvl7_name string,categ_flags
> decimal(10,0),adult_categ_yn string,domain_id
> decimal(10,0),user_defined_field5 string,vcs_id decimal(10,0),gcs_id
> decimal(10,0),move_to decimal(10,0),sap_category_id decimal(10,0),src_id
> tinyint,bsns_vrtcl_name string) stored as orc tblproperties
> ("orc.compress"="SNAPPY");
> insert into table kylin_category_groupings_sida select * from
> kylin_category_groupings;
> create table kylin_cal_dt_sida (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,age_for_week_id
> smallint,age_for_dt_id smallint,age_for_rtl_year_id
> smallint,age_for_rtl_qtr_id smallint,age_for_rtl_month_id
> smallint,age_for_rtl_week_id smallint,age_for_cs_week_id
> smallint,day_of_cal_id int,day_of_year_id smallint,day_of_qtr_id
> smallint,day_of_month_id smallint,day_of_week_id int,week_of_year_id
> tinyint,week_of_cal_id int,month_of_qtr_id tinyint,month_of_year_id
> tinyint,month_of_cal_id smallint,qtr_of_year_id tinyint,qtr_of_cal_id
> smallint,year_of_cal_id smallint,year_end_dt string,qtr_end_dt
> string,month_end_dt string,week_end_dt string,cal_dt_name string,cal_dt_desc
> string,cal_dt_short_name string,ytd_yn_id tinyint,qtd_yn_id tinyint,mtd_yn_id
> tinyint,wtd_yn_id tinyint,season_beg_dt string,day_in_year_count
> smallint,day_in_qtr_count tinyint,day_in_month_count
> tinyint,day_in_week_count tinyint,rtl_year_beg_dt string,rtl_qtr_beg_dt
> string,rtl_month_beg_dt string,rtl_week_beg_dt string,cs_week_beg_dt
> string,cal_date string,day_of_week string,month_id string,prd_desc
> string,prd_flag string,prd_id string,prd_ind string,qtr_desc string,qtr_id
> string,qtr_ind string,retail_week string,retail_year string,retail_start_date
> string,retail_wk_end_date string,week_ind string,week_num_desc
> string,week_beg_date string,week_end_date string,week_in_year_id
> string,week_id string,week_beg_end_desc_mdy string,week_beg_end_desc_md
> string,year_id string,year_ind string,cal_dt_mns_1year_dt
> string,cal_dt_mns_2year_dt string,cal_dt_mns_1qtr_dt
> string,cal_dt_mns_2qtr_dt string,cal_dt_mns_1month_dt
> string,cal_dt_mns_2month_dt string,cal_dt_mns_1week_dt
> string,cal_dt_mns_2week_dt string,curr_cal_dt_mns_1year_yn_id
> tinyint,curr_cal_dt_mns_2year_yn_id
[jira] [Commented] (KYLIN-4038) Lookup tables is empty when build on ORC tables.
[
https://issues.apache.org/jira/browse/KYLIN-4038?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16861804#comment-16861804
]
XiaoXiang Yu commented on KYLIN-4038:
-
{code}$ watch org.apache.kylin.dict.lookup.SnapshotTable takeSnapshot
"\{params,target,returnObj}" -x 2
Press Q or Ctrl+C to abort.
Affect(class-cnt:1 , method-cnt:1) cost in 172 ms.
ts=2019-06-12 02:35:45; [cost=235.194326ms] result=@ArrayList[
@Object[][
@HiveTable[hive: database=[DEFAULT], table=[KYLIN_ACCOUNT_SIDA]],
@TableDesc[TableDesc\{name='KYLIN_ACCOUNT_SIDA', columns=[ColumnDesc{id='1',
name='ACCOUNT_ID', datatype='bigint', comment='null'}, ColumnDesc\{id='2',
name='ACCOUNT_BUYER_LEVEL', datatype='integer', comment='null'},
ColumnDesc\{id='3', name='ACCOUNT_SELLER_LEVEL', datatype='integer',
comment='null'}, ColumnDesc\{id='4', name='ACCOUNT_COUNTRY',
datatype='varchar(256)', comment='null'}, ColumnDesc\{id='5',
name='ACCOUNT_CONTACT', datatype='varchar(256)', comment='null'}],
sourceType=0, tableType='MANAGED_TABLE', database=DatabaseDesc [name=DEFAULT],
identity='DEFAULT.KYLIN_ACCOUNT_SIDA'}],
],
@SnapshotTable[
STORAGE_TYPE_METASTORE=@String[metaStore],
tableName=@String[DEFAULT.KYLIN_ACCOUNT_SIDA],
signature=@TableSignature[FileSignature
[path=hdfs://hdp30.hortonworks.com:8020/warehouse/tablespace/managed/hive/kylin_account_sida,
size=0, lastModifiedTime=1560234144865]],
useDictionary=@Boolean[true],
lastBuildTime=@Long[0],
rowIndices=@ArrayList[isEmpty=true;size=0],
dict=@TrieDictionary[org.apache.kylin.dict.TrieDictionary@663163f9],
NULL_STR=@String[ ÿ],
],
null,
]
$
$
$ watch org.apache.kylin.dict.lookup.SnapshotTable takeSnapshot
"\{params,target,returnObj}" -x 2
Press Q or Ctrl+C to abort.
Affect(class-cnt:1 , method-cnt:1) cost in 123 ms.
ts=2019-06-12 02:40:06; [cost=611.591702ms] result=@ArrayList[
@Object[][
@HiveTable[hive: database=[DEFAULT], table=[KYLIN_CAL_DT]],
@TableDesc[TableDesc\{name='KYLIN_CAL_DT', columns=[ColumnDesc{id='1',
name='CAL_DT', datatype='date', comment='null'}, ColumnDesc\{id='2',
name='YEAR_BEG_DT', datatype='date', comment='null'}, ColumnDesc\{id='3',
name='QTR_BEG_DT', datatype='date', comment='null'}, ColumnDesc\{id='4',
name='MONTH_BEG_DT', datatype='date', comment='null'}, ColumnDesc\{id='5',
name='WEEK_BEG_DT', datatype='date', comment='null'}, ColumnDesc\{id='6',
name='AGE_FOR_YEAR_ID', datatype='smallint', comment='null'},
ColumnDesc\{id='7', name='AGE_FOR_QTR_ID', datatype='smallint',
comment='null'}, ColumnDesc\{id='8', name='AGE_FOR_MONTH_ID',
datatype='smallint', comment='null'}, ColumnDesc\{id='9',
name='AGE_FOR_WEEK_ID', datatype='smallint', comment='null'},
ColumnDesc\{id='10', name='AGE_FOR_DT_ID', datatype='smallint',
comment='null'}, ColumnDesc\{id='11', name='AGE_FOR_RTL_YEAR_ID',
datatype='smallint', comment='null'}, ColumnDesc\{id='12',
name='AGE_FOR_RTL_QTR_ID', datatype='smallint', comment='null'},
ColumnDesc\{id='13', name='AGE_FOR_RTL_MONTH_ID', datatype='smallint',
comment='null'}, ColumnDesc\{id='14', name='AGE_FOR_RTL_WEEK_ID',
datatype='smallint', comment='null'}, ColumnDesc\{id='15',
name='AGE_FOR_CS_WEEK_ID', datatype='smallint', comment='null'},
ColumnDesc\{id='16', name='DAY_OF_CAL_ID', datatype='integer', comment='null'},
ColumnDesc\{id='17', name='DAY_OF_YEAR_ID', datatype='smallint',
comment='null'}, ColumnDesc\{id='18', name='DAY_OF_QTR_ID',
datatype='smallint', comment='null'}, ColumnDesc\{id='19',
name='DAY_OF_MONTH_ID', datatype='smallint', comment='null'},
ColumnDesc\{id='20', name='DAY_OF_WEEK_ID', datatype='integer',
comment='null'}, ColumnDesc\{id='21', name='WEEK_OF_YEAR_ID',
datatype='tinyint', comment='null'}, ColumnDesc\{id='22',
name='WEEK_OF_CAL_ID', datatype='integer', comment='null'},
ColumnDesc\{id='23', name='MONTH_OF_QTR_ID', datatype='tinyint',
comment='null'}, ColumnDesc\{id='24', name='MONTH_OF_YEAR_ID',
datatype='tinyint', comment='null'}, ColumnDesc\{id='25',
name='MONTH_OF_CAL_ID', datatype='smallint', comment='null'},
ColumnDesc\{id='26', name='QTR_OF_YEAR_ID', datatype='tinyint',
comment='null'}, ColumnDesc\{id='27', name='QTR_OF_CAL_ID',
datatype='smallint', comment='null'}, ColumnDesc\{id='28',
name='YEAR_OF_CAL_ID', datatype='smallint', comment='null'},
ColumnDesc\{id='29', name='YEAR_END_DT', datatype='varchar(256)',
comment='null'}, ColumnDesc\{id='30', name='QTR_END_DT',
datatype='varchar(256)', comment='null'}, ColumnDesc\{id='31',
name='MONTH_END_DT', datatype='varchar(256)', comment='null'},
ColumnDesc\{id='32', name='WEEK_END_DT', datatype='varchar(256)',
comment='null'}, ColumnDesc\{id='33', name='CAL_DT_NAME',
datatype='varchar(256)', comment='null'}, ColumnDesc\{id='34',
name='CAL_DT_DESC', datatype='varchar(256)', comment='null'},
ColumnDesc\{id='35', name='CAL_DT_SHORT_NAME', datatype='varchar(256)',
comment='null'}, ColumnDesc\{id='36', name='YTD_YN_ID', datatype='tinyint',
[jira] [Commented] (KYLIN-4038) Lookup tables is empty when build on ORC tables.
[
https://issues.apache.org/jira/browse/KYLIN-4038?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16861786#comment-16861786
]
XiaoXiang Yu commented on KYLIN-4038:
-
Hi, have you ever try external table? Could that meet the same error?
> Lookup tables is empty when build on ORC tables.
>
>
> Key: KYLIN-4038
> URL: https://issues.apache.org/jira/browse/KYLIN-4038
> Project: Kylin
> Issue Type: Bug
>Affects Versions: v2.6.2
> Environment: HDP 3 cluster.
>Reporter: Alexander
>Assignee: XiaoXiang Yu
>Priority: Blocker
> Attachments: image-2019-06-11-16-12-09-472.png
>
>
> Create tables, simple copy of learn_kylin project.
> {code:java}
> // Create tables
> create table kylin_sales_sida (trans_id bigint,part_dt date,lstg_format_name
> string,leaf_categ_id bigint,lstg_site_id int,slr_segment_cd smallint,price
> decimal(19,4),item_count bigint,seller_id bigint,buyer_id bigint,ops_user_id
> string,ops_region string) stored as orc tblproperties
> ("orc.compress"="SNAPPY");
> insert into table kylin_sales_sida select * from kylin_sales;
> create table kylin_country_sida (country string,latitude double,longitude
> double,name string) stored as orc tblproperties ("orc.compress"="SNAPPY");
> insert into table kylin_country_sida select * from kylin_country;
> create table kylin_category_groupings_sida (leaf_categ_id
> bigint,leaf_categ_name string,site_id int,categ_busn_mgr
> string,categ_busn_unit string,regn_categ string,user_defined_field1
> string,user_defined_field3 string,kylin_groupings_cre_date
> string,kylin_groupings_upd_date string,kylin_groupings_cre_user
> string,kylin_groupings_upd_user string,meta_categ_id
> decimal(10,0),meta_categ_name string,categ_lvl2_id
> decimal(10,0),categ_lvl3_id decimal(10,0),categ_lvl4_id
> decimal(10,0),categ_lvl5_id decimal(10,0),categ_lvl6_id
> decimal(10,0),categ_lvl7_id decimal(10,0),categ_lvl2_name
> string,categ_lvl3_name string,categ_lvl4_name string,categ_lvl5_name
> string,categ_lvl6_name string,categ_lvl7_name string,categ_flags
> decimal(10,0),adult_categ_yn string,domain_id
> decimal(10,0),user_defined_field5 string,vcs_id decimal(10,0),gcs_id
> decimal(10,0),move_to decimal(10,0),sap_category_id decimal(10,0),src_id
> tinyint,bsns_vrtcl_name string) stored as orc tblproperties
> ("orc.compress"="SNAPPY");
> insert into table kylin_category_groupings_sida select * from
> kylin_category_groupings;
> create table kylin_cal_dt_sida (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,age_for_week_id
> smallint,age_for_dt_id smallint,age_for_rtl_year_id
> smallint,age_for_rtl_qtr_id smallint,age_for_rtl_month_id
> smallint,age_for_rtl_week_id smallint,age_for_cs_week_id
> smallint,day_of_cal_id int,day_of_year_id smallint,day_of_qtr_id
> smallint,day_of_month_id smallint,day_of_week_id int,week_of_year_id
> tinyint,week_of_cal_id int,month_of_qtr_id tinyint,month_of_year_id
> tinyint,month_of_cal_id smallint,qtr_of_year_id tinyint,qtr_of_cal_id
> smallint,year_of_cal_id smallint,year_end_dt string,qtr_end_dt
> string,month_end_dt string,week_end_dt string,cal_dt_name string,cal_dt_desc
> string,cal_dt_short_name string,ytd_yn_id tinyint,qtd_yn_id tinyint,mtd_yn_id
> tinyint,wtd_yn_id tinyint,season_beg_dt string,day_in_year_count
> smallint,day_in_qtr_count tinyint,day_in_month_count
> tinyint,day_in_week_count tinyint,rtl_year_beg_dt string,rtl_qtr_beg_dt
> string,rtl_month_beg_dt string,rtl_week_beg_dt string,cs_week_beg_dt
> string,cal_date string,day_of_week string,month_id string,prd_desc
> string,prd_flag string,prd_id string,prd_ind string,qtr_desc string,qtr_id
> string,qtr_ind string,retail_week string,retail_year string,retail_start_date
> string,retail_wk_end_date string,week_ind string,week_num_desc
> string,week_beg_date string,week_end_date string,week_in_year_id
> string,week_id string,week_beg_end_desc_mdy string,week_beg_end_desc_md
> string,year_id string,year_ind string,cal_dt_mns_1year_dt
> string,cal_dt_mns_2year_dt string,cal_dt_mns_1qtr_dt
> string,cal_dt_mns_2qtr_dt string,cal_dt_mns_1month_dt
> string,cal_dt_mns_2month_dt string,cal_dt_mns_1week_dt
> string,cal_dt_mns_2week_dt string,curr_cal_dt_mns_1year_yn_id
> tinyint,curr_cal_dt_mns_2year_yn_id tinyint,curr_cal_dt_mns_1qtr_yn_id
> tinyint,curr_cal_dt_mns_2qtr_yn_id tinyint,curr_cal_dt_mns_1month_yn_id
> tinyint,curr_cal_dt_mns_2month_yn_id tinyint,curr_cal_dt_mns_1week_yn_ind
> tinyint,curr_cal_dt_mns_2week_yn_ind tinyint,rtl_month_of_rtl_year_id
> string,rtl_qtr_of_rtl_year_id tinyint,rtl_week_of_rtl_year_id
> tinyint,season_of_year_id tinyint,ytm_yn_id tinyint,ytq_yn_id
> tinyint,ytw_yn_id
[jira] [Commented] (KYLIN-4038) Lookup tables is empty when build on ORC tables.
[
https://issues.apache.org/jira/browse/KYLIN-4038?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16860690#comment-16860690
]
Alexander commented on KYLIN-4038:
--
XiaoXiang Yu, many thanks for you research!
This is it.
I'm stuck at this point, and need any suggestions\help on that...
> Lookup tables is empty when build on ORC tables.
>
>
> Key: KYLIN-4038
> URL: https://issues.apache.org/jira/browse/KYLIN-4038
> Project: Kylin
> Issue Type: Bug
>Affects Versions: v2.6.2
> Environment: HDP 3 cluster.
>Reporter: Alexander
>Assignee: XiaoXiang Yu
>Priority: Blocker
> Attachments: image-2019-06-11-16-12-09-472.png
>
>
> Create tables, simple copy of learn_kylin project.
> {code:java}
> // Create tables
> create table kylin_sales_sida (trans_id bigint,part_dt date,lstg_format_name
> string,leaf_categ_id bigint,lstg_site_id int,slr_segment_cd smallint,price
> decimal(19,4),item_count bigint,seller_id bigint,buyer_id bigint,ops_user_id
> string,ops_region string) stored as orc tblproperties
> ("orc.compress"="SNAPPY");
> insert into table kylin_sales_sida select * from kylin_sales;
> create table kylin_country_sida (country string,latitude double,longitude
> double,name string) stored as orc tblproperties ("orc.compress"="SNAPPY");
> insert into table kylin_country_sida select * from kylin_country;
> create table kylin_category_groupings_sida (leaf_categ_id
> bigint,leaf_categ_name string,site_id int,categ_busn_mgr
> string,categ_busn_unit string,regn_categ string,user_defined_field1
> string,user_defined_field3 string,kylin_groupings_cre_date
> string,kylin_groupings_upd_date string,kylin_groupings_cre_user
> string,kylin_groupings_upd_user string,meta_categ_id
> decimal(10,0),meta_categ_name string,categ_lvl2_id
> decimal(10,0),categ_lvl3_id decimal(10,0),categ_lvl4_id
> decimal(10,0),categ_lvl5_id decimal(10,0),categ_lvl6_id
> decimal(10,0),categ_lvl7_id decimal(10,0),categ_lvl2_name
> string,categ_lvl3_name string,categ_lvl4_name string,categ_lvl5_name
> string,categ_lvl6_name string,categ_lvl7_name string,categ_flags
> decimal(10,0),adult_categ_yn string,domain_id
> decimal(10,0),user_defined_field5 string,vcs_id decimal(10,0),gcs_id
> decimal(10,0),move_to decimal(10,0),sap_category_id decimal(10,0),src_id
> tinyint,bsns_vrtcl_name string) stored as orc tblproperties
> ("orc.compress"="SNAPPY");
> insert into table kylin_category_groupings_sida select * from
> kylin_category_groupings;
> create table kylin_cal_dt_sida (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,age_for_week_id
> smallint,age_for_dt_id smallint,age_for_rtl_year_id
> smallint,age_for_rtl_qtr_id smallint,age_for_rtl_month_id
> smallint,age_for_rtl_week_id smallint,age_for_cs_week_id
> smallint,day_of_cal_id int,day_of_year_id smallint,day_of_qtr_id
> smallint,day_of_month_id smallint,day_of_week_id int,week_of_year_id
> tinyint,week_of_cal_id int,month_of_qtr_id tinyint,month_of_year_id
> tinyint,month_of_cal_id smallint,qtr_of_year_id tinyint,qtr_of_cal_id
> smallint,year_of_cal_id smallint,year_end_dt string,qtr_end_dt
> string,month_end_dt string,week_end_dt string,cal_dt_name string,cal_dt_desc
> string,cal_dt_short_name string,ytd_yn_id tinyint,qtd_yn_id tinyint,mtd_yn_id
> tinyint,wtd_yn_id tinyint,season_beg_dt string,day_in_year_count
> smallint,day_in_qtr_count tinyint,day_in_month_count
> tinyint,day_in_week_count tinyint,rtl_year_beg_dt string,rtl_qtr_beg_dt
> string,rtl_month_beg_dt string,rtl_week_beg_dt string,cs_week_beg_dt
> string,cal_date string,day_of_week string,month_id string,prd_desc
> string,prd_flag string,prd_id string,prd_ind string,qtr_desc string,qtr_id
> string,qtr_ind string,retail_week string,retail_year string,retail_start_date
> string,retail_wk_end_date string,week_ind string,week_num_desc
> string,week_beg_date string,week_end_date string,week_in_year_id
> string,week_id string,week_beg_end_desc_mdy string,week_beg_end_desc_md
> string,year_id string,year_ind string,cal_dt_mns_1year_dt
> string,cal_dt_mns_2year_dt string,cal_dt_mns_1qtr_dt
> string,cal_dt_mns_2qtr_dt string,cal_dt_mns_1month_dt
> string,cal_dt_mns_2month_dt string,cal_dt_mns_1week_dt
> string,cal_dt_mns_2week_dt string,curr_cal_dt_mns_1year_yn_id
> tinyint,curr_cal_dt_mns_2year_yn_id tinyint,curr_cal_dt_mns_1qtr_yn_id
> tinyint,curr_cal_dt_mns_2qtr_yn_id tinyint,curr_cal_dt_mns_1month_yn_id
> tinyint,curr_cal_dt_mns_2month_yn_id tinyint,curr_cal_dt_mns_1week_yn_ind
> tinyint,curr_cal_dt_mns_2week_yn_ind tinyint,rtl_month_of_rtl_year_id
> string,rtl_qtr_of_rtl_year_id tinyint,rtl_week_of_rtl_year_id
> tinyint,season_of_year_id tinyint,ytm_yn_id
[jira] [Commented] (KYLIN-4038) Lookup tables is empty when build on ORC tables.
[
https://issues.apache.org/jira/browse/KYLIN-4038?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16860684#comment-16860684
]
XiaoXiang Yu commented on KYLIN-4038:
-
Yes, I found it. By HDP3.0 's SparkSQL CLI, "select * from kylin_account_sida"
return 0 row. But in Hive CLI, it return every rows.
> Lookup tables is empty when build on ORC tables.
>
>
> Key: KYLIN-4038
> URL: https://issues.apache.org/jira/browse/KYLIN-4038
> Project: Kylin
> Issue Type: Bug
>Affects Versions: v2.6.2
> Environment: HDP 3 cluster.
>Reporter: Alexander
>Assignee: XiaoXiang Yu
>Priority: Blocker
> Attachments: image-2019-06-11-16-12-09-472.png
>
>
> Create tables, simple copy of learn_kylin project.
> {code:java}
> // Create tables
> create table kylin_sales_sida (trans_id bigint,part_dt date,lstg_format_name
> string,leaf_categ_id bigint,lstg_site_id int,slr_segment_cd smallint,price
> decimal(19,4),item_count bigint,seller_id bigint,buyer_id bigint,ops_user_id
> string,ops_region string) stored as orc tblproperties
> ("orc.compress"="SNAPPY");
> insert into table kylin_sales_sida select * from kylin_sales;
> create table kylin_country_sida (country string,latitude double,longitude
> double,name string) stored as orc tblproperties ("orc.compress"="SNAPPY");
> insert into table kylin_country_sida select * from kylin_country;
> create table kylin_category_groupings_sida (leaf_categ_id
> bigint,leaf_categ_name string,site_id int,categ_busn_mgr
> string,categ_busn_unit string,regn_categ string,user_defined_field1
> string,user_defined_field3 string,kylin_groupings_cre_date
> string,kylin_groupings_upd_date string,kylin_groupings_cre_user
> string,kylin_groupings_upd_user string,meta_categ_id
> decimal(10,0),meta_categ_name string,categ_lvl2_id
> decimal(10,0),categ_lvl3_id decimal(10,0),categ_lvl4_id
> decimal(10,0),categ_lvl5_id decimal(10,0),categ_lvl6_id
> decimal(10,0),categ_lvl7_id decimal(10,0),categ_lvl2_name
> string,categ_lvl3_name string,categ_lvl4_name string,categ_lvl5_name
> string,categ_lvl6_name string,categ_lvl7_name string,categ_flags
> decimal(10,0),adult_categ_yn string,domain_id
> decimal(10,0),user_defined_field5 string,vcs_id decimal(10,0),gcs_id
> decimal(10,0),move_to decimal(10,0),sap_category_id decimal(10,0),src_id
> tinyint,bsns_vrtcl_name string) stored as orc tblproperties
> ("orc.compress"="SNAPPY");
> insert into table kylin_category_groupings_sida select * from
> kylin_category_groupings;
> create table kylin_cal_dt_sida (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,age_for_week_id
> smallint,age_for_dt_id smallint,age_for_rtl_year_id
> smallint,age_for_rtl_qtr_id smallint,age_for_rtl_month_id
> smallint,age_for_rtl_week_id smallint,age_for_cs_week_id
> smallint,day_of_cal_id int,day_of_year_id smallint,day_of_qtr_id
> smallint,day_of_month_id smallint,day_of_week_id int,week_of_year_id
> tinyint,week_of_cal_id int,month_of_qtr_id tinyint,month_of_year_id
> tinyint,month_of_cal_id smallint,qtr_of_year_id tinyint,qtr_of_cal_id
> smallint,year_of_cal_id smallint,year_end_dt string,qtr_end_dt
> string,month_end_dt string,week_end_dt string,cal_dt_name string,cal_dt_desc
> string,cal_dt_short_name string,ytd_yn_id tinyint,qtd_yn_id tinyint,mtd_yn_id
> tinyint,wtd_yn_id tinyint,season_beg_dt string,day_in_year_count
> smallint,day_in_qtr_count tinyint,day_in_month_count
> tinyint,day_in_week_count tinyint,rtl_year_beg_dt string,rtl_qtr_beg_dt
> string,rtl_month_beg_dt string,rtl_week_beg_dt string,cs_week_beg_dt
> string,cal_date string,day_of_week string,month_id string,prd_desc
> string,prd_flag string,prd_id string,prd_ind string,qtr_desc string,qtr_id
> string,qtr_ind string,retail_week string,retail_year string,retail_start_date
> string,retail_wk_end_date string,week_ind string,week_num_desc
> string,week_beg_date string,week_end_date string,week_in_year_id
> string,week_id string,week_beg_end_desc_mdy string,week_beg_end_desc_md
> string,year_id string,year_ind string,cal_dt_mns_1year_dt
> string,cal_dt_mns_2year_dt string,cal_dt_mns_1qtr_dt
> string,cal_dt_mns_2qtr_dt string,cal_dt_mns_1month_dt
> string,cal_dt_mns_2month_dt string,cal_dt_mns_1week_dt
> string,cal_dt_mns_2week_dt string,curr_cal_dt_mns_1year_yn_id
> tinyint,curr_cal_dt_mns_2year_yn_id tinyint,curr_cal_dt_mns_1qtr_yn_id
> tinyint,curr_cal_dt_mns_2qtr_yn_id tinyint,curr_cal_dt_mns_1month_yn_id
> tinyint,curr_cal_dt_mns_2month_yn_id tinyint,curr_cal_dt_mns_1week_yn_ind
> tinyint,curr_cal_dt_mns_2week_yn_ind tinyint,rtl_month_of_rtl_year_id
> string,rtl_qtr_of_rtl_year_id tinyint,rtl_week_of_rtl_year_id
> tinyint,season_of_year_id
[jira] [Commented] (KYLIN-4038) Lookup tables is empty when build on ORC tables.
[
https://issues.apache.org/jira/browse/KYLIN-4038?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16860676#comment-16860676
]
Alexander commented on KYLIN-4038:
--
Hello,
Yes, it's because ORC with Snappy have great compression...
learn_kylin tables is a MANAGED Text tables with insert_only mode and they are
have much more size.
if you perform select * from *tables*(orc), from beeline, you can see that all
rows persist in table.
But selection from orc tables works only in Beeline and hive clients. Select
from spark-shell shows empty table too (like a MR Job in Kylin):
{code:java}
// Spark
scala> spark.table("DEFAULT.KYLIN_ACCOUNT_SIDA").show
+--+---++---+---+
|account_id|account_buyer_level|account_seller_level|account_country|account_contact|
+--+---++---+---+
+--+---++---+---+
scala> spark.table("DEFAULT.KYLIN_ACCOUNT_SIDA").count
res21: Long = 0
{code}
Trying to select it thru JDBC as a workaround
(https://github.com/Gowthamsb12/Spark/blob/master/Spark_ACID), and no result
too:
{code:java}
// Spark JDBC call
import org.apache.spark.sql.jdbc.JdbcDialect
import org.apache.spark.sql.jdbc.JdbcDialects
object HiveDialect extends JdbcDialect {
override def canHandle(url : String): Boolean = url.startsWith("jdbc:hive2")
override def quoteIdentifier(colName: String): String = {
colName.split('.').map(part => s"`$part`").mkString(".")
}
}
JdbcDialects.registerDialect(HiveDialect)
scala> val jdbcDF = spark.read.format("jdbc").option("url",
"jdbc:hive2://localhost:1/").option("dbtable",
"DEFAULT.KYLIN_ACCOUNT_SIDA").option("user", "***").option("password",
"***").load()
jdbcDF: org.apache.spark.sql.DataFrame = [kylin_account_sida.account_id:
bigint, kylin_account_sida.account_buyer_level: int ... 3 more fields]
scala> jdbcDF.count
res28: Long = 0
scala> jdbcDF.show
+-+--+---+--+--+
|kylin_account_sida.account_id|kylin_account_sida.account_buyer_level|kylin_account_sida.account_seller_level|kylin_account_sida.account_country|kylin_account_sida.account_contact|
+-+--+---+--+--+
+-+--+---+--+--+
{code}
> Lookup tables is empty when build on ORC tables.
>
>
> Key: KYLIN-4038
> URL: https://issues.apache.org/jira/browse/KYLIN-4038
> Project: Kylin
> Issue Type: Bug
>Affects Versions: v2.6.2
> Environment: HDP 3 cluster.
>Reporter: Alexander
>Assignee: XiaoXiang Yu
>Priority: Blocker
> Attachments: image-2019-06-11-16-12-09-472.png
>
>
> Create tables, simple copy of learn_kylin project.
> {code:java}
> // Create tables
> create table kylin_sales_sida (trans_id bigint,part_dt date,lstg_format_name
> string,leaf_categ_id bigint,lstg_site_id int,slr_segment_cd smallint,price
> decimal(19,4),item_count bigint,seller_id bigint,buyer_id bigint,ops_user_id
> string,ops_region string) stored as orc tblproperties
> ("orc.compress"="SNAPPY");
> insert into table kylin_sales_sida select * from kylin_sales;
> create table kylin_country_sida (country string,latitude double,longitude
> double,name string) stored as orc tblproperties ("orc.compress"="SNAPPY");
> insert into table kylin_country_sida select * from kylin_country;
> create table kylin_category_groupings_sida (leaf_categ_id
> bigint,leaf_categ_name string,site_id int,categ_busn_mgr
> string,categ_busn_unit string,regn_categ string,user_defined_field1
> string,user_defined_field3 string,kylin_groupings_cre_date
> string,kylin_groupings_upd_date string,kylin_groupings_cre_user
> string,kylin_groupings_upd_user string,meta_categ_id
> decimal(10,0),meta_categ_name string,categ_lvl2_id
> decimal(10,0),categ_lvl3_id decimal(10,0),categ_lvl4_id
> decimal(10,0),categ_lvl5_id decimal(10,0),categ_lvl6_id
> decimal(10,0),categ_lvl7_id decimal(10,0),categ_lvl2_name
> string,categ_lvl3_name string,categ_lvl4_name string,categ_lvl5_name
> string,categ_lvl6_name string,categ_lvl7_name string,categ_flags
> decimal(10,0),adult_categ_yn string,domain_id
> decimal(10,0),user_defined_field5 string,vcs_id decimal(10,0),gcs_id
> decimal(10,0),move_to decimal(10,0),sap_category_id decimal(10,0),src_id
> tinyint,bsns_vrtcl_name
[jira] [Commented] (KYLIN-4038) Lookup tables is empty when build on ORC tables.
[
https://issues.apache.org/jira/browse/KYLIN-4038?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16860651#comment-16860651
]
XiaoXiang Yu commented on KYLIN-4038:
-
Currently I have reproduced this issue, and after use "sh bin/metastore.sh
backup" and check snapshot file size, I found they are totally different.
!image-2019-06-11-16-12-09-472.png!
> Lookup tables is empty when build on ORC tables.
>
>
> Key: KYLIN-4038
> URL: https://issues.apache.org/jira/browse/KYLIN-4038
> Project: Kylin
> Issue Type: Bug
>Affects Versions: v2.6.2
> Environment: HDP 3 cluster.
>Reporter: Alexander
>Assignee: XiaoXiang Yu
>Priority: Blocker
> Attachments: image-2019-06-11-16-12-09-472.png
>
>
> Create tables, simple copy of learn_kylin project.
> {code:java}
> // Create tables
> create table kylin_sales_sida (trans_id bigint,part_dt date,lstg_format_name
> string,leaf_categ_id bigint,lstg_site_id int,slr_segment_cd smallint,price
> decimal(19,4),item_count bigint,seller_id bigint,buyer_id bigint,ops_user_id
> string,ops_region string) stored as orc tblproperties
> ("orc.compress"="SNAPPY");
> insert into table kylin_sales_sida select * from kylin_sales;
> create table kylin_country_sida (country string,latitude double,longitude
> double,name string) stored as orc tblproperties ("orc.compress"="SNAPPY");
> insert into table kylin_country_sida select * from kylin_country;
> create table kylin_category_groupings_sida (leaf_categ_id
> bigint,leaf_categ_name string,site_id int,categ_busn_mgr
> string,categ_busn_unit string,regn_categ string,user_defined_field1
> string,user_defined_field3 string,kylin_groupings_cre_date
> string,kylin_groupings_upd_date string,kylin_groupings_cre_user
> string,kylin_groupings_upd_user string,meta_categ_id
> decimal(10,0),meta_categ_name string,categ_lvl2_id
> decimal(10,0),categ_lvl3_id decimal(10,0),categ_lvl4_id
> decimal(10,0),categ_lvl5_id decimal(10,0),categ_lvl6_id
> decimal(10,0),categ_lvl7_id decimal(10,0),categ_lvl2_name
> string,categ_lvl3_name string,categ_lvl4_name string,categ_lvl5_name
> string,categ_lvl6_name string,categ_lvl7_name string,categ_flags
> decimal(10,0),adult_categ_yn string,domain_id
> decimal(10,0),user_defined_field5 string,vcs_id decimal(10,0),gcs_id
> decimal(10,0),move_to decimal(10,0),sap_category_id decimal(10,0),src_id
> tinyint,bsns_vrtcl_name string) stored as orc tblproperties
> ("orc.compress"="SNAPPY");
> insert into table kylin_category_groupings_sida select * from
> kylin_category_groupings;
> create table kylin_cal_dt_sida (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,age_for_week_id
> smallint,age_for_dt_id smallint,age_for_rtl_year_id
> smallint,age_for_rtl_qtr_id smallint,age_for_rtl_month_id
> smallint,age_for_rtl_week_id smallint,age_for_cs_week_id
> smallint,day_of_cal_id int,day_of_year_id smallint,day_of_qtr_id
> smallint,day_of_month_id smallint,day_of_week_id int,week_of_year_id
> tinyint,week_of_cal_id int,month_of_qtr_id tinyint,month_of_year_id
> tinyint,month_of_cal_id smallint,qtr_of_year_id tinyint,qtr_of_cal_id
> smallint,year_of_cal_id smallint,year_end_dt string,qtr_end_dt
> string,month_end_dt string,week_end_dt string,cal_dt_name string,cal_dt_desc
> string,cal_dt_short_name string,ytd_yn_id tinyint,qtd_yn_id tinyint,mtd_yn_id
> tinyint,wtd_yn_id tinyint,season_beg_dt string,day_in_year_count
> smallint,day_in_qtr_count tinyint,day_in_month_count
> tinyint,day_in_week_count tinyint,rtl_year_beg_dt string,rtl_qtr_beg_dt
> string,rtl_month_beg_dt string,rtl_week_beg_dt string,cs_week_beg_dt
> string,cal_date string,day_of_week string,month_id string,prd_desc
> string,prd_flag string,prd_id string,prd_ind string,qtr_desc string,qtr_id
> string,qtr_ind string,retail_week string,retail_year string,retail_start_date
> string,retail_wk_end_date string,week_ind string,week_num_desc
> string,week_beg_date string,week_end_date string,week_in_year_id
> string,week_id string,week_beg_end_desc_mdy string,week_beg_end_desc_md
> string,year_id string,year_ind string,cal_dt_mns_1year_dt
> string,cal_dt_mns_2year_dt string,cal_dt_mns_1qtr_dt
> string,cal_dt_mns_2qtr_dt string,cal_dt_mns_1month_dt
> string,cal_dt_mns_2month_dt string,cal_dt_mns_1week_dt
> string,cal_dt_mns_2week_dt string,curr_cal_dt_mns_1year_yn_id
> tinyint,curr_cal_dt_mns_2year_yn_id tinyint,curr_cal_dt_mns_1qtr_yn_id
> tinyint,curr_cal_dt_mns_2qtr_yn_id tinyint,curr_cal_dt_mns_1month_yn_id
> tinyint,curr_cal_dt_mns_2month_yn_id tinyint,curr_cal_dt_mns_1week_yn_ind
> tinyint,curr_cal_dt_mns_2week_yn_ind tinyint,rtl_month_of_rtl_year_id
> string,rtl_qtr_of_rtl_year_id
[jira] [Commented] (KYLIN-4038) Lookup tables is empty when build on ORC tables.
[
https://issues.apache.org/jira/browse/KYLIN-4038?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16860603#comment-16860603
]
Alexander commented on KYLIN-4038:
--
Hello,
In my case i use MANAGED ORC tables.
As i see, managed orc tables not working in Spark too...
Is it any kind of limitation of Hive tables usage?
> Lookup tables is empty when build on ORC tables.
>
>
> Key: KYLIN-4038
> URL: https://issues.apache.org/jira/browse/KYLIN-4038
> Project: Kylin
> Issue Type: Bug
>Affects Versions: v2.6.2
> Environment: HDP 3 cluster.
>Reporter: Alexander
>Assignee: XiaoXiang Yu
>Priority: Blocker
>
> Create tables, simple copy of learn_kylin project.
> {code:java}
> // Create tables
> create table kylin_sales_sida (trans_id bigint,part_dt date,lstg_format_name
> string,leaf_categ_id bigint,lstg_site_id int,slr_segment_cd smallint,price
> decimal(19,4),item_count bigint,seller_id bigint,buyer_id bigint,ops_user_id
> string,ops_region string) stored as orc tblproperties
> ("orc.compress"="SNAPPY");
> insert into table kylin_sales_sida select * from kylin_sales;
> create table kylin_country_sida (country string,latitude double,longitude
> double,name string) stored as orc tblproperties ("orc.compress"="SNAPPY");
> insert into table kylin_country_sida select * from kylin_country;
> create table kylin_category_groupings_sida (leaf_categ_id
> bigint,leaf_categ_name string,site_id int,categ_busn_mgr
> string,categ_busn_unit string,regn_categ string,user_defined_field1
> string,user_defined_field3 string,kylin_groupings_cre_date
> string,kylin_groupings_upd_date string,kylin_groupings_cre_user
> string,kylin_groupings_upd_user string,meta_categ_id
> decimal(10,0),meta_categ_name string,categ_lvl2_id
> decimal(10,0),categ_lvl3_id decimal(10,0),categ_lvl4_id
> decimal(10,0),categ_lvl5_id decimal(10,0),categ_lvl6_id
> decimal(10,0),categ_lvl7_id decimal(10,0),categ_lvl2_name
> string,categ_lvl3_name string,categ_lvl4_name string,categ_lvl5_name
> string,categ_lvl6_name string,categ_lvl7_name string,categ_flags
> decimal(10,0),adult_categ_yn string,domain_id
> decimal(10,0),user_defined_field5 string,vcs_id decimal(10,0),gcs_id
> decimal(10,0),move_to decimal(10,0),sap_category_id decimal(10,0),src_id
> tinyint,bsns_vrtcl_name string) stored as orc tblproperties
> ("orc.compress"="SNAPPY");
> insert into table kylin_category_groupings_sida select * from
> kylin_category_groupings;
> create table kylin_cal_dt_sida (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,age_for_week_id
> smallint,age_for_dt_id smallint,age_for_rtl_year_id
> smallint,age_for_rtl_qtr_id smallint,age_for_rtl_month_id
> smallint,age_for_rtl_week_id smallint,age_for_cs_week_id
> smallint,day_of_cal_id int,day_of_year_id smallint,day_of_qtr_id
> smallint,day_of_month_id smallint,day_of_week_id int,week_of_year_id
> tinyint,week_of_cal_id int,month_of_qtr_id tinyint,month_of_year_id
> tinyint,month_of_cal_id smallint,qtr_of_year_id tinyint,qtr_of_cal_id
> smallint,year_of_cal_id smallint,year_end_dt string,qtr_end_dt
> string,month_end_dt string,week_end_dt string,cal_dt_name string,cal_dt_desc
> string,cal_dt_short_name string,ytd_yn_id tinyint,qtd_yn_id tinyint,mtd_yn_id
> tinyint,wtd_yn_id tinyint,season_beg_dt string,day_in_year_count
> smallint,day_in_qtr_count tinyint,day_in_month_count
> tinyint,day_in_week_count tinyint,rtl_year_beg_dt string,rtl_qtr_beg_dt
> string,rtl_month_beg_dt string,rtl_week_beg_dt string,cs_week_beg_dt
> string,cal_date string,day_of_week string,month_id string,prd_desc
> string,prd_flag string,prd_id string,prd_ind string,qtr_desc string,qtr_id
> string,qtr_ind string,retail_week string,retail_year string,retail_start_date
> string,retail_wk_end_date string,week_ind string,week_num_desc
> string,week_beg_date string,week_end_date string,week_in_year_id
> string,week_id string,week_beg_end_desc_mdy string,week_beg_end_desc_md
> string,year_id string,year_ind string,cal_dt_mns_1year_dt
> string,cal_dt_mns_2year_dt string,cal_dt_mns_1qtr_dt
> string,cal_dt_mns_2qtr_dt string,cal_dt_mns_1month_dt
> string,cal_dt_mns_2month_dt string,cal_dt_mns_1week_dt
> string,cal_dt_mns_2week_dt string,curr_cal_dt_mns_1year_yn_id
> tinyint,curr_cal_dt_mns_2year_yn_id tinyint,curr_cal_dt_mns_1qtr_yn_id
> tinyint,curr_cal_dt_mns_2qtr_yn_id tinyint,curr_cal_dt_mns_1month_yn_id
> tinyint,curr_cal_dt_mns_2month_yn_id tinyint,curr_cal_dt_mns_1week_yn_ind
> tinyint,curr_cal_dt_mns_2week_yn_ind tinyint,rtl_month_of_rtl_year_id
> string,rtl_qtr_of_rtl_year_id tinyint,rtl_week_of_rtl_year_id
> tinyint,season_of_year_id tinyint,ytm_yn_id tinyint,ytq_yn_id
>
