注：hive其他语法在hive官网有说明，建议初学者，去官网学习一手的资料，
官网
默认情况下，Hive会创建内部表，其中文件，元数据和统计信息由内部Hive进程管理。
说到建表首先我们需要了解内部表和外部表，这个点比较重要。
hive内部表和外部表
1、语法
从语法上看，内部表和外部表的区别就是建表时外部表使用
EXTERNAL
修饰，而内部表没有。
2、区别：
我们下面从数据管理、数据存储、表删除、元数据更新这四个方面阐述两种模式建表的区别：
1、
数据管理
：内部表数据由
Hive
自身管理，外部表数据由
HDFS
管理；
2、
存储位置
：内部表数据存储的位置是
hive.metastore.warehouse.dir（默认情况下位于/user/hive/warehouse/databasename.db/tablename/ 文件夹路径中）
，外部表数据的存储位置由自己制定（如果没有
LOCATION
，Hive将在HDFS上的
/user/hive/warehouse
文件夹下以外部表的表名创建一个文件夹，并将属于这个表的数据存放在这里）；
3、
表删除
：
EXTERNAL
关键字允许您创建表并提供
LOCATION
，以便Hive不使用此表的默认位置。如果您已经生成了数据，这会派上用场。如果删除内部表或分区，则删除与该表或分区关联的存储数据和元数据。如果未指定
PURGE
选项，则数据将移动到回收站文件夹中一段时间；删除外部表仅仅会删除元数据，HDFS上的文件也就是实际数据并不会被删除，在下次创建表时，可以直接创建表
refresh table(sparkSql)全量表
或者
msck repair table xxx
刷新下元数据就可以正常使用。内部表，一旦被某个访问者删除，所有人都无法在使用；而外部表如果一个访问者不小心删除表，其他用户仍然可以继续正常使用。
4、
元数据更新
对内部表的修改会将修改直接同步给元数据，而对外部表的表结构和分区进行修改，则需要修复（MSCK REPAIR
TABLE table_name;）。
例：
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>';
您可以使用上述语句创建一个
page_view
表，
该表指向其存储的任何HDFS位置
。但是您仍然必须确保数据按照上面CREATE语句中的指定分隔。
hive官网建表说明及注释
记住官网的才是框架最为准确的使用教程，我们首先看官网介绍
注意：
’[]’ 表示可选，’|’ 表示二选一
**1、建表方式一：**
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name -- (Hive 0.14.0及以后版本中提供的`TEMPORARY`关键字可以用于创建临时表，`EXTERNAL`外部表修饰词，在创建外部表时使用，创建内部须省略该关键字,IF NOT EXISTS用于判断数据库中表是否存在，如果不存在在创建表)
[(col_name data_type [COMMENT col_comment], ... --表字段说明，col_name 字段也就是列名；data_type 字段类型也就是数据类型，COMMENT类似MYSQL后面跟对字段说明的字符串，如：`user_name` string COMMENT '用户姓名' 。
[constraint_specification] -- 表约束规范，具体使用参见官网，笔者未曾使用过
)]
[COMMENT table_comment] --表说明，不论是什么表跟在字段说明之后，可选。
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] --在创建分区表时使用，我可以看到分区主键可以为多个，对应hdfs也就是父子目录。
[CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] --分桶建表，我们可以看到分桶主键和排序主键均可以指定多个且都是可选参数，但是在指定SORTED BY在指定排序方式时必须为ASC或者DESC。
[SKEWED BY (col_name, col_name, ...) -- (注意:在Hive 0.10.0及以后版本中可用)
] ON ((col_value, col_value, ...), (col_value, col_value, ...), ...) [STORED AS DIRECTORIES] --在创建的目标的数据源确定会发生数据倾斜时，我们可以在设计表时使用这个方式创建HIVE Skewed Table，Skewed Table可以提高有一个或多个列有倾斜值的表的性能。
[
[ROW FORMAT row_format] -- 设置表数据间分隔符和行分隔，row_format可以是delimited fields terminated by 'xxx' lines terminated by 'xxx'
[STORED AS file_format] -- 表数据文件存储格式,file_format可以是orc、textfile、parquet等
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] -- （注意:在Hive 0.6.0和更高版本可用)
]
[LOCATION hdfs_path] -- 自定义表数据存储路径
[TBLPROPERTIES (property_name=property_value, ...)] -- (注意:在Hive 0.6.0和更高版本可用)
**2、建表方式二：**
[AS select_statement]; -- (注意:在Hive 0.5.0和更高版本可用;不支持外部表)
**3、建表方式三：**
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
LIKE existing_table_or_view_name
[LOCATION hdfs_path];
data_type
: primitive_type
| array_type
| map_type
| struct_type
| union_type -- (注意:在Hive 0.7.0和更高版本可用)
primitive_type
: TINYINT
| SMALLINT
| INT
| BIGINT
| BOOLEAN
| FLOAT
| DOUBLE
| DOUBLE PRECISION -- (注意:在Hive 2.2.0和更高版本可用)
| STRING
| BINARY -- (注意:在Hive 0.8.0和更高版本可用)
| TIMESTAMP -- (注意:在Hive 0.8.0和更高版本可用)
| DECIMAL -- (注意:在Hive 0.11.0和更高版本可用)
| DECIMAL(precision, scale) -- (注意:在Hive 0.13.0和更高版本可用)
| DATE -- (注意:在Hive 0.12.0和更高版本可用)
| VARCHAR -- (注意:在Hive 0.12.0和更高版本可用)
| CHAR -- (注意:在Hive 0.13.0和更高版本可用)
array_type
: ARRAY < data_type >
map_type
: MAP < primitive_type, data_type >
struct_type
: STRUCT < col_name : data_type [COMMENT col_comment], ...>
union_type
: UNIONTYPE < data_type, data_type, ... > -- (注意:在Hive 0.7.0和更高版本可用)
row_format
: DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] --设置数据分隔符
[COLLECTION ITEMS TERMINATED BY char] --设置集合类型数据分隔符
[MAP KEYS TERMINATED BY char] --设置Map类型数据分隔符
[LINES TERMINATED BY char] --设置数据行分隔符
[NULL DEFINED AS char] -- (No注意:在Hive 0.13.0和更高版本可用)
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
file_format:
: SEQUENCEFILE
| TEXTFILE -- (默认值，取决于hive.default.fileformat配置)
| RCFILE -- (注意:在Hive 0.6.0和更高版本可用)
| ORC -- (注意:在Hive 0.11.0和更高版本可用)
| PARQUET -- (注意:在Hive 0.13.0和更高版本可用)
| AVRO -- (注意:在Hive 0.14.0和更高版本可用)
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
constraint_specification:
: [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE ]
[, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE
说明：
1、
CREATE TABLE
创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；
用户可以用
IF NOT EXISTS
选项来忽略这个异常。
2、
EXTERNAL
可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（
LOCATION
）.Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据.
3、
LIKE
允许用户复制现有的表结构，但是不复制数据。
4、
ROW FORMAT DELIMITED
是用来设置创建的表在加载数据的时候，支持的列分隔符。
Hive默认的分隔符是
\001
，属于不可见字符，这个字符在
vi
里是
^A
5、
STORED AS
SEQUENCEFILE|TEXTFILE|RCFILE
如果文件数据是纯文本，可以使用
STORED AS TEXTFILE
。
如果数据需要压缩，使用
STORED AS SEQUENCEFILE
。
6、
CLUSTERED BY
对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
7、
LOCATION
指定加载数据路径(指定在hdfs上的位置).针对的extetnal外部表，要指定存储路径，不指定也没事，默认路径。内部表不用指定，默认路径
/user/hive/warehouse
。
CREATE TABLE
创建一个具有给定名称的表。如果已存在具有相同名称的表或视图，则会引发错误。您可以使用
IF NOT EXISTS
跳过错误。
注意:
(1)、表名和列名不区分大小写，但
SerDe
和属性名称区分大小写。在
Hive 0.12及更早版本
中，
表名和列名中只允许使用字母数字和下划线字符
。在
Hive0.13
后来，列名可以包含
任何的Unicode字符
。在重音符飘号（`）中指定的任何列名都按字面处理。在反引号字符串中，使用双反引号（“）来表示反引号字符。反引号引用还允许对表和列标识符使用保留关键字。
(2)、表和列注释是字符串文字（单引号）。
(3)、在没有
EXTERNAL
子句的情况下创建的表称为内部表，由Hive管理其数据。要查明表是内部还是外部表，请在
DESCRIBE EXTENDED table_name
的输出中查找
tableType
。
(4)、
TBLPROPERTIES
子句允许您使用自己的元数据键/值对标记表定义。还存在一些预定义的表属性，例如由Hive自动添加和管理的
last_modified_user
和
last_modified_time
。其他预定义表属性包括：
(5).要为表指定数据库，请在
CREATE TABLE
语句之前（在
Hive 0.6及更高版本
中）发出
USE database_name
语句，或者使用数据库名称（在
Hive 0.7及更高版本中使用“ ”
）限定表名。关键字
“ ”
可用于默认数据库。
database_name.table.name
。
hive创建表的三种方法
Hive创建表的方式（默认路径
/user/hive/warehouse
，也可以
location
指定，主要针对
external
表）
1、使用create命令直接创建一个新表
CREATE TABLE `test.dept`(
`dept_no` int,
`addr` string,
`tel` string)
partitioned by(date_key string )
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;
可以使用
desc formatted test.dept
查看建表相关的各种配置属性以及默认属性。从下面可以看出，在创建表时有很多属性，比如存储地址，存储格式等属性我们都没有直接配置，而是选择了系统默认的。
hive> desc formatted test.dept;
OK
# col_name data_type comment
dept_no int
addr string
tel string
# Partition Information
# col_name data_type comment
date_key string
# Detailed Table Information
Database: test
Owner: bd
CreateTime: Wed May 12 11:18:51 CST 2021
LastAccessTime: UNKNOWN
Retention: 0
Location: hdfs://bd-hadoop/apps/hive/warehouse/test.db/dept
Table Type: MANAGED_TABLE
Table Parameters:
COLUMN_STATS_ACCURATE {\"BASIC_STATS\":\"true\"}
numFiles 0
numPartitions 0
numRows 0
rawDataSize 0
totalSize 0
transient_lastDdlTime 1620789531
# Storage Information
SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
field.delim ,
serialization.format ,
注意：
这里小小说明下
desc
后边加一个
formatted
可以将数据存储、压缩、分隔等信息一起输出,此参数在spark-sql中同样适用，可以解决spark-sql在
show create table xxx
时展示不出复杂类型如
map
、
struct
类型分隔符的问题。
2、 create table
xxx
AS select * from [database.table]建表
xxx
与mysql等关系型数据库类似，在hive中可以把一张表的某些字段抽取出来，创建成一张新表，使用
as
。
create table mytest_tmp1
AS
select *
from test.dept
where date_key ='20180229';
CREATE TABLE user_statis
STORED AS parquet
LOCATION 'big-data/test/user_statis'
AS
SELECT concat(date_key,'_',app_no) AS unique_key,
date_key,
app_no,
'' AS pid,
count(distinct user_id) AS pull_up_uv,
count(if(date_key=date(register_time),user_id,null))fresh_cnt
FROM user
group by date_key,
app_no;
注意：
1、
AS
只会复制属性以及属性值到新的表中
2、使用as创建的表，并不会带原表的分区（分区丢失），包括一些字段的约束等（可以通过
describe formatted
查看）
3、新表中会将原表的分区当做字段出现在新表中。
4、当需要复制表结果时不能使用
AS
，使用
like
创建表
3、create table xxx like [database.table]建表
create table test.dept_tmp
like test.dept;
注意：
不会复制表中属性值，只会复制表结构（包括表的分区以及存储格式之类的，区别
as
）
hive表数据存储格式
Hive支持内置和自定义开发的文件格式。以下是Hive内置的一些格式：
1、文本格式
hive中默认的数据存储格式是文件格式，主要是一下几种，主要优劣点如下：
1、
textfile
存储空间消耗比较大，并且压缩的
text
无法分割和合并查询的效率最低,可以直接存储，加载数据的速度最高.
2、
sequencefile
存储空间消耗最大,压缩的文件可以分割和合并查询效率高，需要通过text文件转化来加载.
3、rcfile
存储空间最小，查询的效率最高 ，需要通过text文件转化来加载，加载的速度最低.
相比传统的行式存储引擎，列式存储引擎具有更高的压缩比，更少的IO操作而备受青睐（注：列式存储不是万能高效的，很多场景下行式存储仍更加高效），尤其是在数据列（column）数很多，但每次操作仅针对若干列的情景，列式存储引擎的性价比更高。
下面对几种文件存储格式进行简单介绍。
1.TEXTFILE
简介
：默认格式，建表时不指定默认为这个格式，导入数据时会直接把数据文件拷贝到hdfs上不进行处理。源文件可以直接通过
hadoop fs -cat
查看.
存储方式
：行存储
特点
：磁盘开销大,数据解析开销大.压缩的text文件 hive无法进行合并和拆分。
建表示例：
CREATE TABLE [EXTERNAL] [IF NOT EXESITS] test.dept (
`dept_no` int COMMENT '部门编号',
`addr` string COMMENT '部门地址',
`tel` string COMMENT '联系方式')
partitioned by(date_key string COMMENT '日期维度，分区主键')
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS textfile
LOCATION '/user/hive/warehouse/test/dept';
2.SEQUENCEFILE
简介
：一种
Hadoop API
提供的二进制文件，使用方便、可分割、可压缩等特点。
基本原理
：
SEQUENCEFILE
将数据以
< key,value>
的形式序列化到文件中。序列化和反序列化使用
Hadoop
的标准的
Writable
接口实现。
key
为空，用
value
存放实际的值， 这样可以避免
map
阶段的排序过程。它有三种压缩选择：
NONE, RECORD, BLOCK。
Record
压缩率低，一般建议使用
BLOCK
压缩。文件和
Hadoop api
中的
mapfile
是相互兼容的。使用时设置参数:
set hive.exec.compress.output=true;
set io.seqfile.compression.type=BLOCK; --NONE/RECORD/BLOCK
create table test2
(str STRING)
STORED AS SEQUENCEFILE;
3.RCFILE
简介
：
一种行列存储相结合的存储方式。首先，其将数据按行分块，保证同一个
record
在一个块上，避免读一个记录需要读取多个
block
。其次，块数据列式存储，有利于数据压缩和快速的列存取。
特点
：
理论上具有高查询效率（但hive官方说效果不明显，只有存储上能省10%的空间，所以不好用，可以不用）。
RCFile
结合行存储查询的快速和列存储节省空间的特点 :
- 同一行的数据位于同一节点，因此元组重构的开销很低；
- 块内列存储，可以进行列维度的数据压缩，跳过不必要的列读取。
查询过程中，在
IO
上跳过不关心的列。实际过程是，在
map
阶段从远端拷贝仍然拷贝整个数据块到本地目录，也并不是真正直接跳过列，而是通过扫描每一个
row group
的头部定义来实现的。 但是在整个
HDFS Block
级别的头部并没有定义每个列从哪个
row group
起始到哪个
row group
结束。所以在读取所有列的情况下，
RCFile
的性能反而没有
SequenceFile
高。
4.ORC
hive给出的新格式，属于
RCFILE
的升级版。
简介
：
ORC（OptimizedRC File）
存储源自于
RC（RecordColumnar File
）这种存储格式，
RC
是一种列式存储引擎，对
schema
演化（修改
schema
需要重新生成数据）支持较差，而
ORC
是对
RC
改进，但它仍对
schema
演化支持较差，主要是在压缩编码，查询性能方面做了优化。
RC/ORC
最初是在Hive中得到使用，最后发展势头不错，独立成一个单独的项目。Hive
1.x版本对事务和
update
操作的支持，便是基于
ORC
实现的（其他存储格式暂不支持）。
ORC
发展到今天，已经具备一些非常高级的
feature
，比如支持
update
操作，支持
ACID
，支持
struct
，
array
复杂类型。你可以使用复杂类型构建一个类似于
parquet
的嵌套式数据架构，但当层数非常多时，写起来非常麻烦和复杂，而
parquet
提供的
schema
表达方式更容易表示出多级嵌套的数据类型。
存储方式
: 列式存储
特点
：
ORC
是
RCfile
的升级版，性能有大幅度提升，而且数据可以压缩存储，压缩比和
Lzo
压缩差不多，比
text
文件压缩比可以达到70%的空间。而且读性能非常高，可以实现高效查询。
具体介绍https://cwiki.apache.org/confluence/display/Hive/LanguageManual+ORC
示例：
CREATE TABLE [EXTERNAL] [IF NOT EXESITS] test.dept (
`dept_no` int COMMENT '部门编号',
`addr` string COMMENT '部门地址',
`tel` string COMMENT '联系方式')
partitioned by(date_key string COMMENT '日期维度，分区主键')
STORED AS ORC
LOCATION '/user/hive/warehouse/test/dept';
注意
：
只有
TEXTFILE
表能直接加载数据，必须本地
load
数据和
external
外部表直接加载运路径数据，都只能用
TEXTFILE
表。更深一步，hive默认支持的压缩文件（hadoop默认支持的压缩格式）也只能用
TEXTFILE
表直接读取。其他格式不行。可以通过
TEXTFILE
表加载后
insert
到其他表中。
换句话说，
SequenceFile
、
RCFile
表不能直接加载数据，数据要先导入到
textfile
表，再从
textfile
表通过
insert select from
导入到
SequenceFile
,
RCFile
表。
SequenceFile
、
RCFile
表的源文件不能直接查看，在hive中用
select
看。
RCFile
源文件可以用
hive –service rcfilecat /xxx/000000_0
查看，但是格式不同，很乱。
ORCFile
源文件可以用
hive –orcfiledump
来进行分析
ORC
存储文件，就可以看到这些信息：
hive –orcfiledump < path_to_file>
建表语句如下：
同时，将
ORC
的表中的
NULL
取值，由默认的
\N
改为”,
ORC
三种创建/使用方式:
1. STORED AS ORC;
2.ROW FORMAT SERDE ‘org.apache.hadoop.hive.ql.io.orc.OrcSerde’ with serdeproperties(‘serialization.null.format’ = ”) STORED AS ORC;
3.ROW FORMAT DELIMITED NULL DEFINED AS ” STORED AS ORC;
方式一:
create table if not exists test_orc(
advertiser_id string,
ad_plan_id string,
cnt BIGINT
) partitioned by (day string, type TINYINT COMMENT '0 as bid, 1 as win, 2 as ck', hour TINYINT)
STORED AS ORC;
alter table test_orc set serdeproperties('serialization.null.format' = '');
方式二:
drop table test_orc;
create table if not exists test_orc(
advertiser_id string,
ad_plan_id string,
cnt BIGINT
) partitioned by (day string, type TINYINT COMMENT '0 as bid, 1 as win, 2 as ck', hour TINYINT)ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.orc.OrcSerde' with serdeproperties('serialization.null.format' = '')
STORED AS ORC;
方式三:
drop table test_orc;
create table if not exists test_orc(
advertiser_id string,
ad_plan_id string,
cnt BIGINT
) partitioned by (day string, type TINYINT COMMENT '0 as bid, 1 as win, 2 as ck', hour TINYINT)ROW FORMAT DELIMITED
NULL DEFINED AS ''
STORED AS ORC;
查看结果:
hive> show create table test_orc;
CREATE TABLE `test_orc`(
`advertiser_id` string,
`ad_plan_id` string,
`cnt` bigint)
PARTITIONED BY (
`day` string,
`type` tinyint COMMENT '0 as bid, 1 as win, 2 as ck',
`hour` tinyint)
ROW FORMAT DELIMITED
NULL DEFINED AS ''
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
'hdfs://namenode/hivedata/warehouse/pmp.db/test_orc'
TBLPROPERTIES (
'transient_lastDdlTime'='1465992916');
5.Apache Parquet
源自于
google Dremel
系统（可下载论文参阅），
Parquet
相当于
Google Dremel
中的数据存储引擎，而
Apache
顶级开源项目
Drill
正是
Dremel
的开源实现。
Apache Parquet
最初的设计动机是存储嵌套式数据，比如
Protocolbuffer
，
thrift
，
json
等，将这类数据存储成列式格式，以方便对其高效压缩和编码，且使用更少的
IO
操作取出需要的数据，这也是
Parquet
相比于
ORC
的优势，它能够透明地将
Protobuf
和
thrift
类型的数据进行列式存储，在
Protobuf
和
thrift
被广泛使用的今天，与
parquet
进行，是一件非容易和自然的事情。
除了上述优势外，相比于
ORC
,
Parquet
没有太多其他可圈可点的地方，比如它不支持
update
操作（数据写成后不可修改），不支持
ACID
等。
示例：
CREATE TABLE [EXTERNAL] [IF NOT EXESITS] test.dept (
`dept_no` int COMMENT '部门编号',
`addr` string COMMENT '部门地址',
`tel` string COMMENT '联系方式')
partitioned by(date_key string COMMENT '日期维度，分区主键')
STORED AS parquet
LOCATION '/user/hive/warehouse/test/dept';
6.Avro
Avro
（读音类似于[ævrə]）是
Hadoop
的一个子项目，由
Hadoop
的创始人
Doug Cutting
牵头开发。
Avro
是一个数据序列化系统，设计用于支持大批量数据交换的应用。它的主要特点有：支持二进制序列化方式，可以便捷，快速地处理大量数据；动态语言友好，
Avro
提供的机制使动态语言可以方便地处理
Avro
数据。
如果需要在Hive中使用
Avro( Starting in Hive 0.14)
，需要在
$HIVE_HOME/lib
目录下放入以下四个工具包：avro-1.7.1.jar、avro-tools-1.7.4.jar、jackson-core-asl-1.8.8.jar、jackson-mapper-asl-1.8.8.jar。当然，你也可以把这几个包存在别的路径下面，但是你需要把这四个包放在
CLASSPATH
中。
为了解析
Avro
格式的数据，我们可以在Hive建表的时候用下面语句：
hive> CREATE EXTERNAL TABLE tweets
> COMMENT "A table backed by Avro data with the
> Avro schema embedded in the CREATE TABLE statement"
> ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
> STORED AS
> INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
> OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
> LOCATION '/user/wyp/examples/input/'
> TBLPROPERTIES (
> 'avro.schema.literal'='{
> "type": "record",
> "name": "Tweet",
> "namespace": "com.miguno.avro",
> "fields": [
> { "name":"username", "type":"string"},
> { "name":"tweet", "type":"string"},
> { "name":"timestamp", "type":"long"}
> ]
> }'
> );
可将
TBLPROPERTIES
中的语句写入到一个文件中,然后再引用:
TBLPROPERTIES (
‘avro.schema.url’=‘file:///path/to/the/schema/tweets.avsc’);
{
"namespace": "com.linkedin.haivvreo",
"name": "test_serializer",
"type": "record",
"fields": [
{ "name":"string1", "type":"string" },
{ "name":"int1", "type":"int" },
{ "name":"tinyint1", "type":"int" },
{ "name":"smallint1", "type":"int" },
{ "name":"bigint1", "type":"long" },
{ "name":"boolean1", "type":"boolean" },
{ "name":"float1", "type":"float" },
{ "name":"double1", "type":"double" },
{ "name":"list1", "type":{"type":"array", "items":"string"} },
{ "name":"map1", "type":{"type":"map", "values":"int"} },
{ "name":"struct1", "type":{"type":"record", "name":"struct1_name", "fields": [
{ "name":"sInt", "type":"int" }, { "name":"sBoolean", "type":"boolean" }, { "name":"sString", "type":"string" } ] } },
{ "name":"union1", "type":["float", "boolean", "string"] },
{ "name":"enum1", "type":{"type":"enum", "name":"enum1_values", "symbols":["BLUE","RED", "GREEN"]} },
{ "name":"nullableint", "type":["int", "null"] },
{ "name":"bytes1", "type":"bytes" },
{ "name":"fixed1", "type":{"type":"fixed", "name":"threebytes", "size":3} }
] }
注意
:
Hive0.14及之后的版本
中可用
STORED AS AVRO
简化建表语句. 具体说明可查阅
官网
:
然后用
Snappy
压缩我们需要的数据.压缩完的数据假如存放在
/home/wyp/twitter.avsc
文件中，我们将这个数据复制到
HDFS
中的
/user/wyp/examples/input/
目录下：
hadoop fs -put /home/wyp/twitter.avro /user/wyp/examples/input/
然后我们就可以在Hive中使用了。
7.自定义格式
用户可以通过实现
inputformat
和
outputformat
来自定义输入输出格式。
2、行格式和
SerDe
SerDe
SerDe
是
“Serializer and Deserializer”
的简称。Hive使用
SerDe
（和
FileFormat
）来读写表行。
我们可以使用自定义
SerDe
或使用
Native SerDe
创建表。如果未指定
ROW FORMAT
或指定了
ROW FORMAT DELIMITED
，则使用
Native SerDe
。 使用
SERDE
子句创建具有自定义
SerDe
的表。 从
Hive 0.14
开始，已经为
Native Hive SerDes
引入了注册机制。这允许在
CreateTable
语句中使用
“STORED AS”
关键字代替
{SerDe，InputFormat和OutputFormat}
规范的三元组之间的动态绑定 。
通过此注册机制添加了以下映射：
Hive当前使用这些
SerDe
类来序列化和反序列化数据. 要添加具有
STORED AS
关键字的新
Native``SerDe
，请按照下列步骤操作：
1.创建一个从
AbstractStorageFormatDescriptor.java
扩展的存储格式描述符类， 它返回一个
“STORED AS ”
关键字以及
InputFormat
，
OutputFormat
和
SerDe
类的名称。
2.将存储格式描述符类的名称添加到
StorageFormatDescriptor
注册文件。
分区表
可以使用
PARTITIONED BY
子句创建分区表。表可以包含一个或多个分区列，并为分区列中的每个不同值组合创建单独的数据目录。此外，可以使用
CLUSTERED BY
列对表或分区进行分区，并且可以通过
SORT BY
列在该存储桶中对数据进行排序。这可以提高某些类型的查询的性能。如果在创建分区表时出现此错误：“
FAILED
：语义分析错误：列在分区列中重复”，这意味着您尝试将分区列包含在表本身的数据中。您可能确实已经定义了列。但是，您创建的分区会生成一个可以查询的伪列，因此您必须将表列重命名为其他内容（用户不应该查询！）。
例如，假设您的原始未分区表有三列：
id
，
date
和
name
。
例：
id int,
date date,
name varchar
现在你想在日期上进行分区。您的Hive定义可以使用
“dtDontQuery”
作为列名，以便
“date”
可以用于分区（和查询）。
例：
create table table_name (
id int,
dtDontQuery string,
name string
)
partitioned by (date string);
现在，您的用户仍将查询
“ where date = ‘…’”
，但第二列
dtDontQuery
将保留原始值。这是创建分区表的示例语句：例：
CREATE TABLE page_view(
viewTime INT,
userid BIGINT,
page_url STRING,
referrer_url STRING,
ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(dt STRING, country STRING)
STORED AS SEQUENCEFILE;
上面的语句使用
viewTime
，
userid
，
page_url
，
referrer_url
和
ip
列（包括注释）创建
page_view
表。该表也是分区的，数据存储在序列文件中。假定文件中的数据格式由
ctrl-A
字段分隔，并由换行符分隔行。例：
CREATE TABLE page_view(
viewTime INT,
userid BIGINT,
page_url STRING,
referrer_url STRING,
ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(dt STRING, country STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
STORED AS SEQUENCEFILE;
上述语句允许您创建与上一个表相同的表。
在前面的示例中，数据存储在
<hive.metastore.warehouse.dir> / page_view
中。
hive.metastore.warehouse.dir
在Hive配置文件
hive-site.xml
中指定密钥的值。
分桶表
分桶对应hdfs目录下的一个个文件,它是将1张大表进行hash(表行索引多分桶数
hash
,
hash
值相同的到同一个文件中去),将一份数据拆分成多份,优化查询效率.分桶和
MapReduce
的分区概念相似。
例：
CREATE TABLE page_view(
viewTime INT,
userid BIGINT,
page_url STRING,
referrer_url STRING,
ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(dt STRING, country STRING)
CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
COLLECTION ITEMS TERMINATED BY '\002'
MAP KEYS TERMINATED BY '\003'
STORED AS SEQUENCEFILE;
在上面的示例中，
page_view
表被用户ID分块（聚集），并且在每个桶中，数据按
viewTime
的递增顺序排序。这样的组织允许用户对集群列进行有效采样
- 在本例中为
userid
。排序属性允许内部操作员在评估查询时利用更知名的数据结构，同时提高效率。如果任何列是列表或映射，则可以使用
MAP KEYS
和
COLLECTION ITEMS
关键字。
CLUSTERED BY
和
SORTED BY
创建命令不会影响数据如何插入表中- 只会影响它的读取方式。这意味着用户必须小心地通过指定减少器的数量等于桶的数量并在查询中使用
CLUSTER BY
和
SORT BY
命令来正确插入数据。
临时表
版本信息:截至Hive 0.14.0。
已创建为临时表的表仅对当前会话可见。数据将存储在用户的暂存目录中，并在会话结束时删除。如果使用数据库中已存在的永久表的数据库/表名创建临时表，则在该会话中，对该表的任何引用都将解析为临时表，而不是永久表。如果不删除临时表或将其重命名为非冲突名称，用户将无法访问该会话中的原始表。
临时表具有以下限制：
不支持分区列。
不支持创建索引。
例：
CREATE TEMPORARY TABLE list_bucket_multiple (
col1 STRING,
col2 int,
col3 STRING);
事务型表
版本信息:截至Hive 4.0。
支持
ACID
语义操作的表。
例：
CREATE TRANSACTIONAL TABLE transactional_table_test(
key string,
value string)
PARTITIONED BY(ds string)
STORED AS ORC;
创建视图（用于视图查询）
关键信息源于多个复杂的关联表，可以建立视图来简化操作不用每次重复执行一段重复代码，因为视图把查询语句虚拟成一个虚表来供我们操作
视图是从一个或者几个来导出的表，数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍然存放在之前的表中，他就像一个窗口供我们查看数据库中我们需要的一部分数据。
create view viewname as (子查询);
create view deemview as
(SELECT * from dept LEFT JOIN emp on dept.id=emp.dept_id);
查看视图：
show create view viewname;
注意:视图不可以创建索引，也不能关联触发器和默认值； 视图可以使用
order by
；
修改视图也是对表的数据的修改，删除视图时不会删除表内的数据； 视图支持嵌套，也就是可以把根据视图检索出来的东西来建立新的视图。