离线数仓-Hive 交互命令、DDL与DML操作

最新推荐文章于 2022-02-20 22:50:44 发布

.Mr Zhang

最新推荐文章于 2022-02-20 22:50:44 发布

阅读量164

点赞数

分类专栏： Hive

本文链接：https://blog.csdn.net/zhangtikang134/article/details/104378863

版权

Hive 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

常用命令

###交互命令
#查看帮助
bin/hive -help

# -e 不进入hive提示窗口执行sql语句
bin/hive -e "select id from student;"

# -f 执行脚本中sql语句
bin/hive -f /opt/module/datas/hivef.sql

###hive提示窗口命令
#进入提示窗口
bin/hive

#在hive提示窗口命令中如何查看hdfs文件系统
hive(default)>dfs -ls /;

#在hive提示窗口命令中如何查看本地文件系统
hive(default)>! ls /opt/module/datas;

#退出提示窗口
hive(default)>exit;
hive(default)>quit;

DDL数据定义

定义数据库

1.创建数据库

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, ...)];

2.删除数据库

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

若数据库不为空，使用CASCADE强制删除。
3.修改数据库

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);   -- (Note: SCHEMA added in Hive 0.14.0)
 
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;   -- (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0)
  
ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path; -- (Note: Hive 2.2.1, 2.4.0 and later)

4.查看数据库

#显示数据库
show databases;
show databases like 'hive*';

#显示数据库信息
desc database db_hive;
#显示数据库详细信息
desc database extended db_hive;

#切换数据库
use db_hive;

定义表

内部表（Managed Tables）：默认创建的表是内部表，Hive会控制数据的生命周期。默认情况下，将这些表数据存储在配置项hive.metastore.warehouse.dir指向的目录下。当删除一个内部表时，Hive会一起删除表元数据与表中数据。
外部表（External Tables）：创建的外部表，Hive认为自己不完全拥有对应的外部数据。删除该表时，不删除数据，只删除描述表的元数据。

内部表不适合和其他工具共享数据，一般使用内部表来作为中间结果表，通过SELECT + INSERT将数据写入内部表。

1.创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
  [COMMENT table_comment]
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
  [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)

EXTERNAL：创建一个外部表，与此同时指定一个指向数据路LOCATION
COMMENT：为表和列添加注释。
PARTITIONED BY：创建分区表
CLUSTERED BY：创建分桶表
SORTED BY：不常用，对分桶中的列另外排序
ROW FORMAT：指定数据行格式row_format，如下：

DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char]   -- (Note: Available in Hive 0.13 and later)
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

STORED AS：指定存储文件类型
比如 SEQUENCEFILE、TEXTFILE、RCFILE、ORC、PARQUET、AVROJSONFILE
LOCATION：指定表数据在HDFS上的存储位置。
AS：后跟查询语句，根据查询结果创建表。
LIKE：允许用户复制现有的表结构，但不复制数据。

举例：
创建一个普通内部表

create table if not exists student(
id int, 
name string
)
row format delimited fields terminated by '\t'
stored as textfile
location '/user/hive/warehouse/student';

创建一个普通外部表

create external table stu_external(
id int, 
name string
) 
row format delimited fields terminated by '\t' 
location '/student';

查看表格式类型以及内部表与外部表的转换

#查看表格式类型
desc formatted student;

#内部表转外部表
alter table student set tblproperties('EXTERNAL'='TRUE');
#外部表转内部表
alter table student set tblproperties('EXTERNAL'='FALSE');

3.重命名表

alter table student rename to student_new ;

4.修改列

#查询表结构
desc student;

#新增列
alter table student add columns(className string);

#更新列
alter table student change column className className_new int;

#替换所有列
alter table student replace columns(did int, dname string);

5.删除表

drop table student ;

6.分区表
分区表实际上对应一个HDFS独立目录，该目录下是该分区所有的数据文件。

#创建分区表
create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (month string)
row format delimited fields terminated by '\t';

#加载数据到分区表
load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201909');
load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201910');

