Java Database Connectivity (JDBC)

Apache Spark natively supports reading and writing data directly to and from several different types of databases. This is accomplished using Java Database Connectivity, commonly referred to as JDBC. The Spark JDBC data source reads and writes data directly to and from databases using Spark DataFrames. By extension, this means that GeoAnalytics Engine can read and write data to any of the databases supported by Apache Spark. A few of the supported database types are:

• Microsoft SQL Server
• Oracle
• PostgreSQL

A JDBC driver is required to read and write data to and from a database with GeoAnalytics Engine. Each database type (e.g. PostgreSQL, Microsoft SQL Server, etc.) requires a specific JDBC driver. For example the JDBC driver for Microsoft SQL Server is different than the JDBC driver for PostgreSQL. The Maven repository contains the JDBC drivers required to connect to each of the databases supported by Apache Spark.

The table below outlines a few of the common JDBC data source options that are used when reading and writing to and from a database.

Database read

The Python code sample below creates a new DataFrame by reading in all of the database table records.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
df = spark.read \
          .format("jdbc") \
          .option("url", "jdbc:postgresql://localhost:5432/sample") \
          .option("dbtable", "locations") \
          .option("user", "user1") \
          .option("password", "test123") \
          .option("driver", "org.postgresql.Driver") \
          .load()

Database write

The Python code sample below writes the data from a DataFrame to a database table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
df.write \
  .format("jdbc") \
  .option("url", "jdbc:sqlserver://localhost\demoInstance;databaseName=sampleTwo") \
  .option("dbtable", "places") \
  .option("user", "username2") \
  .option("password", "p@ssw0rd") \
  .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver") \
  .save()

The read and write to databases tutorial includes detailed examples of how to connect to, read from, and write to Microsoft SQL Server, Oracle, and PostgreSQL.

Usage notes

• Geometry type columns do not work with JDBC.

  • When writing, geometry columns need to be converted to a well-known binary or a well-known text column and then the geometry type column will need to be dropped from the DataFrame before writing it to a database table with JDBC. The following GeoAnalytics Spatial Type functions are used to convert the geometry type column to a type supported by JDBC:

    • Well-known binary:
      • ST_AsBinary
    • Well-known text:
      • ST_AsText

  • When reading in a geometry column from a database table, the geometry column needs to be in the well-known binary or the well-known text format. The following GeoAnalytics Spatial Type functions are used to convert a well-known binary or a well-known text column to a geometry type column:

    • Well-known binary:
      • ST_PointFromBinary
      • ST_MPointFromBinary
      • ST_LineFromBinary
      • ST_PolyFromBinary
      • ST_GeomFromBinary
    • Well-known text:
      • ST_PointFromText
      • ST_MPointFromText
      • ST_LineFromText
      • ST_PolyFromText
      • ST_GeomFromText