[root@hadoop-yarn1 hbase]# su hdfs
tab1.csv:
1,true,123.123,2012-10-24 08:55:00
2,false,1243.5,2012-10-25 13:40:00
3,false,24453.325,2008-08-22 09:33:21.123
4,false,243423.325,2007-05-12 22:32:21.33454
5,true,243.325,1953-04-22 09:11:33
tab2.csv:
1,true,12789.123
2,false,1243.5
3,false,24453.325
4,false,2423.3254
5,true,243.325
60,false,243565423.325
70,true,243.325
80,false,243423.325
90,true,243.325
Put each .csv file into a separate HDFS directory using commands like the following, which use paths available in the Impala Demo VM:
$ hdfs dfs -mkdir /user/ercan/tab1
$ hdfs dfs -put tab1.csv /user/ercan/tab1
$ hdfs dfs -cat /user/ercan/tab1/tab1.csv
$ hdfs dfs -ls /user/ercan/tab1
Found 1 items
-rw-r--r-- 1 ercan ercan 192 2013-04-02 20:08 /user/ercan/tab1/tab1.csv
$ hdfs dfs -mkdir /user/ercan/tab2
$ hdfs dfs -put tab2.csv /user/ercan/tab2
$ hdfs dfs -ls /user/ercan/tab2
DROP TABLE IF EXISTS tab1;
CREATE EXTERNAL TABLE tab1
(
id INT,
col_1 BOOLEAN,
col_2 DOUBLE,
col_3 TIMESTAMP
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/user/ercan/tab1';
DROP TABLE IF EXISTS tab2;
CREATE EXTERNAL TABLE tab2
(
id INT,
col_1 BOOLEAN,
col_2 DOUBLE
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/user/ercan/tab2';
DROP TABLE IF EXISTS tab3;
CREATE TABLE tab3
(
id INT,
col_1 BOOLEAN,
col_2 DOUBLE,
month INT,
day INT
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
$ impala-shell -i hadoop-yarn1.mydomain.com -f q.sql
$ impala-shell
[Not connected] > connect hadoop-yarn1.mydomain.com;
[hadoop-yarn1.mydomain.com:21000] > select * from tab1;
Query: select * from tab1
Query finished, fetching results ...
+----+-------+------------+-------------------------------+
| id | col_1 | col_2 | col_3 |
+----+-------+------------+-------------------------------+
| 1 | true | 123.123 | 2012-10-24 08:55:00 |
| 2 | false | 1243.5 | 2012-10-25 13:40:00 |
| 3 | false | 24453.325 | 2008-08-22 09:33:21.123000000 |
| 4 | false | 243423.325 | 2007-05-12 22:32:21.334540000 |
| 5 | true | 243.325 | 1953-04-22 09:11:33 |
+----+-------+------------+-------------------------------+
Returned 5 row(s) in 28.36s
[hadoop-yarn1.mydomain.com:21000] >
[hadoop-yarn1.mydomain.com:21000] > select * from tab1 t join tab2 a on t.col_1=a.col_1;
Query: select * from tab1 t join tab2 a on t.col_1=a.col_1
Query finished, fetching results ...
+----+-------+------------+-------------------------------+----+-------+---------------+
| id | col_1 | col_2 | col_3 | id | col_1 | col_2 |
+----+-------+------------+-------------------------------+----+-------+---------------+
| 1 | true | 123.123 | 2012-10-24 08:55:00 | 90 | true | 243.325 |
| 1 | true | 123.123 | 2012-10-24 08:55:00 | 70 | true | 243.325 |
| 1 | true | 123.123 | 2012-10-24 08:55:00 | 5 | true | 243.325 |
| 1 | true | 123.123 | 2012-10-24 08:55:00 | 1 | true | 12789.123 |
| 2 | false | 1243.5 | 2012-10-25 13:40:00 | 80 | false | 243423.325 |
| 2 | false | 1243.5 | 2012-10-25 13:40:00 | 60 | false | 243565423.325 |
| 2 | false | 1243.5 | 2012-10-25 13:40:00 | 4 | false | 2423.3254 |
| 2 | false | 1243.5 | 2012-10-25 13:40:00 | 3 | false | 24453.325 |
| 2 | false | 1243.5 | 2012-10-25 13:40:00 | 2 | false | 1243.5 |
| 3 | false | 24453.325 | 2008-08-22 09:33:21.123000000 | 80 | false | 243423.325 |
| 3 | false | 24453.325 | 2008-08-22 09:33:21.123000000 | 60 | false | 243565423.325 |
| 3 | false | 24453.325 | 2008-08-22 09:33:21.123000000 | 4 | false | 2423.3254 |
| 3 | false | 24453.325 | 2008-08-22 09:33:21.123000000 | 3 | false | 24453.325 |
| 3 | false | 24453.325 | 2008-08-22 09:33:21.123000000 | 2 | false | 1243.5 |
| 4 | false | 243423.325 | 2007-05-12 22:32:21.334540000 | 80 | false | 243423.325 |
| 4 | false | 243423.325 | 2007-05-12 22:32:21.334540000 | 60 | false | 243565423.325 |
| 4 | false | 243423.325 | 2007-05-12 22:32:21.334540000 | 4 | false | 2423.3254 |
| 4 | false | 243423.325 | 2007-05-12 22:32:21.334540000 | 3 | false | 24453.325 |
| 4 | false | 243423.325 | 2007-05-12 22:32:21.334540000 | 2 | false | 1243.5 |
| 5 | true | 243.325 | 1953-04-22 09:11:33 | 90 | true | 243.325 |
| 5 | true | 243.325 | 1953-04-22 09:11:33 | 70 | true | 243.325 |
| 5 | true | 243.325 | 1953-04-22 09:11:33 | 5 | true | 243.325 |
| 5 | true | 243.325 | 1953-04-22 09:11:33 | 1 | true | 12789.123 |
+----+-------+------------+-------------------------------+----+-------+---------------+
Returned 23 row(s) in 0.60s
[hadoop-yarn1.mydomain.com:21000] > select * from tab1 t join tab2 a on t.col_1=a.col_1 where a.id >80;
Query: select * from tab1 t join tab2 a on t.col_1=a.col_1 where a.id >80
Query finished, fetching results ...
+----+-------+---------+---------------------+----+-------+---------+
| id | col_1 | col_2 | col_3 | id | col_1 | col_2 |
+----+-------+---------+---------------------+----+-------+---------+
| 1 | true | 123.123 | 2012-10-24 08:55:00 | 90 | true | 243.325 |
| 5 | true | 243.325 | 1953-04-22 09:11:33 | 90 | true | 243.325 |
+----+-------+---------+---------------------+----+-------+---------+
Returned 2 row(s) in 0.57s
[hadoop-yarn1.mydomain.com:21000] > explain select * from tab1 t join tab2 a on t.col_1=a.col_1;
Explain query: select * from tab1 t join tab2 a on t.col_1=a.col_1
PLAN FRAGMENT 0
PARTITION: UNPARTITIONED
4:EXCHANGE
tuple ids: 0 1
PLAN FRAGMENT 1
PARTITION: RANDOM
STREAM DATA SINK
EXCHANGE ID: 4
UNPARTITIONED
2:HASH JOIN
| join op: INNER JOIN (BROADCAST)
| hash predicates:
| t.col_1 = a.col_1
| tuple ids: 0 1
|
|----3:EXCHANGE
| tuple ids: 1
|
0:SCAN HDFS
table=default.tab1 #partitions=1 size=193B
tuple ids: 0
PLAN FRAGMENT 2
PARTITION: RANDOM
STREAM DATA SINK
EXCHANGE ID: 3
UNPARTITIONED
1:SCAN HDFS
table=default.tab2 #partitions=1 size=158B compact
tuple ids: 1
[hadoop-yarn1.mydomain.com:21000] >
[hadoop-yarn1.mydomain.com:21000] > show tables;
Query: show tables
Query finished, fetching results ...
+------+
| name |
+------+
| tab1 |
| tab2 |
| tab3 |
+------+
create 'hbasealltypessmall', 'bools', 'ints', 'floats', 'strings'
tab1.csv:
1,true,123.123,2012-10-24 08:55:00
2,false,1243.5,2012-10-25 13:40:00
3,false,24453.325,2008-08-22 09:33:21.123
4,false,243423.325,2007-05-12 22:32:21.33454
5,true,243.325,1953-04-22 09:11:33
tab2.csv:
1,true,12789.123
2,false,1243.5
3,false,24453.325
4,false,2423.3254
5,true,243.325
60,false,243565423.325
70,true,243.325
80,false,243423.325
90,true,243.325
Put each .csv file into a separate HDFS directory using commands like the following, which use paths available in the Impala Demo VM:
$ hdfs dfs -mkdir /user/ercan/tab1
$ hdfs dfs -put tab1.csv /user/ercan/tab1
$ hdfs dfs -cat /user/ercan/tab1/tab1.csv
$ hdfs dfs -ls /user/ercan/tab1
Found 1 items
-rw-r--r-- 1 ercan ercan 192 2013-04-02 20:08 /user/ercan/tab1/tab1.csv
$ hdfs dfs -mkdir /user/ercan/tab2
$ hdfs dfs -put tab2.csv /user/ercan/tab2
$ hdfs dfs -ls /user/ercan/tab2
DROP TABLE IF EXISTS tab1;
CREATE EXTERNAL TABLE tab1
(
id INT,
col_1 BOOLEAN,
col_2 DOUBLE,
col_3 TIMESTAMP
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/user/ercan/tab1';
DROP TABLE IF EXISTS tab2;
CREATE EXTERNAL TABLE tab2
(
id INT,
col_1 BOOLEAN,
col_2 DOUBLE
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/user/ercan/tab2';
DROP TABLE IF EXISTS tab3;
CREATE TABLE tab3
(
id INT,
col_1 BOOLEAN,
col_2 DOUBLE,
month INT,
day INT
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
$ impala-shell -i hadoop-yarn1.mydomain.com -f q.sql
$ impala-shell
[Not connected] > connect hadoop-yarn1.mydomain.com;
[hadoop-yarn1.mydomain.com:21000] > select * from tab1;
Query: select * from tab1
Query finished, fetching results ...
+----+-------+------------+-------------------------------+
| id | col_1 | col_2 | col_3 |
+----+-------+------------+-------------------------------+
| 1 | true | 123.123 | 2012-10-24 08:55:00 |
| 2 | false | 1243.5 | 2012-10-25 13:40:00 |
| 3 | false | 24453.325 | 2008-08-22 09:33:21.123000000 |
| 4 | false | 243423.325 | 2007-05-12 22:32:21.334540000 |
| 5 | true | 243.325 | 1953-04-22 09:11:33 |
+----+-------+------------+-------------------------------+
Returned 5 row(s) in 28.36s
[hadoop-yarn1.mydomain.com:21000] >
[hadoop-yarn1.mydomain.com:21000] > select * from tab1 t join tab2 a on t.col_1=a.col_1;
Query: select * from tab1 t join tab2 a on t.col_1=a.col_1
Query finished, fetching results ...
+----+-------+------------+-------------------------------+----+-------+---------------+
| id | col_1 | col_2 | col_3 | id | col_1 | col_2 |
+----+-------+------------+-------------------------------+----+-------+---------------+
| 1 | true | 123.123 | 2012-10-24 08:55:00 | 90 | true | 243.325 |
| 1 | true | 123.123 | 2012-10-24 08:55:00 | 70 | true | 243.325 |
| 1 | true | 123.123 | 2012-10-24 08:55:00 | 5 | true | 243.325 |
| 1 | true | 123.123 | 2012-10-24 08:55:00 | 1 | true | 12789.123 |
| 2 | false | 1243.5 | 2012-10-25 13:40:00 | 80 | false | 243423.325 |
| 2 | false | 1243.5 | 2012-10-25 13:40:00 | 60 | false | 243565423.325 |
| 2 | false | 1243.5 | 2012-10-25 13:40:00 | 4 | false | 2423.3254 |
| 2 | false | 1243.5 | 2012-10-25 13:40:00 | 3 | false | 24453.325 |
| 2 | false | 1243.5 | 2012-10-25 13:40:00 | 2 | false | 1243.5 |
| 3 | false | 24453.325 | 2008-08-22 09:33:21.123000000 | 80 | false | 243423.325 |
| 3 | false | 24453.325 | 2008-08-22 09:33:21.123000000 | 60 | false | 243565423.325 |
| 3 | false | 24453.325 | 2008-08-22 09:33:21.123000000 | 4 | false | 2423.3254 |
| 3 | false | 24453.325 | 2008-08-22 09:33:21.123000000 | 3 | false | 24453.325 |
| 3 | false | 24453.325 | 2008-08-22 09:33:21.123000000 | 2 | false | 1243.5 |
| 4 | false | 243423.325 | 2007-05-12 22:32:21.334540000 | 80 | false | 243423.325 |
| 4 | false | 243423.325 | 2007-05-12 22:32:21.334540000 | 60 | false | 243565423.325 |
| 4 | false | 243423.325 | 2007-05-12 22:32:21.334540000 | 4 | false | 2423.3254 |
| 4 | false | 243423.325 | 2007-05-12 22:32:21.334540000 | 3 | false | 24453.325 |
| 4 | false | 243423.325 | 2007-05-12 22:32:21.334540000 | 2 | false | 1243.5 |
| 5 | true | 243.325 | 1953-04-22 09:11:33 | 90 | true | 243.325 |
| 5 | true | 243.325 | 1953-04-22 09:11:33 | 70 | true | 243.325 |
| 5 | true | 243.325 | 1953-04-22 09:11:33 | 5 | true | 243.325 |
| 5 | true | 243.325 | 1953-04-22 09:11:33 | 1 | true | 12789.123 |
+----+-------+------------+-------------------------------+----+-------+---------------+
Returned 23 row(s) in 0.60s
[hadoop-yarn1.mydomain.com:21000] > select * from tab1 t join tab2 a on t.col_1=a.col_1 where a.id >80;
Query: select * from tab1 t join tab2 a on t.col_1=a.col_1 where a.id >80
Query finished, fetching results ...
+----+-------+---------+---------------------+----+-------+---------+
| id | col_1 | col_2 | col_3 | id | col_1 | col_2 |
+----+-------+---------+---------------------+----+-------+---------+
| 1 | true | 123.123 | 2012-10-24 08:55:00 | 90 | true | 243.325 |
| 5 | true | 243.325 | 1953-04-22 09:11:33 | 90 | true | 243.325 |
+----+-------+---------+---------------------+----+-------+---------+
Returned 2 row(s) in 0.57s
[hadoop-yarn1.mydomain.com:21000] > explain select * from tab1 t join tab2 a on t.col_1=a.col_1;
Explain query: select * from tab1 t join tab2 a on t.col_1=a.col_1
PLAN FRAGMENT 0
PARTITION: UNPARTITIONED
4:EXCHANGE
tuple ids: 0 1
PLAN FRAGMENT 1
PARTITION: RANDOM
STREAM DATA SINK
EXCHANGE ID: 4
UNPARTITIONED
2:HASH JOIN
| join op: INNER JOIN (BROADCAST)
| hash predicates:
| t.col_1 = a.col_1
| tuple ids: 0 1
|
|----3:EXCHANGE
| tuple ids: 1
|
0:SCAN HDFS
table=default.tab1 #partitions=1 size=193B
tuple ids: 0
PLAN FRAGMENT 2
PARTITION: RANDOM
STREAM DATA SINK
EXCHANGE ID: 3
UNPARTITIONED
1:SCAN HDFS
table=default.tab2 #partitions=1 size=158B compact
tuple ids: 1
[hadoop-yarn1.mydomain.com:21000] >
[hadoop-yarn1.mydomain.com:21000] > show tables;
Query: show tables
Query finished, fetching results ...
+------+
| name |
+------+
| tab1 |
| tab2 |
| tab3 |
+------+
create 'hbasealltypessmall', 'bools', 'ints', 'floats', 'strings'
Comments
Post a Comment