Ctas With Three Table Joins In Two Different Databases In a Subquery

 Ctas With Three Table Joins In Two Different Databases In a Subquery(hands on explanation)

--------
hive> describe default.pd_det;
OK
dept                    string                                    
cost                    int                                       
id                      int                                       
asmb_city               string                                    
asmb_ct                 string                                    
retail                  int                                       
country                 string                                    
         
# Partition Information         
# col_name                data_type               comment           
         
country                 string 

------
hive> describe default.item_avgprofit_byctry ;
OK
avgprf                  int                                        
makein                  string                                     
itemshipped             int                                        
Time taken: 2.596 seconds, Fetched: 3 row(s)
----
hive> describe customersales.product_part ;
OK
prod_name               string                                     
description             varchar(20)                                
qty_onhand              int                                        
prod_num                bigint                                     
packaged_with           array<string>                              
category                string                                     
        
# Partition Information        
# col_name                data_type               comment            
        
category                string                                     
Time taken: 0.185 seconds, Fetched: 11 row(s)
-----
Step 2:  A join between three tables
table 1:
default.pd_det a (join column :a.country)
table 2:
default.item_avgprofit_byctry (join cloumn:b.makein)
table3:
  customersales.product_part c (join column: c.prod_num with a.id)
______________________
Step 3: CTAS

create table threejoin_ctastwodifferentdb stored as textfile as

select t3.cost,t3.pdid,t3.madein,t3.pdname from
 ( 
select a.cost as cost, a.id as pdid, b.makein as madein, c.prod_name as pdname from default.pd_det a
join default.item_avgprofit_byctry b on (a.country = b.makein)
 join  customersales.product_part c on (a.id = c.prod_num)
 )t3

------
hive> describe threejoin_ctastwodifferentdb;
OK
cost                    int                                        
pdid                    int                                        
madein                  string                                     
pdname                  string                                     
Time taken: 1.17 seconds, Fetched: 4 row(s)

CTAS has these restrictions:(which we created in the above example " threejoin_ctastwodifferentdb"cannot be

    The target table cannot be a partitioned table.
    The target table cannot be an external table.
    The target table cannot be a list bucketing table.