READ_CSV

A table-valued function (TVF) that reads CSV files from Amazon S3. The function can use either a location object (recommended) or direct credentials to access the data. READ_CSV returns a table with data from the specified CSV file, where each cell is read as TEXT.

​

Syntax

-- Using LOCATION object (recommended)
READ_CSV ( 
  LOCATION => location_name
  [, COMPRESSION => <file_compression>]
  [, HEADER => { TRUE | FALSE }]
  [, DELIMITER => <field_delimiter>]
  [, QUOTE => { "'" | '"' | SINGLE_QUOTE | DOUBLE_QUOTE}]
  [, NULL_STRING => <null_string>]
  [, ESCAPE => <escape_character>]
  [, SKIP_BLANK_LINES => { TRUE | FALSE }]
  [, EMPTY_FIELD_AS_NULL => { TRUE | FALSE }]
)
|
-- Using static credentials
READ_CSV ( 
  URL => <url>
  [, COMPRESSION => <file_compression>]
  [, 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>]
  [, HEADER => { TRUE | FALSE }]
  [, DELIMITER => <field_delimiter>]
  [, QUOTE => { "'" | '"' | SINGLE_QUOTE | DOUBLE_QUOTE}]
  [, NULL_STRING => <null_string>]
  [, ESCAPE => <escape_character>]
  [, SKIP_BLANK_LINES => { TRUE | FALSE }]
  [, EMPTY_FIELD_AS_NULL => { TRUE | FALSE }]
)

​

Parameters

​

Return Type

The result is a table with the data from the CSV file. Each cell is read as a TEXT.

​

Examples

​

Using LOCATION object

Best practice

Firebolt recommends using a LOCATION object to store credentials for authentication.

The following code example reads a CSV file from the location specified by my_location, treating the first row as a header containing column names:

SELECT * FROM READ_CSV(
    LOCATION => my_location,
    HEADER => true
);

​

Using static credentials

SELECT * FROM READ_CSV(
    URL => 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/levels.csv',
    HEADER => true
);

Example

In the following example, the URL is set as the first positional parameter and reads a CSV file:

SELECT * FROM READ_CSV('s3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/levels.csv');

Returns:

| f0 | f1 | f2 | f3 | f4 | f5 | f6 | f7 | f8 | … | |:----|:----|:----|:----|:----|:----|:----|:----|:----|:----|:----| | LevelID | GameID | Level | Name | LevelType | NextLevel | MinPointsToPass | MaxPoints | NumberOfLaps | … | | 1 | 1 | 1 | Thunderbolt Circuit | FastestLap | 2 | 5 | 20 | 5 | … | | 2 | 1 | 2 | Velocity Vale | FirstToComplete | 3 | 15 | 30 | 10 | … | | 3 | 1 | 3 | Raceway Ridge | FastestLap | 4 | 25 | 40 | 20 | … | | 4 | 1 | 4 | Nitro Narrows | FirstToComplete | 5 | 60 | 100 | 10 | … | | 5 | 1 | 5 | Thunder Road | FirstToComplete | 6 | 80 | 150 | 15 | … | | 6 | 1 | 6 | Burnout Boulevard | Drift | 7 | 50 | 80 | 8 | … | | 7 | 1 | 7 | Speed Street | FastestLap | 8 | 40 | 70 | 7 | … | | 8 | 1 | 8 | Racing Ravine | FastestLap | 9 | 60 | 100 | 20 | … | | 9 | 1 | 9 | Drift District | Drift | 10 | 100 | 250 | 25 | … | | 10 | 1 | 10 | Acceleration Alley | FirstToComplete | null | 200 | 500 | 50 | … |

Example

The following example accepts URL as a named parameter and reads a CSV file with column names in the first row:

SELECT * FROM READ_CSV(URL => 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/levels.csv', 
        HEADER => true);

Returns

Example

The following example reads a CSV with headers and reads empty values as empty strings, rather than NULL values:

SELECT * FROM READ_CSV(URL => 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/levels.csv',
        HEADER => true, EMPTY_FIELD_AS_NULL => false);

Returns: