Artificial Intelligence is a rapidly evolving field, with new terms and concepts emerging all the time. Three terms that are often used, but have distinct meanings, are Generative AI, AI Agents, and Agentic AI. This blog post will break down these concepts in simple terms, while also acknowledging that the distinctions are not always rigid.

What is Generative AI?

At its core, Generative AI is a type of artificial intelligence model that can create new and original content, such as text, images, or videos. This is done by learning patterns from vast amounts of existing historical data. The heart of many Generative AI systems is a Large Language Model (LLM), which is trained on a massive dataset from the internet, including diverse sources.

However, a simple LLM-based Generative AI has a significant limitation which is a knowledge cutoff date, which is nothing but the date until which the data is used train the model. This means it might not be able to provide information about very recent events or real-time data. For example,

• It wouldn’t be able to tell you the precise, current price of a flight ticket for tomorrow.
• It wouldn’t be able to tell you the details of any world wide event which took place just yesterday.

This is where we have more advanced feature of Generative AI which is able to solve the above mentioned limitations by going a step further i.e. AI Agents

AI Agents

An AI Agent is a more advanced system that can not only generate content but also take actions to complete tasks. Think of an AI Agent as a program that can perceive its environment, think, and act to achieve a specific goal.

You can also compare an AI Agent with a real world agent, which we contact for any kind of assistance related to booking of flight tickets, train tickets or assistance in finding a house in a locality and so on

To overcome the limitations of a simple LLM, an AI Agent is typically given access to tools, such as APIs for travel websites or weather services. This allows the agent to fetch real-time information and perform actions like identifying and suggesting the cheapest flight for you. The key here is that the agent has a degree of autonomy and can make independent decisions to complete a task.

Now what if we need to plan an entire trip to a specific destination, which not only includes booking of flights, hotels, but also getting details on sightseeing and their timings, travel cab agency, best local food recommendations and restaurants to try out. This is where a single AI Agent won’t be able handle multiple of these tasks, as it would be trained for a specific task — which will be booking Flight tickets.

This issue can be further solved using an Agentic AI Workflows

The Power of Agentic AI / Agentic AI Workflows

Agentic AI takes a step further in which it refers to a system where one or more AI agents work together autonomously to accomplish complex, multi-step goals. These systems can handle long-running tasks and make decisions using a variety of tools and even other AI agents.

Consider the sophisticated travel planning scenario which we discussed above. An Agentic AI system could not only book a flight within a specific budget but also check the weather for your destination to ensure it’s sunny during your trip. It could even go a step further and collaborate with an “Immigration AI Agent” to verify your visa status before finalizing the flight booking. This demonstrates the multi-step reasoning, planning, and collaborative capabilities of Agentic AI.

Understanding the Overlap

It’s important to understand that these differences are not always a hard and fast distinction. There is always going to be an overlap of concepts across them. For instance, an AI Agent will often utilize Generative AI capabilities to understand prompts or formulate responses. Similarly, an Agentic AI system is, by definition, composed of multiple AI Agents.

Think of it as a spectrum rather than rigid boxes. As AI technology advances, the lines between these categories may continue to blur, with systems incorporating elements from each to achieve increasingly complex objectives.

The Bottom Line

The progression from Generative AI to AI Agents and then to Agentic AI represents an increase in the complexity, autonomy, and sophistication of the tasks that AI can perform.

• Generative AI: Focuses on creating new content with the help of the past historic data on which it is trained on
• AI Agents: Perform autonomous actions to achieve specific tasks, often leveraging external tools or APIs.
• Agentic AI: Involves multiple agents collaborating to achieve complex, long-term, multi-step goals.

As this technology continues to develop, we can expect to see even more powerful and capable AI systems that can assist us in all aspects of our lives.

— Rajas Walavalkar

Introductions

Amazon QuickSight is a scalable, serverless business intelligence (BI) service powered by machine learning (ML). It seamlessly integrates data sources from Amazon Web Services (AWS), other cloud providers, and on-premises environments. Commonly utilized data sources within QuickSight include Amazon Simple Storage Service (Amazon S3), Amazon Athena, Amazon Relational Database Service (Amazon RDS), and Amazon Redshift.

Once connected to a data source, QuickSight allows users to create datasets that can operate in either SPICE (Super-fast, Parallel, In-memory Calculation Engine) or direct query mode. These datasets serve as the foundation for building interactive dashboards, paginated reports, and Q topics, enabling intuitive natural language querying capabilities over the underlying data.

Benefits of SPICE Memory

• SPICE, Amazon QuickSight’s in-memory calculation engine, accelerates dashboard performance by ingesting data from sources based on scheduled, on-demand, or API-driven refreshes.
• It utilizes columnar storage, in-memory technologies, and machine code generation for rapid interactive query responses on large datasets. During dataset refreshes, dashboards display the previous snapshot to ensure uninterrupted user interaction.
• SPICE reduces data source workload by querying it only when necessary for data ingestion. Charges for SPICE usage are based solely on purchased storage capacity in gigabytes (GB), allowing data consumption across multiple dashboards and users without additional costs.
• For security, data stored in SPICE is encrypted at rest, with AWS managing the encryption key by default or allowing customer-managed keys through AWS Key Management Service (AWS KMS). This setup supports auditing access and compliance with regulatory requirements, with immediate access lockdown by revoking AWS KMS key access.
• Data transfers between SPICE and users are encrypted in transit using Transport Layer Security (TLS), ensuring secure data transmission throughout.

When to use SPICE Dataset Memory?

A common use case is to use the APIs to invoke a SPICE data refresh once extract, transform, and load (ETL) processes have been completed and data is stored in the data lake or data warehouse. This on-demand refresh process involves initiating a data ingestion either through the QuickSight UI or using the QuickSight API call CreateIngestion. A point to note here is that you cannot initiate an on-demand full refresh or incremental refresh after the limit has been reached. However, this doesn’t impact schedules that are configured; they will continue to run as planned.

Another important consideration is the total SPICE capacity, which is allocated at an account level. If this available capacity is consumed by either a single dataset or multiple datasets, this will lead to data ingestion failures. To prevent such failures, you can manually purchase additional SPICE capacity from the QuickSight UI as needed. Alternatively, you can enable the Auto-purchase capacity option, which allows SPICE to automatically acquire the necessary capacity and so avoid data ingestion failures.

Benefits of using Direct Query Mode

Direct query mode enables near real-time querying directly from the data source. When a user opens or refreshes a dashboard in QuickSight, queries are sent directly to the data source. The data source processes these queries and returns results that are immediately visualized in the dashboard. Utilizing direct query mode can provide the benefits of accessing the freshest data from regularly updated sources and maintaining high-performance dashboards, especially when the data source responds quickly.

Unlike SPICE, direct query mode does not impose limits on the number of rows or dataset size, making it suitable for scenarios where data exceeds SPICE’s capacity constraints. To optimize query performance for slower queries, dataset parameters can be used to filter data and reduce the volume queried. User interactions within the dashboard, such as selections in controls, filters, and visuals, can propagate to the data source through live, customized, and parameterized SQL queries.

When to use Direct Query Mode?

• Performance in direct query mode depends on the runtime performance of the data source. Longer query execution times can lead to longer wait times for dashboard visuals compared to SPICE datasets. For a good user experience, it’s recommended to use a data source with fast response times when using direct query mode.
• QuickSight imposes a 2-minute timeout for generating dashboard visuals, applicable to both direct query mode and SPICE. Not all database drivers may respond to this timeout by cancelling queries; manual or programmatic cancellation may be required.
• Direct query mode supports up to 2,000 columns per dataset. Data source-specific timeouts also apply and vary for each type of data source.
• Scaling up the capacity of the data source may be necessary as data volume grows, which can incur additional costs. High concurrency in direct query mode, especially with frequent dashboard users, can overwhelm the data source’s ability to handle the query load effectively.

Different Scenarios with their Outcomes

Dashboard Data Updates Daily

Dashboard data often requires daily updates to reflect the previous day’s information. Typically, this involves extracting data from source systems, transforming it, and loading it into a data lake or data warehouse through batch ETL processes, usually scheduled outside of regular business hours. The recommended approach for using SPICE depends on how data updates occur in the source system.

• If individual records are updated in the source system, SPICE with a daily full refresh is suitable.
• For scenarios where data is primarily appended, SPICE with a daily incremental refresh is preferable to manage new additions.
• Align SPICE data refresh with the completion of ETL processes by scheduling it at a fixed time each day.
• If ETL processing times vary, invoke SPICE refresh programmatically using the QuickSight API after ETL completion.

Dashboard Data Updates every 20 minutes

