Welcome everybody, you're watching mr fugu data science. Today, we're using
cycop g (psycopg2) two. To interface between python and Postgres.
We will use what is called h store. It will allow us to take
semi-structured data or unstructured data and use it within Postgres.
Before, i get started: i would like to say thank you to the recent subscribers and
viewers i greatly appreciate it. The data that
we're starting with will look like this: where we have three
columns that are employer, year began, year end
for their employment. And then this dictionary let you see:
which is just some oddball column with key value pairs. And we need
to figure a way to store this. After we import it
into psycopg it'll look like this weird formatting.
Where it's converted into strings with this weird
arrow notation denoting your key value pairs. But, what
really are some practical uses for this? First off it's really good if you have
something that's semi-structured or unstructured. So you kind of represent
similar to i guess you could think of like a
document database would have it set up in essence. You could still perform the
same types of updates, inserts, triggers, etc. One example that you
could think of would be: if you decided to store some data. For
instance if you had some kind of administrator for your
database doing constant updates. And you wanted to perform some kind of
history; you could use that column to basically store
where they were accessing data and what they updated and who updated data
for instance. Something to take into consideration:
when you're using these key value pairs if you have duplicates.
One of them will be saved but, you cannot guarantee which one. So one of the
upsides of using this you don't have to pre-define any types of keys. You could
just store all your values in a single column: which gets turned into a string.
Which is also kind of one of the downfalls. Considering you're
now limited on the data types that you can use. You also have something
that's useful: which are different forms of indexing
g-i-n and g-i-s-t. If you're interested, when you download
this file you can look at some nuances between h-store json and json binary
formatting and use cases. Let's get into this you will need to
import psycopg2 extras. And specifically the function of
register h store. That's going to let us create our dictionaries and basically
store and run some queries. You need to also import just a regular
psycopg2. We're not going to use pandas today. I'm also calling in my
config functions. If you do not have config
functions to store your user credentials, feel free to login
using this type of setup here and see if that works for you.
I'm calling in my config function which is storing my key value pairs that i'm
calling in to create the connection. The cursor
is utilized to send and retrieve the data as well as
run queries. I'm using auto commit equals true in order to
send my data or my queries immediately. Here's what our data looked like.
Something was very realistic. You have a list
of tuples where, you have a string an integer, integer and then your dictionary and
then you notice right here this tuple and you have the column. So that's
specific with this. All right, so we have person who's
employed by Han Solo who started the year 1979 they left in 2019.
And his first name is Chewie his last name is Wookie and
his occupation is a thrill seeker. Let's get into creating a
function, that's going to allow us to set up our schema.
First, thing that we need to do is create an extension
for h store. Which will allow us to basically interface.
Then i'm going to drop the table if it exists.
Which i will call i don't know i'll just abbreviate,
abbreviated for the h store dictionary practice. But, i'll just call
it like this. And then we're going to create an
unlocked table, with the same name that we just had.
And we're going to take in; what? I would like to have an
id that's not null, and it's a primary key where we will also take our first column
which will be our employer. Which is going to be
texted. We will take the year they started their employment. Which would be year
began which is an integer. The year they ended their employment
which is an integer. As well as our new column we're
going to call. I'm just going to have some fun with it
and call it fun column info. And this is your h store, all right.
Then since i already have this started. Take that out.
Well ,that's fine. Then we need to send off our
new schema using this formatting. There's other ways you could send off data. But,
here's one. We call in our create staging table
function with our cursor, we send that off. But,
you're going to notice something here. So you see this h store already exists,
right. Since, i already have that existing i'll just do this
and it'll work. But, for you since you're creating yours you need to leave that in.
Now, let's first do a simple example of inserting into one, so we could see that
this actually works. Let's first make sure that we don't
have anything so you know that i'm not playing funny business. There's
nothing in there right. Now so let's do our little insert. Let's insert
into our table: where we're going to call in the
employer, the year begin, the year
end. Let's do this.
This is going to mess me up later if i don't. Okay, there i just changed that for
mine okay, so you're beginning your end and then
our fun column info. Where what are we going to have
for an info. I need to call in some values that i'm going to place in there,
and they're all going to be strings all right okay.
Now, i have some just generic list that i'm calling Nemo.
We're going to have an employer which is everyone. We're going to have a year
of 1500 to 2020 for employment. And who's getting employed? The first
name of this person is guess what. Well we're gonna have some fun first
name is... Ooh, and i need to mention something:
there's very specific formatting for this.
You have the single string notation for the outermost part
and then inside of that, you're going to have
your regular strings. That are all going to be separated by doubles double
quotation. Then we're using our first name which
now is going to have that weird little arrow notation; and his name
is going to be Santa. Mr Santa has a last name
of Claus. And what is his occupation? Well, his occupation is a gift giver.
Alright, so send off mr; we make our adjustment, we send it off.
Let's print it off. And look we got a first entry, okay cool.
That's not very useful: you could see your first entry and all the other web
documentation. That's where this video gets interesting:
and no one else has done this, that i know of. Other than doing hard coding
individual entries. So this is going to be a two-step
process, all right. We need to get in essence: this formatting where we
have the single string, then our key which is the double string, separated by
this weird little arrow notation and then the double string. Anything
inside of that key value pair: dictionary will be
stored inside of this. And it finally ends when you close
out with the single string, okay. So there partially zoom out of this thing.
The first step you're going to notice this list
comprehension that we're going to us.e That's going to start doing the
formatting. The end of the first word will have the double string
the beginning of the second word: which is the value. Will have the
double string, and then we'll already have our separator. And then the
second part of it we will finish off where we'll have our
beginning of the first word which is your key double
quotation, and then the second word will finish out with it.
Let's start this. Now, iterate through the names
and we're going to use the first format. Oh
okay. iIm just going to store a temp variable which would be called first
format step. And that's going to store our list comprehension.
Then we have to store this in a list to append everything. So we're
going to take our first three original columns: which
would be your employer, your start date, your ending date. And
then we're going to call in our list comprehension: where we're
storing the first part of our formatting. We can print that off to see what this
looks like. You can see that we're starting to
format: where you have the end of the first word,
the beginning of the second word, and then you have your separator. Now we need
to get into doing the second part of this. Where we
need to iterate through the list that we just created. And now
we're going to store a generic temporary variable. Which is
going to be our second list comprehension
allowing us to finish out the correct formatting. I will create a
second temporary variable which is going to do
a string join for us separated by commas. And then
i need to append to the final format. So we can store everything.
The final format is going to look very much the same as the first part.
I could cut and paste these, and then we need to call in our new temp
variable. And we can look at what this is. So this
will actually go here. And this
goes here. Let's look at what the final formatting
looks like. There's our final formatting. Each one of those is separated by a
comma. We have our enclosed double
string notation for our key value pairs. Ending it
with a single string. Now, let's actually send this data off. This
is just cut and pasted from above: but, this is
inserting in to each one of our columns where each one is a string.
We're going to now send this off; so we need to
iterate through it. We're going to call in the employee
deal that they began the year that they end, the h
store now is going to take into account our iterator.
After, we call in those first two columns everything else would be stored
as the dictionary column. And then we can send
this off with a query; and the new list of
information we're going to take in. Which would be
the employer, the year, let's just add and paste all this.
Paste that in. Now, we run this we should have four entries.
we have Santa Claus, we have our Wookie, we have our Ewok
and we have our Harry. So that looks perfect. So now we have the id,
the employer, the year they started, the year they
end. Our key value pair information stored as a string.
Let's do some updating of mr Santa Claus where you you're
updating existing key value pair. So let's do an update just like
regular Postgres. Where we're going to use the set
operator; we're going to use set call in the
column that we're looking for. Storing our information and paste that
in and then you're going to notice that we're using a double pipe
and then now we have to be specific on the notation.
Single then double. And what we're looking for is the first name
and we need to use our special little pointer.
And we're going to look for Santa. But, we're going to change it to
mr Santa and i'll do an underscore why not. And then
you're going to say: where and what's the id for Santa?
Santa's id is one. So that should close us out.
If i spell it correctly, one day. Then let's finally look at this.
And you notice the ordering is different but Santa got changed to mr Santa. And
something i want to mention real quick. Why didn't i just do a fetch all on this
line? Because, it's not going to print anything.
iI's doing this update it's not just running a query. I'm actually updating
the information. That's why i just ran this simple query real quick so then we
can fetch it and actually see let it worked, okay.
We can do a few specific queries real quick. This is not an exhaustive list
this is just showing you what you can do. Let's select a specific key real fast. If
we want to do that we can use an operator that'll just be a regular
looking arrow. That's going to allow us to
search through all of our data. So let's go through our fun column info
and use this little arrow. We're going to call the first name,
we're going to call it an alias it is fn. And we're going to take it from the
table of h store dictionary practice. And there we are. But,
let's look at something real quick. What does this actually look like in
Postgres? Because, it's not printing out. We could create this as a data frame to
see. But let's look at Postgres. So let's run the same thing inside of
Postgres and you can see that this is what our column looks like
stored as these strings with this special notation.
Now, there's going to be some duplicates here: because, i've run this before. But
let's see if it changes, okay cool. So here's what it looks like: where i alias
this new this new column here.
You can see that we aliased this new column and here's what it prints off, okay.
We can take the column that's storing all of our key value pairs.
Let's look at what that looks like. So let's just you don't need to capitalize
this. But, i chose to just out of a habit. Let's just take everything from
it. And there we are. Basically we took
only the column that has all of our information
from a key value pairs, okay. You could do a where clause: that's not
too bad so that's let's just take the ending year
of employment and then take the column that has our
info stored as a dictionary. And let's use the arrow
to look through the whole thing. And find the first name
we could alias this as fn again. We could also
cut and paste and do the same thing for the last name.
What else do we want? We want to grab this from
our table. And then we're going to say from where? We're going to take it from
the column info: and we're going to look for what?
The occupation which is which is equal to magic.
So who's who has an occupation of magic and that's mr Harry
alright. Oops there we are, mr Harry P. You can figure that out, if you're
inclined to do so. Let's do a key value pair, and just take it by this
operator. But, here's something that's interesting with this where clause. Let
me step back. If we were going to take just the
year of employment where it ended with the first and last name
of the person. This would be useful if there were multiple people with the same
occupation for example. On to the next one. Key value pair is
using the at greater than symbol as our little operator. So let's
take our key value pairs and then let's select
the employer from our table. Where we're going to call in our cool
little function column. And let's throw in our little operator
and get specific with the notation once again.
Single then double, and then a little operator and then doubles again. Let's
find last name of Wookie and then one little
thing to make sure that we basically force it.
We're basically forcing this. Now let's find
something has a key.This is pretty easy; so let's select
everything from our table. Where we call in the column and then we use
question mark and then this percent s like your string operator.
And then now you're going to look for the first name.
There we are. So then we're going by the first name. But, this looks like we're
just printing everything off: which we are. Except for the fact that; if you had
row entries that did not have first name as one of
the keys then it would slice through everything.
And only taken the particular keys i have first
name. If we set this up where we had other key
value pairs that were not included in all of these.
Like if this one had a few more key value pairs and we just call those
specific ones for that entry. Then we could have saw the distinction
with this query. We could take all of the keys;
and that's a really easy query. So let's just select basically
all the keys. So A keys; and then we're going to call it from what?
Our column, our fun column with all the entries.
And then just call on the table. This is easy enough.
If you want to do; change this up and make everything into a set.
You just change this A key into an S. If you wanted to get all of the values:
we change this into A valves, easy enough.
If you want to convert this into json formatting:
we could select and let's just take the employer
and then h-store to json is the function that you're going to use.
On the column that you have for your data.
We're going to call it as json from our table.
And there you go. So now you can convert it. You could also convert it into json
binary too. There's so many things that you can do.
I would like to say: thank you for watching this video. Please, like
share and subscribe. And if you subscribe please turn on that notification bell. I
left in this a few useful citations that are very
helpful for other examples, okay. This will be on
my Github. I hope this brought utility to someone. Thank you for watching; i'll see
you in the next video.
Bye