hive sql 学习笔记(1)

转载自http://www.cnblogs.com/tangtianfly/archive/2012/03/13/2393449.html

一、 创建表 
    在官方的wiki里，example是这样的：

1. CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name   
2.   [(col_name data_type [COMMENT col_comment], ...)]   
3.   [COMMENT table_comment]   
4.   [PARTITIONED BY (col_name data_type   
5.     [COMMENT col_comment], ...)]   
6.   [CLUSTERED BY (col_name, col_name, ...)   
7.   [SORTED BY (col_name [ASC|DESC], ...)]   
8.   INTO num_buckets BUCKETS]   
9.   [ROW FORMAT row_format]   
10.   [STORED AS file_format]   
11.   [LOCATION hdfs_path]  

row_format 
  : DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] 
        [MAP KEYS TERMINATED BY char] 
  |  SERDE  serde_name  [WITH  SERDEPROPERTIES  property_name=property_value, 
property_name=property_value, ...] 
 
file_format: 
  : SEQUENCEFILE 
  | TEXTFILE 
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname 

 

 [ROW FORMAT DELIMITED]关键字，是用来设置创建的表在加载数据的时候，支持的列分隔符；
[STORED AS file_format]关键字是用来设置加载数据的数据类型。Hive本身支持的文件格式只有：Text File，Sequence File。如果文件数据是纯文本，可以使用 [STORED AS TEXTFILE]。如果数据需要压缩，使用 [STORED AS SEQUENCE] 。通常情况，只要不需要保存序列化的对象，我们默认采用[STORED AS TEXTFILE]。

 

    那么我们创建一张普通的hive表，hive sql就如下：

1. CREATE TABLE test_1(id INT, name STRING, city STRING) SORTED BY TEXTFILE ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’  ;

    其中，hive支持的字段类型，并不多，可以简单的理解为数字类型和字符串类型，详细列表如下：

1. TINYINT   
2. SMALLINT  
3. INT  
4. BIGINT  
5. BOOLEAN   
6. FLOAT  
7. DOUBLE  
8. STRING  

 

注意partitioned by 的位置：

create table webdata2(vstart string,vend string,hdid int,userid  int,sid int,refsid  int,active  int,duration int,mdomain string,sdomain string,refsdomain string,ieid    int,refieid string,url     string,totaltime int,param2 int,param4 string,param4code string) partitioned by(pid int,daytime string) row format delimited fields terminated by '\t' stored as SEQUENCEFILE;

    Hive的表，与普通关系型数据库，如mysql在表上有很大的区别，所有hive的表都是一个文件，它是基于Hadoop的文件系统来做的。

   hive总体来说可以总结为三种不同类型的表。

1. 普通表 
    普通表的创建，如上所说，不讲了。其中，一个表，就对应一个表名对应的文件。

2. 外部表

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

1. CREATE EXTERNAL TABLE test_1(id INT, name STRING, city STRING) SORTED BY TEXTFILE ROW FORMAT DELIMITED FIELDS TERMINATED BY'\t’ LOCATION ‘hdfs://http://www.cnblogs.com/..’  

3. 分区表

    有分区的表可以在创建的时候使用 PARTITIONED BY 语句。一个表可以拥有一个或者多个分区，每一个分区单独存在一个目录下。而且，表和分区都可以对某个列进行 CLUSTERED BY 操作，将若干个列放入一个桶（bucket）中。也可以利用SORT BY 对数据进行排序。这样可以为特定应用提高性能。具体SQL如下：

1. CREATE TABLE test_1(id INT, name STRING, city STRING) PARTITIONED BY (pt STRING) SORTED BY TEXTFILE ROW FORMAT DELIMITED FIELDS TERMINATED BY‘\t’   

    Hive的排序，因为底层实现的关系，比较不同于普通排序，这里先不讲。

 

     桶的概念，主要是为性能考虑，可以理解为对分区内列，进行再次划分，提高性能。在底层，一个桶其实是一个文件。如果桶划分过多，会导致文件数量暴增，一旦达到系统文件数量的上限，就杯具了。哪种是最优数量，这个哥也不知道。

 

    分区表实际是一个文件夹，表名即文件夹名。每个分区，实际是表名这个文件夹下面的不同文件。分区可以根据时间、地点等等进行划分。比如，每天一个分区，等于每天存每天的数据；或者每个城市，存放每个城市的数据。每次查询数据的时候，只要写下类似 where pt=2010_08_23这样的条件即可查询指定时间得数据。

 

    总体而言，普通表，类似mysql的表结构，外部表的意义更多是指数据的路径映射。分区表，是最难以理解，也是最hive最大的优势。之后会专门针对分区表进行讲解。

 

二、 加载数据

    Hive不支持一条一条的用insert语句进行插入操作，也不支持update的操作。数据是以load的方式，加载到建立好的表中。数据一旦导入，则不可修改。要么drop掉整个表，要么建立新的表，导入新的数据。

官方指导为：

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

    Hive在数据load这块，大方向分为两种方式，load文件或者查询一张表，或者将某张表里的额查询结果插入指定表。
如果划分更细一点个人归纳总结为4种不同的方式的load：

 

1. Load data到指定的表 
    直接将file，加载到指定的表，其中，表可以是普通表或者分区表。具体sql如下：

1. LOAD DATA LOCAL INPATH '/home/admin/test/test.txt' OVERWRITE INTO TABLE test_1  

    关键字[OVERWRITE]意思是是覆盖原表里的数据，不写则不会覆盖。
    关键字[LOCAL]是指你加载文件的来源为本地文件，不写则为hdfs的文件。
    其中

     ‘/home/admin/test/test.txt’为绝对路径

2. load到指定表的分区 
    直接将file，加载到指定表的指定分区。表本身必须是分区表，如果是普通表，导入会成功，但是数据实际不会被导入。具体sql如下：

1. LOAD DATA LOCAL INPATH '/home/admin/test/test.txt' OVERWRITE INTO TABLE test_1 PARTITION（pt=’xxxx）  

    load数据，hive支持文件夹的方式，将文件夹内的所有文件，都load到指定表中。Hdfs会将文件系统内的某文件夹路径内的文件，分散到不同的实际物理地址中。这样，在数据量很大的时候，hive支持读取多个文件载入，而不需要限定在唯一的文件中。
    
<span style="font-size:12px;"><strong>3. insert+select</strong> </span>

    这个是完全不同于文件操作的数据导入方式。官方指导为：

1. Standard syntax:   
2. INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement    
3.   
4. Hive extension (multiple inserts):   
5. FROM from_statement   
6. INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1   
7. [INSERT OVERWRITE TABLE tablename2 [PARTITION ...] select_statement2] ...   
8.   
9. Hive extension (dynamic partition inserts):   
10. INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement  

    这个的用法，和上面两种直接操作file的方式，截然不同。从sql语句本身理解，就是把查询到的数据，直接导入另外一张表。这个暂时不仔细分析，之后查询章节，再细讲。

4. alter 表，对分区操作

    在对表结构进行修改的时候，我们可以增加一个新的分区，在增加新分区的同时，将数据直接load到新的分区当中。

1. ALTER TABLE table_name ADD  
2.   partition_spec [ LOCATION 'location1' ]   
3.   partition_spec [ LOCATION 'location2' ] ...