【博学谷学习记录】超强总结，用心分享|Hive表基础语法

创建数据库

create database if not exists myhive;
use  myhive;

hive的表在HDFS中默认存放位置模式是由hive-site.xml当中的一个属性指定
hive.metastore.warehouse.dir
/user/hive/warehouse

创建数据库并指定HDFS存储位置

create database myhive2 location '/myhive2';

删除数据库

只能删除空数据库，如果数据库下面有数据表会报错

drop database myhive2;

强制删除数据库,包含数据库下面的表一起删除

drop database myhive2 cascade;

删除前

删除后

创建表(内部表)

未被external修饰的是内部表（managed table）,内部表又称管理表,内部表数据存储的位置由hive.metastore.warehouse.dir参数决定（默认：/user/hive/warehouse），删除内部表会直接删除元数据（metadata）及存储数据，因此内部表不适合和其他工具共享数据

create table test1(
    id int,
    name string
);

内部表的表类型为managed_table

hdfs默认的表存储位置

创建表并指定字段之间的分隔符

create table if not exists test2(
    id int,
    name string
) row format delimited
    fields terminated by ' ';

根据查询结果创建表

create table test3 as select * from test1;

根据已存在的表结构创建表

create table test4 like test1;

删除表且删除表结构

drop table test4;

没有delete语法

需要清空表数据可以使用truncate

清空前

清空表数据且保留表结构

创建表(外部表)

在创建表的时候可以指定external关键字创建外部表,外部表对应的文件存储在location指定的hdfs目录下,向该目录添加新文件的同时，该表也会读取到该文件(当然文件格式必须跟表定义的一致)。
外部表因为是指定其他的hdfs路径的数据加载到表当中来，所以hive表会认为自己不完全独占这份数据，所以删除hive外部表的时候，数据仍然存放在hdfs当中，不会删掉。

create external table test4
(
    id   string,
    name string
) row format delimited
    fields terminated by '\t';

外部数据加载命令load

1. 上传一个和已创建的表结构相同的数据文件到hdfs任意目录
2. 
3. 加载文件中的数据到hive表中

load操作实际上移动文件的操作,如果删掉test4表，hdfs的数据仍然存在，并且重新创建表之后，表中就直接存在数据了

load data inpath '/myhive/input/teacher.txt' into table test4;

执行加载命令前

文件位置: 有一个tearch.txt文件

外部表目录: 无文件

执行加载命令后

文件位置: 无文件

外部表目录: 有一个tearcher.txt文件

复杂类型操作

Array类型

源数据

zhangsan beijing,shanghai,tianjin,hangzhou
wangwu changchun,chengdu,wuhan,beijing

建表语句

create external table hive_array(
    name string,
    locations array<string>
) row format delimited
    fields terminated by ' '
    collection items terminated by ',';

加载数据

load data inpath '/myhive/input/array.txt' into table hive_array;

查询

map类型

源数据

1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,28
2,lisi,father:mayun#mother:huangyi#brother:guanyu,22
3,wangwu,father:wangjianlin#mother:ruhua#sister:jingtian,29
4,mayun,father:mayongzhen#mother:angelababy,26

建表语句

create table hive_map(
    id int,
    name string,
    members map<string,string>,
    age int
) row format delimited
    fields terminated by ','
    collection items terminated by '#'
    map keys terminated by ':';

加载语句

load data inpath '/myhive/input/map.txt' into table hive_map;

查询

根据键找对应的值

-- 根据键找对应的值
select id, name, members['father'] father, members['mother'] mother, age
from hive_map;

获取所有键

select id, name, map_keys(members) as relations
from hive_map;

获取所有值

select id,name,map_values(members) as relations
from hive_map;

获取键值对大小

select map_keys(members),size(members) as map_size
from hive_map;

获取有指定key的数据

select *
from hive_map
where array_contains(map_keys(members),'brother');

获取包含指定key的数据,且获取value的内容

select id,name,members['brother'] as brother
from hive_map
where array_contains(map_keys(members),'brother');

struct类型

源数据

192.168.1.1#zhangsan:40
192.168.1.2#lisi:50
192.168.1.3#wangwu:60
192.168.1.4#zhaoliu:70

建表语句

create table hive_struct(
    ip string,
    info struct<name:string,age:int>
) row format delimited
    fields terminated by '#'
    collection items terminated by ':';

加载语句

load data inpath '/myhive/input/struct.txt' into table hive_struct;

查询

查询指定成员信息

select ip,info.name
from hive_struct;