SQL Relational Query, SQL Metric Query, and Relational Data Table

From FojiSoft Docs

Foji is a powerful data integration platform that allows you to store and process data efficiently. This documentation will guide you through the process of configuring a SQL Metric/Relational Query as a data source and a Relational Data Table under the Storage section in Foji's settings. The SQL Metric Query configuration enables you to retrieve specific metrics or perform custom SQL queries on your configured SQL database. The SQL Relational Query configuration enables you to execute custom SQL queries on your configured SQL database and retrieve data based on those queries. Finally, the Relational Data Table configuration enables you to define a structured table for storing relational data. To configure the table, you will need to provide the following fields: Name, Primary Key, and Columns.

Before proceeding with the configuration of a Relational Data Table in Foji, it is essential to ensure that the required connectors have been set up correctly. The connectors establish connections with the respective data sources and enable seamless data integration within Foji. Please follow the documentation provided at the following links for detailed instructions on setting up specific connectors:

  • Database Connector: For configuring general database connections, please refer to the documentation at Database Configuration in Foji
  • Snowflake Connector: If you are using Snowflake as your data source, follow the guidelines provided at Snowflake Connector Configuration in Foji to set up the Snowflake connector in Foji
  • Google BigQuery Connector: If you are using Google BigQuery as your data source, you can find detailed instructions on configuring the Google BigQuery connector in Foji at Google BigQuery Relational Data Configuration as a Data Source in Foji

By following the documentation specific to the connectors you require, you can ensure that the necessary connections are established and proceed with configuring your data sources and integration tasks in Foji effectively.

SQL Metric Query Data Source Set-up

To configure the data source, you will need to provide the following fields: Name, Connection, Scan Interval, Metric Prefix, and Query.

Step 1: Accessing Data Source Configuration

  1. Log in to your Foji account and navigate to the data sources section.
  2. Click on the "Add Data Source" button or select the option to create a new data source.

Step 2: Naming the Data Source

  1. In the "Name" field, enter a unique name for your SQL Metric Query data source. Choose a descriptive name that helps you identify the purpose or characteristics of this data source.

Step 3: Selecting the Connection

  1. From the "Connection" dropdown, select the configured database connection that you want to use for executing the metric query. Ensure that you have already set up the appropriate database connector in Foji before configuring the SQL Metric Query data source.

Step 4: Configuring the Scan Interval

  1. Specify the scan interval in minutes. This determines how frequently Foji will execute the metric query to retrieve updated data from the SQL database.

Step 5: Specifying the Metric Prefix

  1. In the "Metric Prefix" field, enter a prefix that will be appended to the metric names retrieved from the query results. This allows you to organize and differentiate metrics from multiple data sources within Foji.

Step 6: Defining the Query

  1. In the "Query" field, provide the custom SQL query that you want to execute on the SQL database. Ensure that the query is valid and retrieves the desired metrics or data. You can use SQL syntax supported by your database.

Step 7: Saving the Data Source Configuration

  1. After providing the necessary configuration details, click on the "Save" or "Create" button to save the SQL Metric Query data source configuration.
  2. Foji will automatically execute the metric query based on the specified scan interval and retrieve the data from the SQL database.
  3. You can now use this data source in your Foji workflows to process and analyze the metrics or data retrieved from the SQL database.
  4. Contact the Foji support team for more information on utilizing the SQL Metric Query data source within your workflows.

Relational Data Table Set-up

To configure the table, you will need to provide the following fields: Name, Primary Key, and Columns.

Step 1: Accessing Storage Settings

  1. Log in to your Foji account and navigate to the settings section.
  2. Look for the Storage or Database settings, where you can configure the Relational Data Table.

Step 2: Naming the Table

  1. In the "Name" field, enter a unique name for your Relational Data Table. Choose a descriptive name that helps you identify the purpose or characteristics of this table.

Step 3: Configuring Primary Key

  1. Specify the Primary Key for the table. The Primary Key uniquely identifies each row in the table and ensures data integrity. You can choose one or more columns from the table as the Primary Key.

