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' COLLECTION ITEMS TERMINATED BY '\002' MAP KEYS TERMINATED BY '\003' STORED AS TEXTFILE;
这里创建了表page_view,有表的注释,一个字段ip的注释,分区有两列,分别是dt和country。 [ROW FORMAT DELIMITED]关键字,是用来设置创建的表在加载数据的时候,支持的列分隔符。不同列之间用一个’\001′分割,集合(例如array,map)的元素之间以’\002′隔开,map中key和value用’\003′分割。 [STORED AS file_format]关键字是用来设置加载数据的数据类型,默认是TEXTFILE,如果文件数据是纯文本,就是使用 [STORED AS TEXTFILE],然后从本地直接拷贝到HDFS上,hive直接可以识别数据。 常用的创建表
CREATE TABLE login( userid BIGINT, ip STRING, time BIGINT) PARTITIONED BY(dt STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE;
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 '/user/hadoop/warehouse/page_view';
CREATE TABLE new_key_value_store AS SELECT (key % 1024) new_key, concat(key, value) key_value_pair FROM key_value_store;
复杂的方式
CREATE TABLE new_key_value_store ROW FORMAT SERDE "org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe" STORED AS RCFile AS SELECT (key % 1024) new_key, concat(key, value) key_value_pair FROM key_value_store SORT BY new_key, key_value_pair;
删除表
DROP TABLE table_name; DROP TABLE IF EXISTS table_name;
简单的创建表create table table_name ( id int, dtDontQuery string, name string) 创建有分区的表create table table_name ( id int, dtDontQuery string, name string)partitioned b