Hive应用(2.2)DML

加载、插入数据

  1. 加载数据

    1
    2
    LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
    --不使用[LOCAL]则使用HDFS上的文件,[OVERWRITE]将覆盖表(分区)中原有数据,[PARTITION]如果表为分区表则必须指定分区的属性和值

    将数据文件users.data加载到test.users表中

    1
    LOAD DATA INPATH '/user/aviraer/users.data' OVERWRITE INTO TABLE test.users

    使用LOAD加载数据,必须保证加载的数据文件格式和建表时指定的格式相同。

  2. 插入查询结果

    使用LOAD加载数据灵活性较差,无法使用动态分区(必须手动指定分区和值),通过INSERT和SELECT来插入数据更为灵活

    将表users中的数据插入到test.users_partition_by_country表中,该表使用country分区

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    CREATE TABLE users_partition_by_country(
    id BIGINT,
    name STRING,
    gender STRING,
    age SMALLINT
    )
    PARTITIONED BY(country STRING);

    set hive.exec.dynamic.partition.mode=nonstrict --动态分区为非严格模式,否则需要指定至少一个非动态分区
    INSERT OVERWRITE TABLE test.users_partition_by_country PARTITION(country) SELECT * FROM test.users;
    1. 直接插入数据

      hive0.14版本后支持直接使用insert…values语句插入数据,但是这种方式不支持集合数据类型

      1
      INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]

      向users_partition_by_country中插入一条数据

      1
      INSERT INTO test.users_partition_by_country PARTITION(country) VALUES (100, 'Jimmy', 'male', '22', 'CN');
    2. 通过查询语句建表并加载数据

      1
      CREATE TABLE users2 AS SELECT * FROM users;

      此方法不能用于创建外部表

导出数据

  1. 如果格式满足要求,直接导出数据文件

  2. 格式不满足要求,使用INSERT DIRECTORY

    1
    INSERT OVERWRITE DIRECTORY '/user/aviraer/export_file' select name from users;

查询

  1. CASE…WHEN…THEN句式

    1
    2
    3
    4
    5
    6
    select name, 
    CASE
    WHEN gender = 'female' THEN '女'
    WHEN gender = 'male' THEN '男'
    ELSE '未知'
    END as gender From test.users;
  2. 避免触发MR

    使用本地模式可以避免触发MR,提高查询速度。

    1
    set hive.exec.mode.local.auto=true;
  3. LIKE和RLIKE

    使用LIKE在查询中进行模糊匹配,使用RLIKE可以支持正则表达式匹配

  4. ORDER BY和SORT BY

    ORDER BY会由一个reducer对全局数据进行排序,大数据集情况下可能会非常慢。SORT BY进行局部排序,只能保证每个reducer任务的结果都是有序的

  5. DISTRIBUTE BY和SORT BY

    DISTRIBUTE BY决定了数据如何分配到reducer中,它可以保证指定列相同key值的行进入同一个reducer,默认情况下是对key进行hash后进行均匀分配。

    对订单表中属于同一用户的数据进行排序

    这个问题正好可以利用SORT BY的局部排序和DISTRIBUTE BY来解决:

    1
    SELECT * FROM orders DISTRIBUTE BY user_id SORT BY creation_date DESC;
  6. CLUSTER BY

    ClUSTER BY等同于,DISTRIBUTE BY和SORT BY的特定组合,即:

    1
    2
    3
    SELECT * FROM orders DISTRIBUTE BY user_id SORT BY user_id;
    =
    SELECT * FROM orders CLUSTER BY user_id;

    但是只能使用ASC,且DISTRIBUTE BY 和 SORT BY的字段必须完全相同

  7. 抽样查询

    1
    SELECT * FROM users TABLESAMPLE(BUCKET 1 OUT OF 2 ON rand()) s;

    如果把rand()替换成某一列,则该语句每次查询的结果都是一样的

视图

Hive目前不支持物理视图,可以通过视图来降低查询语句的复杂程度

创建一个视图,统计users表中的用户性别情况

1
CREATE VIEW gender_statistic AS SELECT gender, count(gender) FROM users GROUP BY gender;

索引

如果分区数量太多而无法发挥作用时,Hive索引可以裁剪一张表的数据块,从而减少MR的输入数据量提升查询速度。另外索引会占用额外的空间。

  1. 创建索引

    创建索引的方式:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    CREATE INDEX index_name
    ON TABLE base_table_name (col_name, ...)
    AS 'index.handler.class.name' --指定索引处理器,可以通过第三方处理器进行扩展
    [WITH DEFERRED REBUILD]
    [IDXPROPERTIES (property_name=property_value, ...)]
    [IN TABLE index_table_name]
    [PARTITIONED BY (col_name, ...)]
    [
    [ ROW FORMAT ...] STORED AS ...
    | STORED BY ...
    ]
    [LOCATION hdfs_path]
    [TBLPROPERTIES (...)]
    [COMMENT "index comment"]

为users表的name属性创建索引

1
2
3
4
5
6
7
CREATE INDEX 
name_index
ON TABLE
users(name)
AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
WITH DEFERRED REBUILD
IN TABLE users_index_table;
  1. Bitmap索引

    用于排重后值较少的列

    1
    2
    3
    4
    5
    6
    7
    CREATE INDEX 
    name_index
    ON TABLE
    users(name)
    AS 'BITMAP'
    WITH DEFERRED REBUILD
    IN TABLE users_index_table;
  1. 重建索引

    使用 WITH DEFERRED REBUILD 选项创建索引时,新建的索引是空白的,可以使用ALTER INDEX进行重建

    1
    ALTER INDEX index_name ON table_name [PARTITION partition_spec] REBUILD;

    重建name_index:

    1
    ALTER INDEX name_index ON users REBUILD;
  1. 显示索引

    1
    SHOW FORMATTED INDEX ON users;
  1. 删除索引

    1
    DROP INDEX name_index ON users;