大数据组件之Hive（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
#-------------------------------------# hiveexport 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的元数据

showtables;+-------------------------------+| 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 intotable kb16.user_movie_rating_par partition(dt)select userid,moviedid,rating,`timestamp`,data_format(from_unixtime(`timestamp`),'yyyy-MM') dt from user_moive_rating;orderby`timestamp`desclimit10;

三、Hive的数据类型

1、Hive基本数据类型

字段数据类型和Java对应的数据类型短短整型TINYINTbyte短整型SMALLINTshort整型INTint长整型BIGINTlong布尔型BOOLEANboolean浮点型FLOATfloat双精度DOUBLEdouble双精度DOUBLE PRECISIONdouble字符STRINGString比特BINARYbit时间戳TIMESTAMPdate大数据DECMALBigDecimal大数据DECIMAL(precision，scala)BigDecimal时间DATEdate不定长字符VARCHARString字符类型CHARString

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<province:string,city:string,district:string>)

4.集合类型

UNIONTYPE<data_type,data_type,...>

四、Hive的DDL

1、数据库的基本操作

1.创建数据库

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

2.显示数据库信息

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

3.显示数据库详细信息

#描述数据库详细信息descdatabaseextended db_hive;

4.切换数据库

#切换数据库use db_hive;

5.修改数据库

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

6.删除数据库

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

2、创建表

1.建表语句

#EXTERNAL表示创建外部表，TEMPORARY表示创建内部表，创建时建议库名.表名CREATE[TEMPORARY][EXTERNAL]TABLE[IFNOTEXISTS][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按照什么字段进行排序[CLUSTEREDBY(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|DOUBLEPRECISION-- (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 [FIELDSTERMINATEDBYchar[ESCAPEDBYchar]][COLLECTION ITEMS TERMINATEDBYchar][MAP KEYSTERMINATEDBYchar][LINESTERMINATEDBYchar][NULL DEFINED ASchar]-- (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:
  : [PRIMARYKEY|UNIQUE|NOTNULL|DEFAULT[default_value]|CHECK[check_expression]ENABLE|DISABLE NOVALIDATE RELY/NORELY ]
 
default_value:
  : [ LITERAL|CURRENT_USER()|CURRENT_DATE()|CURRENT_TIMESTAMP()|NULL] 
 
constraint_specification:
  : [,PRIMARYKEY(col_name,...)DISABLE NOVALIDATE RELY/NORELY ][,PRIMARYKEY(col_name,...)DISABLE NOVALIDATE RELY/NORELY ][,CONSTRAINT constraint_name FOREIGNKEY(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[ifnot exits][dbname.]tabname
[(
    colname data_type [comment col_comment],...,[constraint_specification])][comment table_comment]#创建分区表[partitioned by(extrenal_colname data_type [comment col_comment],...)]#创建分桶表[clusteredby(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.创建简单表

#创建简单表createtable kb16.student(stuname string,stuage int,stugender string);#插入数据insertinto 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<province:string,city:string,district:string>)row format delimited
#字段用,进行分割fieldsterminatedby','#数组用|进行分割
collection items terminatedby'|'#图
map keysterminatedby':'#每行按照换行进行分割linesterminatedby'\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 province,stuaddress.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.从已有表复制新表

#将一个表的查询结果创建为一个新表createtable kb16.student_cpoy 
asselect*from kb16.student_ext;

6.复制表结构

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

7.指定路径创建数据表

#用一个挂载在HDFS上文件创建新的数据表7create external table kb16.user_movie_rating(
userid bigint,
movieid bigint,
rating decimal(2,1),`timestamp`bigint)row format delimited
fieldsterminatedby','
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
fieldsterminatedby',';

9.创建分桶表

#创建分区分桶表create external table user_movie_ratin_par_bucket(
userid bigint,
movieid bigint,
rating decimal(2,1),`timestamp`bigint,)partitionby(years int)clusteredby(`timestamp`) sorted by(`timestamp`ASC)into5 buckets
row format delimited fieldsterminatedby',';

3、查看表

