Usually, this process involves different steps, coordinating their resolution, accessing different databases with different technologies, preparing many scripts, knowing different languages to query the relevant data, and so on.<\/p>\n\n\n\n
Taking care of all these steps is a daring task and requires a lot of expertise, and of course, is time-consuming, undercutting the efficiency of the entire project at hand.<\/p>\n\n\n\n
In the last couple of years AWS has been aggressively developing tools and services to help in Machine Learning and ETL tasks and at the last re:Invent<\/strong> introduced another important component for ETL-ML preparation: AWS Elastic Views<\/strong>. <\/p>\n\n\n\n AWS Elastic Views allows a user to request data from different data sources being completely agnostic on their nature, to query for data in a SQL-compatible language, and to send all the queried data to a target, typically S3 or another data store in order to aggregate the heterogenous data in a data lake.<\/strong><\/p>\n\n\n Some of the main advantages are: <\/p>\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 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 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 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 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 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 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 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 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 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 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 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 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 For our example, we decided to use a simple dataset about UFO sightings<\/a>. We want to put this CSV file in a DynamoDB table, as stated before, and apply some filtering operations on latitude and longitude, or even city fields. The result will be used to answer the following example question: \u201cHow UFO sightings vary depending on different locations?\u201d. 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 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 The 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 The 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 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 We add id <\/strong>as a primary key for UFO sightings. <\/p>\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 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 We 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 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 Here is a screenshot with the settings we used:<\/p>\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 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 The 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 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 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 Nonetheless, these operations took less than 5 minutes and the new data was soon ready in the correct S3 bucket:<\/p>\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 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 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![\"AWS](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image8.png\")
<\/figure><\/div>\n\n\n\n
How it works<\/h2>\n\n\n\n
![\"AWS](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image13-1024x424.png\")
Use SQL as a mean to create a view<\/h4>\n\n\n\n
Automatically copies data from the source data store to a target<\/h4>\n\n\n\n
Keeps target\u2019s data always up-to-date automatically<\/h4>\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
Serverless<\/h4>\n\n\n\n
Register for the free preview<\/h2>\n\n\n\n
![\"AWS](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image5-1-1024x299.png\")
![\"AWS](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image3-1-1024x185.png\")
Our test case<\/h2>\n\n\n\n
![\"AWS](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image11-1024x394.png\")
Overview<\/h3>\n\n\n\n
Dataset<\/h3>\n\n\n\n
This is, of course, just a simple example, it has no practical meanings apart from demonstrating AWS Glue Elastic Views capabilities.<\/p>\n\n\n\nImport data in DynamoDB<\/h3>\n\n\n\n
![\"a](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image9-1024x127.png\")
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\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\nCreate DynamoDB table<\/h3>\n\n\n\n
![\"DynamoDB](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image10-1024x413.png\")
Creating Materialized View from table<\/h3>\n\n\n\n
![\"Source](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image1-1-1024x180.png\")
SELECT id, Latitude, Longitude FROM article_ufo_sightings.article_ufo_sightings<\/code><\/pre>\n\n\n\nSend data to S3<\/h3>\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\n![\"Materialized](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image4-1-1024x907.png\")
![\"ufo](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image12-1024x463.png\")
![\"Cast](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image2-1-1024x118.png\")
<\/figure><\/div>\n\n\nAlter the data on S3<\/h3>\n\n\n\n
![\"Incomplete](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image7.png\")
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\n![\"Added](\"https:\/\/blog.besharp.it\/wp-content\/uploads\/2021\/04\/image6.png\")
Make some Machine Learning out of it!<\/h3>\n\n\n\n
References<\/h2>\n\n\n\n
\n
Final Thoughts<\/h2>\n\n\n\n