整理和总结hive sql

进入hive shell
#hive或者hive --service cli
Hive 的启动方式:
hive  命令行模式，直接输入/hive/bin/hive的执行程序，或者输入 hive –service cli
hive  web界面的启动方式，hive –service hwi  
hive  远程服务 (端口号10000) 启动方式，hive --service hiveserver
hive  远程后台启动(关闭终端hive服务不退出): nohup hive -–service hiveserver  &
显示所有函数：
hive> show functions;
查看函数用法：
hive> describe function substr;
查看hive为某个查询使用多少个MapReduce作业
hive> Explain select a.id from tbname a;
--------------------------------------------------------------------------
表结构操作：
托管表和外部表
托管表会将数据移入Hive的warehouse目录；外部表则不会。经验法则是，如果所有处理都由Hive完成，
应该使用托管表；但如果要用Hive和其它工具来处理同一个数据集，则使用外部表。
创建表(通常stored as textfile)：
hive> create table tbName (id int,name string) stored as textfile;
创建表并且按分割符分割行中的字段值(即导入数据的时候被导入数据是以该分割符划分的，否则导入后为null，缺省列为null)；
hive> create table tbName (id int,name string) row format delimited fields terminated by ',';
创建外部表:
hive>create external table extbName(id int, name string);
创建表并创建单分区字段ds(分区表指的是在创建表时指定的partition的分区空间。):
hive> create table tbName2 (id int, name string) partitioned by (ds string); 
创建表并创建双分区字段ds:
hive> create table tbname3 (id int, content string) partitioned by (day string, hour string);
表添加一列:
hive> alter table tbName add columns (new_col int);
添加一列并增加列字段注释:
hive> alter table tbName add columns (new_col2 int comment 'a comment');
更改表名:
hive> alter table tbName rename to tbName3;
删除表(删除表的元数据，如果是托管表还会删除表的数据):
hive>drop table tbName;
只删除内容(只删除表的内容，而保留元数据，则删除数据文件)：
hive>dfs –rmr ‘warehouse/my-table’;
删除分区，分区的元数据和数据将被一并删除：
hive>alter table tbname2 drop partition (dt='2008-08-08', hour='09');
--------------------------------------------------------------------------
元数据存储(从HDFS中将数据导入到表中都是瞬时的):
将文件中的数据加载到表中(文件要有后缀名，缺省列默认为null):
hive> load data local inpath 'myTest.txt' overwrite into table tbName;
在已创立的表上添加单分区并指定数据：
hive> alter table tbname2 add partition (ds='20120701') location '/user/hadoop/his_trans/record/20120701';
在已创立的表上添加双分区并指定数据：
hive> alter table tbname2 add partition (ds='2008-08-08', hour='08') location '/path/pv1.txt' partition (dt='2008-08-08', hour='09') location '/path/pv2.txt';
加载本地数据，根据给定分区列信息:
hive> alter table tbname2 add partition (ds='2013-12-12');
hdfs数据加载进分区表中语法(当数据被加载至表中时，不会对数据进行任何转换。Load操作只是将数据复制至Hive表对应的位置)[不建议使用]：
hive> load data local inpath 'part.txt' overwrite into table tbName2 partition(ds='2013-12-12');
hive> load data inpath '/user/hadoop/*' into table tbname3 partition(dt='2008-08-08', hour='08'); 
--------------------------------------------------------------------------
SQL 操作：
查看表结构：
hive> describe tbname;
hive> desc tbname;
显示所有表:
hive> show tables;
按正条件（正则表达式）显示表：
hive> show tables '.*s';
查询表数据不会做mapreduce操作：
hive> select * from tbName;
查询一列数据，会做mapreduce操作：
hive> select a.id from tbname a ;
基于分区的查询的语句：
hive> select tbname2.* from tbname2 a where a.ds='2013-12-12' ;
查看分区语句：
hive> show partitions tbname2;
函数avg/sum/count/group by/order by (desc)/limit:
select logdate, count(logdate) as count from access_1 group by logdate order by count limit 5;
内连接(inner join)：
hive> SELECT sales.*, things.* FROM sales JOIN things ON (sales.id = things.id);
外连接：
hive> SELECT sales.*, things.* FROM sales LEFT OUTER JOIN things ON (sales.id = things.id);
hive> SELECT sales.*, things.* FROM sales RIGHT OUTER JOIN things ON (sales.id = things.id);
hive> SELECT sales.*, things.* FROM sales FULL OUTER JOIN things ON (sales.id = things.id);
in查询：Hive不支持，但可以使用LEFT SEMI JOIN
hive> SELECT * FROM things LEFT SEMI JOIN sales ON (sales.id = things.id);
相当于sql语句：SELECT * FROM things WHERE things.id IN (SELECT id from sales);
Map连接：Hive可以把较小的表放入每个Mapper的内存来执行连接操作
hive> SELECT /*+ MAPJOIN(things) */ sales.*, things.* FROM sales JOIN things ON (sales.id = things.id);
INSERT OVERWRITE TABLE ..SELECT：新表预先存在
hive> FROM records2
    > INSERT OVERWRITE TABLE stations_by_year SELECT year, COUNT(DISTINCT station) GROUP BY year 
    > INSERT OVERWRITE TABLE records_by_year SELECT year, COUNT(1) GROUP BY year
    > INSERT OVERWRITE TABLE good_records_by_year SELECT year, COUNT(1) WHERE temperature != 9999 AND 
    (quality = 0 OR quality = 1 OR quality = 4 OR quality = 5 OR quality = 9) GROUP BY year;  
