hive库表常用命令及常用函数

hive对库表的常用命令

查看数据库 ：

 show database;

切换数据库:

 use database_name;

查看所有的表：

 show tables;

查询表结构:

 desc table_name;

创建数据库:

 create database database_name;

删除数据库

 drop database if exists database_name;
 drop database database_name;

创建表

 create table if not exists table_name(
     field1 type1,
     field2 type2,
     field3 type3,
     ……
 )comment "学生信息表"
 row format delimited
 fields terminated by "\t"

删除表

 drop table table_name

创建视图

create view view_name as
 select * from table_name where field1 > condition

创建外部表

create external table if not exists `db_name`.`table_name`(
         `col_name1` type comment "",
         `col_name2` type comment ""
 )
 ​
 partitioned by(data string)
 row format delimited fields terminated by ",";

建表语句标准化

create view if not exists `db_name`.`table_name`(
         `col_name1` type comment "",
         `col_name2` type comment ""
 )
 create table student(
     name string,
     age int
 );

查看表

show create table student

 CREATE TABLE `student`(
   `name` string, 
   `age` int)
 ROW FORMAT SERDE 
   'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
 STORED AS INPUTFORMAT 
   'org.apache.hadoop.mapred.TextInputFormat' 
 OUTPUTFORMAT 
   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
 LOCATION
   'hdfs://yunqidt:9000/yunqi/hive/warehouse/ods.db/student'
 TBLPROPERTIES (
   'COLUMN_STATS_ACCURATE'='true', 
   'numFiles'='1', 
   'numRows'='1', 
   'rawDataSize'='5', 
   'totalSize'='6', 
   'transient_lastDdlTime'='1591778652')

hive常用函数

1、over()开窗范围：rows是行数的意思，后面跟的是函数的范围，between是行数在什么范围，unbounded是行数的起点,这里可以将unbounded替换成1，那就是相邻上一个月的意思，preceding是前面的意思，current row是当前行的意思
2、cast(数据类型1 as 数据类型2)表示将数据类型1强转成数据类型2
3、decimal(10,2)是整数位长度为10，小数位长度为2，如果没有小数位，默认补0
4、row_number：添加序号，无论字段值是否相同
5、dense_rank()：基于over开窗函数的排序函数，如果值相同，则排序的序号相同，紧接的序号不跳过。举例123,1123,1223这样6、6、date_sub(日期，数值)，用日期-数值，即当前日期的前n天，返回值是日期字符串类型
7、ntile：把有序的数据集合平均分配到指定的数据量个桶中，将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。
8、split(字符串，分割符)：使用分割符切割字符串，返回一个数组
9、lateral view explode(数组)：将数组字段拆分成多行
10、concat_ws(连接符,字符串，字符串)：连接多个字符串
11、collect_list(分组键)：将分组中的某列聚合成一个数组，数组中元素与分组后的数据保持一致
12、map：将多个排列好的k,v,k,v...变成一个map结构，这是初始化map结构的方式，取数据是map[key]
13、row_number()：row_number是基于over()开窗函数的一个不重复的序号，如上结果所示，即便结果相同，也会顺延,序号自增
14、substring(字符串，起始位置，步长)：根据起始位置和步长切割字符串
15、nvl(value1，value2)：如果value1是null，则返回value2，如果不是，则返回value1
16、lag(字段，n，默认值)：基于over开窗函数，根据排序规则取当前行前第n个数，如果不指定n，则默认取前一个，如果取不到，返回默认值，如果不指定默认值，取不到则返回Null