创建按日期范围分区的暂存表

Question

我正在尝试使用以下查询创建一个暂存表，我正在尝试修改它以创建按日期分区的表

create table scratch.myTable
as (
select 
concat(eid,'_',group) as eid_group,
name,
test
from 
test_logs 
where 
regexp_like(eid, '[A-Z0-9]{22}') and 
(regexp_like(group, '[a-z0-9]{8}') OR group = '') and
line_type = 'test' and
date between '2018-09-27' and '2018-09-30' and
eid NOT IN ('123456789','ABCDEFF')
) WITH (partitioned_by sequence('2018-09-27','2018-09-30'))

此查询在 S3 上创建一个暂存表，并将所有内容转储为 ORC 文件。我正在尝试按日期范围partition此表

有人可以帮助我查询吗？

Answer 1

您是否尝试过将"with"替换为"over"？

create table scratch.myTable
as (
select 
concat(eid,'_',group) as eid_group,
name,
test
from 
test_logs 
where 
regexp_like(eid, '[A-Z0-9]{22}') and 
(regexp_like(group, '[a-z0-9]{8}') OR group = '') and
line_type = 'test' and
date between '2018-09-27' and '2018-09-30' and
eid NOT IN ('123456789','ABCDEFF')
) over(partition by date)