impala之DDL数据定义

创建数据库

CREATE DATABASE [IF NOT EXISTS] database_name

  [COMMENT database_comment]

  [LOCATION hdfs_path];

注：Impala不支持WITH DBPROPERTIE…语法

[hadoop103:21000] > create database db_hive

                  > WITH DBPROPERTIES('name' = 'ttt');

Query: create database db_hive

WITH DBPROPERTIES('name' = 'ttt')

ERROR: AnalysisException: Syntax error in line 2:

WITH DBPROPERTIES('name' = 'ttt')

^

Encountered: WITH

Expected: COMMENT, LOCATION

 

查询数据库

     显示数据库

[hadoop103:21000] > show databases;

[hadoop103:21000] > show databases like 'hive*';

Query: show databases like 'hive*'

+---------+---------+

| name    | comment |

+---------+---------+

| hive_db |         |

+---------+---------+

[hadoop103:21000] > desc database hive_db;

Query: describe database hive_db

+---------+----------+---------+

| name    | location | comment |

+---------+----------+---------+

| hive_db |          |         |

+---------+----------+---------+

 

删除数据库

[hadoop103:21000] > drop database hive_db;

[hadoop103:21000] > drop database hive_db cascade;

注：

Impala不支持alter database语法

当数据库被 USE 语句选中时，无法删除

 

创建表

       管理表(内部表)

[hadoop103:21000] > create table if not exists student2(

                  > id int, name string

                  > )

                  > row format delimited fields terminated by '\t'

                  > stored as textfile

                  > location '/user/hive/warehouse/student2';

[hadoop103:21000] > desc formatted student2;

 

外部表

[hadoop103:21000] > create external table stu_external(

                  > id int,

                  > name string)

                  > row format delimited fields terminated by '\t' ;

 

分区表

 

 创建分区表

[hadoop103:21000] > create table stu_par(id int, name string)

                  > partitioned by (month string)

                  > row format delimited

                  > fields terminated by '\t';

 

向表中导入数据

[hadoop103:21000] > alter table stu_par add partition (month='201810');

[hadoop103:21000] > load data inpath '/student.txt' into table stu_par partition(month='201810');

[hadoop103:21000] > insert into table stu_par partition (month = '201811')

                  > select * from student;

注意：

如果分区没有，load data导入数据时，不能自动创建分区。

 

查询分区表中的数据

[hadoop103:21000] > select * from stu_par where month = '201811';

 

增加多个分区

[hadoop103:21000] > alter table stu_par add partition (month='201812') partition (month='201813');

 

删除分区

[hadoop103:21000] >  alter table stu_par drop partition (month='201812');

 

查看分区

[hadoop103:21000] > show partitions stu_par;