数据库基本操作database
数据库模式定义语言DDL(Data Definition Language)是用于描述数据库中要存储的现实世界实体的语言
create database if not exists 数据库名; //创建数据库
show databases; //展示数据库
use 数据库名; //切换数据库
describe database 数据库名; //查看数据库更多描述信息
alter database 数据库名 set owner user root; //更改数据库用户
drop database if exists 数据库名 cascade; //强制删除数据库
1.新建数据库
语法：
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name --DATABASE|SCHEMA 是等价的
[COMMENT database_comment] --数据库注释
[LOCATION hdfs_path] --存储在 HDFS 上的位置
[WITH DBPROPERTIES (property_name=property_value, ...)]; --指定额外属性
示例：
CREATE DATABASE IF NOT EXISTS hive_test
COMMENT 'hive database for test'
WITH DBPROPERTIES ('create'='heibaiying');
2.使用数据库
USE database_name;
3.查看数据库信息
语法：
DESC DATABASE [EXTENDED] db_name; --EXTENDED 表示是否显示额外属性
示例：
DESC DATABASE EXTENDED hive_test;
4.删除数据库
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
默认行为是 RESTRICT，如果数据库中存在表则删除失败。要想删除库及其中的表，可以使用 CASCADE 级联删除。
DROP DATABASE IF EXISTS hive_test CASCADE;
新建数据表基本操作
1.建表语法
建表语法如下所示：
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name --表名
[(col_name data_type [COMMENT col_comment],
... [constraint_specification])] --列名 列数据类型
[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
] --分桶表分桶规则
[SKEWED BY (col_name, col_name, ...) ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
[STORED AS DIRECTORIES]
] --指定倾斜列和值
[
[ROW FORMAT row_format]
[STORED AS file_format]
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
] -- 指定行分隔符、存储文件格式或采用自定义存储格式
[LOCATION hdfs_path] -- 指定表的存储位置
[TBLPROPERTIES (property_name=property_value, ...)] --指定表的属性
[AS select_statement]; --从查询结果创建表
创建数据表解释说明：
1、 CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。hive中的表可以分为内部表（托管表）和外部表，区别在于，外部表的数据不是有hive进行管理的，也就是说当删除外部表的时候，外部表的数据不会从hdfs中删除。而内部表是由hive进行管理的，在删除表的时候，数据也会删除。一般情况下，我们在创建外部表的时候会将表数据的存储路径定义在hive的数据仓库路径之外。hive创建表主要有三种方式，第一种直接使用create table命令，第二种使用create table ... as select...(会产生数据)。第三种使用create table tablename like exist_tablename命令。
2、 EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
3、 LIKE 允许用户复制现有的表结构，但是不复制数据。
4、 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 确定表的具体的列的数据。
5、 STORED AS
SEQUENCEFILE | TEXTFILE | RCFILE
如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。
如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
6、CLUSTERED BY
对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
把表（或者分区）组织成桶（Bucket）有两个理由：
（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。
（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。
7、create table命令介绍2
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name] table_name LIKE existing_table_orview_name ---指定要创建的表和已经存在的表或者视图的名称。
[LOCATION hdfs_path] ---数据文件存储的hdfs文件地址信息。
8、CREATE [EXTERNAL] TABLE [IF NOT EXISTS]
[db_Name] table_name ---指定要创建的表名称
...指定partition&bucket等信息，指定数据分割符号。
[AS select_statement] ---导入的数据
示例：
create table if not exists student(
id int, name string
)
row format delimited fields terminated by '\t'
stored as textfile
location '/home/hadoop/hive/warehouse/student';
desc formatted 表名; //查询表的类型
desc 表名; //查询表结构
（1）建表语句解析
//IF NOT EXISTS可选，如果表存在，则忽略
CREATE EXTERNAL TABLE IF NOT EXISTS employee_external (
//列出所有列和数据类型
name string,
work_place ARRAY<string>,
sex_age STRUCT<sex:string,age:int>,
skills_score MAP<string,int>,
depart_title MAP<STRING,ARRAY<STRING>>
)
//COMMENT可选
COMMENT 'This is an external table'
//如何分隔列（字段）
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
//如何分隔集合和映射
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
//文件存储格式
STORED AS TEXTFILE
//数据存储路径（HDFS)
LOCATION '/home/hadoop/hive/warehouse/employee';
（2）分隔符
指定列分隔符语法
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
2.内部表
CREATE TABLE emp(
empno INT,
ename STRING,
job STRING,
mgr INT,
hiredate TIMESTAMP,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
3.外部表
CREATE EXTERNAL TABLE emp_external(
empno INT,
ename STRING,
job STRING,
mgr INT,
hiredate TIMESTAMP,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
LOCATION '/hive/emp_external';
使用 desc format emp_external 命令可以查看表的详细信息如下：
4.分区表
CREATE EXTERNAL TABLE emp_partition(
empno INT,
ename STRING,
job STRING,
mgr INT,
hiredate TIMESTAMP,
sal DECIMAL(7,2),
comm DECIMAL(7,2)
)
PARTITIONED BY (deptno INT) -- 按照部门编号进行分区
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
LOCATION '/hive/emp_partition';
5. 分桶表
CREATE EXTERNAL TABLE emp_bucket(
empno INT,
ename STRING,
job STRING,
mgr INT,
hiredate TIMESTAMP,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT)
CLUSTERED BY(empno) SORTED BY(empno ASC) INTO 4 BUCKETS --按照员工编号散列到四个 bucket 中
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
LOCATION '/hive/emp_bucket';
6.倾斜表
通过指定一个或者多个列经常出现的值（严重偏斜），Hive 会自动将涉及到这些值的数据拆分为单独的文件。在查询时，如果涉及到倾斜值，它就直接从独立文件中获取数据，而不是扫描所有文件，这使得性能得到提升。
CREATE EXTERNAL TABLE emp_skewed(
empno INT,
ename STRING,
job STRING,
mgr INT,
hiredate TIMESTAMP,
sal DECIMAL(7,2),
comm DECIMAL(7,2)
)
SKEWED BY (empno) ON (66,88,100) --指定 empno 的倾斜值 66,88,100
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
LOCATION '/hive/emp_skewed';
7.临时表
1.临时表是应用程序自动管理在复杂查询期间生成的中间数据的方法。
2.临时表仅对当前 session 可见，临时表的数据将存储在用户的暂存目录中，表空间位于/tmp/hive-<user_name>(安全考虑)，并在会话结束后删除。
3.如果临时表与永久表表名相同，则对该表名的任何引用都将解析为临时表，而不是永久表。
4.临时表还具有以下两个限制：
CREATE TEMPORARY TABLE emp_temp(
empno INT,
ename STRING,
job STRING,
mgr INT,
hiredate TIMESTAMP,
sal DECIMAL(7,2),
comm DECIMAL(7,2)
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
或者：
CREATE TEMPORARY TABLE tmp_table_name1 (c1 string); //常规的创建方式
CREATE TEMPORARY TABLE tmp_table_name2 AS SELECT * FROM employee; //as的方式 CTAS
CREATE TEMPORARY TABLE tmp_table_name3 LIKE employee; //like的方式
示例：
create temporary table if not exist employee_tmp(
name string,
id int,
address array<string>,
info struct<gender:string,age:int>,
workAndSal map<string,int>,
jobAndRole map<string,array<string>>)
查看是否是临时表的方式：
1.查看建表语句，
2.临时表是存在一个临时目录下的，在之前的数据库目录下是找不到它的。
3.查看元数据信息： desc formatted employee_tmp; location稍微能看出来，表类型还是管理表（只要没加external的表），不太能看得出来，除非加一个描述信息，说明这个表是一个临时表。
4.退出会话再重新进入 临时表会被删除
使用场景：
原始的几张表/日志信息，订单信息，从表中做分析，可以先把表查一部分出来做关联，把结果存到临时表里。如果后面需要中间的数据的时候可以从里面去取，全部过程结束了之后，这个临时表就被删除掉了。一个复杂的查询语句用嵌套的子查询，逻辑上会非常复杂，可以把一部分子查询的结果先存到临时表里。
8.CTAS创建表（复制表）
注意：CTAS不能创建partition, external, bucket table
支持从查询语句的结果创建表：
CREATE TABLE emp_copy AS SELECT * FROM emp WHERE deptno='20';
CREATE TABLE ctas_employee as SELECT * FROM employee;
这种方式创建表的时候会把表里的数据同样拿过来。
示例：
create table ctas_employee
as select * from employee_external;
(2)CTE建表(CTAS with Common Table Expression)
相当于简化了子查询的方式，逻辑更清晰一些，和上面很相似，只是写法不一样
示例：
CREATE TABLE cte_employee AS
WITH
r1 AS (SELECT name FROM r2 WHERE name = 'Michael'),
r2 AS (SELECT name FROM employee WHERE sex_age.sex= 'Male'),
r3 AS (SELECT name FROM employee WHERE sex_age.sex= 'Female')
SELECT * FROM r1 UNION ALL SELECT * FROM r3; //union all 合并两张表
9 复制表结构
使用like关键字的方式创建表，只是复制表的结构信息，但是数据没有写进来
语法：
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name --创建表表名
LIKE existing_table_or_view_name --被复制表的表名
[LOCATION hdfs_path]; --存储位置
示例：
CREATE TEMPORARY EXTERNAL TABLE IF NOT EXISTS emp_co LIKE emp
CREATE TABLE employee_like LIKE employee;
10 加载数据到表
加载数据到表中属于 DML 操作，这里为了方便大家测试，先简单介绍一下加载本地数据到表中：
两个关键字：
local：指定文件位于本地文件系统，执行后为拷贝数据
没有local关键字，表示文件位于hdfs文件系统中，执行后为直接移动数据到表的目录下
overwrite：表示覆盖表中现有数据，不使用overwrite会追加
load data [local] inpath "Linux路径" [overwrite] into table 表名;
-- 加载数据到 emp 表中(追加)
load data local inpath "/usr/file/emp.txt" into table emp;
-- 本地加载数据(覆盖)
LOAD DATA LOCAL INPATH '/home/dayongd/Downloads/employee.txt'
OVERWRITE INTO TABLE employee;
-- 加LOCAL关键字，表示原始文件位于Linux本地，执行后为拷贝数据
LOAD DATA LOCAL INPATH '/home/dayongd/Downloads/employee.txt'
OVERWRITE INTO TABLE employee_partitioned PARTITION (year=2014, month=12);
-- 没有LOCAL关键字，表示文件位于HDFS文件系统中,执行后为直接移动数据
LOAD DATA INPATH '/tmp/employee.txt'
OVERWRITE INTO TABLE employee_partitioned PARTITION (year=2017, month=12);
LOCAL：指定文件位于本地文件系统，执行后为拷贝数据
OVERWRITE：表示覆盖表中现有数据
修改表（alter针对元数据）
表的删除和修改使用情况不多。
//修改表名
ALTER TABLE employee RENAME TO new_employee;
//修改属性
ALTER TABLE c_employee SET TBLPROPERTIES ('comment'='New name, comments');
//修改分隔符
ALTER TABLE employee_internal SET SERDEPROPERTIES ('field.delim' = '$');
//修正表文件格式
ALTER TABLE c_employee SET FILEFORMAT RCFILE;
//修改表的列操作
ALTER TABLE employee_internal CHANGE old_name new_name STRING; -- 修改列名
ALTER TABLE c_employee ADD COLUMNS (work string); -- 添加列
ALTER TABLE c_employee REPLACE COLUMNS (name string); -- 替换列
前面所有的修改操作都是alter table的方式，不是重点，在实际使用过程中一般不会修改表，正常做法会先把表删除再重新建。
-- 删除一个表（重点需要掌握）
drop table if exists like_employee; //if exists 在执行脚本的时候尽量避免ERROR的情况
1.重命名表
语法：
ALTER TABLE table_name RENAME TO new_table_name;
示例：
ALTER TABLE emp_temp RENAME TO new_emp; --把 emp_temp 表重命名为 new_emp
2.修改列
语法：
ALTER TABLE table_name [PARTITION partition_spec] CHANGE [COLUMN] col_old_name col_new_name column_type
[COMMENT col_comment] [FIRST|AFTER column_name] [CASCADE|RESTRICT];
示例：
-- 修改字段名和类型
ALTER TABLE emp_temp CHANGE empno empno_new INT;
-- 修改字段 sal 的名称 并将其放置到 empno 字段后
ALTER TABLE emp_temp CHANGE sal sal_new decimal(7,2) AFTER ename;
-- 为字段增加注释
ALTER TABLE emp_temp CHANGE mgr mgr_new INT COMMENT 'this is column mgr';
3 新增列
ALTER TABLE emp_temp ADD COLUMNS (address STRING COMMENT 'home address');
清空表/删除表
1.清空表
语法：
-- 清空整个表或表指定分区中的数据
TRUNCATE TABLE table_name [PARTITION (partition_column = partition_col_value, ...)];
目前只有内部表才能执行 TRUNCATE 操作，外部表执行时会抛出异常 `Cannot truncate non-managed table XXXX`。
示例：
TRUNCATE TABLE emp_mgt_ptn PARTITION (deptno=20);
2.删除表
语法：
//删除表（[PURGE]直接删除（可选），否则会放到 .Trash目录）一般配置后会默认保留7天
DROP TABLE [IF EXISTS] table_name [PURGE];
内部表：不仅会删除表的元数据，同时会删除 HDFS 上的数据；
外部表：只会删除表的元数据，不会删除 HDFS 上的数据；
删除视图引用的表时，不会给出警告（但视图已经无效了，必须由用户删除或重新创建）。
Hadoop 回收站 trash
未指定 purge 时，当删除表时，将从元数据存储中删除表信息，并像通过hadoop dfs -rm 那样删除原始数据。
强烈建议用户不要随意删除表。
在许多情况下，这会导致表数据被移动到用户主目录中的 .Trash/Current 文件夹中
（/user/root/.Trash/Current/home/hadoop/hive/warehouse/test1.db）；
因此，错误删除表的用户可以通过使用相同的模式重新创建表、重新创建任何必要的分区，然后使用 Hadoop 手动将数据移回原位，从而恢复丢失的数据。
此解决方案依赖于底层实现，因此可能随时间或跨安装发生更改；
Hadoop 回收站 trash，默认是关闭的，生产环境建议打开，删除后会进入到该目录。
在hdfs-site.xml文件中添加相关配置：
<property>
<name>fs.trash.interval</name>
<value>1440</value>
<description>Number of minutes between trash checkpoints.If zero, the trash feature is disabled. </description>
</property>
其他命令
1.Describe
查看数据库：
DESCRIBE|Desc DATABASE [EXTENDED] db_name; --EXTENDED 是否显示额外属性
查看表：
DESCRIBE|Desc [EXTENDED|FORMATTED] table_name --FORMATTED 以友好的展现方式查看表详情
2.Show
1. 查看数据库列表
-- 语法
SHOW (DATABASES|SCHEMAS) [LIKE 'identifier_with_wildcards'];
-- 示例：
SHOW DATABASES like 'hive*';
LIKE 子句允许使用正则表达式进行过滤，但是 SHOW 语句当中的 LIKE 子句只支持 *（通配符）和 |（条件或）两个符号。例如 employees，emp *，emp * | * ees，所有这些都将匹配名为 employees 的数据库。
2. 查看表的列表
-- 语法
SHOW TABLES [IN database_name] ['identifier_with_wildcards'];
-- 示例
SHOW TABLES IN default;
3. 查看视图列表
SHOW VIEWS [IN/FROM database_name] [LIKE 'pattern_with_wildcards']; --仅支持 Hive 2.2.0 +
4. 查看表的分区列表
SHOW PARTITIONS table_name;
5. 查看表/视图的创建语句
SHOW CREATE TABLE ([db_name.]table_name|view_name);
创建视图:
create view view_hr as select name,id from employee_hr where id >10;
在使用的时候和表没有区别，只是没有装载数据
判断是否是视图：
show create table view_hr;
删除视图：
drop view view_hr;
hive侧视图：
和视图有些区别，关系不是很大，只是使用了视图的概念，称为侧视图其实不是视图。
侧视图是在查询的时候，通过lateral view的方式，在查询的过程中相当于视图的概念，并没有去创建这个视图。
一般会和表生成函数结合使用，将这个函数的输入和输出连接在一块，比如explode
一般在行转列，列转行会使用表生成函数。
举例：
1,(“beijing”,“nanjing”),job
想转换成2行：
(1,beijing,job)
(1,nanjing,job)
写法很固定，一般就这么写：
select name,wps,skill,score from employee
lateral view explode(work_place) work_place as wps
lateral view explode(skills_score) sks as skill,score;
使用split切割开来就是一个数组，array数据类型用的比较少，可以用split按分割符切割，达到的效果是一样的。很多处理字符串的函数可以让我们不去使用array map这些数据类型也能完成相关操作，
一般使用侧视图来规范行或解析json
便于分析 使用的还是比较多的
使用lateral view explode
select
name,
wps,skills,score
from employee
lateral view explode(work_place) workplace as wps //把work_place这一列传进来，因为本身就是一个数组,后面没有逗号
//把skills_score这一列也展开以下 是个数组的某一列转成了多行
lateral view explode(skills_score) sks as skills,score;
