002 - Reddit Bot - Submission - Transform - SQL\Python\Master Data Management

In this video, I go over the transformation process of the Submission JSON from the previous video.

======== Partial Transcript ======
hey everyone and welcome to another
video my name is Elric and in this video
I'm going to talk about how I'm going to
be transforming the JSON that these you
know that we're getting from the last
video the reddit submission JSON and how
we are going to build a table structure
that will transform it and tell our
system how to ingest it so we're
skipping ssis completely because it's
really unnecessary in a lot of cases so
I hope you enjoy and I will see you soon
so in the last video I was talking about
sequel executing Python which was using
the prowl pro library which was pulling
submissions from reddit from our sequel
and this was the example at the very
bottom of the procedure that I pulled
from so I'm gonna go how over how I've
transformed this using a table structure
so I have a database called our database
unit called quality main and history and
archive show me all the changes that
happen between these tables and right
now these are the only tables that I'm
using and as we get deeper into quality
assurance we'll have more structures
maintaining our system but it's pretty
simple the same realm class family type
structure with a mapping table base now
this is a table valued function I
created get mapping tree and actually
make this Knoll right now this allows me
to pass it all or any realm class family
type key names and to get some
information back basically applying all
of these tables together so you can see
that I have a realm of reddit API and I
have mapping classes for submission
redditor and comment and subreddit right
now and the family key name I'm choosing
JSON because I'm actually mapping JSON
if i were downloading you know exome or
if i was calling for xml or if i was
downloading files from an archive i
would probably not have this family i
would have the
family most appropriate for the format
I'm dealing with and the mapping type
key name helps me start to classify
where it will end up in our system so to
go further into that we have to look at
that base table that mapping so this
table value function shows us the class
family Rome class family type now to
look at the actual mapping table what it
is is the because this is JSON that
we're using that's all what I have
that's all I have defined right now the
JSON key and each element matches this
mapping source and because this is a
taxonomy system everything requires a
class a family and type this is
basically telling me where it needs to
go so we have post addresses and we even
have people for redditors posts for
comments and I believe I just have
submissions comments and redditors
defined right now so because of these
two things I want up creating a new
table value function that I can overload
because most everything stays the same I
really just want to be able to call one
function to get a JSON key name and a
destination where does it need to go if
it was a redditor you could see it goes
into people and we have eleven records
has verified email is friend gold etc
and submission everything changes and it
goes in the posts table structure which
is in my data main database and it's a
lets people let's post crazy looking
structure so that's how I'm mapping JSON
to the tables itself what it looks like
when we're processing it is really I'm
just opening the element so if I'm
getting you know 100 submissions back or
hundred comments back for each element
in that array I'm going to be processing
it like this I'll have to open up the
array and then open up each element and
this will give me back a simple table of
the key and the value so it would go in
and I would say because I'm using
submission or I could use reuse this
procedure which I do and say you know or
maybe not that specific name but this
would change from redditor to comment to
submission which would then in turn
change the values that I'm getting back
based on the JSON key that I'm sending
it like create UTC or or what have you
and then I'm picking up the mapping type
name and I want to print everything out
just so I can show you exactly what's
going on on the inside of the load
statement so this is like the transform
this data is what what we'll be using
inside of the load procedure which will
be in the next...
===============

SQL Complete Affiliate Link: