Login
June 6, 2023Voiced by Amazon Polly |
What is AWS Glue?
AWS Glue is a fully managed, serverless extract, transform, and load (ETL) service that makes it easy to move data between various data sources for analytics and data processing purposes. It provides a centralized data catalog and can automatically discover and profile the data, making it easier for data analysts and scientists to find the data they need for their work. We use this service to do the task.
What is AWS Glue Crawler?
It analyses the data structure, schema, and metadata to create a unified and searchable data catalog. We don’t use this AWS Glue Crawler. Rather we will do it with Pandas library with the help of AWS Lambda or AWS Glue ETL Jobs.
Pioneers in Cloud Consulting & Migration Services
- Reduced infrastructural costs
- Accelerated application deployment
What is AWS Glue Catalog?
AWS Glue Catalog is a metadata repository that stores references to data sources, tables, and schemas. It provides a unified view of all data assets across different services and can be used to query and access data across other data stores.
What is AWS Glue Database?
AWS Glue Database is a logical container that holds related tables in the AWS Glue Catalog. A database in AWS Glue Catalog is similar to a database in a traditional relational database management system (RDBMS).
What is AWS Glue Table?
AWS Glue Table is a data structure that defines the data schema in a particular data store. Tables are stored in the AWS Glue Catalog databases and can be used by ETL jobs to transform data and load it into other systems.
What is Amazon S3 (Simple Storage Service)?
Amazon S3 (Simple Storage Service) is a cloud storage service that provides scalable, durable, and secure storage for data. Amazon S3 can be used as a data store for various data types and can be accessed through a simple web interface or programmatically using APIs. It is commonly used as a data lake to store raw data for analytics and processing.
Steps to get Schema
Step 1 – Get the dataset from the below link.
After downloading, extract the folder and upload the file(winequality-red.csv) to Amazon S3 like, shown in the below image
Step 2 – Create AWS Glue Database, testing-database
Step 3 – Create an AWS Lambda
Give necessary permission for Lambda execution such as Amazon S3 access, AWS Glue Service, etc.
Enter this code in AWS Lambda
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | import pandas as pd import boto3 import io import json def lambda_handler(event, context): s3_bucket = 'demo-bucket-for-schema' s3_key = 'winequality-red.csv' database_name = 'testing-database' table_name = 'testing-table' s3 = boto3.client('s3') response = s3.get_object(Bucket=s3_bucket, Key=s3_key) df = pd.read_csv(io.BytesIO(response['Body'].read()), encoding='utf8') print(df) glue_columns = [] for column in df.columns: column_type = df[column].dtype.name if column_type == 'object': glue_column = {'Name': column, 'Type': 'string'} elif column_type == 'int64': glue_column = {'Name': column, 'Type': 'bigint'} elif column_type == 'float64': glue_column = {'Name': column, 'Type': 'double'} else: raise ValueError(f"Unsupported column type: {column_type}") glue_columns.append(glue_column) print(glue_columns) glue = boto3.client('glue') table_input = { 'Name': table_name, 'StorageDescriptor': { 'Columns': glue_columns, 'Location': f's3://{s3_bucket}/{s3_key}', 'InputFormat': 'org.apache.hadoop.mapred.TextInputFormat', 'OutputFormat': 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat', 'SerdeInfo': { 'SerializationLibrary': 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe', 'Parameters': { 'field.delim': ',' } } }, 'PartitionKeys': [], 'TableType': 'EXTERNAL_TABLE' } glue.create_table(DatabaseName=database_name, TableInput=table_input) |
Step 4 – Create a Layer to add Pandas Library
Step 5 – Upload the Pandas library zip file to Amazon S3 and copy the URL of it and paste it here.
If you need reference how to download Pandas library then click here How to Install Pandas on AWS Lambda Function – YouTube
Scroll down in the AWS Lambda page to last, you will find a Layer to add and add the Pandas layer which was created earlier.
Step 6 – Execute Lambda and if you get any error regarding time out then increase it in configuration to 1 minute.
Step 7 – Refresh the table and you’ll find the testing-table in AWS Glue Database
As we can see the schema got created in the new table
Now go to AWS Glue ETL jobs->create a Job->Click on Script->Enter the Same code in the Script and click on Save.
Run the job to see the results in AWS Glue Table.
Conclusion
We can easily create the schema with the help of the Pandas library without needing the AWS Glue Crawler, as we have seen above. We can use AWS Lambda or AWS Glue ETL Jobs to do this task. This library automatically creates the schema based on the CSV data stored in Amazon S3.
Making IT Networks Enterprise-ready – Cloud Management Services
- Accelerated cloud migration
- End-to-end view of the cloud environment
About CloudThat
CloudThat is an official AWS (Amazon Web Services) Advanced Consulting Partner and Training partner and Microsoft Gold Partner, helping people develop knowledge of the cloud and help their businesses aim for higher goals using best in industry cloud computing practices and expertise. We are on a mission to build a robust cloud computing ecosystem by disseminating knowledge on technological intricacies within the cloud space. Our blogs, webinars, case studies, and white papers enable all the stakeholders in the cloud computing sphere.
Drop a query if you have any questions regarding AWS Glue or AWS S3. I will get back to you quickly.
To get started, go through our Consultancy page and Managed Services Package that is CloudThat’s offerings.
FAQs
1. Does Pandas Library open source?
ANS: – Yes. Pay for only AWS Lambda or AWS Glue ETL Jobs based on number of crawls you do.
2. Which one to use Pandas Library or AWS Glue Crawler?
ANS: – Well, it depends on various factors such as resources, cost, use case, etc. if you don’t need to worry about anything then just go with AWS Glue Crawler as it does everything for you.
WRITTEN BY Suresh Kumar Reddy
Yerraballi Suresh Kumar Reddy is working as a Research Associate - Data and AI/ML at CloudThat. He is a self-motivated and hard-working Cloud Data Science aspirant who is adept at using analytical tools for analyzing and extracting meaningful insights from data.
PREV
Comments