Creating SQL Connectors with Connector Designer: A Step-by-Step Guide

Introduction to Connector Designer

Connector Designer is a powerful, code-free visual tool designed for creating and modifying simple searches against third-party data sources. It's particularly useful for users who have experience working with REST services or SQL databases, allowing them to set up connections and simple searches without requiring coding skills.

In this comprehensive guide, we'll walk you through the process of creating a simple connector in Connector Designer to connect to and search a SQL data source. We'll build upon the concepts covered in the "Getting Started REST" video, focusing on the unique aspects of working with SQL data sources.

Getting Started with Connector Designer

Creating a New Connector

To begin, navigate to the Connector Designer overview window and follow these steps:

1. Click on the option to create a new connector
2. Provide a name for your connector (e.g., "My SQL Connector")
3. Configure the schema and optional charting scheme
   • You can use existing schemas from other connectors or I2 Analyze
   • Alternatively, create a new schema using the I2 Analyze Schema Designer
4. Select "SQL" as the data source type
5. Click "Continue" to proceed to the connection details

Configuring Connection Details

Next, you'll need to provide the necessary information to connect to your SQL database:

1. Choose the database type (Oracle or Microsoft SQL)
2. Enter the server's location (address and port)
3. Provide login details for accessing the data source
4. Select the specific database you want to connect to
5. Click "Create" to finalize the connector creation

Building a Query

Once your connector is created, it's time to build a query to retrieve data from the SQL table or view.

Selecting Tables

1. Review the list of available tables in the database
2. Choose the tables containing the information you need
   • In our example, we'll search for a person and their associated addresses and phones
3. Select all relevant tables and click "Select"

Creating a Service

The service you create will define the searches available to users of the connector.

1. Click "Add Service"
2. Provide a name for your search service (e.g., "Find People by Full Name")
3. Check the "Persistent Record Identifier" box if your SQL database always provides the same identifier for a given record
4. Add a description of the service's purpose
5. Click "Add" to begin mapping fields

Mapping Fields

In this crucial step, you'll map the fields from your source database to your target schema.

Entity Mapping

1. Drag the "Person" entity from the schema into the target area
2. Add the necessary links (e.g., "Access to" and "Resides" for address and phone data)

Property Mapping

1. Map database fields to target properties:
   • Person ID
   • Full Name
   • Date of Birth
   • Gender
2. For SQL databases, note that you cannot change field types or provide transformations for database fields

Link Mapping

1. Populate the "from" and "to" entities for each link
2. For the "Access" link:
   • Set "from" as Person and "to" as Phone
   • Use the copy and paste function to duplicate person entity mappings
   • Map the phone number to both the "identify" and "phone number" target properties
3. Repeat the process for the "Resides at" link:
   • Set "from" as Person and "to" as Address
   • Map relevant fields to target address properties

Configuring User Input

Define what information users need to provide to use the search:

1. Set the required input (e.g., full name)
2. Specify the input type (e.g., single-line string)
3. Mark the input as mandatory if necessary
4. Configure visibility for other properties
   • Hide properties that will be populated by database results

Setting Up Table Joins

To retrieve a complete picture of the data spread across multiple tables, you need to set up joins between the tables in your source database.

Creating Joins

1. Select the main table (e.g., "Person")
2. Click "Add Row" to build each join
3. For joining the Person and Phone tables:
   • Join the "Phone Link" field in the Person table with the "Phone Link" field in the Person Phone table
   • Add another row to join the "Person Link" field in the Person Phone table to the "Person Link" field in the Phones table
4. Repeat the process for address tables, following the same structure and relationships

Publishing and Testing the Connector

After setting up your service completely, it's time to publish and test your connection.

1. Click "Publish" to make your connector available
2. Once published, click "Test" to open the External Searches tool
3. Open your newly created search (e.g., "Find People by Full Name using MySQL Connector")
4. Enter a sample search term (e.g., a full name)
5. Provide any required authentication details for the SQL database
6. Run the search and review the results
7. Optionally, copy the results to a new chart for visualization

Best Practices for SQL Connectors

When working with SQL connectors in Connector Designer, keep these best practices in mind:

1. Optimize Your Queries

• Limit the number of tables and joins to improve performance
• Use indexes on frequently searched columns
• Avoid retrieving unnecessary data

