HiveQL基本操作整理

1. 创建操作

1.1 创建表

CREATE TABLE pokes (foo INT, bar STRING);

1.2 基于现有的表结构创建一个新表

create table new_table like records;

1.3 创建视图：

CREATE VIEW valid_records AS SELECT * FROM records2 WHERE temperature !=9999;

1.4 创建外部表：

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

1.5 建分区表：

CREATE TABLE par_table(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(date STRING, pos STRING)
ROW FORMAT DELIMITED fields terminated by '\t'
lines terminated by '\n';
STORED AS SEQUENCEFIL