Hive建表语句详解–CREATE TABLE

创建表的三种方法

CREATE TABLE `mydb.dept`( `dept_no` int, `addr` string, `tel` string) partitioned by(date string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ; 

create table mytest_tmp1 as select * from FDM_SOR.mytest_deptaddr where statis_date=''; 

create table mytest_tmp like FDM_SOR.mytest_deptaddr; 

注意：不会复制表中属性值，只会复制表结构（包括表的分区以及存储格式之类的，区别as）

创建表

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).表名和列名不区分大小写，但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 

内部和外部表

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 ' 
  
    '; 
  

存储格式

Hive支持内置和自定义开发的文件格式。以下是Hive内置的一些格式：

默认格式，建表时不指定默认为这个格式，导入数据时会直接把数据文件拷贝到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 

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'=''); 

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"} > ] > }' > ); 

 { "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 简化建表语句.
具体说明可查阅官网:
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

分区表

id int, date date, name varchar 

create table table_name ( id int, dtDontQuery string, name string ) partitioned by (date string); 

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; 

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; 

分桶表

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; 

临时表

CREATE TEMPORARY TABLE list_bucket_multiple (col1 STRING, col2 int, col3 STRING); 

事务型表

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;