hive建立外部表语句

hive外部表和内部表的区别是，内部表将表删除了，数据会跟着删除，外部表删除表以后，数据还在，所以生产上一般使用外部表建表语句如下
指定表位置用：

LOCATION
  's3://djf.taobao.com/hive_dataware/mediabuy_dsp/t_dsp_bid_middle_detail_tbl_3'

指定表的格式类型：下面说明用orc格式

ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCA

指定分区：ps，分区字段不要出现在表字段中了

PARTITIONED BY ( 
  `updatedate` string)

CREATE EXTERNAL TABLE `mediabuy_dsp.t_dsp_bid_middle_detail_tbl_3`(
  `app` string, 
  `day` string, 
  `hour` string, 
  `adx` string,
  `os` string,
  `osv` string,
  `country` string,
  `impType` string,
  `cnt` string,
  `request` string,
  `response` string,
  `bid` string,
  `timeout` string,
  `status` string,
  `ccount` string,
  `remarketing` string,
  `banner` string
PARTITIONED BY ( 
  `updatedate` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  's3://djf.taobao.com/hive_dataware/mediabuy_dsp/t_dsp_bid_middle_detail_tbl_3'