Apache Impala insert、select和describe语句
insert语句
Impala的INSERT语句有两个子句: into和overwrite。
into用于插入新记录数据，overwrite用于覆盖已有的记录。
insert into table_name (column1, column2, column3,...columnN)
values (value1, value2, value3,...valueN);
Insert into table_name values (value1, value2, value2);
column1，column2，… columnN是要插入数据的表中的列的名称。
还可以添加值而不指定列名，但是，需要确保值的顺序与表中的列的顺序相同。
例如：
create table employee (Id INT, name STRING, age INT,address STRING, salary BIGINT);
insert into employee VALUES (1, 'Ramesh', 32, 'Ahmedabad', 20000 );
insert into employee values (2, 'Khilan', 25, 'Delhi', 15000 );
Insert into employee values (3, 'kaushik', 23, 'Kota', 30000 );
Insert into employee values (4, 'Chaitali', 25, 'Mumbai', 35000 );
Insert into employee values (5, 'Hardik', 27, 'Bhopal', 40000 );
Insert into employee values (6, 'Komal', 22, 'MP', 32000 );
[hadoop03.Hadoop.com:21000] > use hahaha;
Query: use hahaha
[hadoop03.Hadoop.com:21000] > create table employee (Id INT, name STRING, age INT,address STRING, salary BIGINT);
Query: create table employee (Id INT, name STRING, age INT,address STRING, salary BIGINT)
insert into employee VALUES (1, 'Ramesh', 32, 'Ahmedabad', 20000 );
insert into employee values (2, 'Khilan', 25, 'Delhi', 15000 );
Insert into employee values (3, 'kaushik', 23, 'Kota', 30000 );
Insert into employee values (4, 'Chaitali', 25, 'Mumbai', 35000 );
Insert into employee values (5, 'Hardik', 27, 'Bhopal', 40000 );
Insert into employee values (6, 'Komal', 22, 'MP', 32000 );Fetched 0 row(s) in 0.86s
[hadoop03.Hadoop.com:21000] > insert into employee VALUES (1, 'Ramesh', 32, 'Ahmedabad', 20000 );
Query: insert into employee VALUES (1, 'Ramesh', 32, 'Ahmedabad', 20000 )
Query submitted at: 2019-12-10 20:51:47 (Coordinator: http://hadoop03:25000)
Query progress can be monitored at: http://hadoop03:25000/query_plan?query_id=c24c7301f2a61062:8008552000000000
Modified 1 row(s) in 8.88s
[hadoop03.Hadoop.com:21000] > insert into employee values (2, 'Khilan', 25, 'Delhi', 15000 );
Query: insert into employee values (2, 'Khilan', 25, 'Delhi', 15000 )
Query submitted at: 2019-12-10 20:51:56 (Coordinator: http://hadoop03:25000)
Query progress can be monitored at: http://hadoop03:25000/query_plan?query_id=1744a9ef93a683b4:c5c8a55800000000
Modified 1 row(s) in 0.36s
[hadoop03.Hadoop.com:21000] > Insert into employee values (3, 'kaushik', 23, 'Kota', 30000 );
Query: insert into employee values (3, 'kaushik', 23, 'Kota', 30000 )
Query submitted at: 2019-12-10 20:51:57 (Coordinator: http://hadoop03:25000)
Query progress can be monitored at: http://hadoop03:25000/query_plan?query_id=814857e1d96cef89:77e3612a00000000
Modified 1 row(s) in 0.11s
[hadoop03.Hadoop.com:21000] > Insert into employee values (4, 'Chaitali', 25, 'Mumbai', 35000 );
Query: insert into employee values (4, 'Chaitali', 25, 'Mumbai', 35000 )
Query submitted at: 2019-12-10 20:51:57 (Coordinator: http://hadoop03:25000)
Query progress can be monitored at: http://hadoop03:25000/query_plan?query_id=964d7665bbbf3af0:e34c7bfc00000000
Modified 1 row(s) in 0.27s
[hadoop03.Hadoop.com:21000] > Insert into employee values (5, 'Hardik', 27, 'Bhopal', 40000 );
Query: insert into employee values (5, 'Hardik', 27, 'Bhopal', 40000 )
Query submitted at: 2019-12-10 20:51:57 (Coordinator: http://hadoop03:25000)
Query progress can be monitored at: http://hadoop03:25000/query_plan?query_id=d543a24cfa42e928:72344b2400000000
Modified 1 row(s) in 0.72s
[hadoop03.Hadoop.com:21000] > Insert into employee values (6, 'Komal', 22, 'MP', 32000 );
Query: insert into employee values (6, 'Komal', 22, 'MP', 32000 )
Query submitted at: 2019-12-10 20:51:58 (Coordinator: http://hadoop03:25000)
Query progress can be monitored at: http://hadoop03:25000/query_plan?query_id=24422f171a15246:f9a04f9500000000
Modified 1 row(s) in 0.23s
[hadoop03.Hadoop.com:21000] > select * from employee;
Query: select * from employee
Query submitted at: 2019-12-10 20:52:16 (Coordinator: http://hadoop03:25000)
Query progress can be monitored at: http://hadoop03:25000/query_plan?query_id=dc4f572fd45bde59:18cdb16300000000
+----+----------+-----+-----------+--------+
| id | name | age | address | salary |
+----+----------+-----+-----------+--------+
| 4 | Chaitali | 25 | Mumbai | 35000 |
| 6 | Komal | 22 | MP | 32000 |
| 2 | Khilan | 25 | Delhi | 15000 |
| 5 | Hardik | 27 | Bhopal | 40000 |
| 3 | kaushik | 23 | Kota | 30000 |
| 1 | Ramesh | 32 | Ahmedabad | 20000 |
+----+----------+-----+-----------+--------+
Fetched 6 row(s) in 2.06s
overwrite覆盖子句覆盖表当中全部记录。 覆盖的记录将从表中永久删除。
Insert overwrite employee values (1, 'Ram', 26, 'Vishakhapatnam', 37000 );
[hadoop03.Hadoop.com:21000] > Insert overwrite employee values (1, 'Ram', 26, 'Vishakhapatnam', 37000 );
Query: insert overwrite employee values (1, 'Ram', 26, 'Vishakhapatnam', 37000 )
Query submitted at: 2019-12-10 20:53:49 (Coordinator: http://hadoop03:25000)
Query progress can be monitored at: http://hadoop03:25000/query_plan?query_id=9943bab551d4105a:b26077e500000000
Modified 1 row(s) in 0.21s
[hadoop03.Hadoop.com:21000] > select * from employee;
Query: select * from employee
Query submitted at: 2019-12-10 20:53:52 (Coordinator: http://hadoop03:25000)
Query progress can be monitored at: http://hadoop03:25000/query_plan?query_id=b42e5384f0d6f55:bacdd7c000000000
+----+------+-----+----------------+--------+
| id | name | age | address | salary |
+----+------+-----+----------------+--------+
| 1 | Ram | 26 | Vishakhapatnam | 37000 |
+----+------+-----+----------------+--------+
Fetched 1 row(s) in 0.13s
select语句
Impala SELECT语句用于从数据库中的一个或多个表中提取数据。 此查询以表的形式返回数据。
[hadoop03.Hadoop.com:21000] > select * from employee;
Query: select * from employee
Query submitted at: 2019-12-10 20:56:43 (Coordinator: http://hadoop03:25000)
Query progress can be monitored at: http://hadoop03:25000/query_plan?query_id=dd495c7c2924a952:f44e04be00000000
+----+------+-----+----------------+--------+
| id | name | age | address | salary |
+----+------+-----+----------------+--------+
| 1 | Ram | 26 | Vishakhapatnam | 37000 |
+----+------+-----+----------------+--------+
Fetched 1 row(s) in 0.13s
describe语句
Impala中的describe语句用于提供表的描述。 此语句的结果包含有关表的信息，例如列名称及其数据类型。
Describe table_name;
[hadoop03.Hadoop.com:21000] > describe employee;
Query: describe employee
+---------+--------+---------+
| name | type | comment |
+---------+--------+---------+
| id | int | |
| name | string | |
| age | int | |
| address | string | |
| salary | bigint | |
+---------+--------+---------+
Fetched 5 row(s) in 0.93s
此外，还可以使用hive的查询表元数据信息语句。
desc formatted table_name;
[hadoop03.Hadoop.com:21000] > desc formatted employee;
Query: describe formatted employee
+------------------------------+-------------------------------------------------------------+----------------------+
| name | type | comment |
+------------------------------+-------------------------------------------------------------+----------------------+
| # col_name | data_type | comment |
| | NULL | NULL |
| id | int | NULL |
| name | string | NULL |
| age | int | NULL |
| address | string | NULL |
| salary | bigint | NULL |
| | NULL | NULL |
| # Detailed Table Information | NULL | NULL |
| Database: | hahaha | NULL |
| Owner: | root | NULL |
| CreateTime: | Tue Dec 10 20:51:47 CST 2019 | NULL |
| LastAccessTime: | UNKNOWN | NULL |
| Protect Mode: | None | NULL |
| Retention: | 0 | NULL |
| Location: | hdfs://hadoop01:8020/user/hive/warehouse/hahaha.db/employee | NULL |
| Table Type: | MANAGED_TABLE | NULL |
| Table Parameters: | NULL | NULL |
| | transient_lastDdlTime | 1575982307 |
| | NULL | NULL |
| # Storage Information | NULL | NULL |
| SerDe Library: | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe | NULL |
| InputFormat: | org.apache.hadoop.mapred.TextInputFormat | NULL |
| OutputFormat: | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat | NULL |
| Compressed: | No | NULL |
| Num Buckets: | 0 | NULL |
| Bucket Columns: | [] | NULL |
| Sort Columns: | [] | NULL |
+------------------------------+-------------------------------------------------------------+----------------------+
Fetched 28 row(s) in 2.35s