If you require more real-time dashboard data updates, especially when new data is appended with a creation timestamp, consider scheduling a SPICE incremental refresh instead of a full refresh. This refresh can occur as frequently as every 20 minutes.

To achieve even more frequent updates, such as every 10 minutes, while leveraging SPICE for enhanced dashboard performance, you can combine different data refresh methods — full refresh, incremental refresh, and API refresh — on the same dataset.

This strategy optimizes data freshness and performance, adapting to both real-time data needs and operational efficiency throughout the day.

Dashboard Data has Batch Data Updates and Near Real-Time Data Updates

Dashboards often display diverse data types with varying dimensions and temporal granularity. For instance, a dashboard’s landing page typically showcases high-level key performance indicators (KPIs), monthly trends, and aggregated data across different dimensions. A secondary sheet within the dashboard delves deeper, offering near real-time insights at a transactional level.

Amazon QuickSight supports the integration of multiple datasets within a single dashboard. In this setup, the landing page utilizes a SPICE dataset scheduled for daily updates, ensuring fast performance with aggregated data. Meanwhile, the secondary sheet requires continuously fresh data directly from the source, utilizing direct query mode for this dataset.

Dashboard Data refreshes in Real-Time

When dashboards require immediate access to business-critical operational data, retrieving information in near real-time directly from the data source is crucial. Direct query mode stands out as the optimal solution for displaying data promptly on visuals.

In this mode, QuickSight queries data directly from the source for each visual upon dashboard loading, ensuring users always have access to the latest data available. This approach minimizes delays and supports timely decision-making and actions based on current information.

Conclusion

Thus mastering the best practices of Amazon QuickSight, particularly with SPICE datasets and Direct Query mode, is pivotal for optimizing data visualization and analysis workflows. SPICE’s in-memory calculation engine empowers users with faster query responses and offline access capabilities, making it ideal for interactive dashboards and reports. On the other hand, Direct Query mode ensures real-time data freshness directly from the source, essential for scenarios requiring up-to-the-minute insights.

By strategically choosing between SPICE and Direct Query based on data requirements and usage patterns, organizations can enhance dashboard performance, manage costs efficiently, and empower decision-makers with timely, accurate information. Implementing these best practices not only improves operational efficiency but also maximizes the value derived from Amazon QuickSight, driving continuous improvement in business intelligence strategies.

INTRODUCTION

The dynamic duo of data processing Amazon Athena and Apache Spark Engine. In the realm of big data analytics, efficiency and flexibility are paramount, and this powerful combination delivers just that. Amazon Athena, a serverless interactive query service, enables users to analyze vast datasets stored in Amazon S3 with standard SQL queries, without the need for complex infrastructure management.

Now, with the integration of the Apache Spark Engine, Athena elevates its capabilities to new heights. Apache Spark, renowned for its lightning-fast processing speeds and versatile data processing capabilities, enhances Athena’s querying power, enabling users to tackle even the most demanding analytics tasks with ease. Amazon Athena With Apache Spark Engine will offer a seamless and scalable solution for organizations seeking to unlock insights from their data quickly and efficiently, ushering in a new era of data-driven innovation.

Benefits Of Spark Engine for Athena

1. Accelerate time to insights — You can spend more time on insights, not on waiting for results. Interactive Spark applications start in under a second and run faster with our optimized Spark runtime.

2. Leverage Spark for complex, powerful analytics — Use the expressiveness of Python with the popular open-source Spark framework to seek more complex insights from your data. Use notebooks to query data, chain calculations, and visualize results.

3. Build applications without managing resources — Run Spark applications cost-effectively, without provisioning and managing resources. Build Spark applications without worrying about Spark configurations or version upgrades.

4. Work with your data where it lives — Work with data in various data lakes, in open-data formats, and with your business applications without moving the data. Use data discovered and categorized by AWS Glue to build your Spark insights.

So let’s now see how we can spin up the entire tech stack which can power Spark Engine with Athena and give us the desired results

Pre-requisites

1. Full Access of Athena and its features
2. Access to create and Modify IAM roles
3. Access to S3 buckets and to upload files and to edit bucket policies

STEP 1: Create a new Athena Workgroup

Open the Amazon Athena Console which will redirect you to the Athena Query Editor console, over there you can open up the left side panel and then click on Workgoups

Over here you will have an already existing workgroup default which is a default workgroup for any AWS account. So let now click on Create Workgroup button to create a new Workgroup. Over fill in the details as follows

• Workgroup name: demo-spark-workgroup
• Analytics Engine Type : Select the analytics engine as Spark
• Analytics Engine Version : Select the option PySpark Engine Version 3

Keep the remaining parameters same and then you can click on Create Workspace.

Note: Once the workgroup is created, we need to update the IAM role which is attached to this workgroup. In this IAM role add access the permissions to read the data from the S3 bucket where you have the data stored

Once we have created the Spark Workspace, we can move with the next step

STEP 2: Create a Spark Notebook within the Workspace

After the creation of Workspace, there you will get an option to create a notebook, then click on Create Notebook

You can enter a name for your notebook, in our example we are going to use spark-notebook. In the Apache Spark properties, you can define the table format of the source tables. Currently Athena Spark Engine supports 4 types of table formats

As per your table requirement, you can select the table format as per the requirements. For our demo purpose, we are not going to select any specific table format and going to keep it open. Going ahead you can see the session parameters that you can define as per the processing power required in your Spark notebook

Now click on Create, which will create our Spark Notebook. Once the spark notebook is created you can actually see the Python IDE interface of a python notebook.

STEP 3: Try out some code in Spark Notebook

Lets import some of the Python and Spark libraries into the code and then run the cell

from pyspark.sql.functions import max
import calendar
import seaborn as sns
import matplotlib.pyplot as plt

noa_22_csv = spark.read.option("header","true").csv(f"s3://<S3-bucket-name-path>/2022/")
noa_22_csv.printSchema()

noa_22_csv.createOrReplaceTempView("noa_22view")
# UDF to convert month number to month name
spark.udf.register("month_name_udf",lambda x: calendar.month_name[int(x)])

min_temp_df=spark.sql("select month_name_udf(month(to_date(date,'yyyy-MM-dd'))) as month_yr_22,\
min(min) as min_temp \
from noa_22view \
group by 1 order by 2 desc")

min_temp_df.createOrReplaceTempView("min_temp_view")

min_temp_df.show(10)

The example shows how, we can leverage the Athena Spark engine to run our spark based codes on Athena and perform analytics on the same

CONCLUSION

Thus the integration of Amazon Athena with the Apache Spark Engine represents a significant advancement in the field of data analytics. By combining the ease of use and scalability of Amazon Athena with the powerful processing capabilities of Apache Spark, organizations gain access to a comprehensive solution for analyzing large datasets with unprecedented speed and efficiency.

This synergy not only simplifies the process of querying and analyzing data but also opens up new possibilities for extracting valuable insights and driving informed decision-making. As businesses continue to grapple with the complexities of big data, the collaboration between Amazon Athena and Apache Spark Engine emerges as a transformative force, empowering organizations to stay ahead in today’s data-driven world.

REFERENCES

1. Apache Spark on Amazon Athena — https://docs.aws.amazon.com/athena/latest/ug/notebooks-spark.html
2. Setting up Spark on Athena — https://docs.aws.amazon.com/athena/latest/ug/notebooks-spark-getting-started.html
3. Troubleshooting Notebooks — https://docs.aws.amazon.com/athena/latest/ug/notebooks-spark-troubleshooting-workgroups.html
4. Python Libraries Sypported — https://docs.aws.amazon.com/athena/latest/ug/notebooks-spark-preinstalled-python-libraries.html
5. JARs and Custom configuration — https://docs.aws.amazon.com/athena/latest/ug/notebooks-spark-custom-jar-cfg.html

INTRODUCTION

In most of the scenarios where we have applications and Amazon Cognito being used as an User Authentication and Authorization, there are requirements of accessing AWS services depending on the user roles. Access to application users depends on the user groups or roles which are tied to the Business application of the web application. Thus it becomes necessary to tie back the application users to the AWS service access depending on the authorization levels defined

Today, we are going to see how to achieve this functionality in any kind of web application. For this we are going to implement the above access methodology defined in the architecture image shown. So let’s get started with the implementation

Step 1: Generate the ID Tokens using User Credentials

At this step, we need to understand that, the user is already registered in the Amazon Cognito User pool and there are Cognito groups defined, where the user is part of the group.

Every Cognito group have been assigned an IAM role which defines AWS service level access for the users. These IAM roles are also defined in the AWS Lake formation principals which defined the AWS Database and tables access in Glue Data Catalog

