一、Hive安装

1、解压环境

#切换到指定文件夹
cd /opt/download
#解压
tar -zxvf apache-hive-3.1.2-bin.tar.gz -C /opt/software/
#重命名
mv /opt/softwareapache-hive-3.1.2-bin/ /opt/softwarehive312
#切换到hive文件夹中
cd /opt/software/hive312

2、环境变量配置

#编辑文件并输入配置信息
vim /etc/profile.d/my.sh
#-------------------------------------
# hive
export HIVE_HOME=/opt/software/hive312
export PATH=$PATH:$HIVE_HOME/bin
#-------------------------------------
#生效配置信息
source /etc/profile

3、配置文件信息

1.打开编辑文件

#文件重命名
mv conf/hive-default.xml.template  conf/hive-default.xml
#创建并编辑hive-site.xml信息
vim conf/hive-site.xml

2.输入以下内容

#-----------------------------------------
<configuration>
    <!--hdfs仓库路径-->
    <property>
        <name>hive.metastore.warehouse.dir</name>
        <value>/hive312/warehouse</value>
    </property>
    <!--metastore(元)数据库类型-->
    <property>
        <name>hive.metastore.db.type</name>
        <value>mysql</value>
        <description>Expects one of [derby, oracle, mysql, mssql, postgres].</description>
    </property>
    <!--连接mysql字符串-->
    <property>
        <name>javax.jdo.option.ConnectionURL</name>mysql
        <value>jdbc:mysql://192.168.71.128:3306/hive312?createDatabaseIfNotExist=true</value>
    </property>
    <!--mysql连接驱动-->
    <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.jdbc.Driver</value>
    </property>
    <!--mysql连接账号-->
    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>root</value>
    </property>
    <!--mysql本地连接密码-->
    <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>kb16</value>
    </property>
    <!--关闭schema验证-->
    <property>
        <name>hive.metastore.schema.verification</name>
        <value>false</value>
    </property>
    <!--提示当前库名-->
    <property>
        <name>hive.cli.print.current.db</name>
        <value>true</value>
        <description>Whether to include the current database in the Hive prompt.</description>
    </property>
    <!--查询输出显示列名-->
    <property>
        <name>hive.cli.print.header</name>
        <value>true</value>
        <description>Whether to print the names of the columns in query output.</description>
    </property>
    <!--server2对外开放的端口号-->
    <property>
        <name>hive.server2.thrift.port</name>
        <value>10000</value>
    </property>
    <property>
        <name>hive.server2.thrift.bind.host</name>
        <value>localhost</value>
    </property>
</configuration>
#-----------------------------------------

4、拷贝mysql驱动

#将JDBC的JAR包拷贝到hive的lib文件夹中，这样hive就可以操作mysql数据库
cp /opt/download/mysql-connector-java-5.1.47.jar lib/

5、更新guava包和hadoop一致

#列出hive的guava的jar包
ls lib/|grep guava
#--------------
# guava-19.0.jar
#---------------
#删除当前hive的guava包
rm -f lib/guava-19.0.jar
#查看hadoop使用guava包的版本
find /opt/software/hadoop313/ -name guava*
#----------------------------------------------------------------
/opt/software/hadoop313/share/hadoop/common/lib/guava-27.0-jre.jar
/opt/software/hadoop313/share/hadoop/hdfs/lib/guava-27.0-jre.jar
#----------------------------------------------------------------
#将hadoop的guava包拷贝到本地中
cp /opt/software/hadoop313/share/hadoop/hdfs/lib/guava-27.0-jre.jar lib/

6、mysql授权

#mysql数据库对外授权
grant all on *.* to root@master01 identified by 'kb16';
#刷新权限
flush privileges;

7、初始化

#hive初始化，并在mysql中建立一个数据库，该数据库用于存储元数据
schematool -dbType mysql -initSchema

8、hive启动模式

#首先启动元数据服务
nohup hive --service metastore 1>/dev/null 2>&1 &
#1、方法一 hive客户端
hive
#2、方法二 基于metastore和hiveserver2的beeline
#启动hiveserver2服务
nohup hive --service hiveserver2 1>/dev/null 2>&1 &
#登录的时候，一定重启MySQL服务
beeline -u jdbc:hive2://localhost:10000 -n uername
 
beeline -u jdbc:hive2://master01:10000 -n root
beeline -u jdbc:hive2://single01:10000 -n root
!connect jdbc:hive2://localhost:10000/default

9、Hadoop的core-site.xml配置

<!---hadoop对外开发的用户和用户组-->
<property>
	<name>hadoop.proxyuser.root.hosts</name>
	<value>*</value>
</property>
<property>
	<name>hadoop.proxyuser.root.groups</name>
	<value>*</value>
</property>

二、Hive

