Hive DDL DML

向右看齐 2024-02-19 17:34 50阅读 0赞

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

### 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][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;

[https_cwiki.apache.org_confluence_display_Hive_Configuration_Properties]: https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties