hadoop学习笔记之HiveSQL DDL

Hive SQL


1. Numeric Types: tinyint/smallint/int/bigint/float/double/decimal
2. Date/Time Types: timestamp/date
3. String Types: string/varchar/char
4. Misc Types: boolean/binary
5. Complex Types: arrays/maps/structs/union
Example: array[0]  map["key"]  struct.xx


DDL数据定义语言
库操作
1. create database tem
2. drop database tem
3. alter database dbname set ...
4. use tem


表操作
1. create talbe temp.test_create_table(sid int, sname string);内部表，受hive管理
2. create EXTERNAL table temp.test_create_table(sid int, sname string) partitioned by (dt string) location '...';外部表，hive只创建一个元数据指向它，删除表时只删除了元数据。
3. create table temp.table1 as select * from templ.dual;不支持外部表和分区表
4. create table temp.table2 like temp.dual;不支持外部表和分区表
5. 临时表只对当前session有效
6. drop table temp.table1  开启回收站参数，被删表在用户目录下的.Trash中保留一段时间，恢复直接拷贝出来
7. truncate table temp.table2 [partition dt='2015']
8. alter table temp.table2 rename to temp.table3
9. alter table temp.table2 set tblproperties('comment'=new_comment)
10. alter table properties改变参数
11. add serde properties设置与序列化反序列化相关属性
12. alter table storage properties 设置与存储相关属性


分区操作
1. alter table table_name add partition(partCol='value1') location 'loc1'增加分区
2. alter table table_name partition partition_spec rename to partition partition_spe分区改名
3. alter table table_name exchange partition(partition_spec) with table table_name_2交换分区
4. alter table table_name drop [if exists] partition partition_spec, partition...删除分区
5. alter table table_name archive partition partition_spec;归档分区


列操作
1. alter table table_name [partition partitionSpec] set fileformat file_format
2. alter table table_name [partition partitionSpec] set location "new location"
3. alter table table_name change [column] col_old_name col new_name column_type [comment col_comment] [first|after column_name|]

4. alter talbe table_name add |replace columns(col_name data_type [comment col_comment],...)

视图
create view [if not exists] view_name [(column_name [comment column_comment], ...)]
[comment view_comment]
[tblproperties (property_name=property_value, ...)]
as select
视图可用来做权限控制


删除视图 drop view [if exists] view_name
重建视图 alter view view_name as select_statement
创建索引 create index index_name on table base_table_name( col_name, ...) as index_type    (用得少)
删除索引 drop index [if exists] index_name on table_name
索引重建 alter index index_name on table_name [partition partitionSpec] rebuild




创建临时函数 create temporary function function_name as class_name
删除临时函数 drop temporary function [if exists] function_name
创建指定类名的函数 create function [db_name] function_name as class_name [using jar|file|archive 'file_url' [,jar|file|archive 'file_url'] ]
删除函数
drop function [if exists] function_name


权限执行
crant priv_type [on object_type] to principal_specification
revoke priv_type [on object_type] from principal_specification


建议按角色来管理
create role role_name
drop role role_name
show current roles
set role (role_name|all)
show roles
grant role_name [,role_name] ... to principal_specification [, prinprincipal_specification]... [with admin option];
revoke [admin option for] role_name [, role_name]...from principal_specification [,principal_specification]...;


展示
show grant [principle_name] on (all | ([table]) table_or_view_name);
show (database|schemas) [like identifier_with_wildcards];
show create table ([db_name.] table_name|view_name);
show functions "a.*"匹配任何字符


注意：show views命令没有，显示图视也用show table。但drop table不能删除view。


定义
describe [extended] databade db_name