几种保存Hive查询结果的方法

几种保存Hive查询结果的方法

标签： hiveinsert overwritecreate table as

2014-09-29 13:34  2835人阅读  评论(0)  收藏  举报

  分类：

Hive

版权声明：本文为博主原创文章，未经博主允许不得转载。

目录(?)[+]

很多时候，我们需要将Hive的查询（select）结果保存起来，方便进一步处理或查看。
在Hive里面提供了不同的方式来保存查询结果，在这里做下总结：

一、保存结果到本地

方法1：调用hive标准输出，将查询结果写到指定的文件中

这个方法最为常见，笔者也经常使用。sql的查询结果将直接保存到/tmp/out.txt中

[plain]  view plain  copy

 

1. $ hive -e "select user, login_timestamp from user_login" > /tmp/out.txt  

当然我们也可以查询保存到某个文件file.sql中，按下面的方式执行查询，并保存结果

[plain]  view plain  copy

 

1. $ hive -f file.sql > /tmp/out.txt  

 下面是file.sql的内容：

[plain]  view plain  copy

 

1. $ cat file.sql  

[plain]  view plain  copy

 

1. select user, login_timestamp from user_login  

hive客户的的详细使用方法可以参考hive的官方文档《 Hive Batch Mode Commands》

[sql]  view plain  copy

 

1. hive -e '<query-string>' executes the query string.  
2. hive -f <filepath> executes one or more SQL queries from a file.  

方法2：使用INSERT OVERWRITE LOCAL DIRECTORY结果到本地

[sql]  view plain  copy

 

1. hive> insert overwrite local directory "/tmp/out/"                                          
2.     > select user, login_time from user_login;  

上面的命令会将select user, login_time from user_login的查询结果保存到/tmp/out/本地目录下。

我们查看一下/tmp/out/目录下的文件，发现命令执行后，多了两个文件：

[plain]  view plain  copy

 

1. $ find /tmp/out/ -type f  
2. /tmp/out/.000000_0.crc  
3. /tmp/out/000000_0  

这两个文件存放的内容不一样，其中000000_0存放查询的结果，带有crc后缀的存放那个文件的crc32校验。

用vim打开查看下000000_0的内容：

[plain]  view plain  copy

 

1. vim /tmp/out/000000_0  
2.  1 user_1^A20140701  
3.  2 user_2^A20140701  
4.  3 user_2^A20140701  

可以看到，导出的查询结果字段之间是用^A（Ctrl+A）作为分割符，行与行之间用\n作为分割。

默认的字段分割符有时候可能不太方便，幸好Hive提供了修改分割符号的方法，我们只要在导出时指定就可以了：

[sql]  view plain  copy

 

1. hive> insert overwrite local directory "/tmp/out/"  
2.     > row format delimited fields terminated by "\t"   
3.     > select user, login_time from user_login;  

[plain]  view plain  copy

 

1. $ vim /tmp/out/000000_0  
2. 1 user_1 20140701  
3. 2 user_2 20140701  
4. 3 user_2 20140701  

可以看到字段分割符已经变成了tab（人眼看起来更舒服^-^）。

同样，我们也可以指定复杂类型(collection、map)的输出格式

更多关于INSERT OVERWRITE LOCAL DIRECTORY的语法，可以参考HIVE的官方文档《Writing data into the filesystem from queries》。

[sql]  view plain  copy

 

1. Standard syntax:  
2. INSERT OVERWRITE <span style="color:#ff0000;">[LOCAL] </span>DIRECTORY directory1  
3.   [ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive 0.11.0)  
4.   SELECT ... FROM ...  
5.    
6. Hive extension (multiple inserts):  
7. FROM from_statement  
8. INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1  
9. [INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...  
10.    
11.    
12. row_format  
13.   : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]  
14.         [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]  
15.         [NULL DEFINED AS char] (Note: Only available starting with Hive 0.13)  

二、保存结果到hdfs

保存查询结果到hdfs很简单，使用INSERT OVERWRITE DIRECTORY就可以完成操作：

[sql]  view plain  copy

 

1. hive> insert overwrite directory "/tmp/out/"  
2.     > row format delimited fields terminated by "\t"   
3.     > select user, login_time from user_login;  

需要注意的是，跟保存到本地文件系统的差别是，保存到hdfs时命令不需要指定LOCAL项

更多关于INSERT OVERWRITE DIRECTORY的语法，可以参考HIVE的官方文档《 Writing data into the filesystem from queries》。

三、保存结果到HIVE表

方法1、已经建好结果表，使用INSERT OVERWRITE TABLE以覆盖方式写入结果表

如果结果表已经建好，可以使用INSERT OVERWRITE TABLE将结果写入结果表：

[sql]  view plain  copy

 

1. hive> desc query_result;  
2. OK  
3. user                    string,  

[sql]  view plain  copy

 

1. login_time              bigint  
2. hive> insert overwrite table query_result       
3.     > select user, login_time from user_login;  

[sql]  view plain  copy

 

1. hive> select * from query_result;               
2. OK  
3. user_1  20140701  
4. user_2  20140701  
5. user_3  20140701  

当然，HIVE也提供了追加方式INSERT TABLE，可以在原有数据后面加上新的查询结果。在上面这个例子基础上，我们再追加一个查询结果：

[sql]  view plain  copy

 

1. hive> insert into table query_result  
2.     > select * from query_result;  

[sql]  view plain  copy

 

1. hive> select * from query_result;  
2. OK  
3. user_1  20140701  
4. user_2  20140701  
5. user_3  20140701  
6. <span style="color:#ff0000;">user_1   20140701  
7. user_2  20140701  
8. user_3  20140701</span>  

注意标红的部分，使用INSERT TABLE后，query_result增加了三行数据

