Hive实操

一.数据仓库和数据库
数仓和数据库的区别
数据库与数据仓库的区别实际讲的是OLTP与OLAP的区别

操作型处理(数据库)，叫联机事务处理OLTP（On-Line Transaction Processing），也可以称面向用户交易的处理系统，它是针对具体业务在数据库联机的日常操作，通常进行增删改查操作。用户较为关心操作的响应时间、数据的安全性、完整性和并发支持的用户数等问题。传统的数据库系统作为数据管理的主要手段，主要用于操作型处理。

分析型处理(数据仓库)，叫联机分析处理OLAP（On-Line Analytical Processing）,也可以称为面向专业分析人员进行数据分析,通常进行查询分析操作,一般针对某些主题的历史数据进行分析，支持管理决策。

数据仓库主要特征:面向主题的（Subject-Oriented）、集成的（Integrated）、非易失的（Non-Volatile）和时变的（Time-Variant）数据仓库，是在数据库已经大量存在的情况下，为了进一步挖掘数据资源、为了决策需要而产生的。

数据仓库的出现，并不是要取代数据库,主要区别如下:

数据库是面向事务的设计，数据仓库是面向主题设计的。
数据库一般存储业务数据，数据仓库存储的一般是历史数据。
数据库是为捕获数据而设计，数据仓库是为分析数据而设计数据库设计是尽量避免冗余，一般针对某一业务应用进行设计，比如一张简单的User表，记录用户名、密码等简单数据即可，符合业务应用，但是不符合分析。
数据仓库在设计是有意引入冗余，依照分析需求，分析维度、分析指标进行设计。
数仓的分层架构
数据仓库架构可以分为三层：

源数据层（ODS）：此层数据无任何更改，直接沿用外围系统数据结构和数据，不对外开放；为临时存储层，是接口数据的临时存储区域，为后一步的数据处理做准备。
数据仓库层（DW）：也称为细节层，DW层的数据应该是一致的、准确的、干净的数据，即对源系统数据进行了清洗（去除了杂质）后的数据。
数据应用层（DA或APP）：前端应用直接读取的数据源；根据报表、专题分析需求而计算生成的数据。
ELT和ELT
数据仓库从各数据源获取数据及在数据仓库内的数据转换和流动都可以认为是ETL（抽取Extract,转化Transform ,装载Load）的过程。但是在实际操作中将数据加载到仓库却产生了两种不同做法：ETL和ELT。

ETL:先从数据源池中抽取数据，数据保存在临时暂存数据库中（ODS）。然后执行转换操作，将数据结构化并转换为适合目标数据仓库系统的形式,然后将结构化数据加载到数据仓库中进行分析。
ELT:从数据源中抽取后立即加载。没有专门的临时数据库（ODS），这意味着数据会立即加载到单一的集中存储库中,数据在数据仓库系统中直接进行转换，然后进行分析
二.数据库操作
创建库的语法为 CREATE DATABASE [IF NOT EXISTS] db_name [LOCATION position];
删除库的语法为 DROP DATABASE db_name [CASCADE];
数据库和HDFS的关系
Hive的库在HDFS上就是一个以.db结尾的目录
默认存储在：/user/hive/warehouse内
可以通过LOCATION关键字在创建的时候指定存储目录
三.数据表操作
1.表操作语法和数据类型
创建数据库表语法

EXTERNAL，创建外部表
PARTITIONED BY，分区表
CLUSTERED BY，分桶表
STORED AS，存储格式
LOCATION，存储位置
数据类型

2.内部表和外部表
Hive中可以创建的表有：

内部表(MANAGED_TABLE)：分区表 分桶表
外部表(EXTERNAL_TABLE)：分区表 分桶表
在HDFS的表默认存储路径：

/user/hive/warehouse/库名.db
查看表类型和详情:
DESC FORMATTED表名;
内部表（CREATE TABLE table_name ...）

未被external关键字修饰的即是内部表,内部表又称管理表或者托管表。删除内部表:直接删除元数据和存储的业务数据 
外部表（CREATE EXTERNAL TABLE table_name ...）

被external关键字修饰的即是外部表,外部表又称非管理表或者非托管表。删除外部表:仅仅是删除元数据，存储的业务数据并不会被删除 。
内部表和外部表的对比

3.查看/修改表
查看建表语句的语法 show create table表名
内部表转外部表alter table stu set tblproperties('EXTERNAL'='TRUE')
外部表转内部表alter table stu set tblproperties('EXTERNAL'='FALSE')
4.映射表
Hive映射表的流程步骤是什么?创建表加载数据验证数据
Hive表指定字段间分隔符的语法是什么?答:row format delimited fields terminated by '分隔符'
Hive表中加载数据的语法是什么?答:load data [local] inpath '数据文件路径'into table表名
5.数据加载和导出
加载数据的语法

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE表名
INSERT INTO|OVERWRITE TABLE表名SELECT...
注意:使用LOAD语句：

数据来源本地，本地数据文件会保留，本质是本地文件上传
HDFS据来自HDFS，加载后文件不存在，本质是在HDFS上进行文件移动
导出数据的语法

INSERT OVERWRITE [local] directory‘path’[format delimited]SELECT...;
hive -f/-e执行语句或者脚本>文件名
6.分区表和分桶表
分区表概念以及使用场景

建表的使用partitioned by (分区字段名分区字段类型)关键字
分区其实就是HDFS上的不同文件夹
分区表可以极大的提高特定场景下Hive的操作性能
分区表的完整语法

create table tablename(...) partitioned by(分区列列类型,...) row format delimited fields terminated by'';
分区表的分区列，在partitioned by中定义，不在普通列中定义
分桶表的关键字

clusteredby(字段名) :指定按照哪个字段进行分桶
intoxbuckets :设置分桶数量为x个
复杂类型操作