hive常用命令

1.设置显示schema
hive
set hive.cli.print.header=true;

2.set mapred.reduce.tasks=100;
查看set mapred.reduce.tasks;

3.（1）启动hive
hive (bin/hive)
（2）查看数据库
show databases;
（3）打开默认数据库
3use default;
（4）显示default数据库中的表
3show tables;
（5）创建一张表
3create table student(id int, name string);
（6）显示数据库中有几张表
3show tables;
（7）查看表的结构
3desc student;
（8）向表中插入数据
3insert into student values(1000,“ss”);
（9）查询表中数据
3select * from student;
（10）退出hive
quit;

4.类型转换
a．隐式类型转换规则如下
（1）任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。
（2）所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。
（3）TINYINT、SMALLINT、INT都可以转换为FLOAT。
（4）BOOLEAN类型不可以转换为任何其它的类型。
b．可以使用CAST操作显示进行数据类型转换
例如CAST(‘1’ AS INT)将把字符串’1’ 转换成整数1；如果强制类型转换失败，如执行CAST(‘X’ AS INT)，表达式返回空值 NULL。

5．退出hive窗口：
exit;
quit;
在新版的hive中没区别了，在以前的版本是有的：
exit:先隐性提交数据，再退出；
quit:不提交数据，退出；

6．在hive cli命令窗口中如何查看hdfs文件系统
dfs -ls /;

7．在hive cli命令窗口中如何查看本地文件系统
! ls /opt/module/datas;

8.加载数据
load data local inpath ‘/root/data/test.txt’ into table test;

9.查看表结构、建表语句、建库语句
desc test;
show create table tablename;
show create table test;

create database ods_all_dt;

10.创建一个数据库，指定数据库在HDFS上存放的位置
create database if not exists db_hive2 location ‘/db_hive2.db’;

11.删除空数据库,如果删除的数据库不存在，最好采用 if exists判断数据库是否存在
drop database if exists hive_test;

如果数据库不为空，可以采用cascade命令，强制删除
drop database ods_all_dt cascade;

12.创建表
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], …)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], …)]
[CLUSTERED BY (col_name, col_name, …)
[SORTED BY (col_name [ASC|DESC], …)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]

字段解释说明
（1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。
（2）EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
（3）COMMENT：为表和列添加注释。
（4）PARTITIONED BY创建分区表
（5）CLUSTERED BY创建分桶表
（6）SORTED BY不常用
（7）ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, …)]
用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。
SerDe是Serialize/Deserilize的简称，目的是用于序列化和反序列化。
（8）STORED AS指定存储文件类型
常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）
如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
（9）LOCATION ：指定表在HDFS上的存储位置。
（10）LIKE允许用户复制现有的表结构，但是不复制数据。

create table is not exists test(
name string,
friends array,
children map<string, int>,
address struct<street:string, city:string>
)
row format delimited fields terminated by ‘,’
collection items terminated by ‘_’
map keys terminated by ‘:’
lines terminated by ‘\n’;

字段解释：
row format delimited fields terminated by ‘,’ – 列分隔符
collection items terminated by ‘_’ --MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ‘:’ – MAP中的key与value的分隔符
lines terminated by ‘\n’; – 行分隔符

建表demo
create table park_attribution(
uuid string,
parkId bigint,
parkName string,
addr string,
addrSize int,
timeStateId bigint)
row format delimited fields terminated by ‘\t’
stored as ORC;

13.管理表（内部表）与外部表的互相转换
（1）修改内部表student2为外部表
alter table student2 set tblproperties(‘EXTERNAL’=‘TRUE’);
（2）查询表的类型
hive (default)> desc formatted student2;
Table Type: EXTERNAL_TABLE
（3）修改外部表student2为内部表
alter table student2 set tblproperties(‘EXTERNAL’=‘FALSE’);
（4）查询表的类型
hive (default)> desc formatted student2;
Table Type: MANAGED_TABLE
注意：(‘EXTERNAL’=‘TRUE’)和(‘EXTERNAL’=‘FALSE’)为固定写法，区分大小写！

14.分区表基本操作
a．引入分区表（需要根据日期对日志进行管理）
/user/hive/warehouse/log_partition/20170702/20170702.log
b．创建分区表语法
create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (month string)
row format delimited fields terminated by ‘\t’;

c．加载数据到分区表中
load data [local] inpath ‘/opt/module/datas/student.txt’ overwrite | into table student [partition (partcol1=val1,…)];
（1）load data:表示加载数据
（2）local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表
（3）inpath:表示加载数据的路径
（4）overwrite:表示覆盖表中已有数据，否则表示追加
（5）into table:表示加载到哪张表
（6）student:表示具体的表
（7）partition:表示上传到指定分区

load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201709');

d．查询分区表中数据
单分区查询
select * from dept_partition where month=‘201709’;
多分区联合查询
select * from dept_partition where month=‘201709’
union
select * from dept_partition where month=‘201708’
union
select * from dept_partition where month=‘201707’;
e．增加分区
创建单个分区
alter table dept_partition add partition(month=‘201706’) ;
同时创建多个分区
alter table dept_partition add partition(month=‘201705’) partition(month=‘201704’);
6．删除分区
删除单个分区
alter table dept_partition drop partition (month=‘201704’);
同时删除多个分区
alter table dept_partition drop partition (month=‘201705’), partition (month=‘201706’);
7．查看分区表有多少分区
show partitions dept_partition;
8．查看分区表结构
desc formatted dept_partition;

15.重命名表
ALTER TABLE table_name RENAME TO new_table_name;

16.增加/修改/替换列信息
更新列
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
alter table dept_partition add columns(deptdesc string);

增加和替换列
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], …)
alter table dept_partition change column deptdesc desc int;
alter table dept_partition replace columns(deptno string, dname string, loc string);

注：ADD是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE则是表示替换表中所有字段。

16.导数据，mkdir /opt/module/datas/export
a.导到本地，进入hive窗口命令行
hive
dfs -get /user/hive/warehouse/student5/ /opt/module/datas/export/student5.txt;
b.导到本地，linux shell命令行导
hive -e ‘select * from default.student;’ > /opt/module/datas/export/student4.txt;
3.导到hdfs
hive
export table default.student5 to ‘/user/hive/warehouse/export/student5’;

17.hive执行hql脚本testddl.hql

use test2;
CREATE TABLE if not exists t_test(
id int,
name string,
num int,
sale double,
create_time timestamp)
row format delimited fields terminated by ‘\t’
stored as ORC;

#–drop table t_test;

hive -f testddl.hql

18.保留2位小数
cast(column_name as decimal(10,2))

19.查看当前库
select current_database();

20.presto连接hive时间转换、varchar 转 timestamp
select substr(cast(now() as varchar),1,19);
select * from table1 where creationtime>= (cast(‘2020-05-01 00:00:00’ as timestamp));