COPY INTO Statements

  • Capella Columnar
  • reference
    +
    This topic describes how you use the COPY INTO DML statement to upsert—both insert and update—objects from an external collection to a standalone collection.
    You can also populate a standalone collection by importing a file in a supported format from your local network. See Import Data to a Standalone Collection.

    Syntax

    CopyInto EBNF
    CopyInto ::= "COPY" "INTO"? QualifiedName
                 ("AS" CollectionTypeDef)?
                 "FROM" Identifier
                 "AT" QualifiedName
                 ("PATH" StringLiteral) ?
                 ("WITH" ObjectConstructor) ?
    CopyInto Diagram
    "COPY" "INTO"? QualifiedName FROM Identifier "AT" QualifiedName PATH StringLiteral "WITH" ObjectConstructor ?

    Example

    This example refreshes the data in the my-ad-hoc-collection with documents located in an external file store named TravelShop. The referenced link, myS3Link, stores further details about the location of the external file store.

      COPY INTO database_name.scope_name.`my-ad-hoc-collection`
      FROM TravelShop AT myS3Link
      PATH "json-data/customers"
      WITH {
        "format": "json",
        "include": ["*2018*.json", "*2019*.json"]
      };

    After you use COPY INTO, 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.

    Arguments

    FROM

    The FROM clause identifies the bucket name on the external data source, such as an Amazon S3 bucket.

    AT

    The AT clause specifies the name of the link that contains credentials for the S3 bucket name. The specified link must have a type of S3.

    PATH

    The PATH clause is a string that specifies the location path to the location of the data, relative to the external data source.

    WITH

    In the WITH clause, you define the same ObjectConstructor parameters for format and file parsing, and what to include or exclude, as you would provide when you create an external collection. See CREATE an External Collection.