Import Parquet to PostgreSQL: 2 Easy Ways

PostgreSQL is a database management system based on relational and object-oriented databases. To manage and modify complex data sets, it uses advanced SQL queries. 

Apache Parquet is a free, open-source column-oriented data file format that is compatible with the majority of data processing frameworks in the Hadoop environment. It has a striking resemblance to other column-oriented data storage formats available in Hadoop like ORC and RCfile.

This blog tells you about steps you can follow for Parquet to PostgreSQL data migration. In addition to giving an overview on PostgreSQL and Parquet, it also sheds light on why Parquet is better than other Data Formats.

What is PostgreSQL?

PostgreSQL is an open-source advanced model of the relational database system. PostgreSQL is a dependable database that has been developed by the open-source community for more than two decades.

Among the existing Open Source DBMS, only PostgreSQL offers enterprise-class performance and functions, as well as limitless growth opportunities. It is a major database used by both large organizations and startups to support their applications and products. It is generally utilized as a powerful back-end database that supports a wide range of dynamic websites and web applications.

Postgres provides querying and windowing capabilities. It supports relational and as well as non-relational querying. Because of its adaptability, it can be used as both a transactional database and a data warehouse for analytics.

 PostgreSQL assists developers in creating the most complex applications, running administrative tasks, and creating integral environments. Many web applications, as well as mobile and analytics apps, use PostgreSQL as their primary database.

PostgreSQL was designed to allow the addition of new capabilities and functionality. You can create your own data types, index types, functional languages, and other features in PostgreSQL.If you don’t like something about the system, you can always create a custom plugin to make it better, such as adding a new optimizer.

Key Features Of PostgreSQL

• Querying and Programming Language Support: PostgreSQL supports both SQL and JSON querying. The most prominent programming languages supported by PostgreSQL are Python, Java, C#, C/C+, Ruby, JavaScript, Perl, Go, and Tcl. 
• Table Inheritance: PostgreSQL supports a strong object-relational feature called inheritance. This enables a table to inherit part of its column properties from one or more other tables through inheritance, forming a parent-child connection. The child table inherits the same columns and constraints as its parent table (or tables), as well as its defined columns.
• Multi-version Concurrency Control (MVCC): Unlike most other database systems, which employ locks to regulate concurrency, Postgres uses a multi-version architecture model to maintain data consistency. This implies that each transaction in a database sees a snapshot of data from a previous transaction, regardless of the current state of the underlying data. This protects the transaction from viewing inconsistent data that could be caused by concurrent transaction updates on the same data rows, providing transaction isolation for each database session.
• Foreign Key Referential Integrity: PostgreSQL foreign key concept is based on the first table combination of columns with primary key values from the second table. It is also known as constraints or referential integrity constraints. It is specified that the values of the foreign key constraints column, which was corresponding with the actual values of the primary key column from another table.
• Asynchronous Replication: Asynchronous replication in  PostgreSQL offers a reliable and easy way to distribute data and make your setups more failsafe. In the event of server failure and disaster, Administrators can create read-only replicas of a primary server easily.

What is Parquet?

 Parquet was created to take advantage of compressed, efficient columnar data representation available to any project in the Hadoop ecosystem. It is designed to support compression and encoding schemes that are extremely efficient. It also allows per-column compression schemes

to be specified and it is future-proofed to allow for the addition of new encodings as they are invented and implemented.

Parquet, which was built from the ground up with complex nested data structures in mind, uses the record shredding and assembly algorithm described in the Dremel paper. This approach is believed to be much better than the simple flattening of nested namespaces. In a database, flattening data means storing it in one or a few tables that contain all of the information with little structure.

Limited Schema Evolution is supported by Apache Parquet, which means that the schema can be changed in response to changes in the data. It also allows you to add new columns and merge schemas that aren’t incompatible.

Apache Hive, Apache Drill, Apache Impala, Apache Crunch, Apache Pig, Cascading, Presto, and Apache Spark are some of the Big Data processing frameworks that Parquet supports.

Why is Parquet better than other Data Formats?

