CREATE TABLE AS

Creates a named table whose contents are materialized from a SQL query.

These tables can join and reshape data from multiple underlying sources, presenting them as a single, unified dataset. Because the results are persisted, they can be used downstream by other queries, tools, or systems without re-running the original logic.

They are commonly used to restrict access to sensitive columns or rows, expose aggregated or derived data, and encapsulate complex transformations behind a simpler, stable interface.

Syntax

CREATE TABLE [workspace_name.]table_name [ ( column column_list [, ...] ) ] AS { query };

Parameters

Optional the <workspace_name> specifies the location in which the table is created. If not provided, the table is created in the current workspace.

The <table_name> of the newly created table. The fully qualified table name must be unique.

Optional <column_list> specifies the column names in the table. If you do not supply column names, they are derived from the query.

A SELECT <query> that constructs the table from base tables or other views.

Output

The command output provides table properties and metadata in the following columns:

Setting the format

Before creating a table, you must specify the output format that Metaform will use to write the data. Metaform does not infer this automatically. Instead, it relies on the store.format system option to determine how query results are materialized on disk.

Metaform supports the following storage formats:

• csv, tsv, psv — Delimited text formats suitable for interoperability and simple inspection
• parquet — A columnar format optimized for analytical workloads and efficient compression
• json — A semi-structured format useful for nested or schema-flexible data

Use the ALTER SYSTEM command to set the desired format before executing your statement:

ALTER SYSTEM SET `store.format` = 'csv';

The selected format applies to all subsequent operations.

In addition to setting the storage format, ensure that your connector configuration supports the desired output format. The workspace you are writing to must be configured with the appropriate file format settings.

Usage notes

• Metaform stores a table in the location defined by the workspace in effect when the CREATE TABLE statement is executed. If no workspace is explicitly specified, the table is created in the current workspace.
• Creating a table requires a writable workspace. At present, Metaform supports table creation only in file system–backed workspaces.
• The following example shows a writable workspace defined within a connector, using the /persist directory of the local file system:

"persist": {
  "location": "/persist",
  "writable": true,
  "defaultInputFormat": null,
  "allowAccessOutsideWorkspace": false
}

Example

The following example demonstrates creating a table using the scenario described in the PDF connector:

CREATE TABLE pdf.persist.`donations`
AS
SELECT
  State,
  City,
  SUM(
    CAST(
      REPLACE(REPLACE(Amount, '$', ''), ',', '') AS DOUBLE
    )
  ) AS `Total Amount`
FROM
  table(
    pdf.`data.pdf` (
      type => 'pdf',
      combinePages => true,
      extractHeaders => true,
      extractionAlgorithm => 'spreadsheet'
    )
  )
WHERE
  State IS NOT NULL
  AND TRIM(State) <> ''
GROUP BY
  State,
  City
ORDER BY
  State ASC,
  `Total Amount` DESC;