基本表操作

查看数据表详情

desc touna;

desc formatted touna;

更改表的列名

ALTER TABLE laoniushuju_pv CHANGE host domain string;

清空数据表（慎用！）

insert overwrite table ua_chinese select * from ua_chinese where 1=0;

删除表（慎用！）

drop table pingan_data_pv_20150504;

内部表

创建内部表

create table kw_test(
srcip string,
ad string,
ts string,
url string,
ref string,
ua string,
dstip string,
cookie string,
datelabel string,
loadstamp string)
row format delimited fields terminated by '\t';

导入数据到内部表

load data local inpath '/home/yangbl/data/task' into table ua_chinese;

load data inpath '/user/xuji/yang/data/part-r-00069.gz' into table e_pingan.kw_test;

临时外部表

创建临时外部表

CREATE EXTERNAL TABLE IF NOT EXISTS laoniushuju_pv
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS RCFILE
LOCATION '/user/e_pingan/private/laoniushuju/base/laoniushuju_pv'
AS
${查询语句};

外部表分区表

创建外部分区表

CREATE EXTERNAL TABLE IF NOT EXISTS laoniushuju_pv (
host string,
pv bigint)
PARTITIONED BY (
datelabel string,
loadstamp string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS RCFILE
LOCATION '/user/e_pingan/private/laoniushuju/base/laoniushuju_pv';

向表中插入数据

查询结果插入数据到分区表

INSERT OVERWRITE TABLE laoniushuju_pv PARTITION(datelabel='${date}',loadstamp)
${查询语句};

HDFS数据导入分区表

LOAD DATA INPATH '/user/yangbl/yang/task/文件名' INTO TABLE laoniushuju_pv PARTITION(datelabel='${date}',loadstamp='${hour}');

本地数据导入分区表

LOAD DATA LOCAL INPATH '/home/yangbl/data/task/文件名' INTO TABLE laoniushuju_pv PARTITION(datelabel='${date}',loadstamp='${hour}');

存储查询结果

保存到HDFS路径

INSERT OVERWRITE DIRECTORY '/user/yangbl/yang/task' 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
${查询语句};

保存到本地路径

INSERT OVERWRITE LOCAL DIRECTORY '/home/yangbl/data/task' 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
${查询语句};