Hive 创建外部表 原

hdfs dfs -put stocks /user/bigdata

create external table if not exists stocks_external(
ymd date,
price_open float,
price_high float,
price_low float,
price_close float,
volume int,
price_adj_close float
)
partitioned by (exchanger string,symbol string)
row format delimited fields terminated by ','
location '/user/bigdata/stocks';

alter table stocks_external add partition(exchanger="NASDAQ", symbol="AAPL") 
location '/user/bigdata/stocks/NASDAQ/AAPL';

alter table stocks_external add partition(exchanger="NASDAQ", symbol="INTC") 
location '/user/bigdata/stocks/NASDAQ/INTC';

alter table stocks_external add partition(exchanger="NYSE", symbol="IBM") 
location '/user/bigdata/stocks/NYSE/IBM';

alter table stocks_external add partition(exchanger="NYSE", symbol="GE") 
location '/user/bigdata/stocks/NYSE/GE';

-- 展示分区信息
show partitions stocks_external;

select * from stocks_external where exchanger = 'NASDAQ' and symbol = 'AAPL' limit 10;

select ymd, price_close from stocks_external where exchanger = 'NASDAQ' and symbol = 'AAPL' limit 10;

select exchanger, symbol, count(*) from stocks_external group by exchanger,symbol;

select exchanger, symbol, max(price_high) from stocks_external group by exchanger, symbol;

-- 删除外部表
drop table stocks_external;
-- 查看 hdfs 上的数据，删除外部表是只删除表的元数据，不删除表的实际数据，这点和
hdfs dfs -ls /user/bigdata