CREATE DATA SOURCE - Kinetica Docs

Creates a new data source, which contains the location and connection information for a data store that is external to Kinetica. A data source serves as an authentication & access mechanism to a remote resource and can optionally be used as a streaming source as well.

CREATE DATA SOURCE Syntax

CREATE [OR REPLACE] [EXTERNAL] DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = '<provider>[://[<host>[:<port>]]]'
[USER = '<username>']
[PASSWORD = '<password>']
[WITH OPTIONS (<option name> = '<option value>'[,...])]

A data source can also supply the object store connection used by a data lake catalog, in which case the individual data files are identified by the catalog rather than named in the referencing statement.

A data source does not reference specific data files within the source; file references (if applicable) can be made by using the data source in a CREATE EXTERNAL TABLE call (for creating a persistent view of the file data) or a LOAD INTO call (for either a one-time load or a subscribed loading of data from the external source to a locally persisted table).

The data source will be validated upon creation, by default, and will fail to be created if an authorized connection cannot be established.

The following data source providers are supported:

Azure (Microsoft blob storage)
GCS (Google Cloud Storage)
HDFS (Apache Hadoop Distributed File System)
JDBC (Java Database Connectivity, using a user-supplied driver or one of the drivers on the supported list)
Kafka (streaming feed)
- Apache
- Confluent
S3 (Amazon S3 Bucket)

Azure anonymous data sources are only supported when both the container and the contained objects allow anonymous access.
HDFS systems with wire encryption are not supported.
Confluent & Kafka data sources require an associated credential for authentication.

For provider-specific syntax, see Provider-Specific Syntax. For provider-specific examples, see Creating Data Sources.

Parameters

OR REPLACE

Any existing data source with the same name will be dropped before creating this one

EXTERNAL

Optional keyword for clarity

Name of the schema that will contain the created data source; if no schema is specified, the data source will be created in the user’s default schema

Name of the data source to create; must adhere to the supported naming criteria

Provider of the data sourceSupported providers include:

Provider	Description
`AZURE`	Microsoft Azure blob storage
`CONFLUENT`	Confluent Kafka streaming feed The `LOCATION` can be a comma-delimited list of Kafka URLs, to be used for high-availability; only one of which will be streamed from at any given time.
`GCS`	Google Cloud Storage
`HDFS`	Apache Hadoop Distributed File System
`JDBC`	JDBC connection, where `LOCATION` is the JDBC URL. `LOCATION = ‘jdbc:postgresql://example.com:5432/mydb’` See the supported list for the full list of supported drivers, or specify one with `JDBC_DRIVER_JAR_PATH` and `JDBC_DRIVER_CLASS_NAME`.
`KAFKA`	Apache Kafka streaming feed The `LOCATION` can be a comma-delimited list of Kafka URLs, to be used for high-availability; only one of which will be streamed from at any given time.
`S3`	Amazon S3 bucket

<host>

Host to use to connect to the data source; the following providers have a default host, if host is left blank:

Provider	Default Host
Azure	`<storage_account_name>.blob.core.windows.net`
GCS	`storage.googleapis.com`
S3	`<region>.amazonaws.com`

<port>

Port, for HDFS or Kafka, to use to connect to the data source

USER

Optional user name, given in <username>, to use for authenticating to the data source

PASSWORD

Optional password, given in <password>, to use for authenticating to the data source

WITH OPTIONS

Optional indicator that a comma-delimited list of connection option/value assignments will follow. See Options for the full list of options.

Examples

To create a data source, kin_ds, that connects to an Amazon S3 bucket, kinetica_ds, in the US East (N. Virginia) region:

CREATE DATA SOURCE Example

CREATE DATA SOURCE kin_ds
LOCATION = 'S3'
USER = '<aws access id>'
PASSWORD = '<aws access key>'
WITH OPTIONS
(
    BUCKET NAME = 'kinetica-ds',
    REGION = 'us-east-1'
)

Options

CREDENTIAL

Provider: AnyCredential object to use to authenticate to the remote system

VALIDATE

Provider: AnyWhether to test the connection to the data source upon creation; if TRUE (default), the creation of a data source that cannot be connected to will fail; if FALSE, the data source will be created regardless of connectivity

WAIT TIMEOUT

Provider: Any except JDBCTimeout in seconds for reading from the storage provider

USE_MANAGED_CREDENTIALS

Provider: AZURE, GCS, S3Whether to connect to the storage provider with provider-managed credentials

Provider	Notes
`AZURE`	Use the Azure Instance Metadata Service (IMDS) endpoint local to the Kinetica cluster to acquire credentials (only for on-prem clusters deployed within Azure)
`GCS`	Satisfy the Application Default Credentials requirements for credentials, making sure any supporting server-side configuration (files, environment variables) is done on every node in the cluster (only for on-prem clusters deployed within GCS)
`S3`	Use the AWS Default Credential Provider Chain to acquire credentials, making sure any supporting server-side configuration (files, environment variables) is done on every node in the cluster (only for on-prem clusters deployed within S3)

CONNECTION TIMEOUT

Provider: HDFS, S3Timeout in seconds for connecting to a given storage provider