import boto3

client=boto3.client("cognito-idp")

#Add your Cognito Identity Pool ID in this variable
identityPoolId='<Cognito-Identity-PoolID>'

#Define the User Credentials for the user for which Token's needs to be generated
user_name='<UserName>'
user_password='<User-Passowrd>'

#Define the Client ID for the application defined in the Cognito User Pool
client_id='<Cognito-application-client-id>'

response = client.initiate_auth(
    AuthFlow='USER_PASSWORD_AUTH',
    AuthParameters={
        'USERNAME': user_name,
        'PASSWORD': user_password
    },
    ClientId=client_id
)

In the above code, we are defining the User credentials and then the Cognito application client id which is registered with the Cognito user pool. The response that we will get will be the tokens of the users

If the user’s credentials are correct then you will get response of the above boto3 API and then you can check the response variable which will be the Cognito token id

STEP 2: Pass the Cognito ID Token and then generate the Identity id

At this step we will be passing the previously obtained Cognito ID token and then will be getting the Identity ID for the user. For this we need to define the Identity Pool ID (identityPoolId) which we have already defined in the previous step.

cognito_IdToken=response['AuthenticationResult']['IdToken']

print(cognito_IdToken)

cog_client=boto3.client("cognito-identity")

#Need to define the Cognito IDPusing the URL as cognito-idp.<region>.amazonaws.com/<user-pool-id>
response = cog_client.get_id(IdentityPoolId=identityPoolId, Logins={'cognito-idp.<region>.amazonaws.com/<user-pool-id>': cognito_IdToken})

Then we create cognito-identity client using boto3 and then use the get_id API. Once we hit this API we will get the identity id for the user

Once we get the identity id, we need to generate the temporary user credentials using the API

STEP 3: Pass the Identity ID and then generate the temporary user credentials

At this stage the Identity ID which we received from the previous API call will be leveraged to call the get_credentials_for_identity API and generate the temporary credentials of the user

IdentityId=response['IdentityId']

#Need to define the Cognito IDPusing the URL as cognito-idp.<region>.amazonaws.com/<user-pool-id>
response = cog_client.get_credentials_for_identity(IdentityId=IdentityId, Logins={'cognito-idp.<region>.amazonaws.com/<user-pool-id>': cognito_IdToken})

access_key_id=response['Credentials']['AccessKeyId']
secret_key=response['Credentials']['SecretKey']
session_token=response['Credentials']['SessionToken']

print("Access Key ID : "+str(access_key_id))
print("Access Scret Key : "+str(secret_key))
print("Session Key : "+str(session_token))

STEP 4: Leverage the temporary credentials to access the AWS services

Now, after we got the AWS Secret keys of the users, then we can access the AWS services using boto3 APIs. For that we need to define the temporary credentials while creating the client of the server. Just follow the following code for the same

s3_client = boto3.client(
    's3',
    aws_access_key_id=access_key_id,
    aws_secret_access_key=secret_key,
    aws_session_token=session_token
)

response = s3_client.list_objects_v2(
    Bucket='<S3-bucket-name>'
)


athena_client = boto3.client('athena',
    aws_access_key_id=access_key_id,
    aws_secret_access_key=secret_key,
    aws_session_token=session_token
)

response = athena_client.start_query_execution(
    QueryString='SELECT * FROM "<database-name>"."<table-name>" limit 100;',
    WorkGroup='primary'
)

This is the way in which you can access the various AWS services depending on the user who is logged in to the application by generating the user specific temporary credentials. Thus all the APIs in which we will be using these credentials will only be getting the access defined for the user which has been defined in the cognito

In case if the user does not have access to a specific S3 bucket or a specific Database and table in the Glue data Catalog, the APIs will give an error as Access Denied, as shown in the image below.

CONCLUSION:

Amazon Cognito User Pools serves as a robust and scalable solution for managing user authentication and authorization in web and mobile applications. With its seamless integration with other AWS services, extensive support for various authentication methods, and built-in security features, Cognito User Pools empowers developers to rapidly build and deploy secure applications without the complexities of managing user identities.

By offering features such as multi-factor authentication, user profile management, and fine-grained access control, Cognito User Pools enables organizations to deliver a seamless and secure user experience while maintaining compliance with industry standards and regulations. As a foundational component of the AWS ecosystem, Cognito User Pools plays a pivotal role in enabling organizations to innovate and deliver value to their users in today’s digital landscape.

REFERENCES:

1. Amazon Cognito Overview — https://aws.amazon.com/cognito/
2. Amazon Cognito User Pools — https://docs.aws.amazon.com/cognito/latest/developerguide/cognito-user-identity-pools.html
3. Amazon Cognito Identity Pools — https://docs.aws.amazon.com/cognito/latest/developerguide/cognito-identity.html
4. Get Credentials -https://docs.aws.amazon.com/cognito/latest/developerguide/getting-credentials.html
5. Amazon Cognito with token generation — https://aws.amazon.com/blogs/publicsector/getting-started-healthcare-data-lakes-deeper-dive-amazon-cognito/

INTRODUCTION

Welcome to the gateway of boundless data possibilities — AWS Data Exchange. In the era of information abundance, AWS Data Exchange emerges as a pivotal platform, seamlessly connecting data providers with consumers, facilitating the exchange of high-quality, curated data sets. Whether you’re a data scientist seeking to enrich your analytics, a business leader aiming to fuel strategic decisions, or a developer looking to enhance your applications, AWS Data Exchange offers a diverse array of data products, coupled with the reliability, scalability, and security synonymous with Amazon Web Services.

AWS Data Exchange offers a plethora of advantages for both data providers and consumers, making it a premier platform for data exchange:

1. Diverse Data Selection: Access a wide range of high-quality, third-party data sets from various industries, including financial services, healthcare, retail, and more. This breadth of data options allows consumers to find precisely the data they need to drive insights and innovation.
2. Data Curation and Quality Assurance: Data listed on AWS Data Exchange undergoes rigorous curation and quality assurance processes, ensuring accuracy, reliability, and compliance with industry standards. This assurance mitigates risks associated with using unreliable or unverified data sources.
3. Streamlined Data Acquisition: Simplify the process of acquiring external data by leveraging AWS Data Exchange’s intuitive platform. With standardized data delivery mechanisms and easy integration with AWS services, consumers can quickly access and ingest data into their analytics pipelines or applications.
4. Flexible Subscription Models: Choose from various subscription models, including free, one-time purchase, or subscription-based pricing, depending on individual data consumption needs and budget constraints. This flexibility enables consumers to align their data access costs with their usage patterns effectively.
5. Secure Data Exchange: Benefit from the robust security measures inherent in the AWS ecosystem, including encryption, access controls, and compliance certifications. AWS Data Exchange prioritizes data privacy and security, ensuring that sensitive information remains protected throughout the exchange process.
6. Scalability and Performance: Leverage the scalability and performance capabilities of AWS infrastructure to handle large volumes of data efficiently. Whether processing real-time streaming data or performing complex analytics on massive datasets, AWS Data Exchange delivers the performance needed to meet evolving business requirements.
7. Integration with AWS Services: Seamlessly integrate AWS Data Exchange with other AWS services, such as Amazon S3, Amazon Redshift, and AWS Lambda, to leverage additional capabilities for data processing, storage, and analysis. This integration simplifies workflows and enables consumers to derive maximum value from their data assets.

Let’s get our hand’s dirty on the AWS console itself by creating a Data Exchange to share data across datasets

Step 1: Create a Data Exchange Dataset

Go to the AWS Data Exchange home page and navigate to the Owned datasets and then click on the Create data set, to create a new Dataset for the data exchange

Here you will get multiple options to select data set type, for our demo purpose lets stick to Amazon S3 data access and provide a name to the dataset s3-covid-data and then click on Next

Next, we need to configure the S3 data access, for that lets click on Choose Amazon S3 Location where you can browse to the s3 location and select a particular location which will be used by the dataset. When you scroll down you see a bucket policy — which you need to copy and add it to the specific S3 bucket as a bucket policy. This policy will ensure that the S3 access is allowed to the Data Exchange jobs. Keep the remaining configurations default and then click on Next

At this stage it will create a backend job to import the data into the dataset. once your job is completed, the click on create data set and this will complete the process of creating a dataset

Step 2: Create a Data Grant for the Dataset

Data Grant is where we can grant access to our previously created dataset to different AWS accounts. For this lest navigate to the Sent Data Grants subsection and then lets create a data grant

On the first section, we need to select the dataset which we own and have create din the previous step.

