Skip to content

How-To set up an SQL Data Source#

Introduction#

The SQL Data Source connects Hopsworks to a Relational Database Service. Supported database types are MySQL, PostgreSQL, Oracle, and Microsoft SQL Server (including Azure SQL Database). Using this connector, you can query and update data in your relational database from Hopsworks.

In this guide, you will configure a Data Source in Hopsworks to securely store the authentication information needed to set up a connection to your database instance. When you're finished, you'll be able to query your SQL database using Hopsworks APIs.

Note

This guide creates the data source in the Hopsworks UI. The hops CLI creates the same data source with hops datasource create sql <name> --database-type <type> ....

Prerequisites#

Before you begin, ensure you have the following information from your database instance:

  • Host: The endpoint for your database instance.

    Example from AWS: 1. Go to the AWS Console → Aurora and RDS 2. Click on your DB instance. 3. Under Connectivity & security, you'll find the endpoint, e.g.: mydb.abcdefg1234.us-west-2.rds.amazonaws.com

  • Database: The name of the database to connect to. For Oracle, this is the service name (e.g. ORCL or a TNS alias). For SQL Server, this is the database the login connects to by default; every database the login can access can still be browsed.

  • Port: The port to connect to (e.g. 3306 for MySQL, 5432 for PostgreSQL, 1521 for Oracle, 1433 for SQL Server).

  • Username and Password: A username and password with the necessary permissions to access the required tables.

Optional: Oracle Wallet for mTLS Authentication#

If your Oracle database requires mutual TLS (mTLS) authentication, which is common with Oracle Autonomous Database and Oracle Cloud, you will also need:

  • Wallet file: A .zip file containing the wallet credentials (e.g. cwallet.sso, tnsnames.ora, sqlnet.ora).
  • Wallet password: The password for the wallet, if using a PKCS12 wallet (ewallet.p12). Auto-login wallets (cwallet.sso) do not require a password.

Tip

You can download the wallet zip from the Oracle Cloud Console under your Autonomous Database's DB Connection page. Upload the zip file to your Hopsworks project (e.g. to Resources/) before creating the data source.

Leave the host empty when using a wallet

A host and a wallet are alternatives, not a pair. The wallet's tnsnames.ora supplies the host and port, and the database field is the alias to look up there. Supplying a host as well makes the driver connect directly, past the wallet, which a wallet-protected database refuses with a connection error that names neither the cause nor the fix. Hopsworks therefore refuses to save a data source with both a host and a wallet.

Creation in the UI#

Step 1: Set up a new Data Source#

Head to the Data Sources view on Hopsworks (1) and click New data source (2). The Add a data source catalog lists the available sources, grouped under Object storage, Data warehouse, Database, Streaming and API & SaaS. Pick your database's card under Database, such as MySQL, PostgreSQL, Oracle or SQL Server, to open the creation form.

Data Source Creation

The Data Source View in the User Interface

Step 2: Enter SQL Settings#

Enter the details for your database. Start by giving the connector a name and an optional description.

  1. The form opens for the database whose card you picked in the gallery (MySQL, PostgreSQL, Oracle, SQL Server and the others), which sets the database type. Click Change source to pick a different one.
  2. Enter the host endpoint. Leave it empty when using an Oracle wallet: the wallet supplies the connection details, and the database field names the TNS alias to use.
  3. Enter the database name (service name for Oracle).
  4. Specify the port.
  5. For Oracle, choose under Credentials whether they are Stored with the data source (the default) or Provided by each user. With Provided by each user the username, password and wallet fields disappear and every member adds their own later, see provided credentials.
  6. For Stored with the data source, provide the username and password.
  7. For Stored with the data source on a database behind mTLS, upload the wallet zip file and provide the wallet password (if required).
  8. Click on "Save Credentials".

SQL Connector Creation

SQL Connector Creation Form

Oracle-Specific Notes#

The generic read, external feature group, and training data workflows are covered in the usage guide for data sources. The following notes apply only to Oracle.

JDBC driver on the Spark classpath#

The Oracle JDBC driver JAR (e.g. ojdbc11.jar) must be available on the Spark classpath. Upload it via the Jupyter configuration or Job configuration in Additional Jars. The MySQL, PostgreSQL and SQL Server drivers are included in Hopsworks by default.

Spark JDBC limitations#

Oracle Spark JDBC limitations

  • Single-partition reads only. All data is fetched through a single JDBC connection from the Spark driver. Spark's parallel JDBC read (via numPartitions / partitionColumn) is not supported. For very large tables, filter with a WHERE clause in your query.
  • Wallet available on the driver only. When using wallet-based authentication, the wallet zip is downloaded from HopsFS and extracted on the Spark driver node. This is sufficient because reads are single-partition (driver-only).
  • Timestamp precision. Spark JDBC supports timestamp precision up to seconds only. Sub-second precision from Oracle TIMESTAMP columns may be truncated.

