readSqlQuery
Retrieves the schema of an SQL query result using the provided database configuration.
Default Behavior:
If DbConnectionConfig.readOnly is true (which is the default), the connection will be:
explicitly set as read-only via
Connection.setReadOnly(true)used with
autoCommit = falseautomatically rolled back after reading, ensuring no changes to the database
Even if DbConnectionConfig.readOnly is set to false, the library still prevents data-modifying queries and only permits safe SELECT operations internally.
Return
the schema of the SQL query as a DataFrameSchema object.
Parameters
the database configuration to connect to the database, including URL, user, and password.
the SQL query to execute and retrieve the schema from.
Retrieves the schema of an SQL query result using the provided DataSource.
Example with HikariCP:
import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource
val config = HikariConfig().apply {
jdbcUrl = "jdbc:postgresql://localhost:5432/mydb"
username = "user"
password = "password"
}
val dataSource = HikariDataSource(config)
// Get the schema for a SQL query
val querySchema = DataFrame.getSchemaForSqlQuery(
dataSource,
"SELECT name, age, city FROM customers WHERE age > 25"
)
// Inspect the schema
println(querySchema.columns)Return
the schema of the SQL query as a DataFrameSchema object.
Parameters
the DataSource to get a database connection from.
the SQL query to execute and retrieve the schema from.
the type of database, could be a custom object, provided by user, optional, default is null, in that case the dbType will be recognized from the dataSource.
See also
Retrieves the schema of an SQL query result using the provided database connection.
Return
the schema of the SQL query as a DataFrameSchema object.
Parameters
the database connection.
the SQL query to execute and retrieve the schema from.
the type of database, could be a custom object, provided by user, optional, default is null, in that case the dbType will be recognized from the connection.
See also
Converts the result of an SQL query to the DataFrame.
A column renamed in the query with AS is named by its alias: SELECT id AS customer_id FROM Users gives a column customer_id.
Note that if input dataframe contains duplicate column names, they will be automatically renamed in the resulting DataFrame.
NOTE: SQL query should start from SELECT and contain one query for reading data without any manipulation. It should not contain ; symbol.
Default Behavior:
If DbConnectionConfig.readOnly is true (which is the default), the connection will be:
explicitly set as read-only via
Connection.setReadOnly(true)used with
autoCommit = falseautomatically rolled back after reading, ensuring no changes to the database
Even if DbConnectionConfig.readOnly is set to false, the library still prevents data-modifying queries and only permits safe SELECT operations internally.
Return
the DataFrame containing the result of the SQL query.
Parameters
the database configuration to connect to the database, including URL, user, and password.
the SQL query to execute.
the maximum number of rows to retrieve from the result of the SQL query execution. null (default) means no limit - all available rows will be fetched or positive integer (e.g., 100) - fetch at most that many rows
indicates how the column nullability should be inferred.
controls SQL query validation. SqlValidation.ReadOnly blocks DDL/DML statements and only allows read-oriented queries (SELECT, WITH, VALUES, TABLE, EXPLAIN). SqlValidation.None (default) passes the query to the database without validation.
optional lambda to configure the PreparedStatement before execution. This allows for custom tuning of fetch size, query timeout, and other JDBC parameters.
Converts the result of an SQL query to the DataFrame.
A column renamed in the query with AS is named by its alias: SELECT id AS customer_id FROM Users gives a column customer_id.
Note that if input dataframe contains duplicate column names, they will be automatically renamed in the resulting DataFrame.
Return
the DataFrame containing the result of the SQL query.
Parameters
the DataSource to obtain a database connection from.
the SQL query to execute.
the maximum number of rows to retrieve from the result of the SQL query execution. null (default) means no limit - all available rows will be fetched or positive integer (e.g., 100) - fetch at most that many rows
indicates how the column nullability should be inferred.
the type of database, could be a custom object, provided by user, optional, default is null, in that case the dbType will be recognized from the dataSource.
controls SQL query validation. SqlValidation.ReadOnly blocks DDL/DML statements and only allows read-oriented queries (SELECT, WITH, VALUES, TABLE, EXPLAIN). SqlValidation.None (default) passes the query to the database without validation.
optional lambda to configure the PreparedStatement before execution. This allows for custom tuning of fetch size, query timeout, and other JDBC parameters.
See also
Converts the result of an SQL query to the DataFrame.
A column renamed in the query with AS is named by its alias: SELECT id AS customer_id FROM Users gives a column customer_id.
Note that if input dataframe contains duplicate column names, they will be automatically renamed in the resulting DataFrame.
Return
the DataFrame containing the result of the SQL query.
Parameters
the database connection to execute the SQL query.
the SQL query to execute.
the maximum number of rows to retrieve from the result of the SQL query execution. null (default) means no limit - all available rows will be fetched or positive integer (e.g., 100) - fetch at most that many rows
indicates how the column nullability should be inferred.
the type of database, could be a custom object, provided by user, optional, default is null, in that case the dbType will be recognized from the connection.
controls SQL query validation. SqlValidation.ReadOnly blocks DDL/DML statements and only allows read-oriented queries (SELECT, WITH, VALUES, TABLE, EXPLAIN). SqlValidation.None (default) passes the query to the database without validation.
optional lambda to configure the PreparedStatement before execution. This allows for custom tuning of fetch size, query timeout, and other JDBC parameters.