整理和总结hive sql

布满荆棘的人生 2022-09-19 04:08 115阅读 0赞

进入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