HIVE命令及语法
命令帮助hive -H
hive -e ‘SQL’
在命令行输入sql运行hive -f sqlfile
运行文件中的sql
就可以写像存储过程一样的一大堆的sql
例如在
xxx.sql文件内写一大堆sql
create table … temp01 select * from emp;
create table … temp02 select * from dept;
insert into dws_result
select * from temp01 letj join temp02…hive -i
定义UDF函数
hive语法
hive默认有一个default,对应的HDFS地址为:/user/hive/warehouse
如果我们要新建库的话,就需要使用到库相关语法
库相关语法
-
create
CREATE [REMOTE] (DATABASE|SCHEMA) [IF NOT EXISTS] database_name [COMMENT database_comment] [LOCATION hdfs_path] [MANAGEDLOCATION hdfs_path] [WITH DBPROPERTIES (property_name=property_value, ...)];
create database test;
不加location的,对应的hdfs地址为:/user/hive/warehouse/test.db
删除后重新创建 加上location
create database test location ‘/dwd/test’;
hdfs里文件就会放在/dwd/test -
drop
drop database test;
如果库中有表存在,不允许删除
如果想要删除这个数据库:- 把所有表全删了
- 使用cascade(级联){这里想到hdfs里面创建或者删除级联文件需要加 -p}
drop database if exists test cascade;
(可以理解为级联删除)
-
use
use test
查看库的属性信息
desc database [extended] test
查看现在是哪一个库
select current_database();
这个也可以通过hive-site.xml设置
hive.cil.print.current.db查看创建库的详细语句
show create database test;
表
- create
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name -- (Note: TEMPORARY available in Hive 0.14.0 and later)
[(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
[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]
[SKEWED BY (col_name, col_name, ...) -- (Note: Available in Hive 0.10.0 and later)]
ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
[STORED AS DIRECTORIES]
[
[ROW FORMAT row_format]
[STORED AS file_format]
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] -- (Note: Available in Hive 0.6.0 and later)
]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)] -- (Note: Available in Hive 0.6.0 and later)
[AS select_statement]; -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)
create table 创建一个指定名字的表,如果相同名字的表已经存在,抛异常;如果加上 if not exists,则会忽略异常
external(外部的):创建一个外部表,在建表的同事可以指定一个实际存数据的路径(location)
col_name data_type:列名以及对应的数据类型,多列用逗号分割
comment col_comment:列的表述(sal comment ‘薪水’)
comment table_comment:表的描述
例如
emp
comment ‘emp表,存放xxxx信息’
partition by:指定分区 hive中一个重要的概念
clustered by:对于每一个表或者分区,hive可以进一步进变为桶,hive采用对列值进行哈希,
然后除以桶的个数求余的方式决定该条件存放在哪个桶中。
row format:一般是row format DELIMITED FILEDS TERMINATED by ‘,’,指定表存储中列的分隔符 默认是
\001
,还可以自定义花里胡哨的其他分隔符
STORED AS:STORED AS SEQUENCEFILE|TEXTFILE|REFILE,
如果是纯文本格式,那就是STORE AS TEXTFILE,
如果数据需要压缩 STROE AS SEQUENCE
location:指定hive表在HDFS上的路径,如果是外部表的话,则需要直接指定一个路径,内部表不需要指定,默认在xml里设置的地方/user/hive/warehouse/*
AS select statement:通过select的sql语句的结果来建表
create table emp(
EMPNO int,
ENAME string,
JOB string,
MGR bigint,
HIREDATE string,
SAL bigint,
COMM bigint,DEPTNO bigint
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’;
create table test0403 like test0330;
可以建表,但是无数据,只能复制表结构
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] AS SQL;
create table test0403_b as select * from test0330;
可以建表,也有数据,表结构也有
-
查看表
show tables
查看其它库的表
show tables in default;
模糊查询表
show tables like‘test04’;
查看表的详细信息
desc [format] test0330;
查看表的建表语句
show create table test0330; -
LOAD/INSERT
-
LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE tablename
[PARTITION] (partcol1=val1,partcol2= val2)
[LOCAL] 代表本地文件系统,
INPATH 加载数据的路径
OVERWRITE 有则覆盖 无则追加
PARTITION 加载到哪个分区LOAD DATA LOCAL INPATH ‘/home/zhangyujie/data/emp.txt’ OVERWRITE INTO TABLE emp;
-
Insert
- insert into table…values()造数据用的多,一般没人这样用
- insert overwrite table partition (dt) …select xxx,xxx,xxx from table_a where join group by
-
-
修改表
修改表明,字段名,增加字段,注释comment ‘xx类型(已废弃)’等
alter table
hive里删除一个字段和修改类型是不支持的
如果硬要删除某个字段,那么就替换掉所有字段
alter table_a replace colunms(新字段);一般不这么做,可以在字段后面加个注释不去用就行了,表明改字段已作废即可 -
删除表
drop table table_name;
内部表删除后,表和数据文件均删除
外部表删除后,表没了,location数据文件还在 -
清空表
truncate table table_name
外部表不允许truncate,会报错
内部表(MANAGED)VS外部表(EXTERNAL)
有很多不同
- 内部表删除后,表和数据文件均删除 ;HDFS和MySQL的数据都被删除
外部表删除后,表没了,location数据文件还在 ;MySQL中的数据被删除了,但是HDFS文件还在
外部表不删除数据这个特性,方便数据共享,比如说原始的数据和日志等
内部表可以作为中间表和结果表
7 STORE AS的含义(官网)
我对官网上内容做简单翻译绘制本表
Storage Format | Description |
---|---|
STORED AS TEXTFILE | 存储为纯文本文件 |
STORED AS SEQUENCEFILE | 存储为压缩序列文件 |
STORED AS ORC | 存储为 ORC 文件格式。 支持 ACID 事务和基于成本的优化器 (CBO)。 存储列级元数据。 |
STORED AS PARQUET | 在 Hive 0.13.0 及更高版本中存储为 Parquet 列式存储格式的 Parquet 格式; 在 Hive 0.10、0.11 或 0.12 中使用 ROW FORMAT SERDE … STORED AS INPUTFORMAT … OUTPUTFORMAT 语法 …。 |
STORED AS AVRO | 在 Hive 0.14.0 及更高版本中存储为 Avro 格式 |
STORED AS RCFILE | 存储为记录列文件格式。 |
STORED AS JSONFILE | 在 Hive 4.0.0 及更高版本中存储为 Json 文件格式 |
STORED BY | 以非本地表格式存储 |