2. Handle Data Types Correctly

• Ensure that data types in your schema match those in the SQL database
• Be aware of potential data type conversion issues

3. Secure Your Connection

• Use strong authentication methods
• Implement the principle of least privilege for database access
• Encrypt sensitive data in transit and at rest

4. Plan for Scalability

• Design your connector to handle increasing data volumes
• Consider implementing pagination for large result sets

5. Maintain Data Integrity

• Use transactions where appropriate
• Implement error handling and logging

6. Document Your Connector

• Provide clear descriptions for services and input parameters
• Document any assumptions or limitations of your connector

7. Test Thoroughly

• Test with various input scenarios
• Verify data accuracy and completeness
• Perform load testing to ensure performance under stress

Advanced SQL Connector Techniques

Using Stored Procedures

Connector Designer allows you to leverage stored procedures in your SQL database:

1. Select the stored procedure instead of tables when creating a query
2. Map the stored procedure parameters to input fields
3. Map the result set to your schema entities and properties

Implementing Complex Joins

For more sophisticated data relationships:

1. Use left, right, or full outer joins when necessary
2. Implement subqueries for complex data retrieval
3. Consider using Common Table Expressions (CTEs) for readability

Handling Large Datasets

When dealing with extensive data:

1. Implement server-side pagination
2. Use efficient indexing strategies
3. Consider data partitioning for improved query performance

Dynamic SQL Queries

For more flexible searches:

1. Use parameter binding to create dynamic WHERE clauses
2. Implement optional search criteria
3. Use CASE statements for conditional logic within queries

Troubleshooting Common Issues

Connection Problems

If you're having trouble connecting to your SQL database:

1. Verify network connectivity
2. Check firewall settings
3. Confirm database credentials
4. Ensure the database server is running and accessible

Mapping Errors

When encountering issues with field mapping:

1. Double-check data types between source and target
2. Verify that all required fields are mapped
3. Look for any naming conflicts or reserved keywords

Performance Issues

If your connector is running slowly:

1. Analyze and optimize your SQL queries
2. Check for missing indexes
3. Monitor database server resources
4. Consider caching frequently accessed data

Data Inconsistencies

When facing data discrepancies:

1. Verify the accuracy of your joins
2. Check for any data transformation issues
3. Ensure that your queries are returning the expected results
4. Validate data integrity in the source database

Extending Your SQL Connector

Adding Multiple Services

Expand your connector's functionality:

1. Create additional services for different search criteria
2. Implement services for various data views or reports
3. Design services for specific user roles or departments

Integrating with Other Data Sources

Enhance your connector's capabilities:

1. Combine SQL data with REST API calls
2. Implement data enrichment from external sources
3. Create composite services that merge data from multiple connectors

Implementing Data Transformation

Manipulate data to fit your needs:

1. Use SQL functions for basic transformations
2. Implement custom logic in stored procedures
3. Consider using ETL processes for complex transformations

Maintaining and Updating Your SQL Connector

Regular Maintenance

Keep your connector running smoothly:

1. Monitor performance and usage statistics
2. Regularly update database statistics and indexes
3. Review and optimize queries based on usage patterns

Handling Schema Changes

Adapt to evolving data structures:

1. Implement a change management process
2. Update your connector mappings when source schemas change
3. Version your connectors to maintain backward compatibility

Staying Current with Connector Designer Updates

Leverage new features and improvements:

1. Keep your Connector Designer installation up to date
2. Review release notes for new functionality
3. Refactor existing connectors to take advantage of new features

Conclusion

Creating SQL connectors with Connector Designer is a powerful way to integrate your SQL databases with your data analysis and visualization tools. By following this comprehensive guide, you've learned how to set up a connector for a SQL data source, select and map fields, configure table joins, and publish your connector.

Remember that practice makes perfect. As you create more connectors, you'll become more proficient at optimizing your queries, troubleshooting issues, and leveraging advanced features. Keep exploring the capabilities of Connector Designer, and you'll be able to create increasingly sophisticated and efficient SQL connectors to meet your data integration needs.

By mastering these techniques, you'll be well-equipped to harness the full potential of your SQL data sources, enabling more comprehensive and insightful data analysis across your organization.

Article created from: https://www.youtube.com/watch?v=coQqv_9TK7E