mysql 分桶_impala建表,并分桶

创建外部表并分桶：

CREATE EXTERNAL TABLE ml_tempdb.terry_add_forver_heros

(roleid DECIMAL(20,0),registday_buy BIGINT,level BIGINT,activedays BIGINT, chargediamond BIGINT,curdiamond BIGINT,curbattle_points BIGINT,curticket BIGINT,operate STRING,

heroid BIGINT, is_spend_diamond BIGINT)

partitioned by (logymd STRING)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\t'

-- LINES TERMINATED BY '\n'STORED AS TEXTFILE LOCATION 's3a://moonton-test/test/terryzeng/product_recommend/terry_add_forver_heros'

存入数据：

INSERT INTO ml_tempdb.terry_add_forver_heros

partition(logymd)

SELECT DISTINCT roleid,datediff(logymd,usercreateymd)registday_buy,level,activedays,chargediamond,curdiamond,curbattle_points,curticket,operate,heroid,is_spend_diamond,logymd

FROM ml_behavior.add_hero

WHERE time_len=0 AND logymd>="2016-11-04" AND logymd<="2020-07-14"

创建外部表未分桶：

CREATE EXTERNAL TABLE IF NOT EXISTS terry_mla_mlbb_base_09

(usercreateymd STRING,logymd_mla STRING,accountid DECIMAL(20,0),adjust_adid STRING,adjust_adid_mla STRING)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\t'

LINES TERMINATED BY '\n'

STORED AS TEXTFILE LOCATION "s3a://moonton-test/test/terryzeng/MLA-09/MLA-MLBB-adjust_adid_base_09"

存入数据：

INSERT INTO ml_tempdb.terry_mla_mlbb_base_09

SELECT a.logymd as usercreateymd,b.logymd logymd_mla,accountid,a.adjust_adid,b.adjust_adid AS adjust_adid_mla FROM

(

SELECT logymd,accountid,adjust_adid,a.country FROM ml_basic.adv_install a

WHERE EXISTS(SELECT adjust_adid FROM ml_tempdb.terry_mla_adjust_09 b WHERE a.accountid=b.accountid) GROUP BY logymd,accountid,adjust_adid,a.country

)a

LEFT JOIN

(SELECT DISTINCT adjust_adid,logymd FROM ml_tempdb.terry_mla_adjust_09)b on a.adjust_adid=b.adjust_adid