HIVE基本操作

前提：虚拟机安装HIVE,并连接。

hive启动hive thrift服务端，使用命令

启动为前台，我们需要再打开一个终端运行以下命令：

DG连接hive

基础操作

显示所有库
show databases;
创造库
create database hive_dev;
删除无数据库
drop database hive_test;
强制删除有数据库
drop database hive_test casecade;
查看库
desc database hive_dev;
使用库
use hive_dev;

 DDL操作

建表

create table student
(
  id int comment '学生主键',
  stu_name string 
)comment '学生表'
查看表
show tables;
desc student;
插入数据
insert into student values (1,'zhangsan'),(2,'lisi'),(3,'wangwu');
查询数据
select * from student;

内部传输数据操作 

create table student_inner
(
  id int comment '学生主键',
  stu_name string
)comment '学生表'
 row format delimited fields terminated by '|'
 stored as textfile;
装载数据
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename 
查询数据
select * from student_inner;

外部传输数据 

create table student_outer
(
  id int comment '学生主键',
  stu_name string
)comment '学生表'
 row format delimited fields terminated by '|'
 stored as textfile location '传入路径';
 装载数据
 LOAD DATA INPATH '数据路径' INTO TABLE student_outer;
 查询数据
 select * from syudent_outer;

分区-partitioned 

create table student_outer_pd
(
    id int comment '学生主键',
    stu_name string
)comment '学生表' partitioned by (pd string)
 row format delimited fields terminated by '|'
 stored as textfile location '传入路径';
 
LOAD DATA LOCAL INPATH '/root/stu.txt' INTO TABLE student_outer_pd partition (pd='202401');--自行设置
LOAD DATA LOCAL INPATH '/root/stu1.txt' INTO TABLE student_outer_pd partition (pd='202402');
LOAD DATA LOCAL INPATH '/root/stu2.txt' INTO TABLE student_outer_pd partition (pd='202403');
查询数据
select * from student_outer_pd where pd='202401';