hive的with as临时查询语句案例

-- create table xx [stored as parquet] as  with tt1 as(..),tt2 as (..)    语法

drop table if exists tmp_aa.aa;
create table if not exists tmp_aa.aa stored as parquet as
with tt1 as (
select ..
), tt2 as (
select ..
)

select tt1.*,tt2.*
from tt1 
left join tt2
on tt1.id=tt2.id

 

-- with  as  ...  insert into 语法

with  xx as (
select ..
)
, yy as (
select ..
)

insert [overwrite] into   tb_rst
select 

from xx
left join yy
on xx.id=yy.id

 

--ddd基础信息维表，最新， dim.dim_ddd
ALTER TABLE dim.dim_ddd SET TBLPROPERTIES('EXTERNAL'='False');
desc formatted dim.dim_ddd;
DROP TABLE IF  EXISTS dim.dim_ddd;
CREATE EXTERNAL TABLE dim.dim_ddd(
 ddd_code          STRING COMMENT 'ddd编码(可能会变)'
,ddd_name          STRING COMMENT 'ddd名称'
,modify_tm         STRING COMMENT '修改时间'
,ddd_id            STRING COMMENT 'ddd唯一主键'
,load_tm           STRING COMMENT '数据写入时间'
)
COMMENT 'ddd基础信息维表，最新'
stored as parquet;

CREATE EXTERNAL TABLE `ods_aa.bb`(
`id` bigint COMMENT '自增ID',
)
COMMENT 'id账号'
PARTITIONED BY (
`inc_day` string COMMENT '增量日期')
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
'hdfs://cluster1/hive/warehouse/ods/ods_aa/bb'
TBLPROPERTIES (
'parquet.compression'='Snappy',
'transient_lastDdlTime'='1563954325')