COPY FROM

-- Using location object (recommended)
COPY
[INTO] <table_name> [ <column_mapping> ] 
FROM location_name
[ LIMIT <count> ]
[ OFFSET <start> ]
[ [ WITH ] ( <option> [, ...] )  ]
[ WHERE <condition> ]
|
-- Using static credentials
COPY
[INTO] <table_name> [ <column_mapping> ] 
FROM '<url>'
[ LIMIT <count> ]
[ OFFSET <start> ]
[[ WITH ] ( <option> [, ...] ) ]
[ WHERE <condition> ]

<column_mapping>:
    ( <column_name> [DEFAULT <default_value>] [ { $c<source_column_index> | <source_column_name> } ] [, ...] )

<option>:
    [ CREDENTIALS = ( <credentials> ) ] 
    [ PATTERN = <glob_pattern> ]
    [ TYPE = { AUTO | CSV | TSV | PARQUET } ]
    [ AUTO_CREATE = { TRUE | FALSE } ]
    [ ALLOW_COLUMN_MISMATCH = { TRUE | FALSE } ]
    [ ERROR_FILE = <directoryLocation> ]
    [ ERROR_FILE_CREDENTIALS = <credentials> ]
    [ MAX_ERRORS_PER_FILE = { integer | '<percentage>' } ]
    [ CASE_SENSITIVE_COLUMN_MAPPING = { FALSE | TRUE } ]
    [ <csv_options> ]
    [ <parquet_options> ]
    [ <settings> ]

<credentials>: (    -- Only applicable when using URL destination
  AWS_ACCESS_KEY_ID = '<aws_access_key_id>'
  AWS_SECRET_ACCESS_KEY = '<aws_secret_access_key>'
  [ AWS_SESSION_TOKEN = '<aws_session_token>' ]
  |
  AWS_ROLE_ARN = '<aws_role_arn>'
  [ AWS_ROLE_EXTERNAL_ID = '<aws_role_external_id>' ]
)

<csv_options>:
    [ HEADER = { FALSE | TRUE  } ]
    [ DELIMITER = '<character>' ]
    [ NEWLINE = '<string>' ]
    [ QUOTE = { DOUBLE_QUOTE | '<character>' |  SINGLE_QUOTE } ]
    [ ESCAPE = '<character>' ]
    [ NULL_STRING = '<string>' ]
    [ EMPTY_FIELD_AS_NULL = { TRUE | FALSE } ]
    [ SKIP_BLANK_LINES = { FALSE | TRUE } ]
    [ DATE_FORMAT = '<date_format>' ]
    [ TIMESTAMP_FORMAT = '<timestamp_format>' ]

<parquet_options>:
    [ SUPPORT_STRUCTS = { FALSE | TRUE } ]
    [ REPLACE_NON_UTF8_BYTES = { FALSE | TRUE } ]
    [ PARSE_JSON_AS = { 'TEXT' | 'JSON' }]

COPY my_table 
FROM my_location
WITH (PATTERN = '*.parquet');

COPY tournament_results
FROM 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/rankings/TournamentID=1/'
LIMIT 50 OFFSET 50
WHERE $source_file_timestamp > NOW() - interval '3 YEARS';

COPY tournament_results
FROM my_tournament_location
LIMIT 50 OFFSET 50
WHERE $source_file_timestamp > NOW() - interval '3 YEARS';

-- Create a table
CREATE TABLE levels (
    LevelID INT,
    Name TEXT
);

-- Copy data using location object
COPY INTO levels
FROM my_game_data_location
WITH (
    PATTERN = 'levels/*.parquet',
    TYPE = PARQUET
);

COPY tournament_results 
FROM my_tournaments_location
WITH 
(
    PATTERN = 'tournaments/*/data/*.parquet',
    TYPE = PARQUET
);

COPY table_from_multiple_directories FROM 
    's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/playstats/TournamentID=1/',
    's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/playstats/TournamentID=10/',
    's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/playstats/TournamentID=100/'
WITH (pattern='*.parquet');

COPY table_from_multiple_files FROM 
    's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/levels.csv',
    's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/tournaments.csv'
WITH (HEADER=TRUE);

COPY automatic_schema_table 
FROM 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/levels.csv'
WITH (HEADER=TRUE);

SELECT * FROM automatic_schema_table ORDER BY 4,5;

COPY pattern_target FROM 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/'
WITH (TYPE=CSV, HEADER=TRUE, PATTERN='*.csv');

CREATE TABLE levels ("LevelID" TEXT NOT NULL, date_of_creation TIMESTAMP);
COPY INTO levels("LevelID", date_of_creation $source_file_timestamp)
FROM 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/levels.csv' WITH HEADER=TRUE;

SELECT * FROM levels;

