选择分桶字段和计算分桶的正确方法
一、 选出合理的分桶字段。
可以拿 (字段中重复值最多的记录数/表的总记录数) 的值作为是否可以作为分桶字段的依据。
查询字段中重复值最多的记录数:
COL_COUNT=select column1,count(1) cnt from table_name group by column1 order by cnt desc limit 1;
查询表的总记录数
TOTAL_COUNT=select count(1) from table_name;
如果COL_COUNT/ TOTAL_COUNT的值小于5%,或者COL_COUNT的值在100万以下,那么该字段可作为分桶字段。
特别的,如果一个表中确实找不到分布均匀的字段作为分桶,可以单独对表追加一个唯一ID作为分桶字段。
以a)中PIMS_PMART2.TB_FCT_VIP_S_DET_FL_M表为例,通过对该表每个字段做分析发现,custcd 这个字段COL_COUNT= 2114438 ,TOTAL_COUNT=2357030761 ,两者比值仅为0.09% ,非常适合作为分桶字段。
在所有的字段中,根据字段的设计,优先找一些看起来会是散列分布的字段,执行计算公式,来确定是否适合作为分桶字段。
select cs_order_number, count(1) cnt from test_orc.holo_catalog_sales_orc group by cs_order_number order by cnt desc limit 1; -- 14
select count(1) from test_orc.holo_catalog_sales_orc; -- 2880058
二、 算出合理的分桶数
PIMS_PMART2.TB_FCT_VIP_S_DET_FL_M该表已经存在数据的分区每个分区约60000万条记录,TXT格式8000MB。根据2.2.2提到的计算方法,
分桶数=MAX(60000万/1000万, 8000M/500M) = 60
为了使数据分布更加均匀,我们一般建议选择质数作为分桶数。所以该表的最佳分桶数为59或61。
根据数据量,考虑实际的分桶最佳值,此外也要根据计算的时候,需要的task数据量。
CREATE TABLE test_orc.catalog_sales(
cs_sold_date_sk int DEFAULT NULL COMMENT ' ',
cs_sold_time_sk int DEFAULT NULL COMMENT ' ',
cs_ship_date_sk int DEFAULT NULL COMMENT ' ',
cs_bill_customer_sk int DEFAULT NULL COMMENT ' ',
cs_bill_cdemo_sk int DEFAULT NULL COMMENT ' ',
cs_bill_hdemo_sk int DEFAULT NULL COMMENT ' ',
cs_bill_addr_sk int DEFAULT NULL COMMENT ' ',
cs_ship_customer_sk int DEFAULT NULL COMMENT ' ',
cs_ship_cdemo_sk int DEFAULT NULL COMMENT ' ',
cs_ship_hdemo_sk int DEFAULT NULL COMMENT ' ',
cs_ship_addr_sk int DEFAULT NULL COMMENT ' ',
cs_call_center_sk int DEFAULT NULL COMMENT ' ',
cs_catalog_page_sk int DEFAULT NULL COMMENT ' ',
cs_ship_mode_sk int DEFAULT NULL COMMENT ' ',
cs_warehouse_sk int DEFAULT NULL COMMENT ' ',
cs_item_sk int DEFAULT NULL COMMENT ' ',
cs_promo_sk int DEFAULT NULL COMMENT ' ',
cs_order_number int DEFAULT NULL COMMENT ' ',
cs_quantity int DEFAULT NULL COMMENT ' ',
cs_wholesale_cost float DEFAULT NULL COMMENT ' ',
cs_list_price float DEFAULT NULL COMMENT ' ',
cs_sales_price float DEFAULT NULL COMMENT ' ',
cs_ext_discount_amt float DEFAULT NULL COMMENT ' ',
cs_ext_sales_price float DEFAULT NULL COMMENT ' ',
cs_ext_wholesale_cost float DEFAULT NULL COMMENT ' ',
cs_ext_list_price float DEFAULT NULL COMMENT ' ',
cs_ext_tax float DEFAULT NULL COMMENT ' ',
cs_coupon_amt float DEFAULT NULL COMMENT ' ',
cs_ext_ship_cost float DEFAULT NULL COMMENT ' ',
cs_net_paid float DEFAULT NULL COMMENT ' ',
cs_net_paid_inc_tax float DEFAULT NULL COMMENT ' ',
cs_net_paid_inc_ship float DEFAULT NULL COMMENT ' ',
cs_net_paid_inc_ship_tax float DEFAULT NULL COMMENT ' ',
cs_net_profit float DEFAULT NULL COMMENT ' '
)
clustered by (cs_order_number) into 5 buckets
stored as orc ;