网站做那个效果好,网站建设服务合同 律师,编程网站有哪些,国家精品课程建设工作网站1、数据库操作
1.1、创建数据库
create database if not exists myhive;use myhive;1.2、查看数据库详细信息
desc database myhive;数据库本质上就是在HDFS之上的文件夹。 默认数据库的存放路径是HDFS的#xff1a;/user/hive/warehouse内
1.3、创建数据库并指定hdfs存…1、数据库操作
1.1、创建数据库
create database if not exists myhive;use myhive;1.2、查看数据库详细信息
desc database myhive;数据库本质上就是在HDFS之上的文件夹。 默认数据库的存放路径是HDFS的/user/hive/warehouse内
1.3、创建数据库并指定hdfs存储位置
使用location关键字可以指定数据库在HDFS的存储路径。
create database myhive2 location /myhive2;1.4、删除数据库
删除一个空数据库如果数据库下面有数据表那么就会报错
drop database myhive;强制删除数据库包含数据库下面的表一起删除
drop database myhive2 cascade;2、数据库表操作
2.1、表分类
Hive中可以创建的表有好几种类型 分别是
内部表外部表分区表分桶表
2.2、内部表
内部表CREATE TABLE table_name …
未被external关键字修饰的即是内部表 即普通表。 内部表又称管理表,内部表数据存储的位置由hive.metastore.warehouse.dir参数决定默认/user/hive/warehouse删除内部表会直接删除元数据metadata及存储数据因此内部表不适合和其他工具共享数据。
2.2.1、创建内部表
内部表的创建语法就是标准的CREATE TABLE table_name…
创建一个基础的表
create database if not exists myhive;use myhive;create table if not exists stu(id int,name string);insert into stu values (1,zhangsan), (2, wangwu);select * from stu; 查看表的数据存储 在HDFS上查看表的数据存储文件
hdfs dfs -ls /user/hive/warehouse/myhive.db/stuhdfs dfs -cat /user/hive/warehouse/myhive.db/stu/*2.2.2、数据分隔符
可以看到数据在HDFS上也是以明文文件存在的。
奇怪的是 列ID和列NAME好像没有分隔符而是挤在一起的。
这是因为默认的数据分隔符是”\001”是一种特殊字符是ASCII值键盘是打不出来 在某些文本编辑器中是显示为SOH的。
2.2.3、自行指定分隔符
在创建表的时候可以自己决定
create table if not exists stu2(id int ,name string) row format delimited fields terminated by \t;row format delimited fields terminated by ‘\t’表示以\t分隔 2.2.4、其它创建内部表的形式
除了标准的CREATE TABLE table_name的形式创建内部表外 我们还可以通过
CREATE TABLE table_name as基于查询结果建表
create table stu3 as select * from stu2;CREATE TABLE table_name like基于已存在的表结构建表
create table stu4 like stu2;也可以使用DESC FORMATTED table_name查看表类型和详情
DESC FORMATTED stu2;2.2.5、删除内部表
我们是内部表删除后数据本身也不会保留让我们试一试吧。 DROP TABLE table_name删除表
drop table stu2;可以看到stu2文件夹已经不存在了数据被删除了。
2.3、外部表
外部表CREATE EXTERNAL TABLE table_name …LOCATION…
被external关键字修饰的即是外部表 即关联表。
外部表是指表数据可以在任何位置通过LOCATION关键字指定。 数据存储的不同也代表了这个表在理念是并不是Hive内部管理的而是可以随意临时链接到外部数据上的。
所以在删除外部表的时候 仅仅是删除元数据表的信息不会删除数据本身。
内部表和外部表
2.3.1、外部表的创建
外部表创建表被EXTERNAL关键字修饰从概念是被认为并非Hive拥有的表只是临时关联数据去使用。
创建外部表也很简单基于外部表的特性可以总结出 外部表 和 数据 是相互独立的 即
可以先有表然后把数据移动到表指定的LOCATION中也可以先有数据然后创建表通过LOCATION指向数据
在Linux上创建新文件test_external.txt并填入如下内容
先创建外部表然后移动数据 2. 演示先创建外部表然后移动数据到LOCATION目录 首先检查hadoop fs -ls /tmp确认不存在/tmp/test_ext1目录
hadoop fs -ls /tmp创建外部表create external table test_ext1(id int, name string) row format delimited fields terminated by ‘\t’ location ‘/tmp/test_ext1’;
create external table test_ext1(id int, name string) row format delimited fields terminated by \t location /tmp/test_ext1;可以看到目录/tmp/test_ext1被创建
select * from test_ext1空结果无数据
上传数据 hadoop fs -put test_external.txt /tmp/test_ext1/ select * from test_ext1即可看到数据结果
hadoop fs -put test_external.txt /tmp/test_ext1/ select * from test_ext1演示先存在数据后创建外部表
hadoop fs -mkdir /tmp/test_ext2hadoop fs -put test_external.txt /tmp/test_ext2/create external table test_ext2(id int, name string) row format delimited fields terminated by \t location /tmp/test_ext2;select * from test_ext2; 2.3.2、删除外部表
drop table test_ext1;
drop table test_ext2;可以发现在Hive中通过show table表不存在了 但是在HDFS中数据文件依旧保留
2.3.3、内外部表转换
ive可以很简单的通过SQL语句转换内外部表。 查看表类型desc formatted stu;
desc formatted stu;内部表
内部表转外部表
alter table stu set tblproperties(EXTERNALTRUE);外部表转内部表
alter table stu set tblproperties(EXTERNALFALSE);要注意(‘EXTERNAL’‘FALSE’) 或 (‘EXTERNAL’‘TRUE’)为固定写法区分大小写
2.4、数据加载和导出
2.4.1、数据加载 - LOAD语法
我们使用 LOAD 语法从外部将数据加载到Hive内语法如下
LOAD DATA [LOCAL] INPATH filepath [OVERWRITE] INTO TABLE tablename;建表
CREATE TABLE myhive.test_load(dt string comment 时间时分秒, user_id string comment 用户ID, word string comment 搜索词,url string comment 用户访问网址
) comment 搜索引擎日志表 ROW FORMAT DELIMITED FIELDS TERMINATED BY \t;还没有数据 数据加载 从本地加载
load data local inpath /home/hadoop/search_log.txt into table myhive.test_load;数据加载从HDFS上传 注意基于HDFS进行load加载数据源数据文件会消失本质是被移动到表所在的目录中 load data inpath /tmp/search_log.txt overwrite into table myhive.test_load;加上overwrite 关键字。相同的数据不会追加。
2.4.2、数据加载 - INSERT SELECT 语法
除了load加载外部数据外我们也可以通过SQL语句从其它表中加载数据。
语法
将SELECT查询语句的结果插入到其它表中被SELECT查询的表可以是内部表或外部表。
insert into myhive.test_load2 select * from myhive.test_load;2.4.3、两种语法的选择 数据在本地 推荐 load data local加载。 数据在HDFS 如果不保留原始文件 推荐使用LOAD方式直接加载。 如果保留原始文件 推荐使用外部表先关联数据然后通过INSERT SELECT 外部表的形式加载数据。 数据已经在表中 只可以INSERT SELECT。
2.4.4、hive表数据导出 - insert overwrite 方式
将hive表中的数据导出到其他任意目录例如linux本地磁盘例如hdfs例如mysql等等
语法insert overwrite [local] directory ‘path’ select_statement1 FROM from_statement;
将查询的结果导出到本地 - 使用默认列分隔符
insert overwrite local directory /home/hadoop/export1 select * from test_load ;将查询的结果导出到本地 - 指定列分隔符
insert overwrite local directory /home/hadoop/export2 row format delimited fields terminated by \t select * from test_load;将查询的结果导出到HDFS上(不带local关键字)
insert overwrite directory /tmp/export row format delimited fields terminated by \t select * from test_load;2.4.5、hive表数据导出 - hive shell
基本语法hive -f/-e 执行语句或者脚本 file
bin/hive -e select * from myhive.test_load; /home/hadoop/export3/export4.txtbin/hive -f export.sql /home/hadoop/export4/export4.txt2.5、分区表
什么是分区表
可以选择字段作为表分区。分区其实就是HDFS上的不同文件夹。分区表可以极大的提高特定场景下Hive的操作性能。
在大数据中最常用的一种思想就是分治我们可以把大的文件切割划分成一个个的小的文件这样每次操作一个小的文件就会很容易了
同样的道理在hive当中也是支持这种思想的就是我们可以把大的数据按照每天或者每小时进行切分成一个个的小的文件这样去操作小的文件就会容易得多了。
同时Hive也支持多个字段作为分区多分区带有层级关系如图 2.5.1、分区表的使用
基本语法
create table tablename(...) partitioned by (分区列 列类型, ......)
row format delimited fields terminated by ;创建一个分区表
-- 创建一个分区表按照月分区
create table myhive.score(name string,course string,score int
) partitioned by (month string)
row format delimited fields terminated by \t;加载数据到分区
load data local inpath /home/hadoop/score.txt into table myhive.score partition(month202309);9月分区 10月分区
2.5.2、查hdfs存储的数据
hdfs dfs -ls /user/hive/warehouse/myhive.db/scorehdfs dfs -cat /user/hive/warehouse/myhive.db/score/month202309/score.txt2.5.3、多分区表
根据年月日分区
create table myhive.score2(name string,course string,score int
) partitioned by (year string,month string,day string)row format delimited fields terminated by \t;-- 加载数据到分区表中
load data local inpath /home/hadoop/score.txtinto table myhive.score2 partition(year2023,month09,day01);load data local inpath /home/hadoop/score.txtinto table myhive.score2 partition(year2023,month09,day02);select * from myhive.score2;2.6、分桶表
分桶和分区一样也是一种通过改变表的存储模式从而完成对表优化的一种调优方式。 但和分区不同分区是将表拆分到不同的子文件夹中进行存储而分桶是将表拆分到固定数量的不同文件中进行存储。
2.5.1、分桶表创建
开启分桶的自动优化自动匹配reduce task数量和桶数量一致
set hive.enforce.bucketingtrue;创建分桶表
create table course (c_id string,c_name string,t_id string) clustered by(c_id) into 3 buckets row format delimited fields terminated by \t;2.5.2、分桶表数据加载
桶表的数据加载由于桶表的数据加载通过load data无法执行只能通过insert select. 所以比较好的方式是
创建一个临时表外部表或内部表均可通过load data加载数据进入表
-- 创建一个普通表
create table course_temp(c_id string,c_name string,t_id string
) row format delimited fields terminated by \t;-- 向普通表加载数据
load data local inpath /home/hadoop/course.txt into table course_temp;select * from course_temp;然后通过insert select 从临时表向桶表插入数据
-- 临时表向course表加载数据
insert overwrite table course select * from course_temp cluster by(c_id);2.5.3、为什么不可以用load data必须用insert select插入数据
如果没有分桶设置插入加载数据只是简单的将数据放入到
表的数据存储文件夹中没有分区表指定分区的文件夹中带有分区 一旦有了分桶设置比如分桶数量为3那么表内文件或分区内数据文件的数量就限定为3 当数据插入的时候需要一分为3进入三个桶文件内。
问题就在于如何将数据分成三份划分的规则是什么
数据的三份划分基于分桶列的值进行hash取模来决定由于load data不会触发MapReduce也就是没有计算过程无法执行Hash算法只是简单的移动数据而已所以无法用于分桶表数据插入。
2.5.4、Hash取模
Hash算法是一种数据加密算法其主要特征 同样的值被Hash加密后的结果是一致的 比如字符串“hadoop”被Hash后的结果是12345仅作为示意那么无论计算多少次字符串“hadoop”的结果都会是12345。 比如字符串“bigdata”被Hash后的结果是56789仅作为示意那么无论计算多少次字符串“bigdata”的结果都会是56789。
基于如上特征在辅以有3个分桶文件的基础上将Hash的结果基于3取模除以3 取余数 那么可以得到如下结果
无论什么数据得到的取模结果均是0、1、2 其中一个同样的数据得到的结果一致如hadoop hash取模结果是1无论计算多少次字符串hadoop的取模结果都是1。
所以必须使用insert select的语法因为会触发MapReduce进行hash取模计算。
2.5.5、ash取模确定数据归属哪个分桶文件
基于Hash取模数据中的每一个分桶列的值都被hash取模得到0、1、2其中一个数 基于结果存入对应序号的桶文件中。
2.5.6、分桶表的性能提升
如果说分区表的性能提升是在指定分区列的前提下减少被操作的数据量从而提升性能。
分桶表的性能提升就是基于分桶列的特定操作如过滤、JOIN、分组均可带来性能提升。 2.7、修改表
2.7.1、表重命名
alter table old_table_name rename to new_table_name;如alter table score4 rename to score5;2.7.2、修改表属性值
ALTER TABLE table_name SET TBLPROPERTIES table_properties; table_properties: : (property_name property_value, property_name property_value, … )
如ALTER TABLE table_name SET TBLPROPERTIES(EXTERNALTRUE); 修改内外部表属性如ALTER TABLE table_name SET TBLPROPERTIES (comment new_comment); 修改表注释2.7.3、添加分区
ALTER TABLE tablename ADD PARTITION (month201101);新分区是空的没数据需要手动添加或上传数据文件
2.7.4、修改分区值
ALTER TABLE tablename PARTITION (month202005) RENAME TO PARTITION (month201105);2.7.5、删除分区
ALTER TABLE tablename DROP PARTITION (month201105);2.7.6、添加列
ALTER TABLE table_name ADD COLUMNS (v1 int, v2 string);2.7.7、修改列名
ALTER TABLE test_change CHANGE v1 v1new INT;2.7.8、删除表
DROP TABLE tablename;2.7.9、清空表
TRUNCATE TABLE tablename;只可以清空内部表
2.8、复杂类型操作
Hive支持的数据类型很多除了基本的int、string、varchar、timestamp等 还有一些复杂的数据类型
array 数组类型map 映射类型struct 结构类型
2.8.1、array类型
如下数据文件有2个列locations列包含多个城市:
说明:name与locations之间制表符分隔locations中元素之间逗号分隔
可以使用array数组类型存储locations的数据
建表语句
create table myhive.test_array(name string, work_locations arraystring)
row format delimited fields terminated by \t
COLLECTION ITEMS TERMINATED BY ,;row format delimited fields terminated by ‘\t’ 表示列分隔符是\tCOLLECTION ITEMS TERMINATED BY ‘,’ 表示集合array元素的分隔符是逗号
导入数据
load data local inpath /home/hadoop/data_for_array_type.txt overwrite into table myhive.test_array;常用array类型查询
-- 查询所有数据
select * from myhive.test_array;
-- 查询loction数组中第一个元素
select name, work_locations[0] location from myhive.test_array;
-- 查询location数组中元素的个数
select name, size(work_locations) location from myhive.test_array;
-- 查询location数组中包含tianjin的信息
select * from myhive.test_array where array_contains(work_locations,tianjin); 2.8.2、Map类型
map类型其实就是简单的指代Key-Value型数据格式。 有如下数据文件其中members字段是key-value型数据
字段与字段分隔符: “,”需要map字段之间的分隔符“#”map内部k-v分隔符“:”
建表语句
create table myhive.test_map(
id int, name string, members mapstring,string, age int
)
row format delimited
fields terminated by ,
COLLECTION ITEMS TERMINATED BY #
MAP KEYS TERMINATED BY :;MAP KEYS TERMINATED BY ‘:’ 表示key-value之间用:分隔 导入数据
load data local inpath /home/hadoop/data_for_map_type.txt overwrite into table myhive.test_map;常用查询
# 查询全部
select * from myhive.test_map;
# 查询father、mother这两个map的key
select id, name, members[father] father, members[mother] mother, age from myhive.test_map;
# 查询全部map的key使用map_keys函数结果是array类型
select id, name, map_keys(members) as relation from myhive.test_map;
# 查询全部map的value使用map_values函数结果是array类型
select id, name, map_values(members) as relation from myhive.test_map;
# 查询map类型的KV对数量
select id,name,size(members) num from myhive.test_map;
# 查询map的key中有brother的数据
select * from myhive.test_map where array_contains(map_keys(members), brother);2.8.3、Struct类型
struct类型是一个复合类型可以在一个列中存入多个子列每个子列允许设置类型和名称
有如下数据文件说明字段之间#分割struct之间冒号分割
建表语句
create table myhive.test_struct(
id string, info structname:string, age:int
)
row format delimited
fields terminated by #
COLLECTION ITEMS TERMINATED BY :;导入数据
load data local inpath /home/hadoop/data_for_struct_type.txt into table myhive.test_struct;常用查询
select * from myhive.test_struct;
-- 直接使用列名.子列名 即可从struct中取出子列查询
select id, info.name from myhive.test_struct;2.9、基本查询
2.9.1、基本语法
查询语句的基本语法
SELECT [ALL | DISTINCT]select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BYcol_list]
[HAVING where_condition]
[ORDER BYcol_list]
[CLUSTER BYcol_list| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]整体上和普通SQL差不多部分有区别如CLUSTER BY、DISTRIBUTE BY、SORT BY等
2.9.2、基本查询
准备数据订单表
CREATE DATABASE it;
USE it;
CREATE TABLE it.orders (orderId bigint COMMENT 订单id,orderNo string COMMENT 订单编号,shopId bigint COMMENT 门店id,userId bigint COMMENT 用户id,orderStatus tinyint COMMENT 订单状态 -3:用户拒收 -2:未付款的订单 -1用户取消 0:待发货 1:配送中 2:用户确认收货,goodsMoney double COMMENT 商品金额,deliverMoney double COMMENT 运费,totalMoney double COMMENT 订单金额包括运费,realTotalMoney double COMMENT 实际订单金额折扣后金额,payType tinyint COMMENT 支付方式,0:未知;1:支付宝2微信;3、现金4、其他,isPay tinyint COMMENT 是否支付 0:未支付 1:已支付,userName string COMMENT 收件人姓名,userAddress string COMMENT 收件人地址,userPhone string COMMENT 收件人电话,createTime timestamp COMMENT 下单时间,payTime timestamp COMMENT 支付时间,totalPayFee int COMMENT 总支付金额
) ROW FORMAT DELIMITED FIELDS TERMINATED BY \t;数据加载
LOAD DATA LOCAL INPATH /home/hadoop/itheima_orders.txt INTO TABLE it.orders;准备数据用户表
CREATE TABLE it.users (userId int,loginName string,loginSecret int,loginPwd string,userSex tinyint,userName string,trueName string,brithday date,userPhoto string,userQQ string,userPhone string,userScore int,userTotalScore int,userFrom tinyint,userMoney double,lockMoney double,createTime timestamp,payPwd string,rechargeMoney double
) ROW FORMAT DELIMITED FIELDS TERMINATED BY \t;数据加载
LOAD DATA LOCAL INPATH /home/hadoop/itheima_users.txt INTO TABLE it.users;查询所有
SELECT * FROM itheima.orders;查询单列
SELECT orderid, totalmoney, username, useraddress, paytime FROM itheima.orders;查询数据量
SELECT COUNT(*) FROM itheima.orders;过滤广东省订单
SELECT * FROM itheima.orders WHERE useraddress LIKE %广东%;找出广东省单笔营业额最大的订单
SELECT * FROM itheima.orders WHERE useraddress like %广东% ORDER BY totalmoney DESC LIMIT 1;2.9.3、分组、聚合
统计未支付、已支付各自的人数
SELECT ispay, COUNT(*) AS cnt FROM itheima.orders GROUP BY ispay;在已付款订单中统计每个用户最高的一笔消费金额
SELECT userid, MAX(totalmoney) AS max_money FROM itheima.orders WHERE ispay 1 GROUP BY userid;统计每个用户的平均订单消费额
SELECT userid, AVG(totalmoney) FROM itheima.orders GROUP BY userid;统计每个用户的平均订单消费额过滤大于10000的数据
SELECT userid, AVG(totalmoney) AS avg_money FROM itheima.orders GROUP BY userid HAVING avg_money 10000;2.9.4、JOIN
JOIN订单表和用户表找出用户名
SELECT o.orderid, o.userid, u.username, o.totalmoney, o.useraddress, o.paytime FROM itheima.orders o JOIN itheima.users u ON o.userid u.userid;左外关联订单表和用户表找出用户名
SELECT o.orderid, o.userid, u.username, o.totalmoney, o.useraddress, o.paytime FROM itheima.orders o LEFT JOIN itheima.users u ON o.userid u.userid;2.10、RLIKE 正则匹配
正则表达式是一种规则集合通过特定的规则字符描述来判断字符串是否符合规则。
RLIKE Hive中提供RLIKE关键字可以供用户使用正则和数据进行匹配。
我们以上面中使用的订单表为例来简单使用一下RLIKE正则匹配。
查找广东省的数据
SELECT * FROM itheima.orders WHERE useraddress RLIKE .*广东.*;查找用户地址是xx省 xx市 xx区的数据
SELECT * FROM itheima.orders WHERE useraddress RLIKE ..省 ..市 ..区;查找用户姓为张、王、邓
SELECT * FROM itheima.orders WHERE username RLIKE [张王邓]\\S;查找手机号符合188*0 规则
SELECT * FROM itheima.orders WHERE userphone RLIKEE 188\\S{4}0\\S{3};2.11、UNION联合
UNION 用于将多个 SELECT 语句的结果组合成单个结果集。
每个 select 语句返回的列的数量和名称必须相同。否则将引发架构错误。
基础语法
SELECT ...UNION [ALL]
SELECT ...准备数据进行测试
创建表
CREATE TABLE it.course(
c_id string,
c_name string,
t_id string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t;加载数据
LOAD DATA LOCAL INPATH /home/hadoop/course.txt INTO TABLE it.course; 联合两个查询结果集
SELECT * FROM course WHERE t_id 周杰轮UNION
SELECT * FROM course WHERE t_id 王力鸿2.11.1、UNION联合 - 去重
UNION默认有去重功能
直接联合两个同样的查询结果
SELECT * FROM course
UNION
SELECT * FROM course如果不需要去重效果
SELECT * FROM courseUNION ALL
SELECT * FROM course2.11.2、其他写法
UNION写在FROM中
SELECT t_id, COUNT(*) FROM
(SELECT t_id FROM itheima.course WHERE t_id 周杰轮UNION ALLSELECT t_id FROM itheima.course WHERE t_id 王力鸿
) AS u GROUP BY t_id;用于INSERT SELECT中
CREATE TABLE it.course2 LIKE it.course;
INSERT OVERWRITE TABLE it.course2SELECT * FROM it.courseUNION ALLSELECT * FROM it.course;2.12、Sampling采样
2.12.1、为什么需要抽样表数据
对表进行随机抽样是非常有必要的。
大数据体系下在真正的企业环境中很容易出现很大的表比如体积达到TB级别。 对这种表一个简单的SELECT * 都会非常的慢哪怕LIMIT 10想要看10条数据也会走MapReduce流程这个时间等待是不合适的。
Hive提供的快速抽样的语法可以快速从大表中随机抽取一些数据供用户查看。
2.12.2、TABLESAMPLE函数
进行随机抽样本质上就是用TABLESAMPLE函数
语法1基于随机分桶抽样
SELECT ... FROM tbl TABLESAMPLE(BUCKET x OUT OF y ON(colname | rand()))y表示将表数据随机划分成y份y个桶x表示从y里面随机抽取x份数据作为取样colname表示随机的依据基于某个列的值rand()表示随机的依据基于整行
示例
SELECT username, orderId, totalmoney FROM itheima.orders TABLESAMPLE(BUCKET 1 OUT OF 10 ON username);
SELECT * FROM itheima.orders TABLESAMPLE(BUCKET 1 OUT OF 10 ON rand());注意
使用colname作为随机依据则其它条件不变下每次抽样结果一致。使用rand()作为随机依据每次抽样结果都不同。
语法2基于数据块抽样
SELECT ... FROM tbl TABLESAMPLE(num ROWS | num PERCENT | num(K|M|G));num ROWS 表示抽样num条数据。num PERCENT 表示抽样num百分百比例的数据。num(K|M|G) 表示抽取num大小的数据单位可以是K、M、G表示KB、MB、GB。
注意
使用这种语法抽样条件不变的话每一次抽样的结果都一致。即无法做到随机只是按照数据顺序从前向后取。
2.13、Virtual Columns 虚拟列
2.13.1、Virtual Columns虚拟列
虚拟列是Hive内置的可以在查询语句中使用的特殊标记可以查询数据本身的详细参数。
Hive目前可用3个虚拟列
INPUT__FILE__NAME显示数据行所在的具体文件BLOCK__OFFSET__INSIDE__FILE显示数据行所在文件的偏移量ROW__OFFSET__INSIDE__BLOCK显示数据所在HDFS块的偏移量 此虚拟列需要设置SET hive.exec.rowoffsettrue 才可使用
示例
SELECT *, INPUT__FILE__NAME, BLOCK__OFFSET__INSIDE__FILE, ROW__OFFSET__INSIDE__BLOCK FROM it.course;2.13.2、虚拟列的作用
使用虚拟列可以让我们更精准的查看到具体每一条数据在存储上的详细参数细节。
虚拟列不仅仅可以用于SELECT在WHERE、GROUP BY等均可使用。 如
SELECT *, BLOCK__OFFSET__INSIDE__FILE FROM course WHERE BLOCK__OFFSET__INSIDE__FILE 50;SELECT INPUT__FILE__NAME, COUNT(*) FROM it.orders GROUP BY INPUT__FILE__NAME;除此以外在某些错误排查场景上虚拟列可以提供相关帮助。
3、函数
官方文档(https://cwiki.apache.org/confluence/display/Hive/LanguageManualUDF#LanguageManualUDF-MathematicalFunctions)
3.1、分类标准
Hive的函数分为两大类内置函数Built-in Functions、用户定义函数UDFUser-Defined Functions
3.2、查看函数列表
Hive内建了不少函数
使用show functions查看当下可用的所有函数
通过describe function extended funcname来查看函数的使用方式。
3.3、Mathematical Functions 数学函数
----Mathematical Functions 数学函数-------------
--取整函数: round 返回double类型的整数值部分 遵循四舍五入
select round(3.1415926);--指定精度取整函数: round(double a, int d) 返回指定精度d的double类型
select round(3.1415926,4);--取随机数函数: rand 每次执行都不一样 返回一个0到1范围内的随机数
select rand();--指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列
select rand(3);--求数字的绝对值
select abs(-3);--得到pi值小数点后15位精度
select pi();3.4、Collection Functions集合函数 3.5、Type Conversion Functions类型转换函数 3.6、Date Functions日期函数 3.7、Conditional Functions条件函数 3.8、String Functions字符串函数 3.9、Data Masking Functions数据脱敏函数 3.10、Misc. Functions其它函数 4、案例
4.1、需求分析
4.1.1、背景介绍
聊天平台每天都会有大量的用户在线会出现大量的聊天数据通过对聊天数据的统计分析可以更好的对用户构建精准的用户画像为用户提供更好的服务以及实现高ROI的平台运营推广给公司的发展决策提供精确的数据支撑。
我们将基于一个社交平台App的用户数据完成相关指标的统计分析并结合BI工具对指标进行可视化展现。
4.1.2、目标
基于Hadoop和Hive实现聊天数据统计分析构建聊天数据分析报表。
4.1.3、需求
统计今日总消息量统计今日每小时消息量、发送和接收用户数统计今日各地区发送消息数据量统计今日发送消息和接收消息的用户数统计今日发送消息最多的Top10用户统计今日接收消息最多的Top10用户统计发送人的手机型号分布情况统计发送人的设备操作系统分布情况
4.1.4、数据内容
数据大小30万条数据列分隔符Hive默认分隔符’\001’数据字典及样例数据
4.1.5、建库建表
–如果数据库已存在就删除
drop database if exists db_msg cascade ;–创建数据库
create database db_msg ;–切换数据库
use db_msg ;–列举数据库
show databases ;--如果表已存在就删除
drop table if exists db_msg.tb_msg_source ;--建表
create table db_msg.tb_msg_source(msg_time string comment 消息发送时间,sender_name string comment 发送人昵称,sender_account string comment 发送人账号,sender_sex string comment 发送人性别,sender_ip string comment 发送人ip地址,sender_os string comment 发送人操作系统,sender_phonetype string comment 发送人手机型号,sender_network string comment 发送人网络类型,sender_gps string comment 发送人的GPS定位,receiver_name string comment 接收人昵称,receiver_ip string comment 接收人IP,receiver_account string comment 接收人账号,receiver_os string comment 接收人操作系统,receiver_phonetype string comment 接收人手机型号,receiver_network string comment 接收人网络类型,receiver_gps string comment 接收人的GPS定位,receiver_sex string comment 接收人性别,msg_type string comment 消息类型,distance string comment 双方距离,message string comment 消息内容
);4.1.6、加载数据 上传文件到Linux系统 load数据到表
load data local inpath /home/hadoop/chat_data-30W.csv overwrite into table tb_msg_source;验证结果
select msg_time, sender_name, sender_ip, sender_phonetype, receiver_name, receiver_network
from tb_msg_source limit 10;4.2、ETL数据清洗
4.2.1、数据清洗
数据问题
问题1当前数据中有一些数据的字段为空不是合法数据
select msg_time, sender_name, sender_gps from
db_msg.tb_msg_source where length(sender_gps) 0 limit 10;问题2需求中需要统计每天、每个小时的消息量但是数据中没有天和小时字段只有整体时间字段不好处理
select msg_time from db_msg.tb_msg_source limit 10;问题3需求中需要对经度和维度构建地区的可视化地图但是数据中GPS经纬度为一个字段不好处理
select sender_gps from db_msg.tb_msg_source limit 10;4.2.2、需求
需求1对字段为空的不合法数据进行过滤 where过滤需求2通过时间字段构建天和小时字段 date hour函数需求3从GPS的经纬度中提取经度和维度 split函数需求4将ETL以后的结果保存到一张新的Hive表中
create table db_msg.tb_msg_etl(msg_time string comment 消息发送时间,sender_name string comment 发送人昵称,sender_account string comment 发送人账号,sender_sex string comment 发送人性别,sender_ip string comment 发送人ip地址,sender_os string comment 发送人操作系统,sender_phonetype string comment 发送人手机型号,sender_network string comment 发送人网络类型,sender_gps string comment 发送人的GPS定位,receiver_name string comment 接收人昵称,receiver_ip string comment 接收人IP,receiver_account string comment 接收人账号,receiver_os string comment 接收人操作系统,receiver_phonetype string comment 接收人手机型号,receiver_network string comment 接收人网络类型,receiver_gps string comment 接收人的GPS定位,receiver_sex string comment 接收人性别,msg_type string comment 消息类型,distance string comment 双方距离,message string comment 消息内容,msg_day string comment 消息日,msg_hour string comment 消息小时,sender_lng double comment 经度,sender_lat double comment 纬度
);4.2.3、ETL数据清洗
实现
INSERT OVERWRITE TABLE db_msg.tb_msg_etl
SELECT*,day(msg_time) as msg_day,HOUR(msg_time) as msg_hour,split(sender_gps, ,)[0] AS sender_lng,split(sender_gps, ,)[1] AS sender_lat
FROM tb_msg_source WHERE LENGTH(sender_gps) 0;查看结果
select msg_time, msy_day, msg_hour, sender_gps, sender_lng, sender_latfrom db_msg.tb_msg_etllimit 10;4.2.4、扩展概念ETL
其实我们刚刚完成了 从表tb_msg_source 查询数据进行数据过滤和转换并将结果写入到tb_msg_etl表中的操作。
这种操作本质上是一种简单的ETL行为。
ETL
EExtract抽取TTransform转换LLoad加载 从A抽取数据(E)进行数据转换过滤(T)将结果加载到B(L)就是ETL啦。
ETL在大数据系统中是非常常见的。
4.3、指标计算
指标1统计今日消息总量
--保存结果表
CREATE TABLE IF NOT EXISTS tb_rs_total_msg_cnt
COMMENT 每日消息总量 AS
SELECT msg_day, COUNT(*) AS total_msg_cnt
FROM db_msg.tb_msg_etl
GROUP BY msg_day;指标2统计每小时消息量、发送和接收用户数
--保存结果表
CREATE TABLE IF NOT EXISTS tb_rs_hour_msg_cnt
COMMENT 每小时消息量趋势 AS
SELECT msg_hour, COUNT(*) AS total_msg_cnt, COUNT(DISTINCT sender_account) AS sender_usr_cnt, COUNT(DISTINCT receiver_account) AS receiver_usr_cnt
FROM db_msg.tb_msg_etl GROUP BY msg_hour;指标3统计今日各地区发送消息总量
CREATE TABLE IF NOT EXISTS tb_rs_loc_cnt
COMMENT 今日各地区发送消息总量 AS
SELECT msg_day, sender_lng, sender_lat, COUNT(*) AS total_msg_cnt
FROM db_msg.tb_msg_etl
GROUP BY msg_day, sender_lng, sender_lat;指标4统计今日发送和接收用户人数
--保存结果表
CREATE TABLE IF NOT EXISTS tb_rs_usr_cnt
COMMENT 今日发送消息人数、接受消息人数 AS
SELECT
msg_day,
COUNT(DISTINCT sender_account) AS sender_usr_cnt,
COUNT(DISTINCT receiver_account) AS receiver_usr_cnt
FROM db_msg.tb_msg_etl
GROUP BY msg_day;指标5统计发送消息条数最多的Top10用户
--保存结果表
CREATE TABLE IF NOT EXISTS db_msg.tb_rs_s_user_top10
COMMENT 发送消息条数最多的Top10用户 AS
SELECT sender_name AS username, COUNT(*) AS sender_msg_cnt
FROM db_msg.tb_msg_etl
GROUP BY sender_name
ORDER BY sender_msg_cnt DESC
LIMIT 10;指标6统计接收消息条数最多的Top10用户
CREATE TABLE IF NOT EXISTS db_msg.tb_rs_r_user_top10
COMMENT 接收消息条数最多的Top10用户 AS
SELECT
receiver_name AS username,
COUNT(*) AS receiver_msg_cnt
FROM db_msg.tb_msg_etl
GROUP BY receiver_name
ORDER BY receiver_msg_cnt DESC
LIMIT 10;指标7统计发送人的手机型号分布情况
CREATE TABLE IF NOT EXISTS db_msg.tb_rs_sender_phone
COMMENT 发送人的手机型号分布 AS
SELECT sender_phonetype, COUNT(sender_account) AS cnt
FROM db_msg.tb_msg_etl
GROUP BY sender_phonetype;指标8统计发送人的手机操作系统分布
--保存结果表
CREATE TABLE IF NOT EXISTS db_msg.tb_rs_sender_os
COMMENT 发送人的OS分布 AS
SELECTsender_os, COUNT(sender_account) AS cnt
FROM db_msg.tb_msg_etl
GROUP BY sender_os;4.4、fineBI安装与配置
4.4.1、BI概述
BIBusiness Intelligence商业智能。
指用现代数据仓库技术、线上分析处理技术、数据挖掘和数据展现技术进行数据分析以实现商业价值。
简单来说就是借助BI工具可以完成复杂的数据分析、数据统计等需求为公司决策带来巨大的价值。
所以一般提到BI我们指代的就是工具软件。常见的BI软件很多比如
FineBISuperSetPowerBITableAu
4.4.2、FineBI的介绍
FineBI的介绍https://www.finebi.com/
FineBI 是帆软软件有限公司推出的一款商业智能Business Intelligence产品。FineBI 是定位于自助大数据分析的 BI 工具能够帮助企业的业务人员和数据分析师开展以问题导向的探索式分析。
FineBI的特点
通过多人协作来实现最终的可视化构建不需要通过复杂代码来实现开发通过可视化操作实现开发适合于各种数据可视化的应用场景支持各种常见的分析图表和各种数据源支持处理大数据
4.4.3、FineBI的安装 下载https://www.finebi.com/ 注册拿到激活码 安装软件运行激活 运行后会打开一个网页设置用户名密码 自己使用选择内置数据库即可 fineBI首页
4.4.4、fineBI配置hive数据库 fineBI引入hive驱动 安装隔离插件
4.4.5、连接数据库 4.5、FineBI可视化展现
4.5.1、同步数据 4.5.2、显示–今日发送消息人数、接受消息人数 添加仪表板 去掉标题 切换样式 接受消息人数
4.5.3、发送用户总数 4.5.4、发送消息最多的top10折线雷达图 4.5.5、发送用户操作系统占比饼图 4.5.6、各地区人数分布地图 4.5.7、接受消息最多的top10柱状图 4.5.8、发送人的手机型号分布文本 4.5.8、每小时消息趋势多区折线图 结束 hy:43 一个胜利者不会放弃而一个放弃者永远不会胜利。
文章转载自： http://www.morning.ymwrs.cn.gov.cn.ymwrs.cn http://www.morning.bdypl.cn.gov.cn.bdypl.cn http://www.morning.jcrfm.cn.gov.cn.jcrfm.cn http://www.morning.qhkdt.cn.gov.cn.qhkdt.cn http://www.morning.fgsqz.cn.gov.cn.fgsqz.cn http://www.morning.hxwhyjh.com.gov.cn.hxwhyjh.com http://www.morning.wcft.cn.gov.cn.wcft.cn http://www.morning.rzbgn.cn.gov.cn.rzbgn.cn http://www.morning.mhnxs.cn.gov.cn.mhnxs.cn http://www.morning.tsynj.cn.gov.cn.tsynj.cn http://www.morning.jbpodhb.cn.gov.cn.jbpodhb.cn http://www.morning.mxcgf.cn.gov.cn.mxcgf.cn http://www.morning.ysbrz.cn.gov.cn.ysbrz.cn http://www.morning.pxlql.cn.gov.cn.pxlql.cn http://www.morning.jtdrz.cn.gov.cn.jtdrz.cn http://www.morning.wjhnx.cn.gov.cn.wjhnx.cn http://www.morning.ccffs.cn.gov.cn.ccffs.cn http://www.morning.rwls.cn.gov.cn.rwls.cn http://www.morning.mmqhq.cn.gov.cn.mmqhq.cn http://www.morning.zfcfk.cn.gov.cn.zfcfk.cn http://www.morning.qlsbz.cn.gov.cn.qlsbz.cn http://www.morning.pqcsx.cn.gov.cn.pqcsx.cn http://www.morning.nzqmw.cn.gov.cn.nzqmw.cn http://www.morning.fgwzl.cn.gov.cn.fgwzl.cn http://www.morning.fhhry.cn.gov.cn.fhhry.cn http://www.morning.zlnkq.cn.gov.cn.zlnkq.cn http://www.morning.rbmnq.cn.gov.cn.rbmnq.cn http://www.morning.slkqd.cn.gov.cn.slkqd.cn http://www.morning.hmsong.com.gov.cn.hmsong.com http://www.morning.prfrb.cn.gov.cn.prfrb.cn http://www.morning.xcxj.cn.gov.cn.xcxj.cn http://www.morning.ggqcg.cn.gov.cn.ggqcg.cn http://www.morning.lyhry.cn.gov.cn.lyhry.cn http://www.morning.gwmjy.cn.gov.cn.gwmjy.cn http://www.morning.kxnnh.cn.gov.cn.kxnnh.cn http://www.morning.lmmkf.cn.gov.cn.lmmkf.cn http://www.morning.lgnz.cn.gov.cn.lgnz.cn http://www.morning.ysfj.cn.gov.cn.ysfj.cn http://www.morning.kybjr.cn.gov.cn.kybjr.cn http://www.morning.rydbs.cn.gov.cn.rydbs.cn http://www.morning.nkjxn.cn.gov.cn.nkjxn.cn http://www.morning.ydtdn.cn.gov.cn.ydtdn.cn http://www.morning.ffptd.cn.gov.cn.ffptd.cn http://www.morning.kcnjz.cn.gov.cn.kcnjz.cn http://www.morning.gtxrw.cn.gov.cn.gtxrw.cn http://www.morning.gassnw.com.gov.cn.gassnw.com http://www.morning.hqwcd.cn.gov.cn.hqwcd.cn http://www.morning.nsrtvu.com.gov.cn.nsrtvu.com http://www.morning.gqhgl.cn.gov.cn.gqhgl.cn http://www.morning.xwlmr.cn.gov.cn.xwlmr.cn http://www.morning.jwtjf.cn.gov.cn.jwtjf.cn http://www.morning.xmrmk.cn.gov.cn.xmrmk.cn http://www.morning.nwczt.cn.gov.cn.nwczt.cn http://www.morning.mjbjq.cn.gov.cn.mjbjq.cn http://www.morning.ssfq.cn.gov.cn.ssfq.cn http://www.morning.sqqds.cn.gov.cn.sqqds.cn http://www.morning.jqswf.cn.gov.cn.jqswf.cn http://www.morning.kpxky.cn.gov.cn.kpxky.cn http://www.morning.ntgrn.cn.gov.cn.ntgrn.cn http://www.morning.frzdt.cn.gov.cn.frzdt.cn http://www.morning.cykqg.cn.gov.cn.cykqg.cn http://www.morning.nsrtvu.com.gov.cn.nsrtvu.com http://www.morning.jppb.cn.gov.cn.jppb.cn http://www.morning.fkcjs.cn.gov.cn.fkcjs.cn http://www.morning.dkbsq.cn.gov.cn.dkbsq.cn http://www.morning.szoptic.com.gov.cn.szoptic.com http://www.morning.dkmzr.cn.gov.cn.dkmzr.cn http://www.morning.qpntn.cn.gov.cn.qpntn.cn http://www.morning.xrpwk.cn.gov.cn.xrpwk.cn http://www.morning.jftl.cn.gov.cn.jftl.cn http://www.morning.fmswb.cn.gov.cn.fmswb.cn http://www.morning.yfcyh.cn.gov.cn.yfcyh.cn http://www.morning.wwkdh.cn.gov.cn.wwkdh.cn http://www.morning.qrqcr.cn.gov.cn.qrqcr.cn http://www.morning.xqxrm.cn.gov.cn.xqxrm.cn http://www.morning.jfcbs.cn.gov.cn.jfcbs.cn http://www.morning.sxlrg.cn.gov.cn.sxlrg.cn http://www.morning.xdjwh.cn.gov.cn.xdjwh.cn http://www.morning.yjmlg.cn.gov.cn.yjmlg.cn http://www.morning.2d1bl5.cn.gov.cn.2d1bl5.cn
