JDBC data sources

JDBC integration allows FeatureQL to connect to any JDBC-compatible database, including PostgreSQL (and MySQL, Oracle, SQL Server, and many others to come). This enables direct access to existing database systems for online feature value retrieval.

Creating a JDBC data source

Establish a connection to your database using standard JDBC connection strings:

Connection configuration:

• DSN: Standard JDBC connection string with authentication
• Tables: Array of table/view names that can be accessed through this connection
• Timeout: Connection and query timeout settings
• Security: Supports SSL/TLS and various authentication methods

The SOURCE_JDBC() function creates a persistent database connection that can be reused across multiple queries and features.

Accessing data with EXTERNAL_VIEW

Use EXTERNAL_VIEW() to query JDBC sources with SQL statements:

Key components explained:

1. Table reference: %FM.DEMO1.POSTGRES_CONN[customers_view] references a specific table through the JDBC source
2. Join conditions: ON SELF.customer_id=%CUSTOMER_ID creates parameterized joins
3. Type safety: AS ROW(...) specification ensures proper type mapping
4. JSON handling: Complex data need to be stored as varchar like orders_json (limitations of datafusion) and can be parsed into structured types with JSON_PARSE_AS().

SQL query structure:

`SELECT customer_id, last_order_id, orders_json FROM %SOURCE[table_name]`

• Placeholder syntax: %SOURCE[table_name] is replaced with the actual table reference
• Parameter binding: %INPUT_NAME links the column in the source table to the input feature
• Dialect compatibility: SQL must be compatible with the target database system

Best practices

1. Connection management: Reuse JDBC sources across multiple features
2. Table allowlisting: Only expose necessary tables in the tables array
3. Type precision: Specify exact types in ROW specifications
4. Performance: Monitor query execution and optimize database indexes
5. Overload: Be careful of not overloading your databases. FeatureMesh can generate violent burst of queries.