pg.connector Documentation

Overview

The pg.connector is a robust integration component designed for the Vantage analytics and data platform. Its primary purpose is to connect to a PostgreSQL database, execute SQL queries, and return the results. This functionality facilitates data retrieval from PostgreSQL databases, allowing users to leverage the power of their stored data within the Vantage analytics environment.

Settings

The pg.connector includes several settings that dictate its configuration and behavior. Each of these settings plays a vital role in how the integration interacts with user inputs and the PostgreSQL service.

1. Inputs

• Name: inputs
• Input Type: Object
• Description: This setting is an object that requires specific properties to function correctly. The key property is query, which holds the SQL query string to be executed against the PostgreSQL database. Without this input, the component cannot perform its intended function.
• Default Value: None (This setting must be provided; failure to do so will cause an error).

2. Config

• Name: config
• Input Type: Object
• Description: The config setting is an object that can hold various configuration options for the connector. While the current implementation does not specify other configuration parameters, it serves as a placeholder for future enhancement or customization of connection parameters.
• Default Value: None (The configuration object may remain empty if not utilized, but an empty object is still a valid input).

3. Session Management

• Name: session
• Input Type: Object
• Description: The component retrieves the user's session information. This session contains user-specific data, including the clientId. The clientId is essential for connecting to the PostgreSQL database. The integration will throw an error if there is no valid session or if the clientId is missing.
• Default Value: N/A (This is dynamically generated based on user authentication).

How It Works

1. Session Validation: The pg.connector begins by acquiring the user's session. It checks for a valid clientId. If the session is invalid or the clientId is not present, the integration throws an error.

2. Service Identification: It queries the the database ORM to find the third-party service corresponding to the PostgreSQL adapter using a service key. If no service is found for the client, it returns an informative message.

3. Credential Retrieval: The integration then attempts to fetch the database credentials for the client. The credentials are vital for establishing a secure connection to the PostgreSQL service. If no credentials are found, an error message is returned.

4. Execute SQL Query: Upon successfully retrieving the service and credentials, the component constructs an integration instance using the node. It then executes the read-only SQL query provided in the inputs object. The result of this query is returned as output.

5. Error Handling: In cases where an error occurs during execution (e.g., invalid SQL syntax, database connection failure), the component returns a formatted error message.

Expected Data

• Inputs:

  • query (String): An SQL query string that the user wishes to execute, such as SELECT * FROM users;.

• Output:

  • On success, a structured result is returned containing data from the PostgreSQL database.
  • On failure, an error object is returned with details about what went wrong.

Use Cases & Examples

Use Cases

1. Data Analytics Reporting:

   • A business analyst needs a customizable report based on sales data stored in a PostgreSQL database. They can use pg.connector to execute necessary SQL queries to gather insights efficiently.

2. Real-Time Data Monitoring:

   • A company wants to pull real-time operational metrics from their PostgreSQL database to display on a dashboard. Using the pg.connector, they can execute queries to update this data dynamically as it changes in the database.

3. Machine Learning Model Input:

   • A data science team requires data inputs from the PostgreSQL database for model training. pg.connector can be used to query and retrieve this data in a format suitable for machine learning applications.

Example Configuration

Use Case: Data Analytics Reporting

Business Problem: A business analyst needs information about user sign-ups over the past month to prepare a monthly report.

Sample Configuration Data:

{
  "inputs": {
    "query": "SELECT COUNT(*) AS total_signups FROM users WHERE created_at >= NOW() - INTERVAL '1 month';"
  },
  "config": {}
}

Explanation:

• The inputs object contains a SQL query that counts the number of user sign-ups from the users table during the last month.
• The config object is empty in this case but could be expanded later if additional configuration settings are introduced.

By leveraging pg.connector, the analyst can directly execute this SQL query and retrieve valuable data for their reporting needs without needing to manage database connections manually.