Salesforce Docs Portal

external

Applies to: ❌ Data 360 SQL ✅ Tableau Hyper API

Reads data stored in an external file format from one or multiple external files.

Syntax

external(<source_location> [, <option> [, ...] ] )

Where <option>s are provided using the => syntax for named parameters.

Arguments

Required

Optional

  • FORMAT => format_name: Selects the data format to be read. You can exclude this option if the format is in the file extension. All extensions in a source list must match. For supported formats, see External Formats.
  • format_specific_option => value: A format-specific option. See Format Options.

Returns

Returns the rows read from the external file(s).

Examples

Read a local CSV file

Read a local CSV file from the working directory of the server, having two columns and a custom delimiter. A filter is applied to the price column.

SELECT name FROM external(
    './products.csv',
    COLUMNS => DESCRIPTOR(
        name      text NOT NULL,
        price     int
    ),
    FORMAT => 'csv',
    DELIMITER => '|'
) WHERE price > 100;

Reading from multiple CSV files

SELECT name FROM external(
    ARRAY['./products1.csv', './products2.csv', './products3.csv'],
    COLUMNS => DESCRIPTOR(
        name      text NOT NULL,
        price     int
    ),
    FORMAT => 'csv',
    DELIMITER => '|'
) WHERE price > 100;

Reading an Apache Parquet file from Amazon S3

The following SQL snippet reads an Apache Parquet file from Amazon S3 using empty credentials. The bucket region is inferred from the bucket location. The schema of the file is inferred from the schema information in the file. The file format is inferred to be Apache Parquet, based on the file extension.

SELECT name FROM external(
    s3_location(
        's3://mybucket/mydirectory/products.parquet',
        access_key_id => '',
        secret_access_key => ''
    )
) WHERE price > 100;

Reading from Amazon S3 with explicit Amazon S3 credentials and bucket region

Similar to the previous example, we read a Parquet file from Amazon S3. This time, we provide Amazon S3 credentials, allowing us to also access private buckets.

Furthermore, we specify the bucket region explicitly. Without an explicit region parameter, Hyper will auto-infer the region from the bucket location by calling the corresponding AWS API. This auto-inference requires a network roundtrip. By specifying the region explicitly, we avoid this network roundtrip and improve performance.

SELECT name FROM external(
    s3_location(
        's3://mybucket/mydirectory/products.parquet',
        access_key_id => 'ACCESSKEYID12EXAMPLE',
        secret_access_key => 'sWfssWSmnME5X/36dsf3G/cbyDzErEXAMPLE123',
        region => 'us-east-1'
    )
) WHERE price > 100;

Related Documentation