Python engine#

The Python engine reads Oracle via the Hopsworks Arrow Flight service, which handles the database connection server-side. No JDBC driver or wallet files are needed on the client, and the Spark JDBC limitations above do not apply.

SQL Server-Specific Notes#

Host and port#

The Host is a host name or an IPv4 address. For a named instance, enter the server's host name and the port the instance listens on, not host\instance: the JDBC driver would connect to the port and the Python engine to the instance, which can be different servers. For an IPv6-only server, use a host name that resolves to it, because the JDBC driver does not accept an IPv6 address in its URL.

Databases, schemas and tables#

SQL Server names a table database.schema.table. When you browse a SQL Server data source, the database is the top level and the schema (often dbo) is the group, so an external feature group over a browsed table reads [database].[schema].[table]. A hand-written query can use the same three-part name, which lets one data source read any database on the server that the login can access. Write such a query as a plain SELECT: Hopsworks reads it as a derived table, where SQL Server does not accept a WITH clause.

Azure SQL Database does not support three-part names across databases. Create one data source per Azure SQL database, with that database in the Database field.

Encryption and certificates#

Every connection is encrypted, and the server certificate is validated against the public certificate authorities, including its host name. For a server with a self-signed certificate, such as a default SQL Server installation, add the argument trustServerCertificate with the value true. This applies to Spark, the query engine, the Python engine and DLTHub ingestion alike. Spark and the query engine use Microsoft's JDBC driver, so other JDBC connection properties can be added as arguments the same way; the Python engine and DLTHub ingestion read only trustServerCertificate. The argument encrypt=false therefore turns off encryption for Spark and the query engine only: the Python engine and DLTHub ingestion always encrypt. The arguments cannot replace the host, port or database (serverName, portNumber, databaseName); the data source's own fields set those.

FILESTREAM and binary columns as files#

An ingestion job can copy the values of a SQL Server table's large binary columns into files in the project, instead of storing the bytes in the feature group. Every FILESTREAM column is stored as files. A varbinary(max) or image column is stored as files when you select Store as files for it in the ingestion wizard, or list it in file_columns.

For each such column, the feature group holds two features:

  • The column's own feature, a string with the full path of the file, such as /Projects/sales/Resources/erp/Sales/dbo/Documents/c-Body/3f/v-42.
  • <column>_size, a bigint with the length of the value in bytes.

A NULL value gives a NULL path and a NULL size, and an empty value gives an empty file and the size 0. The preview in the wizard shows the size of each value instead of its bytes. In the SDK, get_data(file_columns=[...]) returns the features as an ingestion job stores them; pass file_columns=[] for a table whose only file columns are FILESTREAM columns. Without file_columns, get_data returns the source schema, which is what an external feature group uses. data.column_metadata lists each source column's type and whether it is, or can be, stored as files.

A feature group created before this feature, with a binary feature for the column, keeps copying the bytes into the feature group. To store the values as files instead, create a new feature group with the path and size features.

from hopsworks_common.core.sink_job_configuration import SinkJobConfiguration

documents = fs.get_data_source("erp").get_tables("Sales")[0]
data = documents.get_data(file_columns=["Body"])

fg = fs.get_or_create_feature_group(
    name="documents",
    version=1,
    primary_key=["id"],
    features=data.features,
    data_source=documents,
    sink_enabled=True,
    sink_job_conf=SinkJobConfiguration(
        write_mode="MERGE",
        file_columns=["Body"],
        files_directory="Resources/documents",
    ),
)
fg.save()

The files are written under the files directory, by default Resources/<data source>/<database>/<schema>/<table>. It must be a directory inside a dataset, outside Resources/jobs and the ingestion state under Resources/dlthub-warehouse, and it cannot overlap the files directory of another table or job. Each file is named after the row's primary key, under one directory per column and one of 256 subdirectories per column. The directory also holds .hopsworks-files.json, which names the job and feature group it belongs to, and .hopsworks-files.lease, which keeps two runs from writing it at the same time. A job refuses a directory that belongs to another job or feature group, or that holds files but no .hopsworks-files.json.

The feature group needs a primary key, since it names the files. It must be written by one ingestion job only: a job with file columns cannot share its feature group with another ingestion job. A file column keeps its path and size features while it keeps its files directory; to store it under other features, choose another files directory. Its columns must be non-NULL and include every column of the table's primary key or of one of its unique indexes. A date and time key is matched to the microsecond, so two rows whose keys differ by less than a microsecond fail the run. A value can be at most 2147483647 bytes.

