Hive DDL DML

方法和方式要学习 一定要举一反三

DDL: Data Definition Language

    数据   定义  语言
create delete drop alter关键字开头的

Database

hive中的database 对应HDFS上的一个文件夹
hive启动默认自带一个default数据库
hive上默认数据库存放位置hadoop的位置：
hadoop fs -ls /user/hive/warehouse
这个位置是由参数决定的：hive.metastore.warehouse.dir
Hive所有参数的查询：https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties
在hive主页有个Hive Configuration Properties
在Hive里面查看指定配置参数的语法：
>set hive.metastore.warehouse.dir;
set key;
你要设置参数： set key=value;

创建数据库 括号里面竖直线表示2者选择其一 中括号表示可有可无
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name 数据库名称

 [COMMENT database_comment]
  [LOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, ...)];

>CREATE DATABASE IF NOT EXISTS d5_hive;
非default的存放路径就是这个参数：${hive.metastore.warehouse.dir}/dbname.db

CREATE DATABASE IF NOT EXISTS d5_hive_2
COMMENT 'this is ruozedata d5'  这是备注信息
WITH DBPROPERTIES ('creator'='ruoze', 'date'='20181020');相关属性信息

show database；
在show的时候也接受模糊匹配 s
how database like 'd5*’; 这里比较模糊，

desc database d5_hive；查看之前的详细信息，全屏猜，所以工作当中不用他
desc database extended d5_hive;
在hive当中后面是否跟数据库的显示
hive（default）>
是在hive.site 里面加配置

<property>
	<name>hive.cli.print.current.db</name>
	<value>true</value>
</property>

修改的语法

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);   -- (Note: SCHEMA added in Hive 0.14.0)
 
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;   -- (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0)                                           用户和角色
  
ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path; -- (Note: Hive 2.2.1, 2.4.0 and later)
                                                                                 

  


alter detabase d5_hive set dbproperties (‘edited-by’=‘jeson’);
desc database extended d5-hive;

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE]; 级联
在删除数据库的时候，如果只空的直接可以删掉，
如果数据库里面有表的话，删除数据库会有提醒。删除不了，你可以先把表删除掉。
然后在删除数据库。不过呢
这里也有更加暴力的
drop database d5_hive;
在这个基础上后面加个cascade 就Ok了
在生产上百分之百不要使用的，

cascade: hibernate/jpa
1对多的时候，你删除1的一端是否删除多的一端

表的创建

create table xx(id int);

数值类型：int bigint float double
字符串： string <= date time 这个不好用 ，并且容易出现问题，转不过来，除了特殊的，就用万能的string
20181020

t:date time - string
boolean 类型站空间大。因为他返回的是ture和flase
where flag=true  0使用0好，0占用空间好

Hive构建在Hadoop之上
hive创建表，然后数据是存储在HDFS之上
hdfs上的文件：zhangsan,20,m,beijing
创建的表： name age gender location

所以创建表的时候要指定分隔符（默认分隔符是\001 ^A)
工作当中用的最多分隔符是 空格、制表符(\t)

CREATE TABLE ruoze_emp_2 (
empno int, 
ename string, 
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
ROW FORMAP DELIMITED FILELDS TERMINATED BY '\t'
;

load data local inpath '/home/hadoop/data/emp.txt' overwrite into table ruoze_emp; 

$$$$$
hive在创建分隔符的时候如果不是char怎么办

row_format

: DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char]   -- (Note: Available in Hive 0.13 and later)
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

最简单的创建一张表的模式：

CREATE TABLE table_name (
col_name data_type,
col_name data_type,
col_name data_type,)
ROW FORMAP DELIMITED FILELDS TERMINATED BY '\t'
LINES TERMINATES BY '\n'  行与行的分隔符hive中可以默认不用的，
;

如果分隔符不对，你在看你的表的时候就全是 null

desc xxx 看表的结构

create table ruoze_emp like ruzoe ;     拷贝表的结构

desc formatted ruoze_emp; 可以清晰的看到这张表的信息，有这张表在hdfs的存在路径 工作当中用的

create table ruoze_emp_managed as select * from ruoze_emp;  拷贝全部

更改表

ALTER TABLE table_name RENAME TO new_table_name;

删除

drop  table  table_name ;

这里的更改和删除将会hadoop上面的一致更改

内部表和外部表
MANAGED_TABLE
被hive所管理的一张表叫做内部表

create table ruoze_emp_managed as select * from ruoze_emp; 拷贝全部

这是创建了一张内部表：HDFS MySQL 都有数据
在hive里面 对于内部表做删除处理的时候 hadf和mysql都会没有

external为外部表  创建外部表 	
CREATE EXTERNAL TABLE ruoze_emp_external (
empno int, 
ename string, 
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/ruoze_emp_external'我们这里可以指定路径
;	

外部表的路径是可以随意设置的，你也可以在hdfs上面找到这路径，之后直接hadoop fs -put 文件到这里路径
这样在hive上也可以看到

这是在drop table table_name ;的时候
hive和mysql上的数据没有了，而hdfs上的表还存在

DML : Data Manipulation Language

LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 …)]

创建一张表

create table ruoze_dept(
deptno int,
dname string,
loc string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
 

LOAD DATA LOCAL INPATH '/home/hadoop/data/dept.txt' INTO TABLE ruoze_dept;
 加载数据       本地
OVERWRITE  带上overwrite 表示数据重写，  如果不带就会在之前数据的基础上累加，这是你执行几次的上面的语句

LOAD DATA INPATH ‘/data/dept.txt’ INTO TABLE ruoze_dept;
hdfs上面上传 的路径
在这个过程中，相当于把hdfs上的文件mv到这里了，这是要注意的，而本地文件不会

Standard syntax:

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;
INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;

插入数据

INSERT OVERWRITE TABLE ruoze_emp_test select empno,ename from ruoze_emp; 

字段的列数要对上 这里要写对应的字段就可以了 问题如果字段对应不上会报错
但是如果插入的字段顺序错了，是可以运行的，但是数据可以看出来，
不过数据是错位的 这种是最可怕的，这里一定要慎重

INSERT OVERWRITE TABLE ruoze_emp_test 
select empno,job,ename,mgr,hiredate,sal,comm,deptno from ruoze_emp; 

写到本地系统

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/ruoze'                directory
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
SELECT empno,ename FROM ruoze_emp;