CREATE TABLE ... AS SELECT：新表表预先不存在
hive>CREATE TABLE target AS SELECT col1,col2 FROM source;
创建视图：
hive> CREATE VIEW valid_records AS SELECT * FROM records2 WHERE temperature !=9999;
查看视图详细信息：
hive> DESCRIBE EXTENDED valid_records;
--------------------------------------------------------------------------
将查询数据输出至目录
hive> insert overwrite directory '/tmp/hdfs_out' select a.* from tbname2 a where a.ds='2013-12-12';
将查询结果输出至本地目录
hive> insert overwrite local directory '/tmp/local_out' select ds,count(1) from tbname group by ds;
hive> insert overwrite table events select a.* from tbname a where a.id < 100;
hive> insert overwrite local directory '/tmp/sum' select sum(a.pc) from tbpc a ;
将一个表的统计结果插入另一个表中
hive> from tbname a insert overwrite table events select a.bar,count(1) where a.foo > 0 group by a.bar;
hive> insert overwrite table events select a.bar,count(1) from tbname a where a.foo > 0 group by a.bar;
JOIN:
hive> from tbname t1 join tbname2 t2 on (t1.id = t2.id) insert overwrite table events select t1.id,t1.name,t2,ds;
将多表数据插入到同一表中
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;
将文件流直接插入文件
hive> FROM invites a INSERT OVERWRITE TABLE events SELECT TRANSFORM(a.foo, a.bar) AS (oof, rab) USING '/bin/cat' WHERE a.ds > '2008-08-09';
This streams the data in the map phase through the script /bin/cat (like hadoop streaming). Similarly - streaming can be used on the reduce 
side (please see the Hive Tutorial or examples) 
--------------------------------------------------------------------------
### 错误信息 ### 
问题：load数据全部为null  
原因：数据分隔符的问题，反序列化数据的时候出错了，定义表的时候需要定义数据分隔符。
解决：row format delimited fields terminated by ',' stored as textfile;

create table mytable(key int , value string ) row format delimited fields terminated by ',' escaped by '\\' stored as textfile;
[row format delimited]是用来设置创建的表在加载数据的时候，支持的列分隔符，如以','为分隔符；row format delimited fields terminated by ',';
[terminated by]分隔符：意思是以什么字符作为分隔符，默认情况下是tab字符（\t） 
[enclosed by]字段括起字符
[escaped by]转义字符
使用"\"符号转义或者写作:ALTER TABLE splitchar SET SERDEPROPERTIES ('escape.delim' = '\\');
[stored as file_format]:是用来设置加载数据的数据类型。Hive本身支持的文件格式只有：Text File，Sequence File。
如果文件数据是纯文本，可以使用 [stored as textfile]。
如果数据需要压缩，使用 [stored as sequence] 通常情况，只要不需要保存序列化的对象，我们默认采用[STORED AS TEXTFILE]。
将CSV中数据导入表中：
add jar /home/hadoop/csv-serde-1.1.2.jar;//引用了这个jar包，关于这个表的所有操作都要引入这个jar。
row format serde 'com.bizo.hive.serde.csv.CSVSerde'
eg：create external table trans_data
(
id int,
name string
)
partitioned by (pdate string) 
row format serde 'com.bizo.hive.serde.csv.CSVSerde' stored as textfile;
alter table trans_data add partition (pdate='20120701') location '/user/hadoop/his_trans/record/20120701';
--------------------------------------------------------------------------
### 错误信息 ### 
问题：java.lang.OutOfMemoryError: Java heap space
解决：检查hiveserver服务是否开启
--------------------------------------------------------------------------
### 错误信息 ###
java.lang.NoSuchMethodError: com.facebook.fb303.FacebookService
由于hadoop与hive版本不兼容导致(hadoop-0.20.2+320)
解决方法：mv $HADOOP_HOME/lib/libfb303.jar $HADOOP_HOME/lib/libfb303.jar_backup && ln -s $HIVE_HOME/lib/libfb303.jar $HADOOP_HOME/lib/libfb303.jar