hive基本操作

发表于 |

hive基本操作

Hive简介

hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。 其优点是学习成本低，可以通过类SQL语句快速实现简单的MapReduce统计，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。
Hive是建立在 Hadoop 上的数据仓库基础构架。它提供了一系列的工具，可以用来进行数据提取转化加载（ETL），这是一种可以存储、查询和分析存储在 Hadoop 中的大规模数据的机制。Hive 定义了简单的类 SQL 查询语言，称为 HQL，它允许熟悉 SQL 的用户查询数据。同时，这个语言也允许熟悉 MapReduce 开发者的开发自定义的 mapper 和 reducer 来处理内建的 mapper 和 reducer 无法完成的复杂的分析工作。
安装见详情

Hive基本操作

  1. Create Table
  • CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常。
  • EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
  • LIKE 允许用户复制现有的表结构，但是不复制数据。
  • 用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。
  • 如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCE 。
  • 有分区的表可以在创建的时候使用 PARTITIONED BY 语句。一个表可以拥有一个或者多个分区，每一个分区单独存在一个目录下。而且，表和分区都可以对某个列进行 CLUSTERED BY 操作，将若干个列放入一个桶（bucket）中。也可以利用SORT BY 对数据进行排序。这样可以为特定应用提高性能。
  • 表名和列名不区分大小写，SerDe 和属性名区分大小写。表和列的注释是字符串
    　　注：

SerDe是Serialize/Deserilize的简称，目的是用于序列化和反序列化
STORED AS TEXTFILE：默认格式，数据不做压缩，磁盘开销大，数据解析开销大。 可结合Gzip、Bzip2使用(系统自动检查，执行查询时自动解压)，但使用这种方式，hive不会对数据进行切分， 从而无法对数据进行并行操作
STORED AS SEQUENCE：Hadoop API提供的一种二进制文件支持，其具有使用方便、可分割、可压缩的特点。SequenceFile支持三种压缩选择：NONE，RECORD，BLOCK。Record压缩率低，一般建议使用BLOCK压缩。

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
39
40
41
42
43
44
45
46
47
48
49
50
语法
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
  [(col_name data_type [COMMENT col_comment], ...)]
  [COMMENT table_comment]
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [
   [ROW FORMAT row_format] [STORED AS file_format]
   | STORED BY 'storage.handler.class.name' [ WITH SERDEPROPERTIES (...) ]  
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]  [AS select_statement]  CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
  LIKE existing_table_name
  [LOCATION hdfs_path]

data_type
  : primitive_type
  | array_type
  | map_type
  | struct_type

primitive_type
  : TINYINT
  | SMALLINT
  | INT
  | BIGINT
  | BOOLEAN
  | FLOAT
  | DOUBLE
  | STRING

array_type
  : ARRAY < data_type >

map_type
  : MAP < primitive_type, data_type >

struct_type
  : STRUCT < col_name : data_type [COMMENT col_comment], ...>

row_format
  : DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

file_format:
  : SEQUENCEFILE
  | TEXTFILE
  | RCFILE     (Note:  only available starting with 0.6.0)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

基本示例：
1、如果一个表已经存在，可以使用if not exists

2、create table user(id int,cont string) row format delimited fields terminated by ‘\005’ stored as textfile; terminated by：关于来源的文本数据的字段间隔符

3、如果要将自定义间隔符的文件读入一个表，需要通过创建表的语句来指明输入文件间隔符，然后load data到这个表。

4、Shops数据库常用间隔符的读取 我们的常用间隔符一般是Ascii码5，Ascii码7等。在hive中Ascii码5用’\005’表示， Ascii码7用’\007’表示，依此类推。

5、装载数据 查看一下：Hadoop fs -ls LOAD DATA INPATH ‘/user/admin/user/a.txt’ OVERWRITE INTO TABLE user;

6、如果使用external建表和普通建表区别：前者存放元数据，删除后文件系统中的数据不会删除，后者会直接删除文件系统中的数据
创建多层目录

