Hive基础命令

Hive基本操作

1、Hive库操作

1）创建一个数据库，数据库在HDFS上的默认存储路径是/hive/warehouse/*.db

create database testdb;

2)避免要创建的数据库已经存在错误，增加if not exists判断。（标准写法）

create database if not exists testdb;

2、创建数据库以及位置（loccation）

create databse if not exists testdb;

3、修改数据库

数据库的其他元素都是不可更改的，包括数据库名和数据库所在目录位置，只能修改数据库创建时间（重点关注哪些不能修改和为什么不能修改）

alter database dept set 

4、显示数据库详细信息

显示数据库(show)

show databases;

通过like进行过滤

show databases like 't*'

查看详情（desc）

desc database testddb;

查看详情（更加详细）

desc formatted testdb;

删除数据库（删除的数据库进入到回收站中）

最简写法

drop database testdb;

判断是否存在，能减少报错

drop database if exists testdb;

想要强制删除在后面加上cascade

drop database if exists testdb cascade;

6、数据类型

6.1、基础数据类型

最常用的类型：bigint,decimal(precision,scale)string,tiimestamp,boolean

6.2 复杂的数据类型

7、表操作

Hive存储格式：textfile,sequencefile,avro,rcfile,orcfile,parquet

textfile:
	正常的文本格式，是Hive的默认文件存储格式，可以直接进行读取，但是占用存储空间较大，一般只有与其它系统进行交互的接口使用textfile格式，其他的事实表（存有事实记录的表，比如系统日志，销售记录）和维度表（查找表，比如时间表（存储与时间对应的周、月、季度等对的属性），地点表（包含国家、省、州、城市等信息））不建议使用

rcfile：
	record columnar的缩写，是hadoop中的第一个列式文件格式，能很好的压缩和快速的查询，写操作比较慢，且比非列式的文件格式需要更多的内存空间和计算量
	
ordfile：
	orcfile是列式文件存储格式，拥有很高的压缩比，对于mapreduce来说是可切分对的，可以节省HDFS的存储资源，对于数据的查询和处理性能有很多大的提升
	
Parquet:
通常我们使用关系数据库存储结构化数据，而关系数据库中使用数据模型都是扁平式的，遇到诸如数组、Map和自定义Struct的时候就需要用户在应用层解析。但是在大数据环境下，通常数据的来源是服务端的埋点数据，很可能需要把程序中的某些对象内容作为输出的一部分，而每一个对象都可能是嵌套的，所以如果能够原生的支持这种数据，这样在查询的时候就不需要额外的解析便能获得想要的结果。Parquet的灵感来自于2010年Google发表的Dremel论文，文中介绍了一种支持嵌套结构的存储格式，并且使用了列式存储的方式提升查询性能。Parquet仅仅是一种存储格式，它是语言、平台无关的，并且不需要和任何一种数据处理框架绑定。这也是parquet相较于orc的仅有优势：支持嵌套结构。Parquet 没有太多其他可圈可点的地方,比如他不支持update操作(数据写成后不可修改),不支持ACID等.

SEQUENCEFILE:
SequenceFile是Hadoop API 提供的一种二进制文件，它将数据以<key,value>的形式序列化到文件中。这种二进制文件内部使用Hadoop 的标准的Writable 接口实现序列化和反序列化。它与Hadoop API中的MapFile 是互相兼容的。Hive 中的SequenceFile 继承自Hadoop API 的SequenceFile，不过它的key为空，使用value 存放实际的值， 这样是为了避免MR 在运行map 阶段的排序过程。SequenceFile支持三种压缩选择：NONE, RECORD, BLOCK。 Record压缩率低，一般建议使用BLOCK压缩。 SequenceFile最重要的优点就是Hadoop原生支持较好，有API，但除此之外平平无奇，实际生产中不会使用。

AVRO:
Avro是一种用于支持数据密集型的二进制文件格式。它的文件格式更为紧凑，若要读取大量数据时，Avro能够提供更好的序列化和反序列化性能。并且Avro数据文件天生是带Schema定义的，所以它不需要开发者在API 级别实现自己的Writable对象。Avro提供的机制使动态语言可以方便地处理Avro数据。最近多个Hadoop 子项目都支持Avro 数据格式，如Pig 、Hive、Flume、Sqoop和Hcatalog。
	
	

8、创建表：

官网的标准方式：

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
[(col_name data_type [COMMENT col_comment], ...)] 
[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] 
[LOCATION hdfs_path]


字段解释说明:
- CREATE TABLE 
	创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。

- EXTERNAL
	关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）
	创建内部表时，会将数据移动到数据仓库指向的路径（默认位置）；
	创建外部表时，仅记录数据所在的路径，不对数据的位置做任何改变。在
	删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

- COMMENT：
	为表和列添加注释。

- PARTITIONED BY
	创建分区表

- CLUSTERED BY
	创建分桶表

- SORTED BY
	不常用

- ROW FORMAT 
  DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
	用户在建表的时候可以自定义SerDe或者使用自带的SerDe。
	如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。
	在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。
	SerDe是Serialize/Deserilize的简称，目的是用于序列化和反序列化。

- STORED AS指定存储文件类型
	常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）
	如果文件数据是纯文本，可以使用STORED AS TEXTFILE。
	如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

- LOCATION ：
	指定表在HDFS上的存储位置。

- LIKE
	允许用户复制现有的表结构，但是不复制数据。

建表1：全部使用默认建表方式

create table IF NOT EXISTS students
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; // 必选，指定列分隔符 

建表2：指定location （这种方式也比较常用）

create table IF NOT EXISTS students2
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/bigdata27/input1'; // 指定Hive表的数据的存储位置，一般在数据已经上传到HDFS，想要直接使用，会指定Location，通常Locaion会跟外部表一起使用，内部表一般使用默认的location


create table IF NOT EXISTS person_avg_counts
(
	name string,
	avg_count bigint
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/bigdata29/out4'; 

建表3：指定存储格式

create table IF NOT EXISTS test_orc_tb
(
    id bigint,
    name string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS ORC
LOCATION '/bigdata29/out6'; // 指定储存格式为orcfile，inputFormat:RCFileInputFormat,outputFormat:RCFileOutputFormat，如果不指定，默认为textfile，注意：除textfile以外，其他的存储格式的数据都不能直接加载，需要使用从表加载的方式。

建表4：create table xxxx as select_statement(SQL语句) (这种方式比较常用)

create table IF NOT EXISTS bigdata29.students(id bigint,name string,age int,gender string,clazz string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

create table IF NOT EXISTS bigdata29.xuqiu2(clazz string,number bigint)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

insert into students3_1 values(1002,'吴小康',19,'男','27期特训营');

create table students4 as select * from students2;

建表5：create table xxxx like table_name 只想建表，不需要加载数据

create table students5 like students;

简单用户信息表创建：

create table t_user(
id int,
uname string,
pwd string,
gender string,
age int
)
row format delimited fields terminated by ','
lines terminated by '\n';

数据

1,admin,123456,男,18
2,zhangsan,abc123,男,23
3,lisi,654321,女,16

复杂人员信息表创建：

create table IF NOT EXISTS t_person(
name string,
friends array<string>,
children map<string,int>,
address struct<street:string ,city:string>
)
row format delimited fields terminated by ',' -- 列与列之间的分隔符
collection items terminated by '_' -- 元素与元素之间分隔符
map keys terminated by ':' -- Map数据类型键与值之间的分隔符
lines terminated by '\n';  -- 行与行之间的换行符

数据

songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,beng bu_anhui
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,he fei_anhui