Hive命令

列出存在的DB

show databases;

生成db

create database if not exists bookdb;

删除db

drop databases bookdb;

生成表

语法

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[ROW FORMAT row_format]
[STORED AS file_format]

实例

create table if not exists usrtb(uid int, name string, age int, sex string) 
comment 'user info' 
partitioned by (month string) 
row format delimited 
fields terminated by '\t' 
lines terminated by '\n' 
stored as textfile;

生成包含array，map的table

create table readhistory(
  id bigint,
  usrname string,
  read array<string>,
  add map<String,string>
) 
partitioned by (month int)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
;

对应的数据文件内容如下

1,张一,Master C-Java-Python-Golang,历史:明朝那些事-军事:中途岛-人物:巴顿-推理:福尔摩斯    
2,王二,斗罗大陆-大主宰-斗破苍穹,网文:盗墓笔记-鸡汤:心灵鸡汤
3,李四,万古天帝-金牌县令-绝世符神,玄幻:元尊-仙侠:剑来-奇闻:山海秘藏

生成外部表时加external并且指定存储位置

create external table e_readhistory(
  id bigint,
  usrname string,
  read array<string>,
  add map<String,string>
) 
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
stored as textfile
location '/user/hive/external/rh_external_table';

load数据和内部表一样
通过HDFS命令查看

hdfs dfs -ls /user/hive/external/rh_external_table

可以看到load的文件
外部表通过drop table删除后存储在hdfs上的数据文件并不会被删除。内部表会删除

改变表

语法

ALTER TABLE name RENAME TO new_name
ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...])
ALTER TABLE name DROP [COLUMN] column_name
ALTER TABLE name CHANGE column_name new_name new_type
ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec ...])

加载数据(到指定分区)

load data inpath '/binTest/202001' overwrite into table usrdb.usrtb partition(month='202001');

删除分区

alter table readhistory drop partition(month='202001');

查找

指定分区查找

select * from usrdb.usrtb where uid > 95100 AND usrtb.month='202001';

生成view

create view man_view as select * from usrdb.usrtb where sex like '男';

显示表的分区

show partitions usrtb;

创建索引

create index index_id on table readhistory(id) as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild;

连接

join只展示符合条件的

select u.uid, u.name, u.sex, r.read from usrtb u join readhistory r on (u.uid = r.id);

左外连接

left outer join左侧的全部展示，即便右侧没有匹配到的显示NULL

select u.uid, u.name, u.sex, r.read from usrtb u left outer join readhistory r on (u.uid = r.id);

右外连接

right outer join右侧全部展示，即便左侧没有匹配的显示NULL

select u.uid, u.name, u.sex, r.read from usrtb u right outer join readhistory r on (u.uid = r.id);

全外连接

full outer join包含两个表所有的记录，如果任一边缺少匹配结果则显示NULL

select u.uid, u.name, u.sex, r.read from usrtb u full outer join readhistory r on (u.uid = r.id);