AWS Glue Python shell now helps Python 3.9 with a versatile pre-loaded surroundings and help to put in extra libraries

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

In the present day, 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 lets you customise your Python shell surroundings with pre-loaded libraries and affords you PIP help to put in different native or customized Python libraries.

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

On this put up, 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 information warehouse. The job reads the Excel file as a Pandas DataFrame, creates an information 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 info right into a goal MySQL database for low-latency information entry. Moreover, in parallel, the script exports the DataFrame within the information 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 lets you use new options of Python 3.9 and add customized libraries to your script utilizing job parameter configurations. This provides you extra flexibility to put in writing your Python code and reduces the necessity to manually preserve and replace Python libraries wanted to your code.

Personalized pre-loaded library environments

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

• analytics (default) – You may run your script in a fullly pre-loaded surroundings for complicated analytics workloads. This feature hundreds the complete package deal of libraries.
• none – You may select an empty surroundings for easy and quick ETL jobs. This feature solely hundreds awscli and botocore as primary libraries.

You may set this selection through the use of the library-set parameter within the job creation, for instance:

"library-set":"analytics"

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

Added help 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’ve gotten PIP help to put in native or buyer supplied Python libraries with the help of the next compilers:

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

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

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

Find out how to use the brand new options with the AWS Glue Python shell script

Now that now we have launched the brand new options, let’s create a Python 3.9 job with extra libraries with AWS Glue Python shell. You’ve gotten two choices to create and submit a job: you should utilize 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 title and enter your Python script.
   
3. On the Job particulars tab, enter an non-obligatory description.
4. For IAM function¸ select your job function.
5. For Python model, select Python 3.9.
6. Choose Load frequent Python libraries.
   
7. Select the script and the non permanent 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 package deal>=<model>, <>=<>"
}'
--connections <your_glue_connection> 
--timeout 30 
--max-capacity 0.0625

Let’s discover the principle 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 information flows into your system as spreadsheet information experiences. On this case, you wish to shortly orchestrate a option to serve this information to the appropriate instruments. This script imports the info 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 info immediately from Python shell to an Amazon Relational Database Service (Amazon RDS) for MySQL server for low-latency app response. Moreover, it exports the info right into a Parquet file and copies it into Amazon Redshift for visualization and reporting.

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

Stipulations

There are just a few necessities which might be frequent to all situations:

1. Create an S3 bucket to retailer the enter and output information, script, and non permanent information.
   
   Then, we create the AWS Id and Entry Administration (IAM) person and function essential to create and run the job.
2. Create an IAM AWS Glue service function known as glue-blog-role and fasten the AWS managed coverage AWSGlueServiceRole for basic AWS Glue permissions.For those who’re additionally testing an Amazon Redshift or Amazon RDS use case, you could grant the required permission to this function. For extra data, consult with Utilizing identity-based insurance policies (IAM insurance policies) for Amazon Redshift and Id-based coverage examples for Amazon RDS.
3. Create an IAM person 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 title
   Default output format

   The dataset used on this instance is an Excel file containing Amazon Video Evaluate information 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 information, we want 4 Python modules that had been made out there in AWS Glue Python shell when the parameter library-set is about to analytics:
   1. boto3
   2. awswrangler
   3. PyMySQL
   4. Pandas

Notice that Amazon buyer critiques aren’t licensed for industrial use. You must substitute this information with your personal licensed information supply when implementing your software.

Load the info

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

1. Import the libraries that we want:

   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())

State of affairs 1: Knowledge profiling and dataset cleansing

To help with primary information 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 put up, we generate an HTML output file and place it in an S3 bucket for fast information evaluation.

To make use of this new library through 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. Bear in mind to incorporate this package deal 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 standard downside that we frequently see when coping with a column’s information kind is the combination of information varieties are recognized as an object in a Pandas DataFrame. Blended information kind columns are flagged by pandas-profiling as Unsupported kind and saved within the profile report description. We will entry the knowledge and standardize it to our desired information varieties.

The next strains of code loop each column within the DataFrame and examine if any of the columns are flagged as Unsupported by pandas-profiling. We then solid 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 information, 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'], " information kind ", profile2.description_set['variables'][col]['type'])
        #lacking information dealing with
        #....

