hive库操作示例

hive库操作示例

1、常规表

创建数据库

CREATE DATABASE mydatabase;

使用数据库

USE mydatabase;

创建表

CREATE TABLE mytable (
  id INT,
  name STRING,
  age INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

插入数据

INSERT INTO TABLE mytable VALUES (1, 'John', 25);
INSERT INTO TABLE mytable VALUES (2, 'Alice', 30);

插入报错

[42000][10797] COMPILE FAILED: Semantic error: [Error 10797] Only allow to single insert into Hyperbase/ES/Transaction Orc

单行插入同样报错，下述分区表、分桶表均存在相同报错；

正确插入示例

INSERT INTO TABLE mytable
SELECT * FROM(VALUES(1, 'John', 25),(2, 'Alice', 30)) AS (id, name,age)

查询数据

SELECT * FROM mytable;

更新数据

UPDATE mytable SET age = 26 WHERE id = 1;

删除数据

DELETE FROM mytable WHERE id = 2;

更新删除报错

COMPILE FAILED: Semantic error[40005]: Update/Delete/Merge operations cannot apply on views or non-transactional inceptor tables

更新/删除操作

创建临时表

创建新的空白表，用于存储更新后的数据。

CREATE TABLE newtable (
  id INT,
  name STRING,
  age INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

插入数据至临时表

使用INSERT OVERWRITE语句将更新后的数据插入到新表中。

INSERT OVERWRITE TABLE newtable
SELECT id, name, CASE WHEN id = 1 THEN 26 ELSE age END AS age
FROM mytable;

临时表重命名

如果需要，可以将新表重命名为原始表的名称。

ALTER TABLE newtable RENAME TO mytable;

删除表

DROP TABLE mytable;

2、分区表

创建分区表

首先，我们需要创建一个分区表。以下是一个创建分区表的示例：

CREATE TABLE my_table (
  col1 INT,
  col2 STRING
)
PARTITIONED BY (partition_col INT, partition_key STRING);

上述示例创建了一个名为 my_table 的表，它有两列 col1 和 col2，并且定义了两个分区列 partition_col 和 partition_key。

添加分区表数据

接下来，我们需要向分区表中添加数据。以下是向分区表添加数据的示例：

INSERT INTO TABLE my_table PARTITION (partition_col=1, partition_key='key1') VALUES (10, 'value1');
INSERT INTO TABLE my_table PARTITION (partition_col=2, partition_key='key2') VALUES (20, 'value2');

上述示例向 my_table 表的两个分区中插入了数据。

删除分区

如果你想删除分区，可以使用以下语法：

ALTER TABLE my_table DROP PARTITION (partition_col=1, partition_key='key1');

上述示例删除了 my_table 表中的一个特定分区。

查询分区表

要查询分区表，可以使用带有 WHERE 子句的 SELECT 语句来过滤特定的分区。例如：

SELECT * FROM my_table WHERE partition_col = 2;

上述示例将返回 my_table 表中 partition_col 为 2 的所有数据。

关于更新、删除

如果你使用的是Hyperbase、ES或Transaction Orc存储引擎，你将无法直接执行更新和删除操作。这些存储引擎主要用于高性能的数据写入，并且对数据的修改操作是受限的。

如果你需要更新或删除数据，你可能需要考虑使用其他类型的存储引擎，比如传统的关系型数据库（如MySQL、PostgreSQL）或类似Hive的数据仓库。

分区表数据插入

正确插入示例

INSERT INTO TABLE my_table

PARTITION (partition_col=1, partition_key=‘key1’)

SELECT * FROM (VALUES (10, ‘value1’)) AS t (col1, col2);

3、分桶表

单从概念上理解分桶表可能会比较晦涩，其实和分区一样，分桶这个概念同样不是 Hive 独有的，对于 Java 开发人员而言，这可能是一个每天都会用到的概念，因为 Hive 中的分桶概念和 Java 数据结构中的 HashMap 的分桶概念是一致的。

方案一

创建分桶表

SET transaction.type=inceptor;
CREATE TABLE student (name STRING,age INT)CLUSTERED BY (age)INTO 100 BUCKETS STORED AS ORC
TBLPROPERTIES ("transactional"="true");

数据插入

INSERT INTO student(name,age) VALUES ('xiaoming', 12);

方案二

创建分桶表

CREATE TABLE student (name STRING,age INT)CLUSTERED BY (age)INTO 100 BUCKETS;

数据插入

INSERT INTO student
SELECT * FROM (VALUES ('xiaofang', 12)) AS t (col1, col2);

多条插入

INSERT INTO student
SELECT * FROM (VALUES ('xiaofang', 12),('xiaoguang', 13)) AS t (col1, col2);

分桶表数据删除

创建一个临时表，用于存储保留的分桶数据

CREATE TABLE temp_table AS SELECT * FROM my_bucketed_table WHERE bucket_id <> 12; 
-- 此处的 bucket_id  指代 `age`

删除原始的分桶表

DROP TABLE my_bucketed_table;

将临时表重命名为原始的分桶表

ALTER TABLE temp_table RENAME TO my_bucketed_table;

4、java客户端采用jdbc操作hive库

代码示例

package com.xxx.demo;
import java.sql.*;
public class HiveExample {
    // Hive连接信息
    private static final String HIVE_DRIVER = "org.apache.hive.jdbc.HiveDriver";
    private static final String HIVE_URL = "jdbc:hive2://localhost:10000/default"; // default -> your_database_name
    private static final String HIVE_USER = "your_username";
    private static final String HIVE_PASSWORD = "your_password";
    // 查询示例
    public static void selectExample() {
        try {
            // 加载Hive驱动类
            Class.forName(HIVE_DRIVER);
            // 建立Hive连接
            Connection conn = DriverManager.getConnection(HIVE_URL, HIVE_USER, HIVE_PASSWORD);
            // 创建Statement
            Statement stmt = conn.createStatement();
            // 执行查询语句
            String sql = "SELECT * FROM your_table";
            ResultSet rs = stmt.executeQuery(sql);
            // 处理查询结果
            while (rs.next()) {
                // 根据列名获取字段值，例如：
                String column1Value = rs.getString("column1");
                int column2Value = rs.getInt("column2");
                // 其他字段依此类推
                System.out.println(column1Value + ", " + column2Value);
            }
            // 关闭连接和释放资源
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    // 插入示例
    public static void insertExample() {
        try {
            // 加载Hive驱动类
            Class.forName(HIVE_DRIVER);
            // 建立Hive连接
            Connection conn = DriverManager.getConnection(HIVE_URL, HIVE_USER, HIVE_PASSWORD);
            // 创建PreparedStatement
            String sql = "INSERT INTO your_table SELECT * FROM (VALUES" + " (?, ?))";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            // 设置参数
            pstmt.setString(1, "value1");
            pstmt.setInt(2, 123);
            // 其他参数依此类推
            // 执行插入操作
            pstmt.executeUpdate();
            // 关闭连接和释放资源
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    // 更新示例
    public static void updateExample() {
        try {
            // 加载Hive驱动类
            Class.forName(HIVE_DRIVER);
            // 建立Hive连接
            Connection conn = DriverManager.getConnection(HIVE_URL, HIVE_USER, HIVE_PASSWORD);
            // 创建Statement
            Statement stmt = conn.createStatement();
            // 执行更新语句
            String sql = "UPDATE your_table SET column1 = 'new_value' WHERE condition";
            int rowsAffected = stmt.executeUpdate(sql);
            System.out.println("Rows affected: " + rowsAffected);
            // 关闭连接和释放资源
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    // 删除示例
    public static void deleteExample() {
        try {
            // 加载Hive驱动类
            Class.forName(HIVE_DRIVER);
            // 建立Hive连接
            Connection conn = DriverManager.getConnection(HIVE_URL, HIVE_USER, HIVE_PASSWORD);
            // 创建Statement
            Statement stmt = conn.createStatement();
            // 执行删除语句
            String sql = "DELETE FROM your_table WHERE condition";
            int rowsAffected = stmt.executeUpdate(sql);
            System.out.println("Rows affected: " + rowsAffected);
            // 关闭连接和释放资源
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static void main(String[] args) {
        selectExample();
        insertExample();
        updateExample();
        deleteExample();
    }
}