• Columnar Storage Format: When compared to row-based files like CSV, columnar storage like Apache Parquet is designed to be more efficient. When querying columnar storage, you can quickly skip over non-relevant data. So, as compared to row-oriented databases, aggregation queries take less time. As a result, latency is minimized for accessing data.
• Supports Nested Data Structures: Advanced nested data structures are supported by Apache Parquet. For each file, the layout of Parquet data files is optimized for queries that process large volumes of data.
• Flexible Compression: Parquet is designed to support a variety of compression methods as well as efficient encoding schemes. Since the column values tend to be of the same type, compression techniques specific to that type can be used. Data can be compressed using one of several codecs, resulting in different compression rates for different data files.
• Compatibility: Apache Parquet is a free open source platform compatible with most Hadoop data processing frameworks. It works best with serverless technologies like AWS Athena, Google BigQuery, Google Dataproc. etc.

What types of Data are Imported in PostgreSQL?

PostgreSQL has an Import/Export tool built into dbForge Studio that supports file transfer between most frequently used data formats. It allows you to save templates for repetitive export and import tasks. With this, you can easily migrate data from other servers, customize import and export jobs, save templates for recurring scenarios, and populate new tables with data.

Data formats that are compatible with PostgreSQL are:

• Text
• HTML
• MS Excel
• XML
• CSV
• JSON 
• Parquet

How To Import Data from Parquet to PostgreSQL?

Here are a few ways you can migrate data from Parquet to PostgreSQL:

• Parquet to PostgreSQL Integration: Querying Parquet Data as a PostgreSQL Database
• Parquet to PostgreSQL Integration: Using Spark Postgres Library

Parquet to PostgreSQL Integration: Querying Parquet Data as a PostgreSQL Database

An ODBC driver makes use of Microsoft’s Open Database Connectivity (ODBC) interface, which allows applications to connect to data in database management systems using SQL as a standard. SQL Gateway is a  secure ODBC proxy for remote data access. With SQL Gateway, your ODBC data sources behave like a standard SQL Server or MySQL database.

Using the SQL Gateway, the ODBC Driver for Parquet, and the MySQL foreign data wrapper from EnterpriseDB we can access Parquet data as a PostgreSQL database on Windows.

Steps to import Parquet to PostgreSQL:

• Configure Connection to Parquet: Specify the connection properties in an ODBC DSN (data source name). Microsoft ODBC Data Source Administrator can be used to create and configure ODBC DSNs. Then, Connect to your local Parquet files by setting the URI (Uniform resource identifier)connection properly to the location of the Parquet file.
• Start the Remoting Device: The MySQL remoting service is a daemon process that waits for clients’ incoming MySQL connections. You can set up and configure the MySQL remoting device in the SQL Gateway.
• Build the MySQL Foreign data wrapper: A Foreign Data Wrapper is a library that can communicate with an external data source while concealing the details of connecting to it and retrieving data from it. It can be installed as an extension to PostgreSQL without recompiling PostgreSQL. Building a Foreign Data Wrapper is essential for importing data from Parquet to PostgreSQL.

If you’re using PostgreSQL on a Unix-based system, you can install the mysql fdw using the PostgreSQL Extension Network (PGXN). Compile the extension if you’re using PostgreSQL on Windows to make sure you’re using the most recent version.

 Building Extension from Visual Studio

Obtain Prerequisites

• Step 1: Step Install PostgreSQL.
• Step 2: If you’re using a 64-bit PostgreSQL installation, get libintl.h from the PostgreSQL source as libintl.h is not currently included in the PostgreSQL 64-bit installer. 
• Step 3: Get the source code for the mysql fdw foreign data wrapper from Enterprise DB.
• Step 4: Install the MySQL Connector C.

Configure a Project 

You’re now ready to compile the extension with Visual Studio as you’ve obtained the necessary software and source code.

Follow the steps to create a project using mysql_fdw source:

• Step 1: Create a new empty C++ project in Microsoft Visual studio.
• Step 2:Right-click Source Files and click on Add -> Existing Item In the Solution Explorer. Select all of the .c and .h files from the mysql_fdw source In the file explorer.

To configure your project follow the steps:

• Step 2.1: If you’re creating a 64-bit system, go to Build -> Configuration Manager and select x64 under Active Solution Platform.
• Step 2.2: Right-click your project and select Properties from the drop-down menu.
• Step 2.3: Select All Configurations from the Configuration drop-down menu.
• Step 2.4: Dynamic Library should be selected under Configuration Properties -> General -> Configuration Type.
• Step 2.5: Enable C++ Exceptions is set to No in Configuration Properties -> C/C++ -> Code Generation -> Enable C++ Exceptions.
• Step 2.6: Select Compile as C Code in Configuration Properties -> C/C++ -> Advanced -> Compile As.
• Step 2.7: Select No from Linker -> Manifest File -> Generate Manifest.

