Continuing from the last lecture, we can now create another script that handles common database actions
like inserts, updates and deletes.
So for this, let's create a new script and call it data underscore modification.
Again we do it under the Data warehouse folder.
I will first include any libraries and variables that we need for the script, which relates mostly
to logging and the global table name for both staging and core schemas.
Now let's put our focus on the insert functionality.
Let's do this by first creating a function called insert rows that will insert the API data, which
we load it from the JSON file into the staging and core tables.
This function will take the cursor and connection objects, the schema to differentiate between staging
and core, And a variable rho, which is a dictionary containing the data to be inserted.
So now we can define a try and accept clause.
And in the try we can specify an if else statement.
In the if block we will define the insert related to the staging schema, while the else block will
contain operations related to the schema.
We also define a video ID variable that will be used for logging purposes.
Now that we have done this, we can specify the SQL that will be executed.
This will be done using the insert into SQL command.
So obviously since we are in the if block for the staging schema, this value will be staging and the
table will be YouTube underscore API.
Now the insert into command takes the names of the columns in the staging schema, which are the following
seven.
Now that we have defined the columns where we will insert the data, we have to actually define the
data itself that will be inserted.
And this we can do using the values statement.
When specifying values, it is a good practice to use named placeholders to remove any doubt of what
data is being inserted into which columns.
So the basic syntax will be something like values.
Then we define the percentage and the placeholder name.
We also define the s at the end.
So as we are all on the same page, what we are inserting here under the names placeholder is the actual
name that we have inside the JSON data.
So if we go on one of these examples, the name will be this video underscore ID.
It will be actually the key name that we have here.
So what we can do is we can go back to the data modification script.
And we can change this to video underscore ID.
We can do the same for the other keys as follows.
So all of these key names are what we see here in the actual JSON data file.
Finally, we specify the row variable as the second argument of the execute command.
As every insert as we will do, will contain a specific row.
We will later develop Python code to loop through each row of videos, but for now, let's continue
this insert function.
So far we have completed the if block for the staging schema.
Now we can put our focus on the block that will be for the core schema.
So now we can write.
Else we can define the video ID for logging purposes.
But notice how in this case, the name is video ID, which is the column name as we defined it in the
table of the staging layer.
So these have to match.
Now we repeat what we did in the if block.
But using the placeholder names as we defined in staging layer.
We do this because we are no longer reading from the JSON like we did for staging for the core layer.
We read data from the previous layer, which is the staging layer.
So the placeholder names must match the names of the columns on the staging table.
Now I have the command already written, so I will just paste it here and you can notice how the command
is very similar.
We just have the values part that is different as this would represent the columns in the core table
schema.
Finally, we can commit our changes.
Since we are changing the contents of the database.
And also we can log our insert.
If there is an exception.
We also look this as another.
Like this.
We have built the insert function.
However, we need to take into account that most of the variables we defined can be updated, especially
variables like the video views, the likes count and the comments count as they are values that will
grow day by day as more people watch and interact with the YouTube videos.
So now we need to define a function which we can name update rows that will have parameters similar
to the insert function.
Again, for the staging layer, we base the row dictionary keys on the JSON file, while the core layer
is based on the column specified in the staging layer.
Knowing this, we can write the following.
So here you can see that for the staging schema, the variable names are based on the JSON keys that
we have JSON file here.
And the variables here are based on the columns of the staging table.
We again need to define an SQL command to execute using the cursor object.
And in this case we use the update SQL command where we define the table to update.
We can do this as follows.
Now we use set by specifying the columns where changes can occur based on the row dictionary value.
For the columns, we will consider the video title, which could potentially change, and the other
three variables which will definitely change, which are the video views, likes count and the comments
count.
And also very important we need to specify a Where clause depending on the video ID and upload date,
which won't change.
So to summarize, what we are saying here is that for a particular video which has a specific video
ID and upload date, the next time we run this ELT process, update the values of the video title,
video views, likes and comments count based on the new values which we get from the latest JSON data
file.
This update function will need to be run for both staging and core layers.
Finally, we commit log the update and for some reason we have an error in the try block for any of
the video updates.
We raise an exception like this.
The update function is ready.
Now there could be instances where the channel we are extracting data from the video.
We need to cater for this scenario as it might lead to incorrect data in the tables.
Let's define a delete rows function that's different from the insert and update functions.
Takes a different arguments as default arguments, which is the ids to delete.
So def delete rows here on schema.
And as we said, the IDs to delete this last variable will contain the video ID or IDs.
If there is more than one video to delete.
In order to be read properly in the SQL that we will define soon, we first need to convert this variable
to a proper SQL format string, and we can do this using the Python string join method as follows.
So first we define the try block IDs to delete will be equal to.
Note we have changed the format of the string.
We can move on to the SQL delete statement that takes the table in question, and using the Where clause,
deletes the row or rows depending if the IDs to delete variable has one or more IDs.
So now we can write execute.
Finally, as we did in the insert and update functions, we commit log deletion.
And if for some reason we have an error in the try block for any of the video deletions, we raise it
as an exception.
And that's all we have to do for the data modification scripts.
In the next lecture we will discuss data transformations.