In the next step add a Grant name and some description to the grant and then click on next button. In the next page we need to define the time period for which the grant will be valid by the receiver account. You need add the receiver AWS accounts Account ID information first

Once you add the receiver’s AWS account ID, then we need to define the Access end date for our data grant. In this there are two options to select.

• No End Date — Here there wont be any specific end date for the data grant to be expired automatically and the receiver account will be able to access the dataset which is shared without any time period boundaries
• Specific End Date — Here you can define a specific end date for the Data grant till the point it will be accessible by the receiver’s AWS account.

On the last page, you can review the entire grant and option and then click on Create and Sent data grant

Once you click on Create Data Grant — it will execute a process in the backend , which you can see the status of it. Once the data grant status is Completed then you can proceed with the next step

Step 3: Accept the Data Grant for the Receiver’s AWS Account

Up to this step — the sender AWS account has already done its work of creating a Dataset and sending a Data grant to the receiver’s account. Now, the receiver’s AWS account need to accept the Grant which has been received. For our demo, purpose, let’s now login and to the receiver’s AWS account and check the Data grant

For this, you need to open the AWS Data Exchange service console and then navigate to the Received data grants section. On this screen — you will see the Pending data grants which you need to accept.

Let’s now click on the data grant request that is received and then proceed with the acceptance. Once you click — you will be able to see all the details of the data grant received. Once you have reviewed all the details then you can click on Accept data grant button

Once you can click — on Accept, it will take some time of 5 minutes to complete the granting process. Once the process is completed, then you can click on it and browse the S3 location by clicking on the Browse shared s3 location

Once you can click on the browse S3 location, you will be able to see the s3 paths and files which can be browsed from the console itself and download as well the files

CONCLUSION

AWS Data Exchange stands as a cornerstone of modern data ecosystems, offering unparalleled advantages for both data providers and consumers alike. By providing access to a diverse array of high-quality, curated data sets from various industries, AWS Data Exchange empowers organizations to unlock insights, drive innovation, and make informed decisions with confidence. Through streamlined data acquisition processes, flexible subscription models, and robust security measures, AWS Data Exchange ensures that data exchange is efficient, cost-effective, and secure.

With seamless integration with AWS services, scalability, and global accessibility, AWS Data Exchange is poised to revolutionize the way organizations leverage external data assets, fostering collaboration, and driving transformative outcomes across industries. As the data landscape continues to evolve, AWS Data Exchange remains at the forefront, facilitating the exchange of data-driven insights that fuel success in the digital age.

REFERENCES

1. AWS Data Exchange Overview — https://docs.aws.amazon.com/data-exchange/latest/userguide/what-is.html
2. Data in AWS Exchnage — https://docs.aws.amazon.com/data-exchange/latest/userguide/data-sets.html
3. Create Data grant — https://docs.aws.amazon.com/data-exchange/latest/userguide/creating-data-grants.html
4. Accept Data Grant — https://docs.aws.amazon.com/data-exchange/latest/userguide/data-grant-access-data-set.html
5. Subscribing Data Marketplace — https://docs.aws.amazon.com/data-exchange/latest/userguide/subscribe-to-data-sets.html

INTRODUCTION

In the dynamic landscape of data management, efficiency is paramount. Enterprises today grapple with the challenge of seamlessly transferring data between various databases and warehouses while maintaining integrity, security, and speed. Amazon Web Services (AWS) offers a robust solution to this conundrum with Amazon Aurora MySQL and Redshift, two powerful services tailored to handle heavy workloads and vast datasets.

The advent of “zero ETL” strategies has further revolutionized data processing, allowing organizations to bypass traditional Extract, Transform, Load (ETL) processes and directly ingest data into Redshift from Aurora MySQL in near real-time. This blog delves into the intricacies of this cutting-edge approach, exploring the benefits, implementation strategies, and best practices for achieving seamless data integration between Aurora MySQL and Redshift, thus optimizing data pipelines for enhanced performance and agility.

Why do we require Zero ETL Data pipelines?

1. Real-Time Analytics: Businesses increasingly rely on real-time insights to make timely decisions. Traditional ETL processes introduce latency, delaying the availability of data for analysis. By bypassing ETL and directly transferring data from Aurora MySQL to Redshift, organizations can achieve near real-time analytics, gaining a competitive edge in today’s fast-paced markets.
2. Data Freshness: With the exponential growth of data, maintaining data freshness is crucial for accurate insights. Zero ETL enables organizations to ingest data into Redshift without delay, ensuring that analytics are based on the most up-to-date information available in Aurora MySQL.
3. Capturing Data History : Operational database has a lot of CRUD operations which constantly keeps on updating the data as per the business need. Thus for analyzing trends we need to maintain history of the data.
4. Scalability and Flexibility: As data volumes and processing requirements scale, flexibility becomes paramount. Zero ETL architectures facilitate seamless scalability, allowing organizations to adapt to changing business needs without the constraints imposed by traditional ETL processes.

Lets quickly get our hands dirty by implementing this zero ETL feature by considering source Aurora MySQL and Amazon Redshift as a destination.

Pre-requisite required for Demo

1. Aurora MySQL database — required as a source. You need to choose MySQL DB engine
2. Amazon Redshift — required as a target. (in this example I am using a serverless instance)

Step 1: Create a Cluster Parameter Group

Create a new Cluster Parameter Group with name as zero-etl-custom-pg-mysql and with the following configurations

• binlog_backup=0
• binlog_replication_globaldb=0
• binlog_format=ROW
• aurora_enhanced_binlog=1
• binlog_row_metadata=FULL
• binlog_row_image=FULL

STEP 2: Attach the Cluster Parameter Group to Aurora MySQL Cluster

Cick on the cluster node and then click on Modify to update the configuration of the Aurora instance and then in the Addtional Configuration section, you can select the Cluster Parameter grop which created in the previous step

Once you click on Continue button, it will ask you if you want to apply the effect immediately, then for development purpose — you can chose apply immediately, but if it was Production Database cluster then you can apply it on the next maintenance window. As it is a development environment so I am going select Apply Immediately and then click on Modify cluster

Once, this is done, you can check the configuration and see the cluster parameter group for the clsuter which will be the one which had attached just now

STEP 3: Creation of Zero-EL integration

1. In the Amazon RDS console and navigate to the cluster DB and then go to the Zero-ETL Integration section and then click on Create Zero-ETL Integration

2. provide a name — first-zero-etl-integration

3. Source will be automatically selected with the current Database cluster

4. Target — Select the current AWS account option and then browse the target Redshift cluster which you want to leverage as a target for the integration. Once you select the target redshift, you will see an error message as shown below

5. Here you need to click on Fix it for me option which will take care of the configuration required for case sensitivity parameter from the Redshift side and then click on Next and then you will get a Pop-up message box, click on Continue

6. On the Review page, just review all the configuration and the click on Create zero-etl integration, then you get an error shown below

7. At this stage wait for sometime, as its making change to the Redshift configuration which will take sometime and once you get the following message, then you can click on Create zero-etl integration

NOTE: At this stage if you are getting any error then check the AWS documentation (Link) for any sort of limitation in the source database engine versions supported for zero-etl integration

8. The integration would take sometime to complete and you can check the status from the console itself. It can take up to 15–20 mins for the integration to complete, so just be Patient! :)

STEP 4: Configure target Redshift to accept the Integration

Go to the Redshift console, you can check Redshift serverless namespace, that you had selected in the target while creating the integration. There you will see the integration in the Zero-ETL Integration tab

For that page, you can note down the Integration ID and then we will use it in the next step while creating connection to Aurora MySQL source

Next Open the Query-Editor V2 for Redshift namespace and then lets create a database for the Aurora MySQL source using a Create Database query

CREATE DATABASE aurora_mysql_zeroetl_db FROM INTEGRATION ‘<add-your-integration-id>’;

Once you run the above query by replacing the Integration ID with yours Id then it will create a database in Redshift which will be pointing to the Aurora MySQL DB as shown below in the diagram

Currently, you wont see any tables or schemas, as the source MySQL DB is currently empty and there are no DB objects

STEP 4: Add Databases and Tables to Aurora MySQL DB and check whether we can access it from Redshift namespace

Lets, first connect to the Aurora MySQL DB using any IDE that you are comfortable with. I am using MySQL Workbench for the same. Lets run the below SQL script which will create two Tables and will insert some dummy records into it

Create Database aurora_source_mysql; 

Create table aurora_source_mysql.student (id int(10) PRIMARY KEY, name varchar(50), location varchar(50), department varchar(100));

