AWS Glue Python shell now helps Python 3.9 with a versatile pre-loaded atmosphere and assist to put in further libraries

AWS Glue is the central service of an AWS trendy knowledge structure. It’s a serverless knowledge integration service that means that you can uncover, put together, and mix knowledge for analytics and machine studying. AWS Glue presents you a complete vary of instruments to carry out ETL (extract, remodel, and cargo) on the proper scale. AWS Glue Python shell jobs are designed for operating small-to-medium dimension ETL, and triggering SQLs (together with long-running queries) on Amazon Redshift, Amazon Athena, Amazon EMR, and extra.

As we speak, we’re excited to announce a brand new launch of AWS Glue Python shell that helps Python 3.9 with extra pre-loaded libraries. Moreover, it means that you can customise your Python shell atmosphere with pre-loaded libraries and presents you PIP assist to put in different native or customized Python libraries.

The brand new launch of AWS Glue Python shell contains the required Python libraries to attach your script to SQL engines and knowledge warehouses like SQLAlchemy, PyMySQL, pyodbc, psycopg2, redshift, and extra. It additionally helps communications with different AWS companies similar to Amazon OpenSearch Service (opensearch-py, elasticsearch), Amazon Neptune (gremlinpython), or Athena (PyAthena). It integrates AWS Information Wrangler for ETL duties like loading and unloading knowledge from knowledge lakes, knowledge warehouses, and databases. It additionally contains library assist for knowledge serialization in trade codecs similar to avro and et-xmlfile.

On this publish, we stroll you thru on use AWS Glue Python shell to create an ETL job that imports an Excel file and writes it in a relational database and knowledge warehouse. The job reads the Excel file as a Pandas DataFrame, creates a knowledge profiling report, and exports it into your Amazon Easy Storage Service (Amazon S3) bucket. This routine cleans inaccurate data and imputes lacking values primarily based on predefined enterprise guidelines. It writes the information right into a goal MySQL database for low-latency knowledge entry. Moreover, in parallel, the script exports the DataFrame within the knowledge lake in columnar format to be copied into Amazon Redshift for reporting and visualization.

AWS Glue Python shell new options

The brand new launch of AWS Glue Python shell means that you can use new options of Python 3.9 and add customized libraries to your script utilizing job parameter configurations. This offers you extra flexibility to jot down your Python code and reduces the necessity to manually preserve and replace Python libraries wanted in your code.

Personalized pre-loaded library environments

AWS Glue Python shell for Python 3.9 comes with two library atmosphere choices:

• analytics (default) – You’ll be able to run your script in a fullly pre-loaded atmosphere for advanced analytics workloads. This feature masses the total bundle of libraries.
• none – You’ll be able to select an empty atmosphere for easy and quick ETL jobs. This feature solely masses awscli and botocore as primary libraries.

You’ll be able to set this selection through the use of the library-set parameter within the job creation, for instance:

"library-set":"analytics"

On your reference, the next desk lists the libraries included in every choice.

Added assist for library compilers

On this launch, you may import and set up libraries as a part of the script, together with your personal C-based libraries. You will have PIP assist to put in native or buyer supplied Python libraries with the assist of the next compilers:

• gcc
• gcc-c++
• gmake
• cmake
• cython
• boost-devel
• conda
• python-dev

If you wish to embrace a brand new bundle throughout your job creation, you may add the job parameter --additional-python-modules adopted by the identify of the library and the model. For instance:

"--additional-python-modules":"boto3==1.22.13"

use the brand new options with the AWS Glue Python shell script

Now that we now have launched the brand new options, let’s create a Python 3.9 job with further libraries with AWS Glue Python shell. You will have two choices to create and submit a job: you need to use the interface of AWS Glue Studio, or the AWS Command Line Interface (AWS CLI) for a programmatic strategy.

AWS Glue Studio

To make use of AWS Glue Studio, full the next steps:

1. On the AWS Glue Studio console, create a brand new job and choose Python Shell script editor.
   
2. Enter a job identify and enter your Python script.
   