Adding Required Dependencies

• Step 1:Select Edit in Linker -> Input -> Additional Dependencies and type the following:  

Also, make sure that Inherit From Parent or Project Defaults are both checked.

• Step 2:Select Edit and add the path to the lib folder in your PostgreSQL installation in Linker -> General -> Additional Library Directories.

• Step 3:Select No from Linker -> General -> Link Library Dependencies.

• Step 4: Add the following include to complete your project’s configuration: Add the following folders in the following order to C/C++ -> General -> Additional Include Directories:

Configure mysql_fdw for Windows

• Step 1:Add these defines to mysql fdw.c:  

• Step 2:Delete the following line from the mysql load library definition:

• Step 3:Replace the assignment of mysql dll handle with the following line in the mysql load library definition for a Windows build:

• Step 4:To export the function from the DLL, prefix the call to the mysql fdw handler function with the __declspec(dllexport) keyword:

• Step 5:To export the function from the DLL, add the __declspec(dllexport) keyword to the declaration of the mysql fdw validator function in option.c:

You can now build and choose the Release configuration.

Install the Extension 

Follow the steps below to install the extension after you’ve compiled the DLL:

• Step 1: In the PATH environment variable of the PostgreSQL server, add the path to the MySQL Connector C lib folder.
• Step 2:Copy the DLL from your project’s Release folder to your PostgreSQL installation’s lib subfolder.
• Step 3:Copy mysql fdw—1.0.sql and mysql fdw.control from the folder containing the mysql fdw csource files to the extension folder in your PostgreSQL installation’s share folder. C: Program FilesPostgreSQL9.4 share extension is an example of a location.

Query Parquet Data as a PostgreSQL Database

After you’ve installed the extension, you can begin running queries against Parquet data to import data from Parquet to PostgreSQL by following the steps below:

•  Step 1:Go to your PostgreSQL database and log in.

• Step 2:Install the database extension.

• Step 3:Create a Parquet data server object:

• Step 4:Make a user mapping for a MySQL remoting service user’s username and password. In the sample service configuration, the user’s credentials are as follows:

• Step 5: Make a local schema.

• Step 6: Import all of the tables from your Parquet database.

You can now run SELECT commands to import data from Parquet to PostgreSQL:

Parquet to PostgreSQL Integration: Using Spark Postgres Library

Spark-Postgres is intended for dependable and performant ETL in big-data workloads, and it includes read/write/scd capabilities to better connect Spark and Postgres. Spark SQL supports both reading and writing Parquet files, preserving the schema of the original data automatically.

It enables you to load multiple data files of parquet to PostgreSQL in a single spark command:

•  spark 
• .read.format(“parquet”)// specifies the read file format is parquet
•  .load(parquetFilesPath)   // reads the parquet files
•  .write.format(“postgres”) //specifies the write file format is parquet
• .option(“host”,”yourHost”)// specifies the host
• .option(“partitions”, 4) // 4 threads 
• .option(“table”,”theTable”)//specifies the table
•  .option(“user”,”theUser”) //specifies the user
• .option(“database”,”thePgDatabase”)//specifies the database
•  .option(“schema”,”thePgSchema”)// specifies the schema
• .loada             // bulk load into postgres

• Step 1: Parquet is fetched through a query into a Spark data frame, this fetches the query with 4 threads and produces a spark data frame. It produces 5 * 4 = 20 files to be read one by one (multiline).
• Step 2: We can use an optional JDBC URL, a JDBC URL identifies a database so that the appropriate driver can recognize it and connect to it. It copies the spark data frame into Postgres with 4 threads. Also, it disables the indexes and re-indexes the table afterward.

Conclusion

This blog talks about the steps to follow to import data from Parquet to PostgreSQL in an easy way. It also gives us a basic understanding of Parquet and PostgreSQL.

Extracting complex data from a diverse set of data sources to carry out an insightful analysis can be challenging, and this is where Hevo saves the day! Hevo offers a faster way to move data from 100+ Data Sources including Databases or SaaS applications into a destination of your choice or a Data Warehouse to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

VISIT OUR WEBSITE TO EXPLORE HEVO

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations, with a few clicks. Hevo Data with its strong integration with 100+ sources (including 40+ free sources) allows you to not only export data from your desired data sources & load it to the destination of your choice, but also transform & enrich your data to make it analysis-ready so that you can focus on your key business needs and perform insightful analysis using BI tools.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.