Insert into aurora_source_mysql.student(id,name,location,department)
Values (1,'Suresh Kumar', 'Mumbai', 'Computer Science'),
(2,'Siddhesh Mehta', 'Delhi', 'Computer Science'),
(3,'Anant Shah', 'Mumbai', 'Electronics'),
(4,'Chirag Patil', 'Mumbai', 'Computer Science'),
(5,'Jayesh Choubey', 'Delhi', 'Electronics');

Select * from aurora_source_mysql.student;

Create table aurora_source_mysql.grades (grade_id int(10) Primary Key,student_id int(10), subject varchar(50), grade varchar(50), result varchar(100),
FOREIGN KEY (student_id) REFERENCES student(id));

Insert into aurora_source_mysql.grades(grade_id,student_id,subject,grade,result)
Values (101,1,'JAVA', 'A+', 'PASS'),
(102,2,'Networking', 'A', 'PASS'),
(103,3,'Micro Controllers', 'C', 'PASS'),
(104,4,'Python Programming', 'F', 'FAIL'),
(105,5,'Digital Analog', 'B', 'PASS');

Select * from aurora_source_mysql.grades;

Now lets see the MAGIC!

Lets navigate to the Redshift Visual Editor V2 console and just refresh the Database and tables panel which is there on the left side to see whether the changes are reflected here in the redshift as well.

Now, you might not see the Magic instantaneously, as its running the integration process in the backend and it might take some 5– 10 seconds to see both the tables here in the Redshift

To check the Integrations which are running in the backend, you can run the folowing query and see outputs

SELECT * FROM SYS_INTEGRATION_ACTIVITY; 

Once you open up the Databases and Schema on Redshift you should be able to see all the tables and databases defined in the Aurora MySQL DB source

Lets now, run a query and see if we are able to access the data from both the tables on redshift, for that just run the below query and see the Magic!

Select * FROM aurora_source_mysql.student s inner join  aurora_source_mysql.grades g on s.id = g. student_id; 

Awesome! We are successfully created a ZERO-ETL Integration between Source Aurora MYSQL DB serverless and Amazon Redshift Serverless

But, now lets make it more practical and real-life usecase by allowing only certain tables to be pushed down to the target rather than integrating all the tables from the source. In a real-life scenario, you will only be integrating the required tables and not all the tables to the Redshift Warehouse Layer.

STEP 5: (OPTIONAL) Filtering only required tables from the source to be integrated with the target Redshift cluster

Let’s again navigate back to the Zero-ETL integration screen on the RDS console and lets modify the current integration which is created. When you click on modify button, then you can see the filter option, where you can see two options possible

• Include — Where only certain DB objects are included in the integration to be pushed down to the target
• Exclude — Where you define specific DB objects which are skipped or excluded from being pushed to the target

For our example I am going to use Include option, in which I am going to include the student table in the integration so that only student table will be pushed to the target.

Now modifying the Integrations can take good amount of time like somewhat 20–45 minutes as its going to update the backend logic to filter the DB objects from the integration, so be Patient with it. Also the best option would be to add the filter conditions while you are creating the integration so that you avoid modifying it later.

Once the modification is completed successfully, then lets navigate to the Redshift Query Editor V2, to see what effect it has on the target database.

Form the above image, you can see that we are only able to see the student table and no other table apart from that. Lets now try running the previous JOIN query between student and grade table and then see the what output we get.

From the above image, we can see that grades table does not exist. So from here we can conclude that the when you remove a data filter from an integration, Aurora reevaluates the remaining filters as if the removed filter never existed. Aurora then replicates the data that previously didn’t match the filtering criteria (but now does) into the target Amazon Redshift data warehouse. Removing one or more data filters causes all affected tables to resynchronize.

CONCLUSION:

So the journey towards a streamlined data pipeline through Amazon Aurora MySQL zero ETL to Redshift represents a pivotal shift in modern data management practices. By embracing this innovative approach, organizations can unlock new levels of agility, efficiency, and real-time insights. The elimination of traditional ETL bottlenecks not only accelerates data processing but also reduces costs and complexity, empowering businesses to stay competitive in today’s data-driven landscape.

As organizations continue to evolve and scale, the integration of Amazon Aurora MySQL and Redshift offers a scalable and flexible solution that aligns seamlessly with their growing data needs. Through strategic implementation and adherence to best practices, businesses can leverage this powerful combination to drive actionable intelligence, foster innovation, and ultimately achieve their goals in an increasingly data-centric world.

REFERENCE:

1. Zero-ETL Integration Overview and Benefits — https://aws.amazon.com/rds/aurora/zero-etl/
2. Aurora Zero-ETL integration with Redshift AWS documentation— https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/zero-etl.html
3. Setting up Zero-ETL integration for Redshift — https://docs.aws.amazon.com/redshift/latest/mgmt/zero-etl-using.setting-up.html
4. Zero-ETL integrations LIMITATIONS — https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/zero-etl.html#zero-etl.reqs-lims
5. Zero-ETL Quota Limits — https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/zero-etl.html#zero-etl.quotas
6. Amazon RDS Zero-ETL parameters configurations — https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/zero-etl.setting-up.html#zero-etl.parameters

INTRODUCTION

A data lake is a centralized repository that you can use to store all your structured and unstructured data at any scale. You can store your data as-is, without having to first structure the data and then run different types of analytics for better business insights. On AWS cloud the go-to service for creating a Data Lake is to use Amazon S3 service and then we leverage other services to perform analytics on the data lake

Currently there has been spike in a new type of usage pattern for data lake which is more like a transactional system rather then historic analytics. In transactional system the most important types of operations are Updates, Inserts & Deletes which was difficult and not so feasible in traditional S3 data lake structure. Thus there has been rise of a new concept of Open Table Formats — which actually help in performing CRUD operation on a data lake. One of the open table format is Apache Iceberg format.

What is Apache Iceberg Format?

Apache Iceberg is an open table format designed for huge, petabyte-scale tables. The function of a table format is to determine how you manage, organize and track all of the files that make up a table. You can think of it as an abstraction layer between your physical data files (written in Parquet or ORC etc.) and how they are structured to form a table.

Iceberg brings the reliability and simplicity of SQL tables to big data, while making it possible for engines like Spark, Trino, Flink, Presto, Hive and Impala to safely work with the same tables, at the same time.

Some of the important features of Apache Iceberg are as follows

• Transactional consistency between multiple applications where files can be added, removed or modified atomically, with full read isolation and multiple concurrent writes
• Full schema evolution to track changes to a table over time
• Time travel to query historical data and verify changes between updates
• Partition layout and evolution enabling updates to partition schemes as queries and data volumes change without relying on hidden partitions or physical directories
• Rollback to prior versions to quickly correct issues and return tables to a known good state
• Advanced planning and filtering capabilities for high performance on large data volumes

Seems INTERESTING ! Right? So lets see how can leverage Apache Iceberg format in AWS Glue Jobs

Create Apache Iceberg Data Lake using AWS Glue

To get started with this, there some pre-requisite activities that are needed here.

• Access to AWS services — AWS Glue, S3 & Athena
• Glue IAM service role which has access to Amazon S3
• An existing Glue Table which you can create by running just a simple crawler which will be used as a source for the Glue Job. In our example we will be using Covid table data
• An existing Glue database which we will be leveraging as a destination database to create a Iceberg table

Once you have the above pre-requisites completed, then we can start to write the Glue Spark job

1. Define some of the important spark and python libraries which are required in the job

import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from pyspark.sql import SparkSession
from pyspark.sql.functions import *
from awsglue.context import GlueContext
from awsglue.job import Job

args = getResolvedOptions(sys.argv, ["JOB_NAME"])
catalog_nm = "glue_catalog"

#The Glue Database Name which has the source table
in_database="<glue-database-input>" 

#The input Glue Table which we will be using as a source for the Iceberg data
in_table_name="covid_19_data"

#The Glue Database Name which will be used to create an output Icerberg table
database_op='database_ib'

#The Glue Table Name which will be used as a destination for Icerberg table
table_op='covid_dataset_iceberg'

#The S3 path which will be use to store the Iceberg files as output
s3_output_path='s3://<your-destination-bucket-name>/iceberg-output/'

table = str(catalog_nm)+'.`'+str(database_op)+'`.'+str(table_op)

print("\nINPUT Database : " + str(in_database))
print("\nINPUT Table : " + str(in_table_name))
print("\nOUPUT IcerBerg Database : " + str(database_op))
print("\nOUPUT IcerBerg Table : " + str(table))
print("\nOUPUT IcerBerg S3 Path : " + str(s3_output_path))

The above code snippet, will import all the required python and spark packages for our Glue script. Along with that we also need to define some important parameters and variables which will be used through out the script