CONTAINER NAME

Provider: AZUREAzure storage container name

SAS TOKEN

Provider: AZUREAzure storage account shared access signature token; this should be an account-level access token, not a container-level one

STORAGE ACCOUNT NAME

Provider: AZUREAzure storage account name (only used if TENANT ID is specified)

TENANT ID

Provider: AZUREAzure Active Directory tenant identifier

GCS_BUCKET_NAME

Provider: GCSName of the GCS bucket to use as the data source

GCS_PROJECT_ID

Provider: GCSName of the Google Cloud project to use for request billing

GCS_SERVICE_ACCOUNT_KEYS

Provider: GCSText of the JSON key file containing the GCS private key

DELEGATION TOKEN

Provider: HDFSOptional Kerberos delegation token for worker nodes; if not specified, the token will be acquired from HDFS

KERBEROS KEYTAB

Provider: HDFSLocation of the Kerberos keytab file in KiFS

USE KERBEROS

Provider: HDFSWhether to attempt Kerberos authentication to HDFS

JDBC_DRIVER_CLASS_NAME

Provider: JDBCJDBC driver class name (optional, if the name is available in the JAR file’s manifest)

JDBC_DRIVER_JAR_PATH

Provider: JDBCKiFS path of the JDBC driver JAR file to use

KAFKA_TOPIC_NAME

Provider: KAFKA, CONFLUENTKafka topic to access

SCHEMA_REGISTRY_CREDENTIAL

Provider: KAFKA, CONFLUENTCredential object to use to authenticate to the Confluent Schema Registry

SCHEMA_REGISTRY_LOCATION

Provider: KAFKA, CONFLUENTLocation of the Confluent Schema Registry in format:

[storage_path[:storage_port]]

SCHEMA_REGISTRY_PORT

Provider: KAFKA, CONFLUENTPort of the Confluent Schema Registry

BUCKET NAME

Provider: S3Amazon S3 bucket name

REGION

Provider: S3Amazon S3 region identifier

S3_AWS_ROLE_ARN

Provider: S3Amazon Resource Name (ARN) specifying the role

S3_ENCRYPTION_CUSTOMER_ALGORITHM

Provider: S3Algorithm used to encrypt/decrypt data

S3_ENCRYPTION_CUSTOMER_KEY

Provider: S3Key used to encrypt/decrypt data

S3_USE_VIRTUAL_ADDRESSING

Provider: S3Whether to use virtual addressing when referencing the S3 sink

Value	Description
`true`	The requests URI should be specified in virtual-hosted-style format where the bucket name is part of the domain name in the URL
`false`	Use path-style URI for requests

S3_VERIFY_SSL

Provider: S3Whether to verify SSL connections

Value	Description
`true`	Verify SSL connections (default)
`false`	Don’t verify SSL connections; for testing purposes, bypassing TLS errors, self-signed certificates, etc.

Provider-Specific Syntax

Several authentication schemes across multiple providers are supported.

Azure

Syntax below, examples here.

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'AZURE[://<host>]'
WITH OPTIONS
(
    CREDENTIAL = '[<credential schema name>.]<credential name>',
    CONTAINER NAME = '<azure container name>'
)

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'AZURE[://<host>]'
USER = '<azure storage account name>'
WITH OPTIONS (CONTAINER NAME = '<azure container name>')

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'AZURE[://<host>]'
[USER = '<ad client id>']
WITH OPTIONS
(
    USE_MANAGED_CREDENTIALS = true,
    STORAGE ACCOUNT NAME = '<azure storage account name>',
    CONTAINER NAME = '<azure container name>',
    TENANT ID = '<ad tenant id>'
)

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'AZURE[://<host>]'
USER = '<azure storage account name>'
PASSWORD = '<azure storage account key>'
WITH OPTIONS (CONTAINER NAME = '<azure container name>')

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'AZURE[://<host>]'
USER = '<azure storage account name>'
WITH OPTIONS
(
    SAS TOKEN = '<sas token>',
    CONTAINER NAME = '<azure container name>'
)

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'AZURE[://<host>]'
USER = '<ad client id>'
PASSWORD = '<ad client secret key>'
WITH OPTIONS
(
    STORAGE ACCOUNT NAME = '<azure storage account name>',
    CONTAINER NAME = '<azure container name>',
    TENANT ID = '<ad tenant id>'
)

GCS

Syntax below, examples here.

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'GCS[://<host>]'
WITH OPTIONS
(
	CREDENTIAL = '[<credential schema name>.]<credential name>',
	[GCS_PROJECT_ID = '<gcs project id>',]
	GCS_BUCKET_NAME = '<gcs bucket name>'
)

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'GCS[://<host>]'
WITH OPTIONS
(
	USE_MANAGED_CREDENTIALS = true,
	[GCS_PROJECT_ID = '<gcs project id>',]
	GCS_BUCKET_NAME = '<gcs bucket name>'
)

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'GCS[://<host>]'
WITH OPTIONS
(
	[GCS_PROJECT_ID = '<gcs project id>',]
	GCS_BUCKET_NAME = '<gcs bucket name>'
)

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'GCS[://<host>]'
USER = '<gcs account id>'
PASSWORD = '<gcs account private key>'
WITH OPTIONS
(
	[GCS_PROJECT_ID = '<gcs project id>',]
	GCS_BUCKET_NAME = '<gcs bucket name>'
)

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'GCS[://<host>]'
WITH OPTIONS
(
	GCS_SERVICE_ACCOUNT_KEYS = '<gcs account json key text>',
	[GCS_PROJECT_ID = '<gcs project id>',]
	GCS_BUCKET_NAME = '<gcs bucket name>'
)