Hive是一套根据客户需求，集合各种大数据组件工具，对客户数据进行管理、处理，治理方案

1、Hive的文件结构

1、bin： 主要存放hive运行的可执行文件

2、lib： 主要存放hive运行的jar包

wget --no-check-certificate --no-cookies --header "Cookies: oraclelicense=accept-securebackup-cookies" https://archive.apache.org/dist/hadoop/core/hadoop-3.1.2/hadoop-3.1.2.tar.gz

2、MySQL上Hive的元数据

show tables;
+-------------------------------+
| Tables_in_hive312             |
+-------------------------------+
| AUX_TABLE                     |
| BUCKETING_COLS                |
| CDS                           |
| COLUMNS_V2                    |
| COMPACTION_QUEUE              |
| COMPLETED_COMPACTIONS         |
| COMPLETED_TXN_COMPONENTS      |
| CTLGS                         |
| DATABASE_PARAMS               |
| DBS                           |
| DB_PRIVS                      |
| DELEGATION_TOKENS             |
| FUNCS                         |
| FUNC_RU                       |
| GLOBAL_PRIVS                  |
| HIVE_LOCKS                    |
| IDXS                          |
| INDEX_PARAMS                  |
| I_SCHEMA                      |
| KEY_CONSTRAINTS               |
| MASTER_KEYS                   |
| MATERIALIZATION_REBUILD_LOCKS |
| METASTORE_DB_PROPERTIES       |
| MIN_HISTORY_LEVEL             |
| MV_CREATION_METADATA          |
| MV_TABLES_USED                |
| NEXT_COMPACTION_QUEUE_ID      |
| NEXT_LOCK_ID                  |
| NEXT_TXN_ID                   |
| NEXT_WRITE_ID                 |
| NOTIFICATION_LOG              |
| NOTIFICATION_SEQUENCE         |
| NUCLEUS_TABLES                |
| PARTITIONS                    |
| PARTITION_EVENTS              |
| PARTITION_KEYS                |
| PARTITION_KEY_VALS            |
| PARTITION_PARAMS              |
| PART_COL_PRIVS                |
| PART_COL_STATS                |
| PART_PRIVS                    |
| REPL_TXN_MAP                  |
| ROLES                         |
| ROLE_MAP                      |
| RUNTIME_STATS                 |
| SCHEMA_VERSION                |
| SDS                           |
| SD_PARAMS                     |
| SEQUENCE_TABLE                |
| SERDES                        |
| SERDE_PARAMS                  |
| SKEWED_COL_NAMES              |
| SKEWED_COL_VALUE_LOC_MAP      |
| SKEWED_STRING_LIST            |
| SKEWED_STRING_LIST_VALUES     |
| SKEWED_VALUES                 |
| SORT_COLS                     |
| TABLE_PARAMS                  |
| TAB_COL_STATS                 |
| TBLS                          |
| TBL_COL_PRIVS                 |
| TBL_PRIVS                     |
| TXNS                          |
| TXN_COMPONENTS                |
| TXN_TO_WRITE_ID               |
| TYPES                         |
| TYPE_FIELDS                   |
| VERSION                       |
| WM_MAPPING                    |
| WM_POOL                       |
| WM_POOL_TO_TRIGGER            |
| WM_RESOURCEPLAN               |
| WM_TRIGGER                    |
| WRITE_SET                     |
+-------------------------------+

3、hadoop文件授权

#修改hdfs的hive文件的所有者和用户组
hdfs dfs -chown -R root:supergroup /hive312
#修改缓存文夹件的权限
hdfs dfs -chmod -R 777 /tmp

4、Hive的驱动器：Driver

（1）解析器（SQL Parser）：将SQL字符串转换成抽象语法树AST，这一步一般都用第三方工具库完 成，比如antlr；对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。 （2）编译器（Physical Plan）：将AST编译生成逻辑执行计划。 （3）优化器（Query Optimizer）：对逻辑执行计划进行优化。 （4）执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是MR/Spark。

5、抽象语法树（AST）

• Antlr定义SQL的语法规则，完成SQL词法，语法解析，将SQL转化为抽象语法树AST Tree
• 遍历AST Tree，抽象出查询的基本组成单元QueryBlock
• 遍历QueryBlock，翻译为执行操作树OperatorTree
• 逻辑层优化器进行OperatorTree变换，合并不必要的ReduceSinkOperator，减少shuffle数据量
• 遍历OperatorTree，翻译为MapReduce任务
• 物理层优化器进行MapReduce任务的变换，生成最终的执行计划

6、动态分区

#查看hive的动态分区的状态
set hive.exec.dynamic.partition;
#修改hive的动态分区状态
set hive.exec.dynamic.partition.mode=nonstrict;
#向分区表中插入数据
insert overwrite into table kb16.user_movie_rating_par partition(dt) select userid,moviedid,rating,`timestamp`,data_format(from_unixtime(`timestamp`),'yyyy-MM') dt from user_moive_rating; order by `timestamp` desc limit 10;

