Technical Document: API Data Ingestion

and SQL Logging System

Overview
This document outlines the architecture and functionality of a system designed to ingest
JSON data from an API, process it, and load it into a SQL database. The system includes
components for logging, error handling, and dynamically handling SQL tables based on the
structure of the incoming JSON data.

Key Components
1. Data Ingestion: Fetches JSON data from a specified API endpoint.
2. Data Parsing: Parses the fetched JSON data into a format suitable for SQL insertion.
3. Dynamic SQL Table Handling: Ensures the target SQL table exists and adjusts to match the
structure of the incoming JSON data.
4. Data Loading: Inserts the parsed data into the SQL table, handling dynamic columns and
truncating data when necessary.
5. Logging: Logs API calls and inserts error details into a dedicated error log table in the
database.

1. Data Ingestion
The system uses the `FetchDataFromApi` method to fetch data from a provided API
endpoint. It returns the raw JSON data along with the HTTP status code and the time taken
for the request.

Method: FetchDataFromApi
```csharp
public static async Task<(string jsonData, int statusCode, long timeConsumed)>
FetchDataFromApi(string apiUrl)
```
- **Parameters**: API URL.
- **Returns**: Tuple containing the fetched JSON data, HTTP response status code, and time
consumed.
- **Implementation**:
- Uses `HttpClient` to make a `GET` request to the API.
- Tracks the time taken to get a response.
- Ensures the response status is successful (`2xx` status).
- Logs the API call details.
2. Data Parsing
The system parses the fetched JSON data into a `List<Dictionary<string, object>>` format,
which can be dynamically mapped to SQL table columns.

Method: ParseJsonData
```csharp
public static List<Dictionary<string, object>> ParseJsonData(string jsonData)
```
- **Parameters**: Raw JSON data string.
- **Returns**: List of key-value pair dictionaries, each representing a parsed record from
the JSON data.
- **Implementation**:
- Handles both JSON arrays and objects.
- Iteratively parses JSON elements and their nested structures (arrays/objects).
- Dynamically creates key-value pairs, flattening nested JSON structures.

3. Dynamic SQL Table Handling

Before loading the data, the system ensures the target SQL table exists and adjusts its
schema if necessary to match the structure of the parsed data. This is handled by
dynamically generating SQL `CREATE TABLE` statements based on the incoming data.

Method: EnsureTableExists
```csharp
public static void EnsureTableExists(string tableName, string connectionString,
List<Dictionary<string, object>> sampleData)
```
- **Parameters**: Table name, database connection string, and sample data (parsed JSON).
- **Implementation**:
- Checks if the table exists in the database using SQL metadata queries.
- Dynamically constructs a `CREATE TABLE` query based on the keys in the parsed data.
- Handles column naming by converting keys from JSON (dots `.` are replaced with
underscores `_`).
- Ensures the table has a column for raw JSON data (`jsonRawData`) and a timestamp
(`MESSAGE_TIME`).

4. Data Loading
The system loads the parsed data into the SQL table, ensuring the correct mapping between
JSON keys and SQL columns. It also handles truncation for columns with size limits.

Method: LoadDataToSqlModifiNew
```csharp
public static void LoadDataToSqlModifiNew(List<Dictionary<string, object>> data, string
tableName, string connectionString, string jsonData)
```
- **Parameters**: Parsed data, table name, connection string, and raw JSON data.
- **Implementation**:
- Retrieves the column names and their maximum sizes from the SQL table.
- Dynamically constructs `INSERT INTO` SQL queries for each record in the parsed data.
- If a column has a size limit, it truncates the data to fit within that limit.
- Adds the raw JSON data to the `jsonRawData` column.
- Executes the insert queries for each record.

5. Logging and Error Handling

The system includes robust logging for API calls and error handling. API call details are
serialized as JSON and written to a log file, while error details are logged in a SQL database.

Method: LogApiCall
```csharp
public static void LogApiCall(string httpMethod, string endpoint, Dictionary<string, string>
requestHeaders, Dictionary<string, string> queryParams, int responseStatus, string
responseBody, long timeConsumed)
```
- **Parameters**: HTTP method, API endpoint, request headers, query parameters,
response status, response body, and time consumed.
- **Implementation**:
- Creates a structured log entry containing API call details.
- Serializes the log entry as JSON and appends it to a log file.

Method: InsertErrorLog
```csharp
public static void InsertErrorLog(string connectionString, string errorMessage, string
stackTrace, string pay_idl)
```
- **Parameters**: Database connection string, error message, stack trace, and payload
identifier (`pay_idl`).
- **Implementation**:
- Inserts error details into the `ErrorLog` table in the database.
- Captures the error message, stack trace, and a custom payload identifier to track which
API call caused the error.

Error Handling
The system handles various error scenarios, including:
- **API Failure**: If the API call fails, it logs the error and the API endpoint.
- **Data Truncation**: If a column value exceeds the defined maximum length, the system
truncates the data before inserting it into the SQL table.
- **Logging Errors**: Errors are logged in both the file system (API logs) and the database
(ErrorLog table), ensuring full traceability.
Example Workflow
1. **API Data Fetching**:
- The API is queried using `FetchDataFromApi`, and the response is logged.
2. **JSON Parsing**:
- The fetched JSON data is parsed by `ParseJsonData` into a structured list of records.
3. **Table Creation**:
- `EnsureTableExists` checks if the SQL table exists or creates it based on the incoming
JSON structure.
4. **Data Loading**:
- The parsed data is loaded into the SQL table using `LoadDataToSqlModifiNew`, ensuring
column sizes are respected and truncating data where necessary.
5. **Logging and Error Handling**:
- API call details are logged using `LogApiCall`, and any errors encountered are logged to
the database using `InsertErrorLog`.