大数据组件之Hive
一、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基本数据类型
| 字段 | 数据类型 | 和Java对应的数据类型 |
|---|---|---|
| 短短整型 | TINYINT | byte |
| 短整型 | SMALLINT | short |
| 整型 | INT | int |
| 长整型 | BIGINT | long |
| 布尔型 | BOOLEAN | boolean |
| 浮点型 | FLOAT | float |
| 双精度 | DOUBLE | double |
| 双精度 | DOUBLE PRECISION | double |
| 字符 | STRING | String |
| 比特 | BINARY | bit |
| 时间戳 | TIMESTAMP | date |
| 大数据 | DECMAL | BigDecimal |
| 大数据 | DECIMAL(precision，scala) | BigDecimal |
| 时间 | DATE | date |
| 不定长字符 | VARCHAR | String |
| 字符类型 | CHAR | String |
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]
- **temporary：**建立内部表，**external：**建立外部表
- if not exits： 判断表名是否已经存在，如果存在就抛出异常
- comment: 为表和列添加注释
- partitioned by： 创建分区表
- clustered by： 创建分桶表
- sorted by： 对桶中的一个或者多个列进行排序
- rowformat：
- stored as： 指定文件存储类型。常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、 TEXTFILE（文本）、 RCFILE（列式存储文件）
- location： 指定表在HDFS上存储位置
- AS： 根据查询结果创建表
- 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],...)
- ADD是代表新增一字段 ，字段位置在所有列后面 (partition列前 )
- REPLACE则是表示替换中所有字段。
5、删除表
1.删除表
#语法结构
drop table table_name;
#例程
drop table stu;
2.清空表
#清空表
truncate table 表名;
五、Hive的DML
1、数据导入方式
数据导入方式可以分为以下几种：
- 通过hive，使用insert方式插入
- 使用HDFS的put命令，将文件直接写入到hive指定表文件夹下的方式
- 使用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、排序
- asc: 为升序排序
- 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、日期函数
| 返回值 | 语法结构 | 描述 |
|---|---|---|
| string | from_unix(bigint time,string time_format) | 将时间戳进行格式化输出 |
| bigint | unix_timestamp() | 获得当前时区的UNIX时间戳 |
| bigint | unix_timestamp(string date) | 将格式为yyyy-MM-dd HH:mm:ss的时间字符串转换成时间戳。如果转化失败，则返回0。 |
| bigint | unix_timestamp(string date, string pattern) | 将指定时间字符串格式字符串转换成Unix时间戳。如果转化失败，则返回0。 |
| string | to_date(string timestamp) | 返回日期时间字段中的日期部分 |
| int | year(string date) | 返回日期中的年 |
| int | month (string date) | 返回日期中的月份 |
| int | day (string date) | 返回日期中的天 |
| int | hour (string date) | 返回日期中的小时 |
| int | minute (string date) | 返回日期中的分钟 |
| int | second (string date) | 返回日期中的秒 |
| int | weekofyear (string date) | 返回时间字符串位于一年中的第几个周内 |
| int | datediff(string enddate, string startdate) | 返回结束日期减去开始日期的天数 |
| string | date_add(string startdate, int days) | 返回开始日期startdate增加days天后的日期 |
| string | date_sub (string startdate, int days) | 返回开始日期startdate减少days天后的日期 |
| timestamp | from_utc_timestamp(timestamp, string timezone) | 如果给定的时间戳并非UTC，则将其转化成指定的时区下时间戳 |
| timestamp | to_utc_timestamp(timestamp, string timezone) | 如果给定的时间戳指定的时区下时间戳，则将其转化成UTC下的时间戳 |
| date | current_date() | 返回当前时间日期 |
| timestamp | current_timestamp() | 返回当前时间戳 |
| string | add_months(string start_date, int num_months) | 返回当前时间下再增加num_months个月的日期 |
| string | last_day(string date) | 返回这个月的最后一天的日期，忽略时分秒部分（HH:mm:ss） |
| string | next_day(string start_date, string day_of_week) | 返回当前时间的下一个星期X所对应的日期 |
| string | trunc(string date, string format) | 返回时间的最开始年份或月份 |
| double | months_between(date1, date2) | 返回date1与date2之间相差的月份，如date1>date2，则返回正，如果date1<date2,则返回负，否则返回0.0 |
| string | date_format(date/timestamp/string ts, string fmt) | 按指定格式返回时间date |
| int | dayofweek(date) | 返回日期那天的周几 |
| int | quarter(date/timestamp/string) | 返回当前时间属性哪个季度 |
2、数学函数
| 返回值 | 语法结构 | 描述 |
|---|---|---|
| double | round(double a) | 返回double类型的整数值部分（遵循四舍五入） |
| double | round(double a, int d) | 返回指定精度d的double类型 |
| bigint | floor(double a) | 返回等于或者小于该double变量的最大的整数 |
| bigint | ceil(double a) | 返回等于或者大于该double变量的最小的整数 |
| bigint | ceiling(double a) | 返回等于或者大于该double变量的最小的整数 |
| double | rand(),rand(int seed) | 返回一个double型0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列 |
| double | exp(double a) | 返回自然对数e的a次方,a可为小数 |
| double | ln(double a) | 返回a的自然对数，a可为小数 |
| double | log10(double a) | 返回以10为底的a的对数，a可为小数 |
| double | log2(double a) | 返回以2为底的a的对数，a可为小数 |
| double | log(double base, double a) | 返回以base为底的a的对数，base与a都是double类型 |
| double | pow(double a, double p), power(double a, double p) | 返回a的p次幂 |
| double | sqrt(double a) | 返回a的平方根 |
| string | bin(BIGINT a) | 返回a的二进制代码表示,，a为BIGINT类型 |
| string | hex(BIGINT a),hex(string a) | 如果变量是int类型，那么返回a的十六进制表示； |
| string | unhex(string a) | 返回该十六进制字符串所代码的字符串,hex的逆方法 |
| string | conv(BIGINT num, int from_base, int to_base),conv(STRING num, int from_base, int to_base) | 将bigint/string数值num从from_base进制转化到to_base进制 |
| double or int | abs(double a),abs(int a) | 返回数值a的绝对值 |
| int or double | pmod(int a, int b),pmod(double a, double b) | 返回正的a除以b的余数 |
| double | sin(double a) | 返回a的正弦值 |
| double | asin(double a) | 返回a的反正弦值 |
| double | cos(double a) | 返回a的余弦值 |
| double | acos(double a) | 返回a的反余弦值 |
| double | tan(double a) | 返回a的正切值 |
| double | atan(double a) | 返回a的反正切值 |
| double | degrees(double a) | 返回a的角度值 |
| double | radians(double a) | 返回a的弧度值 |
| int or double | positive(int a), positive(double a) | 返回a的正数 |
| int or double | negative(int a), negative(double a) | 返回a的负数 |
| double | sign(double a) | 如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0 |
| double | e() | 数学常数e |
| double | pi() | 圆周率π |
| bigint | factorial(int a) | 求a的阶乘 |
| double | cbrt(double a) | 求a的立方根 |
| int bigint | shiftleft(BIGINT a, int b) | 按位左移 |
| int bigint | shiftright(BIGINT a, int b) | 按位右移 |
| int bigint | shiftrightunsigned(BIGINT a, int b) | 无符号按位右移（<<<） |
| T | greatest(T v1, T v2, …) | 求最大值 |
| T | least(T v1, T v2, …) | 求最小值 |
| double | bround(double a) | 银行家舍入法（1-4：舍，6-9：进，5->前位数是偶：舍，5->前位数是奇：进） |
| double | bround(double a，int d) | 银行家舍入法,保留d位小数 |
3、字符函数
| 返回值 | 语法结构 | 功能描述 |
|---|---|---|
| int | ascii(string str) | 返回字符串str第一个字符的ascii码 |
| string | base64(binary bin) | 将二进制bin转换成64位的字符串 |
| string | concat(string A, string B…) | 返回输入字符串连接后的结果，支持任意个输入字符串 |
| array<struct<string,double>> | context_ngrams(array<array>, array, int K, int pf) | 与ngram类似，但context_ngram()允许你预算指定上下文(数组)来去查找子序列 |
| string | concat_ws(string SEP, string A, string B…) | concat_ws(string SEP, array) | 返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符 |
| string | decode(binary bin, string charset) | 使用指定的字符集charset将二进制值bin解码成字符串，支持的字符集有：‘US-ASCII’, ‘ISO-8859-1’, ‘UTF-8’, ‘UTF-16BE’, ‘UTF-16LE’, ‘UTF-16’，如果任意输入参数为NULL都将返回NULL |
| binary | encode(string src, string charset) | 使用指定的字符集charset将字符串编码成二进制值，支持的字符集有：‘US-ASCII’, ‘ISO-8859-1’, ‘UTF-8’, ‘UTF-16BE’, ‘UTF-16LE’, ‘UTF-16’，如果任一输入参数为NULL都将返回NULL |
| int | find_in_set(string str, string strList) | 返回str在strlist第一次出现的位置，strlist是用逗号分割的字符串。如果没有找该str字符，则返回0，如果任一参数为NULL将返回NULL |
| string | format_number(number x, int d) | 将数值X转换成"#,###,###.##"格式字符串，并保留d位小数，如果d为0，将进行四舍五入且不保留小数 |
| string | get_json_object(string json_string, string path) | 解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。注意此路径上JSON字符串只能由数字 字母 下划线组成且不能有大写字母和特殊字符，且key不能由数字开头，这是由于Hive对列名的限制 |
| boolean | in_file(string str, string filename) | 如果文件名为filename的文件中有一行数据与字符串str匹配成功就返回true |
| int | instr(string str, string substr) | 查找字符串str中子字符串substr出现的位置，如果查找失败将返回0，如果任一参数为Null将返回null，注意位置为从1开始的 |
| int | length(string A) | 返回字符串A的长度 |
| int | locate(string substr, string str[, int pos]) | 查找字符串str中的pos位置后字符串substr第一次出现的位置 |
| string | lower(string A) lcase(string A) | 返回字符串A的小写格式 |
| string | lpad(string str, int len, string pad) | 将str进行用pad进行左补足到len位,从左边开始对字符串str使用字符串pad填充，最终len长度为止，如果字符串str本身长度比len大的话，将去掉多余的部分 |
| string | ltrim(string A) | 去掉字符串A前面的空格 |
| array<struct<string,double>> | ngrams(array, int N, int K, int pf) | 返回出现次数TOP K的的子序列,n表示子序列的长度 |
| string | parse_url(string urlString, string partToExtract [, stringkeyToExtract]) | 返回从URL中抽取指定部分的内容，参数url是URL字符串，而参数partToExtract是要抽取的部分，这个参数包含(HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO |
| string | printf(String format, Obj… args) | 按照printf风格格式输出字符串 |
| string | regexp_extract(string subject, string pattern, int index) | 将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。注意些预定义字符的使用，如第二个参数如果使用’\s’将被匹配到s,’\s’才是匹配空格 |
| string | regexp_replace(string A, string B, string C) | 按照Java正则表达式PATTERN将字符串INTIAL_STRING中符合条件的部分成REPLACEMENT所指定的字符串，如里REPLACEMENT这空的话，抽符合正则的部分将被去掉 |
| string | repeat(string str, int n) | 返回重复n次后的str字符串 |
| string | reverse(string A) | 返回字符串A的反转结果 |
| string | rpad(string str, int len, string pad) | 从右边开始对字符串str使用字符串pad填充，最终len长度为止，如果字符串str本身长度比len大的话，将去掉多余的部分 |
| string | rtrim(string A) | 去除字符串右边的空格 |
| array | sentences(string str, string lang, string locale) | 字符串str将被转换成单词数组，如：sentences(‘Hello there! How are you?’) =( (“Hello”, “there”), (“How”, “are”, “you”) ) |
| string | space(int n) | 返回n个空格 |
| array | split(string str, string pat) | 按照pat字符串分割str，会返回分割后的字符串数组 |
| map<string,string> | str_to_map(text[, delimiter1, delimiter2]) | 将字符串str按照指定分隔符转换成Map，第一个参数是需要转换字符串，第二个参数是键值对之间的分隔符，默认为逗号;第三个参数是键值之间的分隔符，默认为"=" |
| string | substr(string A, int start),substring(string A, int start) | 返回字符串A从start位置到结尾的字符串 |
| string | substr(string A, int start, int len),substring(string A, int start, int len) | 返回字符串A从start位置开始，长度为len的字符串 |
| string | substring_index(string A, string delim, int count) | 截取第count分隔符之前的字符串，如count为正则从左边开始截取，如果为负则从右边开始截取 |
| string | translate(string|char|varchar input, string|char|varchar from,string|char|varchar to) | 将input出现在from中的字符串替换成to中的字符串 |
| string | trim(string A) | 去除字符串两边的空格 |
| binary | unbase64(string str) | 将64位的字符串转换二进制值 |
| string | upper(string A) ucase(string A) | 将字符串A中的字母转换成大写字母 |
| string | initcap(string A) | 将字符串A转换第一个字母大写其余字母的字符串 |
| int | levenshtein(string A, string B) | 计算两个字符串之间的差异大小 |
| string | soundex(string A) | 将普通字符串转换成soundex字符串 |
4、聚合函数
| 返回值 | 语法结构 | 功能描述 |
|---|---|---|
| bigint | count(*), count(expr), count(DISTINCT expr[, expr…]) | count(*)统计检索出的行的个数，包括NULL值的行；count(expr)返回指定字段的非空值的个数； |
| double | sum(col), sum(DISTINCT col) | sum(col)统计结果集中col的相加的结果；sum(DISTINCT col)统计结果中col不同值相加的结果 |
| double | avg(col), avg(DISTINCT col) | avg(col)统计结果集中col的平均值；avg(DISTINCT col)统计结果中col不同值相加的平均值 |
| double | min(col) | 统计结果集中col字段的最小值 |
| double | max(col) | 统计结果集中col字段的最大值 |
| double | variance(col), var_pop(col) | 统计结果集中col非空集合的总体变量（忽略null），（求指定列数值的方差） |
| double | var_samp (col) | 统计结果集中col非空集合的样本变量（忽略null）（求指定列数值的样本方差） |
| double | stddev_pop(col) | 该函数计算总体标准偏离，并返回总体变量的平方根，其返回值与VAR_POP函数的平方根相同（求指定列数值的标准偏差） |
| double | stddev_samp (col) | 该函数计算样本标准偏离，（求指定列数值的样本标准偏差） |
| double | covar_pop(col1, col2) | 求指定列数值的协方差 |
| double | covar_samp(col1, col2) | 求指定列数值的样本协方差 |
| double | corr(col1, col2) | 返回两列数值的相关系数 |
| double | percentile(BIGINT col, p) | 求准确的第pth个百分位数，p必须介于0和1之间，但是col字段目前只支持整数，不支持浮点数类型 |
| array | percentile(BIGINT col, array(p1 [, p2]…)) | 功能和上述类似，之后后面可以输入多个百分位数，返回类型也为array，其中为对应的百分位数 |
| double | percentile_approx(DOUBLE col, p [, B]) | 求近似的第pth个百分位数，p必须介于0和1之间，返回类型为double，但是col字段支持浮点类型。参数B控制内存消耗的近似精度，B越大，结果的准确度越高。默认为10,000。当col字段中的distinct值的个数小于B时，结果为准确的百分位数 |
| array | percentile_approx(DOUBLE col, array(p1 [, p2]…) [, B]) | 功能和上述类似，之后后面可以输入多个百分位数，返回类型也为array，其中为对应的百分位数。 |
| array<struct {‘x’,‘y’}> | histogram_numeric(col, b) | 以b为基准计算col的直方图信息 |
5、集合函数
| 返回值 | 语法结构 | 功能描述 |
|---|---|---|
| int | size(Map<K.V>) | 返回map类型的长度 |
| int | size(Array) | 求数组的长度 |
| array | map_keys(Map<K.V>) | 返回map中的所有key |
| array | map_keys(Map<K.V>) | 返回map中的所有value |
| boolean | array_contains(Array, value) | 如该数组Array包含value返回true。，否则返回false |
| array | sort_array(Array) | 按自然顺序对数组进行排序并返回 |
6、条件函数
| 返回值 | 语法结构 | 功能描述 |
|---|---|---|
| T | if(boolean testCondition, T valueTrue, T valueFalseOrNull) | 当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull（valueTrue，valueFalseOrNull为泛型） |
| T | nvl(T value, T default_value) | 如果value值为NULL就返回default_value,否则返回value |
| T | COALESCE(T v1, T v2,…) | 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL |
| T | CASE a WHEN b THEN c [WHEN d THEN e] [ELSE f] END* | 如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f |
| T | CASE WHEN a THEN b [WHEN c THEN d] [ELSE e] END* | 如果a为TRUE,则返回b；如果c为TRUE，则返回d；否则返回e |
| boolean | isnull( a ) | 如果a为null就返回true，否则返回false |
| boolean | isnotnull ( a ) | 如果a为非null就返回true，否则返回false |
7、表生成函数
| 返回值 | 语法结构 | 功能描述 |
|---|---|---|
| Array Type | explode(array a) | 对于a中的每个元素，将生成一行且包含该元素 |
| N rows | explode(ARRAY) | 每行对应数组中的一个元素 |
| N rows | explode(MAP) | 每行对应每个map键-值，其中一个字段是map的键，另一个字段是map的值 |
| N rows | posexplode(ARRAY) | 与explode类似，不同的是还返回各元素在数组中的位置 |
| N rows | stack(INT n, v_1, v_2, …, v_k) | 把M列转换成N行，每行有M/N个字段，其中n必须是个常数 |
| tuple | json_tuple(jsonStr, k1, k2, …) | 从一个JSON字符串中获取多个键并作为一个元组返回，与get_json_object不同的是此函数能一次获取多个键值 |
| tuple | parse_url_tuple(url, p1, p2, …) | 返回从URL中抽取指定N部分的内容，参数url是URL字符串，而参数p1,p2,…是要抽取的部分，这个参数包含HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY: |
| tuple | inline(ARRAY<STRUCT[,STRUCT]>) | 将结构体数组提取出来并插入到表中 |
8、类型转换函数
| 返回值 | 语法结构 | 功能描述 |
|---|---|---|
| binary | binary(string|binary) | 将输入的值转换成二进制 |
| Expected “=” to follow “type” | cast(expr as ) | 将expr转换成type类型 如：cast(“1” as BIGINT) 将字符串1转换成了BIGINT类型，如果转换失败将返回NULL |
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 ...)
- 当前行：current row
- 当前行的前多少行： preceding
- 当前行的后多少行：following
- 无边界：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、星型模型
由事实表和多个维表组成。事实表中存放大量关于企业的事实数据，元祖个数通常很大，而且非规范化程度很高
优点：
- **读取速度快：**针对各个维做了大量预处理，如按照维度进行预先的统计、分组合排序等
- **多种数据源，**减少异构数据带来的分析复杂性
- 标准性，新员工可快速掌握，数据工程师和分析师比较了解，可促进协作
- 可扩展性，添加的事实表可以重用先有维度向事实表添加更多外键，实现事实表添加新维度
2、雪花模型
星型模型的扩展，将星型模型的维表进一步层次化，原来的各个维表可能被扩展为小的事实表，形成一些局部的层次区域
特点：
- 通过定义多重父类维表来描述某些特殊维表定义特殊的统计信息
- 最大限度的减少数据存储量
- 把较小的维度表联合在一起改善查询性能
3、星座模型
星型模型的扩展延伸，多张事实表共享维度表，只有一些大型公司使用
