AWS Glue custom classifier | Grok | AWS Glue tutorial | p6
AWS Glue is a fully managed ETL Service. It consists of a central Metadata Repository which is known as the Glue Data Catalog. It has a Spark ETL engine which is completely server less and it has a flexible scheduler.
If we look at the diagram above, we can see that the Glue Data Catalog sits in the middle and it has connections, tables, settings, transformations and out of the Glue Data Catalog we can create jobs or jobs can use information i.e metadata in the Glue Data Catalog to create scripts. And these scripts can be scheduled and they can move data from points a (Source) to point b (Destination). And we can access the glue data catalog through the AWS management console.
AWS Glue offers a fully managed server-less ETL Tool. This removes the overhead, and barriers to entry, when there is a requirement for a ETL service in AWS. So, in other words it’s the native AWS ETL Service, so if we’re looking to move the data around AWS in a manner where we don’t have to manage the infrastructure then AWS Glue is the tool for us.
The pre-requisite to writing an ETL job is to populate the AWS Glue Data Catalog. To populate the Data Catalog, the AWS Glue Crawlers will connect to the Data Store using the Built-in Classifiers or the Custom Classifiers which will help to determine the schema of our data and then it will create the metadata within AWS Glue Data Catalog. Here, the AWS Glue Crawler will determine the schema of the tables but not the relationship among the tables. The Crawlers can be run on scheduler or by a trigger based on event or on demand.
AWS Glue Data Catalog.Go to S3 and create a bucket.
We then create a couple of folders inside the s3 bucket. We first create a folder called data and that is where we are going to put our data. And then the next folder we have to create is called temp-directory , and this is where our temporary files that Glue actually creates and needs to persist will stay and we will supply this actually as a parameter at runtime. So, in other words this is the directory that glue is going to use in a temporary location and create folder. And the last one we will have to create is called scripts which is the location that glue will actually persist the scripts that we create in it and we’ll supply this again when we’re in glue.
Let’s go inside data and then create a new folder called the customer_database and what we’re doing is giving the folder a physical name that will represent the logical interpretation. So because this is called a database this folder will be where our customer artifacts for the database sit. And under the customer_database folder we are going to create another folder called customers_csv. And under that folder we’re going to create another folder called dataload20211230. And within that folder upload the data from customer.csv from the github link: https://github.com/johnny-chivers/glue-full-course.
Now, we need to create an IAM role which is a service rule that we’re going to use throughout for glue. So we need to go to IAM and then go to roles and create role. We have to give the roles a name and we choose the AWS service as a trusted entity and choose a use-case.
Now, we have to choose permissions. We just give the “Admin Access” because we’re creating buckets and stuff the default glue policy won’t work out of the box because it is a resource constraint on the bucket name so to keep things simple i’m just going to use admin access which is a bad practice in production. But it is okay for the purpose of learning and it is easier for all of us following along that we just have all the permissions.
Then we just have to click on the Create Role.
The AWS Glue Data Catalog sits at the center of everything in the AWS Glue. It is a persistent metadata store. It is a managed service that lets us store, annotate, and share metadata which can be used to query and transform data. There’s one AWS Glue Data Catalog per AWS region. We can control access to the Metadata store and the resources that it holds through IAM. And we can use it for Data Governance, so we can annotate it and say this data is sensitive or this data is classified or this data is incomplete. We can give it descriptions and names and things like that. So let’s just jump onto the console and look at where the AWS Glue Data Catalog is back on the console.
GLUE in the search box. And on the left menu box, there is Databases and within Databases there are Tables and Connections. We also have Crawlers and within Crawlers we have Classifiers. We also have Schema Registries and Settings.An AWS Glue Database is a set of associated Data Catalog table definitions organized into a logical group. So it’s just a name that we give to a database which is being represented by this database symbol that we place tables in. So, tables belong to a database. The data or the tables all reside in the original locations, the AWS Glue Data Catalog is just a meta data store of information belonging to that data. It doesn’t actually move the data anywhere. It always stays where it is. So, database is just a way of organizing tables into logical groups. We’re not physically moving the tables anywhere or moving the data anywhere. It is just a naming convention to logically organize those tables.
So, lets go and create a database in console.
We need to give our database a name. So earlier in S3, inside the s3 bucket we created a folder called data and within it we created on folder called customers_database. All of our tables belonging to this database i.e the folders below will be set inside this folder.
We have to provide the Database a name.
Note that we should always use
underscoresbecause spark needsunderscoresfor its engine. It will convert them for us from hyphens to underscores in certain places and other places. It won’t let us enter them on the Glue Data Catalog.
We also have to provide the location to the folder where the data will reside.
Database is where we logically group tables and tables by definition are the metadata definition that represents our data. The data resides in its original store. This is just a representation of the schema. This is really important as it is saying that the data always resides where it has lived and the glue that is the catalogue is just a representative of that data in schema from.
Partitions are folders where data is stored in S3, which are physical entities which are mapped to partitions, which are logical entities i.e Columns in the Glue Table. If we jump back onto the console, and take a look at our customer csv, this folder is going to be our database. And we have customer csv folder which is going to become a table in Glue Data Catalog. Underneath, we have partitioned by data load so that is our physical partition as a folder where we’re going to get a data load column and the value for all the data under this folder will be 2021-12-30. If we were to carry on and add more data load folders with more data then our queries if we added them and told it to look at the data load date would only look at the data in that specific folder for that query that we specify.
So, for example, straight off on the AWS website, if we take a look at what’s going on here, we can see that they’re using a sales table where they partitioned or put the data into three separate folders below where they have year, month, and day and the way this works is if we were to write a query and we wanted the data for where the year was 2019, the month was february and the day was one, we’d write the query to say where year equal 2019, where month equals february and where day equasl one, the query would come into the sales table go into the year 2019 and go down into february and then go down into the day one and search these two files here. It knows to exclude all this other data sitting there because of the way the partitions are. So partitions are just folders in s3 that helps us query the data more efficiently because the query only goes down into the folders that are specified.
An AWS Glue Crawler is just a program that AWS have wrote that actually goes in and looks at all the data on s3 or databases and infers the schema to save us from having to manually enter the table schema in the AWS Glue Data Catalog. It is a program that connects to a data store (source or target), progresses through a prioritized list of classifiers to determine the schema for our data, and then creates metadata tables in the AWS Glue Data Catalog.
On the console, what we want to do is go to the databases and go inside the customer_database that we created earlier and inside the tables in customers_database.
And now, on the left hand side if we click on the Add tables option, we are provided with three options:
we can either use a crawler to generate the schema for our table, or define the schema manually or using the existing schema. We will be using crawler here.
What we first have to do is give the crawler a name.
Next, we need to define the Crawler’s source type.
Then we need to add the data store.
Choose an IAM role.
Create a scheduler for this crawler to run.
Choose the database to dump the Crawler’s Output.
And then hit the finish button.
Then we need to select and run the crawler.
Now, let’s take a look at the table itself. For that, we need to go to Databases and then to the customer_database and then the Tables in customer_database. And we can see that a table has been created.
We can see that we have the table name based on the folder name in the s3 bucket. So it has used the folder name to infer the table name. If we click on the name, we can see that we have some information.
If we click on the “View Partitions”, we can see that it has picked up that partition.
Now, the next thing we want to do is actually look at the data. To do that, what we would want to use is AWS Athena. So we need to go to the AWS Athena and go inside the Query Editor.
So, since it is the first time we will be running Athena we will have to set up few tings. So before we run a query we need to set up a query results location in S3. We need to go back to our bucket and create a new folder and call this athena_results. Now we need to go to Settings in Athena and then go to Manage. Here, we need to provide the path to the folder inside the bucket that we just created to save the results from Athena.
Now, we can go back to the Query Editor , select the data source and database and inside the Tables we can see the table and click on the 3 little dots and select the preview table options to see the data inside the table.
fuse-internal-analytics-product-bronze-to-silver