MySQL Connector

Overview

The mysql.connector is a critical component of the Vantage analytics and data platform that facilitates a connection to a MySQL database, enabling users to execute SQL queries in a secure and efficient manner. It serves as a bridge between the application and MySQL database by managing authentication, establishing connections, and executing read-only queries.

Purpose

The main purpose of the mysql.connector is to allow authorized users to run SQL queries against a MySQL database and return the results for analysis. It ensures the safety and validation of user credentials and handles potential errors during the request process.

Settings

1. Input Parameters

• Name: inputs
  • Input Type: Object
  • Description: This parameter is an object that contains the user-provided SQL query that will be executed against the MySQL database.
  • Impact of Change: The inputs object must include a valid SQL query string; otherwise, an error will be thrown.
  • Default Value: No default value; the query must be provided by the user.

2. Configuration Parameters

• Name: config
  • Input Type: Object
  • Description: This parameter can contain configuration options for the integration, though in the provided code snippet, it isn’t utilized. Future versions of this component may allow for additional configuration settings for flexibility.
  • Impact of Change: Currently, modifying this parameter will not change the behavior, as no properties are defined in the logic.
  • Default Value: No default value; this parameter does not have set functionality in the current scope.

How It Works

1. Session Management: It starts by retrieving the user's session information using the server session, which validates that the user is logged in and retrieves the clientId.

2. Input Validation: The function checks if the inputs.query is defined. If not, it throws an error indicating that an SQL query is required.

3. Service Retrieval: Then, it attempts to find the MySQL service associated with the user by querying the third_party_service table. The service must match a predefined key ('db/connector/mysql.adapter').

4. Credential Retrieval: If the service exists, it fetches the credentials required to connect to the MySQL database for the user from the credentials store.

5. Execution: Upon successfully obtaining the service credentials, it uses the makeIntegration() function to create an integration instance and passes the fetched service key and credentials.

6. Query Execution: The integration object then executes the user-provided SQL query in read-only mode and returns the output.

7. Error Handling: Any errors that occur during the connection or query execution are caught and displayed in a user-friendly manner.

Use Cases & Examples

Use Case 1: Data Retrieval for Reporting

A business analyst needs to extract specific metrics from a sales database in MySQL to create reports. They can use mysql.connector to run queries fetching sales data for the previous quarter.

Use Case 2: Customer Behavior Analysis

A marketing team aims to analyze customer interactions recorded in a MySQL database to optimize their campaigns. They can execute SQL queries that aggregate customer behavior data.

Example Configuration

Scenario: A data scientist needs to access the list of customer emails and their corresponding purchase amounts for creating a targeted email campaign.

Sample Code Implementation:

const mysqlConnector = require('./mysql.connector');

const runQuery = async () => {
  const inputs = {
    query: "SELECT email, SUM(purchase_amount) as total_spent FROM transactions GROUP BY email;"
  };

  try {
    const result = await mysqlConnector({ inputs });
    console.log(result.output1); // logs the query results
  } catch (error) {
    console.error('Error executing query:', error);
  }
};

runQuery();

In this example, the user specifies an SQL query that retrieves each customer's email alongside their total spending, enabling further analysis or engagement strategies.

AI Integrations and Billing Impacts

The mysql.connector does not have any built-in AI integrations. However, the outputs retrieved from SQL queries can be fed into machine learning models or analytics tools developed on the Vantage platform for enhanced insights.

As for billing impacts, the execution of queries through mysql.connector may generate costs based on database usage and API calls. It's recommended to monitor usage to ensure compliance with any associated billing plans with the database service being used.