hive的基本 *** 作，DDL的数据定义，对表的增删查改，对表数据的增删查改（DML），数据的导入和导出，创建管理表，外部表，内外部表的互相转换，表的修改和替换，常用函数，自定义函数，窗口函数

注意：
hive有三种复杂数据类型：array、map、struct。array和map与java中的array和map类似，而struct与C语言中的struct类似，他封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。

1、假设某表有如下一行，并用json格式来表示其数据结构。在hive下访问的格式为：

{
    "name": "法外狂徒",
    "friends": ["张三" , "李四"],       //列表Array, 
    "children": {                              //键值Map,
        "王麻子 王武": 18,
        "晓晓 松鼠": 19
    }
    "address": {                              //结构Struct,
        "street": "贵阳 清镇 广大城", 
        "city": "贵州",
        "email":[email protected]
    }
}

2、对上面的数据创建txt文件，并导入数据

[lqs@bdc112 data]$ pwd
/home/lqs/module/hive/data
[lqs@bdc112 data]$ vim test.txt 

然后输入以下内容：

法外狂徒,张三_李四,王麻子 王武:18_晓晓 松鼠:19,贵阳_清镇_广大城[email protected]
法外狂徒00,张三00_李四00,王麻子00 王武00:18_晓晓00 松鼠00:19,贵阳_清镇_广大城[email protected]

3、创建demo表

create table demo
(
    name     string,
    friends  array,
    children map,
    address  struct
)
row format delimited fields terminated by ','   --行格式分隔字段终止于 列分隔符
collection items terminated by '_'  --收集项目终止于 map struct 和 array 的分隔符(数据分割符号)
map keys terminated by ':'  --映射键终止于 MAP中的key与value的分隔符
lines terminated by 'n';   --行终止于 行分隔符

4、导入文本数据到demo表

load data local inpath '/home/lqs/module/hive/data/test.txt' into table demo;

5、访问三种集合列里的数据

select friends[1],children['王麻子 王武'],address.city from demo where name='法外狂徒';

hive的原子数据类型是可以进行隐式转换的，类似于java的类型转换。
1、隐式类型转换规则如下
a、任何整数类型都可以是隐式的转换为一个范围更广的类型，如tinyint可以转换成int可以转换成bigint
b、所有整数类型、float和string类型都可以隐式的转换成double；
c、tinyint、smallint、int都可以转换为float；
d、boolean类型不可以转换为任何其它的类型
2、可以使用cast *** 作显示进行数据类型转换

0: jdbc:hive2://bdc112:10000> select '2'+2,cast('2' as int)+2;
+------+------+
| _c0  | _c1  |
+------+------+
| 4.0  | 4    |
+------+------+
1 row selected (0.523 seconds)

CREATE DATAbase [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, …)];

1、创建数据库，我的hive默认路径是hdfs上的/user/hive/warehouse/

create database db_demo;
#标准写法,避免要创建的数据库已经存在错误，增加if not exists判断
create database if not exists db_demo;

创建一个数据库，指定数据库在hdfs上的位置

create database db_demo01 location '/db_demo01';

1、显示数据库

show database;

2、过滤显示查询的数据库，有点类似linux中的grep

show databases like 'db_de*';

3、显示数据库信息

desc database db_demo;

4、显示数据库详细信息

desc database extended db_demo;

5、切换到指定数据库

use db_demo

用户可以使用alter database命令为某个数据库的dbproperties设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。
1、修改属性值drop database db_hive cascade;

alter database db_demo set dbproperties('createTime'='20211202');

2、查看修改后的值

desc database extended db_demo;

1、删除空数据库

drop database db_demo;

2、如果不确定删除的数据库是否存在，最好使用标准写法。存在就删除，不存在也不会报错

drop database if exists db_demo;

3、如果数据库不为空，可以用cascade命令强制删除

drop database db_demo cascade;

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]
[LIKE table_name]

相关字段解释说明

（1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。
（2）EXTERNAL关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
（3）COMMENT：为表和列添加注释。
（4）PARTITIonED BY创建分区表
（5）CLUSTERED BY创建分桶表
（6）SORTED BY不常用，对桶中的一个或多个列另外排序
（7）ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, …)]
用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。
SerDe是Serialize/Deserilize的简称， hive使用Serde进行行对象的序列与反序列化。
（8）STORED AS指定存储文件类型
常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）
如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
（9）LOCATION ：指定表在HDFS上的存储位置。
（10）AS：后跟查询语句，根据查询结果创建表。
（11）LIKE允许用户复制现有的表结构，但是不复制数据。