Step 4: Adding Columns

  • Click on the "+" button to add a new column to the table.
  • In the "Name" field, enter a name for the column. Choose a descriptive name that reflects the data it will store.
  • In the "Position" field, specify the position of the column within the table.
  • From the dropdown list, select the appropriate data type for the column. Foji supports various data types, including:
  1. Array: An array is a collection of elements of the same data type. It allows you to store multiple values within a single column, making it useful for handling lists or sets of values.
  2. Boolean: Boolean represents a logical value that can be either true or false. It is commonly used to store binary or conditional data.
  3. Date: The date data type is used to store calendar dates without any time component. It typically represents a specific day, month, and year.
  4. DateTime: DateTime represents a specific point in time, including both the date and time components. It is used to store timestamps or time-related information.
  5. DateTime64: DateTime64 is an extended version of the DateTime data type that allows for higher precision and a larger range of supported values.
  6. Decimal: Decimal is used to store fixed-point decimal numbers with a specific precision and scale. It is suitable for precise numeric calculations where accuracy is critical.
  7. Fixed String: Fixed String represents a fixed-length string with a predetermined number of characters. The length is defined during table creation and remains constant for each value stored.
  8. Float: Float is used to store floating-point numbers, which are numbers with a fractional component. It provides a higher range of values but with less precision compared to the Decimal data type.
  9. Integer: Integer represents whole numbers without any fractional component. It is commonly used for storing numeric data that does not require decimal precision.
  10. IP: IP is used to store IP addresses, which uniquely identify devices on a network. It can store both IPv4 and IPv6 addresses.
  11. JSON: JSON represents structured data in JavaScript Object Notation format. It allows you to store and manipulate complex hierarchical data structures and nested objects.
  12. Low Cardinality: Low Cardinality is a data type used to optimize storage and performance for columns with a small number of distinct values. It is typically used for categorical or enumerated data.
  13. Map: Map represents a key-value pair data structure. It allows you to store and retrieve values based on a unique key.
  14. Multi-Polygon: Multi-Polygon is used to represent multiple polygons, which are geometric shapes with multiple sides. It is commonly used in geographic and spatial data.
  15. Nullable: Nullable indicates that a column can contain null or missing values. It allows for flexibility when handling optional or unknown data.
  16. Point: Point represents a single point in a two-dimensional space. It is often used to store coordinates or locations.
  17. Polygon: Polygon is used to represent a closed geometric shape with multiple sides. It is commonly used in geographic and spatial data to define boundaries or areas.
  18. Ring: Ring represents a circular or ring-shaped object. It is used to define the boundary of polygons or circles in spatial data.
  19. String: String is used to store text or character data. It can hold a variable number of characters and is widely used for storing textual information.
  20. UUID: UUID (Universally Unique Identifier) is a unique identifier that is generated using specific algorithms. It is used to uniquely identify entities or records in a system and is suitable for scenarios where uniqueness is crucial
  • If needed, repeat Steps 1-4 to add more columns to the table.

Step 5: Saving the Table Configuration

  1. After providing the necessary configuration details, click on the "Save" or "Apply" button to save the Relational Data Table configuration.
  2. The table is now configured and ready to be used for storing and processing relational data within Foji.
  3. You can modify the table configuration at any time by accessing the Storage settings and selecting the appropriate table.
  4. Contact the Foji support team for more information on utilizing the Relational Data Table within your workflows.

Note:

Creating a relational data table is not a prerequisite for configuring the SQL Metric Query data source in Foji. The SQL Metric Query data source allows you to execute custom SQL queries on your existing configured SQL database and retrieve specific metrics or data based on those queries.

However, if you want to store the retrieved data in a structured manner or perform further data integration tasks on it within Foji, you may consider creating a relational data table beforehand. The relational data table provides a structured schema for organizing and storing data. It can be beneficial if you want to persist the metric query results or perform additional operations on the retrieved data.

So, while creating a relational data table is not mandatory for configuring the SQL Metric Query data source, it can be a useful step depending on your specific requirements.

However, Before configuring the SQL Relational Query data source in Foji, it is important to have a pre-configured relational data table. The relational data table provides a structured schema for organizing and storing the retrieved data from the SQL database. Ensure that you have already created the target table in Foji with the appropriate schema matching the expected query results.

SQL Relation Query Configuration Set-up

Step 1: Accessing Data Source Configuration

  1. Log in to your Foji account and navigate to the data sources section.
  2. Click on the "Add Data Source" button or select the option to create a new data source.

Step 2: Naming the Data Source

  1. In the "Name" field, enter a unique name for your SQL Relational Query data source. Choose a descriptive name that helps you identify the purpose or characteristics of this data source.

Step 3: Selecting the Connection

  1. From the "Connection" dropdown, select the configured database connection that you want to use for executing the relational query. Ensure that you have already set up the appropriate database connector in Foji before configuring the SQL Relational Query data source.

Step 4: Configuring the Scan Interval

  1. Specify the scan interval in minutes. This determines how frequently Foji will execute the relational query to retrieve updated data from the SQL database.

Step 5: Selecting the Target Table

  1. From the "Target Table" dropdown, select the pre-configured table where you want to store the retrieved data. Ensure that you have already created the target table in Foji using the appropriate schema to match the data structure of the query results.

Step 6: Toggle - Append Data to Table

  1. Enable the "Append Data to Table" toggle if you want to append the retrieved data to the target table during each scan interval. If disabled, the target table will be truncated and refreshed with the new data.

Step 7: Defining the Query

  1. In the "Query" field, provide the custom SQL query that you want to execute on the SQL database. Ensure that the query is valid and retrieves the desired data. You can use SQL syntax supported by your database.

Step 8: Saving the Data Source Configuration

  1. After providing the necessary configuration details, click on the "Save" or "Create" button to save the SQL Relational Query data source configuration.
  2. Foji will automatically execute the relational query based on the specified scan interval and retrieve the data from the SQL database.
  3. The retrieved data will be stored in the target table, either by appending it to the existing data or replacing it, based on the selected configuration.
  4. You can now use this data source in your Foji workflows to process and analyze the retrieved data from the SQL database.
  5. Contact the Foji support team for more information on utilizing the SQL Relational Query data source within your workflows.

Conclusion

By following the steps outlined in this documentation, you can configure a SQL Metric/Relational Query Data Source and Relational Data Table under the Storage settings in Foji. This allows you to retrieve specific metrics or perform custom SQL queries on your SQL database, enabling seamless integration, execute custom SQL queries on your SQL database and retrieve data based on those queries, enabling seamless integration and processing of data using the Foji platform, and allows you to define a structured table for storing and managing relational data efficiently within the Foji platform.

Retrieved from "https://docs.foji.io/index.php?title=SQL_Relational_Query,_SQL_Metric_Query,_and_Relational_Data_Table&oldid=1568"