HIVE学习笔记 二 精简版

HIVE学习笔记精简版
学习资料：《Hive_user_guide》+ http://www.aahyhaa.com/archives/316+ http://blog.youkuaiyun.com/jiedushi/article/details/6778521 ...

Hive中的数据可划分为如下类型：

表（Tables） 类似于传统数据库中的表。表中的数据存储在HDFS目录中。Hive为用户提供了一个框架，用于访问由DDL语句创建的具有基本数据类型格式的表（或分区）。用户还可以通过定义新的序列化和去序列化方法（又称为SerDe’s）的方式提供相应的抽取器（extractors）和加载器（loaders），用以访问新数据类型格式的表。表和新数据格式的关联关系被存储在系统目录(system catalog)中，在编译和执行查询的时候被Hive自动使用。Hive还提供了默认的序列化格式，默认的格式采用了压缩和惰性去序列化。此外，Hive还支持扩展表，这些表的数据可能存储在HDFS，NFS和本地目录中。

分区（Partitions） 每张表可以有1或多个分区用以划分数据，划分后的数据存放到表所在目录下的子目录中。比如，表T的数据存储在目录/wh/T，现按列ds=20090101和ctry=US对其进行划分，那么划分后的数据将会存储在目录/wh/T/ds=20090101/ctry=US中。

桶（Buckets） 分区中的数据还能依次分成不同的桶，桶的划分基于表中各列的哈希值（Hash），桶做为分区目录中的一个文件存储的。

hive引入partition和bucket的概念，这两个概念都是把数据划分成块，分区是粗粒度的划分桶是细粒度的划分，这样做为了可以让查询发生在小范围的数据上以提高效率。


hive-创建分区
HIVE的分区通过在创建表时启用partition by实现，用来partition的维度并不是实际数据的某一列，具体分区的标志是由插入内容时给定的。当要查询某一分区的内容时可以采用

where语句，形似where tablename.partition_key > a来实现。

创建含分区的表
CREATE TABLE page_view(viewTime INT, userid BIGINT,
     page_url STRING, referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User')
 COMMENT 'This is the page view table'
 PARTITIONED BY(dt STRING, country STRING)
 CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
 ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '\001'
   COLLECTION ITEMS TERMINATED BY '\002'
   MAP KEYS TERMINATED BY '\003'
 STORED AS SEQUENCEFILE;


a、单分区建表语句：create table day_table (id int, content string) partitioned by (dt string);单分区


表，按天分区，在表结构中存在id，content，dt三列。以dt为文件夹区分
b、 双分区建表语句：create table day_hour_table (id int, content string) partitioned by (dt string, 


hour string);双分区表，按天和小时分区，在表结构中新增加了dt和hour两列。


【指定location】
CREATE EXTERNAL TABLE page_view(viewTime INT, userid BIGINT,
     page_url STRING, referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User',
     country STRING COMMENT 'country of origination')
 COMMENT 'This is the staging page view table'
 ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'
 STORED AS TEXTFILE
 LOCATION '<hdfs_location>';


【Add Partitions】
ALTER TABLE table_name ADD [IF NOT EXISTS] 
PARTITION (dt='20101202') location '/user/hive/warehouse/c02_clickstat_fatdt1/part20101202' 
PARTITION (dt='20101203') location '/user/hive/warehouse/c02_clickstat_fatdt1/part20101203';


【Drop Partitions】
ALTER TABLE table_name DROP  PARTITION (dt='20101202');


【Rename Table】
ALTER TABLE table_name RENAME TO new_table_name
//这个命令可以让用户为表更名。数据所在的位置和分区名并不改变。换而言之，老的表


名并未“释放”，对老表的更改会改变新表的数据。


【Change Column】
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type 


[COMMENT col_comment] [FIRST|AFTER column_name]
这个命令可以允许改变列名、数据类型、注释、列位置或者它们的任意组合


【Add/Replace Columns】
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)


hive-创建视图
【Create View】
CREATE VIEW [IF NOT EXISTS] view_name [ (column_name [COMMENT column_comment], ...) ]
[COMMENT view_comment]
[TBLPROPERTIES (property_name = property_value, ...)] 
AS SELECT ...



hive-Show


show tables; 
show tables 'page.*'; 
show tables '.*view';


show partition page_view;//查看表的partition
describe invites; //查看表结构


查看分区内容
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');



HIVE装载数据没有做任何转换加载到表中的数据只是进入相应的配置单元表的位置移动数据文件。
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]


• filepath 可以是： 
o 相对路径，例如：project/data1 
o 绝对路径，例如： /user/hive/project/data1 
o 包含模式的完整 URI，例如：hdfs://namenode:9000/user/hive/project/data1 


从本地导入数据到表格并追加原表
LOAD DATA LOCAL INPATH `/tmp/pv_2008-06-08_us.txt` INTO TABLE c02 PARTITION(date='2008-06-08', country='US')


从本地导入数据到表格并追加记录
LOAD DATA LOCAL INPATH './examples/files/kv1.txt' INTO TABLE pokes; 


从hdfs导入数据到表格并覆盖原表
LOAD DATA INPATH '/user/admin/SqlldrDat/CnClickstat/20101101/18/clickstat_gp_fatdt0/0' INTO table c02_clickstat_fatdt1 OVERWRITE PARTITION (dt='20101201');

【Inserting data into Hive Tables from queries】

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement 


FROM from_statement 
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement2


INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement


INSERT OVERWRITE TABLE events SELECT a.bar, count(*) FROM invites a WHERE a.foo > 0 GROUP BY a.bar;


有一张表B至少有一条数据，我们想向表A（int，string）中插入一条数据，可以用下面的方法实现：
from B
insert table A select  1,‘abc’ limit 1；
//注：hive没有直接插入一条数据的SQL，也不支持update


【Writing data into filesystem from queries】
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2]


导出文件到本地
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/local_out' SELECT a.* FROM pokes a;


导出文件到HDFS
INSERT OVERWRITE DIRECTORY '/user/admin/SqlldrDat/CnClickstat/20101101/19/clickstat_gp_fatdt0/0' SELECT a.* FROM c02_clickstat_fatdt1 a WHERE dt=’20101201’;


一个源可以同时插入到多个目标表或目标文件，多目标insert可以用一句话来完成
FROM src
  INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100
  INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200
  INSERT OVERWRITE TABLE dest3 PARTITION(ds='2008-04-08', hr='12') SELECT src.key WHERE src.key >= 200 and src.key < 300
  INSERT OVERWRITE LOCAL DIRECTORY '/tmp/dest4.out' SELECT src.value WHERE src.key >= 300;