impala数据导入汇总

数据进入到表中的几种不同方式：

1. put/distcp
hdfs dfs -put 从本地上传到hdfs

2.load data
[cdh2:21000] > select count(*) from tab1;
Query: select count(*) from tab1
+----------+
| count(*) |
+----------+
| 3279912  |

-----创建空表
[cdh2:21000] > create table tab2 like tab1;
Query: create table tab2 like tab1

Fetched 0 row(s) in 0.13s
[cdh2:21000] >
[cdh2:21000] > select count(*) from tab2;
Query: select count(*) from tab2
+----------+
| count(*) |
+----------+
| 0        |
+----------+
Fetched 1 row(s) in 5.31s

----load data（将目录tab1下的文件全部move到tab2下）
[cdh2:21000] > load data inpath '/user/hive/warehouse/db1.db/tab1' into table tab2;
Query: load data inpath '/user/hive/warehouse/db1.db/tab1' into table tab2
+----------------------------------------------------------+
| summary                                                  |
+----------------------------------------------------------+
| Loaded 1 file(s). Total files in destination location: 1 |
+----------------------------------------------------------+
Fetched 1 row(s) in 0.68s

[root@cdh0 data]# hdfs dfs -ls /user/hive/warehouse/db1.db/tab1
Found 1 items
-rw-r--r--   3 root hive  742707621 2015-01-23 10:35 /user/hive/warehouse/db1.db/tab1/xaa.csv
[root@cdh0 data]#
[root@cdh0 data]#
[root@cdh0 data]# hdfs dfs -ls /user/hive/warehouse/db1.db/tab2
[root@cdh0 data]#
[root@cdh0 data]#
[root@cdh0 data]#
[root@cdh0 data]# hdfs dfs -ls /user/hive/warehouse/db1.db/tab1
[root@cdh0 data]#
[root@cdh0 data]# hdfs dfs -ls /user/hive/warehouse/db1.db/tab2
Found 1 items
-rw-r--r--   3 root hive  742707621 2015-01-23 10:35 /user/hive/warehouse/db1.db/tab2/xaa.csv
[root@cdh0 data]#

----load之后，原表需要refresh，否则会报错
[cdh2:21000] > select count(*) from tab1;
Query: select count(*) from tab1
WARNINGS: Failed to open HDFS file hdfs://cdh0:8020/user/hive/warehouse/db1.db/tab1/xaa.csv
Error(2):

[cdh2:21000] > refresh tab1;
Query: refresh tab1

Fetched 0 row(s) in 0.56s
[cdh2:21000] >
[cdh2:21000] > select count(*) from tab1;
Query: select count(*) from tab1
+----------+
| count(*) |
+----------+
| 0        |
+----------+
Fetched 1 row(s) in 0.02s

3.ctas

[cdh2:21000] > create table tab3 STORED AS PARQUET as select * from tab2;
Query: create table tab3 STORED AS PARQUET as select * from tab2
+-------------------------+
| summary                 |
+-------------------------+
| Inserted 3279912 row(s) |
+-------------------------+
Fetched 1 row(s) in 4.85s
[cdh2:21000] >
[cdh2:21000] > select count(*) from tab3;
Query: select count(*) from tab3
+----------+
| count(*) |
+----------+
| 3279912  |
+----------+
Fetched 1 row(s) in 1.07s

以下遇到个问题和官当说法不同 where 1=0 没有起作用，以下是官方文档

-- Create new table and copy all data.
CREATE TABLE clone_of_t1 AS SELECT * FROM t1;
-- Same idea as CREATE TABLE LIKE, don't copy any data.
CREATE TABLE empty_clone_of_t1 AS SELECT * FROM t1 WHERE 1=0;

[cdh2:21000] > CREATE TABLE empty_clone_of_t1 AS SELECT * FROM tab1 WHERE 1=0;
Query: create TABLE empty_clone_of_t1 AS SELECT * FROM tab1 WHERE 1=0

+-------------------------+
| summary                 |
+-------------------------+
| Inserted 5653782 row(s) |
+-------------------------+
Fetched 1 row(s) in 30.71s

4.insert 方式
最简单，举个例子即可，不过insert分为into和overwrite，区别看眼就明白了

[cdh2:21000] > insert into tab1 select * from tab3;
Query: insert into tab1 select * from tab3
Inserted 3279912 row(s) in 17.11s

5.同一张表下支持不能格式的文件同时存放
[root@cdh0 data]# hdfs dfs -ls /user/hive/warehouse/db1.db/tab1
Found 4 items
-rw-r--r--   3 impala hive  291811534 2015-01-23 11:00 /user/hive/warehouse/db1.db/tab1/7a4d0328b98fcf78-8052e0dbd8c225b5_2104527952_data.0.
-rw-r--r--   3 impala hive  291745403 2015-01-23 11:00 /user/hive/warehouse/db1.db/tab1/7a4d0328b98fcf78-8052e0dbd8c225b6_2104527952_data.0.
drwxrwxrwt   - impala hive          0 2015-01-23 11:00 /user/hive/warehouse/db1.db/tab1/_impala_insert_staging
-rw-r--r--   3 root   hive  765975316 2015-01-23 16:08 /user/hive/warehouse/db1.db/tab1/xab.csv
[root@cdh0 data]#
[root@cdh0 data]#
[root@cdh0 data]#

