Hive2.x学习笔记(3)-基本操作
1. 基本命令
- 启动hive
[hadoop@hadoop100 ~]$ hive
- 查看数据库
hive> show databases;
- 打开数据库
hive> use default;
- 显示数据库中的表
hive> show tables;
- 创建一张表
hive> create table emp(empno int, name string);
- 查看表的结构
hive> desc emp;
- 添加数据到表中
hive> insert into emp values(7369, 'SMITH');
- 查询表中数据
hive> select * from emp;
- 删除表
hive> drop table emp;
- 退出hive
hive> quit;
2. 将文件数据导入Hive
- 准备数据文件
创建文件emp.txt,并添加数据
vi ~/emp.txt
7369,SMITH,CLERK,7902,17-DEC-1980,800,NULL,20
7499,ALLEN,SALESMAN,7698,20-FEB-1981,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-1981,1250,500,30
7566,JONES,MANAGER,7839,2-APR-1981,2975,NULL,20
7654,MARTIN,SALESMAN,7698,28-SEP-1981,1250,1400,30
7698,BLAKE,MANAGER,7839,1-MAY-1981,2850,NULL,30
7782,CLARK,MANAGER,7839,9-JUN-1981,2450,NULL,10
7788,SCOTT,ANALYST,7566,09-DEC-1982,3000,NULL,20
7839,KING,PRESIDENT,NULL,17-NOV-1981,5000,NULL,10
7844,TURNER,SALESMAN,7698,8-SEP-1981,1500,0,30
7876,ADAMS,CLERK,7788,12-JAN-1983,1100,NULL,20
7900,JAMES,CLERK,7698,3-DEC-1981,950,NULL,30
7902,FORD,ANALYST,7566,3-DEC-1981,3000,NULL,20
7934,MILLER,CLERK,7782,23-JAN-1982,1300,NULL,10
- 创建emp表,并声明“,”为内容的分隔符
hive> create table emp(empno int, ename string, job string, mgr int, hiredate date, sal float, comm float, deptno int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
- 加载文件数据到emp表
hive> load data local inpath '/home/hadoop/emp.txt' into table emp;
- 查询结果
hive> select * from emp;
OK
7369 SMITH CLERK 7902 NULL 800.0 NULL 20
7499 ALLEN SALESMAN 7698 NULL 1600.0 300.0 30
7521 WARD SALESMAN 7698 NULL 1250.0 500.0 30
7566 JONES MANAGER 7839 NULL 2975.0 NULL 20
7654 MARTIN SALESMAN 7698 NULL 1250.0 1400.0 30
7698 BLAKE MANAGER 7839 NULL 2850.0 NULL 30
7782 CLARK MANAGER 7839 NULL 2450.0 NULL 10
7788 SCOTT ANALYST 7566 NULL 3000.0 NULL 20
7839 KING PRESIDENT NULL NULL 5000.0 NULL 10
7844 TURNER SALESMAN 7698 NULL 1500.0 0.0 30
7876 ADAMS CLERK 7788 NULL 1100.0 NULL 20
7900 JAMES CLERK 7698 NULL 950.0 NULL 30
7902 FORD ANALYST 7566 NULL 3000.0 NULL 20
7934 MILLER CLERK 7782 NULL 1300.0 NULL 10
Time taken: 0.215 seconds, Fetched: 14 row(s)
3. 常用交互命令
- "-e"不进入hive的交互窗口执行sql语句
[hadoop@hadoop100 ~]$ hive -e 'select ename from emp;'
- "-f"执行脚本中sql语句
[hadoop@hadoop100 ~]$ hive -f selectname.sql
- 执行文件中的sql语句并将结果写入文件
[hadoop@hadoop100 ~]$ hive -f selectname.sql > selectname_result.txt