1、理论基础
a、默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive会（或多或少地）控制着数据的生命周期。
b、Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。
c、当我们删除一个管理表时，Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。

1、创建本地数据

[lqs@bdc112 data]$ pwd
/home/lqs/module/hive/data
[lqs@bdc112 data]$ vim stu.txt

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

2、创建普通表

create table if not exists stu_demo
(
    id   int,
    name string
)
    row format delimited fields terminated by 't';

load data local inpath '/home/lqs/module/hive/data/stu.txt' into table stu_demo;

select * from stu_demo;

3、根据查询结果创建表，查询的结果会添加到新创建的表中

create table if not exists stu_demo01 as select id,name from stu_demo;
select * from stu_demo01;

4、根据已经存在的表结构创建表

create table if not exists stu_demo02 like stu_demo;
select * from stu_demo02;

5、查询表的类型

desc formatted stu_demo;

6、删除管理表

drop table if exists stu_demo02;

1、理论基础

因为表是外部表，所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。

2、使用场景

每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。

1、数据准备

/home/lqs/module/hive/data
[lqs@bdc112 data]$ vim dept.txt 
[lqs@bdc112 data]$ vim emp.txt

dept:

10 ACCOUNTING 1700
20 RESEARCH 1800
30 SALES 1900
40 OPERATIONS 1700

emp:

7369 SMITH CLERK 7902 1980-12-17 800.00 20
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7839 KING PRESIDENT 1981-11-17 5000.00 10
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7902 FORD ANALYST 7566 1981-12-3 3000.00 20

2、上传到hadoop的hdfs

[lqs@bdc112 data]$ hadoop fs -mkdir -p /company/dept
[lqs@bdc112 data]$ hadoop fs -mkdir -p /company/emp
[lqs@bdc112 data]$ hadoop fs -put dept.txt /company/dept
2021-12-23 16:01:46,454 INFO sasl.SaslDataTransferClient: SASL encryption trust check: localHostTrusted = false, remoteHostTrusted = false
[lqs@bdc112 data]$ hadoop fs -put emp.txt /company/emp
2021-12-23 16:01:57,338 INFO sasl.SaslDataTransferClient: SASL encryption trust check: localHostTrusted = false, remoteHostTrusted = false

3、分别创建部门外部表和员工外部表
dept_demo

create external table if not exists dept_demo
(
    deptno int,
    dname  string,
    loc    int
)
    row format delimited fields terminated by 't'
    location '/company/dept';

emp_demo

create external table if not exists emp_demo
(
    empno    int,
    ename    string,
    job      string,
    mgr      int,
    hiredate string,
    sal      double,
    comm     double,
    deptno   int
)
    row format delimited fields terminated by 't'
    location '/company/emp';

4、查看创建的表

show tables;

5、查看格式化数据

desc formatted dept_demo;

6、删除外部表

drop table dept_demo;

1、查看表类型

desc formatted dept_demo;

2、修改内部表为外部表

alter table stu_demo set tblproperties ('EXTERNAL'='TRUE'); --EXTERNAL和TRUE必须为大写，否则不报错且改不了
desc formatted stu_demo;

注意：EXTERNAL和TRUE必须为大写，否则不报错且改不了，一定要注意大小写

3、修改外部表为内部表

alter table stu_demo set tblproperties ('EXTERNAL'='FALSE');
desc formatted stu_demo;

alter table stu_demo02 rename to stu_demo03;

1、语法基础
a、更新列：

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

注意：更新列，列名是可以随便修改的，但是列的类型是只能由小改大的，不能由大改小，即他们遵循自动转换（自动提升）的转换规则。

b、增加和替换列

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], …)

注意：add是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE则是表示替换表中所有字段，REPLACE使用的时候，字段的类型要跟之前的类型对应上，数量可以减少或者增加，其实就是包含了更新列，增加列，删除列的功能。

1、查询表结构

desc stu_demo03;

2、添加列

