Hive基本操作（一）

create table 

1、1总述 

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

 EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

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

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

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

 

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

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

1、2 语法

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]
 | STORED BY 'storage.handler.class.name' [ WITH SERDEPROPERTIES (...) ]  (Note:  only available starting with 0.6.0)
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]  (Note:  only available starting with 0.6.0)
  [AS select_statement]  (Note: this feature is only available starting with 0.5.0.)

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
  LIKE existing_table_name
  [LOCATION hdfs_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 KEYS TERMINATED BY char] [LINES TERMINATED BY char]
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
file_format:
  : SEQUENCEFILE
  | TEXTFILE
  | RCFILE     (Note:  only available starting with 0.6.0)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

目前在hive中常用的数据类型有:

  BIGINT – 主要用于状态,类别,数量的字段, 如status/option/type/quantity

  DOUBLE – 主要用于金额的字段, 如fee/price/bid

  STRING – 除上述之外的字段基本都使用String, 尤其是id和日期时间这样的字段

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、数据库常用间隔符的读取

我们的常用间隔符一般是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 terminated by '\005' stored as textfile location '/user/admin/xiaojun/';

B、对于使用create table external建表完成后，再drop掉表，表中的数据还在文件系统中。

如：

hive>create  EXTERNAL  table xiaojun(id int,cont string) row formatdelimited fields terminated by '\005' stored as textfile;

 

hive> LOADDATA INPATH '/user/admin/xiaojun' OVERWRITE INTO TABLE xiaojun;

 

hive> drop  table xiaojun;

 

[admin@hadoop1bin]$ ./hadoop fs -ls hdfs://hadoop1:7000/user/hive/warehouse/xiaojun

Found 1 items

使用普通的建表DROP后则找不到。

1、4创建分区

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

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 as textfile;

装载数据：
LOAD DATA INPATH '/user/admin/SqlldrDat/CnClickstat/20101101/19/clickstat_gp_fatdt0/0' OVERWRITE INTO TABLE c02_clickstat_fatdt1
 PARTITION(dt='20101101');

访问某一个分区
SELECT count(*)
    FROM c02_clickstat_fatdt1 a
    WHERE a.dt >= '20101101' AND a.dt < '20101102';