There are lots of ways to populate data into a database but again, this is an MVP.įor this tutorial, I’m using the Google Analytics Geo Targets CSV list found here. The imaginatively named, generates the SQL commands to populate tables based on a CSV file’s contents. There are tools out there that will shortcut that process for us too. You might not yet know how to run CREATE TABLE commands but that’s ok. You have to tell the database exactly how each table should be structured and then what data should be added to each table. Loading tables in Postgres is a little bit different (aka more involved) than loading a CSV into Google Sheets or Excel. Next, let’s load some actual data into Postgres. Ok, the boring stuff is out of the way and it’s only been about 15 minutes! Now we can get to the actual analysis. You’ve done it! You’ve started up a Postgres database, connected to it, and run your first query! Loading data into Postgres Since you haven’t loaded any tables, you’ll just see a list of Postgres system tables that start with the prefix, “ pg_.” As you probably guessed, the “ pg” stands for Postgres. Run the query “ select * from pg_tables ” to see a list of all the tables in your Postgres database. Step 5: Test your connection by running a query.Ĭreate a new “Favorite” connection in Postico.Step 4: Go back to Postico and choose the SQL Query icon.Leave all fields blank the default values are suitable for connecting to Postgres.app. Step 3: Create a database connection by clicking on the “New Favorite” button.Again, it has a simple Mac UI and is designed for more of an analytical workflow than hardcore database management. There are plenty of good SQL editors for Postgres but since we are keeping this MVP, I’m going to recommend Postico. Your Postgres server is up and running Connecting to Postgres Now let’s see how quickly we get connected to the database. And here’s a list of Postgres clients for Windows that you’d use instead of Postico. Here are some instructions for installing Postgres on Windows. Step 2: Move the app to the Applications folder and then open it by double-clicking on the iconĬongrats! You have installed a Postgres server on your local machine and it’s up and running!.Step 1: Head over to and download the app.It wraps everything in a shiny Mac UI and the download experience is no different than something like Spotify. The easiest way to get a Postgres server running on a Mac is Postgres.app. It won’t really matter once everything moves to the cloud, but I don’t own a Windows machine… I apologize in advance but this tutorial will be for a Mac. Ultimately, you’ll be working with BigQuery, Snowflake, and if this were 2016, Redshift. Postgres isn’t the first thing that people think of for massive-scale data warehousing, but it works pretty well for analytics-especially at this scale, it is definitely the easiest way to get started and of course, it’s free. Once you start to work with data in this way, you’ll recognize that the process is a lot less error-prone and repeatable than spreadsheet work because the code is simpler and it’s easier to retrace your steps. Setup any continually updating data syncs for “live” reportingīut don’t underestimate this.Run anything on a cloud server (or be able to share reports beyond your computer).Have basic tooling and process for a repeatable analytical workflow.Write a SQL query to replace a pivot table.In the spirit of a true MVP, This first step is going to get you from spreadsheet to SQL and with the least amount of overhead and a base level of utility. Being able to think about data at scale will change how you approach your work and being able to work at scale will increase your efficiency and impact. The hard part? You have to pull yourself away from spreadsheets for a while-go slow to go fast (and big). Now is a great time to upskill and become more effective in almost any career take advantage of the demand.The tools have caught up and basically, anybody with spreadsheet skills can set up a data stack that works at scale.There are plenty of resources to learn SQL but the path to using it in the “real world” isn’t very clear.There aren’t enough people with the skills to work with data at scale.The scale and application of data still growing ( duh). I didn’t expect to be called to account for it.Īs it turns out, the post was pretty popular and I think I know why. I boldly claimed that “A spreadsheet is just data in single-player mode.” And while I stand by that claim. “A spreadsheet is just data in single-player mode” JFrom Spreadsheets to SQL: Step One towards the Minimum Viable Data Stack
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |