Hive-创建库表测试

Hadoop大数据处理：创建与填充ODS层表

原创已于 2023-11-28 15:53:21 修改 · 1.7k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#hive #hadoop #数据仓库

于 2021-10-29 17:24:33 首次发布

hive 专栏收录该内容

28 篇文章

订阅专栏

该博客展示了如何在ODS层创建`dept`、`emp`和`salgrade`表，并进行数据插入。内容包括数据库的创建、删除，以及使用ROW FORMAT DELIMITED定义字段分隔符和数据存储方式。这些操作对于理解大数据处理中的数据存储和组织至关重要。

drop database if exists `ods`;
create database `ods`;

use ods;
 
create table `dept`(
`deptno` int, 
`dname` varchar(14),
`loc` varchar(13)
) row format delimited fields terminated by '|^|' 
stored as textfile;
 
insert into dept values (10,'accounting','new york'),(20,'research','dallas'),(30,'sales','chicago'),(40,'operations','boston');

create table `emp` (
    `empno` int,
    `ename` varchar(10),  
    `job` varchar(9),  
    `mgr` int,  
    `hiredate` date,  
    `sal` float,  
    `comm` float,  
    `deptno` int
) row format delimited fields terminated by '|^|' 
stored as textfile;


insert into emp values (7369,'smith','clerk',7902,'1980-12-17',800,null,20),
(7499,'allen','salesman',7698,'1981-02-20',1600,300,30),
(7521,'ward','salesman',7698,'1981-02-22',1250,500,30),
(7566,'jones','manager',7839,'1981-04-02',2975,null,20),
(7654,'martin','salesman',7698,'1981-09-28',1250,1400,30),
(7698,'blake','manager',7839,'1981-05-01',2850,null,30),
(7782,'clark','manager',7839,'1981-06-09',2450,null,10),
(7788,'scott','analyst',7566,'1987-07-13',3000,null,20),
(7839,'king','president',null,'1981-11-07',5000,null,10),
(7844,'turner','salesman',7698,'1981-09-08',1500,0,30),
(7876,'adams','clerk',7788,'1987-07-13',1100,null,20),
(7900,'james','clerk',7698,'1981-12-03',950,null,30),
(7902,'ford','analyst',7566,'1981-12-03',3000,null,20),
(7934,'miller','clerk',7782,'1982-01-23',1300,null,10);

create table `salgrade` (  
    `grade` int, 
    `losal` int,  
    `hisal` int
) row format delimited fields terminated by '|^|' 
stored as textfile;


insert into salgrade values (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);