In line with the script we need to define a important job parameter in the glue which will indicate the Glue job executer to leverage the Iceberg table format as output for the data. For this you need to define a parameter named as

--datalake-formats : iceberg

2. Define Spark and Glue context for the Iceberg Table format

In this step, we will be defining the spark iceberg object which will defined with the with Glue Catalog as a spark Catalog, S3 path as a Spark Data warehouse.

def create_spark_iceberg(catalog_nm:str = "glue_catalog"):
    """
    Function to initialize a session with iceberg by default
    :param catalog_nm:
    :return spark:
    """
    from pyspark.sql import SparkSession
    # You can set this as a variable if required
    warehouse_path = s3_output_path

    spark = SparkSession.builder \
    .config(f"spark.sql.catalog.{catalog_nm}",
        "org.apache.iceberg.spark.SparkCatalog") \
    .config(f"spark.sql.catalog.{catalog_nm}.warehouse", 
        warehouse_path) \
    .config(f"spark.sql.catalog.{catalog_nm}.catalog-impl",
        "org.apache.iceberg.aws.glue.GlueCatalog") \
    .config(f"spark.sql.catalog.{catalog_nm}.io-impl",
        "org.apache.iceberg.aws.s3.S3FileIO") \
    .config(f"spark.sql.extensions",
        "org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions") \
    .getOrCreate()
    return spark

ibspark = create_spark_iceberg(catalog_nm)
ibsc = ibspark.sparkContext
ibglueContext = GlueContext(ibsc)
ibjob = Job(ibglueContext)
ibjob.init(args["JOB_NAME"], args)

Once you define the Spark object, then we need to set the Spark Context and set the Glue Context. Once you have all the context set, then we can start to play with the source data and Iceberg table

3. Read the source Glue table and write into a destination Glue Table in Iceberg format

In this step, we will read the data from the existing source Glue Table which we have defined in our previous steps. Then we convert the glue dynamic frame into a spark DataFrame. The Spark DataFrame is then registered as a temp table/view so that we can leverage Spark SQL on it

#Read the Glue inout table from thr Catalog using a Glue DynamicFrame
InputDynamicFrameTable = ibglueContext.create_dynamic_frame.from_catalog(database=in_database, table_name=in_table_name)

#Convert the Glue DynamicFrame into a Spark DataFrame
InputDynamicFrameTable_DF = InputDynamicFrameTable.toDF()

#Register the Spark DataFrame as TempView
InputDynamicFrameTable_DF.createOrReplaceTempView("InputDataFrameTable")
ibspark.sql("select * from InputDataFrameTable LIMIT 10").show()

#Filter the source table with country as 'Australia'
colname_df = ibspark.sql("SELECT * FROM InputDataFrameTable WHERE country='Australia'")
colname_df.createOrReplaceTempView("OutputDataFrameTable")

#Write the filtered Data into an ICEBERG table format in Glue destination table
ib_Write_SQL = f"""
    CREATE OR REPLACE TABLE {catalog_nm}.{database_op}.{table_op}
    USING iceberg
    TBLPROPERTIES ("format-version"="2", "write_compression"="gzip")
    AS SELECT * FROM OutputDataFrameTable;
    """

#Run the Spark SQL query
ibspark.sql(ib_Write_SQL)

In our example, we will be filtering the table on a certain column and then write the data into a specific Glue database and table which we had defined earlier.

4. Let’s now Run the Glue Job and see the output in S3 and Query it through Athena

At this stage, you can save the Glue and then run the Glue job. Once the Glue job completes the execution then we can go ahead and check the Glue Database and Table.

The screenshot above provides detail on the table which we created though the Glue job. The Table format defined is Iceberg format and also has a defined S3 path where the output table files are stored in the Data Lake

Let’s take a look at the S3 destination and how the folder structure looks like

There are by default data and metadata folders created and which stores important data for the table. The data folder, as name suggests it stored the data files in the Iceberg format and the metadata folder only stores the metadata information about the table

Now lets start querying the Iceberg table using Athena. Once you query the table you can actually see the table has only filtered data for Australia which we had added in the Glue script.

Lets run a Update query on the table and see whether we are able to update the actual records in the Iceberg table. For that lets update Australia value in the country column to AUS.

UPDATE "database_ib"."covid_dataset_iceberg" SET country = 'AUS';

You can see that the query ran successfully and without any errors. Lets now actually see whether we can see the country column updated by again firing SELECT query.

In the above screenshot, you can actually see that all the records are updated with the new value of country which is AUS

CONCLUSION

Thus through this blog we can actually see how we can use Apache Iceberg table format through AWS Glue and then leverage other AWS services for manipulating the data. Iceberg format comes with a number of advantages from transactional query standpoint as well.

In this post, we introduced the Apache Iceberg framework and how it helps resolve some of the challenges we have in a modern data lake. I hope this post provides some useful information for you to get started with Apache Iceberg in your data lake solution.

REFERENCE:

1. Iceberg Format and Versions — https://iceberg.apache.org/spec/#format-versioning
2. Iceberg and AWS — https://aws.amazon.com/what-is/apache-iceberg/
3. Apache Iceberg with Athena — https://docs.aws.amazon.com/athena/latest/ug/querying-iceberg.html
4. AWS Glue with Iceberg — https://docs.aws.amazon.com/glue/latest/dg/aws-glue-programming-etl-format-iceberg.html
5. Creating Iceberg using Athena — https://docs.aws.amazon.com/athena/latest/ug/querying-iceberg-creating-tables.html

INTRODUCTION

AWS Glue Data Quality is a feature using which you can create a Data Quality rulesets across datasets, this is something that we have already discussed in our previous blog which you can read here (if you haven’t read it, I would urge you to go through it)

AWS Glue Studio is a graphical interface that makes it easy to create, run, and monitor data integration jobs in AWS Glue. You can visually compose data transformation workflows and seamlessly run them on the Apache Spark–based serverless ETL engine in AWS Glue.

What if, we could merge the power of both the above features in glue and use a Visual interface to create a Glue Job which will use the Glue Data Quality ruleset to evaluate the Quality of the dataset within a Glue ETL Job. Doesn’t it sounds INTERESTING! :)

So lets do a quick hands-on on the same

Let’s Create a Visual ETL Flow

We need to navigate to the AWS Glue Console and then select ETL jobs from the left hand side panel. It will open up all the existing ETL jobs in the account and there you will see the option to create a Visual ETL

Once you click on the Visual ETL it will open a Glue Visual Studio Canvas, which is were we will be creating our Glue Job, but before that lets add some Job details by clicking on the tab

Name — Data-Quality-Visual-ETL-Job

IAM Role — Select an existing IAM role which will have access to AWS S3 and AWS Glue

Keep the remaining configurations default and lets just click on Save button at top right corner

And lets start to create a Visual ETL flow by again going back to the Visual tab on the top

Let’s Design the ETL Flow with Data Quality Rules

Firstly, lets configure the source for the data, for that click on the AWS Glue Data Catalog option, which will automatically add a node on the canvas. If you click on the node which got added it will ask you to configure the node, so lets do that.

Name — Source: Glue Movie Table

Database — Select the database of the source table

Table — Select the table from the above database

Now, as we have source configured properly, lets add the Data Quality Ruleset as an intermediate processing node to the flow. For that, you need to again click on the + (plus) icon at the top left corner in the canvas and open the add node panel.

Once you get Add Node panel, then you need select the transform tab. In the transform tab — you can add any type of transformations that is required for you ETL job, for our case we are going to select the Evaluate Data Quality transform.

Again we need to configure the new node that we added to the canvas, so for that we need to add the following details

Name — Data Quality Ruleset

In Ruleset Editor, we need to add Data Quality rules that we want to evaluate for the source table. For that you can add the rules from the Helper module and then change it as per your need. For our example, I am going to add the DQDL statement which is shown below

Rules = [
    ColumnDataType "imdb_id" = "String",
    ColumnExists "homepage",
    IsComplete "imdb_id",
    ColumnValues "source_id" matches "[1-9]*" with threshold > 0.1,
    ColumnValues "vote_count" >= 50
]

If, you want to get more understanding about the DQDL language, then do refer the previous blog where I have explained the entire AWS Glue Data Quality feature in detail (link)

Note: This is the place where we need to save our progress, otherwise we may loose all the configurations which we have set, if we do not do that. For saving, just click on Save button on the top right corner.

Identify the Records which have Passed and Failed the rules defined

Now to identify and flag the records which do not satisfy our rules and also identify overall status of each Data Quality rules and percentages, we will have to add two more nodes to our workflow.

So for that, we need to just click on the existing Data Quality Ruleset node and then in the configuration panel, scroll down to the bottom, where you see some more options