1.查看数据表

#查看当前数据库中有哪些数据表showtables;#查看非当前数据的数据表showtablesin HIVE_DATABASE;#查看数据库以xxx开头的表showtableslike'xxx*';

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

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

3.查看表分区

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

4.查看详细建表语句

#查看详细建表语句showcreatetable 表名;

4、修改表

1.重命名表

#语法结构altertable table_name renameto new_table_name;#例程altertable student renameto stu;

2.修改字段定义

#增加一个字段altertable 表名 addcolumns(字段名  字段类型);#修改一个字段的定义altertable 表名 change name 字段名 字段类型;#替换所有字段altertable 表名 replacecolumns(字段1 字段类型1,字段2 字段类型2...)

3.修改分区信息

#添加分区altertable 表名 addpartition(字段="值");#添加多个分区altertable 表名 addpartition(分区字段="值1")partition(分区字段="值2");#动态分区loaddatalocal inpath "文件的HDFS路径"intotable 表名 partition(字段="值");

4.修改分区

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

5.删除表分区

#删除表分区altertable 表名 droppartition(字段="值");

6.更新列

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

7.增加和替换列

#语法结构altertable table_name add|replacecolumns(col_name data_type[comment col_comment],...)

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

5、删除表

1.删除表

#语法结构droptable table_name;#例程droptable stu;

2.清空表

#清空表truncatetable 表名;

五、Hive的DML

1、数据导入方式

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

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

2、数据导入

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

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

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

#将数据挂载在数据表中loaddata inpath '/xxx/student.txt' overwrite intotable student;

3.通过insert插入

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

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

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

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

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

6.向分桶表插入数据

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

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 FIELDSTERMINATEDBY'\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

#从导出结果导入到Hiveimporttable student3 from'/export/student';

3、数据删除

#只删表数据，不删表本身truncatetable student;#彻底删除数据droptable student;

六、Hive的DQL

