¶ Database Connectors
¶ Best For
- Database tables/views
- Real-time sync
- Large schemas
- Production systems
¶ Supported Data Sources
¶ PostgreSQL
Supported Versions:
- PostgreSQL 11.x
- PostgreSQL 12.x
- PostgreSQL 13.x
- PostgreSQL 14.x
- PostgreSQL 15.x (recommended)
- PostgreSQL 16.x
Connection Process:
-
Select "Database Connector"
-
Choose PostgreSQL
-
Enter Connection Details:
Host: postgres.company.com Port: 5432 Database: production_db Schema: public Username: readonly_user Password: •••••••••• SSL Mode: Require -
Test Connection:
✓ Connection successful Detected 145 tables -
Select Table/View:
Available Tables: ☐ customers ☑ customer_transactions ← Selected ☐ orders ☐ products -
Review Schema:
Imported Schema from: customer_transactions Columns (8): - transaction_id (uuid, primary key) - customer_id (uuid, foreign key → customers.id) - transaction_date (timestamp) - amount (numeric(10,2)) - currency_code (varchar(3)) - status (varchar(20)) - created_at (timestamp) - updated_at (timestamp) -
Configure Sync:
Sync Mode: Real-time (CDC) Sync Frequency: Continuous Initial Load: Full table Incremental: Track by updated_at
Supported Data Types:
| PostgreSQL Type | Elementrix Type | Notes |
|---|---|---|
integer, bigint |
Integer | Whole numbers |
numeric, decimal |
Decimal | Precision preserved |
varchar, text |
String | Max length detected |
boolean |
Boolean | True/false |
date |
Date | Date only |
timestamp |
Timestamp | With timezone |
uuid |
UUID | Unique identifiers |
json, jsonb |
JSON | Structured data |
array |
Array | List types |
| User-defined enums | Enum | Values extracted |
Performance Optimization:
☑ Use read replica (recommended)
☑ Limit columns (select only needed)
☑ Add indexes on updated_at
☑ Configure batch size (default: 1000)
☑ Enable compression
¶ MySQL
Supported Versions:
- MySQL 5.7.x
- MySQL 8.0.x (recommended)
- MariaDB 10.5+
- MariaDB 10.6+
Connection Process:
Similar to PostgreSQL, with MySQL-specific settings:
Host: mysql.company.com
Port: 3306
Database: app_database
Username: readonly_user
Password: ••••••••••
SSL: Enabled
SSL CA: /path/to/ca.pem
Supported Data Types:
| MySQL Type | Elementrix Type | Notes |
|---|---|---|
INT, BIGINT |
Integer | Whole numbers |
DECIMAL, NUMERIC |
Decimal | Precision preserved |
VARCHAR, TEXT |
String | Max length detected |
BOOLEAN, TINYINT(1) |
Boolean | 0/1 converted |
DATE |
Date | Date only |
DATETIME, TIMESTAMP |
Timestamp | Timezone handling |
CHAR(36) |
UUID | For UUID strings |
JSON |
JSON | MySQL 5.7+ |
ENUM |
Enum | Values extracted |
Performance Optimization:
☑ Use read replica
☑ Configure binlog retention
☑ Limit column selection
☑ Batch size: 1000 rows
☑ Enable row compression
¶ Database Prerequisites
¶ PostgreSQL Prerequisites
Server Requirements:
PostgreSQL Version: 11.0 or higher
Memory: 2GB minimum
Disk: SSD recommended
Required Permissions:
GRANT SELECT ON table_name TO readonly_user;
GRANT REPLICATION SLAVE TO readonly_user; -- For CDC
¶ MySQL Prerequisites
Server Requirements:
MySQL Version: 5.7 or higher
Memory: 2GB minimum
Binary logging: Enabled (for CDC)
Required Permissions:
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT
ON *.* TO readonly_user@'%';
¶ Normalization Support
Second Normal Form (2NF): Fully supported
- No partial dependencies
- All non-key attributes depend on entire primary key
Third Normal Form (3NF): Supported
- No transitive dependencies
- Non-key attributes depend only on primary key
Recommendation: Use 3NF for master data, allow some denormalization for analytical queries.
¶ Next Steps
- Set up CDC for real-time sync
- Review Staging Connector for unsupported databases
- Continue with Data Product Creation