impala sql转成hive sql impala sql语法

Impala-shell 命令参数 sql语法 java开发

• impala-shell外部命令
• impala-shell内部命令
• Impala sql语法

• 数据库特定语句
• 表特定语句

• Impala数据导入方式
• Impala的java开发

• 下载impala jdbc依赖
• 创建java工程
• java api

impala-shell外部命令

• 所谓的外部命令指的是不需要进入到impala-shell交互命令行当中即可执行的命令参数。impala-shell后面执行的时候可以带很多参数。你可以在启动 impala-shell 时设置，用于修改命令执行环境。

impala-shell –h可以帮助我们查看帮助手册。也可以参考课程附件资料。
比如几个常见的：
impala-shell –r刷新impala元数据，与建立连接后执行 REFRESH 语句效果相同
impala-shell –f 文件路径 执行指的的sql查询文件。
impala-shell –i指定连接运行 impalad 守护进程的主机。默认端口是 21000。你可以连接到集群中运行 impalad 的任意主机。
impala-shell –o保存执行结果到文件当中去。

impala-shell内部命令

• 所谓内部命令是指，进入impala-shell命令行之后可以执行的语法
• connect hostname 连接到指定的机器impalad上去执行
• refresh dbname.tablename增量刷新，刷新某一张表的元数据，主要用于刷新hive当中数据表里面的数据改变的情况
• invalidate metadata全量刷新，性能消耗较大，主要用于hive当中新建数据库或者数据库表的时候来进行刷新
• quit/exit命令 从Impala shell中弹出
• explain 命令 用于查看sql语句的执行计划
  explain的值可以设置成0,1,2,3等几个值，其中3级别是最高的，可以打印出最全的信息
  set explain_level=3;
• profile命令执行sql语句之后执行，可以
  打印出更加详细的执行步骤，主要用于查询结果的查看，集群的调优等
• 注意:如果在hive窗口中插入数据或者新建的数据库或者数据库表，那么在impala当中是不可直接查询，需要执行invalidate metadata以通知元数据的更新；
• 在impala-shell当中插入的数据，在impala当中是可以直接查询到的，不需要刷新数据库，其中使用的就是catalog这个服务的功能实现的，catalog是impala1.2版本之后增加的模块功能，主要作用就是同步impala之间的元数据。
• 更新操作通知Catalog，Catalog通过广播的方式通知其它的Impalad进程。默认情况下Catalog是异步加载元数据的，因此查询可能需要等待元数据加载完成之后才能进行（第一次加载）。

Impala sql语法

数据库特定语句

创建数据库

CREATE DATABASE语句用于在Impala中创建新数据库。
CREATE DATABASE IF NOT EXISTS database_name;
这里，IF NOT EXISTS是一个可选的子句。如果我们使用此子句，则只有在没有具有相同名称的现有数据库时，才会创建具有给定名称的数据库

impala默认使用impala用户执行操作，会报权限不足问题，解决办法：
一：给HDFS指定文件夹授予权限
hadoop fs -chmod -R 777 hdfs://node-1:9000/user/hive
二：haoop 配置文件中hdfs-site.xml 中设置权限为false

上述两种方式都可以

默认就会在hive的数仓路径下创建新的数据库名文件夹
/user/hive/warehouse/ittest.db

也可以在创建数据库的时候指定hdfs路径。需要注意该路径的权限。
hadoop fs -mkdir -p /input/impala
hadoop fs -chmod -R 777 /input/impala 


create  external table  t3(id int ,name string ,age int )  row  format  delimited fields terminated  by  '\t' location  '/input/impala/external';

删除数据库

Impala的DROP DATABASE语句用于从Impala中删除数据库。 在删除数据库之前，建议从中删除所有表。
如果使用级联删除，Impala会在删除指定数据库中的表之前删除它。
DROP database sample cascade;

表特定语句

create table语句

• CREATE TABLE语句用于在Impala中的所需数据库中创建新表。 需要指定表名字并定义其列和每列的数据类型。
• impala支持的数据类型和hive类似，除了sql类型外，还支持java类型

create table IF NOT EXISTS database_name.table_name (
   column1 data_type,
   column2 data_type,
   column3 data_type,
   ………
   columnN data_type
);

CREATE TABLE IF NOT EXISTS my_db.student(name STRING, age INT, contact INT );

• 默认建表的数据存储路径跟hive一致。也可以在建表的时候通过location指定具体路径，需要注意hdfs权限问题。
  
  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 );

overwrite覆盖子句覆盖表当中全部记录。 覆盖的记录将从表中永久删除。
Insert overwrite employee values (1, 'Ram', 26, 'Vishakhapatnam', 37000 );

select语句

• Impala SELECT语句用于从数据库中的一个或多个表中提取数据。 此查询以表的形式返回数据

describe语句

• Impala中的describe语句用于提供表的描述。 此语句的结果包含有关表的信息，例如列名称及其数据类型
  Describe table_name;
• 此外，还可以使用hive的查询表元数据信息语句。
  desc formatted table_name;

