Hive LanguageManual DDL

hive语法规则LanguageManual DDL

SQL DML 和 DDL 数据操作语言 (DML) 和 数据定义语言 (DDL)

一、数据库 增删改都在文档里说得也很明白，不重复造车轮

二、表

1.创建table重点解析如下

Create Table

eg1:基础创建方式
create table if not exists default.cenzhongman
(
ip string COMMENT 'this is ip',
name string
)
COMMENT 'this is log of cenzhongman.com'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
--------------------------------------------
eg2:常用于分表
create table if not exists default.cenzhongman_2
AS select ip,date from default.cenzhongman;
--------------------------------------------
eg3:常用于表复制
create table if not exists default.cenzhongman_3
like default.cenzhongman;

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 [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)
  ]

  #数据存储在hdfs文件系统位置
  [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 [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];
 
data_type
  : primitive_type
  | array_type
  | map_type
  | struct_type
  | union_type  -- (Note: Available in Hive 0.7.0 and later)
 
primitive_type
  : TINYINT
  | SMALLINT
  | INT
  | BIGINT
  | BOOLEAN
  | FLOAT
  | DOUBLE
  | DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)
  | STRING
  | BINARY      -- (Note: Available in Hive 0.8.0 and later)
  | TIMESTAMP   -- (Note: Available in Hive 0.8.0 and later)
  | DECIMAL     -- (Note: Available in Hive 0.11.0 and later)
  | DECIMAL(precision, scale)  -- (Note: Available in Hive 0.13.0 and later)
  | DATE        -- (Note: Available in Hive 0.12.0 and later)
  | VARCHAR     -- (Note: Available in Hive 0.12.0 and later)
  | CHAR        -- (Note: Available in Hive 0.13.0 and later)
 
array_type
  : ARRAY < data_type >
 
map_type
  : MAP < primitive_type, data_type >
 
struct_type
  : STRUCT < col_name : data_type [COMMENT col_comment], ...>
 
union_type
   : UNIONTYPE < data_type, data_type, ... >  -- (Note: Available in Hive 0.7.0 and later)
 
row_format
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char] 		#行分隔符和列分隔符
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char]   -- (Note: Available in Hive 0.13 and later)
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
 
file_format:
  : SEQUENCEFILE
  | TEXTFILE    -- (Default, depending on hive.default.fileformat configuration)
  | RCFILE      -- (Note: Available in Hive 0.6.0 and later)
  | ORC         -- (Note: Available in Hive 0.11.0 and later)
  | PARQUET     -- (Note: Available in Hive 0.13.0 and later)
  | AVRO        -- (Note: Available in Hive 0.14.0 and later)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
 
