In the previous post of the series, we mentioned how AWS Glue process bookmarks can help you to incrementally load data from Amazon S3 and relational databases. We additionally noticed how the usage of the AWS Glue optimized Apache Parquet author can lend a hand give a boost to efficiency and arrange schema evolution.

In the 3rd put up of the collection, we’ll talk about 3 subjects. First, we’ll take a look at how AWS Glue can robotically generate code to lend a hand become data in commonplace use circumstances corresponding to deciding on explicit columns, pulling down deeply nested information, successfully parsing nested fields, and dealing with column data kind evolution.

Second, we’ll define tips on how to use AWS Glue Workflows to construct and orchestrate data pipelines the usage of other Glue elements corresponding to Crawlers, Apache Spark and Python Shell ETL jobs.

Third, we’ll see tips on how to leverage SparkSQL on your ETL jobs to accomplish SQL primarily based transformations on datasets saved in Amazon S3 and relational databases.

Automatic Code Generation & Transformations: ApplyMapping, Relationalize, Unbox, ResolveChoice

AWS Glue can robotically generate code to lend a hand carry out a lot of helpful data transformation duties. These transformations supply a easy to make use of interface for running with advanced and deeply nested datasets. For instance, some relational databases or data warehouses don’t natively reinforce nested data buildings. AWS Glue can robotically generate the code vital to flatten the ones nested data buildings prior to loading them into the objective database saving time and enabling non-technical customers to paintings with data.

