Hive基础篇—02

Hive的数据模型

        分区

                根据某些条件，划分数据，将数据划分成一个一个的文件夹存储

                根据虚拟的字段划分

        分桶

                提高针对某些字段的查询效率

                根据某个字段进行hash计算然后%分桶的个数，确定这个数据在哪个桶里

                后续在查询的时候，就可以直接在对应的桶里检索，减少检索范围，提高查询效率

Decimal

        对精确度要求比较高的场景

                一般用于金额

                经纬度的使用

varchar 和 char的区别是什么

        varchar(32)可变，存储的数据长度可变

        char(32) 固定长度，存储的数据长度固定

         应用场景

                位数不确定的时候用varchar

                位数确定一般用char

        char性能更好

        char存储的时候是整齐的

        如果长度相等，char的空间利用率更高，因为varchar需要有一个额外字段存储长度的

truncate

        意思为截断表

        作用是清空表所有信息

                截断表之后重新填写数据，id从1开始

 字节数组

binary

字节数组类型，可以存储任意类型的数据用的很少

复杂(集合)数据类型

数据类型	描述	字面语法示例
STRUCT	和C语言中的struct或者”对象”类似，都可以通过”点”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, lastdt STRING}，那么第1个元素可以通过字段名.first来引用	struct( ‘John’, ‘Doe’)
MAP	MAP是一组键-值对元组集合，使用数组表示法(例如[‘key’])可以访问元素。例如，如果某个列的数据类型是MAP，其中键->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取值’Doe’	map(‘first’, ‘John’, ‘last’, ‘Doe’)
ARRAY	数组是一组具有相同类型的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第1个元素可以通过数组名[0]进行引用	ARRAY( ‘John’, ‘Doe’)

 HiveSQL 

 数据操作分类 

操作分类	具体操作	sql备注
DDL	•创建数据库 •建/删除表 •修改表结构 •创建/删除视图  •显示命令	Create/Drop/Alter Database Create/Drop/Truncate Table Alter Table/Partition/Column Create/Drop/Alter View Create/Drop Index Create/Drop Function Show functions; Describe function;
DML	•数据插入(insert,load)	load data...into table insert overwrite table
DQL	•数据查询(select)

DDL (data definition language)数据定义语言

元数据:描述数据的数据 

         表分类：主要分内表和外表

                内表：元数据和数据本身均被hive管理。删除表则全部删除。

                外表：元数据被hive管理，数据本身存储在hdfs,不受hive管理。删除表则只删除元数据，数据本身不变。

        建表语句模板

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

        建表语句示例 

create table user_info (
  id int,
  name string,
  classid int,
  classname string
)
partitioned by (come_date string )
row format delimited 
fields terminated by '\t'
lines terminated by '\n'
stored as textfile;

         关键词解释

                external: 创建内部表还是外部表，此为内外表的唯一区分关键字。

                comment col_comment: 给字段添加注释

                comment table_comment: 给表本身添加注释

                partitioned by: 按哪些字段分区，可以是一个，也可以是多个

                clustered by col_name... into num_buckets BUCKETS:按哪几个字段做hash后分桶存储

                row format:用于设定行、列、集合的分隔符等设置

                stored as : 用于指定存储的文件类型，如text,rcfile等

                location : 设定该表存储的hdfs目录，如果不手动设定，则采用hive默认的存储路径

        查看已存在表的详细信息

                show create table tablename;

                desc tablename/desc formatted tablename;

        显示所有表

                show tables;

        更改表名

                alert table student rename to student2;

        增加字段

                alert table student add columns(age int comment "新增加的字段");

        视图

                创建视图

                        create view student_view as select id,name from student;

                本身不存储实际数据，只存储表关系，使用时再去通过关系查找数据

                查看所有视图：show views;

                删除视图：drop view student_view;

DML（insert,update,delete）数据管理语言

        加载HDFS数据步骤

                创建一个文本文件存储的表，并以"\t"作为分隔符，方便构造和上传数据

                构造与表对应的输入文件

                将之前的本地文件上传至自己的hdfs目录中

                加载HDFS数据文件到表中

LOAD DATA INPATH '/tmp/tianliangedu/input_student_info/student.txt' 
OVERWRITE INTO TABLE student PARTITION (come_date=20170904);

                        注：原始的hdfs文件数据将被move到目标表的数据目录当中，注意源文件目录和目标文件目录的权限。 

                通过SELECT查看表中是否加载了数据

        将查询结果插入到数据表中

//代码模板
INSERT OVERWRITE TABLE tablename1 
[PARTITION (partcol1=val1, partcol2=val2 ...)] 
select_statement1 FROM from_statement;

//代码示例
insert overwrite table student partition(come_date='20170905')
select
id,username,classid,classname 
from student 
where come_date='20170904';

          多插入模式（一次查询多次插入）

//代码模板
FROM from_statement
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol=val)] select_statement1  
[INSERT OVERWRITE TABLE tablename2 [PARTITION ...] select_statement2 
...;

//代码示例
from student
insert overwrite table student partition(come_date='20170906') select id,username,classid,classname where come_date='20170905'
insert overwrite table student partition(come_date='20170907') select id,username,classid,classname where come_date='20170905'
insert overwrite table student partition(come_date='20170908') select id,username,classid,classname where come_date='20170905';

        动态分区模式（让分区成为被查询出来的结果表的字段名称变量）

//代码模板
INSERT OVERWRITE TABLE tablename 
PARTITION (col_name) select_statement FROM from_statement

                设置非严格模式

set hive.exec.dynamic.partition.mode=nonstric;

                示例

                        需求：有新表student_outer，即新来的外部学生表，有若干学生数据，与student表结构完全相同。

                                将student_outer表的多个分区数据，一次性插入到student表中。

                        数据准备

//	创建新表student_outer与之前的student结构完全一致
CREATE  TABLE student_outer(
id string comment '学号',
username string comment '姓名',
classid int comment '班级id',
classname string comment '班级名称'
)
comment '学生信息主表'
partitioned by (come_date string comment '按入学年份分区')
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS  textfile;

                        装载数据

LOAD DATA INPATH '/temp/input/student.txt' OVERWRITE INTO TABLE student_outer PARTITION (come_date=20171120);
LOAD DATA INPATH '/temp/input/student.txt' OVERWRITE INTO TABLE student_outer PARTITION (come_date=20171121);
LOAD DATA INPATH '/temp/input/student.txt' OVERWRITE INTO TABLE student_outer PARTITION (come_date=20171122);

                         查看分区列表

show partitions student_outer;

                         将student_outer表所有数据插入到student表

//先清空之前的student表，方便查看效果
truncate table student;
insert overwrite table student partition(come_date)  
select id,username,classid,classname,come_date from student_outer;

                        查看结果