整理和总结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 =