HIVE中的表以及语法
HIVE中的表以及语法
一、HIVE的表
HIVE使用的功能性的表格分为四种：内部表、外部表、分区表、分桶表。
1、内部表、外部表
1．特点
创建hive表，经过检查发现TBLS表中，hive表的类型为MANAGED_TABLE，即所谓的内部表。
内部表的特点是，先有表后有数据，数据被上传到表对应的hdfs目录下进行管理。
其实内部表的流程和sql数据库的表流程是几乎一样的。
但是在真实开发中，很可能在hdfs中已经有了数据，希望通过hive直接使用这些数据作为表内容。
此时可以创建hive表关联到该位置，管理其中的数据，这种方式创建出来的表叫做外部表。
外部表的特点是，先有数据后有表，hive表关联到该位置管理其中的数据。
2．创建表
创建内部表的语句和sql语句是一样的。
创建外部表的语法如下：
create external table .... location 'xxxx';案例
向hdfs中准备文件：
hadoop fs -mkdir /hdata
hadoop fs -put stu.txt /hdata/stu.txt
hadoop fs -put teacher.txt /hdata/teacher.txt 在hive中创建外部表管理已有数据：
create external table ext_stu(id int ,name string) row format delimited fields terminated by '\t' location '/hdata';通过观察发现：
并不会在/usr/hive/warehouse/[db]/下创建表文件夹。
在元数据库的TBLS里多出了记录，且表的类型为EXTERNAL_TABLE。
在元数据库的COLUMSV2里多出了列相关的记录。
在元数据库的SDS里多出了记录，指向了真正的数据存放的位置。
经过检查发现可以使用其中的数据。成功的建立了一张外部表。
3．删除表
删除内外部表的语句和sql的语句是一样的。
在删除表时：
内部表删除了元数据库中相关的元数据，删除了hdsf中表对应的文件夹及其中的数据。
外部表删除了元数据库中相关的元数据，并不会删除关联到的文件夹及其内部的数据。
2、分区表
hive也支持分区表。
利用分区表可以对数据进行分区来提高查询的效率，在大量数据经常要按照某些指定特定字段查询时可以设计分区表来提高效率。
1．创建分区表
create table book (id bigint, name string) partitioned by (country string) row format delimited fields terminated by '\t';在创建分区表时，partitioned字段可以不在字段列表中。生成的文件自动就会具有该字段。
2．分区表加载数据
1>相对路径加载
使用相对路径加载本地数据：
load data local inpath './book_china.txt' overwrite into table book partition (country='china');2>绝对路径加载
使用绝对路径加载本地数据：
load data local inpath '/root/book_english.txt' overwrite into table book partition (country='english');3>加载远程数据
加载hdfs中的数据：
load data inpath '/book/jp.txt' overwrite into table book partition (country='jp');注意路径的写法：
如果路径中不指定hdfs的地址，那么会默认寻找本机HDFS上的数据。
如果指定HDFS的地址则会按照指定的地址寻找数据。
例如：’hdfs://hadoop:9000/book/jp.txt’
3．从分区表中查询数据
select * from book;
select * from book where pubdate='2010-08-22';4．流程解析
当创建分区表并向分区表写入数据后，会在表对应的文件夹下创建出子一级分区文件夹来存放数据，并且将该目录加入元数据库中的SDS中作为数据来源文件夹。
当按照分区字段作为条件进行查询时，hive可以直接找到该分区字段值对应的文件夹，直接将该文件夹下的数据返回，效率非常的高。
所以，如果hive表中有大量数据，且经常按照某些字段做查询，则可以将该字段设计为表得到分区字段提升效率。
5．多分区
hive支持一个表中有多个分区字段，在创建表时依次声明即可。
创建表：
create table book2 (id bigint, name string) partitioned by (country string,category string) row format delimited fields terminated by '\t';加载数据：
load data local inpath '/root/work/china.txt' into table book2 partition (country='china',category='xs');多个分区会形成多级子目录，来分开存放数据。同样可以提高按照分区字段查询数据时的效率。
6．添加上传数据
如果直接在HDFS中HIVE的某个表中上传数据文件，此时手动创建目录是无法被hive使用的，因为元数据库中没有记录该分区。
如果需要将自己创建的分区也能被识别，需要执行：
ALTER TABLE book2 add PARTITION (country='jp',category = 'xs') location '/user/hive/warehouse/park2.db/book2/country=jp/category=xs';
ALTER TABLE book2 add PARTITION (country='jp',category = 'it') location '/user/hive/warehouse/park2.db/book2/country=jp/category=it'; 3、分桶表
1．介绍
hive也支持分桶表，分桶操作是更细粒度的分配方式，一张表可以同时分区和分桶。
分桶的原理是根据指定的列的计算将数据分开存放。
分桶的主要作用是：
对于一个庞大的数据集我们经常需要拿出来一小部分作为样例，然后在样例上验证我们的查询，优化我们的程序，利用分桶表可以实现数据的抽样。
2．应用步骤
hive默认关闭分桶功能，需要手动开启set hive.enforce.bucketing=true;
分桶的操作需要在底层mr执行的过程中起作用，所以通常不会在原始的数据表上加分桶，而是专门创建一个测试表，将原始表中的数据导入到测试表，再导入过程触发的mr中实现分桶。
1>开启分桶功能
开启分桶功能，强制多个reduce进行输出：
hive>set hive.enforce.bucketing=true;2>创建主表
准备主表：
create table teacher(id int,name string) row format delimited fields terminated by '|';向主表加载数据:
load data local inpath '/root/work/teachers.txt' into table teacher;3>创建桶表
创建带桶的table：
create table teacher_temp(id int,name string) clustered by (id) into 2 buckets row format delimited fields terminated by '|';从主表向分桶表导入数据：
insert overwrite/into table teacher_temp select * from teacher;分桶表其实就是将表中的数据按照hash分桶的方式分桶存放，而所谓的桶就是表文件夹下不同的文件
4>测试
在分桶表中基于部分数据做测试：
select * from teacher_temp tablesample(bucket 1 out of 2 on id);桶的个数从1开始计数，前面的查询从2个桶中的第一个桶获取数据。其实就是二分之一。
从分桶表中获取1/4的数据：
select * from bucketed_user tablesample(bucket 1 out of 4 on id);tablesample函数是一种逻辑抽样过程，即使物理分桶时并不是4个桶一样可以得到数据，但是最好在抽样时保证抽样的总数和物理分桶的总数相同 这样可以保证最好的效率。
二、HIVE语法
1、数据类型
HIVE和java的数据类型对比：
TINYINT：byte
SMALLINT：short
INT：int
BIGINT：long
BOOLEAN：boolean
FLOAT：float
DOUBLE：double
STRING：String
TIMESTAMP：TimeStamp
BINARY：byte[]
2、语法
1．create table
1>关键字
①CREATE TABLE
创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用IF NOT EXIST选项来忽略这个异常，只是忽略异常，表格仍然没有创建，也不做提示。
②EXTERNAL
此关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
③LIKE
允许用户复制现有的表结构，但是不复制数据。
④PARTITIONED BY
有分区的表可以在创建的时候使用PARTITIONED BY语句。一个表可以拥有一个或者多个分区，每一个分区单独存在一个目录下。
2>语法
创建表的语法：
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]
| STORED BY 'storage.handler.class.name' [ WITH SERDEPROPERTIES (...) ] (Note: only available starting with 0.6.0)
]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)] (Note: only available starting with 0.6.0)
[AS select_statement] (Note: this feature is only available starting with 0.5.0.)克隆表的语法：
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
LIKE existing_table_name
[LOCATION hdfs_path]克隆表，克隆的只是表结构，而不带数据。
3>字段
①data_type
data_type数据类型的值可以有以下几种：
primitive_type、array_type、map_type、struct_type。
②primitive_type
primitive_type的值有以下几种：
TINYINT、SMALLINT、INT、BIGINT、BOOLEAN、FLOAT、DOUBLE、STRING。
③array_type
array_type的值只有这一种：ARRAY < data_type >。
④map_type
此字段的值只有这一种：MAP < primitive_type, data_type >。
⑤struct_type
此字段的值也只有这一种：STRUCT < col_name : data_type [COMMENT col_comment],...>。
⑥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, ...)]⑦file_format
SEQUENCEFILE、TEXTFILE、RCFILE (Note: only available starting with 0.6.0)、INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname。
练习
创建一张内部表
create table xx (id int,name string) row format DELIMITED FIELDS TERMINATED BY '\t';创建一张外部表
create external table xx (id int,name string) row format DELIMITED FIELDS TERMINATED BY '\t';创建一张带有分区的外部表
create external table xx (id int,name string) row format DELIMITED FIELDS TERMINATED BY '\t' partitioned by 'ccc';2．Alter Table
1>Add Partitions
增加分区。
①语法
ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...②字段
1)partition_spec
PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)2>Drop Partitions
删除分区。
语法：
ALTER TABLE table_name DROP partition_spec, partition_spec,...3>Rename Table
表重命名。
语法：
ALTER TABLE table_name RENAME TO new_table_name4>Change Column
修改字段信息。
语法：
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]这个命令可以允许改变列名、数据类型、注释、列位置或者它们的任意组合。
5>Add/Replace Columns
增加或者替换字段。
语法：
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)ADD是代表新增一字段，字段位置在所有列后面(partition列前);REPLACE则是表示替换表中所有字段。
3．Show
1>查看库表
SHOW DATABASES;
SHOW TABLES;2>查看表名
查看表名，部分匹配。
SHOW TABLES 'page.*';
SHOW TABLES '.*view';3>查看分区
查看某表的所有Partition，如果没有就报错：
SHOW PARTITIONS page_view;4>查看表结构
查看某表结构：
DESCRIBE invites;
DESC invites;5>查看分区内容
查看分区内容：
SELECT a.foo FROM invites a WHERE a.ds='2008-08-15';查看有限行内容，同Greenplum，用limit关键词：
SELECT a.foo FROM invites a limit 3;查看表分区定义：
DESCRIBE EXTENDED page_view PARTITION (ds='2008-08-08');4．Load
语法：
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]Load操作只是单纯的复制/移动操作，将数据文件移动到Hive表对应的位置。
5．Insert
1>内部插入查询结果
向hive表中插入一个查询的结果。
语法：
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement2>将查询结果输出
将查询结果写出到外部文件系统中。
语法：
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT ... FROM ...local：此项不加默认存入HDFS中，添加此项存入本地磁盘路径。
6．Drop
删除一个内部表的同时会同时删除表的元数据和数据。
删除一个外部表，只删除元数据而保留数据。
语法：
drop table_name7．Limit
Limit可以限制查询的记录数。
8．Select
查询表内容。
语法：
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]9．JOIN
多表查询连接查询。链接查询分为：笛卡尔基查询、内链接查询、外连接查询。外连接查询又分为左外连接、右外连接、全外连接。
1>join_table
语法：
table_reference JOIN table_factor [join_condition]
| table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
| table_reference LEFT SEMI JOIN table_reference join_condition2>字段
①table_reference
table_factor | join_table
②table_factor
tbl_name [alias] | table_subquery alias | ( table_references )
③join_condition
ON equality_expression ( AND equality_expression )*
④equality_expression
expression = expression