三、Hive的数据类型

1、Hive基本数据类型

2、非常规数据类型

1.数组类型

#Hive数组类型定义，data_type为上面的基本数据类型
ARRAY<data_type>
#数组类型定义
stuhobby array<string>

2.图类型

#图类型定义,primitivez_type代表键类型，data_type为值类型
MAP<primitivez_type,data_type>
#定义图类型字段
stuscore map<string,int>

3.结构体类型

#定义自定义结构体字段
STRUCT<col_name:data_type[COMMENT col_comment],...>
#定义自定义的字段
stuaddress struct<prov***ring,city:string,district:string>)

4.集合类型

UNIONTYPE<data_type,data_type,...>

四、Hive的DDL

1、 数据库的基本操作

1.创建数据库

CREATE DATABASE [IF NOT EXISTS] database_name 
#关于数据块的描述
[COMMENT database_comment]
#指定数据库在HDFS上存储位置
[LOCATION hdfs_path] 
#指定数据块属性
[WITH DBPROPERTIES (property_name=value, ...)];

2.显示数据库信息

#显示数据库的详细信息
desc database db_hive;

3.显示数据库详细信息

#描述数据库详细信息
desc database extended db_hive;

4.切换数据库

#切换数据库
use db_hive;

5.修改数据库

#修改数据库属性
alter database db_hive set dbproperties('字段'='value');

6.删除数据库

#if exits判断是否存在，cascade可以强制删除
drop database[ if exits] db_hive[cascade];

2、创建表

1.建表语句