A run writes each file before it loads the row, and replaces a file that already exists, so a row always points at a complete file. Incremental ingestion needs the MERGE write mode, and picks up a changed value only with a cursor column that changes when the row is updated, such as a datetime2 updated-at column. After a run succeeds, it deletes the files that no row of the feature group references, such as the files of deleted rows on a full load and of values that became NULL. A failed run deletes nothing, and the next successful run deletes what it left. A run that fails after writing files but before loading their rows leaves files that no row references yet, so a file in the files directory is not proof that its row was loaded: read the paths from the feature group. Earlier versions of the feature group can point at files that a later run replaced or deleted, so reading a file column at an earlier commit is not supported. Write the path features only through the ingestion job: a path written by another job or by an insert can point at a file that the next run deletes.

Deleting the feature group, the job or the data source leaves the files in place. To remove them, delete the files directory in the file browser, or with project.get_dataset_api().remove("Resources/documents").

Provided credentials for Oracle#

A data source has a credentials mode. The Data owner chooses it when creating the data source, and it cannot be changed afterwards, because a switch would invalidate every member's setup at once.

Mode Who enters the credentials Who reads with them
Stored with the data source (SHARED, the default) The Data owner, once, in the data source form Every project member
Provided by each user (PROVIDED) Each project member, for themselves That member only

PROVIDED is available for the Oracle database type of the SQL data source. A provided data source stores the host or the wallet alias, the port and the service name, and nothing else. Creating one with a username, a password or a wallet is refused with INVALID_CREDENTIALS_MODE_ARGS (270339). The Python client raises the error itself, before sending the request, when a new provided SqlConnector carries any of them; each member adds credentials with set_credentials() once the data source exists. Reads run with the credentials of the member who makes them, so a Data owner who creates a provided data source adds their own credentials before browsing its tables or mounting feature groups; the UI opens the dialog for them right after the data source is saved.

hops datasource create sql oracle_sales --database-type ORACLE --host db.example.com --port 1521 --database ORCL --credentials-mode provided

Adding your credentials#

Each member adds their credentials once per data source. Data owners and Data scientists can add credentials; Feature store restricted members and Observers cannot. For a data source in a feature store shared with your project, you add them in the project that owns the data source, see feature groups from a shared feature store. Hopsworks tests them by opening a connection and running SELECT 1 FROM DUAL, and saves nothing when the database rejects them. Validate and save are rate limited to 5 calls per minute per user and data source; a call over the limit returns HTTP 429 with DATA_SOURCE_CREDENTIALS_RATE_LIMITED (270343).

In the UI, open Data Sources, find the data source in the list and pick My credentials from its row actions. The Credentials column of the list shows Shared, or Provided with Yours: set, Yours: missing or Yours: incomplete. Yours: incomplete means your binding references an account entry that no longer exists, see where your credentials are stored. In the dialog, type a username and a password, or pick an env var and a secret you already keep in your account. For a database behind mTLS, upload the wallet zip and give its password. The upload is staged as a new file, see replacing a wallet. Click Validate, then Save. Save stays disabled until Validate has passed for the values in the dialog, and editing a field disables it again. Remove my credentials in the same dialog deletes your binding, your wallet file and the account entries saved under their default names.

From Python, the same operations are methods on the SQL connector:

import hopsworks


project = hopsworks.login()
fs = project.get_feature_store()
sc = fs.get_data_source("oracle_sales").storage_connector

if sc.validate_credentials(user="SCOTT", password="..."):
    sc.set_credentials(user="SCOTT", password="...")

# a database behind mTLS also takes a wallet, uploaded to a new file in your home
wallet = f"{project.home_path}/.datasources/oracle_sales/wallet-7f3a9c.zip"
sc.set_credentials(
    user="SCOTT", password="...", wallet_path=wallet, wallet_password="..."
)

# reuse a secret you already keep in your account
sc.set_credentials(user="SCOTT", password_secret="my_oracle_pwd")

sc.get_credentials()
sc.delete_credentials()

set_credentials raises a FeatureStoreException carrying the database's own error when the credentials are rejected. get_credentials returns the names of the account entries your binding references, never their values.

From the CLI, hops datasource credentials has set, validate, show and delete. --password - and --wallet-password - read the value from stdin, one of them per call, and without either flag the HOPSWORKS_DS_CREDENTIALS_PASSWORD and HOPSWORKS_DS_CREDENTIALS_WALLET_PASSWORD environment variables are read. --wallet takes a local zip and stages it as a new wallet file for you.

