Hive2

Store as的含义

hive文件存储格式包括以下几类
1.TEXTFILE
为默认格式，建表时不指定则默认为这个格式，导入数据时会直接吧数据文件拷贝到hdfs上不进行处理；

SEQUENCEFILE，RCFILE，ORCFILE，PARQUET格式的表不能直接从本地文件导入数据数据要先导入到TEXTFILE格式的表中，再从表中用insert导入SEQUENCEFILE，RCFILE，ORCFILE，PARQUET表中；

或者用复制表结构及数据的方式（create table as select * from table）

特点：

（1）默认格式；

（2）存储方式为行存储；

（3）磁盘开销大，数据解析开销大；

（4）但使用这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作

2.SEQUENCEFILE
二进制文件以<key,value>的形式序列化到文件中;

存储方式:行存储;

可分割压缩;

-般选择block压缩;

优势是文件和Hadoop api中的mapfile是相互兼容的

3.RCFILE
存储方式:数据按行分块每块按照列存储;

压缩快快速列存取;

读记录尽量涉及到的block最少;

读取需要的列只需要读取每个row group的头部定义;

读取全量数据的操作性能可能比sequencefile没有明显的优势,

4.ORCFILE
存储方式:数据按行分块每块按照列存储;

压缩快快速列存取;

效率比rcfile高是rcfile的改良版本。

5.PARQUET
类似于ORC，相对于ORC文件格式，HADOOP生态系统中大部分工程都支持PARQUET文件

hive哪些操作会有mapreduce

select，不带count,sum,group by这样的，都不走map/reduce，直接读取hdfs文件进行filter过滤。这样做的好处就是不新开mr任务，执行效率要提高不少，但是不好的地方就是用户界面不友好，有时候数据量大还是要等很长时间，但是又没有任何返回。

1.hive -H 查看hive命令

1.hive -e SQL语句
hive -e “select * from tname”
2.hive -f SQL文件
hive -f test0330.sql
test0330.sql文件

create table … temp01 select * from emp;
create table … temp02 select * from dept;

insert into dws_result
select * from temp01 left join temp02…;
3.hive -i
定义UDF函数

2.库相关的语法
1.create

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

不加LOCATION的，对应的HDFS的地址是：/user/hive/warehouse/test.db

2.drop
drop database test;
如果库中有表存在，不允许删除。

如果需要删除（1.把所有的表删了）（2.使用cascade关键字：drop database if exists test cascade;）

3.use
use test;
查看库的属性信息

desc database [extended] test;
查看正在使用哪个库

select current_database();
这个也可以通过hive-site.xml设置

hive.cli.print.current.db
查看创建库的详细语句

show create database test;

3.表相关的语法

1.create

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(col_name data_type [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]
  [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
   [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)

CREATE TABLE:创建一个指定名字的表，如果相同名字的表已经存在，抛异常：如果加上IF NOT EXISTS，则会忽略异常

EXTERNAL：创建一个外部表，在建表的同时可以指定一个实际数据的路径（LOCATION）

col_name data_type:列名以及对应的数据类型，多列用逗号分隔

COMMENT col_comment:列的描述(sal comment ‘薪水’)

COMMENT table_comment:表的描述

PARTITIONED BY:指定分区 hive中一个重要的概念

CLUSTERED BY：对于每一个表或者分区，hive可以进一步变为桶，hive采用对列值进行哈希，

然后除以桶的个数求余的方式决定该条记录存放在哪个桶中。

ROW FORMAT：一般是ROW FORMAT DELIMITED FILEDS TERMINATED BY ‘,’，指定表存储中列的分隔符，默认是\001，还可以自定义其他分隔符

STORED AS：STORED AS SEQUENCEFILE | TEXTFILE | RCFILE，如果是纯文本，那就是STORED AS TEXTFILE，如果数据需要压缩，STORED AS SEQUENCEFILE

LOCATION：指定hive表在HDFS上的路径，如果是外部表的话，则需要直接指定一个路径；

内部表不需要指定，默认/user/hive/warehouse/*

AS select_statement:通过select的SQL语句的结果来建表

常用建表的三种方式：

（1）

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOTEXISTS] [db_name.]table_name
	LIKE existing_table_or_view_name
	[LOCATION hdfs_path];

（2）

CREATE TABLE test0403 LINK test0330;

可以建表，但是无数据，只是复制了表结构。

(3)

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
AS SQL;
drop TEMPORARY if exists TABLE test0403_b;
CREATE TABLE test0403_b as select * from test0330;

可以建表，也有数据，表结构也有。

2.查看表
show tables;
查看其他库的表

show tables in default;
模糊查询表

show tables like ‘test04*’;
查看表的信息

desc [formatted] test0330;
查看表的建表语句

show create table test0330;
3.给表添加数据
（1）

LOAD/INSERT

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename
	[PARTITION (parcoll=vall,partcol2=val2 ...)]

[LOCAL] 代表本地文件系统，不加则会去HDFS上找

INPATH 加载数据的路径

OVERWRITE 有则覆盖，无则追加

PARTITION 加载到哪个分区

例：

LOAD DATA LOCAL INPATH '/home/yangheng/app/hive/emp.txt' INTO TABLE emp;

（2）

insert

1.insert into table…values();造数据用的较多
2.insert overwrite table partition (dt)…select xxx,yyy,zzz from table_a where join group by
4.修改表
修改表名，字段名，增加字段，注释comment ‘xx类型(已废弃)’

alter table

删除一个字段和修改类型 不支持

如果必须要删除某个字段，那么就替换掉所有字段

alter table table_a replace columns();

5.删除表
drop table table_name;
内部表，删除之后，表和数据文件，均删除

外部表，删除之后，表没了，LOACTION数据文件还在

清空表

truncate table table_name;
外部表，不允许truncate,会报错

3.内部表（MANAGED）VS 外部表（EXTERNAL）
有很多不同，最大的不同

内部表，删除之后，表和数据文件，均删除；HDFS和MySQL的数据都被删除

外部表，删除之后，表没了，LOACTION数据文件还在；MySQL中的数据被删除，但是HDFS的数据还在

外部表不删除数据文件这个特点，方便数据共享，比如我们原始的数据和日志等

内部表可以作为我们中间表和结果表