Now to have a connection set up, we can create the schemas which will represent the raw and refined
layers and the associated tables for each schema.
First thing we should do is define the naming conventions.
The raw layer will be represented by the staging schema, and the refined layer will be represented
by the core schema.
This was already in the presentation.
Each schema in our case will have one table, which we will name YouTube underscore API for both the
raw and refined layers.
So let's first create the global table name at the top of our script.
Now we can build the function create schema which has the schema variable as a parameter.
Function create schema.
The schema parameter.
First thing we do is we define a character and connection variables.
And here we can use the get underscore underscore character function that we just created, and now
we specify what SQL we want to run.
In our case, we want to create a schema so we can define the following SQL.
We use f strings with the SQL being create schema if not exists.
And then the schema name.
Notice how we use the if not exists, which would only create the schema if it doesn't exist already.
And to actually run the SQL we need to use the cursor object.
So execute schema underscore SQL.
Since we are making changes to the database we need to commit these changes.
And to do this we use the convert method.
So con dot commit.
Finally it's important that we close the connection and cursor objects.
So we can call the close command and query function that we also defined previously.
So like this we have defined the create schema function.
So now we need to create another function for the tables.
As we said already we will have the table names with the same for both schemas.
However, since we will later do transformations between each layer, each table will have different
columns.
Let's define a create table function.
So create table.
Which will have the SQL code to create the tables depending on whether it's the raw layer or refined
layer table.
So let's define schema as an argument here again.
Same as before we use the get quantum cursor function.
And now we can define an if else statement depending on the schema.
And here we define the column names.
So if the schema is equal to staging.
The SQL to create the table will look as follows.
Notice how now we are defining a create table, not create schema.
We are also defining the if not exists SQL code.
And here we can define the schema that table.
The actual column names will be the video ID that will be of type varchar which will have a length of
11, and it will be a primary key.
Then there's also the video title which will be of data type text, the upload date that will be of
data type, timestamp duration will be varchar.
And finally the video views like counts and comment counts.
I already have the SQL for the columns, so I will just paste it here.
Notice how depending on the column we have different data types.
So the video ID is of type varchar.
But column like video views will be of type integer.
Same for a date.
Since it's a date it will be of type timestamp.
So like this we have the if part of the if statement.
And now we need to define the else part, which will basically be for the core schema.
Again, I have the SQL ready, so I will just paste it here.
We notice how the columns are almost the same in terms of differences.
It only boils down to the duration and the video type columns, which will be explained in more detail
in a later lecture.
Export table creation.
We can run the commands same as for the schema creation.
So we can go back here and we can copy these commands.
Obviously we need to change from schema underscore SQL to the actual table SQL variable that we have
in this function.
So like that we have defined also the create table function.
One last function that we should create is to get all the video IDs in either the staging or the correlation
tables.
This will be helpful when we come to loop through the rows of data inside the tables so we can first
define get underscore video underscore IDs function that will take the cursor and the schema as arguments.
We can then perform an execute statement to get all the video IDs in the table in question, by using
the fetch all methods of the cursor object.
So here execute.
And as we said, we will get the IDs using the fetch all method.
In our case this will give a list of dictionaries where the key is always this variable.
Here the video id and the value will be a video id value.
So an example of what IDs would look like is something like this.
We have the video ID key that is constant.
And then the actual value of the id.
Notice how we are defining a dictionary in each case.
Now, in our case, we just want the actual value of each row.
So what we can do is to write a list comprehension and return the new list as follows.
So we define video underscore write these variable.
What we're doing here is we are going inside the video ID key and extracting the value for each row.
And then finally we can return the video IDs.
This we can delete.
And an example of how this will look like when we come to return is this would have a list of what the
values for the video IDs.
So now we have the functions ready to create the table and schemas, and also a list of all video IDs
in the table.
In the next lecture we will look into getting the data from the JSON into the actual database tables.