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));

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页