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