#Google+ #GooglePlus #Takeout #Shutdown #JSON #PostgreSQL

#Google+ #GooglePlus #Takeout #Shutdown #JSON #PostgreSQL

I rarely do public posts this technical... I'm not sure I ever have but since the reality is that the G+ shutdown is happening, I wanted to share my thoughts on using Google Takeout to get my data. I'm also posting this to the Google+ Mass Migration Community in hope it might help someone else.

~ ~ ~

Let me give you a quick reason for this post. What I think pains many of us who really embraced G+ and to be honest, are very upset about this shutdown, is that we spend a LOT of time posting here. As of my last takeout archive on 2019-02-13 I have made 4237 posts since 2011-07-10 (you'll see how I know that later). That may seem like a lot but it really isn't compared to other people I know. The number isn't what's important, its my time and that is something that can't be quantified because time is invaluable.

For that reason, the prospect of losing the results of nearly 8 years of my life spent here was really getting to me. More so than the shutdown itself and more than considering what comes next (and I still don't know that one either). The one thing I needed from Google was a way to give me something so that this wasn't all for naught.

Enter Google Takeout...

First thing's, first. It DOES work. Mine came to about 103Gb split over 3 files. What was happening to me and other people was that there is an error showing and did not effect overall data fidelity. Once you get all the files, you'll see that there is an index.html that shows you what Google Takeout could not retrive for you. There is a very high chance that is probably just a media file of some sort.

I don't know about you but that was not critical to me. Anything I produced is part of Photo's and truth be told the archive would have been smaller is that data wasn't included but having duplicate is fine (since, who knows where Photos might be taken from us).

Ok, so now that I have my data, now what?

This where things are going to get technical but that's because it has to be and for those of us who are technically inclined I hope this gets you started.

Having my history is one thing but being able to refer to it in a meaningful way is another matter. In my case, what I wanted was something that was searchable so that regardless of what I do next, I can look up my past writings if for not other reason than to see how I've personally evolved or to undestand the contexuality of something from years past.

Enter PostgreSQL... a database system

Just about every major application I've ever written uses PostgreSQL (I could go on for hours about how much I love this system but that's out of scope and you don't care). One of the more recent features is the abilty to work with JSON, including an ability to query elements directly and index or do pretty much anything you would do with normalized or "proper" data structures. Basically PostgreSQL can handle SQL and "NoSQL" workloads in the same system.

Furthermore, PostgresSQL can also do full text indexing which means that I would be able to efficiently search my posts in a more "web search" style.

The trick is getting the JSON files you get via Google Takeout into a table in the database system. I say "trick" because Google provides you "pretty" JSON files (aka human readable, if want to call it that) and those are NOT compliant with the JSON spec so you have to do some basic manipluation of the files first. Furtunately, in Linux this trivial.

To recap so far...
1) Use Google Take to export your Google Plus data to JSON files
2) Download and extract all the files to the same path

You'll have a path that starts with Takeout/Google+ Stream and then you'll see Posts, Photos, Events and ActivityLog

3) Create a database with a table similar to this...

CREATE TABLE public.postjson
(
id integer NOT NULL DEFAULT nextval('"Posts_id_seq"'::regclass),
data jsonb,
CONSTRAINT "Posts_pkey" PRIMARY KEY (id)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;

4) Import your data correcting for JSON formatting issues (i.e. remove the newlines) and also escaping specialty characters. Linux / Unix people, you know this drill. Here's what I did initially from the Posts level...

for f in $(ls -1 2011*); do cat $f | tr -d '\n' | sed 's/\\/\\\\/g' | /usr/local/pgsql/bin/psql G+20190213 -c "COPY postjson (data) from STDIN"; done

What this does is find all the files that start with 2011 in the "Post", removes the \n or "newline" character and then converts all the \ characters to \\ which is something we have to do in Unix systems to get the bashslash treated like a bashslash (and not the character that starts and escape sequence).

The psql command line database tool and then import the data. In may case, this successed without error and all 95 of my 2011 post were ingested. I just went year by year after that.

5) Create views and indexes to "see" and "search" your posts

As you can imagine not every post you have made has the same information b ut there are some things that are common. So as basic starting point you can do something like this:

CREATE OR REPLACE VIEW public.posts AS
SELECT postjson.id,
postjson.data ->> 'creationTime'::text AS creationtime,
postjson.data ->> 'updateTime'::text AS updatetime,
postjson.data ->> 'url'::text AS url,
postjson.data ->> 'content'::text AS content,
postjson.data ->> 'comments'::text AS comments
FROM postjson
ORDER BY postjson.id, (postjson.data ->> 'creationTime'::text);

That will give you a view of your table with those common fields. Your post text is the "content" column and comments is , you guessed it, the "comments" column. What each of those two fields comtain is a combination of HTML and other JSON fields but as least now you have something that you can query against with typical SQL query language. For example, lets say I was looking a post I did about my track time at Monticello (this was my second post on G+ so I knew it was there)...

select * from posts
where content ilike '%monticello track%'

The above search, is a basic SQL query that is doing case insensitve search for the phrase "monticello track" an my post text. This doesn't return anything because monticello and track don't occur next to each other. I could search for each word individually but that is going to contain more false positives. I would also do a more complicated SQL query but by using full text indexing I don't have to and FTS is suited to this type of query. Lets do that for content and comments (just in case I ever want to search those too)

CREATE INDEX postcontent_idx
ON public.postjson USING gin
(to_tsvector('english'::regconfig, data ->> 'content'::text))
TABLESPACE pg_default;

and

CREATE INDEX postcomment_idx
ON public.postjson USING gin
(to_tsvector('english'::regconfig, data ->> 'comments'::text))
TABLESPACE pg_default;

That same query would now be:

select * from posts
where to_tsvector('english',content) @@ websearch_to_tsquery('monticello track')

and properly returns the post I was expecting. You don't strickly have to create the indexes but on my system using the index is 67% faster (or it takes 1/3 of the time to return the query compared to doing a sequential scan of all the records).

That's pretty much it! Now I have a basic ability to search and retrive my posts (and comments). There are other things I can do with this but now I have a solid starting point.

6) What about a subsequent dump?

One thing that I'm going to do is another dump close to the end of February. I'm still not clear on what is actually going to happen in March. Since you can't select data ranges and have to download everything again, you can make a unique index against your JSON content field so that doing the same import steps will prevent duplicates.

CREATE UNIQUE INDEX postdata_idx
ON public.postjson USING btree
(encode(digest(data::text, 'sha1'::text), 'hex'::text) COLLATE pg_catalog."default")
TABLESPACE pg_default;

You'll need to include the pgcryto entension in your database on PostgreSQL for this but what is give you is a SHA1 hash against your post text which is how you can fingerprint something digitally.

Finally, I know this I've omitted quite a bit so here's a link to the complete schema you can use to build what I'm using now in your own database. This was designed around PostgreSQL but you might be able to port it to something more to your liking. Again, I hope this serves as a starting point to ease the anxiety of someone else.

Good Luck!

https://drive.google.com/file/d/1yvzowNUIhHL1ffNX6ShzdPU9L9PS2Bwm/view?usp=sharing

https://drive.google.com/file/d/1yvzowNUIhHL1ffNX6ShzdPU9L9PS2Bwm/view?usp=sharing