Hive和Sqoop测试数据

最新推荐文章于 2023-03-11 12:51:16 发布

转载最新推荐文章于 2023-03-11 12:51:16 发布 · 76 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/wcwen1990/p/6738195.html

文章标签：

#大数据 #数据库

本文介绍如何在MySQL中创建emp和dept表，并完成数据插入；同时提供了Hive数据仓库中创建相同表结构及填充数据的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

测试数据以Oracle数据库自带scott用户emp和dept表为准：

一、MySQL数据库创建的emp和dept表语法及数据：

drop table if exists dept;
create table dept
(
deptno int not null comment '部门编号',
dname varchar(14) comment '部门名称',
loc varchar(13) comment '部门地址',
PRIMARY KEY (deptno)
)default character set 'utf8' comment '部门表';

insert into dept(deptno,dname,loc) values(10,'ACCOUNTING','NEW YORK');
insert into dept(deptno,dname,loc) values(20,'RESEARCH','DALLAS');
insert into dept(deptno,dname,loc) values(30,'SALES','CHICAGO');
insert into dept(deptno,dname,loc) values(40,'OPERATIONS','BOSTON');

----------------------------------------------------------------------------------------------------------
drop table if exists emp;
create table emp
(
empno int not null comment '员工编号',
ename varchar(10) comment '员工姓名',
job varchar(9) comment '职位',
mgr int comment '直接领导',
hiredate date comment '出生日期',
sal float comment '薪水',
comm float comment '奖金',
deptno int comment '所属部门',
primary key (empno),
unique key (ename),
key (empno) using btree,
key (deptno) using btree
)default character set 'utf8' comment '员工表';

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,null,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,null,10);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7839,'KING','PRESIDENT',null,'1981-11-17',5000,null,10);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7900,'JAMES','CLERK',7698,'1981-12-03',950,null,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,null,10);

二、Hive数据仓库创建emp表和dept表语法和数据：

create table chavin.DEPT
(
DEPTNO int,
DNAME string,
LOC string
) row format delimited fields terminated by '\t';

create table chavin.EMP
(
EMPNO    int,
ENAME    string,
JOB      string,
MGR      int,
HIREDATE string,
SAL      float,
COMM     float,
DEPTNO   int
) row format delimited fields terminated by '\t';

----------------------------

10    ACCOUNTING    NEW YORK
20    RESEARCH    DALLAS
30    SALES    CHICAGO
40    OPERATIONS    BOSTON

-----------------------

7369    SMITH    CLERK    7902    1980-12-17    800.00        20
7499    ALLEN    SALESMAN    7698    1981-02-20    1600.00    300.00    30
7521    WARD    SALESMAN    7698    1981-02-22    1250.00    500.00    30
7566    JONES    MANAGER    7839    1981-04-02    2975.00        20
7654    MARTIN    SALESMAN    7698    1981-09-28    1250.00    1400.00    30
7698    BLAKE    MANAGER    7839    1981-05-01    2850.00        30
7782    CLARK    MANAGER    7839    1981-06-09    2450.00        10
7788    SCOTT    ANALYST    7566    1987-04-19    3000.00        20
7839    KING    PRESIDENT        1981-11-17    5000.00        10
7844    TURNER    SALESMAN    7698    1981-09-08    1500.00    0.00    30
7876    ADAMS    CLERK    7788    1987-05-23    1100.00        20
7900    JAMES    CLERK    7698    1981-12-03    950.00        30
7902    FORD    ANALYST    7566    1981-12-03    3000.00        20
7934    MILLER    CLERK    7782    1982-01-23    1300.00        10

----------------------------------------------------------------------------------------------------

转载于:https://www.cnblogs.com/wcwen1990/p/6738195.html