Hive_HQL

**

Hive##

**
作为第一篇博客,本章主要回顾学习的hive相关知识—hiveQL

– 创建表
create table tabelName(order_id String,user_id String)
row format delimited fields terminated by “,”
line terminated by “\n”;

– 导入数据
load data local inpath “/home/order_id.csv” overwrite into table orders

– 删除表
drop table if exists orders
– 创建外部表
create external table orders (order_id String)
row format delimited fields terminated by “,”
line terminated by “\n”
location “hdfs:\\data”
– 创建分区表,多个分区即含有多个字段
create table orders(order_id String)
partitioned by(order_id String,user_id)

– 向分区中导入本地数据 ----HDFS 不加local
load data local inpath “\hoem\orders.csv” into table orders partition by(dt =“2018”)
– 导入本地数据是复制,导入HDFS数据是移动
– 向表中插入单条数据
insert into table t1 values(“…”,“…”)
– 向表中插入多条数据
insert into | overwrite table t1 select * from orders
– 导出数据到HDFS
insert overwrite directory “/root/data”
row format delimited fields terminated by “,”
line terminatted by “\n”
select * from orders
insert overwrite local dictory “XXX”
– 导入数据到本地磁盘,加个local
insert overwrite local directory “/root”
row format delimited fields terminated by “,”
line terminated by “n”
– hive 支持文件存储成text parquet sequence格式
create table orders (order_id String,User_id String)
row format delimited fileds terminated by “,”
stored as textfile | sequencefile | parquetfile
– 从文本中查询数据插入sequencefile表
insert into tablename
select * from orders
– 日期类型有date 适用于创表时2021-9-14的类型
create table orders (order_id String,birthday date)
row format delimited fileds terminated by “,”
– 创建表的时候可以创建数组类型
create table orders (order_id String,user_id array)
row format delimated fileds termianated by “,”
– 创建表的时候可以创建Map类型
– 数据格式如下
– 1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,28
– 2,lisi,father:mayun#mother:huangyi#brother:guanyu,22
create table orders (order_id String,user_id map<String,String>)
row format delimited fields termianated by “,”
collection items terminated by “#”
map keys terminated by “:”
– 修改表明
alter table t1 rename to t2
– 修改分区名
alter table t1_partition
partition(department=‘xiangsheng’,sex=‘male’,howold=20)
rename to partition(department=‘1’,sex=‘1’,howold=20);
– 添加分区
alter table t1 add partition (department=‘xiangsheng’)
– 删除分区
alter table t1 drop partition(department =‘xiangsheng’)
– 修改列名
alter table t1 change col_old_name col_new_name col_new_type first;
– 增加/替换列
alter table t1 add columns (sex string,addr string);
alter tab