From the course: Data Management with Apache NiFi

Set up a PostgreSQL table - Apache NiFi Tutorial

From the course: Data Management with Apache NiFi

Start my 1-month free trial Buy for my team

Set up a PostgreSQL table

“

- [Instructor] In this demo, we'll construct the flow to do something a little more meaningful. We'll read files present in a folder in our local file system, and parse the data in those files, and save the records in those files to a PostgreSQL table. This means, in order to perform this demo, you need to have PostgreSQL installed on your local machine. Let me show you where you can download and get set up with PostgreSQL for your MacOS and Windows devices. For MacOS, head over to postgresapp.com/downloads.html. This is, by far, the easiest way to get started with PostgreSQL. Just click on the Download button for the latest release. This will download a DMG file, which you can then install in your Applications folder. Follow the remaining instructions here on this page, and you should be all set up. This is very straightforward and should take no more than a few minutes. If you're on a Windows device, well, things are just as easy. Head over to postgresql.org/download/windows. Just download the interactive installer by EDB, and this will walk you through the process of getting set up with PostgreSQL on a Windows machine. You can see that I have PostgreSQL 15 running on my Mac device. You can see my Postgres server named loonycorn that is up and running. Notice the version of PostgreSQL. Other version should work as well. Now, I also have psql set up. psql is the command-line interface that will allow me to run queries on my PostgreSQL server. If you want to quit, you'll need to run quit to exit from the PostgreSQL command-line interface. So you know the commands to get in and get out, let's run psql once again. I'm going to create a database within my PostgreSQL server. Let's take a look at the user that was automatically created when I set up my database. SELECT usename FROM pg_user. You can see that loonycorn was a user that was automatically created. Let's set a password for this user. When this user was automatically created, it did not prompt me to assign a password, and I'd like this user to have a password. The password is simply the word "password." Now, let's create a database, and within that, we'll create a table, and this is where we'll store the records read from the file system. The database is called cars_db. It has been created successfully. Let's connect to this database using \c. And we have now successfully connected. Now, at this point in time, there are no tables present within this database. \dt should give you a message that there are no relations. Let's create a new table within this cars database. This is a table called cars, and you can see the columns that I've defined for this table, brand, price, body, mileage, model, and so on. The table has been successfully created. This is the table that we are going to populate using a NiFi dataflow. Let's run \dt once again to confirm that the table is present, and you can see that it is.

Contents