CREATE TABLE column_mapping_by_name (
    LevelID INT,
    Name TEXT
);

COPY INTO column_mapping_by_name
(
   LevelID,
   Name
)
FROM 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/levels.csv'
WITH (HEADER = TRUE);

CREATE TABLE column_mapping_positional (
    id INT,
    name TEXT
);

COPY INTO column_mapping_positional
(
   id $c1,
   name $c4
)
FROM 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/levels.csv'
WITH (HEADER = TRUE);

CREATE TABLE levels (
  "LevelID"          TEXT NOT NULL,
  "NumberOfLaps"     INT,
  "SceneDetails"     TEXT,
  date_of_creation   TIMESTAMP,
  file_name          TEXT,
  file_last_modified TIMESTAMP,
  file_size          BIGINT
);

COPY INTO levels (
  "LevelID",
  "NumberOfLaps",
  "SceneDetails",
  date_of_creation   $source_file_timestamp,
  file_name          $source_file_name,
  file_last_modified $source_file_timestamp,
  file_size          $source_file_size
)
FROM 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/levels.csv'
WITH (HEADER=TRUE);

CREATE TABLE col_mismatch ("LevelID2" int, "Name" text);
COPY col_mismatch
FROM 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/levels.csv'
WITH (HEADER=TRUE, MAX_ERRORS_PER_FILE='0%');

COPY INTO new_table FROM 's3://path/to/file.parquet'
WITH (TYPE=parquet, SUPPORT_STRUCTS=TRUE);

-- Example with STRUCT column. SUPPORT_STRUCTS is not used because it would have no effect.
CREATE TABLE table_with_struct_type (kv STRUCT(key INT, value TEXT);
COPY INTO table_with_struct_type FROM 's3://path/to/file.parquet';

-- Example with shredded struct columns. Again, SUPPORT_STRUCTS is not used because it would have no effect.
CREATE TABLE table_without_struct_type ("kv.key" INT, "kv.value" TEXT);
COPY INTO table_without_struct_type FROM 's3://path/to/file.parquet';

CREATE TABLE col_mismatch_csv ("LevelID2" int NOT NULL);

COPY col_mismatch_csv FROM 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/levels.csv'
WITH (HEADER=TRUE, MAX_ERRORS_PER_FILE='0%');

CREATE TABLE table_all_errors ("LevelID2" int NOT NULL);

COPY table_all_errors FROM 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/levels.csv'
WITH (HEADER=TRUE, MAX_ERRORS_PER_FILE='100%');

CREATE TABLE col_mismatch_type_csv ("Name" int);

COPY col_mismatch_type_csv("Name" name)
  FROM 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/levels.csv'
WITH (TYPE=CSV, HEADER=TRUE);

CREATE TABLE table_write_errors(TournamentID INT, Name INT);

COPY table_write_errors(TournamentID TournamentId, Name Name) FROM 's3://firebolt-publishing-public/help_center_assets/tournaments.csv'
WITH (ERROR_FILE_CREDENTIALS = (
    AWS_ACCESS_KEY_ID = 'YOUR_AWS_ACCESS_KEY_ID'
    AWS_SECRET_ACCESS_KEY = 'YOUR_AWS_SECRET_ACCESS_KEY'
),
MAX_ERRORS_PER_FILE='100%', HEADER=TRUE, ERROR_FILE='s3://bucket_name/error_directory/');

COPY error_reasons FROM 's3://bucket_name/error_directory/' 
WITH (PATTERN='*error_reasons*.csv', HEADER=TRUE);

SELECT * FROM error_reasons ORDER BY source_line_num LIMIT 1;

COPY rejected_rows FROM 's3://bucket_name/error_directory/'
WITH (PATTERN='*rejected_rows*.csv', HEADER=FALSE);

SELECT * FROM rejected_rows ORDER BY f0 LIMIT 1;

CREATE TABLE target_default_mapping ("LevelID_team_A" text, "LevelID_team_B" text);
COPY target_default_mapping("LevelID_team_A", "LevelID", "LevelID_team_B" default 50 "Country")
  FROM 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/levels.csv'
WITH (HEADER=TRUE);

CREATE TABLE IF NOT EXISTS
 players (
   PlayerID DATE,
   Nickname TEXT,
   Email TEXT);

COPY players FROM 's3://firebolt-sample-datasets-public-us-east-1/gaming/parquet/players/'
WITH (TYPE=PARQUET, MAX_ERRORS_PER_FILE='100%',
ERROR_FILE='s3://bucket_name/parquet_error_directory/');

COPY error_reasons FROM 's3://bucket_name/parquet_error_directory/'
WITH (PATTERN='*error_reasons*', HEADER=TRUE);

SELECT * FROM error_reasons;