Build data connections¶

Databases are a widely used tool to carry valuable business data. To enable integration with a variety of enterprise databases, DataRobot provides a self-service JDBC product for database connectivity setup. Once configured, you can read data from production databases for model building and predictions. This allows you to quickly train and retrain models on that data, and avoids the unnecessary step of exporting data from your enterprise database to a CSV for ingest to DataRobot. With access to more diverse data, you can build more accurate models.

Database connection terminology¶

Database connection configuration uses the following terminology:

• Data store: A configured connection to a database. It has a name, a specified driver, and a JDBC URL. You can register data stores with DataRobot for ease of re-use. A data store has one connector but can have many data sources.
• Data source: A configured connection to the backing data store (the location of data within a given endpoint). A data source specifies, via a SQL query or a selected table and schema data, which data to extract from the data store to use for modeling or predictions. A data source has one data store and one connector but can have many datasets.
• Data driver: The software that allows the application to interact with a database; each data store is associated with either a driver or a connector (created by the administrator). The driver configuration saves the storage location in the application of the JAR file and any additional dependency files associated with the driver.
• Connector: Similarly to data drivers, a connector allows the application to interact with a database; each data store is associated with either a driver or a connector (created by the administrator). The connector configuration saves the storage location in the application of the JAR file and any additional dependency files associated with the connector.
• Dataset: Data, a file or the content of a data source, at a particular point in time. A data source can produce multiple datasets; a dataset has exactly one data source.

Review the workflow to set up projects or prediction datasets below.

1. An administrator sets up datarobot.DataDriver to access a particular database. For any particular driver, this setup is performed once for the entire system and the resulting driver is used by all users.
2. Users create a datarobot.DataStore which represents an interface to a particular database using that driver.
3. Users create a datarobot.DataSource representing a particular set of data to be extracted from the data store.
4. Users create projects and prediction datasets from a data source.

Users can manage their data stores and data sources, while administrators can manage drivers by listing, retrieving, updating, and deleting existing instances of them.

Create a driver¶

To create a driver, administrators must specify the following:

• class_name: The Java class name for the driver if the type is JDBC; otherwise None.
• canonical_name: A user-friendly name or resulting driver to display in the API and the GUI.
• files:A list of local files which contain the driver if the type is JDBC; otherwise omitted.
• typ: The enum for the type of driver. Defaults to dr.enums.DataDriverTypes.JDBC and can also be dr.enums.DataDriverTypes.DR_DATABASE_V1.
• database_driver: The type of native database to use for non-JDBC. For example, dr.enums.DrDatabaseV1Types.BIGQUERY.

>>> import datarobot as dr
>>> driver = dr.DataDriver.create(
...     class_name='org.postgresql.Driver',
...     canonical_name='PostgreSQL',
...     files=['/tmp/postgresql-42.2.2.jar']
... )
>>> driver
DataDriver('PostgreSQL')

Use the code below to create a non-JDBC driver:

driver = dr.DataDriver.create(None, "BigQuery Native", typ=dr.enums.DataDriverTypes.DR_DATABASE_V1, database_driver=dr.enums.DrDatabaseV1Types.BIGQUERY)

To retrieve information about existing drivers, such as the driver ID for data store creation, you can use dr.DataDriver.list().

Create a data store¶

After an administrator has created drivers, any user can use them to create a DataStore. A data store represents a JDBC database or a non-JDBC database. When creating them, you should specify the following:

• type: The type must be either dr.enums.DataStoreTypes.DR_DATABASE_V1 or dr.enums.DataStoreTypes.JDBC.
• canonical_name: A user-friendly name to display in the API and GUI for the data store.
• driver_id: The ID of the driver to use to connect to the database.
• jdbc_url: The full URL specifying the database connection settings such as the database type, server address, port, and database name if the type is JDBC.
• fields: The fields used if the type is dr.enums.DataStoreTypes.DR_DATABASE_V1. A list of dictionary entries, where each entry has an ID, a name, and a value field.

>>> import datarobot as dr
>>> data_store = dr.DataStore.create(
...     data_store_type='jdbc',
...     canonical_name='Demo DB',
...     driver_id='5a6af02eb15372000117c040',
...     jdbc_url='jdbc:postgresql://my.db.address.org:5432/perftest'
... )
>>> data_store
DataStore('Demo DB')
>>> data_store.test(username='username', password='password')
{'message': 'Connection successful'}

You can create a non-JDBC Data Store using fields instead:

