dbQuery Logic Documentation

Overview

The dbQuery Logic component is designed to facilitate the retrieval of data from various connected databases within the Vantage analytics and data platform. Users can configure the database connection, formulate a query—either an SQL string for SQL databases or an operation object for NoSQL databases—and specify an optional limit on the number of rows the query should return. This Logic is integral for businesses needing to extract and manipulate data from their databases for further analysis or visualization.

Settings

The dbQuery Logic component comprises critical settings that allow users to tailor its behavior to their specific needs. Below is an exhaustive description of each configuration setting.

1. credentialRef

• Input Type: Object
• Description: This is a reference to the credentials necessary for accessing the database. It includes three properties:
  • strategy: Defines the authentication strategy used (not detailed in provided code).
  • credentialId: The identifier for the credentials that authenticate access to the database.
  • serviceKey: Represents the type of service being connected (e.g., SQL, NoSQL).
• Default Value: None. This must be provided for the Logic to function.
• Effect on Behavior: If not set, the Logic will throw an error indicating that no database has been selected.

2. query

• Input Type: String (for SQL databases) or Object (for NoSQL databases)
• Description: This is the actual query that the Logic will execute against the database. For SQL databases, this will typically be a string of SQL commands. For NoSQL databases, it is an operation object describing the read operation.
• Default Value: None. A query must be provided; if omitted, the Logic will throw an error.
• Effect on Behavior: The content of this field directly determines the output data returned from the database. Improper or malicious queries may lead to unintended results or errors.

3. maxRows

• Input Type: Numeric (Integer)
• Description: This optional parameter sets an upper limit on the number of rows to return from the query.
• Default Value: No limit applied (if not specified).
• Effect on Behavior:
  • When specified, if the query does not contain a limit clause (in SQL), the Logic appends a LIMIT to the query.
  • For NoSQL operations, if maxRows is specified, it modifies the query to restrict output accordingly.

How It Works

The dbQuery Logic operates by first validating and resolving the database credentials provided in the configuration. It then constructs an appropriate query based on the service type (SQL or NoSQL) and executes it against the connected database:

1. The Logic retrieves the credentialRef and extracts the credentialId.
2. It checks the validity of the credentials. If not found, or if they do not correspond to a valid database service, appropriate error messages are thrown.
3. Based on the serviceKey, the Logic determines the type of database connection to initiate, either for SQL or NoSQL databases:
   • For SQL, it appends a LIMIT statement based on maxRows if necessary.
   • For NoSQL, it modifies the operation object passed in the query.
4. Results from the query execution are normalized to a consistent array format.
5. Finally, it ensures the database adapter is closed properly to prevent resource leakage.

Expected Data

The dbQuery component expects the following data types for its settings:

• credentialRef: An object containing valid credentials.
• query: A string (for SQL) or an object (for NoSQL).
• maxRows: An optional numeric limit.

AI Integrations

Currently, the dbQuery component does not directly leverage AI integrations within its functionality. However, it can be a precursor to data that might later be processed by AI models or algorithms for analysis, predictions, or generating insights based on the queried results.

Billing Impact

Utilizing the dbQuery Logic may affect billing depending on various factors:

• Database Queries: The number of queries executed may incur costs based on the database service provider's pricing structure (e.g., operational charges for SQL querying, data retrieval operations in NoSQL).
• Data Volumes: Large data retrieval actions may contribute to billing, especially in cloud-based database environments.
• Usage Thresholds: Exceeding any usage thresholds outlined in your service agreement with the database provider may lead to increased costs.

Use Cases & Examples

Use Case 1: Business Intelligence Reporting

A business analyst needs to generate monthly sales reports from a SQL database. They require specific customer data joined with sales data filtered by sales regions.

Use Case 2: Data Analysis for Machine Learning

Data scientists need to fetch specific records from a NoSQL store to train a machine-learning model. This involves querying for user behavior data.

Use Case 3: Real-Time Dashboard Updates

A company maintains a real-time dashboard that reflects user engagement metrics stored in a Firestore database and wishes to pull only the latest records.

Example Configuration

Use Case: Monthly sales reporting from a SQL database.

Sample Configuration:

{
  "credentialRef": {
    "strategy": "default",
    "credentialId": "123",
    "serviceKey": "db/connector/postgresql.adapter"
  },
  "query": "SELECT customer_id, SUM(sales_amount) as total_sales FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2023-02-01' GROUP BY customer_id ORDER BY total_sales DESC",
  "maxRows": 100
}

In this example:

• The Logic fetches sales data for January 2023 grouped by customer, limited to the top 100 highest sales. This configuration enables efficient reporting while ensuring performant query execution against the database.