Build Postgres Tables in Heroku (Community)

View the original community article here

Last tested: Apr 16, 2020
 

It's common to want to explore some data in Looker, this post is to show you a way to add data to a postgres database that can be connected to Looker. Note, as it's a free tier, you can have a max of 10,000 rows.

To do this, you will need an understanding of Python and some data!

To start, head to signup.heroku.com and create an account.

When you have an account, you need to create an app. To do this, go to dashboard.heroku.com and click New - Create New App (top right). You need to give your app a unique name, eg sam-test-looker

​​

You now have the framework for an app, from here you can do all sorts. For example you can host a flask app on Heroku. For this card, we are going to to add a database to our app. In Heroku, a database is classed an Add-on. We are going to add Heroku Postgres as an Add-on to our app. https://elements.heroku.com/addons/heroku-postgresql To add this, you need to click Install Heroku Postgres (top right). Then search for your app in the search bar. Make sure the Add-on plan is Hobby Dev - Free. Then go ahead and click Provision add-on.

​​

Head to the database overview page by clicking on Heroku Postgres, which should be in your list of Add-ons. When there, you will see an overview of your database. Click on Settings in the ribbon, and then view the credentials. This is what we will use to add data to our database. You can also use these credentials to add your database as a connection to Looker. Right, the database is set up, let's add some data! So head to an IDE, I'm going to use Sublime for this.

To insert data to the database, we will be using a Python module called sqlalchemy. You can install this with:

pip install SQLAlchemy

Below is some sample code that will create a table and then insert some data into it. To run the code, you will need to get the URI from the Credentials page. It will start with postgres://

from sqlalchemy import create_enginedb_string = "postgres://YOUR_POSTGRES_URI"db = create_engine(db_string)# Create db.execute("CREATE TABLE IF NOT EXISTS users (first_name text, last_name text, company text)")  db.execute("INSERT INTO users (first_name, last_name, company) VALUES ('Sam', 'Pitcher', 'Looker')")# Readresult_set = db.execute("SELECT * FROM users")  for r in result_set:      print(r)

This will then add a table to your database called Users, with one row!

Most of the time though, you will want to upload a csv file or similar to your database. To take this further, we are going to do just that. I have chosen some football world cup data from Kaggle.

https://www.kaggle.com/abecklas/fifa-world-cup#WorldCupMatches.csv

Then back to Sublime, we can automate uploading this csv file with the below code:

import csvfrom sqlalchemy import create_enginefrom sqlalchemy import Table, Column, String, MetaDatafilename = 'WorldCupMatches.csv'table_name = 'match_table'with open(filename, newline='') as csvfile:	data = [row for row in csv.reader(csvfile)]	column_names = data[0]	print(column_names)db_string = "postgres://YOUR_POSTGRES_URI"db = create_engine(db_string)meta = MetaData(db)  a = []for column in column_names:	a.append(Column(column, String))table = Table(table_name, meta, *a)with db.connect() as conn:	table.create()	for row in data[1:]:		insert_statement = table.insert().values(row)		conn.execute(insert_statement)

Sweet! Right, to Looker! From the creds page in Heroku, you need the Database, Host, Port, User and Password. Then go to add a new connection in Looker. Select the Dialect as PostgresSQL and enter the Database, Host, Port, User and Password. Then get modelling!!

​