CREATE VIEW

Constructs a defines a named, virtual table whose contents are derived from a stored SQL query rather than physically stored data. The view presents the query’s result set as if it were a regular table.

Views can join and reshape data from multiple underlying sources, allowing them to appear as a single, unified dataset. They are commonly used to restrict access to sensitive columns or rows, to expose aggregated or derived data, and to encapsulate complex query logic behind a simpler, stable interface.

Syntax

CREATE [ OR REPLACE ] VIEW [workspace_name.]view_name [ ( column_list [, ...] ) ] AS { query };

Parameters

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

The <view_name> of the newly created view. The fully qualified view name must be unique.

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

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

Output

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

Usage notes

• Metaform stores a view in the location defined by the workspace in effect when the CREATE VIEW statement is executed. If no workspace is explicitly specified, the view is created in the current workspace.
• Creating a view requires a writable workspace. At present, Metaform supports view 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 view using the scenario described in the PDF connector:

CREATE VIEW pdf.persist.donation_report 
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;

SELECT *
FROM pdf.persist.donation_report
ORDER BY `Total Amount`;