CREATE TABLE AS SELECT (CTAS)

Reference and syntax for the CTAS SQL command.

Creates a table and loads data into it based on the SELECT query. The table column names and types are automatically inferred based on the output columns of the SELECT. When specifying explicit column names, those override the column names inferred from SELECT.

Syntax

CREATE [FACT|DIMENSION] TABLE [IF NOT EXISTS] <table_name>
[(<column_name>[, ...n] )]
[PRIMARY INDEX <column_name>[, <column_name>[, ...n]]]
[PARTITION BY <column_name>[, <column_name>[, ...n]]]
AS <select_query>

Parameters

Parameter	Description
<table_name>	An identifier that specifies the name of the external table. This name should be unique within the database.
<column_name>	An identifier that specifies the name of the column. This name should be unique within the table.
<select_query>	Any valid select query.

Remarks

Unlike a traditional CREATE statement, the primary index and partition definition must come before the AS clause.