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 文件所在文件

巴特西

Hive LanguageManual DDL

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

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

二、表

1.创建table重点解析如下

Create Table

2.清除表的所有数据

三、Hive表的类型

管理表MANAGED_TABLE

托管表(外部表)EXTERNAL_TABLE

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

4.查询语法

LanguageManualSelect

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

等值 join

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

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

全连接 full join 左 + 右 = 全

Order, Sort, Cluster, and Distribute By

!!总结（重点）：

order by

sort by

distribute by

cluster by

注：Hive 的虚拟属性

最新文章

热门文章

巴特西

Hive LanguageManual DDL

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

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

二、表

1.创建table重点解析如下

Create Table

2.清除表的所有数据

三、Hive表的类型

管理表MANAGED_TABLE

托管表(外部表)EXTERNAL_TABLE

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

4.查询语法

LanguageManualSelect

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

等值 join

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

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

全连接 full join 左 + 右 = 全

Order, Sort, Cluster, and Distribute By

!!总结（重点）：

order by

sort by

distribute by

cluster by

注：Hive 的虚拟属性

最新文章

热门文章

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

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