Hive 數(shù)據(jù)模型

                                                                                Hive 數(shù)據(jù)模型

    Hive 數(shù)據(jù)表有五種類型：內(nèi)部表，外部表，分區(qū)表，桶表，視圖表，默認(rèn)以 tab 分隔

    * MySQL (Oracle) 表默認(rèn)以逗號分隔，因此，要想導(dǎo)入 MySQL(Oracle) 數(shù)據(jù)，需要設(shè)置分隔符，在建表語句后加：

            row  format   delimited   fields   terminated  by  ',';

    內(nèi)部表： 相當(dāng)于 MySQL 中的表，將數(shù)據(jù)保存到Hive 自己的數(shù)據(jù)倉庫目錄中：/usr/hive/warehouse

            例子：

create table emp

(empno int,

ename string,

job string,

mgr int,

hiredate string,

sal int,

comm int,

deptno int

);

導(dǎo)入數(shù)據(jù)到表中：本地、HDFS

load語句、insert語句

load語句相當(dāng)于ctrl+X

load data inpath '/scott/emp.csv' into table emp;   ----> 導(dǎo)入HDFS

load data local inpath '/root/temp/***' into table emp;   ----> 導(dǎo)入本地文件

創(chuàng)建表，并且指定分隔符

create table emp1

(empno int,

ename string,

job string,

mgr int,

hiredate string,

sal int,

comm int,

deptno int

)row format delimited fields terminated by ',';

創(chuàng)建部門表，保存部門數(shù)據(jù)

create table dept

(deptno int,

dname string,

loc string

)row format delimited fields terminated by ',';

load data inpath '/scott/dept.csv' into table dept;

    外部表：相對于內(nèi)部表，數(shù)據(jù)不在自己的數(shù)據(jù)倉庫中，只保存數(shù)據(jù)的元信息

        例子：

（*）實驗的數(shù)據(jù)

[root@bigdata11 ~]# hdfs dfs -cat /students/student01.txt

1,Tom,23

2,Mary,24

[root@bigdata11 ~]# hdfs dfs -cat /students/student02.txt

3,Mike,26

（*）定義：（1）表結(jié)構(gòu)  （2）指向的路徑

create external table students_ext

(sid int,sname string,age int)

row format delimited fields terminated by ','

location '/students';

    分區(qū)表：將數(shù)據(jù)按照設(shè)定的條件分開存儲，提高查詢效率，分區(qū)----->  目錄

        例子：

（*）根據(jù)員工的部門號建立分區(qū)

create table emp_part

(empno int,

ename string,

job string,

mgr int,

hiredate string,

sal int,

comm int

)partitioned by (deptno int)

row format delimited fields terminated by ',';

往分區(qū)表中導(dǎo)入數(shù)據(jù)：指明分區(qū)

insert into table emp_part partition(deptno=10) select empno,ename,job,mgr,hiredate,sal,comm from emp1 where deptno=10;

insert into table emp_part partition(deptno=20) select empno,ename,job,mgr,hiredate,sal,comm from emp1 where deptno=20;

insert into table emp_part partition(deptno=30) select empno,ename,job,mgr,hiredate,sal,comm from emp1 where deptno=30;

    桶  表： 本質(zhì)上也是一種分區(qū)表，類似 hash 分區(qū)   桶 ----> 文件

        例子：

  創(chuàng)建一個桶表，按照員工的職位job分桶

create table emp_bucket

(empno int,

ename string,

job string,

mgr int,

hiredate string,

sal int,

comm int,

deptno int

)clustered by (job) into 4 buckets

row format delimited fields terminated by ',';

使用桶表，需要打開一個開關(guān)

set hive.enforce.bucketing=true;

使用子查詢插入數(shù)據(jù)

insert into emp_bucket select * from emp1;

    視圖表：視圖表是一個虛表，不存儲數(shù)據(jù)，用來簡化復(fù)雜的查詢

        例子：

                                查詢部門名稱、員工的姓名

create view myview

as

select dept.dname,emp1.ename

from emp1,dept

where emp1.deptno=dept.deptno;

select * from myview;