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

      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