HDFS

Syntax below, examples here.

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'HDFS://<host>:<port>'
WITH OPTIONS (CREDENTIAL = '[<credential schema name>.]<credential name>')

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'HDFS://<host>:<port>'
USER = '<hdfs username>'
PASSWORD = '<hdfs password>'

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'HDFS://<host>:<port>'
USER = '<hdfs username>'
WITH OPTIONS (KERBEROS KEYTAB = 'kifs://<keytab file path>')

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'HDFS://<host>:<port>'
USER = '<hdfs username>'
WITH OPTIONS (USE KERBEROS = true)

JDBC

Syntax below, examples here.

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = '<jdbc url>'
WITH OPTIONS
(
	CREDENTIAL = '[<credential schema name>.]<credential name>',
	JDBC_DRIVER_CLASS_NAME = '<jdbc driver class full path>',
	JDBC_DRIVER_JAR_PATH = 'kifs://<jdbc driver jar path>'
)

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = '<jdbc url>'
USER = '<jdbc username>'
PASSWORD = '<jdbc password>'
WITH OPTIONS
(
	JDBC_DRIVER_CLASS_NAME = '<jdbc driver class full path>',
	JDBC_DRIVER_JAR_PATH = 'kifs://<jdbc driver jar path>'
)

Kafka (Apache)

Syntax below, examples here.

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'KAFKA://<host>:<port>'
WITH OPTIONS
(
    CREDENTIAL = '[<credential schema name>.]<credential name>',
    KAFKA_TOPIC_NAME = '<kafka topic name>'
)

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'KAFKA://<host>:<port>'
WITH OPTIONS
(
    CREDENTIAL = '[<credential schema name>.]<credential name>',
    KAFKA_TOPIC_NAME = '<kafka topic name>',
    SCHEMA_REGISTRY_CREDENTIAL = '[<sr credential schema name>.]<sr credential name>',
    SCHEMA_REGISTRY_LOCATION = '<schema registry url>'
)

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'KAFKA://<host>:<port>'
WITH OPTIONS (KAFKA_TOPIC_NAME = '<kafka topic name>')

Kafka (Confluent)

Syntax below, examples here.

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'CONFLUENT://<host>:<port>'
WITH OPTIONS
(
    CREDENTIAL = '[<credential schema name>.]<credential name>',
    KAFKA_TOPIC_NAME = '<kafka topic name>'
)

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'CONFLUENT://<host>:<port>'
WITH OPTIONS
(
    CREDENTIAL = '[<credential schema name>.]<credential name>',
    KAFKA_TOPIC_NAME = '<kafka topic name>',
    SCHEMA_REGISTRY_CREDENTIAL = '[<sr credential schema name>.]<sr credential name>',
    SCHEMA_REGISTRY_LOCATION = '<schema registry url>'
)

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'CONFLUENT://<host>:<port>'
WITH OPTIONS (KAFKA_TOPIC_NAME = '<kafka topic name>')

S3

Syntax below, examples here.

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'S3[://<host>]'
WITH OPTIONS
(
    CREDENTIAL = '[<credential schema name>.]<credential name>',
    BUCKET NAME = '<aws bucket name>',
    REGION = '<aws region>'
)

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'S3[://<host>]'
WITH OPTIONS
(
    BUCKET NAME = '<aws bucket name>',
    REGION = '<aws region>'
)

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'S3[://<host>]'
WITH OPTIONS
(
    USE_MANAGED_CREDENTIALS = true,
    BUCKET NAME = '<aws bucket name>',
    REGION = '<aws region>'
)

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'S3[://<host>]'
USER = '<aws access key id>'
PASSWORD = '<aws secret access key>'
WITH OPTIONS
(
    BUCKET NAME = '<aws bucket name>',
    REGION = '<aws region>'
)

CREATE DATA SOURCE [<data source schema name>.]<data source name>
LOCATION = 'S3[://<host>]'
USER = '<aws access key id>'
PASSWORD = '<aws secret access key>'
WITH OPTIONS
(
    S3_AWS_ROLE_ARN = '<aws iam role arn>',
    BUCKET NAME = '<aws bucket name>',
    REGION = '<aws region>'
)

​Parameters

​Examples

​Options

​Provider-Specific Syntax

​Azure

​GCS

​HDFS

​JDBC

​Kafka (Apache)

​Kafka (Confluent)

​S3

Parameters

Examples

Options

Provider-Specific Syntax

Azure

GCS

HDFS

JDBC

Kafka (Apache)

Kafka (Confluent)

S3