Impala(一) 基本使用

╰半橙微兮° 2023-03-13 03:37 5阅读 0赞

## Impala DEMO ##

### impala shell ###

[root@flinkhadoop ~]# impala-shell 
    Starting Impala Shell without Kerberos authentication
    Opened TCP connection to flinkhadoop:21000
    Connected to flinkhadoop:21000
    Server version: impalad version 3.2.0-cdh6.3.0 RELEASE (build 495397101e5807c701df71ea288f4815d69c2c8a)
    ***********************************************************************************
    Welcome to the Impala shell.
    (Impala Shell v3.2.0-cdh6.3.0 (4953971) built on Thu Jul 18 10:24:11 PDT 2019)
    
    Run the PROFILE command after a query has finished to see a comprehensive summary
    of all the performance and diagnostic information that Impala gathered for that
    query. Be warned, it can be very long!
    ***********************************************************************************

### HUE ###

SELECT * FROM student;
    
    insert into student values(2, "s2");
    insert into student values(3, "s3");
    insert into student values(4, "s4");

## 数据类型 ##

![image-20200510134111653][]

> 注意：Impala 虽然支持 array，map，struct 复杂数据类型，但是支持并不完全，一般处理方 法，将复杂类型转化为基本类型，通过 hive 创建表。

## DML & DDL ##

### DDL ###

CREATE DATABASE [IF NOT EXISTS] database_name
    [COMMENT database_comment]
    [LOCATION hdfs_path];

#### 数据库管理 ####

#------------------------ 创建数据库 ——---------------------
    create database hive_db;
     
     show databases;
    
     show databases like 'hive*';
    
    
     describe database hive_db;
    
    
    #---------------------------- 删除数据库 ---------------------
     drop database hive_db;
     ## 级联删除，会将表删除
     drop database hive_db cascade;

> 注：
> 
> Impala 不支持 alter database 语法
> 
> 当数据库被 USE 语句选中时，无法删除

#### 表管理 ####

##### 内部表 #####

drop table if exists student2;
    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';
    #--------------------------
    describe formatted student2;

##### 外部表 #####

create external table stu_external(
     id int,
     name string)
     row format delimited fields terminated by '\t' ;

##### 分区表 #####

create table stu_par(id int, name string)
     partitioned by (month string)
     row format delimited
     fields terminated by '\t';
     
     #---------------------- 加载数据 ---------------------------------------
    alter table stu_par add partition (month='201810');
    
    load data inpath '/user/admin/student.txt' into table stu_par partition(month='201810');
    
    insert into table stu_par partition (month = '201811') select * from student;
     
     #---------------------- 查询分区表里面的数据 ----------------------------
     select * from stu_par where month = '201811';
     
     #------------------------ 增加多个分区 ----------------------------------
    alter table stu_par add partition (month='201812') partition (month='201813');
    
    #-------------------------------删除分区---------------------------------
    alter table stu_par drop partition (month='201812');
    
    #-------------------------------查看分区---------------------------------
    show partitions stu_par;

![image-20200510134820317][]

### DML 数据操作 ###

与hive基本一致

#### 数据导入 ####

> impala 不支持 load data local inpath…

#### 数据导出 ####

> 1.  impala 不支持 insert overwrite…语法导出数据
> 
>  2. impala 数据导出一般使用 impala -o
> 
> 1.  Impala 不支持 export 和 import 命令

#### 查询 ####

> 1.  基本的语法跟 hive 的查询语句大体一样
> 2.  Impala 不支持 CLUSTER BY, DISTRIBUTE BY, SORT BY
> 3.  Impala 中不支持分桶表
> 4.  Impala 不支持 COLLECT\_SET(col)和 explode（col）函数
> 5.  Impala 支持开窗函数

## 存储和压缩 ##

![image-20200510132635240][]

## Impala 附录 ##

### impala shell 命令 ###

![image-20200510131346457][]

### impala 操作命令 ###

![image-20200510131431311][]

[image-20200510134111653]: /images/20230312/17d517d4ec7447a49f62fade1c62809c.png
[image-20200510134820317]: /images/20230312/0979604ead6a47dda52964c4afa86734.png
[image-20200510132635240]: /images/20230312/980d2a2695d542288ef4db7b0dc5dbea.png
[image-20200510131346457]: /images/20230312/d0201799746440dc8492f5cc49f0d68d.png
[image-20200510131431311]: /images/20230312/278b0182b3cb40cd81910b506a1d47f5.png