写出hive建表的完整语句
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]
1、hive的定义
hive是基于hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类sql查询功能。
2、hive的本质
本质是：将hql转化成mapreduce程序
3、hive的架构
用户接口：client
CLI（command-line interface）、JDBC/ODBC(jdbc访问hive)、WEBUI（浏览器访问hive）
元数据：metastore
元数据包括：表名、表所属的数据库（默认是default）、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等；
默认存储在自带的derby数据库中，推荐使用MySQL存储Metastore
hadoop
使用hdfs进行存储，
使用mapreduce进行计算。
使用yarn进行资源调度
驱动器：driver 解析器 编译器 优化器 执行器
4、hive的优缺点
优点
1操作接口采用类sql语法，提供快速开发的能力（简单、容易上手）。
2避免了去写mapreduce，减少开发人员的学习成本。
3 hive的执行延迟比较高，因此hive常用于数据分析，对实时性要求不高的场合。（特性）
4 hive优势在于处理大数据，对于处理小数据没有优势，因为hive的执行延迟比较高。
5 hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。
缺点
1 hive的hql表达能力有限
（1）迭代式算法无法表达
（2）数据挖掘方面不擅长，由于mapreduce数据处理流程的限制，效率更高的算法却无法实现。
2 hive的效率比较低
（1）hive自动生成的mapreduce作业，通常情况下不够智能化
（2）hive调优比较困难，粒度较粗
5、hive和数据库比较
1 查询语言不同：hive采用的是类sql语言称为hql
2数据存储位置不同：hive是将数据存储在hdfs上，而数据库则是将数据存储在本地或者块设 备上
3数据更新：hive读多写少且不建议对数据进行修改
数据库则是需要经常对数据进行修改
4执行引擎：hive中大部分查询是通过mapreduce来实现，数据库是用自己的执行引擎
5执行延迟：因为hive没有索引所以查询数据时需要扫描整个表，因为mapreduce框架，所以 导致延迟高
数据库在处理数据量小的情况下执行延迟低。
6可扩展性：因为hive基于hadoop，所以hive的可扩展性与hadoop一样。
数据库由于acid语义的严格限制，可扩展性非常有限
7数据规模：hive建立在集群上，可以用mapreduce进行并行计算，所以支持大规模的数据
而数据库可以支持的数据规模较小
6、hive的库、表和数据在hdfs上的形式；
库在hdfs上是以.db结尾的目录的形式存在
表在hdfs上是以库的子目录的形式存在
数据存在于表（内部表）的目录下，以文件的形式存在
7、hive默认数据库，hive表中数据默认分隔符
元数据储存在derby数据库中。 hive默认数据库 为 default;
建表默认分隔符：
表字段分隔符：默认是八进制'\u001' ctrl + v ctrl + a
集合元素分隔符： '\u002' ctrl + v ctrl + b
map kv 分隔符 '\u003' ctrl + v ctrl + c.
8、hive默认元数据存放位置，配置后元数据存放位置
默认存储在自带的derby 数据库中，推荐使用mysql存储metastore
9、hive的三种访问方式
CLI（命令行访问hive）
JDBC/ODBC(jdbc访问hive)
WEBUI（浏览器访问hive
10、hive的几种交互命令
①hive -d 等价于 hive --define 进入hive时定义一个参数的值
②hive --database 进入hive时指定使用的数据库
③hive -e 不进入hive命令行执行一个hql语句
④hive -f 不进入hive命令行执行一个hql脚本
⑤hive --hiveconf 配置hive的参数
⑥hive --hivevar 相当于hive -d
⑦hive -i 初始化一个hql脚本(执行完一个hql脚本后进入hive命令行)
⑧hive -S 等价于 hive --silent 静音模式
⑨hive -v 等价于 hive --verbose 冗余模式，将执行的hql语句打印一遍
11、hive的三个级别参数配置方式
配置文件方式 <命令行参数方式 <参数声明方式
配置文件方式:
默认配置文件：hive-default.xml
用户自定义配置文件：hive-site.xml
命令行参数方式
启动Hive时，可以在命令行添加--hiveconf param=value来设定参数。仅对本次hive启动有效
参数声明方式
可以在HQL中使用SET关键字设定参数 仅对本次hive启动有效
12、hive的基本数据类型和集合数据类型有哪些
基本数据类型:tinyint smallint int bigint boolean float double string timestamp
集合数据类型：struct map array
13、hive数据类型之间隐式转换规则，强制类型转换cast的用法
（1）任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。
(2)TINYINT、SMALLINT、INT都可以转换为FLOAT。
（3）所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。
（4）BOOLEAN类型不可以转换为任何其它的类型。
强制类型转换cast的用法
可以使用cast操作显示进行数据类型转换 cast('1' as int),转换成整数1
执行cast('x' as int)，表达式返回空值 null执行失败
14、库、表、数据的增删改查语法
创建库：create database 库名；
删除库：drop database 库名；
修改库：alter database 库名 set dbproperties('键'='值');
查看库：desc database 库名；
创建表：create table 表名；
删除表：drop table 表名；
修改表：alter table 表名 set tblproperties(‘EXTERNAL’=’true’改为外部表/'EXTERNAL'=’false’修改为内部表);
查看表：select * from 表名；
插入数据：insert into table 表名 partition(分区位置) values(值1),(值2);
删除表中的数据：truncate table 表名；
追加覆盖数据：insert overwrite table 表名 partition(分区位置) select 列名1，列名2 from 表名 where 位置;
查询数据：select * from 表名；
15、数据库的哪些信息可以更改，哪些信息不可更改
用户可以使用alter database命令为某个数据库的dbproperties设置键-值对属性值，来描述这个数据库的属性信息。
数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。即 desc展示的数据是不可更改的.
16、如何删除非空库
drop database 库名 cascade;（卡死k他）
17、内部表和外部表的区别，以及互相转换的语法
内部表：表中的数据属于该表，删除表时，数据会一并删除
外部表：该表并非真正拥有表内的数据，表在删除时，只会删除元数据，表中的数据不会被删除
内部表转换为外部表：alter table t6 set tblproperties('EXTERNAL' = 'true');
外部表转换为内部表：alter table t5 set tblproperties('EXTERNAL' = 'false');
18、什么是静态分区，什么是动态分区。两种分区方式插入数据的语法，动静结合方式插入数据的语法
静态分区是插入时对分区字段指定值，动态分区是插入时对分区字段不指定值
静态
insert into table info1 partition(sex='male') select id,name,year from worktmp;
动态
insert into info2 partition(year) select id,name,sex,year from worktmp;
动静结合
insert into info3 partition(sex='male',year) select id,name,year from worktmp;
19、什么严格/非严格模式
严格模式：所有的分区字段中，至少一个分区字段是静态分区
非严格模式：当所有的分区字段都使用动态分区插入数据时，需要使用非严格模式
20、分区/分桶的意义
分区：在分区表中，使用where关键字过滤指定分区中的数据查询时，可以提高查询效率
分桶： 1方便抽样查询
2两个表join时，如果两个表都是基于连接字段进行了分桶，可以以桶为单位进行join，从而减少Join的次数
21、什么时候使用分区，什么时候使用分桶
1 分区表针对的是按目录细分数据，针对的是数据的存储路径
分桶表针对是按文件进行细分数据 针对的是数据文件。
2分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。
22、分桶的规则是什么
hive的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中
23、动态分区+分桶，静态分区+分桶插入数据的语法
create table info5(
id int, name string, sex string
)
partitioned by(year string)
clustered by(id) into 4 buckets
row format delimited fields terminated by '\t';
insert into table info5 partition(year) select id,name,sex,year from worktmp;
create table info4(
id int, name string, year string
)
partitioned by (sex string)
clustered by(id) into 4 buckets
row format delimited fields terminated by '\t';
insert into table info4 partition(sex='male') select id,name,year from worktmp;
动态分区+分桶插入数据：
insert into table 动态分区表名 partition(分区字段名) select 列名1,列名2,列名3, 内置函数year(列名1) 分区字段名 from 临时表名;
静态分区+分桶插入数据：
insert into table 静态分区表名 partition(分区字段名='分区字段值') select 列名1, 列名2,列名3 from 临时表名 where 内置函数year(列名1) = '分区字段值';
24、创建分区表、分桶表的语法
创建分区表的语法：
create table parTb(name string,age int)
partitioned by(location string)
row format delimited fields terminated by '\t';
创建分桶表的语法：
create table buckTb(
id int,
name string
)
clustered by(id);
sorted by(name desc) into 4 buckets;
25、hive表数据导入的6种方式
导入：
1 load装载
load data [local] inpath '本地路径' [overwrite] into table 表名 [partition (分区字段名=分区字段值,…)];
2 insert into/overwrite方式 inset不支持插入部分字段
基本插入：
插入：insert into table 表名 values(列值1,列值2,列值3),(列值1,列值2,列值3);
覆盖：insert overwrite table 表名values(列值1,列值2,列值3);
基本模式插入（根据从另一张表的查询结果插入）：
insert into table 表名1 select 列名1，列名2 from 表名2;
3多表/多分区联合插入：
从一个表中查询一部分数据放入另一个表/分区，查询另一部分数据放入另一个表/分区内
from 表名1
insert into 表名2
select 列名1，列名2 where 条件
insert into 表名3
select列名1，列名2 where 条件;
4根据查询结果建表并加载数据 as ... select 方式，创建表并添加数据
create table 表名1 as select 列名1，列名2 from 表名2;
5建表时，通过location关键字指定数据在hdfs上的路径（目录）
create table表名(列名1 列类型,列名2 列类型,列名3 列类型) location 'hdfs上的路径';
6使用import命令，将export导出的结果导入到表中（要求表是空表）
import table 表名 from 'hdfs上export导出的结果目录';
26、hive表数据导出的4种方式
1 Insert导出：
insert overwrite [local] directory '本地/hdfs路径' [row format delimited fields terminated by ','] select * from info;
2 hadoop命令 hadoop fs -get 导出到本地
dfs -get /hive/importtable/sex=female/000000_0 /opt/module/datas/123/my.txt
等价于 hadoop fs -get /hive/importtable/sex=female/000000_0 /opt/module/datas/123/my.txt
3 hive的shell命令 hive -f/-e 执行语句或者脚本 >file
hive -e 'select * from info;' > /opt/module/datas/my2.txt
4 export 导出到hdfs上
export table 表名 to '本地路径'
①insert overwrite方式
② hadoop fs -get下载表中的数据文件
③ hive的交互命令查询结果追加/覆盖到本地文件
④ export导出到hdfs
27、hive语句执行的顺序
hive语句的执行顺序：
(1)from
(2)on
(3)join
(4)where
(5)group by
(6)having
(7)select
(8)distinct（迪斯定柯特）
(9)distribute by /cluster by（迪斯追毕又特百）
(10)sort by
(11) order by
(12) limit
(13) union /union all（油n嗯）
28、having和where的用法区别
having与where不同点
（1）where后面不能写聚合函数，而having后面可以使用聚合函数。
（2）having只用于group by分组统计语句。
29、哪些情况下join会产生笛卡尔积
（1）省略连接条件
（2）连接条件无效
（3）所有表中的所有行互相连接
30、order by和sort by 的区别
order by 会对数据进行全局排序,它只在一个reduce中进行所以数据量特别大的时候效率非常低。 ,
sort by 是单独在各自的reduce中进行排序，所以并不能保证全局有序，一般和distribute by
一起执行，而且distribute by 要写在sort by前面。
31、distribute by的作用
控制map 中的输出在 reducer 中是如何进行划分的。使用distribute by 可以保证相同key的记录被划分到一个reduce 中。
32、cluster by 的作用
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是倒叙排序，不能指定排序规则为asc或者desc。
33、join、left join、right join、full join的区别
内连接：只保留能匹配上的数据
左连接，保留左表（写在join左边的表）的所有数据，右表进行匹配，匹配不上的用null填充
右连接，保留右表（写在join右边的表）的所有数据，左表进行匹配，匹配不上的用null填充
满外连接，保留两个表的所有数据，其中一个表匹配不上的都用null填充
34、增加单个分区、增加多个分区语法
alter table 表名 add partition(month='201706') ;
alter table 表名 add partition(month='201705') partition(month='201704');
35、删除单个分区、删除多个分区语法
alter table 表名 drop partition (month='201704');
alter table 表名 drop partition (month='201705'), partition (month='201706');
36、列的更新、增加和替换语法
更新列
alter table table_name change [column] col_old_name col_new_name
column_type [comment col_comment] [first|after column_name]
增加和替换列
alter table table_name add|replace columns (col_name data_type [comment col_comment], ...)
add 是代表新增一字段，字段位置在所有列后面(partition 列前)，replace 则是表示替换表中所有字段。
37、行列转换语法
行转列
CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段
select
concat_ws(',',constellation,blood_type),
concat_ws('|',collect_set(name))
from person_info group by concat_ws(',',constellation,blood_type);
collect_set去重 collect_list不去重
列转行
EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
select movie,newcategory
from movie_info lateral view explode(category) tmp as newcategory;
38、窗口函数支持哪些函数
1、窗口函数
lead 往后数几行
lag 往前数几行
first_value 组内的第一行
last_value 组内的最后一行
2、聚合函数
avg
sum
count
min
max
3、分析函数
RANK 排名 可并列，当排名并列时，会跳号
ROW_NUMBER 排名/行号 不可并列，也不跳号
DENSE_RANK 可并列，当排名并列时，不会跳号
CUME_DIST 小于等于rank值的行数/总行数
PERCENT_RANK rank - 1/总行数-1
NTILE(x) 将所有数据均分为x份
39、窗口函数的语法
over(partition by 分组字段 order by 排序字段 rows between ... and ...)
41、自定义udf函数的步骤
继承udf类 -> 实现evaluate函数 -> 打jar包 -> add jar ‘jar包路径’ -> 创建函数 create function 函数名 as ‘全类名’; -> 删除函数 drop function 函数名
1、创建一个工程
2、导入依赖
3、创建一个继承自udf的类
4、实现父类的evaluate（）方法
该方法中书写业务需求
该方法的返回值类型不能是void，但可以返回null
5、导出jar包
6、把jar包导入linux
7、在hive中把jar包添加进来
add jar /opt/module/datas/hiveudf-1.0-snapshot.jar;
8、创建函数，加temporary（探普瑞）关键字，下次再进入hive，该函数就不可用了
create [temporary] function 函数名 as "主类名的全类名";
9、使用函数
10、删除函数
42、hive支持的存储格式1
textfile、sequencefile、orc（沃克）、Parquet
默认存储格式为textfile，明文存储，生产环境中几乎不用这种格式
textfile和sequencefile是基于行式存储
orc、Parquet是基于列式存储
43、hive的优化方式
1 fetch抓取（凡此）
2 本地模式
3 表的优化
4 设置map及reduce数
5 并行执行
6 严格模式
7 jvm重用
8 推测执行
9 压缩
10 执行计划
扫一扫
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
