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
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
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.