Impala导⼊数据

insert into values

这种⽅式⾮常类似于RDBMS的数据插⼊⽅式。

1
2
3
create table t_test2(id int,name string);

insert into table t_test2 values(1,”zhangsan”);

insert into select

插⼊⼀张表的数据来⾃于后⾯的select查询语句返回的结果。

create table as select

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

load data⽅式，这种⽅式不建议在Impala中使⽤，先使⽤load data⽅式把数据加载到Hive表中，然后使⽤以上⽅式插⼊Impala表中。

Impala的JDBC⽅式查询

在实际⼯作当中，因为impala的查询⽐较快，所以可能有会使⽤到impala来做数据库查询的情况，我们可以通过java代码来进⾏操作impala的查询

导⼊pom文件依赖的jar包

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
<dependencies>
  <!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-common -->
  <dependency>
    <groupId>org.apache.hadoop</groupId>
    <artifactId>hadoop-common</artifactId>
    <version>2.9.2</version>
  </dependency>
  <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-common -->
  <dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-common</artifactId>
    <version>2.3.7</version>
  </dependency>
  <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-metastore -->
  <dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-metastore</artifactId>
    <version>2.3.7</version>
  </dependency>
  <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-service -->
  <dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-service</artifactId>
    <version>2.3.7</version>
  </dependency>
  <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc -->
  <dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-jdbc</artifactId>
    <version>2.3.7</version>
  </dependency>
  <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-exec -->
  <dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-exec</artifactId>
    <version>2.3.7</version>
  </dependency>
</dependencies>

java代码开发

src/main目录创建java文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
package com.lagou.impala.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class ImpalaTest {
  public static void main(String[] args) throws Exception {
    //定义连接impala的驱动和连接url
    String driver = "org.apache.hive.jdbc.HiveDriver";
    String driverUrl = "jdbc:hive2://linux122:21050/default;auth=noSasl";
    //查询的sql语句
    String querySql = "select * from t1";
    //获取连接
    Class.forName(driver);
    //通过Drivermanager获取连接
    final Connection connection = DriverManager.getConnection(driverUrl);
    final PreparedStatement ps = connection.prepareStatement(querySql);
    //执⾏查询
    final ResultSet resultSet = ps.executeQuery();
    //解析返回结果
    //获取到每条数据的列数
    final int columnCount = resultSet.getMetaData().getColumnCount();
    //遍历结果集
    while (resultSet.next()) {
      for (int i = 1; i <= columnCount; i++) {
        final String string = resultSet.getString(i);
        System.out.print(string + "\t");
      }
      System.out.println();
    }
    //关闭资源
    ps.close();
    connection.close();
  }
}