Hive常用的建表语句

1，创建表,external 外部表

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