CREATE DATA SOURCE Examples
1. FILE - Local Filesystem (CSV):
CREATE DATA SOURCE local_files
WITH
(location = '/data/import',
type = 'FILE');
SELECT int_col, str_col
FROM local_files(reference='employees.csv', format='csv', header='true', sep=';', comment='#')
WHERE int_col > 1000;
2. FILE - Amazon S3 (CSV) with Credentials:
CREATE CREDENTIAL s3_cred
WITH
type = 'aws_simple',
secret = '{ "AWS_ACCESS_KEY": "...", "AWS_SECRET_KEY": "..." }';
CREATE DATA SOURCE s3_files
WITH
(location = 's3://my-bucket/data',
type = 'FILE',
storage_credential = s3_cred,
options = ('fs.s3a.endpoint' = 'https://s3.amazonaws.com', 'fs.s3a.path.style.access' = 'true', 'fs.s3a.region' = 'us-east-1'));
SELECT * FROM s3_files(reference='orders/2024.csv', format='csv', header='true', sep=',');
3. FILE - Azure Blob/ADLS Gen2 (OAuth2):
CREATE CREDENTIAL azure_oauth_cred
WITH
type = 'azure_oauth2_client',
secret = '{"AZURE_CLIENT_ENDPOINT": "https://login.microsoftonline.com/<tenant>/oauth2/token", "AZURE_CLIENT_ID": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", "AZURE_CLIENT_SECRET": "..."}';
CREATE DATA SOURCE azure_files
WITH
(location = 'abfs://my-container@myaccount.dfs.core.windows.net',
type = 'FILE',
storage_credential = azure_oauth_cred);
SELECT * FROM azure_files(reference='reports/q4.parquet', format='parquet');
4. FILE - HTTP server:
CREATE DATA SOURCE http_files
WITH
(location = 'http://intranet.example.com/datafiles',
type = 'FILE' );
SELECT * FROM http_files(reference='sales.csv', format='csv', header='true', sep=';');
5. FILE - HDFS:
CREATE DATA SOURCE hdfs_files
WITH
(location = 'hdfs://namenode.example.com:8020',
type = 'FILE');
SELECT _c1, _c0 FROM hdfs_files (reference='warehouse/nation.tbl.gz', format='csv', header='false', sep='|');
6. FILE - Join CSV and Parquet from the Same Source - The same data source can be referenced multiple times in a single query, each time with a different “reference” value:
SELECT p.int_col, c.str_col
FROM parquet_ds(reference='employees.parquet') p
JOIN parquet_ds(reference='departments.csv', format='csv', header='true', sep=';') c
ON p.dept_id = c.dept_id;
Two different data sources can also be joined:
SELECT p.int_col, c.str_col
FROM p_ds(reference='data.parquet') p
JOIN c_ds(reference='data.csv', format='csv', header='true', sep=';') c
ON p.int_col = c.int_col
ORDER BY p.int_col;
7. ICEBERG_HADOOP - Iceberg Hadoop Catalog on S3:
CREATE CREDENTIAL iceberg_s3_cred
WITH
type = 'aws_simple',
secret = '{ "AWS_ACCESS_KEY": "...", "AWS_SECRET_KEY": "..." }';
CREATE DATA SOURCE iceberg_ds
WITH
(location = 's3://iceberg-warehouse/',
type = 'ICEBERG_HADOOP',
storage_credential = iceberg_s3_cred,
options = ('fs.s3a.endpoint' = 'https://s3.amazonaws.com', 'fs.s3a.path.style.access' = 'true', 'fs.s3a.region' = 'us-east-1'));
SELECT * FROM iceberg_ds(reference='mydb.orders') ORDER BY order_id;
8. ICEBERG_REST - Iceberg REST Catalog
CREATE CREDENTIAL rest_s3_cred
WITH
type = 'aws_simple',
secret = '{ "AWS_ACCESS_KEY": "...", "AWS_SECRET_KEY": "..." }';
CREATE DATA SOURCE ice_rest
WITH
(location = 'http://catalog.example.com:8181',
type = 'ICEBERG_REST',
storage_credential = rest_s3_cred,
options =
('ice_rest.s3.endpoint' = 'http://s3.example.com:9000',
'ice_rest.s3.warehouse' = 'demo',
'ice_rest.client.region' = 'us-east-1',
'fs.s3a.path.style.access' = 'true',
'spark.sql.catalog.ice_rest.s3.path-style-access' = 'true'));
SELECT * FROM ice_rest(reference='tpch_sf1.partsupp') ORDER BY "PS_PARTKEY" LIMIT 100;
9. ICEBERG_REST - AWS S3 Tables - When the REST catalog itself requires authentication, for example, for AWS S3 Tables with SigV4 signing, supply “rest_credential” in addition to the “storage_credential”:
CREATE CREDENTIAL s3tables_cred
WITH
type = 'aws_simple',
secret = '{ "AWS_ACCESS_KEY": "...", "AWS_SECRET_KEY": "..." }';
CREATE DATA SOURCE s3tables
WITH
(location = 'https://s3tables.eu-north-1.amazonaws.com/iceberg',
type = 'ICEBERG_REST',
rest_credential = s3tables_cred,
storage_credential = s3tables_cred,
options =
('s3tables.warehouse' = 'arn:aws:s3tables:...',
's3tables.rest.auth.type' = 'sigv4',
's3tables.rest.signing-name' = 's3tables',
's3tables.rest.signing-region' = 'eu-north-1',
's3tables.io-impl' = 'org.apache.iceberg.aws.s3.S3FileIO',
's3tables.client.region' = 'eu-north-1',
'fs.s3a.path.style.access' = 'true',
'fs.s3a.region' = 'eu-north-1'));
SELECT * FROM s3tables(reference='my_namespace.my_table') ORDER BY id;
10. DELTA_LAKE - Delta Lake on Amazon S3:
CREATE CREDENTIAL delta_s3_cred
WITH
type = 'aws_simple',
secret = '{ "AWS_ACCESS_KEY": "...", "AWS_SECRET_KEY": "..." }';
CREATE DATA SOURCE delta_ds
WITH
(location = 's3://delta-warehouse/',
type = 'DELTA_LAKE',
storage_credential = delta_s3_cred,
options = (
'fs.s3a.endpoint' = 'https://s3.amazonaws.com',
'fs.s3a.path.style.access' = 'true',
'fs.s3a.region' = 'us-east-1'));
SELECT * FROM delta_ds(reference='sales_table') ORDER BY id;
11. JDBC - Remote Relational Database:
CREATE CREDENTIAL jdbc_cred
WITH
type = 'basic',
secret = '{ "USERNAME": "dbuser", "PASSWORD": "secret" }';
CREATE DATA SOURCE remote_db
WITH
(location = 'jdbc:ingres://dbhost:27832/mydb',
type = 'JDBC',
storage_credential = jdbc_cred);
SELECT a_float, an_int FROM remote_db(reference='orders_table');
12. ACTIAN_DATASTREAMS - Remote Analytics Engine Instance:
CREATE CREDENTIAL ae_cred
WITH
type = 'basic',
secret = '{ "USERNAME": "aeuser", "PASSWORD": "secret" }';
CREATE DATA SOURCE remote_ae
WITH
(location = 'jdbc:ingres://ae-host:27832/aedb',
type = 'ACTIAN_DATASTREAMS',
storage_credential = ae_cred);
SELECT * FROM remote_ae(reference='fact_sales');