[root@cdh0 data]# hdfs dfs -ls /user/hive/warehouse/db1.db/tab3

Found 3 items
drwxrwxrwt   - impala hive          0 2015-01-23 10:46 /user/hive/warehouse/db1.db/tab3/_impala_insert_staging
-rw-r--r--   3 impala hive   38022642 2015-01-23 10:46 /user/hive/warehouse/db1.db/tab3/f54a22a6adc813d6-a20d99d7d04209a8_1342452609_data.0.parq
-rw-r--r--   3 impala hive   29315637 2015-01-23 10:46 /user/hive/warehouse/db1.db/tab3/f54a22a6adc813d6-a20d99d7d04209a9_639956434_data.0.parq


[cdh2:21000] > load data inpath '/user/hive/warehouse/db1.db/tab1/7a4d0328b98fcf78-8052e0dbd8c225b5_2104527952_data.0.' into table tab4_ymd partition (year=2014,month=07,day=20);
Query: load data inpath '/user/hive/warehouse/db1.db/tab1/7a4d0328b98fcf78-8052e0dbd8c225b5_2104527952_data.0.' into table tab4_ymd partition (year=2014,month=07,day=20)
+----------------------------------------------------------+
| summary                                                  |
+----------------------------------------------------------+
| Loaded 1 file(s). Total files in destination location: 4 |
+----------------------------------------------------------+
Fetched 1 row(s) in 0.62s
[cdh2:21000] >
[cdh2:21000] > select count(*) from tab4_ymd;
Query: select count(*) from tab4_ymd
+----------+
| count(*) |
+----------+
| 1188650  |
+----------+
Fetched 1 row(s) in 1.32s
[cdh2:21000] >
[cdh2:21000] > load data inpath '/user/hive/warehouse/db1.db/tab3/f54a22a6adc813d6-a20d99d7d04209a8_1342452609_data.0.parq' into table tab4_ymd partition (year=2014,month=07,day=20);
Query: load data inpath '/user/hive/warehouse/db1.db/tab3/f54a22a6adc813d6-a20d99d7d04209a8_1342452609_data.0.parq' into table tab4_ymd partition (year=2014,month=07,day=20)
+----------------------------------------------------------+
| summary                                                  |
+----------------------------------------------------------+
| Loaded 1 file(s). Total files in destination location: 5 |
+----------------------------------------------------------+
Fetched 1 row(s) in 0.70s
[cdh2:21000] >
[cdh2:21000] > select count(*) from tab4_ymd;
Query: select count(*) from tab4_ymd
+----------+
| count(*) |
+----------+
| 1859533  |
+----------+
Fetched 1 row(s) in 1.30s
[cdh2:21000] >

[root@cdh0 data]#  hdfs dfs -du /user/hive/warehouse/db1.db/tab4_ymd/year=2014/month=7/day=20
0          0          /user/hive/warehouse/db1.db/tab4_ymd/year=2014/month=7/day=20/5e4e369df4ce6b75-7a6ae25d88d749e_1626585205_data.0.
0          0          /user/hive/warehouse/db1.db/tab4_ymd/year=2014/month=7/day=20/5e4e369df4ce6b75-7a6ae25d88d749f_571246668_data.0.
0          0          /user/hive/warehouse/db1.db/tab4_ymd/year=2014/month=7/day=20/5e4e369df4ce6b75-7a6ae25d88d74a0_571246668_data.0.
291811534  875434602  /user/hive/warehouse/db1.db/tab4_ymd/year=2014/month=7/day=20/7a4d0328b98fcf78-8052e0dbd8c225b5_2104527952_data.0.
38022642   114067926  /user/hive/warehouse/db1.db/tab4_ymd/year=2014/month=7/day=20/f54a22a6adc813d6-a20d99d7d04209a8_1342452609_data.0.parq

注意：
1.如果分区没有，load data不能自动创建
[cdh2:21000] > load data inpath '/user/hive/warehouse/db1.db/tab2' into table tab4_ymd partition (year=2014,month=07,day=19);
Query: load data inpath '/user/hive/warehouse/db1.db/tab2' into table tab4_ymd partition (year=2014,month=07,day=19)
ERROR: AnalysisException: Partition spec does not exist: (year=2014, month=7, day=19).
[cdh2:21000] >

2.目录不能包含嵌套
[root@cdh0 data]# hdfs dfs -ls /user/hive/warehouse/db1.db/tab2/*
Found 1 items
-rw-r--r--   3 root hive  742707621 2015-01-23 11:43 /user/hive/warehouse/db1.db/tab2/test/xaa.csv
-rw-r--r--   3 root hive  742707621 2015-01-23 10:35 /user/hive/warehouse/db1.db/tab2/xaa.csv

[cdh2:21000] > load data inpath '/user/hive/warehouse/db1.db/tab2' into table tab4_ymd partition (year=2014,month=07,day=20);
Query: load data inpath '/user/hive/warehouse/db1.db/tab2' into table tab4_ymd partition (year=2014,month=07,day=20)
ERROR: AnalysisException: INPATH location 'hdfs://cdh0:8020/user/hive/warehouse/db1.db/tab2' cannot contain subdirectories.
[cdh2:21000] >

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10037372/viewspace-1411981/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10037372/viewspace-1411981/