Shulou(Shulou.com)06/03 Report--

Hive data model

There are five types of Hive data tables: internal table, external table, partition table, bucket table, view chart, separated by tab by default

* MySQL (Oracle) tables are separated by commas by default, so if you want to import MySQL (Oracle) data, you need to set a delimiter and add:

Row format delimited fields terminated by','

Internal tables: equivalent to tables in MySQL, save the data to Hive's own data warehouse directory: / usr/hive/warehouse

Example:

Create table emp

(empno int

Ename string

Job string

Mgr int

Hiredate string

Sal int

Comm int

Deptno int

);

Import data into tables: local, HDFS

Load statement, insert statement

The load statement is equivalent to ctrl+X

Load data inpath'/ scott/emp.csv' into table emp;-> Import HDFS

Load data local inpath'/ root/temp/***' into table emp;-> Import local files

Create a table and specify a delimiter

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','

Create department table and save department data

Create table dept

(deptno int

Dname string

Loc string

) row format delimited fields terminated by','

Load data inpath'/ scott/dept.csv' into table dept

External table: relative to the internal table, the data is not in its own data warehouse, only the meta-information of the data is saved.

Example:

(*) Experimental data

[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

(*) definition: (1) the path pointed to by the table structure (2)

Create external table students_ext

(sid int,sname string,age int)

Row format delimited fields terminated by','

Location'/ students'

Partition table: separate the data according to the set conditions to improve query efficiency, partition-> directory

Example:

(*) create a partition based on the employee's department number

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','

Import data into partition table: indicates partition

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

Bucket table: essentially a partition table, similar to hash partition bucket-> file

Example:

Create a bucket table and divide the buckets according to the employee's position 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','

To use a bucket watch, you need to turn on a switch.

Set hive.enforce.bucketing=true

Insert data using subqueries

Insert into emp_bucket select * from emp1

View chart: view chart is a virtual table that does not store data and is used to simplify complex queries

Example:

Query the name of the department and the name of the employee

Create view myview

As

Select dept.dname,emp1.ename

From emp1,dept

Where emp1.deptno=dept.deptno

Select * from myview

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.