HOPSWORKS_DS_CREDENTIALS_WALLET_PASSWORD=... hops datasource credentials set oracle_sales --user SCOTT --password - --wallet ./Wallet_ORCL.zip
hops datasource credentials show oracle_sales --json
{
  "status": "VALID",
  "username_env_var": "DS_ORACLE_SALES_67_USER",
  "password_secret_name": "ds_oracle_sales_67_password",
  "wallet_path": "/Projects/sales/Users/scott/.datasources/oracle_sales/wallet-7f3a9c.zip",
  "wallet_password_secret_name": "ds_oracle_sales_67_wallet_password",
  "validated_at": "2026-10-07T09:12:44Z"
}

hops datasource list shows the mode in its CREDENTIALS column: shared, provided: yours set, provided: missing or provided: incomplete.

Where your credentials are stored#

Credential Stored as Default name
Username Env var in your account, private DS_<NAME>_<feature store id>_USER
Password Secret in your account, private ds_<name>_<feature store id>_password
Wallet zip File in your home directory in the project /Projects/<project>/Users/<username>/.datasources/<name>/wallet-<random>.zip
Wallet password Secret in your account, private ds_<name>_<feature store id>_wallet_password

<NAME> is the data source name upper-cased with every character outside A-Z, 0-9 and _ replaced by _; <name> is the same in lower case. The feature store id is part of the default names because your account entries are not scoped to a project, so two data sources with the same name in different feature stores would otherwise share one entry. The names are editable in the dialog and through the user_env_var, password_secret and wallet_password_secret arguments, so an existing entry can be reused instead of creating one. Your home directory is readable by you alone, so the wallet is too.

The data source keeps a reference to these entries, not a copy. Fetching or listing a provided data source returns the status of your binding and the names of your env var and secrets, never your username or password. The Python client reads the values from your own account entries when it needs them. Deleting the secret or the env var from your account leaves the binding INCOMPLETE, and the data source refuses to read until you set your credentials again. Your Trino catalogs on the data source keep reading with the login already in their published file, so remove your credentials to stop them using it. Removing your credentials deletes the binding, the wallet file, the env var and secrets that carry the default names above, and your Trino catalogs on the data source. The same deletion happens for a member who is removed from the project, and for every member when the data source is deleted. Entries saved under names of your own stay in your account, since you may use them elsewhere.

Replacing a wallet#

Every wallet upload, from the dialog or with hops datasource credentials set --wallet, is staged as a new file, <home>/.datasources/<name>/wallet-<random>.zip, so it never overwrites the wallet your binding points at. A wallet that fails validation, or a save the database rejects, leaves your binding and its wallet unchanged. When the save succeeds, the binding points at the new file and Hopsworks deletes the wallet it pointed at before. From Python, upload the wallet under a name of your own that is not in use, and pass that path as wallet_path.

Runtime variables#

Every runtime you start in the project (a job, a Jupyter server, a terminal, an app or a Ray cluster) receives your credentials for each provided data source you have set them for. A job run receives them through a Kubernetes Secret created for that run, so the values are not in the pod spec that other project members can read. Code inside the runtime reads the same variables either way:

Variable Value
HOPS_DS_<NAME>_CONNECTOR_ID The id of the data source these variables belong to
HOPS_DS_<NAME>_USER Your username
HOPS_DS_<NAME>_PASSWORD Your password
HOPS_DS_<NAME>_WALLET_PATH /hopsfs/Users/<username>/.datasources/<name>/wallet-<random>.zip, when you set a wallet
HOPS_DS_<NAME>_WALLET_PASSWORD Your wallet password, when you set one
import os


user = os.environ["HOPS_DS_ORACLE_SALES_USER"]
password = os.environ["HOPS_DS_ORACLE_SALES_PASSWORD"]
wallet = os.environ.get("HOPS_DS_ORACLE_SALES_WALLET_PATH")

Scheduled jobs run with the creator's credentials

A scheduled run starts as the job's creator and receives the creator's provided credentials. Anyone who can edit the job, its configuration or its app file, can make the next scheduled run execute their code with the creator's database login. Keep a job that reads a provided data source editable only by its owner.

Model deployments do not receive these variables. A deployment is shared by the project and serves every caller with the same environment, so one member's credentials would answer everyone's requests.