3. On the Job particulars tab, enter an optionally available description.
4. For IAM function¸ select your job function.
5. For Python model, select Python 3.9.
6. Choose Load widespread Python libraries.
   
7. Select the script and the short-term information places.
8. Embody the extra libraries as job parameters (--additional-python-modules).

AWS CLI

With the brand new launch, now you can use the AWS CLI with the brand new parameters. The next is an instance of an AWS CLI assertion to create the AWS Glue Python shell script job with Python 3.9:

$ aws glue create-job 
--name <job_name> 
--role <glue_role> 
--command 
Identify=pythonshell, 
PythonVersion=3.9, 
ScriptLocation=s3://<path_to_your_python_script>.py 
--default-arguments 
'{
    "--TempDir":"s3://<path_to_your_temp_dir>",
    "--job-language":"python",
    "library-set":"<analytics/default/none>",
    "--additional-python-modules":"<python bundle>=<model>, <>=<>"
}'
--connections <your_glue_connection> 
--timeout 30 
--max-capacity 0.0625

Let’s discover the primary variations from the earlier AWS Glue Python shell variations:

• Set the choice PythonVersion inside the --command parameter to three.9.
• So as to add new libraries, use --additional-python-modules as a brand new parameter after which record the library and the required model as follows: boto3==1.22.13.
• Embody library-set inside –default-arguments and select one of many values, similar to default/analytics/none.

Answer overview

This tutorial demonstrates the brand new options utilizing a standard use case the place knowledge flows into your system as spreadsheet information stories. On this case, you wish to rapidly orchestrate a technique to serve this knowledge to the best instruments. This script imports the information from Amazon S3 right into a Pandas DataFrame. It creates a profiling report that’s exported into your S3 bucket as an HTML file. The routine cleans inaccurate data and imputes lacking values primarily based on predefined enterprise guidelines. It writes the information immediately from Python shell to an Amazon Relational Database Service (Amazon RDS) for MySQL server for low-latency app response. Moreover, it exports the information right into a Parquet file and copies it into Amazon Redshift for visualization and reporting.

In our case, we deal with every situation as unbiased duties with no dependency between them. You solely must create the infrastructure for the use instances that you just wish to check. Every part offers steerage and hyperlinks to the documentation to arrange the required infrastructure.

Conditions

There are a number of necessities which can be widespread to all situations:

1. Create an S3 bucket to retailer the enter and output information, script, and short-term information.
   
   Then, we create the AWS Identification and Entry Administration (IAM) consumer and function essential to create and run the job.
2. Create an IAM AWS Glue service function referred to as glue-blog-role and fasten the AWS managed coverage AWSGlueServiceRole for common AWS Glue permissions.If you happen to’re additionally testing an Amazon Redshift or Amazon RDS use case, it’s worthwhile to grant the required permission to this function. For extra data, check with Utilizing identity-based insurance policies (IAM insurance policies) for Amazon Redshift and Identification-based coverage examples for Amazon RDS.
3. Create an IAM consumer with safety credentials and configure your AWS CLI in your native terminal.
   This lets you create and launch your scripts out of your native terminal. It is strongly recommended to create a profile related to this configuration.

   $ aws configure --profile glue-python-shell
   AWS Entry Key ID
   AWS Secret Entry Key
   Default area identify
   Default output format

   The dataset used on this instance is an Excel file containing Amazon Video Evaluation knowledge with the next construction. In a later step, we place the Excel file in our S3 bucket to be processed by our ETL script.
   

4. Lastly, to work with pattern knowledge, we’d like 4 Python modules that had been made obtainable in AWS Glue Python shell when the parameter library-set is ready to analytics:
   1. boto3
   2. awswrangler
   3. PyMySQL
   4. Pandas

Observe that Amazon buyer opinions are usually not licensed for industrial use. It’s best to change this knowledge with your personal approved knowledge supply when implementing your utility.

Load the information

On this part, you begin writing the script by loading the information utilized in all of the situations.

1. Import the libraries that we’d like:

   import sys
   import io
   import os
   import boto3
   import pandas as pd
   import awswrangler as wr
   import pymysql
   import datetime
   from io import BytesIO

