Applies to: ❌ Data 360 SQL ✅ Tableau Hyper API
To specify the location where external data is read, use one of:
1'file_system_path'
2'amazon_s3_uri'
3s3_location('amazon_s3_uri'
4 [, access_key_id => 'text', secret_access_key => 'text' [, session_token => 'text'] ]
5 [, region => 'text']
6)
7ARRAY[ <source_location> [, ...] ]This location can be a path in the file system of the Hyper server (not the client!) or an Amazon S3 URI. For example, s3://mybucket/path/to/myfile.csv.
1SELECT * FROM external('path/to/data.parquet')Using a file system path instructs the server to directly read from a file. The files must reside on or be accessible to the database server machine, not the client. They must be accessible to and readable by the user, not the client. The name must be specified from the viewpoint of the server.
1SELECT * 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)In the case of an Amazon S3 URI, you can use the extended syntax s3_location(...) to specify credentials and optionally a bucket region. The specified credentials can be empty if used for buckets with anonymous access.
If no bucket region is specified, Data 360 SQL infers the bucket region. In this case, the specified credentials must have permissions for the HeadBucket S3 request.
Data 360 SQLs read capabilities from Amazon S3 are highly optimized. They use techniques such as concurrent requests, request hedging, and prefetching. For maximum performance, ensure a high network bandwidth to Amazon S3. For example, run Data 360 SQL on an AWS EC2 instance.
1SELECT * FROM external(
2 azure_location(
3 'abfss://container@account.dfs.core.windows.net/products.parquet', sas_token => 'secret-sas-token'
4 )
5)To access data stored on Microsoft Azure Blob Storage, use the azure_location syntax. Data 360 SQL supports the DFS and BLOB endpoints and supports these Azure URL formats.
abfss://container@account.dfs.core.windows.net/...https://account.dfs.core.windows.net/container/...https://account.blob.core.windows.net/container/...http:// and abfs://, and it establishes SSL encrypted connections.1SELECT * FROM external(
2 ARRAY['data/2022/sales.parquet', 'data/2023/sales.parquet']
3)The ARRAY[ <source_location> [, ...] ] syntax can be used to read rows from multiple source locations. Rows are read from all source locations, and the union of all rows from all input files will be returned. All source locations in this list must share the same file format. The list can contain S3 locations, local files, or any other file locations.
Don’t mix different external formats in one invocation of external or in one external table. To scan data from different formats at the same time or to scan data in the same format but with different format options, use one external function or external table per format and combine the results with UNION ALL. This example combines two parquet files with two CSV files.
1-- products1.parquet and products2.parquet both contain the columns (name text, price int)
2SELECT name, price FROM external(ARRAY['products1.parquet', 'products2.parquet'])
3UNION ALL
4SELECT name, price FROM external('products3.csv', COLUMNS => DESCRIPTOR(name text, price int), DELIMITER => ';')
5UNION ALL
6SELECT name, price FROM external('products4.csv', COLUMNS => DESCRIPTOR(name text, discount double precision, price int), DELIMITER => '|')The two parquet files can be scanned with one invocation of external. The CSV files have a different schema and different delimiters, so they’re scanned separately. The example combines 'products4.csv' with the rest, even though it has an additional column discount, because this column isn’t selected.
j