A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from https://docs.snowflake.com/en/developer-guide/python-connector/../../sql-reference/sql/copy-files below:

Website Navigation


COPY FILES | Snowflake Documentation

COPY FILES

Copy files from a source stage to an output stage. You can provide the file list to copy by using a query or a listing on an existing stage.

Use a stage as a source if you want to copy files:

Use a query as a source if you want to copy files:

This command supports file copy operations from and to existing named stages, as the following table illustrates:

Source location

Target location

Internal named stage

Internal named stage

External stage

Internal named stage

Internal named stage

External stage

External stage

External stage

Snowflake Git repository clone

Internal named stage

Snowflake Git repository clone

External stage

A target or source external stage can reference files in any of the following cloud storage services or on-premises locations:

See also:

External stages , Internal stages, Snowflake Git repository clone

Syntax

Copy from a stage

COPY FILES INTO @[<namespace>.]<stage_name>[/<path>/]
  FROM @[<namespace>.]<stage_name>[/<path>/]
  [ FILES = ( '<file_name>' [ , '<file_name>' ] [ , ... ] ) ]
  [ PATTERN = '<regex_pattern>' ]
  [ DETAILED_OUTPUT = { TRUE | FALSE } ]

Copy

Copy from a query

COPY FILES INTO @[<namespace>.]<stage_name>[/<path>/]
  FROM ( SELECT <existing_url> [ , <new_filename> ] FROM ... )
  [ DETAILED_OUTPUT = { TRUE | FALSE } ]

Copy

Required parameters
INTO @[namespace.]stage_name[/path/]

Specifies the target location for the copied files.

Note

  • If a target or source path name includes special characters or spaces, you must enclose the INTO ... value in single quotes.

  • The values for INTO ... must be literal constants. The values cannot be SQL variables.

Using a stage as a source
FROM @[namespace.]stage_name[/path/]

Specifies the source location where the files to copy are staged. The values provided to FROM ... follow the same specification and constraints as INTO... values.

Using a query as a source
FROM (SELECT existing_url [ , new_filename ] FROM ... )

Specifies the source location(s) and optional relative output location(s) of the copies. Each row returned from the SELECT query represents a file to be copied.

existing_url can be a scoped URL, stage name, or stage URL. new_filename is a relative path from the output stage provided to INTO. The resulting file will be then copied to:

@[<namespace>.]<stage_name>[/<path>]<new_filename>

If the new_filename column is not provided, the relative path of existing_url is used.

Optional parameters
FILES = ( 'file_name' [ , 'file_name' ... ] )

Specifies a list of one or more comma-separated file names to copy. The files must already be staged in the source location that you specify in the command. Snowflake skips any specified files that can’t be found.

You can specify a maximum of 1000 file names.

Copy files from query does not support this option. Instead, use the query to provide the filename list.

Note

To set the file path for external stages, Snowflake prepends the URL in the stage definition to each file name in the list.

However, Snowflake does not insert a separator between the path and file name. You must explicitly include a separator (/) at the end of the URL in the stage definition or at the beginning of each file name in the FILES list.

PATTERN = 'regex_pattern'

Specifies a regular expression pattern for filtering the list of files to copy. This command applies the regular expression to the entire storage location in the FROM clause.

Copy files from query does not support this option. Instead, use the query to match the pattern.

Tip

For best performance, avoid patterns that filter on a large number of files.

DETAILED_OUTPUT = { TRUE | FALSE }

Specifies whether the command output should summarize the results of the copy operation or list each file copied.

Values:

  • If TRUE, the output includes a row for each file copied to the target location. A single column named file contains the target path (if applicable) and file name for each copied file.

  • If FALSE, the output is a single row with the number of files that were copied.

Default:

TRUE

Access control requirements

A role used to execute this SQL command must have the following privileges (depending on the source and target locations) at a minimum:

Privilege

Object

Notes

USAGE

External stage

Required on a source or target external stage.

READ

Internal named stage

Required on a source internal stage.

WRITE

Internal named stage

Required on a target internal stage.

The USAGE privilege on the parent database and schema are required to perform operations on any object in a schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage notes Examples Copy files

Copy all of the files from an existing source stage (src_stage) to an existing target stage (trg_stage):

COPY FILES
  INTO @trg_stage
  FROM @src_stage;

Copy

Note

To copy files from or to an external stage with a protected storage location, make sure the stage definition includes credentials to access the cloud storage location.

Specify the names of files to copy from an existing source stage (src_stage) to an existing target stage (trg_stage):

COPY FILES
  INTO @trg_stage
  FROM @src_stage
  FILES = ('file1.csv', 'file2.csv');

Copy

Copy files from a specific path on an existing stage (src_stage/src_path/) to a specific path on an existing target stage (trg_stage/trg_path/):

COPY FILES
  INTO @trg_stage/trg_path/
  FROM @src_stage/src_path/;

Copy

Copy files using pattern matching

Use pattern matching to load only compressed CSV files in any path on an existing source stage (src_stage) to an existing target stage (trg_stage):

COPY FILES
  INTO @trg_stage
  FROM @src_stage
  PATTERN='.*/.*/.*[.]csv[.]gz';

Copy

The .* component represents zero or more occurrences of any character. The square brackets escape the period character (.) that precedes a file extension.

Copy only uncompressed CSV files whose names include the string sales:

COPY FILES
  INTO @trg_stage
  FROM @src_stage
  PATTERN='.*sales.*[.]csv';

Copy

Copy files defined by query Copy a single file

The file name remains the same as in the source stage.

COPY FILES
  INTO @trg_stage
  FROM (SELECT '@src_stage/file.txt');

Copy

Copy a single file to an arbitrary file 
COPY FILES
  INTO @trg_stage
  FROM (SELECT '@src_stage/file.txt', 'new_filename.txt');

Copy

Copy files from a table

Generic queries are supported to allow general processing.

-- Create a table with URLs
CREATE TABLE urls(src_file STRING, trg_file STRING);
INSERT INTO urls VALUES ('@src_stage/file.txt', 'new_filename.txt');

-- Insert additional URLs here
COPY FILES
  INTO @trg_stage
  FROM (SELECT src_file, trg_file FROM urls);

Copy

Copy only some files 
COPY FILES
  INTO @trg_stage
  FROM (SELECT src_file, trg_file FROM urls WHERE src_file LIKE '%file%');

Copy

Copy files from a directory 
COPY FILES
  INTO @trg_stage
  FROM (SELECT relative_path FROM directory(@src_stage) WHERE relative_path LIKE '%.txt');

Copy

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4