2. Learn the Excel spreadsheet right into a DataFrame:

   AWS_S3_BUCKET = <your_s3_bucket_uri>
   s3 = boto3.useful resource(
       service_name="s3",
       region_name="<your_s3_region>" 
   )
   obj = s3.Bucket(AWS_S3_BUCKET).Object('amazon_reviews_us_Video.xlsx').get()
   df = pd.read_excel(io.BytesIO(obj['Body'].learn())

Situation 1: Information profiling and dataset cleansing

To help with primary knowledge profiling, we use the pandas-profiling module and generate a profile report from our Pandas DataFrame. Pandas profiling helps output information in JSON and HTML format. On this publish, we generate an HTML output file and place it in an S3 bucket for fast knowledge evaluation.

To make use of this new library in the course of the job, add the --additional-python-modules parameter from the job particulars web page in AWS Glue Studio or throughout job creation from the AWS CLI. Keep in mind to incorporate this bundle within the imports of your script:

from pandas_profiling import ProfileReport
…
profile = ProfileReport(df)
s3.Object(AWS_S3_BUCKET,'output-profile/profile.html').put(Physique=profile.to_html())

A typical drawback that we regularly see when coping with a column’s knowledge kind is the combination of information varieties are recognized as an object in a Pandas DataFrame. Combined knowledge kind columns are flagged by pandas-profiling as Unsupported kind and saved within the profile report description. We are able to entry the data and standardize it to our desired knowledge varieties.

The next traces of code loop each column within the DataFrame and verify if any of the columns are flagged as Unsupported by pandas-profiling. We then forged it to string:

for col in df.columns:
    if (profile.description_set['variables'][col]['type']) == 'Unsupported':
        df[col] = df[col].astype(str)

To additional clear or course of your knowledge, you may entry variables supplied by pandas-profiling. The next instance prints out all columns with lacking values:

for col in df.columns:
    if profile.description_set['variables'][col]['n_missing'] > 0:
        print (col, " is lacking ", profile.description_set['variables'][col]['n_missing'], " knowledge kind ", profile.description_set['variables'][col]['type'])
        #lacking knowledge dealing with
        #....

Situation 2: Export knowledge in columnar format and duplicate it to Amazon Redshift

On this situation, we export our DataFrame into Parquet columnar format, retailer it in Amazon S3, and duplicate it to Amazon Redshift. We use Information Wrangler to attach our script to Amazon Redshift. This Python module is already included within the analytics atmosphere. Full the next steps to arrange the required infrastructure:

Now we are able to write uncooked knowledge to Amazon S3 in Parquet format and to Amazon Redshift.

A typical partition technique is to divide rows by yr, month, and day out of your date column and apply multi-level partitioning. This strategy permits quick and cost-effective retrieval for all rows assigned to a selected yr, month, or date. One other technique to partition your knowledge is through the use of a selected column immediately. For instance, utilizing review_date as a partition offers you single stage of listing for each distinctive date and shops the corresponding knowledge in it.

On this publish, we put together our knowledge for the multi-level date partitioning technique. We begin by extracting yr, month, and day from our date column:

df['day']= pd.DatetimeIndex(df['review_date']).day.astype(str)
df['month']= pd.DatetimeIndex(df['review_date']).month.astype(str)
df['year']= pd.DatetimeIndex(df['review_date']).yr.astype(str)

With our partition columns prepared, we are able to use the awswrangler module to jot down to Amazon S3 in Parquet format:

wr.s3.to_parquet(
    df=df,
    path="s3://<your_output_s3_bucket>", #change this worth with path to your bucket
    dataset=True,
    mode="overwrite",       
    partition_cols=['year','month','day']

To question your partitioned knowledge in Amazon S3, you need to use Athena, our serverless interactive question service. For extra data, check with Partitioning knowledge with Athena.

Subsequent, we write our DataFrame on to Amazon Redshift inner storage through the use of Information Wrangler. Writing to Amazon Redshift inner storage is suggested once you’re going to make use of this knowledge often for advanced analytics, massive SQL operations, or enterprise intelligence (BI) reporting. In Amazon Redshift, it’s suggested to outline the distribution model and type key on the desk to enhance cluster efficiency. If you happen to’re undecided about the best worth for these parameters, you need to use the Amazon Redshift auto distribution model and type key and observe Amazon Redshift advisor suggestions. For extra data on Amazon Redshift knowledge distribution, check with Working with knowledge distribution types.

#drop overview columns and protect different columns for evaluation
df = df.drop(['review_body','review_headline'], axis=1)

#generate dictionary with size for use by awswrangler to create varchar columns
max_length_object_cols = {col: df.loc[:, col].astype(str).apply(len).max() for col in df.select_dtypes([object]).columns}

#connect with Redshift by way of Glue connection
con = wr.redshift.join("<your_glue_connection>")

#copy DataFrame into Redshift desk 
wr.redshift.copy(
    df=df,
    path=<temporarty path for staging information>,
    con=con,
    desk="<your_redshift_table_name>", #awswrangler will create desk if it doesn't exist
    schema="<your_redshift_schema>",
    mode="overwrite",
    iam_role=<your_iam_role_arn_with_permission_to_redshift>,
    varchar_lengths= max_length_object_cols,
	   diststyle="AUTO",
    )

#shut connection    
con.shut()

Situation 3: Information ingestion into Amazon RDS

On this situation, we open a connection between AWS Glue Python shell and ingest the information immediately into Amazon RDS for MySQL. The infrastructure you require for this situation is an RDS for MySQL database in the identical Area because the AWS Glue Python shell job. For extra data, check with Making a MySQL DB occasion and connecting to a database on a MySQL DB occasion.

With the PyMySQL and boto3 modules, we are able to now connect with our RDS for MySQL database and write our DataFrame right into a desk.

Put together the variables for connection and generate a database authentication token for database login:

#RDS connection particulars
MYSQL_ENDPOINT = "<mysql_endpoint>"
PORT= "3306"
USER= "<mysql_username>"
REGION = "<region_for_rds_mysql>"
DBNAME = "<database_name>"
session = boto3.Session(profile_name="<your_aws_profile>")
consumer = session.consumer('rds')

#generate db authentication token 
token = consumer.generate_db_auth_token(DBHostname=MYSQL_ENDPOINT, Port=PORT, DBUsername=USER, Area=REGION)

#connect with database
connection = pymysql.join(host=MYSQL_ENDPOINT,
    consumer=USER,
    password=token,
    db=DBNAME,
    ssl_ca="global-bundle.pem")
    
#organize columns and values for SQL insert assertion    
columns=",".be part of(df.columns)
values=",".be part of(['%s'.format(i+1) for i in range(len(df.columns))])

#SQL assertion to insert into RDS
load_sql = f"INSERT INTO demo_blog.amazon_video_review({columns:}) VALUES ({values:})"

For extra details about utilizing an SSL connection together with your RDS occasion, check with Utilizing SSL/TLS to encrypt a connection to a DB occasion.

Connect with your RDS for MySQL database and write a Pandas DataFrame into the desk with the next code:

attempt:
    with connection.cursor() as cur:
        cur.executemany(load_sql, df.values.tolist())
    connection.commit()
lastly:
    cur.shut()

That you must create a desk in Amazon RDS for MySQL previous to operating the insert assertion. Use the next DDL to create the demo_blog.amazon_video_review desk:

CREATE TABLE `amazon_video_review` (
  `market` varchar(100) NOT NULL,
  `customer_id` bigint NOT NULL,
  `review_id` varchar(100) DEFAULT NULL,
  `product_id` varchar(100) DEFAULT NULL,
  `product_parent` bigint NOT NULL,
  `product_title` varchar(100) DEFAULT NULL,
  `product_category` varchar(100) DEFAULT NULL,
  `star_rating` bigint NOT NULL,
  `helpful_votes` bigint NOT NULL,
  `total_votes` bigint NOT NULL,
  `vine` varchar(100) DEFAULT NULL,
  `verified_purchase` varchar(100) DEFAULT NULL,
  `review_headline` varchar(100) DEFAULT NULL,
  `review_body` varchar(5000) DEFAULT NULL,
  `review_date` date NOT NULL,
  `yr` varchar(100) DEFAULT NULL,
  `month` varchar(100) DEFAULT NULL,
  `date` varchar(100) DEFAULT NULL,
  `day` varchar(100) DEFAULT NULL
)

When the information is on the market in database, you may carry out a easy aggregation as follows:

agg_sql="insert into demo_blog.video_review_recap choose product_title , yr as review_year, rely(*) as total_review, sum(case when verified_purchase="Y" then 1 else 0 finish) as total_verified_purchase,sum(case when verified_purchase="N" then 1 else 0 finish) as total_unverified_purchase from demo_blog.amazon_video_review avr group by 1 order by 2 DESC"
cursor = connection.cursor()
cursor.execute(agg_sql)

Create and run your job

After you finalize your code, you may run it from AWS Glue Studio or put it aside in a script .py file and submit a job with the AWS CLI. Keep in mind so as to add the required parameters in your job creation relying of the situation you’re testing. The next job parameters cowl all of the situations:

--command pythonVersion=3.9 …
--default-arguments '{"library-set":"analytics" , "--additional-python-modules":"pandas_profile", …}'

Evaluation the outcomes

On this part, we overview the anticipated outcomes for every situation.

In Situation 1, pandas-profiling generates a knowledge report in HTML format. On this report, you may visualize lacking values, duplicated values, dimension estimations, or correlations between columns, as proven within the following screenshots.

For Situation 2, you may first overview the Parquet file written to Amazon S3 in Parquet format with partition yr/month/day.

Then you need to use the Amazon Redshift question editor to question and visualize the information.

For Situation 3, you need to use a JDBC connection or database IDE to connect with your RDS database and question the information that you just simply ingested.

Clear up

AWS Glue Python shell is a serverless routine that received’t incur in any further costs when it isn’t operating. Nevertheless, this demo used a number of companies that may incur in further prices. Clear up after finishing this walkthrough with the next steps:

1. Take away the contents of your S3 bucket and delete it. If you happen to encounter any errors, check with Why can’t I delete my S3 bucket utilizing the Amazon S3 console or AWS CLI, even with full or root permissions.
2. Cease and delete the RDS DB occasion. For directions, see Deleting a DB occasion.
3. Cease and delete the Amazon Redshift cluster. For directions, check with Deleting a cluster.

Conclusion

On this publish, we launched AWS Glue Python shell with Python 3.9 assist and extra pre-loaded libraries. We introduced the customizable Python shell atmosphere with pre-loaded libraries and PIP assist to put in different native or customized Python libraries. We coated the brand new options and get began by way of AWS Glue Studio and the AWS CLI. We additionally demonstrated a step-by-step tutorial of how one can simply use these new capabilities to perform widespread ETL use instances.

To be taught extra about AWS Glue Python shell and this new characteristic, check with Python shell jobs in AWS Glue.

In regards to the authors

Alunnata Mulyadi is an Analytics Specialist Options Architect at AWS. Alun has over a decade of expertise in knowledge engineering, serving to prospects tackle their enterprise and technical wants. Outdoors of the work, he enjoys pictures, biking, and basketball.

Quim Bellmunt is an Analytics Specialist Options Architect at Amazon Internet Providers. Quim has a PhD in Pc Science and Data Graph specializing in knowledge modeling and transformation. With over 6 years of hands-on expertise within the analytics and AI/ML house, he enjoys serving to prospects create programs that scale with their enterprise wants and generate worth from their knowledge. Outdoors of the work, he enjoys strolling together with his canine and biking.

Kush Rustagi is a Software program Improvement Engineer on the AWS Glue staff with over 4 years of expertise within the trade having labored on large-scale monetary programs in Python and C++, and is now utilizing his scalable system design expertise in the direction of cloud growth. Earlier than engaged on Glue Python Shell, Kush labored on anomaly detection challenges within the fin-tech house. Except for exploring new applied sciences, he enjoys EDM, touring, and studying non-programming languages.