HIVE命令及语法

命令帮助hive -H

1. hive -e ‘SQL’
   在命令行输入sql运行
2. 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…
3. hive -i
   定义UDF函数

hive语法

hive默认有一个default，对应的HDFS地址为：/user/hive/warehouse
如果我们要新建库的话，就需要使用到库相关语法

库相关语法

1. 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

2. drop
   drop database test;
   如果库中有表存在，不允许删除
   如果想要删除这个数据库：

   1. 把所有表全删了
   2. 使用cascade（级联）{这里想到hdfs里面创建或者删除级联文件需要加 -p}
      drop database if exists test cascade;(可以理解为级联删除)

3. use
   use test
   查看库的属性信息
   desc database [extended] test

   查看现在是哪一个库
   select current_database();
   这个也可以通过hive-site.xml设置
   hive.cil.print.current.db

   查看创建库的详细语句
   show create database test;

表

1. 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;
可以建表，也有数据，表结构也有

2. 查看表
   show tables
   查看其它库的表
   show tables in default;
   模糊查询表
   show tables like‘test04’;
   查看表的详细信息
   desc [format] test0330;
   查看表的建表语句
   show create table test0330;

3. LOAD/INSERT

   1. 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;

   2. Insert

      1. insert into table…values()造数据用的多，一般没人这样用
      2. insert overwrite table partition (dt) …select xxx，xxx，xxx from table_a where join group by

4. 修改表
   修改表明，字段名，增加字段，注释comment ‘xx类型（已废弃）’等
   alter table
   hive里删除一个字段和修改类型是不支持的
   如果硬要删除某个字段，那么就替换掉所有字段
   alter table_a replace colunms(新字段);一般不这么做，可以在字段后面加个注释不去用就行了,表明改字段已作废即可

5. 删除表
   drop table table_name;
   内部表删除后，表和数据文件均删除
   外部表删除后，表没了，location数据文件还在

6. 清空表
   truncate table table_name
   外部表不允许truncate，会报错

内部表（MANAGED）VS外部表（EXTERNAL）
有很多不同

1. 内部表删除后，表和数据文件均删除 ；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	以非本地表格式存储