I'm using hive 0.9.0 On Thursday, May 31, 2012, Bruce Bian wrote:
> Hi, > I've got a table vt_new_data which is defined as follows: > CREATE TABLE VT_NEW_DATA > ( > V_ACCOUNT_NUM string > ,V_ACCOUNT_MODIFIER_NUM string > ,V_DEPOSIT_TYPE_CD string > ,V_DEPOSIT_TERM int > ,V_LEDGER_SUBJECT_ID string > ,V_ACCOUNTING_ORG_CD string > ,V_OPEN_DT string > ,V_CLOSE_DT string > ,V_CURRENCY_CD string > ,V_ACCOUNT_BAL float > ,V_INNER_MONTH_DELAY_ACCUM float > ) CLUSTERED BY (V_ACCOUNT_NUM,V_ACCOUNT_MODIFIER_NUM) SORTED BY > (V_ACCOUNT_NUM,V_ACCOUNT_MODIFIER_NUM ASC) INTO 256 BUCKETS STORED AS > RCFile; > > when I execute the following query > explain insert into table vt_new_data select * from vt_new_data limit 1; > (this is just a test) > an FAILED: Error in semantic analysis: Bucketized tables do not support > INSERT INTO: Table: vt_new_data error is occurred > > but when I execute the query: > explain insert into table vt_new_data > select /*+ MAPJOIN(T4) */ > t1.account_num as v_account_num > ,t1.account_modifier_num as v_account_modifier_num > ,'3006' as v_deposit_type_cd > ,0 as v_deposit_term > ,'23201000' v_ledger_subject_id > ,coalesce(t2.party_id,'') as v_accounting_org_cd > ,coalesce(t3.card_begin_dt,'19000101') as v_open_dt > ,coalesce(t3.card_live_dt,'19000101') as v_close_dt > ,coalesce(t4.currency_cd,substr(t1.account_modifier_num,3,3)) as > v_currency_cd > ,coalesce(t4.agt_amt,0) as v_account_bal > ,0 as v_inner_month_delay_accum > from t03_e_cash_bucket t1 > left outer join t03_agt_amount_h_bucket t4 > on t1.account_num=t4.account_num > and t1.account_modifier_num=t4.account_modifier_num > and t4.agt_amt_type_cd = '001' > and t4.start_date<='$TXNDATE' > and t4.end_date>'$TXNDATE' > left outer join t01_party_card_rela_h_bucket t2 > on t1.card_no = t2.card_no > and t2.party_card_rela_type_cd = '01' > and t2.start_date<='$TXNDATE' > and t2.end_date>'$TXNDATE' > left outer join t03_card_bucket t3 > on t1.card_no = t3.card_no; > > the execution plan is generated successfully and triggered an SMB Map > Join, which is great. > > But I don't see the difference here? As both are inserting into a > bucketized and sorted table? >