From multiple options, you need to select the following option under Data quality transform output —

• Enable Original data and then also enable the option of Add new columns to indicate data quality errors
• Enable data quality result option as well

Please refer to the following image, and you workflow should like the one shown in the following image

Now, lets save both the result outputs to a S3 location by adding two more nodes to the workflow which will be our target locations. For that you need to again open the Add Node Panel and then select the Targets tab.

In the Targets tab, we need to select Amazon S3 as a node. You need to click on the S3 node twice to add two nodes each for the outputs.

Once you get the S3 nodes, just click on those nodes and then configure the 1st S3 node as follows

Node Parents —Select the ruleOutcomes as Node Parents

S3 target location — Browse the S3 bucket location, where you would want to store the Data Quality Rule Outcome outputs

For the second S3 node, just click on the other S3 node and then configure it as follows

Node Parents — Select the rowLevelOutcomes as Node Parents

S3 target location — Browse the S3 bucket location, where you would want to store the Row Level DQ Rule Outcome outputs

Note: This is the place where we need to save our progress, otherwise we may loose all the configurations which we have set, if we do not do that. For saving, just click on Save button on the top right corner.

Finally, you Visual ETL workflow should look like the one shown the image below

Now Lets run the Job by clicking on the Run button on the top right corner. Once you run the job, you can see all the history of the previous runs in the Runs tab on the console.

Lets query the output files and have a look at the difference in the outputs that we have received

Once the Job is successfully executed, then you need to just navigate to the specific S3 locations and see the output files. Once you can see the parquet files in both the S3 locations, then we just need to query the data from these two locations

For that you can create a AWS Glue Crawler and then run the Crawlers on these two locations to create Tables in Amazon Athena

I have already created a crawler and ran it over both the s3 locations and created Athena tables. Lets go to the Athena console and see both the tables which are created

For the RuleOutcomes data — You can see all the rules that we have defined in our DQDL rulesets within Glue job and it provides which rules Passed and which one Failed. For the Failed rules, it also provide details on why the rules failed

For the RowLevelOutputs data — You will see all the records from the original dataset, along with some additional columns which are added by glue to identify the which rules got failed for which reason.

CONCLUSION

AWS Glue Studio with the Visual Studio has provided and interesting to create ETL and Spark Jobs. Glue studio helps the data analysts and business users to create their own basic level of data aggregation and data preparation. Combining AWS Glue studio with the AWS Glue Data Quality rulesets to create a pipeline that will evaluate the Data Quality of the data and can flag those records which failed the data quality rules.

This blog just gives you starter and introduces you to this interesting feature so that you can use your imaginations and create complex pipelines and integrate it within your ETL workflows

REFERENCES

1. AWS Glue Visual Studio — https://docs.aws.amazon.com/glue/latest/dg/edit-nodes-chapter.html
2. Visual ETL Transformations lists — https://docs.aws.amazon.com/glue/latest/dg/edit-jobs-transforms.html
3. Source and Target for ETL Visual Studio — https://docs.aws.amazon.com/glue/latest/dg/edit-job-add-job-parameters.html
4. Data Quality with Glue Studio — https://docs.aws.amazon.com/glue/latest/dg/tutorial-data-quality.html
5. Data Quality Rule Builder (Glue Studio) — https://docs.aws.amazon.com/glue/latest/dg/data-quality-rule-builder.html
6. Data Quality DQDL Format — https://docs.aws.amazon.com/glue/latest/dg/dqdl.html

INTRODUCTION

AWS Glue Data Quality is a feature of the AWS Glue service which allows you to measure and monitor the quality of your data so that you can make good business decisions. AWS Glue Data Quality is a complete serverless service and does not require provisioning of any server or compute.

The AWS Glue Data Quality evaluates objects that are stored in the AWS Glue Data Catalog. It offers non-coders an easy way to set up data quality rules.

When to use AWS Glue Data Quality?

• If you want to perform data quality tasks on data sets that you’ve already cataloged in the AWS Glue Data Catalog.
• When you are not sure that the quality and correctness of the raw data that you want to ingest
• You work on data governance and need to identify or evaluate data quality issues in your data lake on an ongoing basis.
• When you have specific business rules which you want to evaluate against your dataset

Some important features of AWS Glue Data Quality

• Serverless — Complete serverless and does not require installation, patching or maintenance.
• ML Detection of data quality issues — Use machine learning (ML) to detect anomalies and hard-to-detect data quality issues.
• Get Data Quality Score and to business decisions — Once you evaluate the rules, you get a Data Quality score that provides an overview of the health of your data. Use Data Quality score to make confident business decisions.
• Pay as you go — There are no annual licenses you need to use AWS Glue Data Quality.
• Data quality checks — AWS Glue Data Quality You can enforce data quality checks on Data Catalog and AWS Glue ETL pipelines allowing you to manage data quality at rest and in transit.
• ML-based data quality detection — Use machine learning (ML) to detect anomalies and hard-to-detect data quality issues.
• ML based Recommendations — Get automatic data quality recommendations considering dataset schemas and data types

To get started with defining the Data Quality rulesets, its important to understand the DQDL language which is used to write the rules. Lets get basic understanding the DQDL (Data Quality Definition Language)

What is DQDL in AWS Glue Data Quality?

Data Quality Definition Language (DQDL) is a domain specific language that you use to define rules for AWS Glue Data Quality.

A DQDL document is case sensitive and contains a ruleset, which groups individual data quality rules together. To construct a ruleset, you must create a list named Rules (capitalized), delimited by a pair of square brackets. The list should contain one or more comma-separated DQDL rules like the following example.

Rules = [
   IsComplete "order-id",
   IsUnique "order-id"
]

The above example defined a Data Quality ruleset which has two rules which checks the Completeness and Uniqueness of the order-id column in the dataset.

To get more in depth understanding of the entire DQDL, please refer to the AWS documentation page here

Let’s get our hand’s dirty with the AWS Glue Data Quality console

To get started with the Data Quality rulesets, it is necessary to have a table defined in the AWS Glue Data Catalog. For our example, I already have a table in Glue Data Catalog which has the movie dataset data file on S3

1. Create a Data Quality Ruleset:

To create the Data Quality Ruleset, lets navigate to the Glue Data Catalog Console and select the table on which we want to define the Data Ruleset. When you select the table, then you can see the option of Data Quality at the top as shown below.

At the bottom, of the page, you can see the Create data quality rules button, using which we need to create the data quality rules.

It will redirect you to the Data Quality Ruleset Canvas, where you can create your own rulesets as shown in the diagram below.

Now, rather than creating the Data Quality ruleset on your own, lets try to see the Recommend Data Quality ruleset feature of Glue

2. Get Recommendations of the DQ Ruleset:

Once you click on the recommend rules at the top right corner, then you need to select the IAM role which you need to associate the DQ ruleset with. Other options, you can keep default for now and the click on Recommend rules

Once you click on Recommend rules, you can see the recommendation process running in the backend as shown in the image below

After, the recommend rules process is completed, you see an option to insert rule recommendations

Once you click the insert rule recommendations button, you can see the list of all the records and then you can select the rules which you want to include and then it will automatically populate the rules in the canvas as shown bellow in the diagram

Once you are done with selecting the rules, you can add the rules to the Data Quality ruleset and then click on the Save Ruleset button to save the rules. You need to provide the Ruleset Names — DQ-ruleset-demo while saving ruleset

You can even go-ahead and update and edit the ruleset obtained as per your requirements and business rules. Further ahead, you can add even more customized rules from the left side panel. To get details on pre-defined rulesets of Glue, you can refer the AWS documentations here

3. Run DQ Ruleset

Let’s now Run the Data Quality ruleset by clicking on Run button and select the IAM role and click on Run. It will take some time depending on the volume of the dataset to complete the Data Quality ruleset.

Once the DQ ruleset is executed successfully, you can click on the DQ ruleset execution run to get the outputs and details

If the see the output results, it shows following things about the quality of the data

• Data Quality Score — Its the number of rules that got passed after evaluating them across the dataset. In our example it shows 8/8 (which means 8 DQ rules got passed out of 8 rules i.e. 100% is the DQ Score)
• Data Quality Status — It shows the overall Status of the Data Quality. If anyone of the rule gets failed status after evaluating it across the dataset then the overall Status is shown as Failed. In our example as all the rules are passed thus it shows DQ Passed
• Data Quality Results — It describes which of the rule is actually passed and which one got failed. For the Failed rules it also provides the information — like because of which value in the dataset the rule Failed

Also have a look at the below screenshot, which provides an example where some of the Data Quality rules are not passed as per the datasets.

