Please use the form below to provide your feedback. Because your feedback is valuable to us, the information you submit in this form is recorded in our issue tracking system (JIRA), which is publicly available. You can track the status of your feedback using the ticket number displayed in the dialog once you submit the form.

CREATE COLLECTION AS Statements

  • Capella Columnar
  • reference
March 23, 2025
+ 12
This topic describes how you use the CREATE COLLECTION AS statement to both create a standalone collection and populate it with the results of a query.

Syntax

CreateCollectionAs EBNF 
EBNF
Copy
CreateCollectionAs ::= "CREATE" "ANALYTICS"? "COLLECTION" ("IF" "NOT" "EXISTS")?
                        QualifiedName
                        "PRIMARY" "KEY" "(" FieldList ")" ( "AUTOGENERATED" )?
                        "AS"
                        "SelectClause"
CreateCollectionAs Diagram
"CREATE" "COLLECTION" ("IF" "NOT" "EXISTS")? QualifiedName "PRIMARY" "KEY" "(" FieldList ")" ( "AUTOGENERATED" )? "AS" "SelectClause"
Show SelectClause Diagram
SELECT
SelectClause
Show Projection Diagram
(Expr ("AS"? Identifier)?) | (VariableRef ".")? "*"
Projection

For more information about SELECT clause syntax, see Queries.

Example

In this example, you create a standalone collection, travel-sample.inventory.restaurants and specify that you want the system to autogenerate its primary key. You then select a subset of the documents—those with an activity of eat—in the existing travel-sample.inventory.landmark sample data collection to populate the new collection.

SQL++
Copy
  CREATE COLLECTION `travel-sample`.inventory.restaurants
    PRIMARY KEY (restId: UUID) AUTOGENERATED
    AS
    SELECT *
    FROM `travel-sample`.inventory.landmark
    WHERE activity = "eat";

After you use CREATE COLLECTION AS, you can run ANALYZE COLLECTION on the collection to update the data sample used by cost-based optimization (CBO). See Cost-Based Optimizer for Capella Columnar Services.

See Also