HIVE的分区通过在创建表时启用partition by实现，用来partition的维度并不是实际数据的某一列，具体分区的标志是由插入内容时给定的。当要查询某一分区的内容时可以采用where语句，形似where tablename.partition_key > a来实现。 创建含分区的表。 命令原型：

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE c02_clickstat_fatdt1
(yyyymmdd  string,
 id              INT,
 ip               string,
 country          string,
 cookie_id        string,
 page_id          string  ,  
 clickstat_url_id int,
 query_string     string,
 refer            string
)PARTITIONED BY(dt STRING)
row format delimited fields terminated by '\005' stored as textfile;

装载数据

1
2
LOAD DATA INPATH '/user/admin/SqlldrDat/CnClickstat/20131101/19/clickstat_gp_fatdt0/0' OVERWRITE INTO TABLE c02_clickstat_fatdt1
 PARTITION(dt='20131101');

访问某个分区

1
2
3
SELECT count(*)
    FROM c02_clickstat_fatdt1 a
    WHERE a.dt >= '20131101' AND a.dt < '20131102';

指定LOCATION位置

1
2
3
4
5
6
7
8
CREATE EXTERNAL TABLE page_view(viewTime INT, userid BIGINT,
     page_url STRING, referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User',
     country STRING COMMENT 'country of origination')
 COMMENT 'This is the staging page view table'
 ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'
 STORED AS TEXTFILE
 LOCATION '<hdfs_location>';

  1. Alter Table

添加分区

1
2
3
ALTER TABLE c02_clickstat_fatdt1 ADD 
PARTITION (dt='20131202') location '/user/hive/warehouse/c02_clickstat_fatdt1/part20131202' 
PARTITION (dt='20131203') location '/user/hive/warehouse/c02_clickstat_fatdt1/part20131203';

删除分区

1
ALTER TABLE c02_clickstat_fatdt1 DROP PARTITION (dt='20101202');

重命名表

1
ALTER TABLE table_name RENAME TO new_table_name

修改列/属性

1
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

添加/替换列

1
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

  1. 显示表

查看表名：

1
SHOW TABLES;

　　查看表名，部分匹配：

1
2
SHOW TABLES 'page.*';
SHOW TABLES '.*view';

　　查看某表的所有Partition，如果没有就报错：

1
SHOW PARTITIONS page_view;

　　查看某表结构：

1
DESCRIBE invites;

　　查看分区内容：

1
SELECT a.foo FROM invites a WHERE a.ds='2012-08-15';

　　查看有限行内容，同Greenplum，用limit关键词：

1
SELECT a.foo FROM invites a limit 3;

　　查看表分区定义：

1
DESCRIBE EXTENDED page_view PARTITION (ds='2013-08-08');

  1. 加载
    HIVE装载数据没有做任何转换加载到表中的数据只是进入相应的配置单元表的位置移动数据文件。纯加载操作复制/移动操作。
    Load 操作只是单纯的复制/移动操作，将数据文件移动到 Hive 表对应的位置。
    如：从本地导入数据到表格并追加原表
    1
    LOAD DATA LOCAL INPATH `/tmp/pv_2013-06-08_us.txt` INTO TABLE c02 PARTITION(date='2013-06-08', country='US')

　　从本地导入数据到表格并追加记录：

1
LOAD DATA LOCAL INPATH './examples/files/kv1.txt' INTO TABLE pokes;

　　从hdfs导入数据到表格并覆盖原表：

1
LOAD DATA INPATH '/user/admin/SqlldrDat/CnClickstat/20131101/18/clickstat_gp_fatdt0/0' INTO table c02_clickstat_fatdt1 OVERWRITE PARTITION (dt='20131201');

　　关于来源的文本数据的字段间隔符 如果要将自定义间隔符的文件读入一个表，需要通过创建表的语句来指明输入文件间隔符，然后load data到这个表就ok了。

  1. 插入
    在hive中一般用load加载数据，因为每次你写INSERT语句的时候都是要将整个表的值OVERWRITE。我想这个应该是与Hive的storage layer是有关系的，因为它的存储层是HDFS，插入一个数据要全表扫描，还不如用整个表的替换来的快些。
    1
    2
    hive> FROM invites a INSERT OVERWRITE TABLE events SELECT a.bar, count(*) WHERE a.foo > 0 GROUP BY a.bar;
    hive> INSERT OVERWRITE TABLE events SELECT a.bar, count(*) FROM invites a WHERE a.foo > 0 GROUP BY a.bar;

其他语法操作