（三）hive学习笔记——1.2hive基础
1.Hive常用的交互参数
- -d定义一个变量，hive启动后可以使用${变量名}引用变量。
[hzhao@h2 ~]$ hive -d p=person hive (default)> select * from ${p}; OK person.name person.age hzhao 23 Time taken: 0.981 seconds, Fetched: 1 row(s) - –database指定使用哪个库。
- -e指定命令行获取的一条引号引起来的sql，执行完返回结果后退出cli！
- -f执行一个文件中的sql语句。
- -i先初始化一个sql文件之后不退出cli。
- -s静默模式，不打印无关的信息。
2.在hivecli中执行hdfs和linux命令
- 执行hdfs命令前面加上dfs
hive (default)> dfs -ls /; Found 5 items drwxrwxrwx - hzhao supergroup 0 2021-01-06 03:36 /hive drwxr-xr-x - hzhao supergroup 0 2020-12-20 21:08 /idea -rw-r--r-- 3 hzhao supergroup 12 2020-12-20 16:36 /testRename.txt drwxrwxrwx - hzhao supergroup 0 2021-01-04 08:27 /tmp drwxrwxrwx - hzhao supergroup 0 2021-01-04 08:26 /user - 执行linux命令前面加上！
hive (default)> !ls /opt; data module rh soft
3.Hive中的数据类型
-
基本类型
tinyint(对应Java中的byte)
smallint(对应Java中的short)
int(对应Java中的int)
bigint(对应Java中的long)
boolean(对应Java中的boolean)
float(对应Java中的float)
double(对应Java中的double)
string(对应Java中的String)
timestamp(对应Java中的TimeStamp)
binary(对应Java中的byte[]) -
复杂类型
a. 数组类型 array
例子1:
原始数据：100,200,300
200,300,500
建表语句：create external table ex(vals array<int>) row format delimited fields terminated by '\t' collection items terminated by ',' location '/ex';查询每行数组的个数，查询语句：
select size(vals) from ex;注：hive 内置函数不具备查询某个具体行的数组元素,需要自定义函数来实现
例子2:
原始数据：100,200,300 tom,jary
200,300,500 rose,jack
建表语句：create external table ex1(info1 array<int>,info2 array<string>) row format delimited fields terminated by '\t' collection items terminated by ',' location '/ex';b.map类型
例子1:
原始数据：tom,23
rose,25
jary,28
建表语句：create external table m1 (vals map<string,int>) row format delimited fields terminated by '\t' map keys terminated by ',' location '/map';查询语句：
select vals['tom'] from m1;例子2:
原始数据：tom 192.168.234.21
rose 192.168.234.21
tom 192.168.234.22
jary 192.168.234.21
建表语句 ：create external table ex (vals map<string,string>) row format delimited fields terminated by '\t' map keys terminated by ' ' location '/ex';注意：map类型，列的分割符必须是\t
查询语句select vals['tom'] from ex where vals['tom'] is not null;如果想做去重工作，可以调用distinct内置函数
select distinct(ip) from (select vals['tom'] ip from ex where vals['tom'] is not null)ex1;或者select distinct(vals['tom']) from m2 where vals['tom'] is not null;c.struct 类型
例子:
原始数据：tom 23
rose 22
jary 26
建表语句：create external table ex (vals struct<name:string,age:int>)row format delimited collection items terminated by ' ' location '/ex';查询语句：
select vals.age from ex where vals.name='tom';练习，文件中某条数据如下所示
songsong,bingbing_lili,xiaoxiaosong:18_xiaoxiaoxue:19,huilongguan_beijing
{ "name":"songsong", "frinends":["bingbing","lili"], "children":{ "xiaoxiaosong":18, "xiaoxiaoxue":19 } "address":{ "street":"hui long guan", "city":"beijing" } }建表语句
hive (default)> create table people(name string, friends array<string>, children map<string,int>,address struct<street:string,city:string>) row format delimited fields terminated by ',' collection items terminated by '_' map keys terminated by ':' lines terminated by '\n'; OK Time taken: 0.185 seconds将内容写入 /opt/data/people文件中
[hzhao@h2 ~]$ hive -d t=/opt/data/people hive (default)> load data local inpath '${t}' into table people; Loading data to table default.people Table default.people stats: [numFiles=1, totalSize=74] OK Time taken: 1.054 seconds查询
hive (default)> select friends[0],children['xiaoxiaosong'],address.street from people where name = 'songsong'; OK _c0 _c1 street bingbing 18 huilongguan
4.库的常见操作
-
增
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment] //库的注释说明
[LOCATION hdfs_path] //库在hdfs上的路径
[WITH DBPROPERTIES(property_name=property_value,···)]//库的属性 -
删
drop database 库名:只能删除空库
drop database 库名 cascade:删除非空库和库下的表 -
改
use 库名:切换库
只能修改owner，dbpropertis，location
location:ALERT (DATABASE|SCHEMA) database_name SET location hdfs_path;
dbpropertis:ALERT (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value,···);
owner:location:ALERT (DATABASE|SCHEMA) database_name SET OWNER user; -
查
show databases:查看当前所有库
show tables in database:查看库中所有的表
desc database 库名：查看库的描述信息
desc database extended 库名：查看库的详细描述信息
5.表的常见操作
-
增
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_naem 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]//表中的数据要以哪种文件格式来存储,默认为TEXTFILE(文本文件)
[LOCALION hdfs_path]//表在hdfs中的位置a. 建表时，不带EXTERNAL，创建的表是一个MANAGED_TABLE(管理表，内部表)
[外部表和内部表的区别时：内部表在执行删除操作时，会将表的元数据和表位置的数据一起删除；外部表在执行删除表操作时，值删除表的元数据]
b.在建表时，指定了PARTITIONED BY，这个表成为分区表。将表中的数据，分散到表目录下的多个子目录中
c.在建表时，指定了CLUSTERED BY，这个表成为分桶表其他建表方式
create table 表名 like 表名1:只复制表结构
create table 表名 as slect 语句:先执行查询语句，将查询语句结果按照顺序作为新表的列（不能创建分区表） -
删
drop table 表名:删除表
truncate table 表名：清空管理表，只清空数据 -
改
alert table 表名 set tblproperties(‘EXTERNAL’=‘TRUE’):外部表与内部表的切换
alter table 表名 change [column] 旧列名 新列名 新列类型[comment 新列的注释]:更新列
alert table 表名 ADD/REPLACE COLUMNS (列名 列的类型[comment 列的注释],···):增加和重置列（删除原有列，保留新的列，数据还在） -
查
show tables:查看当前库所有的表
desc 表名：查看表的描述信息
desc extended 表名：查看表的详细描述信息
desc formatted 表名：查看表的详细描述信息并格式化 -
数据导入
5.1. load 方式load data [local] inpath into table 表名 [partition()]:将数据直接导入到表目录中(本地使用local,相当于put操作,不使用local相当于从hdfs使用mv命令)
5.2. insert方式
insert方式运行mr程序，通过程序将数据输出到表目录中，场景：
- 向分桶表中插入数据
- 如果指定表中的数据，不是以纯文本形式存储，需要使用insert方式导入
insert into|overwrite table 表名 select 语句 | value(),(),()
insert into:向表中追加新的数据
insert overwrite:先清空表中所有的数据，再向表中添加新的数据5.3 location方式
在建表时，指定表的location为数据存放的目录
5.4 import方式
不仅可以导入数据还可以导入元数据（表结构）import只能导入export导出的内容！
IMPORTANT [[EXTERNAL] TABLE 表名（新表或已经存在的表）[PARTITION (分区字段=分区值)] FROM ‘source_path’ [LOCATION ‘import target_path’]
如果向一个新表中导入数据，hive会根据要导入的元数据自动创建表
如果向一个已经存在的表导入数据 ，在导入之前会先检查表的结构和属性是否保持一致，只有在表的结构和属性一致时，才会执行导入
不管表是否为空，要导入的分区必须是不存在的 -
数据导出
6.1 insert方式insert overwrite [local] diretcory ‘路径’ ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ select 语句
local：有的话在本地生成，没有在hdfs上生成6.2 export方式
既能导出数据又能导出元数据，export会在导出目录中生成数据和元数据，导出的元数据与关系型数据库无关系！
export table 表名 [partiton(分区字段=分区值,分区字段=分区值)] to ‘hdfsPath’
如果是分区表可以指定分区
6.分区表
- 分区概念
在建表时，指定了PARTITIONED BY，这个表成为分区表。将表中的数据，分散到表目录下的多个子目录中。 - 分区意义
分区的目的是为了就数据分散到多个子目录中，在执行查询时，可以只选择查询某些子目录中的数据，加快查询效率！只有分区表才有子目录（分区目录）。
分区目录的名称由两部分确定：分区列列名=分区列列值
将输入导入到指定的分区后，数据会附加上分区列的信息！
7.分区表操作
-
创建分区表
数据示例：10 ACCOUNTING 1700 20 RESEARCH 1800 30 SALES 1900 40 OPERATIONS 1700建表语句：
hive (default)> create external table if not exists default.deptpart1(deptno int,dname string,loc int)PARTITIONED BY(area string)row format delimited fields terminated by '\t'; OK Time taken: 0.06 seconds注意事项：
如果表是个分区表，在导入数据时，必须指定向哪个分区目录导入数据。
如果分区不存在，load时自动帮我们生成分区导入数据
hive (default)> load data local inpath '${t}' into table deptpart1 partition(area='huazhong'); Loading data to table default.deptpart1 partition (area=huazhong) Partition default.deptpart1{area=huazhong} stats: [numFiles=1, totalSize=69] OK Time taken: 0.648 seconds -
分区的查询
show partitions 表名
-
创建分区
alter table 表名 add partition(分区字段名=分区字段值)创建一个分区
alter table 表名 add partition(分区字段名=分区字段值) partition(分区字段名=分区字段值)创建多个分区a.在hdfs上生成分区目录；
b.在mysql中metastore.partitions表在生成分区的元数据直接使用load命令向分区加载数据，如果分区不存在，load时自动帮我们生成分区
如果数据已经按照规范的格式上传到HDFS，可以使用修复分区命令自动生成分区的元数据msck repair table 表名；
-
数据查询
hive (default)> select * from deptpart1 where area = 'huazhong'; OK deptpart1.deptno deptpart1.dname deptpart1.loc deptpart1.area 10 ACCOUNTING 1700 huazhong 20 RESEARCH 1800 huazhong 30 SALES 1900 huazhong 40 OPERATIONS 1700 huazhong Time taken: 0.129 seconds, Fetched: 4 row(s) -
删除分区
alter table 表名 drop partition(分区字段名=分区字段值)删除分区
alter table 表名 drop partition(分区字段名=分区字段值) partition(分区字段名=分区字段值) 删除多个分区
8.分桶表
- 分桶表概念
在建表时，指定了CLUSTERED BY，这个表成为分桶表！
分桶：和MR中分区是一个概念！把数据分散到多个文件中！ - 分桶的意义
分桶本质上也是为了分散数据！在分桶后，可以结合hive提供的抽样查询，只查询指定桶的数据 - 在分桶时，也可以指定将每个桶的数据根据一定的规则来排序。如果需要排序，可以在CLUSTERED BY后跟SORTED BY
9.分桶表操作
-
创建分桶表
数据示例1001 ss1 1002 ss2 1003 ss3 1004 ss4 1005 ss5 1006 ss6 1007 ss7 1008 ss8 1009 ss9 1010 ss10 1011 ss11 1012 ss12 1013 ss13 1014 ss14 1015 ss15 1016 ss16建表语句
[hzhao@h2 data]$ hive -d t=/opt/data/fentong.txt hive (default)> create table stu_buck(id int,name string) clustered by(id) sorted by(id desc)into 4 buckets row format delimited fields terminated by '\t'; OK Time taken: 1.318 seconds导入数据：
向分桶表导入数据时，需要运行MR程序，才能实现分桶操作！ load的方式，只是执行put操作，无法满足分桶表导入数据！
必须执行insert into
insert into table 表名 select 语句建普通表
hive (default)> create table stu_buck_tmp(id int,name string) row format delimited fields terminated by '\t'; OK Time taken: 0.078 seconds导入数据到普通表
hive (default)> load data local inpath '${t}' into table stu_buck_tmp; Loading data to table default.stu_buck_tmp Table default.stu_buck_tmp stats: [numFiles=1, totalSize=178] OK Time taken: 1.218 seconds打开hive的自动分桶开关
hive (default)> set hive.enforce.bucketing=true;打开hive的排序开关
hive (default)> set hive.enforce.sorting=true;从普通表插入数据到分桶表
hive (default)> insert into table stu_buck select * from stu_buck_tmp; -
抽样查询
抽样查询的表必须是分桶表！
select * from 分桶表 tablesample(bucket 1 out of 4 on 分桶表分桶字段);
110
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
