CREATE DATA SOURCE
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
A data source is a named, reusable connection descriptor that defines how Analytics Engine locates and accesses external data through the embedded Spark Connector.
• Data source can be referenced in SQL queries using a table-function syntax to specify the file or table to access.
• Data source definitions are stored in the Analytics Engine catalog and persist across sessions. They are accessible to users with the appropriate privileges.
• Data sources complement the
CREATE EXTERNAL TABLE statement by separating connection configuration (such as location, credentials, and storage options) from the per-query specification of the external resource (file name or table name). This makes it easier to query multiple files or tables in the same storage back-end using a single shared descriptor, and to join data from different sources within a single SQL statement.
• When using objects from a data source, Analytics Engine requests the schema of the remote object from the Spark Provider. The default timeout that the Analytics Engine waits to receive any schema information from the Spark Provider is 5 minutes. You can configure this timeout in seconds through the "dtsrc_socket_timeout" configuration parameter in the config.dat file.
For example, the following configures timeout to 4 hours:
ii.localhost.config.dtsrc_socket_timeout: 14400
Note: Resolving the schema may take longer for text-based formats like CSV. Schema discovery can be disabled by setting “inferSchema = false” in the data source options. It indicates Spark not to infer data types of the columns of CSV or JSON files and assume all columns as strings.
This statement has the following format:
CREATE [OR REPLACE] DATA SOURCE ["]datasource_name["]
[WITH]
(type = { 'FILE' | 'ICEBERG_HADOOP' | 'ICEBERG_REST' | 'DELTA_LAKE' | 'JDBC' | 'ACTIAN_DATASTREAMS' },
location = 'path | URI | JDBC URL'
[, storage_credential = credential_name]
[, rest_credential = credential_name]
[, options = ('key' = 'value' [, ...])])
datasource_name
(Required) Specifies the name of the data source. If the name contains spaces or reserved words, enclose it in double quotes. Use the quoted form when referencing the data source in query table-function calls.
type
(Required, case-insensitive) Specifies the back-end type supported for the data source. Valid values are:
• FILE: Files stored on a local or network file system, a cloud object store (such as Amazon S3, GCS, Azure Blob Storage/ADLS, or similar), or an HTTP server. This is the default, if no other back-end type is specified.
Supported file formats include CSV, Parquet, JSON, ORC, Avro, and any other format recognized by Spark.
Note: For text-based sources such as CSV, JSON, and similar, string values have a fixed length of 4096. For JSON files, Apache Spark reads all integer values as 64-bit integers (int8/long).
Note: Spark does not automatically convert string values to date type when processing JSON files. Configure the data source definition as follows to read the DATE column as TIMESTAMP WITH TIME ZONE:
options = ( 'inferTimestamp' = 'true', 'timestampFormat' = 'yyyy-MM-dd')
• ICEBERG_HADOOP: Apache Iceberg tables accessed through a Hadoop-based catalog.
• ICEBERG_REST: Apache Iceberg tables accessed through a REST catalog, such as AWS S3 Tables, Databricks Unity Catalog, Nessie, Polaris, and similar.
• DELTA_LAKE: Delta Lake tables stored on Cloud object storage.
• JDBC: A table or view in a remote relational database accessible through JDBC.
• ACTIAN_DATASTREAMS: A table in a remote Analytics Engine instance.
Note: When multiple parallel data source queries using a ACTIAN_DATASTREAMS type are executed concurrently, one or more Spark jobs may be delayed. If dependencies exist between concurrent Spark jobs, query execution may hang. This occurs because the Spark Connector uses all available CPU cores in the Spark Connector container when executing a single Spark job. To reduce this risk, set the “spark.default.parallelism” parameter in the spark_provider.conf file to a value lower than the number of CPU cores available to the Spark Connector container. For more information, see
Configuration - Spark 4.0.1 Documentation.
location
(Required) Specifies the base path, URI, or JDBC URL for the following back-end types:
• FILE - Root directory or bucket location
• ICEBERG_HADOOP - Warehouse root directory
• ICEBERG_REST - REST catalog endpoint URL
• DELTA_LAKE - Delta warehouse root directory
• JDBC and ACTIAN_DATASTREAMS - JDBC connection URL
storage_credential
(Optional) Specifies the name of a previously created CREDENTIAL object used to authenticate access to the storage system or remote database such as S3, GCS, Azure, JDBC, and similar.
rest_credential
(Optional) Specifies the name of a previously created CREDENTIAL object used to authenticate access to an Iceberg REST catalog. Specify this parameter only for ICEBERG_REST back-end type.
Note: The current version of Iceberg for Spark (1.10.1) supports reading of Google credentials from files only and does not support reading credentials from JSON or any other method. Therefore, currently you cannot access Iceberg REST catalogs hosted on GCS. A fix may be available in the next product version.
options
(Optional) Specifies a comma-separated list of 'key'='value' pairs that are passed directly to the Spark data source. Values include Cloud storage options such as endpoint overrides, path-style access, region settings, and similar values, which are passed directly to the Spark Hadoop configuration.
Note: Analytics Engine does not validate the 'key'='value' pair. Incorrect or incompatible options can cause query failures.
For Iceberg and Delta catalog configurations, you can specify keys as:
“spark.sql.catalog.<datasource_name>.<key>”
or
“<datasource_name>.<key>” - In this case, the Spark Connector automatically adds the “spark.sql.catalog.” prefix.
For example, both “spark.sql.catalog.ice_rest.s3.endpoint” and “ice_rest.s3.endpoint” are valid keys for an ICEBERG_REST data source named “ice_rest”.