alter table stu_demo03 add columns(test01 double);
desc stu_demo03;

3、更新列

alter table stu_demo03 change column test01 test string;
desc stu_demo03;

4、替换列

alter table stu_demo03 replace columns(id_t int, name_t string,test_t string);
desc stu_demo03;

注意：
不管是修改列还是替换列，同一列的数据类型只能往上提升，不能往下走，否则会报错，且不能修改
报错内容如下：

[08S01][1] Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Unable to alter table. The following columns have types incompatible with the existing columns in their respective positions : id_t

drop table stu_demo03;
drop table if exists stu_demo03;

注意：truncate只能删除管理表（内部表），不能删除外部表中的数据，但是可以清除元数据。

truncate table stu_demo03;

1、语法基础介绍

load data [local] inpath ‘数据的path’ [overwrite] into table student [partition (partcol1=val1,…)];

相关字段解释：
		注意：
			[] 里的内容表示可选字段。
	（1）load data:表示加载数据
	（2）local:表示从本地加载数据到hive表；如果不写，则表示从从HDFS加载数据到hive表
	（3）inpath:表示加载数据的路径
	（4）overwrite:表示覆盖表中已有数据，否则表示追加
	（5）into table:表示加载到哪张表
	（6）student:表示具体的表
	（7）partition:表示上传到指定分区

1、创建一张表

drop table stu_demo;
create table if not exists stu_demo
(
    id   int,
    name string
)
    row format delimited fields terminated by 't';

2、加载本地文件到hive

方式一

load data local inpath '/home/lqs/module/hive/data/stu.txt' into table stu_demo;

方式二

hadoop fs -put /home/lqs/module/hive/data/stu.tx /da_hive2/stu_demo --第二个路径是hdfs上的路径

3、加载hdfs文件到hive中
上传文件到hdfs

dfs -put  /home/lqs/module/hive/data/stu.txt /da_hive2/stu_demo

加载hdfs上数据，导入完成后去hdfs查看文件是否还存在

load data inpath '/da_hive2/stu_demo/stu.txt' into table stu_demo;

3、加载数据覆盖表中已有的数据

load data inpath '/da_hive2/stu_demo/stu.txt' overwrite into table stu_demo;

1、基本模式插入数据

insert into table stu_demo03 values(12,'法外狂徒'),(24,'张三');

2、根据查询的结果插入数据

insert overwrite table stu_demo03 select id,name from stu_demo where id<1010;

相关字段解释：
	insert into：以追加数据的方式插入到表或分区，原有数据不会删除
	insert overwrite：会覆盖表中已存在的数据

注意：
insert不支持插入部分字段，并且后边跟select语句时，select之前不能加as，加了as会报错，一定要跟下面的as select区分开。
其次，上面两种方式都不推荐使用，以为需要跑MapReduce，插入过程很慢，建议使用数据加载的方式来插入数据。

create table if not exists stu_demo11 as select id,name from stu_demo;

1、上传数据导hdfs上

[lqs@bdc112 ~]$ hadoop fs -mkdir -p /demo/stu_demo12;
[lqs@bdc112 ~]$ hadoop fs -put /home/lqs/module/hive/data/stu.txt /demo/stu_demo12;
2021-12-23 20:41:15,135 INFO sasl.SaslDataTransferClient: SASL encryption trust check: localHostTrusted = false, remoteHostTrusted = false

2、创建表并指定在hdfs上的位置

create external table if not exists stu_demo12
(
    id   int,
    name string
)
    row format delimited fields terminated by 't'
    location '/demo/stu_demo12';

3、查询数据

select * from stu_demo12;

注意：
先用export导出后，再将数据导入。并且因为export导出的数据里面包含了元数据，因此import要导入的表不可以存在，否则报错。

import table stu_demoI from '/user/hive/warehouse/export/stu_demoE';

1、将查询的结果导出到本地

insert overwrite local directory '/home/lqs/module/hive/data/export/stu_demo'
select *
from stu_demo;

2、将查询的结果格式化导出到本地

insert overwrite local directory '/home/lqs/module/hive/data/export/stu_demo'
    row format delimited fields terminated by 't'
select *
from stu_demo;

3、将查询的结果导出到hdfs上

insert overwrite directory '/user/lqs/stu_demo'
    row format delimited fields terminated by 't'