#EXTERNAL表示创建外部表，TEMPORARY表示创建内部表，创建时建议库名.表名
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name  
  [(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按照什么字段进行分桶，SORTED BY按照什么字段进行排序
  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [SKEWED BY (col_name, col_name, ...)                  -
     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)
 

 
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)
  | JSONFILE    -- (Note: Available in Hive 4.0.0 and later)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
 
column_constraint_specification:
  : [ PRIMARY KEY|UNIQUE|NOT NULL|DEFAULT [default_value]|CHECK  [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]
 
default_value:
  : [ LITERAL|CURRENT_USER()|CURRENT_DATE()|CURRENT_TIMESTAMP()|NULL ] 
 
constraint_specification:
  : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE 
    [, CONSTRAINT constraint_name UNIQUE (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, CONSTRAINT constraint_name CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]

1.建表语句

create [temporary][external] table [if not exits] [dbname.]tabname
[(
	colname data_type [comment col_comment],
    ...,
    [constraint_specification]
)]
[comment table_comment]
#创建分区表
[partitioned by (extrenal_colname data_type [comment col_comment],...)]
#创建分桶表
[clustered by (colname,...)[sorted by (colname ASC|DESC,...)] into num_buckets buckets]
[skewed by (colname,colname,...) on (colvalue,...),(colvalue,...),...][stored as directories]
[rowformat row_format]
[stored as file_format| stored by 'storge.handler.classname'[with serdeproperrties]]
[LOCATION hdfs_path]
[TBL PROPERTIES (property_name=value, ...)]
[AS select_statement]

1. **temporary：**建立内部表，**external：**建立外部表
2. if not exits： 判断表名是否已经存在，如果存在就抛出异常
3. comment: 为表和列添加注释
4. partitioned by： 创建分区表
5. clustered by： 创建分桶表
6. sorted by： 对桶中的一个或者多个列进行排序
7. rowformat：
8. stored as： 指定文件存储类型。常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、 TEXTFILE（文本）、 RCFILE（列式存储文件）
9. location： 指定表在HDFS上存储位置
10. AS： 根据查询结果创建表
11. like： 允许用户复制表结构，数据不能复制

2.创建简单表

#创建简单表
create table kb16.student(stuname string,stuage int,stugender string);
#插入数据
insert into kb16.student(stuname,stuage,stugender)values('tom',18,'f'),('jack',16,'m'),('jhon',18,'f');

3.创建外部表

#创建外部表
create external table kb16.student_ext(
stuname string,
stuage int,
stugender string,
#定义数组字段
stuhobby array<string>,
#定义图字段
stuscore map<string,int>,
#定义结构体字段
stuaddress struct<prov***ring,city:string,district:string>
)
row format delimited
#字段用,进行分割
fields terminated by ','
#数组用|进行分割
collection items terminated by '|'
#图
map keys terminated by ':'
#每行按照换行进行分割
lines terminated by '\n'
#按照普通文本进行存储
stored as textfile
#指定文件存储路径
location '/test/hive/student_ext';

4.创建含有特殊数据结构的内部表

select
stuname,stuage,stugender,
stuhobby[0] hobby1,stuhobby[1] hobby2,stuhobby[2] hobby3,
stuscore['java'] javascore,stuscore['mysql'] mysqlscore,
stuaddress.province prov***uaddress.city city,stuaddress.district district
from kb16.student_ext;
where array_contains(student,'eat')
#数据------------------------------------------------
henry,10,f,sing|dance|read,java:88|mysql:67,ah|hf|fx
pola,16,m,sing|eat|read,java:76|mysql:85,ah|la|sc
ariel,8,m,caton|pizzle|read,java:90|mysql:80,ah|hf|fx
#----------------------------------------------------

5.从已有表复制新表

#将一个表的查询结果创建为一个新表
create table kb16.student_cpoy 
as
select * from kb16.student_ext;

6.复制表结构

#复制现有表结构
create table kb16,student_like like kb16.student_ext;

7.指定路径创建数据表

#用一个挂载在HDFS上文件创建新的数据表7
create external table kb16.user_movie_rating(
userid bigint,
movieid bigint,
rating decimal(2,1),
`timestamp` bigint
)
row format delimited
fields terminated by ','
location '/test/kb16/hive/moive_rating/'
#跳过文件第一行
tblproperties("skip.header.line,count"="1");

8.创建分区表

#创建分区表，向分区表插入数据时，需要增加分区字段
create external table kb16.user_movie_rating_par(
userid bigint,
movieid bigint,
rating decimal(2,1),
`timestamp` bigint
)
partitioned by (dt string)
row format delimited
fields terminated by ',';

9.创建分桶表

#创建分区分桶表
create external table user_movie_ratin_par_bucket(
userid bigint,
movieid bigint,
rating decimal(2,1),
`timestamp` bigint,
)
partition by (years int)
clustered by (`timestamp`) sorted by (`timestamp` ASC) into 5 buckets
row format delimited fields terminated by ',';

3、查看表

1.查看数据表

#查看当前数据库中有哪些数据表
show tables;
#查看非当前数据的数据表
show tables in HIVE_DATABASE;
#查看数据库以xxx开头的表
show tables like 'xxx*';

2.查看数据表的详细信息

#查看表信息
desc table 表名;
#查看表信息
desc extened 表名;
#查看表的详细信息
desc formatted 表名;

3.查看表分区

#查看分区信息
show partitions 表名;

4.查看详细建表语句

#查看详细建表语句
show create table 表名;

4、修改表

1.重命名表

#语法结构
alter table table_name rename to new_table_name;
#例程
alter table student rename to stu;

2.修改字段定义

#增加一个字段
alter table 表名 add columns (字段名  字段类型);
#修改一个字段的定义
alter table 表名 change name 字段名 字段类型;
#替换所有字段
alter table 表名 replace columns (字段1 字段类型1,字段2 字段类型2 ...)

3.修改分区信息

#添加分区
alter table 表名 add partition(字段="值");
#添加多个分区
alter table 表名 add partition(分区字段="值1") partition(分区字段="值2");
#动态分区
load data local inpath "文件的HDFS路径" into table 表名 partition(字段="值");

4.修改分区

#添加分区的时候，直接指定当前分区的数据存储目录
alter table 表名 add if not exists partition(字段="值") location 'HDFS地址' partition(字段="值") location 'HDFS地址';
#修改已经指定好的分区的数据存储目录
alter table student_ptn partition (city='beijing') set location '/student_ptn_beijing';

5.删除表分区

#删除表分区
alter table 表名 drop partition (字段="值");

6.更新列

#语法结构
alter table table_name change [column] col_old_name col_new_name
column_type [comment col_comment] [first|after column_name]

7.增加和替换列

#语法结构
alter table table_name add|replace columns(col_name data_type[comment col_comment],...)

1. ADD是代表新增一字段 ，字段位置在所有列后面 (partition列前 )
2. REPLACE则是表示替换中所有字段。

5、删除表

1.删除表

#语法结构
drop table table_name;
#例程
drop table stu;

2.清空表

#清空表
truncate table 表名;

五、Hive的DML

1、数据导入方式

数据导入方式可以分为以下几种：

1. 通过hive，使用insert方式插入
2. 使用HDFS的put命令，将文件直接写入到hive指定表文件夹下的方式
3. 使用hive提供的load命令，将数据导入

2、数据导入

1.从本地磁盘或者HDFS导入数据

#从本地磁盘或者HDFS导入数据
load data [local] inpath '/opt/module/datas/student.txt' [overwrite] into table student [partition (partcol1=val1,…)];
#例程
load data local inpath '/opt/module/datas/student.txt' overwrite into table student;

2.将数据挂载在数据表中

#将数据挂载在数据表中
load data inpath '/xxx/student.txt' overwrite into table student;

3.通过insert插入

#通过insert指令将数据挂载
insert into table student select id, name from stu_par where class="01";

4.建表时候用select as将数据挂载

#在创建表的时候将数据挂载，该挂载方式有局限性，不建议使用
#将select查询的结果创建一个数据表
create TAB_NAME as select xxx;

5.建表的时候用load进行数据挂载

#建表的时候用load进行数据挂载
#数据在数据表创建前已经上传至HDFS上
create external table student2
(id int, name string)
row format delimited 
fields terminated by '\t'
location '/xxx';

6.向分桶表插入数据

insert overwrite table user_movie_ratin_par_bucket partition(years)
select *,pmod(cast(date_format(from_unixtime(U.`timestamp`),'yyyy') as int),5) years
from user_movie_rating limit 10;

2、数据导出

1.使用insert将数据导出

#使用insert将数据导出
#该方法将数据表的查询结果导出为文件
insert overwrite local directory '/opt/module/datas/export/student'
select * from student;

2.带格式导出

#将hive表的数据进行格式化导出
insert overwrite local directory '/opt/module/datas/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;

3.bash命令导出

#bash命令导出
#利用hive -e执行hive的查询语句，利用重定向方式将数据导出
hive -e "select * from student" >/root/student.txt

4.将数据表导出到HDFS

#整张表export到HDFS
export table student to '/export/student';

5.从导出结果导入到Hive

#从导出结果导入到Hive
import table student3 from '/export/student';

3、数据删除

#只删表数据，不删表本身
truncate table student;
#彻底删除数据
drop table student;

六、Hive的DQL

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY order_condition]
[DISTRIBUTE BY distribute_condition [SORT BY sort_condition] ]
[LIMIT number]

1、基本查询语句

1.全表查询

#全表查询
select * from emp;

2.查询某些列

#查询某些列
select empno, ename from emp;

3.给查询的特征列起别名

#起别名
select ename as name from emp;
#as可以省略
select ename name from emp;

4.查询进行运算

#运算符
select ename, sal + 10 from emp;

5.函数

#UDF函数
select substring(ename, 1, 1) from emp;
#UDAF函数
select count(*) from emp;

2、条件过滤

1.数据量限制

#limit，取前几行
select * from emp limit 5;

2.where条件筛选

#查询工资大于1000的人
select * from emp where sal > 1000;

3.like字段查询

#通配符字符串匹配 % _ ，以A开头的员工
select * from emp where ename like "A%";
/*正则入门
一般字符匹配自己
^ 匹配一行开头 ^R 以R开头
$ 匹配一行结束 R$ 以R结尾
. 匹配任意字符 ^.$ 一行只有一个字符
* 前一个子式匹配零次或多次
[] 匹配一个范围内的任意字符
\ 转义
*/

4.与或非判断

#与(and)或非(or)
select * from emp where empno = 30 and sal > 1000;

3、分组

1.group by 分组

#计算emp表每个部门的平均工资
select deptno, avg(sal) aa from emp group by deptno;

2.分组过滤

#分组过滤
#计算部门平均工资大于2000的部门
select deptno, avg(sal) aa from emp group by deptno having aa>2000;

4、连接

1.内关联（JOIN）

只返回能关联上的结果。

#内连接
SELECT column1,column2,...
FROM table_a a
join table_b b
ON (a.column = b.column);

2.左外关联（LEFT [OUTER] JOIN）

以LEFT [OUTER] JOIN关键字前面的表作为主表，和其他表进行关联，返回记录和主表的记录数一致，关联不上的字段置为NULL。

#左外关联
SELECT column1,column2,...
FROM table_a a
left join table_b b
ON (a.column = b.column);

3.右外关联（RIGHT [OUTER] JOIN）

和左外关联相反，以RIGTH [OUTER] JOIN关键词后面的表作为主表，和前面的表做关联，返回记录数和主表一致，关联不上的字段为NULL。

#右外关联
SELECT column1,column2,...
FROM table_a a
right join table_b b
ON (a.column = b.column);

4.全外关联（FULL [OUTER] JOIN）

以两个表的记录为基准，返回两个表的记录去重之和，关联不上的字段为NULL。注意：FULL JOIN时候，Hive不会使用MapJoin来优化。

#右外关联
SELECT column1,column2,...
FROM table_a a
full outer join table_b b
ON (a.column = b.column);

5.LEFT SEMI JOIN

以LEFT SEMI JOIN关键字前面的表为主表，返回主表的KEY也在副表中的记录。

#LEFT SEMI JOIN
SELECT column1,column2,...
FROM table_a a
left semi join table_b b
ON (a.column = b.column);

6.笛卡尔积关联（CROSS JOIN）

返回两个表的笛卡尔积结果，不需要指定关联键。

#笛卡尔积关联
SELECT column1,column2,...
FROM table_a a
cross join table_b b;

5、排序

1. asc: 为升序排序
2. desc: 为降序排序

1.单字段排序

#单字段降序排序
SELECT column1,column2,...
FROM table_a
order by column_od desc;

2.多字段排序

#单字段降序排序
SELECT column1,column2,...
FROM table_a
order by column_od1 asc,column_od2 desc;

3.局部排序

#Hive局部排序
SELECT column1,column2,...
FROM table_a
sort by column_od desc;

4.指定局部排序的分区字段

#指定局部排序的分区字段
select column1,column2,... 
from table_a
distribute by column_
sort by column_od desc;

5.cluster

#如果分区和排序的字段一样，我们可以用cluster by代替
select * from emp distribute by empno sort by empno;
select * from emp cluster by empno;

6、分桶

1.创建分桶表

#创建分桶表
create table stu_buck(id int, name string)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';

2.插入数据

#向分桶表中插入数据
load data local inpath '/opt/module/datas/student.txt' into table stu_buck;

七、hive函数

1、日期函数

2、数学函数

3、字符函数

4、聚合函数

5、集合函数

6、条件函数

7、表生成函数

8、类型转换函数

9、窗口函数

横向扩展表，控制粒度

order by 进行全局检索

#窗口函数
func over(partition by field1,...)
#粒度全表
over()
#所有分区数据
over(partition by field1,...)
#从当前分区的首行到当前行
over(partition by field1,... order by field_a,... rows between ... and ...)

sort by 进行局部排序，只保证部分有序，效率高

func over(distribute by field1,... sort by fielda,...rows between ... and ...)

1. 当前行：current row
2. 当前行的前多少行： preceding
3. 当前行的后多少行：following
4. 无边界：unbounded

unbounded preceding unbound follwing

unbounded preceding ... current row

1.row_number() over()

这个方法主要进行开窗增加自增列

2.sum() over()

通过开窗进行sum计算

3.lag/lead() over()

4.hive的自增列

insert into table User_Attribute select (row_number() over())+1000 as id,customid from tbl_custom;

八、with语法

with...as...需要定义一个SQLK片段，会将这个片段产生的结果集保存在内存中，后续的SQL均可以访问这个结果集和，作用与视图或临时表类似。一个SQL查询语句中只允许出现一个with语句，该语法主要用于子查询。

with t1 as (
		select *
		from user_info
	), 
	t2 as (
		select *
		from goods_list
	)
select *
from t1, t2;

九、多维分组聚合

多维分组聚合函数有：grouping sets、roll up、with cube

1、grouping sets函数

GROUPING SETS子句允许开发者自行组合GROUP BY子句中出现的字段作为分组字段，其实现效果等同于按照不同字段分组的SQL语句进行UNION操作。

SELECT    a.product_id
        , a.channel_id
        , a.promotion_id
        , SUM(a.sale_amount) AS sale_amount
FROM    dwd.dwd_sales a
GROUP BY a.product_id
        , a.channel_id
        , a.promotion_id
GROUPING SETS ((a.product_id, a.channel_id),(a.channel_id, a.promotion_id));

1.单字段

#grouping sets语句
select 
	device_id,
	os_id,app_id,
	count(user_id) 
from test_xinyan_reg 
group by device_id,os_id,app_id #全字段group by
grouping sets((device_id));
#等价hive语句
SELECT 
	device_id,
	null,
	null,
	count(user_id) 
FROM test_xinyan_reg 
group by device_id;

2.全字段排序，按照某个字段聚合

#grouping sets语句
select 
	device_id,
	os_id,app_id,
	count(user_id) from test_xinyan_reg 
group by 
	device_id,
	os_id,
	app_id 
grouping sets((device_id,os_id))
#等价的hive语句
SELECT 
	device_id,
	os_id,null,
	count(user_id) 
FROM test_xinyan_reg 
group by device_id,os_id;

3.多维度聚合

#grouping sets语句
select 
	device_id,
	os_id,app_id,
	count(user_id) 
from test_xinyan_reg 
group by 
	device_id,
	os_id,
	app_id 
grouping sets((device_id,os_id),(device_id));
#等价的hive语句
SELECT 
	device_id,
	os_id,
	null,
	count(user_id) 
FROM test_xinyan_reg 
group by device_id,os_id 
UNION ALL 
SELECT 
	device_id,
	null,
	null,
	count(user_id) 
FROM test_xinyan_reg 
group by device_id;

4.全维度聚合

#grouping sets语句
select
	device_id,
	os_id,app_id,
	count(user_id) 
from test_xinyan_reg 
group by
	device_id,
	os_id,
	app_id 
grouping sets((device_id),(os_id),(device_id,os_id),());
#等价的hive语句
SELECT 
	device_id,
	null,
	null,
	count(user_id) 
FROM test_xinyan_reg 
group by device_id 
UNION ALL 
SELECT 
	null,
	os_id,
	null,
	count(user_id) 
FROM test_xinyan_reg 
group by 
	os_id 
UNION ALL 
SELECT 
	device_id,
	os_id,
	null,
	count(user_id)
FROM test_xinyan_reg 
group by
	device_id,
	os_id 
UNION ALL 
SELECT 
	null,
	null,
	null,
	count(user_id) 
FROM test_xinyan_reg

2、roll up函数

rollup可以实现从右到做递减多级的统计，显示统计某一层次结构的聚合。

#roll up语句
select device_id,os_id,app_id,client_version,from_id,count(user_id)
from test_xinyan_reg
group by device_id,os_id,app_id,client_version,from_id with rollup;
#等价的hive语句
select device_id,os_id,app_id,client_version,from_id,count(user_id)
from test_xinyan_reg
group by device_id,os_id,app_id,client_version,from_id 
grouping sets ((device_id,os_id,app_id,client_version,from_id),(device_id,os_id,app_id,client_version),(device_id,os_id,app_id),(device_id,os_id),(device_id),());

3、with cube函数

cube简称数据魔方，可以实现hive多个任意维度的查询，cube(a,b,c)则首先会对(a,b,c)进行group by，然后依次是(a,b),(a,c),(a),(b,c),(b),©,最后在对全表进行group by，他会统计所选列中值的所有组合的聚合 cube即为grouping sets的简化过程函数

#with cube语句
select device_id,os_id,app_id,client_version,from_id,count(user_id)
from test_xinyan_reg
group by device_id,os_id,app_id,client_version,from_id with cube;
#等价的hive语句
SELECT device_id,null,null,null,null ,count(user_id) FROM test_xinyan_reg group by device_id
UNION ALL
SELECT null,os_id,null,null,null ,count(user_id) FROM test_xinyan_reg group by os_id
UNION ALL
SELECT device_id,os_id,null,null,null ,count(user_id) FROM test_xinyan_reg group by device_id,os_id
UNION ALL
SELECT null,null,app_id,null,null ,count(user_id) FROM test_xinyan_reg group by app_id
UNION ALL
SELECT device_id,null,app_id,null,null ,count(user_id) FROM test_xinyan_reg group by device_id,app_id
UNION ALL
SELECT null,os_id,app_id,null,null ,count(user_id) FROM test_xinyan_reg group by os_id,app_id
UNION ALL
SELECT device_id,os_id,app_id,null,null ,count(user_id) FROM test_xinyan_reg group by device_id,os_id,app_id
UNION ALL
SELECT null,null,null,client_version,null ,count(user_id) FROM test_xinyan_reg group by client_version
UNION ALL
SELECT device_id,null,null,client_version,null ,count(user_id) FROM test_xinyan_reg group by device_id,client_version
UNION ALL
SELECT null,os_id,null,client_version,null ,count(user_id) FROM test_xinyan_reg group by os_id,client_version
UNION ALL
SELECT device_id,os_id,null,client_version,null ,count(user_id) FROM test_xinyan_reg group by device_id,os_id,client_version
UNION ALL
SELECT null,null,app_id,client_version,null ,count(user_id) FROM test_xinyan_reg group by app_id,client_version
UNION ALL
SELECT device_id,null,app_id,client_version,null ,count(user_id) FROM test_xinyan_reg group by device_id,app_id,client_version
UNION ALL
SELECT null,os_id,app_id,client_version,null ,count(user_id) FROM test_xinyan_reg group by os_id,app_id,client_version
UNION ALL
SELECT device_id,os_id,app_id,client_version,null ,count(user_id) FROM test_xinyan_reg group by device_id,os_id,app_id,client_version
UNION ALL
SELECT null,null,null,null,from_id ,count(user_id) FROM test_xinyan_reg group by from_id
UNION ALL
SELECT device_id,null,null,null,from_id ,count(user_id) FROM test_xinyan_reg group by device_id,from_id
UNION ALL
SELECT null,os_id,null,null,from_id ,count(user_id) FROM test_xinyan_reg group by os_id,from_id
UNION ALL
SELECT device_id,os_id,null,null,from_id ,count(user_id) FROM test_xinyan_reg group by device_id,os_id,from_id
UNION ALL
SELECT null,null,app_id,null,from_id ,count(user_id) FROM test_xinyan_reg group by app_id,from_id
UNION ALL
SELECT device_id,null,app_id,null,from_id ,count(user_id) FROM test_xinyan_reg group by device_id,app_id,from_id
UNION ALL
SELECT null,os_id,app_id,null,from_id ,count(user_id) FROM test_xinyan_reg group by os_id,app_id,from_id
UNION ALL
SELECT device_id,os_id,app_id,null,from_id ,count(user_id) FROM test_xinyan_reg group by device_id,os_id,app_id,from_id
UNION ALL
SELECT null,null,null,client_version,from_id ,count(user_id) FROM test_xinyan_reg group by client_version,from_id
UNION ALL
SELECT device_id,null,null,client_version,from_id ,count(user_id) FROM test_xinyan_reg group by device_id,client_version,from_id
UNION ALL
SELECT null,os_id,null,client_version,from_id ,count(user_id) FROM test_xinyan_reg group by os_id,client_version,from_id
UNION ALL
SELECT device_id,os_id,null,client_version,from_id ,count(user_id) FROM test_xinyan_reg group by device_id,os_id,client_version,from_id
UNION ALL
SELECT null,null,app_id,client_version,from_id ,count(user_id) FROM test_xinyan_reg group by app_id,client_version,from_id
UNION ALL
SELECT device_id,null,app_id,client_version,from_id ,count(user_id) FROM test_xinyan_reg group by device_id,app_id,client_version,from_id
UNION ALL
SELECT null,os_id,app_id,client_version,from_id ,count(user_id) FROM test_xinyan_reg group by os_id,app_id,client_version,from_id
UNION ALL
SELECT device_id,os_id,app_id,client_version,from_id ,count(user_id) FROM test_xinyan_reg group by device_id,os_id,app_id,client_version,from_id
UNION ALL
SELECT null,null,null,null,null ,count(user_id) FROM test_xinyan_reg

十、格式化创建动态表

1、CSV文件

该创建方式的忽略字段中包含的分割字符

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
	WITH SERDEPROPERTIES (
    "separatorChar" = ",",
    "quoteChar" = "\"",
	"escapeChar" = "\\"
	)
STORED AS TEXTFILE;

2、json文件

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
    WITH SERDEPROPERTIES (
       "separatorChar" = "\t",
       "quoteChar"     = "'",
       "escapeChar"    = "\\"
    )  
STORED AS TEXTFILE;

十一、增量表、全量表和拉链表

1、增量表

记录更新周期内新增数据，即在原表中数据的基础上新增本周期内产生的新数据。

2、全量表

记录更新周期内的全量数据，无论数据是否有变化都需要记录

3、拉链表

拉链表是针对数据仓库设计中表存储数据的方式而定义，所谓拉链急事记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。

应用场景： 大量的历史数据+新增的数据+有限时间范围内（截止拉取数据的时间）的少量的更新数据

十二、数仓分层

1、源数据层（ODS）

原始数据层，存放原始数据，直接加载原始日志、数据，数据保持原貌不作处理

2、明细粒度事实层（DWD）

以业务过程作为建模驱动，基于每个具体的业务过程特点，构建最细粒度的明细层事实表。可以结合企业的数据使用特点，将明细事实表的某些重要维度属性字段做适当冗余，即宽表化处理。

3、数据中间层（DWM）

在DWD层的数据基础上，对数据做轻度的聚合操作，生成一系列的中间表，提升公共指标的复用性，减少重复加工。直观来讲，就是对通用的核心维度进行聚合操作，算出相应的统计指标

4、公共汇总粒度事实层（DWS）

以分析的主题对象作为建模驱动，基于上层的应用和产品的指标需求，构建公共粒度的汇总指标事实表，以宽表化手段物理化模型。构建命名规范、口径一致的统计指标，为上层提供公共指标，建立汇总宽表、明细事实表。

5、公共维度层（DIM）

基于维度建模理念，建立整个企业的一致性维度。降低数据计算口径和算法不统一风险。此表也被称为逻辑维度表，维度和维度逻辑表通常一一对应。

十三、数仓模型

1、星型模型

由事实表和多个维表组成。事实表中存放大量关于企业的事实数据，元祖个数通常很大，而且非规范化程度很高

优点：

1. **读取速度快：**针对各个维做了大量预处理，如按照维度进行预先的统计、分组合排序等
2. **多种数据源，**减少异构数据带来的分析复杂性
3. 标准性，新员工可快速掌握，数据工程师和分析师比较了解，可促进协作
4. 可扩展性，添加的事实表可以重用先有维度向事实表添加更多外键，实现事实表添加新维度

2、雪花模型

星型模型的扩展，将星型模型的维表进一步层次化，原来的各个维表可能被扩展为小的事实表，形成一些局部的层次区域

特点：

1. 通过定义多重父类维表来描述某些特殊维表定义特殊的统计信息
2. 最大限度的减少数据存储量
3. 把较小的维度表联合在一起改善查询性能

3、星座模型

星型模型的扩展延伸，多张事实表共享维度表，只有一些大型公司使用