#查询单个分区
select * from dept_partition where month='201909';
#查询多个分区
select * from dept_partition where month='201909'
union 
select * from dept_partition where month='201910'

#新增分区
alter table dept_partition add partition (month='201911') partition (month='201912');
#删除分区
alter table dept_partition drop partition (month='201911'), partition (month='201912');

#查看表的分区
show partitions dept_partition;
#查看分区表结构
desc formatted dept_partition;

分区表和数据产生关联的三种方式：

###上传数据后修复
#构建分区目录
dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=12;
#上传数据
dfs -put /opt/module/datas/dept.txt  /user/hive/warehouse/dept_partition2/month=201709/day=12;
#查询数据（查询不到刚上传的数据）
select * from dept_partition2 where month='201709' and day='12';
#执行修复命令
msck repair table dept_partition2;
#再次查询数据
select * from dept_partition2 where month='201709' and day='12';

###上传数据后添加分区
#构建分区目录
dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=11;
#上传数据
dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept_partition2/month=201709/day=11;
#执行添加分区
alter table dept_partition2 add partition(month='201709',
 day='11');
#查询数据
select * from dept_partition2 where month='201709' and day='11';

###创建文件夹后load本地数据到分区
#创建目录
dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=10;
#上传数据
load data local inpath '/opt/module/datas/dept.txt' into table
 dept_partition2 partition(month='201709',day='10');
#查询数据
select * from dept_partition2 where month='201709' and day='10';

DML数据操作

向表中装载数据

 load data [local] inpath '/opt/module/datas/student.txt' [overwrite] into table student [partition (partcol1=val1,…)];

load data:表示加载数据
local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表
inpath:表示加载数据的路径
overwrite:表示覆盖表中已有数据，否则表示追加
into table:表示加载到哪张表
student:表示具体的表
partition:表示上传到指定分区

通过查询语句向表中插入数据

#基本插入
insert into table student partition(month='201709') values(1,'wangwu'),(2,’zhaoliu’);

#清除表中数据:truncate 只能清除内部表，不能清除外部表
truncate table student;

#单表查询插入
insert overwrite table student partition(month='201708')
select id, name from student where month='201709';
#insert into：以追加数据的方式插入到表或分区，原有数据不会删除
#insert overwrite：会覆盖表或分区中已存在的数据

#多表查询插入
from student
insert overwrite table student partition(month='201707')
select id, name where month='201709'
insert overwrite table student partition(month='201706')
select id, name where month='201709';

#使用查询数据创建表并加载数据
create table if not exists student3
as select id, name from student;

#创建表时通过Location指定加载数据路径
create external table if not exists student5(
id int, name string
)
row format delimited fields terminated by '\t'
location '/student';

#Import数据到指定Hive表
import table student2 partition(month='201709') from
 '/user/hive/warehouse/export/student';
 
#将查询的结果导出到本地
insert overwrite local directory '/opt/module/datas/export/student'
select * from student;

#将查询的结果格式化导出到本地
insert overwrite local directory '/opt/module/datas/export/student1' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'             select * from student;

#将查询的结果导出到HDFS上(没有local)
insert overwrite directory '/user/atguigu/student2' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
select * from student;

#Hadoop命令导出到本地
dfs -get /user/hive/warehouse/student/month=201709/000000_0
/opt/module/datas/export/student3.txt;

#Hive Shell 命令导出
bin/hive -e 'select * from default.student;' > /opt/module/datas/export/student4.txt;
 
#Export导出到HDFS上
export table default.student to '/user/hive/warehouse/export/student';

.Mr Zhang

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
离线数仓-Hive 交互命令、DDL与DML操作

常用命令###交互命令#查看帮助bin/hive -help# -e 不进入hive提示窗口执行sql语句bin/hive -e "select id from student;"# -f 执行脚本中sql语句bin/hive -f /opt/module/datas/hivef.sql###hive提示窗口命令#进入提示窗口bin/hive#在hive提示窗口命令中...
复制链接

扫一扫