![\"AWS](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image8.png\")
<\/figure><\/div>\n\n\nSome of the main advantages are: <\/p>\n\n\n\n
- \n
- being able to query with PartiQL language, databases or datastream of different nature, becoming a defacto aggregator without the need to write custom complex ETL jobs.<\/li>\n\n\n\n
- Using powerful commands like JOIN to add aggregation capabilities to data sources that usually don\u2019t have.<\/li>\n<\/ul>\n\n\n\n
The purpose of this article is to guide the reader in exploring some of the key factors that make this service something to consider while drafting your Machine Learning projects.<\/p>\n\n\n\n
We\u2019ll dive deep into what AWS Elastic Views is capable of, considering that it is still in the beta private preview phase, so you\u2019ll have to ask AWS access for the preview.<\/p>\n\n\n\n
Let\u2019s go! <\/p>\n\n\n\n
How it works<\/h2>\n\n\n\n
Let\u2019s start our journey by understanding what is AWS Glue Elastic Views<\/strong>, and how it works. At first, let\u2019s take a look at this scheme by AWS:<\/p>\n\n\n
\n![\"AWS](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image13-1024x424.png\")
Courtesy of AWS – AWS Glue Elastic Views inputs and outputs<\/em><\/figcaption><\/figure><\/div>\n\n\n As shown in the image the focal point of this service is represented by the Materialized View<\/strong>, which is a way to abstract the dataset being it from any kind of data source: i.e. Amazon Aurora, RDS, or DynamoDB. This allows keeping things in synch without the actual use of a Glue Crawler as we would expect from our other articles about ETL workloads: for example this one<\/a>, or this one<\/a>.<\/p>\n\n\n\n
But let\u2019s take a look in detail at the main features and what they are useful for.<\/p>\n\n\n\n
Use SQL as a mean to create a view<\/h4>\n\n\n\n
AWS Glue Elastic Views enables a developer to create materialized views across different data sources using SQL queries to aggregate the data. AWS Glue Elastic Views currently supports Amazon DynamoDB, Redshift, S3, and Elasticsearch Service. Also, AWS has plans to add even more data sources in the future.<\/p>\n\n\n\n
Automatically copies data from the source data store to a target<\/h4>\n\n\n\n
AWS Glue Elastic Views manages the heavy lifting of copying and aggregating data from all the data sources to the target data stores, without having to write custom code or use complex or unfamiliar ETL tools and programming languages, having a beneficial effect on both time and efficiency of the project. AWS Glue Elastic Views reduces the time it takes to combine and replicate data across data stores from months to minutes according to AWS.<\/p>\n\n\n\n
Keeps target\u2019s data always up-to-date automatically<\/h4>\n\n\n\n
Keeping data in sync usually requires Crawlers and jobs to be created and maintained, AWS Glue Elastic Views, instead, continuously monitors for changes in data in the starting data stores, and when a change occurs, Elastic Views automatically updates the targets. This ensures that applications that access data using Elastic Views always have the most up-to-date data.<\/p>\n\n\n\n
Alerts you if there is a change to the data model in a source data store<\/h4>\n\n\n\n
AWS Glue Elastic Views proactively alerts developers when there is a change to the data model in one of the source data stores so that they can update their views to adapt to this change.<\/p>\n\n\n\n
Serverless<\/h4>\n\n\n\n
AWS Glue Elastic Views is serverless and scales capacity up or down automatically to accommodate workloads lifecycles. There is no hardware or software to manage, and as always, a user pays only for the resources it utilizes.<\/p>\n\n\n\n
Register for the free preview<\/h2>\n\n\n\n
Being a service still in beta to try it it is necessary to register for the free preview: to do so, please go to this URL<\/a>, and sign up, by compiling the form.<\/p>\n\n\n
\n![\"AWS](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image5-1-1024x299.png\")
Overview of the preview form<\/em><\/figcaption><\/figure><\/div>\n\n\n You\u2019ll be asked for personal and company details, as well as a basic introduction to the business problem you want to solve using AWS Glue Elastic Views. Please be sure to give reasonable motivations, as interesting use cases increase the chances of being selected for the preview. <\/p>\n\n\n\n
Usually, AWS responds in a week, and if you are eligible for the preview the following message will be sent to your email.<\/p>\n\n\n
\n![\"AWS](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image3-1-1024x185.png\")
The preview email<\/em><\/figcaption><\/figure><\/div>\n\n\n After you have been registered, you\u2019ll need to click on the AWS Glue Elastic View link in the email to access the service.<\/p>\n\n\n\n
Now you\u2019re ready to start your first ETL job with it, instead of using standard Spark scripts, or Glue Crawlers.<\/p>\n\n\n\n
Our test case<\/h2>\n\n\n\n
The best way to understand the possibilities of this service is to try our hands on it. So we decided to create a simple use case to present how it could be used to enhance your ETL workloads.<\/p>\n\n\n
\n![\"AWS](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image11-1024x394.png\")
Schema of our simplified test case<\/em><\/figcaption><\/figure><\/div>\n\n\n Overview<\/h3>\n\n\n\n
The basic idea is to populate a DynamoDB table with some test data obtained from free data sources. We want to extract and manipulate data from this table to demonstrate how Glue Elastic Views can effectively add ETL capabilities on top of DynamoDB, which is historically a little weak on this side. <\/p>\n\n\n\n
Then we want to send this data to S3 and verify that it is possible to update it in real-time to reflect changes, demonstrating how it can effectively speed up the investigation process of a Machine Learning pipeline.<\/p>\n\n\n\n
Note: at the time of writing, unfortunately, JOIN operation is not supported for PartiQL in AWS Glue Elastic Views, so we opted for testing some mathematical and logical operations, as well as validating all the steps to synchronize the DynamoDB table with the S3 bucket.<\/em><\/p>\n\n\n\n
Dataset<\/h3>\n\n\n\n
Import data in DynamoDB<\/h3>\n\n\n\n
We want to create the table for DynamoDB, to do that we\u2019ve defined a simple script, in SageMaker Studio, to do the importing for us.<\/p>\n\n\n\n
![\"a](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image9-1024x127.png\")
The SageMaker Studio interface<\/em><\/figcaption><\/figure>\n\n\n\n We basically read data from the CSV file using Pandas, we convert the rows to JSON, but before doing that we also add a \u201chash\u201d column named id<\/strong>, because DynamoDB needs a primary key for each entry.<\/p>\n\n\n\n
for record in json_list:\n if record['longitude '] and record['latitude']:\n record['id'] = sha256(str(record).encode()).hexdigest()<\/code><\/pre>\n\n\n\nThe UFO dataset also had some problems that needed to be addressed: the \u201clongitude\u201d header had whitespace to be removed, and the latitude and longitude column needed to be converted in string removing NaN entries.<\/p>\n\n\n\n
Finally, we used boto3 to create a corresponding table for the CSV.<\/p>\n\n\n\n
\n\ndynamodb = boto3.resource('dynamodb')\ntable = dynamodb.Table('article_ufo_sightings')\nwith table.batch_writer() as batch:\n...\nbatch.put_item(Item=record)<\/code><\/pre>\n<\/div>\n<\/div>\n\n\n\nThe complete code can be found here<\/a>.<\/p>\n\n\n\n
Note: we could have also used AWS Glue for this task, by putting the CSV file in a source S3 bucket using Glue Crawler to import data, but as we already covered ETL jobs with this service in other <\/em>articles<\/em><\/a>, we opted for a simpler solution as this is not the focus of the example.<\/em><\/p>\n\n\n\n
Create DynamoDB table<\/h3>\n\n\n\n
We simply went to the DynamoDB console, clicked on \u201ccreate table\u201d and used these simple settings, just remember: apply on-demand<\/strong> capacity mode to speed up generating the table.<\/p>\n\n\n
\n![\"DynamoDB](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image10-1024x413.png\")
Table settings – used id as primary key and applied on-demand capacity mode<\/em><\/figcaption><\/figure><\/div>\n\n\n We add id <\/strong>as a primary key for UFO sightings. <\/p>\n\n\n\n
Creating Materialized View from table<\/h3>\n\n\n\n
Before generating the view, we needed to add the DynamoDB table as a source in AWS Glue Elastic Views; to do so, we went to the main console, selected \u201cTables\u201d on the left, and clicked on \u201ccreate table\u201d. Then we just selected the new DynamoDB table from previous passages.<\/p>\n\n\n
\n![\"Source](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image1-1-1024x180.png\")
Source table for the Materialized View<\/em><\/figcaption><\/figure><\/div>\n\n\n The next step was to apply some filtering in order to create our final target dataset, depending on the view. <\/p>\n\n\n\n
By going on the \u201cViews\u201d tab on the left side of the console we created a new one. Here we were presented with the ability to write custom PartiQL code: exactly what we wanted!<\/p>\n\n\n\n
We added the following code in the editor to enable our Materialized View:<\/p>\n\n\n\n
SELECT id, Latitude, Longitude FROM article_ufo_sightings.article_ufo_sightings<\/code><\/pre>\n\n\n\nWe also had to write all the attributes we wanted to export in the target parquet files (it seems that AWS Glue Elastic Views generates batches of parquet files in the output directory).<\/p>\n\n\n\n
As the reader can see from the code above, we avoided requesting useful info on purpose: we want to show that it is possible to alter the view in real-time after the materialized view is created.<\/p>\n\n\n\n
Send data to S3<\/h3>\n\n\n\n
At the time of writing, S3 is one of the three viable options for target along with ElasticSearch and Redshift, in our case S3 is the ideal target as we want the final dataset to be consumed by SageMaker. <\/p>\n\n\n\n
We went to the View and clicked on \u201cMaterialized View\u201d, then we selected \u201cGlue EV\u201d as the support to unlock \u201cS3\u201d as a target: there we add the \u201carticle-ufo-materialized-views\u201d bucket, we selected default encryption and we added a suitable IAM role for the execution. <\/p>\n\n\n\n
The role can be created using the AWS Role & Policy editor<\/strong>, then when the role is created, be aware to change the trust relationship<\/strong> with the code below to enable the IAM role, otherwise, you will not be able to see it in the selector:<\/p>\n\n\n\n
\"Version\": \"2012-10-17\",\n \"Statement\": [\n {\n \"Effect\": \"Allow\",\n \"Principal\": {\n \"Service\": [\n \"materializedviews.amazonaws.com\"\n ]\n },\n \"Action\": \"sts:AssumeRole\"\n }\n ]\n}<\/pre>\n\n\n\nHere is a screenshot with the settings we used:<\/p>\n\n\n
\n![\"Materialized](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image4-1-1024x907.png\")
Materialized View – S3 target settings<\/em><\/figcaption><\/figure><\/div>\n\n\n When the view is created, it must be activated in order to synchronize with the S3 bucket, to do so, we went to both the table and the view we had created and clicked on \u201cActivate\u201d in their detail panel.<\/p>\n\n\n\n
After a couple of minutes after activation, the S3 bucket was filled with the resulting data!<\/p>\n\n\n
\n![\"ufo](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image12-1024x463.png\")
Resulting data<\/em><\/figcaption><\/figure><\/div>\n\n\n Interesting fact: just before activating the view, the service warned us that some fields were not compatible with the output target, and gave us the ability to modify the view on the fly with an inline editor, this is what we did to cast \u201cLatitude\u201d and \u201cLongitude\u201d fields to integer:<\/p>\n\n\n
\n![\"Cast](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image2-1-1024x118.png\")
<\/figure><\/div>\n\n\nThe data is now linked directly with our S3 bucket, so any change made to the table is reflected directly after some seconds. Basically is like having a Glue Crawler that works on-demand, going on and off when needed and without human intervention.<\/p>\n\n\n\n
Alter the data on S3<\/h3>\n\n\n\n
We wanted to show that is possible to alter the data obtained from the DynamoDB table at any time, because of that, we started by saving \u201cincomplete parquet files\u201d as the reader can see here:<\/p>\n\n\n\n
![\"Incomplete](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image7.png\")
Incomplete dataset with only id, Latitude, and Longitude<\/em><\/figcaption><\/figure>\n\n\n\n To modify our final dataset we first needed to \u201cdeactivate\u201d the Materialized View. After that, it was possible to define a new Materialized view on the same target adding more columns. Please note also that, if you have more than one view depending on each other, you must deactivate and delete them in the correct order. Perhaps this operation will become less cumbersome by the time of the official release. <\/p>\n\n\n\n
We altered the original Materialized View definition adding more columns:<\/p>\n\n\n\n
SELECT id,cast(City as string),cast(State as string),cast(Shape as string),cast(Latitude as integer),cast(Longitude as integer) FROM article_ufo_sightings.article_ufo_sightings;<\/code><\/pre>\n\n\n\nNonetheless, these operations took less than 5 minutes and the new data was soon ready in the correct S3 bucket:<\/p>\n\n\n\n
![\"Added](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image6.png\")
Added columns<\/em><\/figcaption><\/figure>\n\n\n\n Of course if the changes are on the original table and not on the view, updates are completely seamless as you would expect.<\/p>\n\n\n\n
Another reminder: being the interface still in the preview phase, we recommend avoiding applying to many operations in a short time, as we experienced many bugs related to race conditions still not being managed correctly.<\/p>\n\n\n\n
Make some Machine Learning out of it!<\/h3>\n\n\n\n
To test that the data source is viable for effective Machine Learning Jobs we\u2019ve also prepared a simple SageMaker test notebook, to apply some correlation analysis on the created data lake. This can be seen in full detail in this notebook<\/a>. The idea was to verify if there is some sort of correlation between locations, cities, and UFO sightings, and based on the example data, try to do some simple inferences. More info on how to use SageMaker to do inference was covered in this story<\/a>.<\/p>\n\n\n\n
References<\/h2>\n\n\n\n
- \n
- https:\/\/pages.awscloud.com\/AWS-Glue-Elastic-Views-Preview.html<\/a><\/li>\n\n\n\n
- https:\/\/aws.amazon.com\/glue\/features\/elastic-views\/<\/a><\/li>\n\n\n\n
- https:\/\/aws.amazon.com\/glue\/faqs\/<\/a><\/li>\n\n\n\n
- https:\/\/cloudacademy.com\/course\/aws-reinvent-2020-aws-glue-elastic-views-1209\/aws-glue-elastic-views\/<\/a><\/li>\n<\/ul>\n\n\n\n
Final Thoughts<\/h2>\n\n\n\n
We have reached the end of this journey into the wonders of AWS Elastic Views, so it\u2019s time to sum up what we have learned so far.<\/p>\n\n\n\n
This AWS Service proves itself to be invaluable when dealing with many different data sources, especially if of different nature, as it recovers and queries all the data with a SQL compatible language (PartiQL) avoiding the creation of many complex ETL Glue jobs.<\/p>\n\n\n\n
It\u2019s perfect in all those situations where you need to mix legacy and fresh data<\/strong>, as they usually reside, as per best practices, on different data sources: those cheaper for infrequent access and those with low latency for fresh data.<\/p>\n\n\n\n
If you decide to use S3 as a target, it becomes a suitable solution for SageMaker Jobs<\/strong> or even managed tasks exploiting AWS Managed Machine Learning services.<\/p>\n\n\n\n
If ElasticSearch is the designated target, Elastic Views becomes perfect for Business Intelligence workloads.<\/p>\n\n\n\n
AWS Elastic Views support on-the-fly updates on data, with the ability to update even a single piece of data to reflect changes; all of this using simple, universally known, SQL language, bringing SQL capabilities for databases that don\u2019t support it.<\/p>\n\n\n\n
Being able to update a single field, avoid crawling again all the data in a data source to update the chosen target.<\/p>\n\n\n\n
Finally, we would like to give advice though: as the current preview is still in a very early stage, most of the features described are not yet available to try, so even if the product is already useful in some cases, experiment before using it for production jobs, or wait for the public release.<\/p>\n\n\n\n
So this is it! We hope you enjoyed the reading and gained useful insights. As always, feel free to comment in the section below, and reach us<\/a> for any doubt, question or idea!<\/p>\n\n\n\n