The following is a listing of the preferred transformations AWS Glue supplies to simplify data processing:

  1. ApplyMapping is a metamorphosis used to accomplish column projection and convert between data sorts. In this situation, we use it to unnest a number of fields, corresponding to motion.identification, which we map to the top-level motion.identification box. We additionally forged the identification column to a protracted.
    medicare_output = medicare_src.apply_mapping(
        [('id, 'string', id, 'string'), 
        ('type, string, type', string),
        (', 'int',', int),
        ('actor.login', 'string', actor.login', 'string'),
        ('actor.display_login', 'string', 'actor.display_login', 'string'),
        ('actor.gravatar_id', 'long', 'actor.gravatar_id', 'long'),
        ('actor.url', 'string','actor.url', 'string'),
        ('actor.avatar_url', 'string', 'actor.avatar_url', string)]

  1. Relationalize converts a nested dataset saved in a DynamicFrameto a relational (rows and columns) structure. Nested buildings are unnested into height point columns and arrays decomposed into other tables with suitable number one and international keys inserted. The result’s a number of DynamicFrames representing a collection of tables that may be immediately inserted right into a relational database. More element about relationalize can also be discovered here.
    ## An instance relationalizing and writing to Redshift
    dfc = historical past.relationalize("hist_root", redshift_temp_dir)
    ## Cycle thru effects and write to Redshift.
    for df_name in dfc.keys():
        df = dfc.make a selection(df_name)
        print "Writing to Redshift desk: ", df_name, " ..."
        glueContext.write_dynamic_frame.from_jdbc_conf(body = df, 
            catalog_connection = "redshift3", 
            connection_options = {"dbtable": df_name, "database": "testdb"}, 
            redshift_tmp_dir = redshift_temp_dir)

  2. Unbox parses a string box of a definite kind, corresponding to JSON, into person fields with their corresponding data sorts and retailer the lead to a DynamicFrame. For instance, you might have a CSV document with one box this is in JSON structure {“a”: 3, “b”: “foo”, “c”: 1.2}. Unbox will reformat the JSON string into 3 distinct fields: an int, a string, and a double. The Unbox transformation is often used to switch pricey Python User Defined Functions required to reformat data that can lead to Apache Spark out of reminiscence exceptions. The following instance displays tips on how to use Unbox:
    df_result = df_json.unbox('json', "json")

  3. ResolveChoice: AWS Glue Dynamic Frames reinforce data the place a column may have fields with differing types. These columns are represented with Dynamic Frame’s choice type. For instance, Dynamic Frame schema for the medicare dataset displays up as follows:
     |-- drg definition: string
     |-- supplier identification: selection
     |    |-- lengthy
     |    |-- string
     |-- supplier title: string
     |-- supplier side road cope with: string

    This is for the reason that “supplier identification” column may just both be a protracted or string kind. The Apache Spark Dataframe considers the entire dataset and is pressured to forged it to probably the most basic kind, specifically string. Dynamic Frames can help you forged the kind the usage of the ResolveChoice become. For instance, you’ll forged the column to lengthy kind as follows.

    medicare_res = medicare_dynamicframe.resolveChoice(specifications = [('provider id','cast:long')])
     |-- drg definition: string
     |-- supplier identification: lengthy
     |-- supplier title: string
     |-- supplier side road cope with: string

    This become would additionally insert a null the place the price used to be a string that might now not be forged. As a consequence, the information with string kind casted to null values may also be known now. Alternatively, the selection kind may also be forged to struct, which assists in keeping values of each sorts.

Build and orchestrate data pipelines the usage of AWS Glue Workflows

AWS Glue Workflows supply a visible software to creator data pipelines via combining Glue crawlers for schema discovery, and Glue Spark and Python jobs to become the data. Relationships can also be outlined and parameters handed between process nodes to allow customers to construct pipelines of various complexity. Workflows can also be scheduled to run on a agenda or precipitated programmatically. You can observe the growth of each and every node independently or all the workflow making it more uncomplicated to troubleshoot your pipelines.

A regular workflow for ETL workloads is arranged as follows:

  1. Glue Python command precipitated manually, on a agenda, or on an exterior CloudWatch tournament. It would pre-process or record the walls in Amazon S3 for a desk below a base location. For instance, a CloudTrail logs partition to job might be: s3://AWSLogs/ACCOUNTID/CloudTrail/REGION/YEAR/MONTH/DAY/HOUR/.The Python command can record all of the areas and agenda crawlers to create other Glue Data Catalog tables on each and every area.
  2. Glue Crawlers precipitated subsequent to populate new walls for each and every hour in Glue Data Catalog for just lately ingested in Amazon S3.
  3. Concurrent Glue ETL jobs precipitated to one by one clear out and job each and every partition or a bunch of walls. For instance, CloudTrail occasions similar to the final week can also be learn via a Glue ETL process via passing within the partition prefix as Glue job parameters and the usage of Glue ETL push down predicates to only learn all of the walls in that prefix.Partitioning and orchestrating concurrent Glue ETL jobs means that you can scale and reliably execute person Apache Spark programs via processing just a subset of walls within the Glue Data Catalog desk. The reworked data can then be at the same time as written again via all person Glue ETL jobs to a commonplace goal desk in Amazon S3 data lake, AWS Redshift or different databases.

Finally, a Glue Python command can also be precipitated to seize the of completion standing of the other Glue entities together with Glue Crawlers, parallel Glue ETL jobs; and post-process or retry any failed elements.

Executing SQL the usage of SparkSQL in AWS Glue

AWS Glue Data Catalog as Hive Compatible Metastore

The AWS Glue Data Catalog is a controlled metadata repository appropriate with the Apache Hive Metastore API. You can apply the detailed directions here to configure your AWS Glue ETL jobs and construction endpoints to make use of the Glue Data Catalog. You additionally want to upload the Hive SerDes to the category trail of AWS Glue Jobs to serialize/deserialize data for the corresponding codecs. You can then natively run Apache Spark SQL queries in opposition to your tables saved within the Data Catalog.

The following instance assumes that you’ve crawled america legislators dataset to be had at s3://awsglue-datasets/examples/us-legislators. We’ll use the Spark shell working on AWS Glue developer endpoint to execute SparkSQL queries immediately at the legislators’ tables cataloged within the AWS Glue Data Catalog.

>>> spark.square("use legislators")
>>> spark.square("display tables").display()
|   database|         tableName|isTemporary|
|legislators|        areas_json|      false|
|legislators|    countries_json|      false|
|legislators|       events_json|      false|
|legislators|  memberships_json|      false|
|legislators|organizations_json|      false|
|legislators|      persons_json|      false|

>>> spark.square("make a selection distinct organization_id from memberships_json").display()
|     organization_id|

A an identical solution to the above can be to make use of AWS Glue DynamicFrame API to learn the data from S3. The DynamicFrame is then transformed to a Spark DataFrame the usage of the toDF means. Next, a short lived view can also be registered for DataFrame, which can also be queried the usage of SparkSQL. The key distinction between the 2 approaches is the usage of Hive SerDes for the primary way, and local Glue/Spark readers for the second one way. The use of local Glue/Spark supplies the efficiency and flexibility advantages corresponding to computation of the schema at runtime, schema evolution, and process bookmarks reinforce for Glue Dynamic Frames.

>>> memberships = glueContext.create_dynamic_frame.from_catalog(database="legislators", table_name="memberships_json")
>>> memberships.toDF().createOrReplaceTempView("memberships")
>>> spark.square("make a selection distinct organization_id from memberships").display()
|     organization_id|

Workflows and S3 Consistency

If you may have a workflow of exterior processes drinking data into S3, or upstream AWS Glue jobs producing enter for a desk utilized by downstream jobs in a workflow, you’ll come upon the next Apache Spark mistakes.

Caused via: org.apache.spark.SparkException: Job aborted because of degree failure: Task 10 in degree 16.Zero failed four occasions, most up-to-date failure: Lost process 10.Three in degree 16.0 (TID 761, ip-<>.ec2.interior, executor 1): No such document or listing 's3://<bucket>/fileprefix-c000.snappy.parquet'
It is imaginable the underlying recordsdata had been up to date.
You can explicitly invalidate the cache in Spark via working 
'REFRESH TABLE tableName' command in SQL or via recreating the Dataset/DataFrame concerned.

These mistakes occur when the upstream jobs overwrite to the similar S3 items that the downstream jobs are at the same time as list or studying. This too can occur because of eventual consistency of S3 leading to overwritten or deleted items get up to date at a later time when the downstream jobs are studying. A commonplace manifestation of this mistake happens if you end up create a SparkSQL view and execute SQL queries within the downstream process. To keep away from those mistakes, the most productive observe is to arrange a workflow with upstream and downstream jobs scheduled at other occasions, and learn/write to other S3 walls in keeping with time.

You too can allow the S3-optimized output committer to your Glue jobs via passing in a special job parameter: “–enable-s3-parquet-optimized-committer” set to true. This committer improves utility efficiency via keeping off record and rename operations in Amazon S3 all the way through process and process devote stages. It additionally avoids problems that may happen with Amazon S3’s eventual consistency all the way through process and process devote stages, and is helping to reduce process screw ups.


In this put up, we mentioned tips on how to leverage the automatic code generation job in AWS Glue ETL to simplify commonplace data manipulation duties corresponding to data kind conversion and pulling down advanced buildings. We additionally explored the usage of AWS Glue Workflows to construct and orchestrate data pipelines of various complexity. Lastly, we checked out how you’ll leverage the ability of SQL, with the usage of AWS Glue ETL and Glue Data Catalog, to question and become your data.

In the general put up, we will be able to discover explicit functions in AWS Glue and highest practices that will help you higher arrange the efficiency, scalability and operation of AWS Glue Apache Spark jobs.


About the Authors

Mohit Saxena is a technical lead supervisor at AWS Glue. His hobby is development scalable dispensed techniques for successfully managing data on cloud. He additionally enjoys staring at motion pictures, and studying about the newest era.




Please enter your comment!
Please enter your name here