>>> fields = [
...     {
...         "id": "bq.project_id",
...         "name": "Project Id",
...         "value": "mldata-358421",
...     }
... ]
>>> data_store = dr.DataStore.create(
...     data_store_type=dr.enums.DataStoreTypes.DR_DATABASE_V1,
...     canonical_name='BigQuery Native Connection',
...     driver_id=driver_id,
...     fields=fields
... )

View data stores¶

To view data stores that already exist, use the code below. However, note that not all data stores show up by default with a call to dr.DataStore.list(). You must explicitly pass a typ=dr.enums.DataStoreListTypes.ALL argument, since the default is to only show JDBC connections.

List all JDBC data stores (default):

data_stores = dr.DataStore.list()

print(f"Found {len(data_stores)} DataStore(s):")
for ds in data_stores:
    print(f"  - {ds.canonical_name} (ID: {ds.id}, Type: {ds.data_store_type})")

List all data stores:

all_stores = dr.DataStore.list(typ=dr.enums.DataStoreListTypes.ALL)

Query a data store¶

Once you have a datarobot.DataStore, you can run SQL directly against it. Use preview_query() for queries that return results, and execute_update() for update statements such as UPDATE, INSERT, and DELETE.

Use preview_query to execute a SQL statement and return a preview of the results:

>>> import datarobot as dr
>>> data_store = dr.DataStore.get('5a8ac90b09a57a0001be306e')
>>> preview = data_store.preview_query(
...     "SELECT * FROM my_catalog.my_schema.my_table WHERE name LIKE ?",
...     credential_id='9963d544d5ce3se783r12190',
...     max_rows=10,
...     bind_parameters=['%Doe%'],
... )
>>> preview.columns
['id', 'name', 'email']
>>> preview.records
[
    {'id': 1, 'name': 'John Doe', 'email': 'john.doe@example.com'},
    {'id': 2, 'name': 'Jane Doe', 'email': 'jane.doe@example.com'},
]

The bind_parameters argument binds values, in order, to the ? placeholders in the SQL statement.

Use execute_update to run an update statement against the data store:

>>> data_store.execute_update(
...     "UPDATE my_table SET name = ? WHERE id = ?",
...     credential_id='9963d544d5ce3se783r12190',
...     bind_parameters=['John', 1],
... )
'OK'

execute_update returns the 'OK' message from the server on success. To check the result programmatically, use DataStore.is_execute_update_success():

>>> message = data_store.execute_update(
...     "UPDATE my_table SET name = 'John Doe' WHERE id = 1"
... )
>>> dr.DataStore.is_execute_update_success(message)
True

Query using a JDBC connection¶

You can also run SQL against a JDBC URL directly, without creating a datarobot.DataStore first. Supply the JDBC URL and credentials directly to datarobot.JdbcPreview, and DataRobot connects to the database for the duration of the request. Use JdbcPreview.preview() for queries that return results, and JdbcPreview.execute_update() for update statements such as UPDATE, INSERT, and DELETE.

Use preview() to execute a SQL statement against the JDBC URL and return a JdbcPreviewData preview of the results:

>>> import datarobot as dr
>>> preview = dr.JdbcPreview.preview(
...     jdbc_url='jdbc:postgresql://localhost:5432/mydb',
...     sql='SELECT id, name, email FROM public.users WHERE age = ?',
...     max_rows=5,
...     parameters={'user': 'dbuser', 'password': 'secret'},
...     bind_parameters=[25],
... )
>>> preview.columns
['id', 'name', 'email']
>>> len(preview.records)
5
>>> preview.df['name'].head(5)
0    John Doe
1    Jane Doe
2    Alice Smith
3    Bob Johnson
4    Carol White
Name: name, dtype: object

The parameters argument passes connection credentials and options (such as user, password, ssl, and timeout) as key-value pairs, instead of embedding them in the JDBC URL.

Use execute_update() to run an update statement against the JDBC URL:

>>> dr.JdbcPreview.execute_update(
...     jdbc_url='jdbc:postgresql://localhost:5432/mydb',
...     sql='INSERT INTO my_table (id, name) VALUES (?, ?)',
...     parameters={'user': 'dbuser', 'password': 'secret'},
...     bind_parameters=[1, 'John'],
... )
'OK'

Query using QueryEngine¶

QueryEngine is a datarobot package add-on that wraps the querying methods above in familiar SQLAlchemy syntax, so you can use named parameter binding (:name) and SQLAlchemy constructs (such as select() and insert()) instead of positional ? placeholders and raw SQL strings.

Install the datarobot query-engine package add-on to use it:

pip install 'datarobot[query-engine]'