CONCLUSION

AWS Glue Data Quality provides you with an easier option to define business rules and data quality standards which are required for the incoming datasets and accordingly also provides a way to evaluate those rules across the dataset. The interesting part of the AWS Glue Data Quality module is that the defining of the DQ rulesets, does not require any Data Engineering technical knowledge and can be easily done by personas like Data Analysts and Business Analysts using the DQDL language.

REFERENCE

1. Introduction to AWS Glue Data Quality — https://aws.amazon.com/glue/features/data-quality/
2. AWS Glue Data Quality Feature — https://docs.aws.amazon.com/glue/latest/dg/glue-data-quality.html
3. Data Quality Definition Language — https://docs.aws.amazon.com/glue/latest/dg/dqdl.html
4. Anomaly detection in AWS Glue Data Quality — https://docs.aws.amazon.com/glue/latest/dg/data-quality-anomaly-detection.html
5. IAM Permissions for AWS Glue Data Quality — https://docs.aws.amazon.com/glue/latest/dg/data-quality-authorization.html

Gen AI, short for “general artificial intelligence,” is one of the most groundbreaking advancements in the field of technology. Unlike narrow AI systems that are designed for specific tasks, Gen AI possesses the ability to understand and perform a wide range of intellectual tasks that previously were exclusive to human intelligence.

Can we think, how this advancement can actually help developers from performing mundane tasks regularly which can be easily automated. Lets take a scenario, where a ETL developer who is working on developing Spark code has write similar piece of code to read data from a source, perform joins and write the output to a target database or destination.

This is where Amazon has enabled a feature within AWS Glue which can assist the developers to improve the speed of writing spark scripts, or generic functions by integrating the power of Amazon CodeWhisperer with AWS Glue Studio. Lets try out this feature with a help of an example.

STEP 1: Create an IAM role for the Glue Spark Notebook

To create an IAM role, open up the IAM console and provide the following details to create a new role

• Select Trusted Entity page— Select Glue service from the drop down provided
• Add Permission page — Create a new IAM policy and copy paste the following JSON document in the IAM policy

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "ec2:DeleteTags",
                "ec2:CreateTags"
            ],
            "Resource": [
                "arn:aws:ec2:*:*:instance/*",
                "arn:aws:ec2:*:*:security-group/*",
                "arn:aws:ec2:*:*:network-interface/*"
            ],
            "Condition": {
                "ForAllValues:StringEquals": {
                    "aws:TagKeys": "aws-glue-service-resource"
                }
            }
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": [
                "glue:BatchCreatePartition",
                "glue:GetConnections",
                "glue:DeleteDatabase",
                "glue:GetTableVersions",
                "glue:GetPartitions",
                "glue:BatchDeletePartition",
                "glue:UpdateTable",
                "glue:DeleteTable",
                "glue:CreateUserDefinedFunction",
                "s3:ListBucket",
                "glue:DeleteJob",
                "glue:*",
                "glue:GetDevEndpoint",
                "codewhisperer:GenerateRecommendations",
                "glue:CreateJob",
                "iam:PassRole",
                "glue:GetConnection",
                "glue:GetUserDefinedFunction",
                "glue:ResetJobBookmark",
                "glue:UpdateJob",
                "glue:CreatePartition",
                "glue:GetJobBookmark",
                "glue:GetUserDefinedFunctions",
                "glue:UpdatePartition",
                "glue:UpdateDatabase",
                "glue:CreateTable",
                "glue:GetJobs",
                "glue:UpdateUserDefinedFunction",
                "glue:GetTables",
                "glue:BatchGetPartition",
                "s3:GetBucketAcl",
                "glue:GetDatabases",
                "glue:DeleteUserDefinedFunction",
                "glue:CreateConnection",
                "glue:GetTable",
                "glue:GetDatabase",
                "glue:GetPartition",
                "glue:DeleteConnection",
                "glue:GetDevEndpoints",
                "glue:UpdateConnection",
                "s3:ListAllMyBuckets",
                "glue:CreateDatabase",
                "glue:BatchDeleteConnection",
                "glue:UpdateDevEndpoint",
                "glue:BatchDeleteTable",
                "s3:GetBucketLocation",
                "glue:DeletePartition",
                "glue:GetJob"
            ],
            "Resource": "*"
        },
        {
            "Sid": "VisualEditor2",
            "Effect": "Allow",
            "Action": "s3:GetObject",
            "Resource": [
                "arn:aws:s3:::crawler-public*",
                "arn:aws:s3:::aws-glue*"
            ]
        },
        {
            "Sid": "VisualEditor3",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:DeleteObject"
            ],
            "Resource": "arn:aws:s3:::aws-glue*"
        }
    ]
}

• Name, Review & Create page — Provide a name to the IAM role Glue-CodeWhisperer-GenerateRecommendations-Role. Then click on create role

This will set you up with the IAM permissions that are needed by the Glue Spark notebook to pull in recommendations from the Code Whisperer

STEP 2: Create a Glue Spark Notebook in the Glue Studio

For this, you need to open up the Glue Studio console and then click on the ETL jobs and then select the Jupyter Notebook and then click on Create. This will open up a form and you will have to fill in the following details

• Job Name — Enter the name of the job as SparkCodeWhispererJob
• IAM Role — From the dropdown select the option Glue-CodeWhisperer-GenerateRecommendations-Role (The IAM role which we created in the previous step)
• Kernel — From the dropdown select the Spark option

Once all the inputs are given then click on the Start Notebook button in the bottom right corner. This will start you Jupyter Spark Notebook on Glue Studio

To check if the IAM roles are properly setup — you can check that in the notebook bottom section you can actually see the CodeWhisperer option which is highlighted in Yellow in the above image as well.

STEP 3: Let’s try out the Magic of Code Whisperer Recommendations while writing the Spark code

1. Lets try and read a CSV file kept in a S3 location into a Spark dataframe using the CodeWhisperer recommendations

For that you need to use # (hash) before any statement, so that you can get the recommendation from the CodeWhisperer. For example,

#Read CSV file data from S3 path in a Spark dataframe with header and inferschema

Once you write the above statement into the Jupyter notebook cell and press ENTER, then it will respond back with the piece of code

If you check the above screenshot — the line below the hash (#) is automatically flashed on the screen by the CodeWhisperer and to accept it you need press TAB button.

Do not believe it! I would suggest you to try it out by yourself :)

2. Now lets try out something COMPLEX

For this, I am going to write down multiple statements to get a piece of code

#register the above spark dataframe into a table

#write a spark sql query to get sum of total_mks column from the students table into a sum_df dataframe

#print the sum_df

If you check the above screenshot, you can see how easy it is to get the code written if you know what actions and transformations needs to be performed

3. Let’s try out recommendation with some Spark specific functions

For this, I am going to write down multiple statements to get a piece of code

#repartion the sum_df dataframe with 5 partitions

#combine the data in sum_df within 1 partition

#write output back the data into S3 location

The above screenshot shows that CodeWhisperer can understand the Spark functions and provides a syntactical code as a response back

4. We can check if its able to create functions and pass arguments with parameters

For this, I am going to write down following statements to get a piece of code

#show the top 5 students having highest total_mks from the df dataframe by defining a function

#pass the df to the above function

If you check the above screenshots, it remembers the previous dataframes and what arguments are to be passed to the function and accordingly it prompts the code

CONCLUSION

The integration of Amazon CodeWhisperer with Amazon Glue Studio provides an efficient way to write a code and accordingly can be a great tool to structure the code. It is able to provide the Spark based function recommendations along with their appropriate syntax as well. Although its just a start and further it can help the developers across programming languages.

This blog, majorly focusses on PySpark based code and its recommendations, but this is just for experiment purpose. I hope that this would give you a good understanding on how to configure this and start using it

REFERENCES

1. AWS CodeWhisperer — https://docs.aws.amazon.com/codewhisperer/latest/userguide/what-is-cwspr.html
2. AWS Glue studio — https://docs.aws.amazon.com/glue/latest/ug/what-is-glue-studio.html
3. Glue Studio with CodeWhisperer integration — https://aws.amazon.com/about-aws/whats-new/2023/07/aws-glue-studio-amazon-codewhisperer/
4. AWS Glue studio Jupyter Notebook — https://docs.aws.amazon.com/glue/latest/ug/notebook-getting-started.htm
5. PySpark Code Examples — https://sparkbyexamples.com/pyspark-tutorial/

Gen AI powered by Amazon Code Whisperer within AWS Glue Studio Notebooks was originally published in AWS Tip on Medium, where people are continuing the conversation by highlighting and responding to this story.