SQL (the quick and dirty way)

Navigating SQL is daunting at first, but completely doable, even if you have little programming experience. I’ll walk through how I ran SQL queries against my Chrome browsing data and the tools I used to do it.

Finding the SQL database

First, make sure you know where the SQL Database is in your computer, and make sure you have the appropriate permissions to access it. For example, when I was accessing my Chrome browsing data, I needed to get to this location on my computer:
~/Library/Application\ Support/Google/Chrome/Default/History

Where “~” means my home directory and “History” is the name of the SQL database file.
If this is daunting, don’t worry! This is what you’d type into a command line. If you don’t know what a command line is, either Google it or come talk to me — I’d be happy to walk you through it. *Note, if you’re looking to access your Chrome history database, make sure you’ve quit out of your Chrome browser. I learned this the hard way.

Notably, the Chrome history database is SQLite and not SQL. For our intents and purposes, this is fine. Functionally, they’re similar in usage; SQLite is a subset of SQL. Just be sure you know what type of database file you’re working with before you start.

How to access and browse your database

So you have your database file. How do you access it and browse through it? The tool I use is sqlitebrowser, made specifically for SQLite. You can open a database file or even create your own. Once you open your database, it looks something like this:

Screen Shot 2016-02-17 at 4.33.18 PM

You can browse through the data row by row, view the structure of the database and execute SQL commands. The “table” dropdown refers to all the different tables in a database; for the Chrome history example, there’s a table for downloads, a table for URLs, and a table source for visits.

It’s great that you can view all this information, but you’re probably also looking to make some meaning from this. To extract rows that are relevant, you’ll want to write a SQL query. I’m not going to go into the details of writing SQL queries here, but I do recommend W3school’s tutorial for the quick version which should be good for most basic queries.

In general, your queries will follow a structure that’s something like this:
select *
from "urls"
where "last_visit_time" > 13099253131722513
and "url" like "%facebook%"
or "url" like "%twitter"
or "url" like "%github%"
or "url" like "%linkedin%"

“select” refers to which columns you’d like to select from the table (I just choose to display all columns by default), “from” refers to which table you’re using and “where” acts like a conditional — if x is true for row i , then include row i in results. You’ll notice my use of “%” for matching strings — these are wildcards (and is easiest to Google as needed).

Below is what’s returned when I run the SQL query I wrote above:

Screen Shot 2016-02-17 at 4.39.52 PM

You can also group together results (like I could’ve grouped by “url” to see how many of each type of URL i visited) and sort by a column.

If you’re new to programming, this probably seems overwhelming, but I definitely think reading the tutorial and just playing around with some SQL queries will help you get the hang of it. I learned basic SQL by having a test database and running queries to figure out what did and didn’t work, as well as how syntax works.

If you have any questions about the post, about databases or about SQL, please reach out! I’d be happy to chat. 🙂

One thought on “SQL (the quick and dirty way)

  1. Pingback: What happened when I kept track of my media usage for a week? – Sravanti Tekumalla

Comments are closed.