COPY INTO <location>

This command unloads data from a table (or query) into one or more files in one of the following locations:

• User / Internal / External stages: See Understanding Stages to learn about stages in Databend.
• Buckets or containers created in a storage service.

See also: COPY INTO <table>

Syntax

COPY INTO { internalStage | externalStage | externalLocation }
FROM { [<database_name>.]<table_name> | ( <query> ) }
[ FILE_FORMAT = ( { TYPE = { CSV | JSON | NDJSON | PARQUET } [ formatTypeOptions ] } ) ]
[ copyOptions ]
[ VALIDATION_MODE = RETURN_ROWS ]

internalStage

internalStage ::= @<internal_stage_name>[/<path>]

externalStage

externalStage ::= @<external_stage_name>[/<path>]

externalLocation

Amazon S3-like Storage Services

externalLocation ::=
  's3://<bucket>[<path>]'
  CONNECTION = (
        <connection_parameters>
  )

For the connection parameters available for accessing Amazon S3-like storage services, see Connection Parameters.

Azure Blob Storage

externalLocation ::=
  'azblob://<container>[<path>]'
  CONNECTION = (
        <connection_parameters>
  )

For the connection parameters available for accessing Azure Blob Storage, see Connection Parameters.

Google Cloud Storage

externalLocation ::=
  'gcs://<bucket>[<path>]'
  CONNECTION = (
        <connection_parameters>
  )

For the connection parameters available for accessing Google Cloud Storage, see Connection Parameters.

Alibaba Cloud OSS

externalLocation ::=
  'oss://<bucket>[<path>]'
  CONNECTION = (
        <connection_parameters>
  )

For the connection parameters available for accessing Alibaba Cloud OSS, see Connection Parameters.

Tencent Cloud Object Storage

externalLocation ::=
  'cos://<bucket>[<path>]'
  CONNECTION = (
        <connection_parameters>
  )

For the connection parameters available for accessing Tencent Cloud Object Storage, see Connection Parameters.

HDFS

externalLocation ::=
  'hdfs://<endpoint_url>[<path>]'
  CONNECTION = (
        <connection_parameters>
  )

For the connection parameters available for accessing HDFS, see Connection Parameters.

WebHDFS

externalLocation ::=
  'webhdfs://<endpoint_url>[<path>]'
  CONNECTION = (
        <connection_parameters>
  )

For the connection parameters available for accessing WebHDFS, see Connection Parameters.

FILE_FORMAT

See Input & Output File Formats.

copyOptions

copyOptions ::=
  [ SINGLE = TRUE | FALSE ]
  [ MAX_FILE_SIZE = <num> ]

Parameter	Description	Required
SINGLE	When TRUE, the command unloads data into one single file. Default: FALSE.	Optional
MAX_FILE_SIZE	The maximum size (in bytes) of each file to be created. Effective when SINGLE is FALSE. Default: 67108864 (64 MB).	Optional

Examples

The following examples unload data into an internal stage:

-- Create a table
CREATE TABLE test_table (
     id INTEGER,
     name VARCHAR,
     age INT
);

-- Insert data into the table
INSERT INTO test_table (id,name,age) VALUES(1,'2',3), (4, '5', 6);

-- Create an internal stage
CREATE STAGE s2;

-- Unload the data in the table into a CSV file on the stage
COPY INTO @s2 FROM test_table FILE_FORMAT = (TYPE = CSV);

-- Unload the data from a query into a parquet file on the stage
COPY INTO @s2 FROM (SELECT name, age, id FROM test_table LIMIT 100) FILE_FORMAT = (TYPE = PARQUET);