external

Applies to: ❌ Data 360 SQL ✅ Tableau Hyper API

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

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

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

• <source_location>: A location to read the data from. See External Location.

• 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 the rows read from the external file(s).

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.

1SELECT name FROM external(
2    './products.csv',
3    COLUMNS => DESCRIPTOR(
4        name      text NOT NULL,
5        price     int
6    ),
7    FORMAT => 'csv',
8    DELIMITER => '|'
9) WHERE price > 100;

1SELECT name FROM external(
2    ARRAY['./products1.csv', './products2.csv', './products3.csv'],
3    COLUMNS => DESCRIPTOR(
4        name      text NOT NULL,
5        price     int
6    ),
7    FORMAT => 'csv',
8    DELIMITER => '|'
9) WHERE price > 100;

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.

1SELECT name FROM external(
2    s3_location(
3        's3://mybucket/mydirectory/products.parquet',
4        access_key_id => '',
5        secret_access_key => ''
6    )
7) WHERE price > 100;

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.

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

• External File Support
• Reading External Data in SQL
• External Formats and Options
• External Locations
• Set Returning Functions