ctas

How do I run a CTAS (Create Table As) with a Query?

There are a few different ways to run a CTAS with a Query in Presto. Below we’ll lay those out.

OPTION 1: Managed Schema
Managed Schema Creation:
CREATE SCHEMA managed_schema WITH (location = 's3a://mybucket/managed_schema/');

Create Table AS with Managed Schema:

CREATE TABLE managed_movies_orc
COMMENT 'Managed Movies ORC'
WITH (format = 'ORC')
AS
select * from "ahana-demo".ahana_s3_movies
CTAS

OPTION 2: External Table 
Technically you can’t do a create table as with an external table you will get the error:

presto error: External tables cannot be created using CREATE TABLE AS

You can however do a create table followed by insert into once the hive.non-managed-table-writes-enabled is set to true
https://prestodb.io/docs/current/connector/hive.html

CREATE TABLE <your schema>.ext_table_movies_orc( movieid bigint, title varchar, genres varchar ) 
WITH 
( format = 'ORC',
  external_location= 's3a://mybucket/externa_table/'
)

insert into <your schema>.ext_table_movies_orc select * from "ahana-demo".ahana_s3_movies

If you want to get started with Presto easily, check out Ahana Cloud. It’s SaaS for Presto and takes away all the complexities of tuning, management and more. It’s free to try out for 14 days, then it’s pay-as-you-go through the AWS marketplace.