Hive 基本操作

Hive 基本操作

• 1、create：创建数据库、表、视图
• • 1.1 创建数据库
  • 1.2 创建表
  • 1.3 创建视图
• 2、drop：删除数据库、表、视图
• • 2.1 删除数据库
  • 2.2 删除表
  • 2.3 删除视图
• 3、alter：修改数据库、表、视图
• • 3.1 修改数据库
  • 3.2 修改表
  • 3.3 修改视图
• 4、补充
• • 4.1 hive模糊搜索表
  • 4.2 查看表结构信息
  • 4.3 查看分区信息
  • 4.4 根据分区查询数据
  • 4.5 查看hdfs文件信息
  • 4.6 从文件加载数据进表(OVERWRITE覆盖，追加不需要OVERWRITE关键字)
  • 4.7 导出数据到文件

环境：VMware® Workstation 16 Pro、Ubuntu18.4、Hadoop伪分布式

HiveQL 是 Hive 的查询语言，和 SQL 比较类似，对 Hive 的操作都是通过编写 HiveQL 语句来实现的。接下来介绍一下 Hive 中常用的几个基本操作。

1、create：创建数据库、表、视图

1.1 创建数据库

（1） 创建数据库 hive：

hive> create database hive;

（2）创建数据库 hive，因为 hive 已经存在，所以会抛出异常，加上 if not exists （如果Hive不存在，就create，存在就不create，所以不会报错）关键字，则不会抛出异常：

hive> create database if not exists hive;

1.2 创建表

（1）在 hive 数据库中，创建 usr 表，含 3 个属性 id、name 和 age：

hive> use hive;
hive> create table if not exists usr(id bigint,name string,age int);

（2）在 hive 数据库中，创建 usr 表，含 3 个属性 id、name 和 age，存储路径为 “/usr/local/hive/warehouse/hive/usr”：

hive> create table if not exists hive.usr(id bigint,name string,age int)
    > location'/usr/local/hive/warehouse/hive/usr';

（3）在 hive 数据库中，创建外部 usr 表，含 3 个属性 id、name 和 age，可以读取路径 "/usr/local/data” 下以 “,” 分隔的数据：

hive> create external table if not exists hive.usr(id bigint,name string,age int)
    > row format delimited fields terminated by ','
    > Location'/usr/local/data';

（4）在 hive 数据库中，创建分区 usr 表，含 3 个属性 id、name 和 age，还存在分区字段 sex：

hive> create table hive.usr(id bigint,name string,age int) partitioned by(sex boolean);

（5）在 hive 数据库中，创建分区 usr1 表，它通过复制 usr 表得到：

hive> use hive;
hive> create table if not exists usr1 like usr;

1.3 创建视图

创建视图 little_usr，只包含表 usr 中 id 和 age 属性：

hive> create view little_usr as select id,age from usr;

现在 Hive 数据库内有以下三个表

2、drop：删除数据库、表、视图

2.1 删除数据库

（1）删除数据库 hive，如果不存在会出现警告：

hive> drop database hive;

（2）删除数据库 hive，因为有 if exists 关键字，即使不存在也不会抛出异常：

hive> drop database if exists hive;

（3）删除数据库 hive，加上 cascade 关键字，可以删除当前数据库和该数据库中的表：

hive> drop database if exists hive cascade;

2.2 删除表

（1）删除 usr 表，如果是内部表，元数据和实际数据都会被删除；如果是外部表，则只删除元数据，不删除实际数据：

hive> drop table if exists usr;

2.3 删除视图

（1）删除视图 little_usr：

hive> drop view if exists little_usr;

3、alter：修改数据库、表、视图

3.1 修改数据库

（1）为 hive 数据库设置 dbproperties 键值对属性值来描述数据库属性信息：

hive> alter database hive set dbproperties('edited-by'='2022-05-01');

3.2 修改表

（1）重命名 usr 表为 user：

hive> alter table usr rename to user;

重命名报错了，网上一查，原来 user 是Hive 的关键字，不可使用，我们这里就改为重命名 usr 表为 usr1 alter table usr rename to usr1

如果想保留关键字，可以参考：Hive 标识符列表及保留方法

（2）为 hello 表增加新分区：

前提在建表的时候就要创建分区表，不然增加分区会报 FAILED: ValidationFailureSemanticException 库名.表名 table is not partitioned but partition spec exists: {分区}

hive> alter table hello add if not exists partition(sex=true);
hive> alter table hello add if not exists partition(sex=false);

（3）删除 hello 表中分区：

hive> alter table hello drop if exists partition(sex=true);

（4）把 usr 表中列名 name 修改为 username：

hive> alter table usr change name username string after age;

（5）在对 usr 表分区字段之前，增加一个新列 sex：

hive> alter table usr add columns(sex boolean);

（6）删除 usr 表中所有字段并重新指定新字段 newid、newname 和 newage：

hive> alter table usr replace columns(newid bigint,newname string,newage int);

（7）为 usr 表设置 tblproperties 键值对属性值来描述表的属性信息：

hive> alter table usr set tblproperties('notes'='the columns in usr may be null except id');

3.3 修改视图

（1）修改 little_usr 视图元数据中的 tblproperties 属性信息：

hive> alter view little_usr set tblproperties('create_at'='refer to timestamp');

4、补充

4.1 hive模糊搜索表

  show tables like '*name*';

4.2 查看表结构信息

  desc formatted table_name; # 详细
  desc table_name; # 简略

4.3 查看分区信息

  show partitions table_name;

4.4 根据分区查询数据

  select table_coulm from table_name where partition_name = '2014-02-25';

4.5 查看hdfs文件信息

  dfs -ls /user/hive/warehouse/table02;

4.6 从文件加载数据进表(OVERWRITE覆盖，追加不需要OVERWRITE关键字)

  LOAD DATA LOCAL INPATH 'dim_csl_rule_config.txt' OVERWRITE into table dim.dim_csl_rule_config;

• 从查询语句给table插入数据

  INSERT OVERWRITE TABLE test_h02_click_log PARTITION(dt) select * 
  from stage.s_h02_click_log where dt='2014-01-22' limit 100;

4.7 导出数据到文件

  insert overwrite directory '/tmp/csl_rule_cfg' select a.* from dim.dim_csl_rule_config a;
  hive -e "select day_id,pv,uv,ip_count,click_next_count,second_bounce_rate,return_visit,pg_type from tmp.tmp_h02_click_log_baitiao_ag_sum where day_id in ('2014-03-06','2014-03-07','2014-03-08','2014-03-09','2014-03-10');"> /home/jrjt/testan/baitiao.dat;