写一些样版代码,便于查询使用
一.创建表
1.建表语法
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]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]
2.字段解释说明
(1)CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXISTS 选项来忽略这个异常。
(2)EXTERNAL关键字可以让用户创建一个外部表,在建表的同时可以指定一个指向实际数据的路径(LOCATION),在删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据。
(3)COMMENT:为表和列添加注释。
(4)PARTITIONED BY创建分区表
(5)CLUSTERED BY创建分桶表
(6)SORTED BY不常用,对桶中的一个或多个列另外排序
(7)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, ...)]
用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED,将会使用自带的SerDe。在建表的时候,用户还需要为表指定列,用户在指定表的列的同时也会指定自定义的SerDe,Hive通过SerDe确定表的具体的列的数据。
SerDe是Serialize/Deserilize的简称, hive使用Serde进行行对象的序列与反序列化。
(8)STORED AS指定存储文件类型
常用的存储文件类型:SEQUENCEFILE(二进制序列文件)、TEXTFILE(文本)、RCFILE(列式存储格式文件)
如果文件数据是纯文本,可以使用STORED AS TEXTFILE。如果数据需要压缩,使用 STORED AS SEQUENCEFILE。
(9)LOCATION :指定表在HDFS上的存储位置。
(10)AS:后跟查询语句,根据查询结果创建表。
(11)LIKE允许用户复制现有的表结构,但是不复制数据。
举个栗子:
create table if not exists sopdm.test1(
name string comment ‘姓名’,
salary float comment ‘薪水’)
comment ‘这是一个测试的表’
partitioned by(country string,state string)
STORED AS rcfile
tblproperties(‘creator’=’me’,’created_at’=’2014-11-13 09:50:33’)
location ‘/user/hive/warehouse/sopdm.db/test1’
外部表举例
hive (default)> create external table stu_external(
id int,
name string)
row format delimited fields terminated by '\t'
location '/student';
二、管理表与外部表的互相转换
(1)查询表的类型
hive (default)> desc formatted student2;
Table Type: MANAGED_TABLE
(2)修改内部表student2为外部表
alter table student2 set tblproperties('EXTERNAL'='TRUE');
(3)查询表的类型
hive (default)> desc formatted student2;
Table Type: EXTERNAL_TABLE
(4)修改外部表student2为内部表
alter table student2 set tblproperties('EXTERNAL'='FALSE');
(5)查询表的类型
hive (default)> desc formatted student2;
Table Type: MANAGED_TABLE
注意:('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法,区分大小写!
三、创建索引
CREATE INDEX employees_index
ON TABLE employees(country)
AS ‘org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler’
WHITH DEFERRED REBUILD
IDXPROPERTIES(‘creator=’me’,’created_at’=’some_time’)
IN TABLE employees_index_table
PARTITIONED BY (country,name)
COMMENT ‘Employees indexed by country and name’;
3.1 BitMap索引
Hive v0.8.0 增加了 bitmap索引处理器,应用于排重后值较少的列
CREATE INDEX employees_index
ON TABLE employees(country)
AS ‘BITMAP’
WHITH DEFERRED REBUILD
IDXPROPERTIES(‘creator=’me’,’created_at’=’some_time’)
IN TABLE employees_index_table
PARTITIONED BY (country,name)
COMMENT ‘Employees indexed by country and name’;
3.2 重建索引
ALTER INDEX employees_index
ON TABLE employees
Partition(country=’US’)
REBUILD;
如果用户指定了 DEFERRED REBUILD,新索引将是空白状态。如果省掉Partition将对所有分区重建索引
3.3 显示索引
Show FORMATTED INDEX ON employees;
FORMATTED 可选(可以输出列名),INDEX也可以换成INDEXES输出所有索引