select *
from stu_demo;

注意：
insert 导出，导出的目录不用自己提前创建，hive会帮我们自动创建，但是由于是overwrite，所以导出路径一定要写具体，否则很可能会误删数据。这个步骤很重要，切勿大意。

dfs -get /user/hive/warehouse/stu_demo/stu.txt /home/lqs/module/hive/data/export/stu_demo1;

[lqs@bdc112 hive]$ pwd
/home/lqs/module/hive
[lqs@bdc112 hive]$ bin/hive -e 'select * from db_hive.stu_demo;' > /opt/module/hive/data/export/stu_demo2.txt;

export table db_hive.stu_demo to
 '/user/hive/warehouse/export/stu_demo';

特别注意：
export和import主要用于两个Hadoop平台集群之间Hive表迁移，不能直接导出的本地。

基本语法：

SELECt [ALL | DISTINCT] select_expr, select_expr, …
FROM table_reference
[WHERe where_condition]
[GROUP BY col_list]
[HAVINg col_list]
[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]

1、数据准备
相关数据和表已经在<外部表demo>准备好了
2、全表查询

select * from emp_demo;

3、选择特定列查询

select empno,ename from emp_demo;

注意：
SQL 语言大小写不敏感。
SQL 可以写在一行或者多行
关键字不能被缩写也不能分行
各子句一般要分行写。
使用缩进提高语句的可读性。

简介：
	1、重命名一个列以便于计算
	2、紧跟列名，也可以在列名和别名之间加入关键字 as

例如：

select ename as name,deptno as dn from emp_demo;
select ename name,deptno dn from emp_demo;
--注意：以上两个起别名都是可以的

1、求总行数

select count(*) row_cnt from emp_demo;

2、求工资的最大值

select max(salary) max_salary from emp_demo;

3、求工资的最小值

select min(salary) min_salary from emp_demo;

4、求工资的和

select sum(salary) sum_salary from emp_demo;

5、求平均工资值

select avg(salary) avg_salary from emp_demo;

show functions;     --查询hive所有的函数有哪些
desc function upper;    --upper(str) - Returns str with all characters changed to uppercase

select unix_timestamp('2021-12-21 12:12:12');   --1640088732

select from_unixtime(1640088732);   --2021-12-21 12:12:12

select `current_date`();    --2021-12-24

select `current_timestamp`();   --2021-12-24 09:08:56.542000000

select year('2021-12-22');--2021    --记住
select month('2021-12-22'); --12
select day('2021-12-22');   --22
select hour('2021-12-21 12:13:14');--12
select minute('2021-12-21 12:13:14');   --13
select second('2021-12-21 12:13:14');   --14

select dayofmonth('2021-12-21');    --21
select weekofyear('2021-12-21');    --51    今年的第几周

select months_between('2021-10-29', '2021-12-28');      ----1.96774194

select add_months('2021-12-22', 1);     --2022-01-22
select datediff('2021-12-22', '2021-12-28');        -- -6   --记住

select date_add('2021-12-22', 1);   --2021-12-23

select date_sub('2021-12-22', 1);--2021-12-21  --记住

select date_format('2021-12-22', 'yyyy年MM月dd日');    --2021年12月22日   --记住

select round(2.5);  --3

select ceil(1.1);   --2

select `floor`(1.9);    --1

select upper('asdfadf');    --ASDFADF
select lower('ASGA');      --asga

select length('  sdf  dfs fsd  ');  --16

select trim('  sdf  dfs fsd  ');    --sdf  dfs fsd 去除字符串前后的空格

select length(trim('  sdf  dfs fsd  '));    --12

select lpad('aaa', 12, '$');    --$$$$$$$$$aaa
select rpad('aaa', 12, '$');    --aaa$$$$$$$$$

select regexp_replace('lqslsdf', 'qsl', 'saf'); --lsafsdf

select regexp_replace('1-2', '(\d+)', 'num');--num-num --使用正则表达式匹配目标字符串，匹配成功后替换

普通查询没有limit语句限制的话会返回多行数据，而limit子句会限制返回的行数

select * from emp_demo limit 5;--返回前五行的数据
select * from emp_demo limit 2,3;--返回从第二条数据开始的往后走三行的数据，即一共返回三行数据