constraint_specification:
  : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE ]
    [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE

2.清除表的所有数据

TRUNCATE TABLE table_name [PARTITION partition_spec];
     
partition_spec:
  : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

三、Hive表的类型

管理表MANAGED_TABLE

表删除之后，表的数据同时删除

托管表(外部表)EXTERNAL_TABLE

一般通过LOCATION指定数据存储目录，以便共用
表删除之后，表的数据不会删除(hdfs中的数据)，只删除元数据（matestore）
直接把需要加载的文件放到表所在文件夹中，自动加载

分区表（此类型与上述类型非并列关系）

#创建分区表
create table emp_partition(ID int, name string, job string, mrg int, hiredate string, sal double, comm double, deptno int) partitioned by (mouth string);

#加载数据
load data local inpath '/opt/datas/xxx.txt' into table default.tableName partition (mouth = '201707' ,day = '14');

#查询数据
select * from emp_partition where mouth = '201707' and day = '14';

#在实现上，分区表在（load）加载数据时候，会往 matestore 的数据库中的 partition 表中添加一行用于说明分区情况
#在查询数据时，会读取 matestore 中的 partition 表中的信息
#若用户自行 put 数据到hdfs文件系统，matestore 中的数据不会添加分区信息，则查询数据为空，此时可以使用 msck 修复表，详情见DDL官方文档
msck repair table table_name;        #自动修复
alter table tableName add partition(day = '20170714');     #手动修复（更常用）

#显示分区
show partitions tablename;

4.查询语法

LanguageManualSelect

eg:全部查询
select * from tablename ;

eg2: t 是表的别名(为了方便书写，同时在存储和查看时显示)
select t.id,t.name,t.xxx from tablename t;

eg3:普通条件查询
select * from tablename t where id = '1234';
=  >=  <=  
is null  /  is not null  /  in   /  not in 

eg4:区间条件查询
select * from tablename t where t.money between 800 and 1500;

eg5:使用函数查询
select count(*) from tablename;
select max(*) from tablename;
select min(*) from tablename;
select sum(money) from tablename;
select avg(*) from tablename;
....

eg6:分组查询（**！不在函数中的字段必须在 group by 里面**）
select t.deptId,avg(money) avg_money(注：别名，可选) from tablename t group by t.deptId; 	#通过 deptId 分组,从表中查询每个部门平均工资
select t.job,t.deptId,avg(money) avg_money from tablename t group by t.deptId,t.job; 	#每个部门每个岗位的平均工资

eg7:having 
	where 针对单挑记录进行筛选
	having 针对分组结果进行筛选 > 先分组，对组进行条件判断
select deptid, avg(sal) avg_sal from tablename group by deptid having > 8000; 	#平均薪资大于 8000 的部门



SELECT [ALL(默认值) | DISTINCT(不重复的)] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]#分组
  [ORDER BY col_list]#显示顺序
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT [offset,] rows]#限制显示行数

join 链接查询：将 m n 两个数据库链接起来，组成一条记录

等值 join

select e.id, e.name, d.deptid, d.name from emp e join dept d on e.deptid = d.deptid; 	#显示e,d两个表 deptid 字段相同的信息在一个结果中

左链接 left join 以 join 左边的表为准（允许有的员工没有部门，左表存在该字段则打印）

select e.id, e.name, d.deptid, d.name from emp e left join dept d on e.deptid = d.deptid;

右链接 right join 以 join 右边的表为准（允许有的部门没有员工，右表存在该字段则打印）

select e.id, e.name, d.deptid, d.name from emp e right join dept d on e.deptid = d.deptid;

全连接 full join 左 + 右 = 全

    select e.id, e.name, d.deptid, d.name from emp e fuill join dept d on e.deptid = d.deptid;

Order, Sort, Cluster, and Distribute By

#order by ( ASC | DESC )全局数据 升序 | 降序 ，仅仅只有一个reduce
select * from tablename order by id desc;

#sort by 每一个reduce内部数据进行排序
set mapreduce.job.reduces = 3;
select * from tablename sort by id desc;    #直接显示结果，效果不明显
insert overwrite local directory '/opt/datas/sortby-res' select * from tablename sort by id decs;    #结果保存到本地文件系统中，分成三个结果文件存储

#Cluster by 当 distribute by 和 sort by 字段相同时相当于 cluster by 根据字段（按照一定规则）根据 reduce 数分组并排序
insert overwrite local directory '/opt/datas/sortby-res' select * from tablename cluster by id；

#distribute by 分布式，指定分区方式，按某个字段进行分区
insert overwrite local directory '/opt/datas/sortby-res' select * from tablename distribute by job sort by id decs;    #按岗位分区，内部按 ID 排序，结果保存到
#！！注：若reduce分区数 > 字段数     存在空数据   若 reduce 数 < 字段数，部分结果会合并

!!总结（重点）：

order by

全局排序，一个Reduce

sort by

每个Reduce中进行排序，全局不排序

distribute by

类似MapReduce 中的 partition 进行分区，结合 sort by 使用

cluster by

当distribute by 和 sort by 字段相同时使用，按照根据该字段进行分区，并排序

注：Hive 的虚拟属性

可以使用虚拟列属性协助 Hive 工作

    select id,name,INPUT__FILE__NAME from tablename;

即可显现 hive 文件所在文件