JDBCOptions — JDBC Data Source Options-spark技术分享

JDBCOptions — JDBC Data Source Options

JDBCOptions represents the options of the JDBC data source.

Option / Key Default Value Description

batchsize

1000

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 saveTable.

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 createTable.

createTableOptions

Empty string

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 createTable.

customSchema

(undefined)

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 data types 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
    ;

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 schema.

dbtable

(required)

Used when:

JDBCRDD is requested to resolveTable (when JDBCRelation is requested for the schema) and compute a partition
JDBCRelation is requested to insert or overwrite data and for the human-friendly text representation
JdbcRelationProvider is requested to write the rows of a structured query (a DataFrame) to a table
JdbcUtils is requested to tableExists, truncateTable, getSchemaOption, saveTable and createTable
JDBCOptions is created (with the input parameters for the url and dbtable options)
DataFrameReader is requested to load data from external table using JDBC data source (using DataFrameReader.jdbc method with the input parameters for the url and dbtable options)

driver

(recommended) Class name of the JDBC driver to use

Used exclusively when JDBCOptions is created. When the driver option is defined, the JDBC driver class will get registered with Java’s java.sql.DriverManager.

Note	`driver` takes precedence over the class name of the driver for the url option.

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

fetchsize

0

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

One of the following:

NONE
READ_UNCOMMITTED
READ_COMMITTED
REPEATABLE_READ
SERIALIZABLE

Used exclusively when JdbcUtils is requested to saveTable.

lowerBound

Lower bound of partition column

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

numPartitions

Number of partitions to use for loading or saving data

Used when:

JdbcRelationProvider is requested to loading data from a table using JDBC
JdbcUtils is requested to saveTable

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 lowerBound, upperBound and numPartitions options are also required.

When undefined, lowerBound and upperBound have to be undefined.

truncate

false

(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

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

Upper bound of the partition column

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

url

(required) A JDBC URL to use to connect to a database

Note	The options are case-insensitive.

JDBCOptions is created when:

DataFrameReader is requested to load data from an external table using JDBC (and create a DataFrame to represent the process of loading the data)
JdbcRelationProvider is requested to create a BaseRelation (as a RelationProvider for loading and a CreatableRelationProvider for writing)

Creating JDBCOptions Instance

JDBCOptions takes the following when created:

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

The input URL and table are set as the current url and dbtable options (overriding the values in the input parameters if defined).

Converting Parameters (Options) to Java Properties — `asProperties` Property



asProperties: Properties

asProperties: Properties

asProperties…FIXME

Note	`asProperties` is used when: `JDBCRDD` is requested to compute a partition (that requests a `JdbcDialect` to beforeFetch) `JDBCRelation` is requested to insert a data (from a DataFrame) to a table

`asConnectionProperties` Property



asConnectionProperties: Properties

asConnectionProperties: Properties

asConnectionProperties…FIXME

Note	`asConnectionProperties` is used exclusively when `JdbcUtils` is requested to createConnectionFactory

JDBCOptions — JDBC Data Source Options