Hive表操作

1.表操作语法和数据类型

1.创建数据库表语法

--库创建查看命令showdatabases;--表创建查看命令showtables;--查看表的基本结构desc table_name;--查看表的详细信息desc formatted table_name;create[external]table[ifnotexists] db_name 
[(col_name data_type [comment col_comment],...)][comment table_comment][partitioned by(col_name data_type [comment col_comment],...)][clusteredby(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]

• external , 创建外部表
• partitioned by，分区表
• clustered by , 分桶表
• stored as , 存储格式
• location ， 存储位置

2.数据类型

分类类型描述字面量示例原始类型TINYINT1字节的有符号整数 -1281271YSMALLINT2字节的有符号整数 -32768327671SINT4字节的有符号整数1BIGINT8字节的有符号整数1LBOOLEANtrue / falseTRUEFLOAT4字节单精度浮点数1.0DOUBLE8字节双精度浮点数1.0DECIMAL任意精度的带符号小数1.0STRING字符串，变长“a”,“b”CHAR固定长度字符串“a”,“b”VARCHAR变长字符串“a”,“b”DATE日期2024-01-03TIMESTAMP时间戳。毫秒值精度BINARY字节数组复杂类型array有序的同类型集合array(1,2)mapkey-value, key原始类型，value任意类型map(‘a’,1,‘b’,2)struct字段集合，类型可以不同struct(‘1’,1,1.0),named_stract(‘col_1’,‘1’,''col_2,‘1’,‘col_3’,‘1.0’)union在有限取值范围内的一个值creat_union(1,‘a’,63)

2.表分类

Hive中可以创建的表有好几种，分别是：
创建存储位置删除数据理念内部表create table …/user/hive/warehouse删除元数据(表信息)，删除数据Hive管理表持久使用外部表create external table …随意，location关键字指定仅删除元数据(表信息)临时链接外部数据用

1.内部表操作

数据分隔符

自行指定

create table if not exists stu2(id int, name string) row format delimited fields terminated by ‘\t’;

2.外部表操作

数据和表相互独立 ，只是链接关系，删除表表不存在，但数据保留。

• 先创建表，在上传数据到目标目录
• 先创上传数据到目标目录，再建表

3.内外部表转换

• ‘EXTERNAL’=‘TRUE’ 或 ‘EXTERNAL’=‘FALSE’ 区分大小写

内转外

altertable stu set tblproperties('EXTERNAL'='TRUE');

外转内

altertable stu set tblproperties('EXTERNAL'='FALSE');

3.数据加载和导出

1.数据加载

1.语法

1.load语法

-- a. 加载数据  -- b. 数据是否在本地；使用local数据不在hdfs，需使用file://协议指定路径;--                 不使用local，数据在hdfs，可以使用hdfs://协议指定路径;-- c. 数据路径-- d. 覆盖已存在数据；overwrite覆盖-- e. 被加载的内部表--  a        b                c          d                     eloaddata[local] inpath 'filepath'[overwrite]intotable tablename;

2. insert select 语法

除了load加载外部数据外，我们也可以通过sql语句，从其它表中加载数据。

insert[overwrite |into]table table_name1 [partition(partcol1=val1, partcol2=val2 ...)[ifnotexists]] select_statement1 from from_ statement;

2.数据加载

举例

-- 建表CREATETABLE myhive.test_load(
dt string comment'时间(时分秒)',
user_id string comment'用户id',
word string comment'搜索内容',
url string comment'访问网址')comment'搜索引擎日志表'ROW FORMAT DELIMITED FIELDSTERMINATEDBY'\t';

-- 数据00:00:011001    JAVA1    http://www.itheima.cn01:30:011002    JAVA2    http://www.itheima.cn02:20:011003    JAVA3    http://www.itheima.cn03:30:011004    JAVA4    http://www.itheima.cn04:40:011005    JAVA5    http://www.itheima.cn05:50:011006    JAVA6    http://www.itheima.cn06:40:011007    JAVA7    http://www.itheima.cn

1.本地上传

-- 将linux本地 '/opt/module/hive3.1.3/search_log.txt' 加载到 hdfs myhive.test_loadloaddatalocal inpath '/opt/module/hive3.1.3/search_log.txt'intotable myhive.test_load;

2.hdfs

-- 将linux本地文件上传到hdfs-- hdfs dfs -put search_log.txt /tmp/-- 注意：写入后源文件会消失loaddata inpath '/tmp/search_log.txt'intotable myhive.test_load;

3.数据加载

insertintotable tbl1 select*from tbl2;insert overwrite tabel tbl1 select*from tbl2;

2.hive表数据导出

1.insert overwrite 方式

-- 将hive表中的数据导出到其它任意目录，例如Linux本地磁盘，例如hdfs、mysql等等-- 语法insert overwrite [local] directory 'path' select_statement1 from from_statement;

• 将查询结果导出到本地 - 使用默认列分隔符

insert overwrite local directory '/opt/module/export1'select*from test_load;

• 将查询结果导出到本地 - 指定列分隔符

insert overwrite local directory '/opt/module/export1'row format delimited fieldsterminatedby'\t'select*from test_load;

• 将查询结果导出到 HDFS 上(不带local关键字)

insert overwrite directory '/tmp/export'row format delimited fieldsterminatedby'\t'select*from test_load;

2.hive shell方式

-- 语法：(hive -e/-f 执行语句/脚本 > file)/opt/module/hive3.1.3/bin/hive -e "select * from myhive.test_load;">/opt/module/hive3.1.3/export2/export_mess1.txt;/opt/module/hive3.1.3/bin/hive -f export.sql>/opt/module/hive3.1.3/export2/export_mess2.txt;

4.分区表

将大文件切割分成多个小文件，方便操作。

• 可以选择字段作为表分区
• 分区其实就是 HDFS 上的不同文件夹
• 分区表可以极大的提高特定场景下 Hive 的操作性能

1.语法

createtable tablename(...) partitioned by(分区列 列类型，...)row format delimited fieldsterminatedby'';

举例

-- 创建一个多分区表，分区 按年月日 分区createtable myhive.score2(id string, cid string, score int) partitioned by(year string,month string,day string)row format delimited fieldsterminatedby'\t';-- load data 加载数据到分区表中loaddatalocal inpath '/opt/module/hive3.1.3/temp1/score.txt'intotable myhive.score2  partition(year='2024',month='01',day='31');-- 添加数据到分区表中 与 创建分区表保持一致

5.分桶表

分桶和分区一样，是通过改变表的存储模式，完成对标优化的一种调优方式。

不同的是，分区是将表拆分到不同的子文件夹中存储，分桶是将表拆分到固定数量的不同问家中存储。

• 可以选择字段作为分桶字段
• 本质上是数据分开在不同的文件中
• 分区和分桶可以同时使用

1.语法

-- 开启分桶表自动优化(自动匹配reduce task数量和桶数量一致)set hive.enforce.bucketing=true;-- 创建分桶表createtable course(c_id string, c_name string, t_id string)clusteredby(c_id)into3 buckets row format delimited fileds terminatedby'\t';

2.数据加载

1.insert select

只能通过 insert select

1.创建一个临时表(外部表或内部表)，通过load data 加载数据进入表

2.通过 insert select 从临时表向桶表插入数据

-- 创建临时中转表createtable myhive.course_temp (c_id string, c_name string, t_id string)row format delimited fieldsterminatedby'\t';-- 中转表中加载数据loaddatalocal inpath '/opt/module/hive3.1.3/temp1/course.txt'intotable myhive.course_temp;-- 中转表通过insert overwrite 给桶表加载数据insert overwrite table myhive.course select*from myhive.course_temp cluster by(c_id);

2.Hash取模

Hash算法是一种数据加密算法，特征：

同样的值被Hash加密后的结果是一致的（如：“abc” 被hash后是 “123456”），那么计算多少次“abc”都是“123456”。分 3 个桶时，无论什么数据取模结果均是：0、1、2。所以，必须用 insert select 语法，因为会触发MapReduce，进行hash取模计算。

3.分桶表性能提升

分区表性能提升：在指定分区列的前提下，减少被操作的数据量，从而提升性能。

分桶表性能提升：基于分桶列的特定操作，如：过滤、join、分组，均可带来性能提升。

6.修改表

1.表重命名

altertable old_table_name renameto new_table_name;-- 示例altertable score1 renameto rename2;

2.修改表属性值

altertable table_name set tblproperties table_properties;

table_properties:(property_name = property_value, property_name = property_value,...)-- 修改内部表属性altertable table_name set tblproperties("external"="true");-- 修改表注释altertable table_name set tblproperties('comment'=new_comment);

3.添加分区

altertable table_name addpartition(year="2023");-- 新分区无数据，需要添加或上传数据文件

4.修改分区

altertable table_name partition(year="2023")renametopartition(year="2024");-- 修改元数据记录，hdfs实体文件夹不会改名,相当于24年的数据存到了23年的文件夹，数据分区分到了24年的分区，物理存在23年-- 不建议修改操作

5.删除分区

altertable table_name droppartition(year="2023");

6.添加列

altertable table_name addcolumns(val1 string, val2 int);

7.修改列名

altertable test_change change val1 val1New int;

8.删除表

droptable table_name;

9.清空表

-- 只能清空内部表truncatetable table_name;

7.复杂类型操作（Array）

Hive支持的数据类型很多，除了基本的：int、string、varchar、timestamp等，还有一些复杂的数据类型

1.array 数组类型.

createtable myhive.test_array(name string, work_locations array<string>)row format delimited fieldsterminatedby'\t' collection items terminatedby',';-- 列分隔符“\t”,array分隔符是“,”

查询 数组名[数字序号]，可以去除指定元素。

size(数组名)，统计数据元素个数。

array_contains(数组名，数据)，可以查看指定数据是否在数组中存在。

2.map 映射类型

map类型其实就是简单的指代：key-value型数据格式。

-- 其中members字段是key-value型数据-- 字段与字段分隔符  ","-- map字段之间的分隔符  "#"-- map内部k-v分隔符  ":"
id,name,members,age
1,zhangsan,father:zhangfei#mother:xiaohuang#brother:zhangsi,282,lisi,father:lisan#mother:huahua#brother:lisisi,223,wangwu,father:wanggang#mother:liuhua#brother:wangwuwu,294,mayun,father:muyiyi#mother:tuhua#brother:mawuyun,29

-- 建表语句createtable myhive.test_map(
id int,
name string,
members map<string, string>,
age int)row format delimited
fieldsterminatedby','
collection items terminatedby'#'
map keysterminatedby':';

3.struct 结构类型

复合类型，可以在一个列中存入多个列，每个子列允许设置类型和名称

1#周杰伦:112#邓紫棋:163#刘德华:214#周星驰:265#凤凰传奇:23

createtable myhive.test_struct(
id string, 
info struct<name:string, age:int>)row format delimited fieldsterminatedby'#'
collection items terminatedby':';

类型arraymapstruct定义array<类型>map<key 类型, value 类型>struct<子列名 类型, 子列名 类型…>示例如定义为array数据为：1，2，3，4，5如定义为：map<string, int>数据为：{‘a’:1,‘b’:2,‘c’:3}如定义为：struct<c1 string, c2 int, c3 date> 数据为：‘a’,1,‘2024-01-06’内含元素类型单值，类型取决于定义键值对，k-v，k和v类型取决于定义单值，类型取决于定义元素个数动态，不限制动态，不限制固定，取决于定义的子列数量取元素array[数字序号] 序号从0开始map[key]取出对应key的valuestruct.子列名通过子列名去除子列值可用函数size统计元素个数 array_contains判断是否包含指定数据size统计元素个数 array_contains判断是否包含指定数据 map_keys取出全部key，返回array map_values取出全部values，返回array暂无

8.数据基本查询

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差不多，部分有区别，如：clusterby、distribute by、sort by等。

示例

-- 建库 切换CREATEDATABASE itheima;USE itheima;-- 订单表CREATETABLE itheima.orders(
orderId bigintCOMMENT'订单id',
orderNo string COMMENT'订单编号',
shopId bigintCOMMENT'门店id',
userId bigintCOMMENT'用户id',
orderStatus tinyintCOMMENT'订单状态 -3:用户拒收 -2:付款的订单 -1: 用户取消 0:待发货 1:配送中 2:用户确认收货',
goodsMoney doubleCOMMENT'商品金额',
deliverMoney doubleCOMMENT'运费',
totalMoney doubleCOMMENT'订单金额 (包括运费)',
realTotalMoney doubleCOMMENT'实际订单金额 (折扣后金额)',
payType tinyintCOMMENT'支付方式,0:未知;1:支付宝;2:微信;3:现金;4:其他',
isPay tinyintCOMMENT'是否支付 0:未支付 1:已支付',
userName string COMMENT'收件人姓名',
userAddress string COMMENT'收件人地址',
userPhone string COMMENT'收件人电话',
createTime timestampCOMMENT'下单时间',
payTime timestampCOMMENT'支付时间',
totalPayFee intCOMMENT'总支付金额')ROW FORMAT DELIMITED FIELDSTERMINATEDBY'\t';loaddatalocal inpath '/opt/module/hive3.1.3/temp1/itheima_orders.txt'intotable itheima.orders;-- 用户表CREATETABLE itheima.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 FIELDSTERMINATEDBY'\t';loaddatalocal inpath '/opt/module/hive3.1.3/temp1/itheima_users.txt'intotable itheima.users;

2.rlike正则匹配

select*from itheima.orders where useraddress rlike'.*广东.*';select*from itheima.orders where useraddress rlike'..省 ..市 ..区';select*from itheima.orders where username rlike'[赵钱孙李]\\S+';select*from itheima.orders where userphone rlike'188\\S{4}0\\S{3}';

字符匹配示例.任意单个字符jav.匹配java[][]中任意一个字符java匹配j[abc]va-[]内表示字符范围java匹配[a-z]av[a-v]^在[]内的开头，匹配除[]内的字符之外的任意一个字符java匹配j[ ^b-f ]va|或x|y匹配x或y\将下一字符标记为特殊字符、文本、反向引用或八进制转义符( 匹配 （$匹配输入字符串结尾的位置，如果设置了regexp对象的multiline属性，$还会与 ‘\n’ 或 '\t’之前的位置匹配;$匹配位于一行及外围的;号零次或多次匹配前面的字符zo匹配zoo或z+一次或多次匹配前面的字符zo+匹配zo或zoo?零次或一次匹配前面的字符zo?匹配z或zop{n}n是非负整数，正好匹配n次o(2)匹配food中的两个op{n,}n是非负整数，正好匹配n次o(2)匹配food中的所有op{n,m}m和n是非负整数，其中n<=m，匹配至少n次，至多m次o{1,3}匹配foood中的三个o\p{P}一个标点字符!*#$%&'()"+,-:;<=>?@[]^_`{|}~J\p{P}a匹配J?a\b匹配一个字边界va\b匹配java中的va，但不匹配javar中的va\B非字边界匹配va\B匹配javar中的va，但不匹配java中的va\d数字字符匹配1[\d]匹配13\D非数字字符匹配[\D]java匹配Jjava\w单词字符java匹配[\W]ava\W非单词字符$java匹配[\W]\s空白字符Java 2匹配Java\s2\S非空白字符java匹配j[\S]va\f匹配换页符等效于\x0a和\cL\n匹配换行符等效于\x0a和\cJ

3.union联合

union 用于将多个select语句的结果组合成单个结果集。

每个select语句返回的列的数量和名称必须相同。否则，将引发架构错误。

-- 默认去重 （无需去重union all）select...union[all]select...

4.Sampling采样

tablesample函数

语法1，基于随机分桶抽样：

select...from tbl tablesample(bucket x outof y on(colname()| rand()))
x: 表示从y里面随机抽取x份数据作为取样
y: 表示将表数据随即划分成y份（y个桶）
colname: 表示随机的依据基于某个列的值
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大小的数据，单位可以是KB、MB、GB

注意：

使用这种语法抽样，条件不变的话，每一次抽样的结果都一致

即无法做到随机，只是按照数据顺序从前向后取

5.虚拟列

虚拟列是Hive内置的可以在查询语句中使用的特殊标记，可以查询数据本身的详细数据。

Hive目前可用3个虚拟列：

• input_file_name：显示数据行所在的具体文件
• block_offset_inside_file：显示数据行所在文件的偏移量
• row_offset_inside_block：显示数据所在hdfs块的偏移量（设置set hive.exec.rowoffset=true才可使用）

虚拟列作用：

• 查看行级别数据详细参数
• 可以用于where、group by 等各类统计计算中
• 可以协助进行错误排查工作

6.函数

分类标准：内置函数、用户定义函数UDF

-- 查看所有可用函数show functions;-- 查看函数使用方式describefunctionextended 函数名;

1.内建函数

1.数值函数（Mathematical Functions）

-- 取整函数：round 返回double类型的整数值部分（四舍五入）selectround(3.1415926);-- 指定精度取整函数：round(double a, int b) 返回指定精度b的double类型selectround(3.1415926,3);-- 取随机函数：rand每次执行都不一样 返回一个0到1范围内的随机数select rand();-- 指定种子取随机数函数：rand(int seed)得到一个稳定的随机数序列select rand(3);-- 求数字的绝对值select abs(-5);-- 得到pi值select pi();

2.集合函数（Collection Functions）

-- 返回map类型的元素个数select size(Map<K,V>)from table_map;-- 返回array类型的元素个数select size(Array<T>)from table_array;-- 返回map内的全部key(得到的是array)select map_key(Map<K,V>)from table_map;-- 返回map内的全部value(得到的是array)select map_values(Map<K, v>)from table_map;-- 如果array包含指定value，返回trueselect*from table_array where array_contains(Array<T>,value);-- 根据数组元素的自然顺序按升序对输入数组进行排序并返回它select*, sort_array(Array<T>)from table_name;

3.类型转换函数（Type Conversion Functions）

-- 将给定字符串转换为二进制selectbinary("hadoop");-- 将表达式expr的结果转换为给定类型select cast('1'asbigint);

4.日期函数（Date Functions）

-- 返回当前时间戳select current_timstamp()-- 返回当前日期selectcurrent_date-- 时间戳转日期select to_date(string timestamp)-- 得到给定时间的：年selectyear(string date)-- 得到给定时间的：季度select quarter(date/timestamp/string)-- 得到给定时间的：月selectmonth(string date)-- 得到给定时间的：日selectday(string date)-- 得到给定时间的：月份的第几天select dayofmonth(date)-- 得到给定时间的：小时selecthour(string date)-- 得到给定时间的：分钟selectminute(string date)-- 得到给定时间的：秒selectsecond(string date)-- 得到给定时间的：本年第几周select weekofyear(string date)-- 返回enddate到startdate之间的天数select datediff(string enddate, string startdate)-- 日期相加：select date_add(date/timestamp/string startdate,tinyint/smallint/int days)-- 日期相减：select date_sub(date/timestamp/string startdate,tinyint/smallint/int days)

5.条件函数（Conditional Functions）

-- testCondition为true，返回valueTrue，否则返回valueFalseOrNullif(boolean testCondition, T valueTrue, T valueFalseOrNull)-- 如果a为null，则返回true，否则返回false
isnull(a)-- 如果a不为null，则返回true，否则返回false
isnotnull(a)-- 如果value为null，则返回default_value，否则value
nvl(T value, default_value)-- 返回第一个不是null的v，如果所有v都是null，则返回nullcoalesce(T v1, T v2,...)-- 当a=b时，返回c；[当a=d时，返回e]*；否则返回fcase a when b then c[when d then e]*[else f]end-- 当a=ture时，返回b；当c=true时，返回d；否则返回e。a可以是表达式，如1+1casewhen a then b [when c then d]*[else e]end-- 如果a=b，则返回null；否则返回a。nullif(a,b)6-- 如果boolean——condition结果不为true，则引发异常报错。
assert_true(boolean condition)

6.字符串函数（String Functions）

-- 连接字符串
concat(string|binary A, string|B ...)-- 同concat，但是可以自己定义字符串之间的分隔符
concat_ws(string SEP, string A, string B ...)-- 字符串长度
length(string A)-- 全部转小写 全部转大写
lower(string A)
upper(string a)-- 返回从A的两端裁剪空格得到的字符串
trim(string A)-- 按照pat分割字符串str，pat是正则表达式
split(string str, string pat)

7.数据脱敏函数（Data Masking Functions）

-- hash加密（结果是16进制字符串）,非字符串会得到null
mask_hash(string|char|varchar str)

8.其它函数（Misc. Functions）

2.用户自定义函数

1.UDF(User-Defined Functions) - 用户自定义功能函数

2.UDAF(User-Defined Aggregate Functions) - 用户定义聚合函数

3.UDTF(User-Defined Table-Generating) - 用户定义表生成函数