hive常见的DDL操作

一  Create/Drop/Truncate table

1.1创建表

1管理表和外部表(略)

2存储格式(略)

3ROW FORMATS & Serde

SerDe:是Serialize/Deserialize的缩写. 就是Hive中用户序列化和反序列话。主要作用就是将某种格式的文件转化为Hive可以使用的记录

我们知道序列化：一般是为了方便将对象保存到文件；另外一个是方便对象的 网络传输。

反序列化：则是为了将字节流转换成对象

在Hive运行的时候序列化和反序列化的流程

HDFSfiles --> InputFileFormat --> <key, value> --> Deserializer--> Row object

Rowobject --> Serializer --> <key, value> --> OutputFileFormat--> HDFS files

3.1常用的SerDe

3.1.1MetadataTypedColumnsetSerDe

读写CSV,TSV文件的Serde

3.1.2LazySimpleSerDe

是Hive默认的Serde可以读写和MetadataTypedColumnsetSerDe一样格式的数据，但是它使用lazy方式创建对象，同时支持使用指定的字符编码读写数据， ALTERTABLE person SET SERDEPROPERTIES ('serialization.encoding'='GBK');

3.1.3ThriftSerDe

支出读写Thrift序列化之后的数据

3.1.4JsonSerde

支持读写Json格式的数据

3.1.5AvroSerde、ORCSerde、ParquetSerde

支持读写avro,orc,parquet这些存储格式文件的数据

3.1.6RegexSerde

支持正则表达式的Serde

 

4分区表(略)

5CTAS、Like（略）

6Bucketed Sorted Tables(略)

7skewed tables（略）

8临时表(略)

 

 

 

 

 

1.2删除表

DROPTABLE IF EXISTS tb_name [PURGE]；

如果不指定PURGE,默认会把删除的数据线放在垃圾桶里，而使用PURGE则是不会放在垃圾桶，就是真实的删掉了。

 

1.3TRUNCATE TABLE

TRUNCATETABLE tb_name [PARTITION part_spec]；

从表或者分区删除所有的行

 

 

二 更改表、分区和列

2.1ALTER TABLE

2.2.1重命名

ALTERTABLE t1 RENAME TO t2;

2.2.2更改表属性

ALTERTABLE t1 SET TBLPROPERTIES table_properties；

table_properties:

:(property_name = property_value, property_name = property_value, ... )

2.2.3更改表的注释

ALTERTABLE t1 SET TBLPROPERTIES('comment' = 'new comment')

2.2.4添加Serde 属性

ALTERTABLE t1 [PARTITION part_spec] SET SERDE serde_class [WITH SERDEPROPERTIESserde_properties];

ALTERTABLE t1 [PARTITION part_spec] SET SERDEPROPERTIES serde_properties;

serde_properties:

  : (property_name = property_value,property_name = property_value, ... )

2.2.5对表进行粪桶

ALTERTABLE table_name CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name,...)] INTO num_buckets BUCKETS;

2.2.6Alter Table Skewed