更多关于INSERT OVERWRITE TABLE的语法，可以参考HIVE官方文档《Inserting data into Hive Tables from queries》

[sql]  view plain  copy

 

1. Standard syntax:  
2. INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;  
3. INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;  
4.    
5. Hive extension (multiple inserts):  
6. FROM from_statement  
7. INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1  
8. [INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2]  
9. [INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2] ...;  
10. FROM from_statement  
11. INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1  
12. [INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2]  
13. [INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2] ...;  
14.    
15. Hive extension (dynamic partition inserts):  
16. INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;  
17. INSERT INTO TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;  

方法2、如果需要新建一个表，用于存放查询结果，可以使用CREATE TABLE AS SELECT语法

[sql]  view plain  copy

 

1. hive> create table query_result   
2.     > as  
3.     > select user, login_time from user_login;  

[sql]  view plain  copy

 

1. hive> select * from query_result;              
2. OK  
3. user_1  20140701  
4. user_2  20140701  
5. user_3  20140701  

更多关于CREATE TABLE AS SELECT的语法，可以参考HIVE官方文档《 Create Table As Select (CTAS)》

[sql]  view plain  copy

 

1. CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name   (Note: TEMPORARY available starting with Hive 0.14.0)  
2.   [(col_name data_type [COMMENT col_comment], ...)]  
3.   [COMMENT table_comment]  
4.   [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]  
5.   [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]  
6.   [SKEWED BY (col_name, col_name, ...) ON ([(col_value, col_value, ...), ...|col_value, col_value, ...])  
7.                                       [STORED AS DIRECTORIES] (Note: Only available starting with Hive 0.10.0)]  
8.   [  
9.    [ROW FORMAT row_format] [STORED AS file_format]  
10.    | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  (Note: Only available starting with Hive 0.6.0)  
11.   ]  
12.   [LOCATION hdfs_path]  
13.   [TBLPROPERTIES (property_name=property_value, ...)]  (Note: Only available starting with Hive 0.6.0)  
14.   [AS select_statement]  (Note: Only available starting with Hive 0.5.0, and not supported when creating external tables.)  
15.    
16. CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name  
17.   LIKE existing_table_or_view_name  
18.   [LOCATION hdfs_path]  
19.    
20. data_type  
21.   : primitive_type  
22.   | array_type  
23.   | map_type  
24.   | struct_type  
25.   | union_type (Note: Only available starting with Hive 0.7.0)  
26.    
27. primitive_type  
28.   : TINYINT  
29.   | SMALLINT  
30.   | INT  
31.   | BIGINT  
32.   | BOOLEAN  
33.   | FLOAT  
34.   | DOUBLE  
35.   | STRING  
36.   | BINARY (Note: Only available starting with Hive 0.8.0)  
37.   | TIMESTAMP (Note: Only available starting with Hive 0.8.0)  
38.   | DECIMAL (Note: Only available starting with Hive 0.11.0)  
39.   | DECIMAL(precision, scale) (Note: Only available starting with Hive 0.13.0)  
40.   | VARCHAR (Note: Only available starting with Hive 0.12.0)  
41.   | CHAR (Note: Only available starting with Hive 0.13.0)  
42.    
43. array_type  
44.   : ARRAY < data_type >  
45.    
46. map_type  
47.   : MAP < primitive_type, data_type >  
48.    
49. struct_type  
50.   : STRUCT < col_name : data_type [COMMENT col_comment], ...>  
51.    
52. union_type  
53.    : UNIONTYPE < data_type, data_type, ... > (Note: Only available starting with Hive 0.7.0)  
54.    
55. row_format  
56.   : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]  
57.         [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]  
58.         [NULL DEFINED AS char] (Note: Only available starting with Hive 0.13)  
59.   | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]  
60.    
61. file_format:  
62.   : SEQUENCEFILE  
63.   | TEXTFILE  
64.   | RCFILE     (Note: Only available starting with Hive 0.6.0)  
65.   | ORC        (Note: Only available starting with Hive 0.11.0)  
66.   | AVRO       (Note: Only available starting with Hive 0.14.0)  
67.   | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname  

四、使用hdfs直接导出表

Hive是构建在hdfs上的，因此，我们可以使用hdfs的命令hadoop dfs -get直接导出表。

首先、我们先找到要导出的表存放到哪个目录下：

[sql]  view plain  copy

 

1. hive> show create table user_login;  
2. OK  
3. CREATE  TABLE `user_login`(  
4.   `user` string,   
5.   `login_time` bigint)  
6. ROW FORMAT SERDE   
7.   'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'   
8. STORED AS INPUTFORMAT   
9.   'org.apache.hadoop.mapred.TextInputFormat'   
10. OUTPUTFORMAT   
11.   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'  
12. <span style="color:#ff0000;">LOCATION  
13.   'file:/user/hive/warehouse/test.db/user_login'</span>  
14. TBLPROPERTIES (  
15.   'totalSize'='160',   
16.   'numRows'='10',   
17.   'rawDataSize'='150',   
18.   'COLUMN_STATS_ACCURATE'='true',   
19.   'numFiles'='1',   
20.   'transient_lastDdlTime'='1411544983')  
21. Time taken: 0.174 seconds, Fetched: 18 row(s)  

可以看到，user_login表存放到在file:/user/hive/warehouse/test.db/user_login。

接下来，直接利用hadoop dfs -get导出到本地：

[sql]  view plain  copy

 

1. hadoop dfs -get file:/user/hive/warehouse/test.db/user_login  /tmp/out/  

更多关于hadoop  dfs -get命令，可以参考hadoop dfs命令界面文档《File System Shell》

转载地址：http://blog.youkuaiyun.com/zhuce1986/article/details/39586189