Hive学习笔记（2）——基本语法

大数据
, ,

基本语法

本文只介绍与传统SQL语法有所区别的部分。

  • 建表：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name (
        col_name col_type [COMMENT col_comment], 
        ...) [COMMENT table_comment]
        PARTITIONED BY (month, string) 
        CLUSTERD BY (id) SORTED BY (id) [ASC|DESC] INTO n BUCKETS 
        ROW FORMAT DELIMITED 
        FIELDS TERMINATED BY '\t' 
        LINES TERMINATED BY '\n'
        STORED AS ORC
        TBLPROPERTIES('orc.compress'='SNAPPY')
        LOCATION '/user/hive/warehouse/table';

  • 通过CREATE TABLE AS SELECT的方式创建表：

    1
    CREATE TABLE table_name AS SELECT ...;

  • 复制表结构，但不复制数据：

    1
    CREATE TABLE table_name_1 LIKE table_name_2;

  • 指定分隔符，如不指定，Hive默认使用SOH字符('\001')为分隔符：

    1
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

  • 从本地加载数据：

    1
    LOAD DATA LOCAL INPATH '/path/file.csv' INTO TABLE table_name;

  • 向指定分区插入数据：

    1
    INSERT OVERWRITE INTO table_name_1 [PARTITION (month=xxx)] SELECT ...;

  • 修改表名：

    1
    ALTER TABLE old_table_name RENAME TO new_table_name;

  • 修改列名：

    1
    2
    ALTER TABLE table_name 
        CHANGE [COLUMN] old_column_name new_column_name column_type；

  • 删除表的数据和元数据（对于外部表，只删除Metastore中的数据，外部数据保持不变）：

    1
    DROP TABLE [IF EXISTS] table_name ;

  • 删除表中的数据，但保留表结构：

    1
    TRUNCATE TABLE table_name;

  • 查询语句：

    1
    2
    3
    4
    5
    6
    7
    SELECT [ALL|DISTINCT] col_name, ...
        FROM table_name
        WHERE where_condition
        GROUP BY col_name HAVING have_condition
        CLUSTER BY col_name
        [DISTRIBUTE BY col_name SORT BY| ORDER BY col_name]
        LIMIT limit_number;

  • 当DISTRIBUTE BY和SORT BY指定为同一个字段时，可以用CLUSTER BY代替之。

  • SORT BY会在每一个Reducer中排序，不保证全局有序；ORDER BY会对所给数据全局排序。

外部表

Hive不会独占外部表的数据，当删除外部表时，数据仍然存放在HDFS中。而删除内部表时，表数据和HDFS中的文件也会被删除。如果数据不只被Hive使用，应当建立外部表。

创建外部表命令：CREATE EXTERNAL TABLE table_name(...);

分区表

Hive把整个表按照某个或某些字段划分成数个小文件，并放到不同的文件夹里。

当查询时通过WHERE指定分区，就只到该分区的文件夹下扫描数据，从而提升查询速度。

如果hive.mapred.mode设置为严格模式，读取分区表需要WHERE语句指定分区。

在物理结构上，分区就是把数据存放在按分区字段命名的目录中，形如分区key=value。

如果设置多个分区字段，反映在文件结构中就是目录的嵌套。

  • 分区建表，指定分区字段为month：

    1
    CREATE TABLE table_name(id int, name string) PARTITIONED BY(month string);

  • 分区表加载本地数据到指定分区：

    1
    LOAD DATA LOCAL INPATH '/path/file.csv' INTO TABLE table_name PARTITION(month='202001');

  • 查询指定分区：

    1
    SELECT * FROM table_name WHERE month='202001';

  • 建立分区表，并将数据文件复制到路径后，可以使用如下命令修复分区表：

    1
    MSCK REPAIR TABLE table_name;

分桶表

分桶是把数据按照指定字段划分到多个文档中，就是MapReduce中的分区。

Hive常见的分桶方式是对某一列数据哈希取模、均匀地分发到不同的桶文件中。

  • 设置强制分桶：

    1
    set hive.enforce.bucketing = true;

  • 设置Reduce个数：

    1
    set mapreduce.job.reduces = 4;

  • 分桶建表：

1
2
3
4
5
6
CREATE TABLE table_name (
    id int, 
    name string) 
    CLUSTERD BY (id) INTO 4 BUCKETS 
    ROW FORMAT DELIMITED 
    FIELDS TERMINATED BY '\t';
  • 分桶表加载数据：先创建普通表作为中建表，再通过把数据加载到分桶表中。
    1
    INSERT OVERWRITE TABLE clustered_table SELECT * FROM helper_table CLUSTER BY(id);

HiveQL注意事项

  • JOIN只支持等值连接，且不支持在子句间使用OR串联。
  • 默认JOIN最后一张表为主表，因此需要把大表放在JOIN语句的最后。也可用/*+STREAMTABLE(xxx)*/来告知优化器哪张表是大表。
  • ORDER BY和MySQL的ORDER BY一致，对输入全局排序，需要较长的时间；而SORT BY只在每一个Reducer内进行排序，不保证全局有序，速度较快。
  • DISTRIBUTE BY根据指定字段把数据分发到不同的Reducer。