alter table

• Impala中的Alter table语句用于对给定表执行更改。使用此语句，我们可以添加，删除或修改现有表中的列，也可以重命名它们

表重命名：
ALTER TABLE [old_db_name.]old_table_name RENAME TO
 [new_db_name.]new_table_name
向表中添加列：
ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...])
从表中删除列：
ALTER TABLE name DROP [COLUMN] column_name
更改列的名称和类型：
ALTER TABLE name CHANGE column_name new_name new_type

delete、truncate table

• Impala drop table语句用于删除Impala中的现有表。此语句还会删除内部表的底层HDFS文件。
• 注意：使用此命令时必须小心，因为删除表后，表中可用的所有信息也将永远丢失。
  DROP table database_name.table_name;



• Impala的Truncate Table语句用于从现有表中删除所有记录。保留表结构。
• 您也可以使用DROP TABLE命令删除一个完整的表，但它会从数据库中删除完整的表结构，如果您希望存储一些数据，您将需要重新创建此表。
  truncate table_name;

view视图

• 视图仅仅是存储在数据库中具有关联名称的Impala查询语言的语句。 它是以预定义的SQL查询形式的表的组合。
• 视图可以包含表的所有行或选定的行。
  Create View IF NOT EXISTS view_name as Select statement

创建视图view、查询视图view
CREATE VIEW IF NOT EXISTS employee_view AS select name, age from employee;

修改视图
ALTER VIEW database_name.view_name为Select语句
删除视图
DROP VIEW database_name.view_name;

order by子句

• Impala ORDER BY子句用于根据一个或多个列以升序或降序对数据进行排序。 默认情况下，一些数据库按升序对查询结果进行排序。

select * from table_name ORDER BY col_name
 [ASC|DESC] [NULLS FIRST|NULLS LAST]

• 可以使用关键字ASC或DESC分别按升序或降序排列表中的数据。
• 如果我们使用NULLS FIRST，表中的所有空值都排列在顶行; 如果我们使用NULLS LAST，包含空值的行将最后排列。

group by子句

Impala GROUP BY子句与SELECT语句协作使用，以将相同的数据排列到组中。
select data from table_name Group BY col_name;

having子句

Impala中的Having子句允许您指定过滤哪些组结果显示在最终结果中的条件。
一般来说，Having子句与group by子句一起使用; 它将条件放置在由GROUP BY子句创建的组上。

limit、offset

• Impala中的limit子句用于将结果集的行数限制为所需的数，即查询的结果集不包含超过指定限制的记录。
• 一般来说，select查询的resultset中的行从0开始。使用offset子句，我们可以决定从哪里考虑输出。

with子句

如果查询太复杂，我们可以为复杂部分定义别名，并使用Impala的with子句将它们包含在查询中。
with x as (select 1), y as (select 2) (select * from x union y);
例如：使用with子句显示年龄大于25的员工和客户的记录。
with t1 as (select * from customers where age>25), 
   t2 as (select * from employee where age>25) 
   (select * from t1 union select * from t2);

distinct

Impala中的distinct运算符用于通过删除重复值来获取唯一值。
select distinct columns… from table_name;

Impala数据导入方式

load data

首先创建一个表：
create table user(id int ,name string,age int ) row format delimited fields terminated by "\t";

准备数据user.txt并上传到hdfs的 /user/impala路径下去

加载数据
load data inpath '/user/impala/' into table user;

查询加载的数据
select  *  from  user;

如果查询不不到数据，那么需要刷新一遍数据表。
refresh  user;

insert into values

这种方式非常类似于RDBMS的数据插入方式。
create table t_test2(id int,name string);
insert into table t_test2 values(1,”zhangsan”);

insert into select

• 插入一张表的数据来自于后面的select查询语句返回的结果。

create as select

• 建表的字段个数、类型、数据来自于后续的select查询语句

Impala的java开发

• 在实际工作当中，因为impala的查询比较快，所以可能有会使用到impala来做数据库查询的情况，可以通过java代码来进行操作impala的查询。

下载impala jdbc依赖

下载路径

• 因为cloudera属于商业公司性质，其提供的jar并不会出现在开源的maven仓库中，如果在企业中需要使用，请添加到企业maven私服

创建java工程

创建普通java工程，把依赖添加工程

java api

import java.sql.*;
public class ConnectionImpala {
    public static void connectImpala(){
        Connection con = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        String JDBC_DRIVER = "com.cloudera.impala.jdbc41.Driver";
        String CONNECTION_URL = "jdbc:impala://node01:21050";
        try
        {
            Class.forName(JDBC_DRIVER);
            con = (Connection) DriverManager.getConnection(CONNECTION_URL);
            ps = con.prepareStatement("select * from myhive.student;");
            rs = ps.executeQuery();
            while (rs.next())
            {
                System.out.printf("%s%5s%15s%5s\r\n",
                        rs.getString(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4));
            }
        } catch (Exception e)
        {
            e.printStackTrace();
        } finally
        {
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void main(String[] args) {
        connectImpala();
    }
}