SELECT[ALL|DISTINCT] select_expr, select_expr,...FROM table_reference
[WHERE where_condition][GROUPBY col_list][ORDERBY 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 +10from emp;

5.函数

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

2、条件过滤

1.数据量限制

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

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 =30and sal >1000;

3、分组

1.group by 分组

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

2.分组过滤

#分组过滤#计算部门平均工资大于2000的部门select deptno,avg(sal) aa from emp groupby 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
leftjoin table_b b
ON(a.column= b.column);

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

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

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

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

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

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

5.LEFT SEMI JOIN

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

#LEFT SEMI JOINSELECT 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
crossjoin table_b b;

5、排序

asc: 为升序排序
desc: 为降序排序

1.单字段排序

#单字段降序排序SELECT column1,column2,...FROM table_a
orderby 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.创建分桶表

#创建分桶表createtable stu_buck(id int, name string)clusteredby(id)into4 buckets
row format delimited fieldsterminatedby'\t';

2.插入数据

#向分桶表中插入数据loaddatalocal inpath '/opt/module/datas/student.txt'intotable stu_buck;

七、hive函数

1、日期函数

返回值语法结构描述string**from_unix(bigint time,string time_format)将时间戳进行格式化输出bigintunix_timestamp()获得当前时区的UNIX时间戳bigintunix_timestamp(string date)将格式为yyyy-MM-dd HH:mm:ss的时间字符串转换成时间戳。如果转化失败，则返回0。bigintunix_timestamp(string date, string pattern)将指定时间字符串格式字符串转换成Unix时间戳。如果转化失败，则返回0。stringto_date(string timestamp)返回日期时间字段中的日期部分intyear(string date)返回日期中的年intmonth (string date)返回日期中的月份intday (string date)返回日期中的天inthour (string date)返回日期中的小时intminute (string date)返回日期中的分钟intsecond (string date)返回日期中的秒intweekofyear (string date)返回时间字符串位于一年中的第几个周内intdatediff(string enddate, string startdate)返回结束日期减去开始日期的天数stringdate_add(string startdate, int days)返回开始日期startdate增加days天后的日期stringdate_sub (string startdate, int days)返回开始日期startdate减少days天后的日期timestampfrom_utc_timestamp(timestamp, string timezone)如果给定的时间戳并非UTC，则将其转化成指定的时区下时间戳timestampto_utc_timestamp(timestamp, string timezone)如果给定的时间戳指定的时区下时间戳，则将其转化成UTC下的时间戳datecurrent_date()返回当前时间日期timestampcurrent_timestamp()返回当前时间戳stringadd_months(string start_date, int num_months)返回当前时间下再增加num_months个月的日期stringlast_day(string date)返回这个月的最后一天的日期，忽略时分秒部分（HH:mm:ss）stringnext_day(string start_date, string day_of_week)返回当前时间的下一个星期X所对应的日期stringtrunc(string date, string format)返回时间的最开始年份或月份doublemonths_between(date1, date2)返回date1与date2之间相差的月份，如date1>date2，则返回正，如果date1<date2,则返回负，否则返回0.0stringdate_format(date/timestamp/string ts, string fmt)按指定格式返回时间dateintdayofweek(date)返回日期那天的周几intquarter(date/timestamp/string)**返回当前时间属性哪个季度

2、数学函数

返回值语法结构描述double**round(double a)返回double类型的整数值部分（遵循四舍五入）doubleround(double a, int d)返回指定精度d的double类型bigintfloor(double a)返回等于或者小于该double变量的最大的整数bigintceil(double a)返回等于或者大于该double变量的最小的整数bigintceiling(double a)返回等于或者大于该double变量的最小的整数doublerand(),rand(int seed)返回一个double型0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列doubleexp(double a)返回自然对数e的a次方,a可为小数doubleln(double a)返回a的自然对数，a可为小数doublelog10(double a)返回以10为底的a的对数，a可为小数doublelog2(double a)返回以2为底的a的对数，a可为小数doublelog(double base, double a)返回以base为底的a的对数，base与a都是double类型doublepow(double a, double p), power(double a, double p)返回a的p次幂doublesqrt(double a)返回a的平方根stringbin(BIGINT a)返回a的二进制代码表示,，a为BIGINT类型stringhex(BIGINT a),hex(string a)如果变量是int类型，那么返回a的十六进制表示；stringunhex(string a)返回该十六进制字符串所代码的字符串,hex的逆方法stringconv(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 intabs(double a),abs(int a)返回数值a的绝对值int or doublepmod(int a, int b),pmod(double a, double b)返回正的a除以b的余数doublesin(double a)返回a的正弦值doubleasin(double a)返回a的反正弦值doublecos(double a)返回a的余弦值doubleacos(double a)返回a的反余弦值doubletan(double a)返回a的正切值doubleatan(double a)返回a的反正切值doubledegrees(double a)返回a的角度值doubleradians(double a)返回a的弧度值int or doublepositive(int a), positive(double a)返回a的正数int or doublenegative(int a), negative(double a)返回a的负数doublesign(double a)如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0doublee()数学常数edoublepi()圆周率πbigintfactorial(int a)求a的阶乘doublecbrt(double a)求a的立方根int bigintshiftleft(BIGINT a, int b)按位左移int bigintshiftright(BIGINT a, int b)按位右移int bigintshiftrightunsigned(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->前位数是奇：进）doublebround(double a，int d)**银行家舍入法,保留d位小数

3、字符函数

返回值语法结构功能描述int**ascii(string str)返回字符串str第一个字符的ascii码stringbase64(binary bin)将二进制bin转换成64位的字符串stringconcat(string A, string B…)**返回输入字符串连接后的结果，支持任意个输入字符串array<struct<string,double>>**context_ngrams(array<array>, array, int K, int pf)与ngram类似，但context_ngram()允许你预算指定上下文(数组)来去查找子序列stringconcat_ws(string SEP, string A, string B…) | concat_ws(string SEP, array)返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符stringdecode(binary bin, string charset)使用指定的字符集charset将二进制值bin解码成字符串，支持的字符集有：‘US-ASCII’, ‘ISO-8859-1’, ‘UTF-8’, ‘UTF-16BE’, ‘UTF-16LE’, ‘UTF-16’，如果任意输入参数为NULL都将返回NULLbinaryencode(string src, string charset)使用指定的字符集charset将字符串编码成二进制值，支持的字符集有：‘US-ASCII’, ‘ISO-8859-1’, ‘UTF-8’, ‘UTF-16BE’, ‘UTF-16LE’, ‘UTF-16’，如果任一输入参数为NULL都将返回NULLintfind_in_set(string str, string strList)返回str在strlist第一次出现的位置，strlist是用逗号分割的字符串。如果没有找该str字符，则返回0，如果任一参数为NULL将返回NULLstringformat_number(number x, int d)将数值X转换成"#,###,###.##"格式字符串，并保留d位小数，如果d为0，将进行四舍五入且不保留小数stringget_json_object(string json_string, string path)解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。注意此路径上JSON字符串只能由数字字母下划线组成且不能有大写字母和特殊字符，且key不能由数字开头，这是由于Hive对列名的限制booleanin_file(string str, string filename)如果文件名为filename的文件中有一行数据与字符串str匹配成功就返回trueintinstr(string str, string substr)查找字符串str中子字符串substr出现的位置，如果查找失败将返回0，如果任一参数为Null将返回null，注意位置为从1开始的intlength(string A)返回字符串A的长度int****locate(string substr, string str[, int pos])查找字符串str中的pos位置后字符串substr第一次出现的位置stringlower(string A) lcase(string A)返回字符串A的小写格式stringlpad(string str, int len, string pad)将str进行用pad进行左补足到len位,从左边开始对字符串str使用字符串pad填充，最终len长度为止，如果字符串str本身长度比len大的话，将去掉多余的部分stringltrim(string A)**去掉字符串A前面的空格array<struct<string,double>>**ngrams(array, int N, int K, int pf)返回出现次数TOP K的的子序列,n表示子序列的长度stringparse_url(string urlString, string partToExtract [, stringkeyToExtract])返回从URL中抽取指定部分的内容，参数url是URL字符串，而参数partToExtract是要抽取的部分，这个参数包含(HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFOstringprintf(String format, Obj… args)按照printf风格格式输出字符串stringregexp_extract(string subject, string pattern, int index)将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。注意些预定义字符的使用，如第二个参数如果使用’\s’将被匹配到s,’\s’才是匹配空格stringregexp_replace(string A, string B, string C)按照Java正则表达式PATTERN将字符串INTIAL_STRING中符合条件的部分成REPLACEMENT所指定的字符串，如里REPLACEMENT这空的话，抽符合正则的部分将被去掉stringrepeat(string str, int n)返回重复n次后的str字符串stringreverse(string A)返回字符串A的反转结果stringrpad(string str, int len, string pad)从右边开始对字符串str使用字符串pad填充，最终len长度为止，如果字符串str本身长度比len大的话，将去掉多余的部分stringrtrim(string A)去除字符串右边的空格arraysentences(string str, string lang, string locale)字符串str将被转换成单词数组，如：sentences(‘Hello there! How are you?’) =( (“Hello”, “there”), (“How”, “are”, “you”) )stringspace(int n)返回n个空格arraysplit(string str, string pat)**按照pat字符串分割str，会返回分割后的字符串数组map<string,string>**str_to_map(text[, delimiter1, delimiter2])将字符串str按照指定分隔符转换成Map，第一个参数是需要转换字符串，第二个参数是键值对之间的分隔符，默认为逗号;第三个参数是键值之间的分隔符，默认为"="stringsubstr(string A, int start),substring(string A, int start)返回字符串A从start位置到结尾的字符串stringsubstr(string A, int start, int len),substring(string A, int start, int len)返回字符串A从start位置开始，长度为len的字符串stringsubstring_index(string A, string delim, int count)截取第count分隔符之前的字符串，如count为正则从左边开始截取，如果为负则从右边开始截取stringtranslate(string|char|varchar input, string|char|varchar from,string|char|varchar to)将input出现在from中的字符串替换成to中的字符串stringtrim(string A)去除字符串两边的空格binaryunbase64(string str)将64位的字符串转换二进制值stringupper(string A) ucase(string A)将字符串A中的字母转换成大写字母stringinitcap(string A)将字符串A转换第一个字母大写其余字母的字符串intlevenshtein(string A, string B)计算两个字符串之间的差异大小stringsoundex(string A)**将普通字符串转换成soundex字符串

4、聚合函数

返回值语法结构功能描述bigint*count(), count(expr), count(DISTINCT expr[, expr…])count(*)统计检索出的行的个数，包括NULL值的行；count(expr)返回指定字段的非空值的个数；doublesum(col), sum(DISTINCT col)sum(col)统计结果集中col的相加的结果；sum(DISTINCT col)统计结果中col不同值相加的结果doubleavg(col), avg(DISTINCT col)avg(col)统计结果集中col的平均值；avg(DISTINCT col)统计结果中col不同值相加的平均值doublemin(col)统计结果集中col字段的最小值doublemax(col)统计结果集中col字段的最大值doublevariance(col), var_pop(col)统计结果集中col非空集合的总体变量（忽略null），（求指定列数值的方差）doublevar_samp (col)统计结果集中col非空集合的样本变量（忽略null）（求指定列数值的样本方差）doublestddev_pop(col)该函数计算总体标准偏离，并返回总体变量的平方根，其返回值与VAR_POP函数的平方根相同（求指定列数值的标准偏差）doublestddev_samp (col)该函数计算样本标准偏离，（求指定列数值的样本标准偏差）doublecovar_pop(col1, col2)求指定列数值的协方差doublecovar_samp(col1, col2)求指定列数值的样本协方差doublecorr(col1, col2)返回两列数值的相关系数doublepercentile(BIGINT col, p)求准确的第pth个百分位数，p必须介于0和1之间，但是col字段目前只支持整数，不支持浮点数类型arraypercentile(BIGINT col, array(p1 [, p2]…))功能和上述类似，之后后面可以输入多个百分位数，返回类型也为array，其中为对应的百分位数doublepercentile_approx(DOUBLE col, p [, B])求近似的第pth个百分位数，p必须介于0和1之间，返回类型为double，但是col字段支持浮点类型。参数B控制内存消耗的近似精度，B越大，结果的准确度越高。默认为10,000。当col字段中的distinct值的个数小于B时，结果为准确的百分位数arraypercentile_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类型的长度intsize(Array)求数组的长度arraymap_keys(Map<K.V>)返回map中的所有keyarraymap_keys(Map<K.V>)返回map中的所有valuebooleanarray_contains(Array, value)如该数组Array包含value返回true。，否则返回falsearraysort_array(Array)**按自然顺序对数组进行排序并返回

6、条件函数

返回值语法结构功能描述T*if(boolean testCondition, T valueTrue, T valueFalseOrNull)当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull（valueTrue，valueFalseOrNull为泛型）Tnvl(T value, T default_value)如果value值为NULL就返回default_value,否则返回valueTCOALESCE(T v1, T v2,…)返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULLTCASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回fT**CASE WHEN a THEN b [WHEN c THEN d] [ELSE e] END如果a为TRUE,则返回b；如果c为TRUE，则返回d；否则返回ebooleanisnull( a )如果a为null就返回true，否则返回falsebooleanisnotnull ( a )**如果a为非null就返回true，否则返回false

7、表生成函数

返回值语法结构功能描述Array Type**explode(array a)对于a中的每个元素，将生成一行且包含该元素N rowsexplode(ARRAY)每行对应数组中的一个元素N rowsexplode(MAP)每行对应每个map键-值，其中一个字段是map的键，另一个字段是map的值N rowsposexplode(ARRAY)与explode类似，不同的是还返回各元素在数组中的位置N rowsstack(INT n, v_1, v_2, …, v_k)把M列转换成N行，每行有M/N个字段，其中n必须是个常数tuplejson_tuple(jsonStr, k1, k2, …)从一个JSON字符串中获取多个键并作为一个元组返回，与get_json_object不同的是此函数能一次获取多个键值tupleparse_url_tuple(url, p1, p2, …)返回从URL中抽取指定N部分的内容，参数url是URL字符串，而参数p1,p2,…是要抽取的部分，这个参数包含HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY:tupleinline(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(partitionby field1,...)#粒度全表over()#所有分区数据over(partitionby field1,...)#从当前分区的首行到当前行over(partitionby field1,...orderby field_a,...rowsbetween...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的自增列

insertintotable User_Attribute select(row_number()over())+1000as 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
GROUPBY 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 
groupby 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 
groupby device_id;

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

#grouping sets语句select 
    device_id,
    os_id,app_id,count(user_id)from test_xinyan_reg 
groupby 
    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 
groupby device_id,os_id;

3.多维度聚合

#grouping sets语句select 
    device_id,
    os_id,app_id,count(user_id)from test_xinyan_reg 
groupby 
    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 
groupby device_id,os_id 
UNIONALLSELECT 
    device_id,null,null,count(user_id)FROM test_xinyan_reg 
groupby device_id;

4.全维度聚合

#grouping sets语句select
    device_id,
    os_id,app_id,count(user_id)from test_xinyan_reg 
groupby
    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 
groupby device_id 
UNIONALLSELECTnull,
    os_id,null,count(user_id)FROM test_xinyan_reg 
groupby 
    os_id 
UNIONALLSELECT 
    device_id,
    os_id,null,count(user_id)FROM test_xinyan_reg 
groupby
    device_id,
    os_id 
UNIONALLSELECTnull,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
groupby 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
groupby 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
groupby 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 groupby device_id
UNIONALLSELECTnull,os_id,null,null,null,count(user_id)FROM test_xinyan_reg groupby os_id
UNIONALLSELECT device_id,os_id,null,null,null,count(user_id)FROM test_xinyan_reg groupby device_id,os_id
UNIONALLSELECTnull,null,app_id,null,null,count(user_id)FROM test_xinyan_reg groupby app_id
UNIONALLSELECT device_id,null,app_id,null,null,count(user_id)FROM test_xinyan_reg groupby device_id,app_id
UNIONALLSELECTnull,os_id,app_id,null,null,count(user_id)FROM test_xinyan_reg groupby os_id,app_id
UNIONALLSELECT device_id,os_id,app_id,null,null,count(user_id)FROM test_xinyan_reg groupby device_id,os_id,app_id
UNIONALLSELECTnull,null,null,client_version,null,count(user_id)FROM test_xinyan_reg groupby client_version
UNIONALLSELECT device_id,null,null,client_version,null,count(user_id)FROM test_xinyan_reg groupby device_id,client_version
UNIONALLSELECTnull,os_id,null,client_version,null,count(user_id)FROM test_xinyan_reg groupby os_id,client_version
UNIONALLSELECT device_id,os_id,null,client_version,null,count(user_id)FROM test_xinyan_reg groupby device_id,os_id,client_version
UNIONALLSELECTnull,null,app_id,client_version,null,count(user_id)FROM test_xinyan_reg groupby app_id,client_version
UNIONALLSELECT device_id,null,app_id,client_version,null,count(user_id)FROM test_xinyan_reg groupby device_id,app_id,client_version
UNIONALLSELECTnull,os_id,app_id,client_version,null,count(user_id)FROM test_xinyan_reg groupby os_id,app_id,client_version
UNIONALLSELECT device_id,os_id,app_id,client_version,null,count(user_id)FROM test_xinyan_reg groupby device_id,os_id,app_id,client_version
UNIONALLSELECTnull,null,null,null,from_id ,count(user_id)FROM test_xinyan_reg groupby from_id
UNIONALLSELECT device_id,null,null,null,from_id ,count(user_id)FROM test_xinyan_reg groupby device_id,from_id
UNIONALLSELECTnull,os_id,null,null,from_id ,count(user_id)FROM test_xinyan_reg groupby os_id,from_id
UNIONALLSELECT device_id,os_id,null,null,from_id ,count(user_id)FROM test_xinyan_reg groupby device_id,os_id,from_id
UNIONALLSELECTnull,null,app_id,null,from_id ,count(user_id)FROM test_xinyan_reg groupby app_id,from_id
UNIONALLSELECT device_id,null,app_id,null,from_id ,count(user_id)FROM test_xinyan_reg groupby device_id,app_id,from_id
UNIONALLSELECTnull,os_id,app_id,null,from_id ,count(user_id)FROM test_xinyan_reg groupby os_id,app_id,from_id
UNIONALLSELECT device_id,os_id,app_id,null,from_id ,count(user_id)FROM test_xinyan_reg groupby device_id,os_id,app_id,from_id
UNIONALLSELECTnull,null,null,client_version,from_id ,count(user_id)FROM test_xinyan_reg groupby client_version,from_id
UNIONALLSELECT device_id,null,null,client_version,from_id ,count(user_id)FROM test_xinyan_reg groupby device_id,client_version,from_id
UNIONALLSELECTnull,os_id,null,client_version,from_id ,count(user_id)FROM test_xinyan_reg groupby os_id,client_version,from_id
UNIONALLSELECT device_id,os_id,null,client_version,from_id ,count(user_id)FROM test_xinyan_reg groupby device_id,os_id,client_version,from_id
UNIONALLSELECTnull,null,app_id,client_version,from_id ,count(user_id)FROM test_xinyan_reg groupby app_id,client_version,from_id
UNIONALLSELECT device_id,null,app_id,client_version,from_id ,count(user_id)FROM test_xinyan_reg groupby device_id,app_id,client_version,from_id
UNIONALLSELECTnull,os_id,app_id,client_version,from_id ,count(user_id)FROM test_xinyan_reg groupby os_id,app_id,client_version,from_id
UNIONALLSELECT device_id,os_id,app_id,client_version,from_id ,count(user_id)FROM test_xinyan_reg groupby device_id,os_id,app_id,client_version,from_id
UNIONALLSELECTnull,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、星座模型

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

标签： hive 大数据 hadoop

本文转载自: https://blog.csdn.net/m0_43405302/article/details/123293462
版权归原作者 绝域时空 所有，如有侵权，请联系我们删除。

大数据组件之Hive（Hive学习一篇就够了）

文章目录

一、Hive安装

1、解压环境

2、环境变量配置

3、配置文件信息

1.打开编辑文件

2.输入以下内容

4、拷贝mysql驱动

5、更新guava包和hadoop一致

6、mysql授权

7、初始化

8、hive启动模式

9、Hadoop的core-site.xml配置

二、Hive

1、Hive的文件结构

2、MySQL上Hive的元数据

3、hadoop文件授权

4、Hive的驱动器：Driver

5、抽象语法树（AST）

6、动态分区

三、Hive的数据类型

1、Hive基本数据类型

2、非常规数据类型

1.数组类型

2.图类型

3.结构体类型

4.集合类型

四、Hive的DDL

1、 数据库的基本操作

1.创建数据库

2.显示数据库信息

3.显示数据库详细信息

4.切换数据库

5.修改数据库

6.删除数据库

2、创建表

1.建表语句

1.建表语句

2.创建简单表

3.创建外部表

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

5.从已有表复制新表

6.复制表结构

7.指定路径创建数据表

8.创建分区表

9.创建分桶表

3、查看表

1.查看数据表

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

3.查看表分区

4.查看详细建表语句

4、修改表

1.重命名表

2.修改字段定义

3.修改分区信息

4.修改分区

5.删除表分区

6.更新列

7.增加和替换列

5、删除表

1.删除表

2.清空表

五、Hive的DML

1、数据导入方式

2、数据导入

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

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

3.通过insert插入

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

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

6.向分桶表插入数据

2、数据导出

1.使用insert将数据导出

2.带格式导出

3.bash命令导出

4.将数据表导出到HDFS

5.从导出结果导入到Hive

3、数据删除

六、Hive的DQL

1、数据库的基本操作