Wsrtjtyk

I have PostgreSQL database with ~1000 different tables. I'd like to export all of these tables and data inside them into Parquet files.

In order to do it, I'm going to read each table into DataFrame and then store this df into Parquet file. Many of the PostgreSQL tables contains user-defined Types.

The biggest issue is - that I can't manually specify the schema for DataFrame. Will Apache Spark, in this case, be able to automatically infer the PostgreSQL table schemas and store them appropriately into Parquet format or this is impossible with Apache Spark and some other technology must be used for this purpose?

UPDATED

I have created the following PostgreSQL user-defined type, table and records:

create type dimensions as (

  width integer,

  height integer,

  depth integer

);



create table moving_boxes (

  id serial primary key,

  dims dimensions not null

);



insert into moving_boxes (dims) values (row(3,4,5)::dimensions);

insert into moving_boxes (dims) values (row(1,4,2)::dimensions);

insert into moving_boxes (dims) values (row(10,12,777)::dimensions);

Implemented the following Spark application:

// that gives an one-partition Dataset

val opts = Map(

  "url" -> "jdbc:postgresql:sparktest",

  "dbtable" -> "moving_boxes",

  "user" -> "user",

  "password" -> "password")



val df = spark.

  read.

  format("jdbc").

  options(opts).

  load



println(df.printSchema())



df.write.mode(SaveMode.Overwrite).format("parquet").save("moving_boxes.parquet")

This is df.printSchema output:

root

 |-- id: integer (nullable = true)

 |-- dims: string (nullable = true)

As you may see, Spark DataFrame infer dims schema as string and not as a complex nested type.

This is the log information from ParquetWriteSupport:

18/11/06 10:08:52 INFO ParquetWriteSupport: Initialized Parquet WriteSupport with Catalyst schema:

{

  "type" : "struct",

  "fields" : [ {

    "name" : "id",

    "type" : "integer",

    "nullable" : true,

    "metadata" : { }

  }, {

    "name" : "dims",

    "type" : "string",

    "nullable" : true,

    "metadata" : { }

  } ]

}

and corresponding Parquet message type:

message spark_schema {

  optional int32 id;

  optional binary dims (UTF8);

}

Could you please explain, will the original complex dims type(defined in PostgreSQL) be lost in the saved Parquet file or no?