$hive>CREATE external TABLE IF NOT EXISTS customer(id int,name string,age int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE ;
分区表:
$hive>CREATE TABLE t3(id int,name string,age int) PARTITIONED BY (Year INT, Month INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;
添加分区:$hive>alter table t3 add partition (year=2014, month=12);
删除分区:$hive>ALTER TABLE t3 DROP IF EXISTS PARTITION (year=2014, month=11);
引用hbase 表的数据:
$hive> CREATE EXTERNAL TABLE airDay(key string, daytime string,citycode string,so2 string, co string,no2 string,o3 string,pm10 string,pm2_5 string,aqi string,measure string,timepoint string) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,f1:daytime,f1:citycode,f1:so2,f1:co," + "f1:no2,f1:o3,f1:pm10,f1:pm2_5,f1:aqi,f1:measure,f1:timepoint")TBLPROPERTIES ("hbase.table.name" = "Air:airDay");
2,//查看表数据
$hive>desc customer;
$hive>desc formatted customer;
3, //加载数据到hive表
$hive>load data local inpath '/home/centos/customers.txt' into table t2 ; //local上传文件
$hive>load data inpath '/user/centos/customers.txt' [overwrite] into table t2 ; //移动文件
加载数据到分区表:
hive>load data local inpath '/home/centos/customers.txt' into table t3 partition(year=2014,month=11);
4, hive>create table tt as select * from users ; //携带数据和表结构
hive>create table tt like users ; //不带数据,只有表结构
5,创建json格式数据的表:
(1)下载json-serde-1.3.8-jar-with-dependencies.jar,添加到hive/lib包下
(2)[hive-site.xml]
<property>
<name>hive.aux.jars.path</name>
<value>file:///soft/hive/lib/json-serde-1.3.8-jar-with-dependencies.jar</value>
</property>
<property>
<name>hive.exec.compress.output</name>
<value>false</value>
</property>
(3)hive>create table test(id int , name string) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
STORED AS TEXTFILE;