1 hive 常用命令
假设有数据库 fm_data,里面有表格 shield_fm_feature_item_ctr
show databases; //列出数据库
desc database fm_data; // 展示数据库 fm_data 的信息
use fm_data; // 使用某个数据库 fm_data\
set hive.cli.print.current.db=true; 显示列头
set hive.cli.print.current.db=false; 关闭列头
show tables; // 展示这个数据库里面的所有表格
show tables in fm_data; // 展示数据库 fm_data 里面的所有表格
show tables like '*ctr*'; // 模糊查找
show create table shield_fm_feature_item_ctr; // 获得表格 shield_fm_feature_item_ctr 的建表语句,其中包括表格的字段,HDFS 的 location 等信息
desc shield_fm_feature_item_ctr; // 展示表格 shield_fm_feature_item_ctr 的字段以及字段类型
desc formatted shield_fm_feature_item_ctr; // 详细描述表格 shield_fm_feature_item_ctr,包括表格的结构,所在的 database,owner,location,表格的类型 (Managed Table or External Table),存储信息等
2 内部表与外部表
hive 的表格分两种,一种是 managed tables(内部表),另一种是 external tables(外部表)。hive 创建表格时,默认创建的是 managed table,这种表会把数据移动到自己的数据仓库目录下;另外一种是 external tables,它关联的数据不是 hive 维护的,也不在 hive 的数据仓库内。
创建内部表格和外部表格:
create table test(name string);
create external table test(name string); 创建外部表格需要加上external;
修改表属性:
alter table test set tblproperties (‘EXTERNAL’=’TRUE’); 内部表转外部表
alter table test set tblproperties (‘EXTERNAL’=’FALSE’); 外部表转内部表
归纳一下Hive中表与外部表的区别:
在导入数据到外部表,数据并没有移动到自己的数据仓库目录下(如果指定了location的话),也就是说外部表中的数据并不是由它自己来管理的!而内部表则不一样;
在删除内部表的时候,Hive 将会把属于表的元数据和数据全部删掉;而删除外部表的时候,Hive 仅仅删除外部表的元数据,数据是不会删除的!换言之,内部表 DROP 时会删除 HDFS 上的数据;外部表 DROP 时不会删除 HDFS 上的数据。
在创建内部表或外部表时加上 location 的效果是一样的,只不过表目录的位置不同而已,加上 partition 用法也一样,只不过表目录下会有分区目录而已,load data local inpath 直接把本地文件系统的数据上传到 hdfs 上,有 location 上传到 location 指定的位置上,没有的话上传到 hive 默认配置的数据仓库中。
使用场景:内部表:HIVE 中间表,结果表,一般不需要从外部(如本地文件,HDFS 上 load 数据)的情况;外部表:源表,需要定期将外部数据映射到表格中。
3 创建表格
create table test1 like test; 只是复制了表的结构,并没有复制内容;
create table test2 as select name from test; 从其他表格查询,再创建表格;
创建表的语法选项特别多,这里只列出常用的选项。
其他请参见Hive官方文档:
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTable
举一个例子来说吧:建立一张表 t_zr9558
CREATE EXTERNAL TABLE t_zr9558 (
id INT,
ip STRING COMMENT ‘访问者IP’,
avg_view_depth DECIMAL(5,1),
bounce_rate DECIMAL(6,5)
) COMMENT ‘test.com’
PARTITIONED BY (day STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,’
STORED AS textfile
LOCATION ‘hdfs://cdh5/tmp/zr9558/’;
(1)关键字 EXTERNAL:表示该表为外部表,如果不指定EXTERNAL关键字,则表示内部表
(2)关键字 COMMENT:为表和列添加注释
(3)关键字 PARTITIONED BY:表示该表为分区表,分区字段为day,类型为string
(4)关键字 ROW FORMAT DELIMITED:指定表的分隔符,通常后面要与以下关键字连用:
FIELDS TERMINATED BY ‘,’ //指定每行中字段分隔符为逗号
LINES TERMINATED BY ‘\n’ //指定行分隔符
COLLECTION ITEMS TERMINATED BY ‘,’ //指定集合中元素之间的分隔符
MAP KEYS TERMINATED BY ‘:’ //指定数据中 Map 类型的 Key 与 Value 之间的分隔符
(5)关键字 STORED AS:指定表在 HDFS 上的文件存储格式,可选的文件存储格式有:
TEXTFILE //文本,默认值
SEQUENCEFILE // 二进制序列文件
RCFILE //列式存储格式文件 Hive0.6 以后开始支持
ORC //列式存储格式文件,比 RCFILE 有更高的压缩比和读写效率,Hive0.11以后开始支持
PARQUET //列出存储格式文件,Hive0.13以后开始支持
(6)关键词 LOCATION:指定表在HDFS上的存储位置。
注:hive 建表的时候默认的分隔符是’\001’,如果建表的时候没有指定分隔符,load文件的时候的分隔符是’\001’。如果需要在建表的时候指定分隔符,需要如下操作:
create table pokes(foo int,bar string)
row format delimited fields terminated by ‘\t’ lines terminated by ‘\n’ stored as textfile;
load data local inpath ‘/root/pokes.txt’ into table pokes;
4 修改表格
alter table shield_fm_feature_item_ctr add columns ( reporttime STRING COMMENT ‘上报日期时间’) //为表格增加列
alter table test rename to test2; //修改表名
alter table test add partition (day=20160301); //增加分区
alter table test drop partition (day=20160301); //删除分区
alter table test partition (day=20160301) rename to partition (day=20160302); //修改分区
load data local inpath ‘/liguodong/hivedata/datatest’ overwrite into table test; // 从文件加载数据:覆盖原来数据
load data local inpath ‘/liguodong/hivedata/datatest’ into table test; // 从文件加载数据:添加数据
insert overwrite directory ‘tmp/csl_rule_cfg’ select a.* from test a; // 导出数据到文件
5 查询和分析数据
dfs -ls /user/hive/warehouse/fm_data.db/shield_fm_feature_item_ctr // 查看 hdfs 文件信息
set hive.cli.print.header=true; 显示列名称
set hive.cli.print.header=false; 不显示列名称
(i)基础操作
假设表格 shield_fm_feature_item_ctr 的格式是:owner (string), key (string), value (int), day (bigint);
select * from shield_fm_feature_item_ctr; // 查找数据
select * from shield_fm_feature_item_ctr limit 10; // 查找10行数据
select * from shield_fm_feature_item_ctr where day=20160301; //查询 day=20160301 的数据
select * from shield_fm_feature_item_ctr where day >= 20160301 and day<=20160302; //查询 day>=20160301 并且 day<=20160302 的数据
select * from shield_fm_feature_item_ctr where day = 20160301 or day =20160302; //查询 day=20160301 或者 day=20160302 的数据
select * from shield_fm_feature_item_ctr where day=20160301 order by value; // 按照value 的值增序排列
select * from shield_fm_feature_item_ctr where day=20160301 order by value desc; // 按照 value 的值降序排列
insert [overwrite] into table shield_fm_feature_item_ctr partition (day=20160301) values (‘20032′,’key_20032’,1.0) // 不使用overwrite是往表格里追加一条数据,如果使用overwrite就是覆盖整个表格。
(ii)高级操作
select * from shield_fm_feature_item_ctr where day between 20160301 and 20160302; //查询表格中从20160301到20160302的数据
JOIN 操作:非常重要的概念
inner join: 在表格中至少存在一个匹配时,inner join 的关键字返回行;注:inner join 和 join 是相同的。
left join: 会从左边的表格返回所有的行,即使在右边的表格中没有匹配的行。
right join:会从右边的表格返回所有的行,即使在左边的表格中没有匹配的行。
full join:只要其中的一张表存在匹配,full join 就会返回行。在某些数据库中,full join 也称作 full outer join。
union:用于合并两个或者多个 select 语句的结果集。
is NULL & is not NULL:来判断某个字段是否是空集。
(iii)聚合函数
group by:通常和聚合函数一起使用,根据一个或者多个列对结果进行分组
常见的聚合函数有:
AVG:返回数列值的平均值
COUNT:返回一列值的数目
MAX/MIN:返回一列值的最大值/最小值
SUM:返回数列值的总和
(iv)数值函数:Scalar Functions
MOD(x,y):取模 x%y
ln(double a):返回给定数值的自然对数
power(double a, double b):返回某数的乘幂
sqrt(double a):开平方
sin/cos/asin/acos:三角函数
(v)字符串函数
字符串函数(替换,拼接,逆序等)
(vi)日期函数
进行unix的时间转换等
6 hive命令行操作
[avilazhang@hadoop-bigdata-hive ~]$ hive -e ‘select * from fm_data.shield_fm_feature_item_ctr where day=20160508;’
[avilazhang@hadoop-bigdata-hive ~]$ hive -S -e ‘select * from fm_data.shield_fm_feature_item_ctr where day=20160508;’ 终端的输出不会有mapreduce的进度,只会输出结果。
执行sql文件:
hive -f hive_sql.sql