Skip to content

JDBCOptions

JDBCOptions is the options of the JDBC data source.

Options

[[options]] .Options for JDBC Data Source [cols="1m,1,2",options="header",width="100%",separator="!"] |=== ! Option / Key ! Default Value ! Description

! batchsize ! 1000 ! [[batchsize]]

The minimum value is 1

Used exclusively when JdbcRelationProvider is requested to write the rows of a structured query (a DataFrame) to a table through JdbcUtils helper object and its <>.

! createTableColumnTypes ! ! [[createTableColumnTypes]]

Used exclusively when JdbcRelationProvider is requested to write the rows of a structured query (a DataFrame) to a table through JdbcUtils helper object and its <>.

! createTableOptions ! Empty string ! [[createTableOptions]]

Used exclusively when JdbcRelationProvider is requested to write the rows of a structured query (a DataFrame) to a table through JdbcUtils helper object and its <>.

! customSchema ! (undefined) a! [[customSchema]] Specifies the custom data types of the read schema (that is used at load time)

customSchema is a comma-separated list of field definitions with column names and their DataTypes in a canonical SQL representation, e.g. id DECIMAL(38, 0), name STRING.

customSchema defines the data types of the columns that will override the data types inferred from the table schema and follows the following pattern:

colTypeList
    : colType (',' colType)*
    ;

colType
    : identifier dataType (COMMENT STRING)?
    ;

dataType
    : complex=ARRAY '<' dataType '>'                            #complexDataType
    | complex=MAP '<' dataType ',' dataType '>'                 #complexDataType
    | complex=STRUCT ('<' complexColTypeList? '>' | NEQ)        #complexDataType
    | identifier ('(' INTEGER_VALUE (',' INTEGER_VALUE)* ')')?  #primitiveDataType
    ;

Used exclusively when JDBCRelation is requested for the <>.

! dbtable ! a! [[dbtable]] (required)

Used when:

! driver ! a! [[driver]][[driverClass]] (recommended) Class name of the JDBC driver to use

Used exclusively when JDBCOptions is <>. When the driver option is defined, the JDBC driver class will get registered with Java's https://docs.oracle.com/javase/8/docs/api/java/sql/DriverManager.html[java.sql.DriverManager].

NOTE: driver takes precedence over the class name of the driver for the <> option.

After the JDBC driver class was registered, the driver class is used exclusively when JdbcUtils helper object is requested to <>.

! fetchsize ! 0 ! [[fetchsize]] Hint to the JDBC driver as to the number of rows that should be fetched from the database when more rows are needed for ResultSet objects generated by a Statement

The minimum value is 0 (which tells the JDBC driver to do the estimates)

Used exclusively when JDBCRDD is requested to compute a partition.

! isolationLevel ! READ_UNCOMMITTED a! [[isolationLevel]] One of the following:

  • NONE
  • READ_UNCOMMITTED
  • READ_COMMITTED
  • REPEATABLE_READ
  • SERIALIZABLE

Used exclusively when JdbcUtils is requested to <>.

! lowerBound ! ! [[lowerBound]] Lower bound of partition column

Used exclusively when JdbcRelationProvider is requested to create a BaseRelation for reading

! numPartitions ! a! [[numPartitions]] Number of partitions to use for loading or saving data

Used when:

! partitionColumn ! ! [[partitionColumn]] Name of the column used to partition dataset (using a JDBCPartitioningInfo).

Used exclusively when JdbcRelationProvider is requested to create a BaseRelation for reading (with proper JDBCPartitions with WHERE clause)

When defined, the <>, <> and <> options are also required.

When undefined, <> and <> have to be undefined.

! truncate ! false ! [[truncate]][[isTruncate]] (used only for writing) Enables table truncation

Used exclusively when JdbcRelationProvider is requested to write the rows of a structured query (a DataFrame) to a table

! sessionInitStatement ! ! [[sessionInitStatement]] A generic SQL statement (or PL/SQL block) executed before reading a table/query

Used exclusively when JDBCRDD is requested to compute a partition.

! upperBound ! ! [[upperBound]] Upper bound of the partition column

Used exclusively when JdbcRelationProvider is requested to create a BaseRelation for reading

! url ! ! [[url]] (required) A JDBC URL to use to connect to a database |===

NOTE: The <> are case-insensitive.

JDBCOptions is <> when:

Creating Instance

JDBCOptions takes the following to be created:

  • JDBC URL
  • [[table]] Name of the table
  • [[parameters]] Case-insensitive configuration parameters (i.e. Map[String, String])

The input URL and <> are set as the current <> and <> options (overriding the values in the input <> if defined).

=== [[asProperties]] Converting Parameters (Options) to Java Properties -- asProperties Property

[source, scala]

asProperties: Properties

asProperties...FIXME

asProperties is used when:

=== [[asConnectionProperties]] asConnectionProperties Property

[source, scala]

asConnectionProperties: Properties

asConnectionProperties...FIXME

NOTE: asConnectionProperties is used exclusively when JdbcUtils is requested to spark-sql-JdbcUtils.md#createConnectionFactory[createConnectionFactory]


Last update: 2020-11-12