Inspecting joins in PostgreSQL

Tags: postgres, guide, tools
Published: 2022-01-11 20:21:33

SQL is a powerful language that provides a deep understanding of what can and cannot be done with data. SQL excels at bringing order to disorganized, large data sets and helps you discover how distinct data sets are related.  [Spark](https://databricks.com/blog/2018/05/23/a-guide-to-developer-apache-spark-use-cases-and-deep-dives-talks-at-spark-ai-summit.html) is an open-source analytics engine for processing large amounts of data (what you might call “big data”).

It allows us to maximize distributed computing when carrying out time-intensive operations on lots of data, or even when building ML models. [PySpark](https://spark.apache.org/docs/latest/api/python/index.html) is a Python application programming interface that allows us to use Apache Spark in Python. Querying SQL databases with PySpark thus lets us take advantage of Spark’s implicit data parallelism and fault tolerance from a Python interface. This gives us the ability to process large quantities of data quickly.

### Prerequisites

Before proceeding in this article, you should be able to check the following boxes:

- Know how to [set up a Python environment](https://arctype.com/postgres/drivers/python-postgres)
- Know how to use a command line
- Have admin access to edit windows environment variables

If you can handle all of the above, you’re in the right place!

## Setting up PostgreSQL

PostgreSQL is a free and advanced database system with the capacity to handle a lot of data. It’s available for very large data in several forms like [Greenplum](https://greenplum.org/) and Redshift on Amazon. It is open source and is managed by an organized and very principled community.

### Why PostgreSQL?

PostgreSQL is widely used for analytics and data mining, and getting help is relatively easy as there is an existing community. Also, its SQL implementation is very similar to ANSI standards. Most web application developers also prefer to use it, including those that work with popular web frameworks like [Django](__GHOST_URL__/decoding-django-sessions-in-postgresql/) and Ruby on Rails.

### Step by step installation guide for Postgres

For Windows, I advise using the [installer provided by EnterpriseDB](https://arctype.com/postgres/install/windows-postgres). It supports PostgreSQL, pgAdmin, and Stack Builder (from EnterpriseDB, which installs the spatial database extension and other relevant tools).

When the installation is completed, the window below is displayed. Here, an option to launch Stack Builder is seen. Make sure the checkbox is ticked and click “Finish.”

![Screenshot with prompt asking about stack builder.](https://lh5.googleusercontent.com/j3LrpMXy6yWJBSUKiouSOX-l7kY6r0e7TdU_DltRJmBdyt6IoDuoOS4tQZaPmYatWFkBw0ukwT84xUZvfJQenb84UA8G0bEyNyZPkJ5pAlb2QdeVTtnmTXPWB2L4rszOO8XcbU0f)

When Stack Builder launches, navigate to the drop-down menu and select the PostgreSQL installation. Click “Next.”

![](https://lh6.googleusercontent.com/8qi_uCWCdVsuXhIBnjC9pcfaI9yexiPo6vD0aOvcTm6d3WbtMMMnXIF8dY7AvGg2lgIruVxOabsvk_muAMwuolD15mqIevWUIKurBAyWqcUv4jjlyY8CM-D-pYefKMEDuEbHBtG9)

A list of applications to be installed is displayed on the next window. Expand Spatial Extensions and check the box for the PostGIS bundle for the Postgres version you downloaded. Next, navigate to “Add-ons, tools, and utilities” and select the latest EDB language pack. This provides support for programming languages, including Python.

![](https://lh3.googleusercontent.com/v6zSjTXb8aRo1rfN_U8goU26Z1mc-qRWURLXVMQz_m4ndh9ktzp56bLotjx6C3few_DCsyDj6KnhBWVcgz7BPpeDza5Y5LBudejNODU0_jM0FDldZenoVh-iW2blKZjQc75dlPTv)

Click “Next” over the next couple of windows and then wait for the installation files to download completely. When this happens, the window below comes up. Leave the “Skip installation” box unchecked and click “Next.”

![](https://lh6.googleusercontent.com/H4K7oeJda8n1RK4MQCH5cj37AefRmQeubqYXs4DeHHJE_wMripNhmB4DqnjotTvqareqIYGIr-6ABIiFGFXoafyyBRsjBs71xYghDr24nNc4738oy6L150nlVQXmc9HzbVdjZnFZ)

Follow the instructions on the following windows to install the EDB language pack. If asked for your database password, enter the password that was set. Also, answer “Yes” every time a window pops up about setting environment variables. 

When finished, a folder containing shortcuts and links to the PostgreSQL documentation should be on your Windows Start menu.

## Configuring a SQL Client

You can use any SQL client to follow the rest of this tutorial but we will be using [Arctype](https://arctype.com). We will create the database and import CSV data which we will then write queries against using PySpark.

### Connecting PostgreSQL with Arctype
![Screenshot of Arctype database connections.](https://lh6.googleusercontent.com/nX1JefTvNS33PXSOMpIpkHSUQpuIzGUaN8On-N3spdTVw8wSblY7sWnfvY6JELbJzMI1wQe0PJq0Vf5LClgaSSZUA7rtIYmzPdPymxTXxYXm-GwcLUrAVNytYZTsDx73c4_iaGcA)

The next step is to connect your database with Arctype. Currently, [MySQL](https://arctype.com/mysql/connect/aws-ssh-mysql), [Postgres](https://arctype.com/postgres/install/docker-mac-postgres), and [PlanetScale](https://docs.arctype.com/connect/planetscale) are supported. As you may have figured, you will be using Postgres, so click the Postgres button. The window below gets displayed.

![Adding connection info](https://lh4.googleusercontent.com/961VTgW37wEJ8xwKrcZjphrg6n6LuR6QH_6-54Ol-x_gYzyqba3Xl_2-b3lATGCQSQH_GomCjz82ddPZ9ujWwQo6aD9qkS3NBDWpZoWX0e7pqM6bm6LSOkudq4LJq136TE4IKjjc)

Enter a name for the connection if you are not fine with the default. Next, enter the username and password you defined for your database under “User” and “Password,” respectively. Finally, enter the name of the default database and click “Test connection.”

You should get a response stating that the test was successful. Once this is done, click “Save.”

![Screenshot of a successful connection in Arctype](https://lh4.googleusercontent.com/kzjC1nbaHXDsSO8mtzTWxmeir2RKHs29sVdbrp5dvPeZVvi8SYIo_lYLtJGwQHw09J9W_is_ap5xJ0mzKuN5MKVAaXgbHX9t-8V7TXEBn66bJHMCN6BcVsKzItq16HNeukNyJe1C)

### Importing data into the database

First write a query to establish the database: `CREATE DATABASE analysis`. To test SQL queries, let's first import our data. To start with, select the “Tables” option on the left menu. Next, click the ellipsis to the right of the “search tables” field and select the “[Import CSV to Table](https://docs.arctype.com/queries/importing-a-csv)” option.

![Screenshot of CSV import process](https://lh4.googleusercontent.com/Eq1ExhHkiBv_lXlvkfy9TdToHE4YTAVui57dTdpAWw_Chw-j16ykB2ZQHQYdlBs7LH0aixuQAHY8hd8evKfyRUejjonFiVc_jVmcRhFhIkOvN07QVklEvQY8nuIgC7ZaDU7-cm_f)

Next, navigate through your directory manager and select the file you wish to open. A preview of the CSV file’s content gets displayed, as shown below. Click accept if all things look as they should be.

![Screenshot of imported CSV data](https://lh4.googleusercontent.com/9L8g3zqEyOZjiT4850EZXree6XZLaHyAtURimSpNFhsr2f55Y8--Epymnor_TRihN_DKeqxaiBvwqutyYhPofeavWmsXlRKNMHb0LRDGioBt9dW8sKS5fnaqWXy9KBXzfxdnGoMd)

The window below appears. Change the name of the table if you deem it fit and click “Import CSV” on the bottom right of the window.

![Screenshot of Arctype table creation.](https://lh4.googleusercontent.com/vz8Bg3t89jzNP6P_bo0olkm_obhgMo_m4GGljlNFUlR8Hu0kEdSJvAk0wju4PRNOJ4eYrKAMqsGIGl-L7Disn_c7ijtRj1T4wWk1ViEWF8UhqyuZbdYawSM-BGV829oT79RwfFUL)

Voila! As seen below, your CSV file and the corresponding data are now imported.

![Screenshot of finished import](https://lh4.googleusercontent.com/oxMLVEvlJV3TJq3xiZVWjL3q-fnmck5ZXVmCnSCytek-vtpcDayKNrDTUBSuCDUQRv-ag1wy9KAH062Wu0sI-pr-iCaPx8biNQNhm9DKNI6jKGZ89Dgi5b1Jzre3CVfiTqjFCKqq)

### Visualizing your data

Visualizations help with understanding the nature and distribution of data in your database table. We can make a couple of charts and graphs before writing queries in PySpark to get a sense of the dataset we are working with. First, navigate to the column headers and change the type for the numeric columns, namely: `age`, `bmi`, and `charges` to int4, float4, and float4. This is done so you can interact with these columns as numbers and not strings.

![Screenshot of visualization edits](https://lh6.googleusercontent.com/SCOQBxZHMyt4nEbIAGtsXMTSoRbMbW4i2cLrxu0kflMl1oUVF2mV8fTiJ3bNdSl2gf4w29xwj8tQ0kSbUyphBT19b_3R4b-hgCqIva8absMoxrqECzqz0Cfl-bsc9AB1wlmBXdS6)

Once this is done, click the “Queries” option and create a new query by clicking on the first option in the now open queries pane. A file named “Untitled Query” is created. In this file, enter the following query to group the age column by unique ages and count their number of occurrences. Click “Run” to see the output in the output pane, as is shown below.

![Running the query](https://lh5.googleusercontent.com/HFaO247auqW-5BRUKPWQ7Y9UiniOfjrhKTbC47vXHgv-AkcmG1gkmrDlmH5zdcu1n7kCO5RM0y3D9-P-gOvpgRyJ2_Y4rL7S5V71BuVtml5sOAq80YuH-_GGZhYnyviElmFGzrjj)

To generate a chart for visualization, click the Chart option on the output pane menu. A window gets opened to the right. Here, you can select “Bar Chart” as the chart type. 

![Chart builder screenshot](https://lh3.googleusercontent.com/4hNEaDhW2aixY90W1Tp7hBG7rFR0_YBgfzGgypgXTPUbZCmxSIp2XyVDzOqLv1Q0wBc9bv75nNefop0Q_1U2iyoFh6PqIEyQZems_RQOKCFfJBKwAPOH1Ce4O0Izz8S3NvNt0Y0_)

Next, drag down the age and sum_age columns to the x-axis and y-axis space on the bottom right. A bar chart is created and displayed, as shown below.

![Screenshot of bar chart in Arctype](https://lh4.googleusercontent.com/nrzh1QmiCH4_PvV0IjibV1IubhhkJq-NzWiI9oRelon0GkofF3VDpulYGlj_qG9nPHy71R--AsaIWj9-nQV9Xe2WBa7DXl40oLqhVaHk2ASx-6lDGyNYeQBYxGtS_JtWTnmM3A_N)

## Setting up PySpark

To get started with PySpark, create a virtual environment and install `pyspark` from PyPI with pip, as shown below.

```
> python -m venv .venv
> pip install pyspark
```

Then, import SparkSession from pyspark.sql and create a session.

```
from pyspark.sql import SparkSession

 # function to create SparkSession 

 spark = SparkSession.builder.appName("TrialApp.com").getOrCreate()
```

`pyspark.sql` is a Spark module for structured data processing in Python. It provides a programming abstraction called DataFrames and also plays the role of a distributed SQL query engine. `.getOrCreate` creates an existing SparkSession or, if there is no existing one, creates a new one with the options in this builder.

### Installing Java 8

Spark does not support support Java versions later than 11. Use [this link](https://java.com/en/download/) to download Java 8 if you don't have it already. You do not need to download the SDK; just the Java runtime environment will do. Check via your command line to ensure that you are on the right track.

![Screenshot of Java 8 install](https://lh6.googleusercontent.com/7W8NTbdWhMjI7i21fdi5t43tcuyl4UmjyZ3o6co2Zc6-iB2wo3_5EDk_dFEPe4MC_e2GnXBh45depGGCTFmwUx4-ZOdiIy7XCm_DKyIU-d1kf56BTuNrH2uff7NiU38Ci2e8DwPj)

Then, download and install Spark. Click[ this link](https://spark.apache.org/downloads.html) to download the spark installer. Make sure that you are downloading the most current non-preview version on the screen below.

![Apache Spark download](https://lh6.googleusercontent.com/IqfExQN-kw5ZpeHHci21a26ZVERBytcAkmXGRcJGuMkW9gXp3W7PbnkzZBYik5O9lX8cJ5Gxy37y5ntOYXolHIEhYpoedkn_OMPPGswSIWGVkH20m9yIagl_RR1bUdnEhhLa45zM)

### Verifying the Spark software file

1. Verify the downloaded file’s integrity by checking its **checksum**. This ensures that you are using an uncorrupted version and an unaltered version.
2. As you see on item 4 in the image above, the checksum link can be opened. Navigate back to that page and click it.
3. Next, in a command line, run the command below.

```
certutil -hashfile [path to spark download] SHA512
```

![Screenshot of successful hashfile command](https://lh4.googleusercontent.com/2CuZVaA5aEjvYXdNvnB0nyF-TkMkp9VYY_nWU2GmT5r_vUblQIJfDwU-FB_4yFkoA2sVosjZy-Cn6LNLhL0gSLoL6eenpegdlbkzu93RfeGUiiwXQywzCYu5FSS6YS7YHESweZ07)

1. Compare the code you see to that which you get from step 2. If they match, you are good to go.

### Install Spark

Create a Spark directory in the root of your C: drive and extract the contents of the downloaded spark file into that directory. You will now have a directory with the path `C:\Spark\spark-3.2.0-bin-hadoop3.2`. Verify that the extracted files are in that specific directory and not a subfolder.

Next, access [this link](https://jdbc.postgresql.org/download.html) to download the latest PostgreSQL spark connector JAR files with their dependencies.

![](https://lh4.googleusercontent.com/hXuPbbtVXWxbxzrovJQifWHyOkhSWHt3me4Ge_R3KISB_YmH7uxuAVl30NGpf9uYSEGCbXKX3lADwRzKknHYEUoo4yzd-4ErZTv0sqeY4_jeujaRnSd7szmvbouDMsvgejCDsUsb)

Save this file in the `C:\Spark\spark-3.2.0-bin-hadoop3.2\jars` directory.

### Add the winutils.exe file

Create a folder still in the root folder of your C: drive named `Hadoop`. Then, click [this repository link](https://github.com/cdarlint/winutils/), identify the bin folder of your Spark installation Hadoop version, and download the **winutils.exe** file.

![Screenshot of hadoop download options](https://lh3.googleusercontent.com/5q1XnTrzycHtcV-zy8vRh0n99iSfg9u4KFh1IEGTRGkgYgxC2l4wG9rPf8P3KDGGpP1PsOL_ycvvPxiqk6fbOKiTc2rS62RJbG5w8iLQ5URwikAuZqXDSmZXlO3B7LlxPtukbdq0)

Create a folder named `bin` in the Hadoop directory and copy the `winutils.exe` file into it.

### Configure environment variables

Next, we’ll add the Spark and Hadoop installation paths and bin folders to our system's environmental variables and PATH.  Search for "environment" in your search bar and click the ***Edit the system environment variables*** option.

In the dialog box that appears, click Environment Variables and then New under User Variables in the window that appears.

![Screenshot of env setup](https://lh5.googleusercontent.com/J2mawsExTppQh8GiYuOu1rADbl1A0H_NcbdneYDUf9_cs7CHJmccDsgTVSVhGJpShpSTpL535oROZffXFrnzgb66NIQJ7lS0tzg3KY308s3swbWdHtQRdGo_Xzc_pAp2TAwdLRpZ)

Enter `SPARK_HOME` as the variable name and the path to the folder where you extracted the Spark files as your variable value type. For example:  `C:\Spark\spark-3.2.0-bin-hadoop3.2`.

![Screenshot of env setup](https://lh6.googleusercontent.com/2jwqYsAUshjkdwmvlL-77Rknm9K2Egt4Htp6qgQZcaFjlgETMwv8kaZJDpUf_Do4NDGE6RA3QhHu4EZ21TEkaN0BrQ4Jo1Jw-cq8erhY7WdX7o8wpbDhCaA9pRUvaKiFA-sK8UkH)

Click the Path variable and then edit it. A new dialogue box appears. Avoid deleting any entries already on the list.

![Screenshot of env setup](https://lh6.googleusercontent.com/6At4M5f7L_E9kJCKnCJZIehUvgm_drgqxIQtJYzFWmPKZvcuWd71UjIGhq5pU11J7lzDd5SQuilK9g0gB0UYhG9uIrBpWZcegcuWvZmpCZEbhSPHLd3iAYgW0WfsZCDqJc_LUw0P)

In this dialogue box, towards the right, click the New button. The system creates and highlights a new line. Enter the path to the Spark folder's bin directory. Since you already created a variable, you can just enter `%SPARK_HOME%\bin`. The percentage sign serves the same purpose as curly braces in a Python f string. This has the additional effect of reducing the likelihood of errors in the path. Click OK.

Repeat this process for Hadoop with the following details:

- Variable name: `HADOOP_HOME`
- Variable value: `C:\Hadoop`
- Addition to path: `%HADOOP_HOME%\bin`

And for Java with the following details:

- Variable name: `JAVA_HOME`
- Variable value: `C:\PROGRA~2\Java\jdk1.8.0_301`

PROGRA~2 is a short form for Program Files(x86) and is used because the computer recognizes the occurrence of a space as the end of the path when you try to use PySpark.

![Screenshot of all env vars](https://lh6.googleusercontent.com/X-LStcoAAiz62csQKvZWpnWzCPMrlfIZmPCnrLrXG2csjfUYnIxW0Fy1e_YnMxstQnuODLQ1APtJ6YxJVeRQn6B42yBhwQk4GdMbXvt4kIp4rNp3SLKjRSY_jWyIc1C5WKEWLasT)

Click **OK** to close all open windows.

## Writing SQL queries in PySpark

Now, the next step is figuring out how to write SQL queries in PySpark. To get started, you need to connect PySpark with PostgreSQL.

### Creating a SparkConnector for use with PostgreSQL

```
from pyspark.sql import SparkSession

spark = SparkSession.builder \
    .appName("PySpark PostgreSQL") \
    .config("spark.jars", "path\to\postgresql-42.3.1.jar") \
    .getOrCreate()

# Read PostgreSQL DB table into dataframe
df = spark.read \
    .format("jdbc") \
    .option("url", "jdbc:postgresql://localhost:5432/analysis")\
    .option("dbtable", "insurance") \
    .option("user", "postgres") \
    .option("password", "********") \
    .option("driver", "org.postgresql.Driver") \
    .load()

# Display database table schema
df.printSchema()
```

In the above code snippet, you first import `SparkSession` from pyspark.sql. Then, you create a Spark session. To do this, you call a series of methods on SparkSession.builder and pass in parameters. 

- `.appName` method which takes in a string which represents an identifier for the app you are creating.
- The `.config` method which sets the location of the earlier downloaded postgresql jar file. 
- Finally, you call the `getorCreate()` method to create the session or get it if it already exists.

With the spark session created, you move on to read the earlier created database table into a spark dataframe. To do this, we call the `.read` method on our earlier created session and set the format to “jdbc”. Following that, we have a series of options representing details like the url of the database, name of the database table, username, password and driver. After these options, we call the `.load()` method to create the dataframe.

Finally, we print the database schema to confirm that the connection was successful.

![Confirming changes succeeded](https://lh5.googleusercontent.com/QfR3kCozhZX71IjZhit4Ae3NkbCeOOhgXIHA0GdRBkkA4WoZDZ0XNwvhjx5edXZzlCMkW6DF4MYrVqYLWMEeRjZ8ddwN5dI0Z0cOqGTxjz-8OC6nnIiw4bcfjE90Mk-DsD67WUfX)

### Selecting columns

```
df.createOrReplaceTempView("insurance_df")

df2 = spark.sql("SELECT AGE, SEX FROM insurance_df")
df2.show()
```

To select a column from the database table, we first need to make our dataframe accessible in our SQL queries. To do this, we call the `df.createOrReplaceTempView` method and set the temporary view name to `insurance_df`. 

Next, we call the spark.sql method, enter the SQL query as a string: `SELECT AGE, SEX FROM insurance_df` and display the query results.

![](https://lh6.googleusercontent.com/B47aQn6BFYl_9Vb2w_ycWczLzlGZ9fn_7jdUaf7i36fouLX3-fD-R-dq0_szhuO4czVpr-EfmxAXNKfaKku1_a-l6B6UzVzsktkuPnz20d_Zg94owb6weppqYMDIsHeZxS3uNMiT)

### Outputting data using constraints

```
df3 = spark.sql("SELECT AGE, SEX FROM insurance_df\
                WHERE AGE>=18 AND SEX=='male'")
df3.show()
```

Now, you create a new data frame, and in this case, select the AGE and SEX columns for just the rows where AGE is greater than or equal to 18 and the SEX is male.

![Screenshot of resulting output](https://lh4.googleusercontent.com/lbFxIDfb6Pr6QDhGX6iXV2Kv4pmIazL8UKLYvAoMGpjxI_eGghk-KTvQNgh7BruDrPnaWxJ6rMqGT51nYGEGeIAQJlqpTxVG0boS7N3f_wd0xl7KtLIAWiFkZS5GkppeuqWorluw)

### Outputting aggregated data using GROUP BY

```
df4 = spark.sql("SELECT AGE, COUNT(AGE) as COUNT_AGE\
                FROM insurance_df\
                GROUP BY AGE\
                ORDER BY AGE;")
df4.show()
```

Next, we write a query similar to what we added when we visualized results on Arctype. Here, we group the table by age and count the entries for the unique ages. Then, we order by age in an ascending manner.

![Resulting output](https://lh6.googleusercontent.com/Xd9jDZ2jTL6egthjV4CAAbLLXc2Vj5Kdrox12XeWqT8OvJ6pMJ1etUGfRqdoev-PhLfTVOerPrrhAJB754AxH2oyKLxbiTQzektbHPUMvClAGK4chRJXG7dPufCUpqGDpcOEWcFx)

### Switching the values of two columns in a table

```
df5 = spark.sql("SELECT AGE as CHILDREN, CHILDREN as AGE\
                FROM insurance_df")

df5.show()
```

In the query above, we simply select two columns from the insurance table and switch their column identifiers. By doing this, we have essentially changed the values that correspond to each column by doing this. The output can be seen below.

![Screenshot of resulting output](https://lh4.googleusercontent.com/J9p7DPRUe-v7dMaRwqajOrhgiJ_nSskeW_gfG1pDKXceU6MTN-HF-f77RrPjbmuKxNhtdlijuxh0nOGMNM15uQesr7pX2q-1TratTl2nl6K77gVKXaG1sOqHmNyDVXMb4iOAVKfC)

## Conclusion

In this tutorial, you have learned about installing and setting up PostgreSQL, [Arctype](https://arctype.com), and PySpark to achieve the goal of writing SQL queries in Python and visualizing the results of queries. This is particularly handy if you are working with very large datasets that will perform better using technologies like Spark.