State of affairs 2: Export information in columnar format and duplicate it to Amazon Redshift

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

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

A standard partition technique is to divide rows by 12 months, 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 12 months, month, or date. One other technique to partition your information is through the use of a selected column immediately. For instance, utilizing review_date as a partition offers you single degree of listing for each distinctive date and shops the corresponding information in it.

On this put up, we put together our information for the multi-level date partitioning technique. We begin by extracting 12 months, 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']).12 months.astype(str)

With our partition columns prepared, we are able to use the awswrangler module to put in writing 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 information in Amazon S3, you should utilize Athena, our serverless interactive question service. For extra data, consult with Partitioning information with Athena.

Subsequent, we write our DataFrame on to Amazon Redshift inner storage through the use of Knowledge Wrangler. Writing to Amazon Redshift inner storage is suggested whenever you’re going to make use of this information often for complicated analytics, giant SQL operations, or enterprise intelligence (BI) reporting. In Amazon Redshift, it’s suggested to outline the distribution fashion and kind key on the desk to enhance cluster efficiency. For those who’re undecided about the appropriate worth for these parameters, you should utilize the Amazon Redshift auto distribution fashion and kind key and comply with Amazon Redshift advisor suggestions. For extra data on Amazon Redshift information distribution, consult with Working with information distribution types.

#drop assessment 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()

State of affairs 3: Knowledge ingestion into Amazon RDS

On this state of affairs, we open a connection between AWS Glue Python shell and ingest the info immediately into Amazon RDS for MySQL. The infrastructure you require for this state of affairs is an RDS for MySQL database in the identical Area because the AWS Glue Python shell job. For extra data, consult 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>")
shopper = session.shopper('rds')

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

#connect with database
connection = pymysql.join(host=MYSQL_ENDPOINT,
    person=USER,
    password=token,
    db=DBNAME,
    ssl_ca="global-bundle.pem")
    
#organize columns and values for SQL insert assertion    
columns=",".be a part of(df.columns)
values=",".be a 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 along with your RDS occasion, consult 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:

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

You might want to create a desk in Amazon RDS for MySQL previous to working 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,
  `12 months` varchar(100) DEFAULT NULL,
  `month` varchar(100) DEFAULT NULL,
  `date` varchar(100) DEFAULT NULL,
  `day` varchar(100) DEFAULT NULL
)

When the info is offered in database, you may carry out a easy aggregation as follows:

agg_sql="insert into demo_blog.video_review_recap choose product_title , 12 months 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. Bear in mind so as to add the required parameters in your job creation relying of the state of affairs 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", …}'

Evaluate the outcomes

On this part, we assessment the anticipated outcomes for every state of affairs.

In State of affairs 1, pandas-profiling generates an information report in HTML format. On this report, you may visualize lacking values, duplicated values, measurement estimations, or correlations between columns, as proven within the following screenshots.

For State of affairs 2, you may first assessment the Parquet file written to Amazon S3 in Parquet format with partition 12 months/month/day.

Then you should utilize the Amazon Redshift question editor to question and visualize the info.

For State of affairs 3, you should utilize a JDBC connection or database IDE to connect with your RDS database and question the info that you just simply ingested.

Clear up

AWS Glue Python shell is a serverless routine that received’t incur in any additional fees when it isn’t working. Nevertheless, this demo used a number of providers that can incur in additional prices. Clear up after finishing this walkthrough with the next steps:

1. Take away the contents of your S3 bucket and delete it. For those who encounter any errors, consult 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, consult with Deleting a cluster.

Conclusion

On this put up, we launched AWS Glue Python shell with Python 3.9 help and extra pre-loaded libraries. We introduced the customizable Python shell surroundings with pre-loaded libraries and PIP help to put in different native or customized Python libraries. We lined 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 frequent ETL use circumstances.

To be taught extra about AWS Glue Python shell and this new characteristic, consult 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 information engineering, serving to prospects deal with their enterprise and technical wants. Exterior of the work, he enjoys pictures, biking, and basketball.

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

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