The HOPS_ prefix is reserved for the platform, so an account env var cannot shadow these. Two provided data sources in one feature store cannot map to the same <NAME>; the second one is refused at creation. The Hopsworks client reads these variables itself inside a runtime, so a read() through the data source needs no further configuration there. It uses them only for the data source whose id is in HOPS_DS_<NAME>_CONNECTOR_ID. A data source with the same name in another feature store, such as one shared with the project, maps to the same variable names, so for it the client reads your values from your own account entries.

The /hopsfs path in HOPS_DS_<NAME>_WALLET_PATH is not mounted in Spark. When that path does not exist, the client downloads your wallet from HopsFS to the Spark driver instead, so a Spark read needs no further configuration either.

Access to mounted tables#

Only Data owners mount external feature groups, and a Data owner's database user may read tables that another member's may not. Hopsworks therefore keeps, per member and per external feature group on a provided data source, whether that member can read the backing table or query:

State Meaning
PENDING A check is queued and usually runs within a few minutes
OK Your credentials read the backing table
NO_CREDENTIALS You have not set complete credentials for the data source
INVALID_CREDENTIALS The database rejected the login, for example ORA-01017 or ORA-28000
NO_ACCESS The login works but the table or query cannot be read: ORA-00942, which Oracle also returns for a missing privilege, or ORA-01031
ERROR Anything else, such as a network or TNS error or a timeout; the message is kept

A check runs on every feature group of the data source in these cases:

  • For you, when you set or remove your credentials.
  • For every member with credentials, when a Data owner mounts a new feature group.
  • For a member who joins the project.
  • Every 10 minutes, in a reconcile pass that matches the stored states with the current members and credentials and queues a check for any event that was missed.

Checks run in the background in batches, and each one may take up to a minute, so a state usually settles within a few minutes. The state is shown in the feature group catalog and in the feature group's Data Source submenu. It informs you and does not gate reads: a read in NO_ACCESS still goes to Oracle and fails there with the database error.

Test Connection#

Test Connection, on the Data Source submenu of a feature group and on greyed-out feature groups in the catalog, runs the check for you now, as you, with a limit of one row, and stores the result. It takes up to a minute. Use it after a privilege was granted on the database side, since Hopsworks learns about grants only through the events above and this button. On a feature group whose data source has shared credentials it tests the shared credentials and stores nothing.

Trino catalogs#

A provided Oracle data source can back a Trino catalog that reads it as the member who creates it. Such a catalog is always a private catalog of that member, named _<username>__<name> and available in every project they belong to. Each member who wants to query the data source through Trino creates their own, as a Data Owner or a Data Scientist. A request for a project catalog from a provided data source is refused with CREDENTIALS_MODE_NOT_SUPPORTED (270337), and a catalog on a provided data source cannot be shared, so only its owner queries it.

The catalog's properties name the data source instead of holding a login:

user-credential-name=hops_ds_<data source id>_user
password-credential-name=hops_ds_<data source id>_password

When the catalog is published to the query engine, Hopsworks puts your username and password for the data source in their place. Every Trino client therefore reads the catalog as you, in any of your projects: the SQL runner, project.get_trino_api(), Superset, JDBC and the Trino CLI. The catalog cannot carry connection-user or connection-password of its own, since its login always comes from your credentials, and its connection URL stays the data source's, since your credentials are sent to it. A Superset dashboard you build on the catalog and share shows its viewers data read with your credentials.

The catalog follows your credentials:

When you The catalog
Save credentials Uses them once the query engine reloads its catalogs, as described in When the catalog goes live; until then it uses the ones saved before
Remove your credentials, are removed from the project, or the data source is deleted Is removed and refused at once, because it would otherwise keep reading with credentials you no longer hold; saving credentials again does not bring it back

When your database password changes, save the new one on the data source as well. The catalog keeps logging in with the saved password until then, and the database may lock your account after repeated failed logins. Editing or deleting the secret or the env var under Account Settings does not reach the catalog. The catalog changes only when its file is rewritten, that is when you save the credentials on the data source or edit the catalog. A query engine restart for another reason reloads it from its unchanged file. To stop the catalog using your login, remove your credentials from the data source.

Creating the catalog requires you to have set your own credentials for the data source. For a database behind mTLS, the catalog's wallet (TNS_ADMIN) is built from your wallet into a mountable secret of your account; it only secures the transport, and the catalog still logs in with your username and password. The wallet follows your credentials: it is rebuilt when you save a new wallet, and deleted when you remove your credentials, or when you delete your last catalog that uses it. Test connection on the catalog runs with your credentials. The catalog becomes queryable after the query engine restarts, as described in When the catalog goes live.

Next Steps#

Move on to the usage guide for data sources to see how you can use your newly created SQL connector. You can also make the database queryable from the query engine by adding a Trino catalog derived from this data source.