步骤一 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;