将数据导入kudu表(建立临时hive表,从hive导入kudu)步骤

步骤一 hue中选择impala,建立kudu表(注意:hue中选择hive无法建立kudu表,因为hive不支持)

步骤二 hue中选择hive,建立hive临时表

drop table dim_bi_productspe_scd_hive;
CREATE TABLE if not exists dim_bi_productspe_scd_hive(
      BIProductSpeKey            bigint       
     ,BIProductSpeID             bigint       
     ,ProductSpecId              bigint       
     ,ownerType                     int       
     ,ownerid                    bigint       
     ,CityId                        int       
     ,RealWarehouseId               int       
     ,TotalCount_MinUnit         double       
     ,TotalCount_MaxUnit         double       
     ,CreateTime              timestamp       
     ,lastupdatetime          timestamp       


     ,ProductInfoID                bigint      
     ,SpeName                      string      
     ,SpeInfoMaxUnit               string      
     ,SpeInfoMinUnit               string      
     ,SpeInfoQuantity              double      

     ,BrandName                    string      
     ,StatisticsCategoryName       string      
     ,productInfoName              string      
     ,OriginalPlace                string      
     ,ProductInfoStatusID             int      
     ,ProductInfoStatus            string      
     ,BottleCode                   string      
     ,ProductCode                  string      
     ,packagingCode                string      
     ,ProductInfoTypeID               int      
     ,ProductInfoType              string      
     ,ProductStatisticsClass       bigint      
     ,SecondStatisticsClass        bigint      
     ,ShopId                       bigint      
     ,ShelfLifeLongTime           tinyint      
     ,PackageType                 tinyint      
     ,IsProcess                   tinyint      
     ,StorageType                 tinyint      

     ,ERPMinUnitCostPrice         double       
     ,ERPMaxUnitCostPrice         double       
     ,FirstDisPlayCategoryId         int       
     ,FirstDisPlayCategory        string       
     ,SecondDisPlayCategoryId        int       
     ,SecondDisPlayCategory       string       
     ,ThirdDisPlayCategoryId         int       
     ,ThirdDisPlayCategory        string       
     ,BrandID                       int      
     ,ProductBusinessClassID        int       
     ,ProductBusinessClass       string       
     ,UnitPriceClassID              int       
     ,UnitPriceClass             string       

    ,IsValid                     tinyint       
    ,ValidFrom                 timestamp       
    ,ValidTo                   timestamp       
    ,ETLCreateTime             timestamp       
    ,ETLLastUpdateTime         timestamp       
)
partitioned by(`day` string)
ROW FORMAT DELIMITED
-- FIELDS TERMINATED BY '\001'
FIELDS TERMINATED BY ',' -- 注意这里换成csv文件中列间隔符逗号','
TBLPROPERTIES('skip.header.line.count'='1');

步骤三 上传文件到服务器

SecureCRT 进入指定上传目录,ALT+P快捷键进入SFTP传输界面。

步骤四 将服务器中的上传文件加载进hive临时表(hue中选择hive进行)

注意:是追加写入

load data local inpath '/tmp/export_import/query-impala-93995.csv' overwrite into table dim_bi_productspe_scd_hive partition(day='20200922');

步骤五 将hive临时表中的数据upsert 到kudu表中(hue中选择impala进行)

建议使用upsert而非insert

upsert into table dim_bi_productspe_scd (
BIProductSpeKey        
,BIProductSpeID         
,ProductSpecId          
,ownerType              
,ownerid                
,CityId                 
,RealWarehouseId        
,TotalCount_MinUnit     
,TotalCount_MaxUnit     
,CreateTime             
,lastupdatetime         
,ProductInfoID             
,SpeName                   
,SpeInfoMaxUnit            
,SpeInfoMinUnit            
,SpeInfoQuantity           
,BrandName             
,StatisticsCategoryName
,productInfoName       
,OriginalPlace         
,ProductInfoStatusID   
,ProductInfoStatus     
,BottleCode            
,ProductCode           
,packagingCode         
,ProductInfoTypeID     
,ProductInfoType       
,ProductStatisticsClass
,SecondStatisticsClass 
,ShopId                
,ShelfLifeLongTime        
,PackageType              
,IsProcess                
,StorageType              
,ERPMinUnitCostPrice      
,ERPMaxUnitCostPrice      
,FirstDisPlayCategoryId 
,FirstDisPlayCategory   
,SecondDisPlayCategoryId
,SecondDisPlayCategory  
,ThirdDisPlayCategoryId 
,ThirdDisPlayCategory   
,BrandID                
,ProductBusinessClassID 
,ProductBusinessClass   
,UnitPriceClassID       
,UnitPriceClass         
,IsValid                 
,ValidFrom               
,ValidTo                 
,ETLCreateTime           
,ETLLastUpdateTime
) 
select
BIProductSpeKey        
,BIProductSpeID         
,ProductSpecId          
,ownerType              
,ownerid                
,CityId                 
,RealWarehouseId        
,TotalCount_MinUnit     
,TotalCount_MaxUnit     
,CreateTime             
,lastupdatetime         
,ProductInfoID             
,SpeName                   
,SpeInfoMaxUnit            
,SpeInfoMinUnit            
,SpeInfoQuantity           
,BrandName             
,StatisticsCategoryName
,productInfoName       
,OriginalPlace         
,ProductInfoStatusID   
,ProductInfoStatus     
,BottleCode            
,ProductCode           
,packagingCode         
,ProductInfoTypeID     
,ProductInfoType       
,ProductStatisticsClass
,SecondStatisticsClass 
,ShopId                
,ShelfLifeLongTime        
,PackageType              
,IsProcess                
,StorageType              
,ERPMinUnitCostPrice      
,ERPMaxUnitCostPrice      
,FirstDisPlayCategoryId 
,FirstDisPlayCategory   
,SecondDisPlayCategoryId
,SecondDisPlayCategory  
,ThirdDisPlayCategoryId 
,ThirdDisPlayCategory   
,BrandID                
,ProductBusinessClassID 
,ProductBusinessClass   
,UnitPriceClassID       
,UnitPriceClass         
,IsValid                 
,ValidFrom               
,ValidTo                 
,ETLCreateTime           
,ETLLastUpdateTime
from dim_bi_productspe_scd_hive; 
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页