Hive exmaple

hive-ddl-example.hql

-- Hive db.
use rds;
alter table customer add columns 
  ( shipping_address varchar(50) comment 'shipping_address' 
  	, shipping_zip_code int comment 'shipping_zip_code'
  	, shipping_city varchar(30) comment 'shipping_city'
  	, shipping_state varchar(2) comment 'shipping_state'
  	);

  alter table sales_order add columns(order_quantity int comment 'order_quantity');



use dw;

-- 修改客户信息表
-- 原表改名作为备份表

alter table customer_dim rename to customer_dim_old ; 

create table customer_dim (

	customer_sk int comment 'surrogate key',
	customer_number int comment 'number',
	customer_name varchar(50) comment 'name', 
	customer_street_address varchar(50) comment 'address',
	customer_zip_code int comment 'zipcode',
	customer_city varchar(30) comment 'city',
	customer_state varchar(2) comment 'state',
	shipping_address varchar(50) comment 'shipping_address',
	shipping_zip_code int comment 'shipping_zip_code',
	shipping_city varchar(30) comment 'shipping_city',
	shipping_state varchar(2) comment 'shipping_state',
	version int comment 'version',
	effective_date date comment 'effective_date',
	expiry_date date comment 'expiry date'
	)
clusterd by (customer_sk) into 8 buckets 
stored as orc tblproperties ('transactional' = 'true' )
;


-- 导入备份表数据

insert into customer_dim 
select customer_sk, customer_number, customer_name , 
  customer_street_address, customer_zip_code, customer_city
  , customer_state, null, null, null, null, version, effective_date , expiry_date
  from customer_dim_old

-- 删除备份表
drop table customer_dim_old; 


-- 修改销售订单事实表

alter table sales_order_fact rename to sales_order_fact_old; 
create table sales_order_fact (
	order_sk int comment 'order surrogate key', 
	customer_sk int comment 'customer surrogate key',
	product_sk int comment 'product surrogate key',
	order_date_sk int comment 'date surrogate key',
	order_amount decimal(10, 2) comment 'order amount', 
	order_quantity int comment 'order quantity'
	)
clustered by (order_sk) into 8 BUCKETS
stored as orc tblproperties('transactional'='true');


insert into sales_order_fact select *, null from sales_order_fact_old ;

drop table sales_order_fact_old;