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'