Create a QueryEngine from either a JDBC connection (from_jdbc_connection) or an existing data store's ID (from_data_store):

>>> from datarobot.query_engine import QueryEngine
>>> engine = QueryEngine.from_jdbc_connection(
...     jdbc_url='jdbc:postgresql://localhost:5432/mydb',
...     jdbc_params={'user': 'dbuser', 'password': 'secret'},
... )

>>> engine = QueryEngine.from_data_store(
...     data_store_id='5a8ac90b07a57a0001be501e',
...     credential_id='9963d544d5ce3se783r12190',
... )

Use execute() to run a statement, passing named parameters:

>>> results = engine.execute(
...     "SELECT * FROM users WHERE name = :name",
...     params={"name": "John Doe"},
... )
>>> results.all()
[(1, "John Doe")]

You can also pass an existing SQLAlchemy construct instead of a raw SQL string, and bind parameters to it the same way:

>>> from sqlalchemy import select, bindparam, column, table
>>> USER_TABLE = table("users", column("name"), column("status"))
>>> results = engine.execute(
...     select(USER_TABLE)
...         .where(USER_TABLE.c.name == "John Doe")
...         .where(USER_TABLE.c.status == bindparam("status")),
...     params={"status": "active"},
... )
>>> results.all()
[("John Doe", "active")]

execute() uses a best-efforts heuristic to guess whether a statement returns rows, based on its first keyword: statements starting with SELECT, WITH, SHOW, and similar query keywords are fetched as results, while statements like INSERT, UPDATE, and DELETE are assumed to be side-effecting and return nothing.

>>> from datarobot.query_engine import QueryMode
>>> results = engine.execute(
...     "UPDATE users SET status = 'active' WHERE name = 'John Doe' RETURNING id, name, status",
...     mode=QueryMode.QUERY,
... )
>>> results.all()
[(1, "John Doe", "active")]

See the QueryEngine API reference for additional examples, including bound parameter expansion for lists & tuples.

Create a data source¶

Once you have a data store, you can query datasets via the data source. When creating a data source, first create a datarobot.DataSourceParameters object from a data store's ID and a query. Then, create the data source with the following:

• type: The type must be either dr.enums.DataStoreTypes.DR_DATABASE_V1 or dr.enums.DataStoreTypes.JDBC.
• canonical_name: A user-friendly name to display in the API and GUI.
• params: The DataSourceParameters object.

>>> import datarobot as dr
>>> params = dr.DataSourceParameters(
...     data_store_id='5a8ac90b07a57a0001be501e',
...     query='SELECT * FROM airlines10mb WHERE "Year" >= 1995;'
... )
>>> data_source = dr.DataSource.create(
...     data_source_type='jdbc',
...     canonical_name='airlines stats after 1995',
...     params=params
... )
>>> data_source
DataSource('airlines stats after 1995')

You can create a non-JDBC Data Store with fields.

>>> params = dr.DataSourceParameters(
...     data_store_id=data_store_id,
...     catalog=catalog,
...     schema=schema,
...     table=table,
... )
>>> data_source = dr.DataSource.create(
...     data_source_type=dr.enums.DataStoreTypes.DR_DATABASE_V1,
...     canonical_name='BigQuery Data Source',
...     driver_id=driver_id,
...     params=params
... )

Create projects¶

You can create new projects from a data source, demonstrated below.

>>> import datarobot as dr
>>> project = dr.Project.create_from_data_source(
...     data_source_id='5ae6eee9962d740dd7b86886',
...     username='username',
...     password='password'
... )

As of v3.0 of the Python API client, you can alternatively pass in the credential_id of an existing Dataset.Credential object.

>>> import datarobot as dr
>>> project = dr.Project.create_from_data_source(
...     data_source_id='5ae6eee9962d740dd7b86886',
...     credential_id='9963d544d5ce3se783r12190'
... )

Alternatively, pass in credential_data, which conforms to CredentialDataSchema.

>>> import datarobot as dr
>>> s3_credential_data = {"credentialType": "s3", "awsAccessKeyId": "key123", "awsSecretAccessKey": "secret123"}
>>> project = dr.Project.create_from_data_source(
...     data_source_id='5ae6eee9962d740dd7b86886',
...     credential_data=s3_credential_data
... )

Create prediction datasets¶

Given a data source, new prediction datasets can be created for any project.

>>> import datarobot as dr
>>> project = dr.Project.get('5ae6f296962d740dd7b86887')
>>> prediction_dataset = project.upload_dataset_from_data_source(
...     data_source_id='5ae6eee9962d740dd7b86886',
...     username='username',
...     password='password'
... )