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