创建表的三种方法
hive创建表的方式（默认路径/user/hive/warehouse，也可以location指定，主要针对external表）
1、使用create命令创建一个新表,带分区
create table `mydb.dept`(
`dept_no` int,
`addr` string,
`tel` string)
partitioned by(date string )
row format delimited fields terminated by ',' ;
可以使用describe formatted mytest_deptaddr 查看建表相关的各种配置属性以及默认属性。从下面可以看出，在创建表时有很多属性，比如存储地址，存储格式等属性我们都没有直接配置，而是选择了系统默认的。
2、把一张表的某些字段抽取出来，创建成一张新表，使用as
create table mytest_tmp1
as
select * from fdm_sor.mytest_deptaddr where statis_date='20180229';
注意： 1.as只会复制属性以及属性值到新的表中
2.使用as创建的表，并不会带原表的分区（分区丢失），包括一些字段的约束等（可以通过describe formatted查看）
3.新表中会将原表的分区当做字段出现在新表中。
3、复制表结构，使用like创建表
create table mytest_tmp like fdm_sor.mytest_deptaddr;
注意：不会复制表中属性值，只会复制表结构（包括表的分区以及存储格式之类的，区别as）
ag真人试玩娱乐官网附件:
https://cwiki.apache.org/confluence/display/hive/languagemanual ddl
创建表
create [temporary] [external] table [if not exists] [db_name.]table_name -- (note: temporary available in hive 0.14.0 and later)
[(col_name data_type [comment col_comment], ... [constraint_specification])]
[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]
[skewed by (col_name, col_name, ...) -- (note: available in hive 0.10.0 and later)]
on ((col_value, col_value, ...), (col_value, col_value, ...), ...)
[stored as directories]
[
[row format row_format]
[stored as file_format]
| stored by 'storage.handler.class.name' [with serdeproperties (...)] -- (note: available in hive 0.6.0 and later)
]
[location hdfs_path]
[tblproperties (property_name=property_value, ...)] -- (note: available in hive 0.6.0 and later)
[as select_statement]; -- (note: available in hive 0.5.0 and later; not supported for external tables)
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 -- (note: available in hive 0.7.0 and later)
primitive_type
: tinyint
| smallint
| int
| bigint
| boolean
| float
| double
| double precision -- (note: available in hive 2.2.0 and later)
| string
| binary -- (note: available in hive 0.8.0 and later)
| timestamp -- (note: available in hive 0.8.0 and later)
| decimal -- (note: available in hive 0.11.0 and later)
| decimal(precision, scale) -- (note: available in hive 0.13.0 and later)
| date -- (note: available in hive 0.12.0 and later)
| varchar -- (note: available in hive 0.12.0 and later)
| char -- (note: available in hive 0.13.0 and later)
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, ... > -- (note: available in hive 0.7.0 and later)
row_format
: delimited [fields terminated by char [escaped by char]] [collection items terminated by char]
[map keys terminated by char] [lines terminated by char]
[null defined as char] -- (note: available in hive 0.13 and later)
| serde serde_name [with serdeproperties (property_name=property_value, property_name=property_value, ...)]
file_format:
: sequencefile
| textfile -- (default, depending on hive.default.fileformat configuration)
| rcfile -- (note: available in hive 0.6.0 and later)
| orc -- (note: available in hive 0.11.0 and later)
| parquet -- (note: available in hive 0.13.0 and later)
| avro -- (note: available in hive 0.14.0 and later)
| jsonfile -- (note: available in hive 4.0.0 and later)
| 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进程管理。
内部表存储在hive.metastore.warehouse.dir路径属性下，默认情况下位于/user/hive/warehouse/databasename.db/tablename/ 文件夹路径中。location在表创建期间，属性可以覆盖默认位置。如果删除内部表或分区，则删除与该表或分区关联的数据和元数据。如果未指定purge选项，则数据将移动到回收站文件夹中一段时间。
当hive应该管理表的生命周期或生成临时表时使用内部表。
外部表描述外部文件的元数据/模式。外部表文件可以由hive外部的进程访问和管理。外部表可以访问存储在azure storage volumes（asv）或远程hdfs位置等源中的数据。如果更改外部表的结构或分区，则可以使用msck repair table table_name语句来刷新元数据信息。
当文件已存在或位于远程位置时使用外部表，即使删除表，文件也应保留。
external关键字允许您创建表并提供location，以便hive不使用此表的默认位置。如果您已经生成了数据，这会派上用场。删除external表时，表中的数据不会从文件系统中删除。
external表指向其存储的任何hdfs位置，而不是存储在配置属性指定的文件夹中hive.metastore.warehouse.dir。
例：
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 '';
您可以使用上述语句创建一个page_view表，该表指向其存储的任何hdfs位置。但是您仍然必须确保数据按照上面create语句中的指定分隔。
有关创建外部表的另一个示例，请参阅教程中的加载数据。
存储格式
hive支持内置和自定义开发的文件格式。以下是hive内置的一些格式：
默认是文本格式.
textfile 存储空间消耗比较大，并且压缩的text 无法分割和合并查询的效率最低,可以直接存储，加载数据的速度最高.
sequencefile 存储空间消耗最大,压缩的文件可以分割和合并查询效率高，需要通过text文件转化来加载.
rcfile 存储空间最小，查询的效率最高 ，需要通过text文件转化来加载，加载的速度最低.
相比传统的行式存储引擎，列式存储引擎具有更高的压缩比，更少的io操作而备受青睐（注：列式存储不是万能高效的，很多场景下行式存储仍更加高效），尤其是在数据列（column）数很多，但每次操作仅针对若干列的情景，列式存储引擎的性价比更高。
1.textfile
默认格式，建表时不指定默认为这个格式，导入数据时会直接把数据文件拷贝到hdfs上不进行处理。源文件可以直接通过hadoop fs -cat 查看.
存储方式：行存储
磁盘开销大,数据解析开销大.
压缩的text文件 hive无法进行合并和拆分.
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结合行存储查询的快速和列存储节省空间的特点
1）同一行的数据位于同一节点，因此元组重构的开销很低；
2) 块内列存储，可以进行列维度的数据压缩，跳过不必要的列读取。
查询过程中，在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
注意：
只有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等。
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 简化建表语句.
具体说明可查阅ag真人试玩娱乐官网:
https://cwiki.apache.org/confluence/display/hive/avroserde
然后用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来自定义输入输出格式。
行格式和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;
上述语句允许您创建与上一个表相同的表。
在前面的示例中，数据存储在
分桶表
分桶对应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；
修改视图也是对表的数据的修改，删除视图时不会删除表内的数据；
视图支持嵌套，也就是可以把根据视图检索出来的东西来建立新的视图。