大数据技术Hive进阶(2)----分区表&开窗函数&自定义函数

除了第一章基本的数据表操作和数据处理操作，Hive还提供了一些进阶操作，可以帮助用户更高效地管理和分析数据。

分区表

分区表可以根据指定的分区字段将数据拆分成不同的分区，从而提高查询效率。例如，我们可以将数据按照时间、地区等字段进行分区。

创建分区表的语法与创建普通表类似，只需要在定义表结构时添加PARTITIONED BY语句来指定分区字段。加载数据时，需要指定每条数据所属的分区。

CREATE TABLE mytable (
  id INT,
  name STRING,
  age INT
) PARTITIONED BY (year INT, month INT);

LOAD DATA INPATH '/path/to/data' INTO TABLE mytable PARTITION (year=2023, month=4);

查询数据时，可以根据指定的分区字段来过滤数据，从而提高查询效率。

SELECT * FROM mytable WHERE year=2023 AND month=4;

动态分区

动态分区是一种特殊的分区方式，它允许在加载数据时自动创建新的分区。这样，我们就不需要预先定义分区，可以自动根据数据内容创建分区，方便快捷。

SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;

INSERT INTO TABLE mytable PARTITION (year, month)
SELECT id, name, age, year, month FROM mytable_raw;

外部表

外部表与普通表不同的是，它们不存储真实数据，而只是对已有数据进行定义和描述。这样，我们就可以在Hive中直接使用外部数据源，而无需将数据复制到Hive中。

创建外部表时，需要指定数据源的位置和格式。

CREATE EXTERNAL TABLE mytable (
  id INT,
  name STRING,
  age INT
) LOCATION '/path/to/data';

查询数据时，可以像普通表一样进行查询操作。

SELECT * FROM mytable WHERE age > 20;

窗口函数

窗口函数可以在SQL查询中提供一些高级计算功能，比如计算排名、累积和等。

SELECT id, name, age, ROW_NUMBER() OVER (ORDER BY age DESC) AS rank
FROM mytable;

上面的语句会计算出每个雇员的年龄排名，在输出结果中添加一列rank表示排名。

自定义函数

Hive允许用户编写自定义函数（UDF），以实现一些特定的功能。用户可以使用Java或Python等语言开发UDF，并通过Hive的扩展机制进行注册和使用。

package com.example.udf;

import org.apache.hadoop.hive.ql.exec.UDF;

public class MyUDF extends UDF {
  public int evaluate(int a, int b) {
    return a + b;
  }
}

注册UDF后，即可以在SQL查询中使用。

ADD JAR /path/to/myudf.jar;
CREATE TEMPORARY FUNCTION myudf AS 'com.example.udf.MyUDF';
SELECT myudf(age, 10) FROM mytable;

以上是Hive的一些进阶操作，它们可以帮助用户更高效地管理和分析大数据，下面看一下这些操作的实例

分区表

创建分区表并插入数据：

CREATE TABLE sales (
  id INT,
  amount DECIMAL
) PARTITIONED BY (year INT, month INT);

INSERT INTO TABLE sales PARTITION (year=2023, month=4) VALUES (1, 100.0), (2, 200.0), (3, 300.0);

查询特定分区的数据：

SELECT * FROM sales WHERE year=2023 AND month=4;

动态分区

创建原始表并插入数据：

CREATE TABLE sales_raw (
  id INT,
  amount DECIMAL,
  date STRING
);

INSERT INTO TABLE sales_raw VALUES (1, 100.0, '2023-04-01'), (2, 200.0, '2023-04-02'), (3, 300.0, '2023-04-03');

使用动态分区将数据加载到分区表中：

SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;

INSERT INTO TABLE sales PARTITION (year, month)
SELECT id, amount, YEAR(date), MONTH(date) FROM sales_raw;

查询特定分区的数据：

SELECT * FROM sales WHERE year=2023 AND month=4;

外部表

创建外部表并查看数据：

CREATE EXTERNAL TABLE ad_clicks (
  id INT,
  ad_id INT,
  ad_name STRING
) LOCATION '/path/to/data/ad_clicks';

SELECT * FROM ad_clicks LIMIT 10;

查询外部表中特定广告的点击情况：

SELECT ad_id, COUNT(*) FROM ad_clicks WHERE ad_name = 'banner' GROUP BY ad_id;

窗口函数

为销售额添加排名并查询前三名：

SELECT id, amount, DENSE_RANK() OVER (ORDER BY amount DESC) AS rank
FROM sales ORDER BY rank LIMIT 3;

自定义函数

编写UDF计算商品折扣后的价格：

package com.example.udf;

import org.apache.hadoop.hive.ql.exec.UDF;

public class DiscountUDF extends UDF {
  public double evaluate(double price, double discount) {
    return price * (1 - discount);
  }
}

注册UDF并使用：

ADD JAR /path/to/udf.jar;
CREATE TEMPORARY FUNCTION discount AS 'com.example.udf.DiscountUDF';

SELECT discount(price, 0.1) FROM sales;