Presto create table with 'with' queries

sql presto
Moosa picture Moosa · Mar 2, 2017 · Viewed 21.1k times · Source

typically to create a table in Presto (from existing db tables), I do:

create table abc as (
select...
)

But to make my code simple, I've broken out subqueries like this:

with sub1 as (
select...
),

sub2 as (
select...
),

sub3 as (
select...
)

select
from sub1 join sub2 on ...
          join sub3 on ...

Where do I put the create table statement here? The actual query is more complex than the above so I am trying to avoid having to put the subqueries within the main query.

Answer

user3250672 picture user3250672 · Mar 10, 2017

This is possible with an INSERT INTO not sure about CREATE TABLE:

INSERT INTO s1 WITH q1 AS (...) SELECT * FROM q1

Maybe you could give this a shot:

CREATE TABLE s1 as WITH q1 AS (...) SELECT * FROM q1

Related questions

Presto SQL - Converting a date string to date format

I'm on presto and have a date formatted as varchar that looks like - 7/14/2015 8:22:39 AM I've looked the presto docs and tried various things(cast, date_format, using split_part to parse and then cast) and am not getting this …

Read More
Presto check if NULL and return default (NVL analog)

Is there any analog of NVL in Presto DB? I need to check if a field is NULL and return a default value. I solve this somehow like this: SELECT CASE WHEN my_field is null THEN 0 ELSE my_field …

Read More
Athena greater than condition in date column

I have the following query that I am trying to run on Athena. SELECT observation_date, COUNT(*) AS count FROM db.table_name WHERE observation_date > '2017-12-31' GROUP BY observation_date However it is producing this …

Read More