Hive_Table_Operation.md

+
+# 基本表操作
+
+## 查看数据表详情
+
+	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' 
+	${查询语句};