HIVE 基本操作create table

2.1 createtable 

2.1.1 总述　　

  l CREATETABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常。

　　l EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive

创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

　　l LIKE 允许用户复制现有的表结构，但是不复制数据。

　　l 用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive 通过 SerDe 确定表的具体的列的数据。

　　l 如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED ASSEQUENCE 。

　　l 有分区的表可以在创建的时候使用 PARTITIONED BY 语句。一个表可以拥有一个或者多个分区，每一个分区单独存在一个目录下。而且，表和分区都可以对某个列进行 CLUSTERED BY 操作，将若干个列放入一个桶（bucket）中。也可以利用SORT BY 对数据进行排序。这样可以为特定应用提高性能。

　　l 表名和列名不区分大小写，SerDe 和属性名区分大小写。表和列的注释是字符串。

2.1.2 语法　　

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
　　[(col_namedata_type [COMMENT col_comment], …)]
　　[COMMENTtable_comment]
　　[PARTITIONED BY(col_name data_type [COMMENT col_comment], …)]
　　[CLUSTERED BY(col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], …)] INTOnum_buckets BUCKETS]
　　[
　　[ROW FORMATrow_format] [STORED AS file_format]
　　| STORED BY’storage.handler.class.name’ [ WITH SERDEPROPERTIES (...) ] (Note: only available starting with 0.6.0)
　　]
　　[LOCATIONhdfs_path]
　　[TBLPROPERTIES(property_name=property_value, ...)] (Note: only available startingwith 0.6.0)
　　[ASselect_statement] (Note: this feature isonly available starting with 0.5.0.)
　　CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
　　LIKEexisting_table_name
　　[LOCATIONhdfs_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 KEYSTERMINATED BY char] [LINES TERMINATED BY char]
　　| SERDEserde_name [WITH SERDEPROPERTIES (property_name=property_value,property_name=property_value, ...)]
　　file_format:
　　: SEQUENCEFILE
　　| TEXTFILE
　　| RCFILE (Note: only available starting with 0.6.0)
　　| INPUTFORMATinput_format_classname OUTPUTFORMAT output_format_classname
　　目前在hive中常用的数据类型有:
　　BIGINT – 主要用于状态,类别,数量的字段, 如status/option/type/quantity
　　DOUBLE – 主要用于金额的字段, 如fee/price/bid
　　STRING – 除上述之外的字段基本都使用String, 尤其是id和日期时间这样的字段

2.1.3 基本例子　

1、如果一个表已经存在，可以使用if not exists

2、 create table xiaojun(id int,cont string) row format delimitedfields terminated by ‘\005′ stored as textfile;

　　terminated by：关于来源的文本数据的字段间隔符

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

　　4、Alibaba数据库常用间隔符的读取

　　我们的常用间隔符一般是Ascii码5，Ascii码7等。在hive中Ascii码5用’\005’表示， Ascii码7用’\007’表示，依此类推。

　　5、装载数据

　　查看一下：Hadoop fs -ls

　　LOAD DATA INPATH’/user/admin/xiaojun/a.txt’ OVERWRITE INTO TABLE xiaojun;

6、如果使用external建表和普通建表区别
　　A、指定一个位置，而不使用默认的位置。如：
　　create EXTERNAL table xiaojun(id int,cont string) row format delimited fields terminatedby ‘\005′ stored as textfile location ‘/user/admin/xiaojun/’;
　　————–check结果
　　ij> selectLOCATION from tbls a,sds b where a.sd_id=b.sd_id and tbl_name=’xiaojun’;
　　—–
　　LOCATION
　　——————————————————————————————————————————–
　　hdfs://hadoop1:7000/user/admin/xiaojun
　　ij> selectLOCATION from tbls a,sds b where a.sd_id=b.sd_id and tbl_name=’c';
　　—-
　　LOCATION
　　——————————————————————————————————————————–
　　hdfs://hadoop1:7000/user/hive/warehouse/c
　　B、对于使用create table external建表完成后，再drop掉表，表中的数据还在文件系统中。
　　如：
　　hive>create EXTERNAL table xiaojun(id int,cont string) row formatdelimited fields terminated by ‘\005′ stored as textfile;
　　—-
　　OK
　　hive> LOADDATA INPATH ‘/user/admin/xiaojun’ OVERWRITE INTO TABLE xiaojun;
　　————————————————–
　　Loading data totable xiaojun
　　OK
　　hive> droptable xiaojun;
　　—-
　　OK
　　[admin@hadoop1bin]$ ./hadoop fs -ls hdfs://hadoop1:7000/user/hive/warehouse/xiaojun
　　Found 1 items
　　使用普通的建表DROP后则找不到

2.1.4 创建分区　　

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

　　创建含分区的表。

　　命令原型：

CREATE TABLE page_view(viewTime INT, userid BIGINT,
　　page_urlSTRING, referrer_url STRING,
　　ip STRINGCOMMENT ‘IP Address of the User’)
　　COMMENT ‘This isthe page view table’
　　PARTITIONED BY(dtSTRING, country STRING)
　　CLUSTEREDBY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
　　ROW FORMATDELIMITED
　　FIELDSTERMINATED BY ‘\001′
　　COLLECTION ITEMSTERMINATED BY ‘\002′
　　MAP KEYSTERMINATED BY ‘\003′
　　STORED ASSEQUENCEFILE;
　　Eg:
　　建表：
　　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 astextfile;
　　装载数据：
　　LOAD DATA INPATH’/user/admin/SqlldrDat/CnClickstat/20101101/19/clickstat_gp_fatdt0/0′ OVERWRITEINTO TABLE c02_clickstat_fatdt1
　　PARTITION(dt=’20101101′);
　　访问某一个分区
　　SELECT count(*)
　　FROMc02_clickstat_fatdt1 a
　　WHERE a.dt >=’20101101′ AND a.dt < ’20101102′;

2.1.5 其它例子　　

1、指定LOCATION位置　　

CREATE EXTERNAL TABLE page_view(viewTime INT, useridBIGINT,
　　page_urlSTRING, referrer_url STRING,
　　ip STRING COMMENT’IP Address of the User’,
　　country STRINGCOMMENT ‘country of origination’)
　　COMMENT ‘This isthe staging page view table’
　　ROW FORMATDELIMITED FIELDS TERMINATED BY ‘\054′
　　STORED AS TEXTFILE
　　LOCATION’<hdfs_location>’;

2、 复制一个空表　　

CREATE TABLE empty_key_value_store
　　LIKE key_value_store;