JDBC data source
This article provides an introduction to JDBC along with a guide on how to create a JDBC data source in Upsolver.
Last updated
Was this helpful?
This article provides an introduction to JDBC along with a guide on how to create a JDBC data source in Upsolver.
Last updated
Was this helpful?
Java Database Connectivity (JDBC) is an application programming interface (API) for the programming language Java, which defines how a client may access a database. It is a Java-based data access technology used for Java database connectivity and is part of the Java Standard Edition platform from Oracle Corporation.
Upsolver supports uploading data from the following JDBC sources:
Microsoft SQL Server
MySQL
PostgreSQL
Amazon Redshift
Snowflake
Oracle
Other databases can also be added. Please .
The JDBC data source has three operating modes for determining changes.
By a timestamp
column
Query according to the timestamp
column, which should be indexed. Otherwise the database will perform a full (and possibly expensive) scan of the data every minute.
2. By an auto-incrementing key
Query the data based on an auto-incrementing value or key. The value should be indexed to avoid full scans of the table.
3. Capture the entire table periodically
Can be used for loading reference data periodically.
Mode 1 and 2 require the table containing a column that Upsolver can query from to detect new changes. The column must be updated every time Upsolver reads the change to the given row. A combination of both mode 1 and mode 2 can be used together as well.
The JDBC data source default behavior performs a full initial load followed by incremental updates.
1. From the Data Sources page, click New.
2. Select JDBC.
3. Name this data source.
6. Under Parallelism, enter in the number of independent shards to parse data, increase parallelism, and reduce latency.
This should remain 1 in most cases and be no more than the number of shards used to read the data from the source.
7. Enter the JDBC connection string.
8. (Optional) Configure any additional properties necessary to make the connection.
9. Enter in the connection username.
10. Enter in the corresponding password.
11. Enter the name of the table to connect to.
12. (Optional) Identify an incrementing column.
If you do not specify an incrementing column, Upsolver will automatically try and identify an incrementing column.
13. (Optional) Identify any timestamp columns.
Examples:
If you have two columns (time1, time2)
and a specific row only has an entry in time2
, this will be used.
If there is an entry in both time1
and time2
, it uses the timestamp from time1
.
14. (Optional) Set a read delay (in terms of seconds) to provide a buffer for how far back in time to look in the data.
15. (Optional) Enter the full load interval.
16. Click Continue to preview your data and review the following metrics:
Sample Size
Parsed Successfully
# of Errors
17. (Optional) If there are any errors, click Back to change the settings as required.
18. Click Create.
You can now use your JDBC data source.
4. From the dropdown, select a compute cluster (or ) to run the calculation on.
5. Select a target storage connection (or ) where the data read will be stored (output storage).
See: for more information on the string format for a specific connection type.