ALTERTABLE table_name SKEWED BY (col_name1, col_name2, ...) ON ([(col_name1_value,col_name2_value, ...) [, (col_name1_value, col_name2_value), ...][STORED ASDIRECTORIES];

 

2.2.7Alter Table Not Skewed

ALTERTABLE table_name NOT SKEWED;

 

2.2.8更改表或者分区的文件格式

ALTERTABLE table_name [PARTITION partition_spec] SET FILEFORMAT file_format;

 

ALTERTABLE emp SET FILEFORMAT ORC;

2.2.8更改表或者分区是否受保护

ALTERTABLE table_name [PARTITION partition_spec] ENABLE|DISABLE NO_DROP [CASCADE];

 

ALTERTABLE table_name [PARTITION partition_spec] ENABLE|DISABLE OFFLINE;

 

2.2.9更改表或者分区的连接

ALTERTABLE t1 [PARTITION part_spec] CONCATENATE;

如果表或者分区包含很小的RCFile或者ORCFile,这上面的命令可以将他们惊醒merge

ALTERTABLE table_name [PARTITION partition_spec] SET FILEFORMAT file_format;

 

2.2ALTER PARTITION (略)

 

2.3ALTER COLUMN

2.3.1改变列名、数据类型以及列的位置

ALITERTABLE t1 [PARTITION part_spec] CHANGE COLUMN old_column new_clomun column_type[COMMENT comment] [FIRST|AFTER column][CASCADE|RESTRICT]

CASCADE:级联操作，如果有视图或者约束，一并删除

RESTRICT:有严格限制，如果不存在视图或者约束才可以删除

 

ALTERTABLE c_order CHANGE COLUMN ofrom ocode INT COMMENT 'CHANGE COLUMN ofrom' AFTERage;

 

2.3.2添加或者替换列

ALTERTABLE table_name [PARTITION part_spec] ADD|REPLACE COLUMNS(column_namedata_type [COMMENT comment] ) [CASCADE|RESTRICT];

ALTERTABLE c_order ADD COLUMNS(revenue DECIMAL COMMENT 'Add Revenue Column')RESTRICT;

ALTERTABLE c_order REPLACE COLUMNS(revenues DECIMAL COMMENT 'Replace RevenueColumn') RESTRICT;

注意：替换列会把所有列替换掉

 

2.3.4根据部分分区该列明

ALTERTABLE foo PARTITION (ds='2008-04-08', hr=11) CHANGE COLUMN dec_column_namedec_column_name DECIMAL(38,18);

 

三 创建更改删除索引

3.1创建索引

CREATEINDEX index_name

  ON TABLE base_table_name (col_name, ...)

  AS index_type

  [WITH DEFERRED REBUILD]

  [IDXPROPERTIES (property_name=property_value,...)]

  [IN TABLE index_table_name]

  [

     [ ROW FORMAT ...] STORED AS ...

     | STORED BY ...

  ]

  [LOCATION hdfs_path]

  [TBLPROPERTIES (...)]

  [COMMENT "index comment"];

通过给定的列进行索引

3.2更改索引

ALTERINDEX index_name ON table_name [PARTITION part_spec] REBUILD；

3.3删除索引

DROPINDEX index_name [IF EXIST] ON table_name;

 

 

四 创建、删除和重新加载函数

4.1创建函数

创建临时函数：

CREATETEMPORARY FUNCTION func_name AS class_name;

只能在session范围内使用，如果session失效，则不能使用这个函数

首先需要把你写的jar 通过ADD JAR命令添加到classpath
创建永久函数：

addjar /opt/data/UDFStripDoubleQuotes.jar;

 

CREATEFUNCTION [db_name.]function_name AS class_name

[USINGJAR|FILE|ARCHIVE 'file_uri' [,JAR|FILE|ARCHIVE 'file_uri']];

说白了就是把jar包放到HDFS上，然后指定这个函数是哪一个数据库的，然后跟着你的HDFS jar路径

CREATEFUNCTION hadoop.lu_str AS 'com.hive.udf.LowerAnd

UpperUDF'USING JAR 'hdfs:/var/hive/udf/lowerOrUpper.jar';

 

4.2删除函数

DROPFUNCTION [IF EXISTS] function_name;

 

4.3重新加载函数

RELOADFUNCTION;

 

 

 

五 创建、删除、授予、撤销权限

5.1创建和删除角色

CREATEROLE role_name;

DROPROLE role_name;

CREATEROLE hive_admin;

 

5.2将角色分配给用户

GRANTROLE role_name [, role_name] ... TO principal_specification

[,principal_specification] ...[WITH ADMIN OPTION]

GRANTROLE hive_admin TO USER hadoop;

 

5.3授权

GRANTpriv_type ON object_specification TO principal_specification[,principal_specification] ... [WITH GRANT OPTION]

5.3.1给角色授权

GRANTALL ON DATABASE d TO ROLE hive_admin;

5.3.2给组授权

GRANTALL ON TABLE emp TO GROUP g;

5.3.3给用户授权

GRANTSELECT,UPDATE,ALTER,DTOP ON TABLE emp TO USER hadoop;

 

5.4回收权限

REVOKE[GRANT OPTION FOR] priv_type [ON object_specification]

FROMprincipal_specification;

5.4.1回收角色权限

REVOKEALL ON DATABASE d FROM ROLE hive_admin；

5.4.2回收组的权限

REVOKEALL ON TABLE emp FROM GROUP g；

5.4.3回收用户的权限

REVOKESELECT,UPDATE,ALTER,DTOP ON TABLE emp FROM

USERhadoop;

 

5.5查看权限

SHOWGRANT principal_specification [ON object_specification [(column_list)]]

SHOWGRANT USER hadoop ON TABLE emp;

权限名称         含义

ALL                所有权限

ALTER             允许修改元数据（modifymetadata data of object）—表信息数据

UPDATE          允许修改物理数据（modifyphysical data of object）—实际数据

CREATE          允许进行Create操作

DROP          允许进行DROP操作

INDEX             允许建索引（目前还没有实现）

LOCK          当出现并发的使用允许用户进行LOCK和UNLOCK操作

SELECT           允许用户进行SELECT操作

SHOW_DATABASE 允许用户查看可用的数据库

 

 

六 Show

6.1SHOW DATABASES/SCHEMAS LIKE

都会列出metastore里定义的数据库 LIKE允许过滤

SHOWDATABASES LIKE "*oo*";

将会查出所有包含oo的数据库

 

6.2Show Tables/Views/Partitions/Indexes

6.2.1显示表

SHOWTABLES [IN database_name] ['*字符*']; 这里没有Like哦

6.2.2显示视图

SHOWVIEWS [IN/FROM database_name] [LIKE 'pattern_with_

wildcards'];

6.2.3显示分区

SHOWPARTITIONS table_name;

6.2.4显示表属性

SHOWTBLPROPERTIES tblname;

SHOWTBLPROPERTIES tblname("foo");

6.2.5SHOW CREATE TABLE

SHOWCREATE TABLE [db_name.]table_name|view_name;

6.2.6查看索引

SHOW[FORMATTED] (INDEX|INDEXES) ON table_with_index [(FROM|IN) db_name];

6.2.7查看表的列

SHOWCOLUMNS (FROM|IN) table_name [(FROM|IN) db_name];

SHOWCOLUMNS FROM emp IN hadoop；

6.2.8查看函数

SHOWFUNCTIONS "a.*"; //查看以a开始的函数

 

七 Describe

7.1描述数据库 DESCRIBEDATABASE

DESCRIBEDATABASE hadoop;

7.2描述表DESCRIBE TABLE
DESCRIBE TABLE emp；

7.3描述列

DESCRIBEFORMATTED emp.hiredate;

7.4描述分区

DESCRIBE[FORMATTED] film PARTITION part-spec;

DESCRIBEFORMATTED film_second_level_part PARTITION(area='

America',release_year='old');