Add to debug logging in the jdbc integration module the information about the data loading process

[zaleslaw]

• number of loaded rows
• total number of rows in the table/query
• number of data/size of data calculated by footprint in memory, number of columns and its types

This functionality could be auto-enabled for all tables or only for huge tables with many rows, also will be great to ask about the stat in alternative ways rather than proposed below:

// Your original query
val originalQuery = "SELECT * FROM your_table_name WHERE column_name = 'some_value'"

// Query to count the number of rows
val countQuery = "SELECT COUNT(*) as count FROM (" + originalQuery + ") AS count_query"

// Create a statement and execute the count query
val countStatement = connection.createStatement()
val countResultSet = countStatement.executeQuery(countQuery)

// Get the row count
val rowCount = 0
if (countResultSet.next()) {
    rowCount = countResultSet.getInt("count")

[zaleslaw]

To rule the settings of logging and debug mode, we probably need a high-level log library as a HOCON with settings like

logging {
  enabled = true
  batchSize = 1000
}

to have an access in code with

val logger = LoggerFactory.getLogger("DatabaseLoader")

val config = ConfigFactory.load()
val loggingEnabled = config.getBoolean("logging.enabled")
val batchSize = config.getInt("logging.batchSize")



    // Load data into DataFrame and log number of loaded rows
    var loadedRows = 0
    var counter = 0
    while (resultSet.next()) {
        extractNewRowFromResultSetAndAddToData(dataFrame, resultSet)
        loadedRows++
        if (loggingEnabled && loadedRows % batchSize == 0) {
            logger.debug("Loaded $loadedRows rows.")
        }
    }
    logger.info("Number of loaded rows: $loadedRows")

[zaleslaw]

To calculate the number of rows we could use the power of the ResultSet

// Log the total number of rows in the table/query
    resultSet.last()
    val totalRows = resultSet.row
    resultSet.beforeFirst()
    logger.info("Total number of rows: $totalRows")

[zaleslaw]

Here's a step-by-step guide on how to estimate the memory usage based on the schema:

Determine the Memory Footprint of Each Data Type: You need to know the approximate memory footprint of each SQL data type when it's loaded into memory in Kotlin.

Calculate the Size of One Row: Sum the memory footprint of all columns for a single row.

Multiply by the Number of Rows: Multiply the size of one row by the total number of rows to get the estimated memory usage.

// Define the memory footprint of common SQL data types in bytes or do the same for the given Kotlin types
val SQL_TYPE_MEMORY_FOOTPRINT = mapOf(
    Types.INTEGER to 4,
    Types.BIGINT to 8,
    Types.FLOAT to 4,
    Types.DOUBLE to 8,
    Types.VARCHAR to 40, // This is an average size for a varchar, actual size can vary
    Types.CHAR to 2, // Assuming each char is 2 bytes (UTF-16)
    Types.DATE to 12,
    Types.TIMESTAMP to 12
    // Add other types as needed
)

fun calculateRowSize(resultSet: ResultSet): Int {
    val metaData = resultSet.metaData
    val columnCount = metaData.columnCount
    var rowSize = 0

    for (i in 1..columnCount) {
        val columnType = metaData.getColumnType(i)
        rowSize += SQL_TYPE_MEMORY_FOOTPRINT.getOrDefault(columnType, 16) // Default size for unknown types
    }

    return rowSize
}

it could be interesting before extracting data from any source not only SQL

fun estimateMemoryUsage(query: String, url: String, user: String, password: String): Long {
    val connection: Connection = DriverManager.getConnection(url, user, password)
    val statement: Statement = connection.createStatement()
    val resultSet: ResultSet = statement.executeQuery(query)

    // Calculate the size of one row
    resultSet.next() // Move to the first row
    val rowSize = calculateRowSize(resultSet)

    // Get the total number of rows in the result set
    resultSet.last()
    val totalRows = resultSet.row
    resultSet.beforeFirst()

    // Estimate total memory usage
    val totalMemoryUsage = rowSize * totalRows

    connection.close()
    return totalMemoryUsage.toLong()
}

fun main() {
    val query = "SELECT * FROM your_table"
    val url = "jdbc:your_database_url"
    val user = "your_username"
    val password = "your_password"

    val estimatedMemoryUsage = estimateMemoryUsage(query, url, user, password)
    println("Estimated memory usage: $estimatedMemoryUsage bytes")
}