Tag Archives: schemas

Harmonize, Query, and Visualize Data from Various Providers using AWS Glue, Amazon Athena, and Amazon QuickSight

Post Syndicated from Ben Snively original https://aws.amazon.com/blogs/big-data/harmonize-query-and-visualize-data-from-various-providers-using-aws-glue-amazon-athena-and-amazon-quicksight/

Have you ever been faced with many different data sources in different formats that need to be analyzed together to drive value and insights?  You need to be able to query, analyze, process, and visualize all your data as one canonical dataset, regardless of the data source or original format.

In this post, I walk through using AWS Glue to create a query optimized, canonical dataset on Amazon S3 from three different datasets and formats. Then, I use Amazon Athena and Amazon QuickSight to query against that data quickly and easily.

AWS Glue overview

AWS Glue is a fully managed extract, transform, and load (ETL) service that makes it easy for customers to prepare and load their data for query and analytics. You can create and run an ETL job with a few clicks in the AWS Management Console. Point AWS Glue to your data stored on AWS, and a crawler discovers your data, classifies it, and stores the associated metadata (such as table definitions) in the AWS Glue Data Catalog. After it’s cataloged, your data is immediately searchable, queryable, and available for ETL. AWS Glue generates the ETL code for data transformation, and loads the transformed data into a target data store for analytics.

The AWS Glue ETL engine generates Python code that is entirely customizable, reusable, and portable. You can edit the code using your favorite IDE or notebook and share it with others using GitHub. After your ETL job is ready, you can schedule it to run on the AWS Glue fully managed, scale-out Spark environment, using its flexible scheduler with dependency resolution, job monitoring, and alerting.

AWS Glue is serverless. It automatically provisions the environment needed to complete the job, and customers pay only for the compute resources consumed while running ETL jobs. With AWS Glue, data can be available for analytics in minutes.

Walkthrough

During this post, I step through an example using the New York City Taxi Records dataset. I focus on one month of data, but you could easily do this for the entire eight years of data. At the time of this post, AWS Glue is available in US-East-1 (N. Virginia).

As you crawl the unknown dataset, you discover that the data is in different formats, depending on the type of taxi. You then convert the data to a canonical form, start to analyze it, and build a set of visualizations… all without launching a single server.

Discover the data

To analyze all the taxi rides for January 2016, you start with a set of data in S3. First, create a database for this project within AWS Glue. A database is a set of associated table definitions, organized into a logical group. In Athena, database names are all lowercase, no matter what you type.

Next, add a new crawler and have it infer the schemas, structure, and other properties of the data.

  1. Under Crawlers, choose Add crawler.
  2. For Name, type “NYCityTaxiCrawler”.
  3. Choose an IAM role that AWS Glue can use for the crawler. For more information, see the AWS Glue Developer Guide.
  4. For Data store, type S3.
  5. For Crawl data in, choose Specified path in another account.
  6. For Include path, type “s3://serverless-analytics/glue-blog”.
  7. For Add Another data store, choose No.
  8. For Frequency, choose On demand. This allows you to customize when the crawler runs.
  9. Configure the crawler output database and prefix:
    1. For Database, select the database created earlier, “nycitytaxianalysis”.
    2. For Prefix added to tables (optional), type “blog_”.
    3. Choose Finish and Run it now.

The crawler runs and indicates that it found three tables.

If you look under Tables, you can see the three new tables that were created under the database “nycitytaxianalysis”.

The crawler used the built-in classifiers and identified the tables as CSV, inferred the columns/data types, and collected a set of properties for each table. If you look in each of those table definitions, you see the number of rows for each dataset found and that the columns don’t match between tables. As an example, bringing up the blog_yellow table, you can see the yellow dataset for January 2017 with 8.7 million rows, the location on S3, and the various columns found.

Optimize queries and get data into a canonical form

Create an ETL job to move this data into a query-optimized form. In a future post, I’ll cover how to partition the query-optimized data. You convert the data into a column format, changing the storage type to Parquet, and writing the data to a bucket that you own.

  1. Create a new ETL job and name it NYCityTaxiYellow.
    1. Select an IAM role that has permissions to write to a new S3 location in a bucket that you own.
    2. Specify a location for the script and tmp space on S3.
  2. Choose the blog_yellow table as a data source.
  3. Specify a new location (a new prefix location without any existing objects) to store the results.
  4. In the transform, rename the pickup and dropoff dates to be generic fields. The raw data actually lists these fields differently and you should use a common name.  To do this, choose the target column name and retype the new field name.

    Also, change the data types to be time stamps rather than strings. The following table outlines the updates.

    Old Name Target Name Target Data Type
    tpep_pickup_datetime pickup_date timestamp
    tpep_dropoff_datetime dropoff_date timestamp
  5. Choose Next and Finish.

In this case, AWS Glue creates a script. You could also start with a blank script or provide your own.

Because AWS Glue uses Apache Spark behind the scenes, you can easily switch from an AWS Glue DynamicFrame to a Spark DataFrame and do advanced operations within Apache Spark.  Just as easily, you can switch back and continue to use the transforms and tables from the catalog.

To show this, convert to a DataFrame and add a new field column indicating the type of taxi. Make the following custom modifications in PySpark.

Add the following header:

from pyspark.sql.functions import lit
from awsglue.dynamicframe import DynamicFrame

Find the last call before the the line that start with the datasink.  This is the dynamic frame that is being used to write out the data.

Let’s now convert that to a DataFrame.  Please replace the <DYNAMIC_FRAME_NAME> with the name generated in the script.

##----------------------------------
#convert to a Spark DataFrame...
customDF = <DYNAMIC_FRAME_NAME>.toDF()
 
#add a new column for "type"
customDF = customDF.withColumn("type", lit('yellow'))
 
# Convert back to a DynamicFrame for further processing.
customDynamicFrame = DynamicFrame.fromDF(customDF, glueContext, "customDF_df")
##----------------------------------

In the last datasink call, change the dynamic frame to point to the new custom dynamic frame created from the Spark DataFrame:

datasink4 = glueContext.write_dynamic_frame.from_options(frame = customDynamicFrame, connection_type = "s3", connection_options = {"path": "s3://<YOURBUCKET/ AND PREFIX/"}, format = "parquet", transformation_ctx = "datasink4")

Save and run the ETL.

 

To use AWS Glue with Athena, you must upgrade your Athena data catalog to the AWS Glue Data Catalog. Without the upgrade, tables and partitions created by AWS Glue cannot be queried with Athena. As of August 14, 2017, the AWS Glue Data Catalog is only available in US-East-1 (N. Virginia). Start the upgrade in the Athena console. For more information, see Integration with AWS Glue in the Amazon Athena User Guide.

Next, we’ll go into Athena and create a table there for the new data format.  This shows how you can interact with the data catalog either through Glue or the Athena.

  • Go into the Athena Web Console
  • Select the nycitytaxianalysis database
  • Run the following create statement:
CREATE EXTERNAL TABLE IF NOT EXISTS nycitytaxianalysis.CanonicalData (
  VendorID INT,
  pickup_datetime TIMESTAMP,
  dropoff_datetime TIMESTAMP,
  passenger_count BIGINT,
  trip_distance DOUBLE,
  pickup_longitude DOUBLE,
  pickup_latitude DOUBLE,
  RatecodeID BIGINT,
  store_and_fwd_flag STRING,
  dropoff_longitude DOUBLE,
  dropoff_latitude DOUBLE,
  payment_type INT,
  fare_amount DOUBLE,
  extra DOUBLE,
  mta_tax DOUBLE,
  tip_amount DOUBLE,
  tolls_amount DOUBLE,
  improvement_surcharge DOUBLE,
  total_amount DOUBLE,
  type STRING
)
STORED AS PARQUET
LOCATION 's3://<YOUR_ETL_TARGET_SCRIPT_LOCATION>'
TBLPROPERTIES ('classification'='parquet')

Note: The table properties are important for Glue ETL to know the destination format.  More details can be found here: http://docs.aws.amazon.com/athena/latest/ug/glue-athena.html#creating-tables-using-ate-for-aws-glue-etl-jobs

Within the Glue Data Catalog, select the new table that you created through Athena under Actions, select “View data”.


If you do a query on the count per type of taxi, you notice that there is only “yellow” taxi data in the canonical location.  Convert “green” and “fhv” data:

SELECT type, count(*) FROM <OUTPUT_TABLE_NAME> group by type

In the AWS Glue console, create a new ETL job named “NYCityTaxiGreen”. Choose blog_green as the source and the new canonical location as the target. Choose the new pickup_date and dropoff_date fields for the target field types for the date variables, as shown below.

In the script, insert the following two lines at the end of the top imports:

from pyspark.sql.functions import lit
from awsglue.dynamicframe import DynamicFrame

Find the last call before the the line that start with the datasink.  This is the dynamic frame that is being used to write out the data.

Let’s now convert that to a DataFrame.  Please replace the <DYNAMIC_FRAME_NAME> with the name generated in the script.

Type this line before the last sink calls:
##----------------------------------
#convert to a Spark DataFrame...
customDF = <DYNAMIC_FRAME_NAME>.toDF()
 
#add a new column for "type"
customDF = customDF.withColumn("type", lit('green'))
 
# Convert back to a DynamicFrame for further processing.
customDynamicFrame = DynamicFrame.fromDF(customDF, glueContext, "customDF_df")

In the last datasink call, change the dynamic frame to point to the new custom dynamic frame created from the Spark DataFrame:

datasink4 = glueContext.write_dynamic_frame.from_catalog(frame = customDynamicFrame, database = "nycitytaxianalysis", table_name = "glue_blog", transformation_ctx = "datasink4")

Notice that it matches the last line in the commands that you pasted earlier. After the ETL job finishes, you can require the count per type through Athena and see the sizes:

SELECT type, count(*) FROM <OUTPUT_TABLE_NAME> group by type

What you have done is taken two datasets that contained taxi data in different formats and converted them into a single, query-optimized format. That format can easily be consumed by various analytical tools, including Athena, EMR, and Redshift Spectrum. You’ve done all of this without launching a single server.

You have one more dataset to convert, so create a new ETL job for that. Name the job “NYCityTaxiFHV”.  This dataset is limited as it only has three fields, one of which you are converting. Choose “–” (dash) for the dispatcher and location, and map the pickup date to the canonical field.

In the script, insert the following two lines at the end of the top imports:

from pyspark.sql.functions import lit
from awsglue.dynamicframe import DynamicFrame

Type the following line before the last sink calls:

##----------------------------------
#convert to a Spark DataFrame...
customDF = resolvechoice3.toDF()

#add a new column for "type"
customDF = customDF.withColumn("type", lit('fhv'))

# Convert back to a DynamicFrame for further processing.
customDynamicFrame = DynamicFrame.fromDF(customDF, glueContext, "customDF_df")

In the last datasink call, change the dynamic frame to point to the new custom dynamic frame created from the Spark DataFrame:

datasink4 = glueContext.write_dynamic_frame.from_catalog(frame = customDynamicFrame, database = "nycitytaxianalysis", table_name = "glue_blog", transformation_ctx = "datasink4")

After the third ETL job completes, you can query the single dataset, in a query-optimized form on S3:

SELECT type, count(*) FROM <OUTPUT_TABLE_NAME> group by type

Query across the data with Athena

Here’s a query:

SELECT min(trip_distance) as minDistance,
         max(trip_distance) as maxDistance,
         min(total_amount) as minTotal,
         max(total_amount) as maxTotal
FROM <OUTPUT_TABLE_NAME>

Within a second or two, you can quickly see the ranges for these values.

You can see some outliers in the total amount and distances that are = 0.  In the next query, don’t include those when calculating things like average cost per mile:

SELECT type,
         avg(trip_distance) AS avgDist,
         avg(total_amount/trip_distance) AS avgCostPerMile
FROM <OUTPUT_TABLE_NAME>
WHERE trip_distance > 0
        AND total_amount > 0
GROUP BY  type

Interesting note: you aren’t seeing FHV broken out yet, because that dataset didn’t have a mapping to these fields. Here’s a query that’s a little more complicated, to calculate a 99th percentile:

SELECT TYPE, 
       avg(trip_distance) avgDistance, 
       avg(total_amount/trip_distance) avgCostPerMile, 
       avg(total_amount) avgCost,
       approx_percentile(total_amount, .99) percentile99
FROM <OUTPUT_TABLE_NAME> 
WHERE trip_distance > 0 and total_amount > 0
GROUP BY TYPE

Visualizing the data in QuickSight

Amazon QuickSight is a data visualization service you can use to analyze the data that you just combined. For more detailed instructions, see the Amazon QuickSight User Guide.

  1. Create a new Athena data source, if you haven’t created one yet.
  2. Create a new dataset for NY taxi data, and point it to the canonical taxi data.
  3. Choose Edit/Preview data.
  4. Add a new calculated field named HourOfDay.

  1. Choose Save and Visualize.

You can now visualize this dataset and query through Athena the canonical dataset on S3 that has been converted by AWS Glue. Selecting HourOfDay allows you to see how many taxi rides there are based on the hour of the day. This is a calculated field that is being derived by the time stamp in the raw dataset.

You can also deselect that and choose type to see the total counts again by type.

Now choose the pickup_date field in addition to the type field and notice that the plot type automatically changed. Because time is being used, it’s showing a time chart defaulting to the year. Choose a day interval instead.

Expand the x axis to zoom out. You can clearly see a large drop in all rides on January 23, 2016.

Summary

In the post, you went from data investigation to analyzing and visualizing a canonical dataset, without starting a single server. You started by crawling a dataset you didn’t know anything about and the crawler told you the structure, columns, and counts of records.

From there, you saw that all three datasets were in different formats, but represented the same thing: NY City Taxi rides. You then converted them into a canonical (or normalized) form that is easily queried through Athena and QuickSight, in addition to a wide number of different tools not covered in this post.
If you have questions or suggestions, please comment below.

Additional Reading

Learn how to harmonize, search, and analyze loosely coupled datasets on AWS.

 

About the Author

Ben Snively is a Public Sector Specialist Solutions Architect. He works with government, non-profit and education customers on big data and analytical projects, helping them build solutions using AWS. In his spare time he adds IoT sensors throughout his house and runs analytics on it.

 

 

 

AWS Summit New York – Summary of Announcements

Post Syndicated from Jeff Barr original https://aws.amazon.com/blogs/aws/aws-summit-new-york-summary-of-announcements/

Whew – what a week! Tara, Randall, Ana, and I have been working around the clock to create blog posts for the announcements that we made at the AWS Summit in New York. Here’s a summary to help you to get started:

Amazon Macie – This new service helps you to discover, classify, and secure content at scale. Powered by machine learning and making use of Natural Language Processing (NLP), Macie looks for patterns and alerts you to suspicious behavior, and can help you with governance, compliance, and auditing. You can read Tara’s post to see how to put Macie to work; you select the buckets of interest, customize the classification settings, and review the results in the Macie Dashboard.

AWS Glue – Randall’s post (with deluxe animated GIFs) introduces you to this new extract, transform, and load (ETL) service. Glue is serverless and fully managed, As you can see from the post, Glue crawls your data, infers schemas, and generates ETL scripts in Python. You define jobs that move data from place to place, with a wide selection of transforms, each expressed as code and stored in human-readable form. Glue uses Development Endpoints and notebooks to provide you with a testing environment for the scripts you build. We also announced that Amazon Athena now integrates with Amazon Glue, as does Apache Spark and Hive on Amazon EMR.

AWS Migration Hub – This new service will help you to migrate your application portfolio to AWS. My post outlines the major steps and shows you how the Migration Hub accelerates, tracks,and simplifies your migration effort. You can begin with a discovery step, or you can jump right in and migrate directly. Migration Hub integrates with tools from our migration partners and builds upon the Server Migration Service and the Database Migration Service.

CloudHSM Update – We made a major upgrade to AWS CloudHSM, making the benefits of hardware-based key management available to a wider audience. The service is offered on a pay-as-you-go basis, and is fully managed. It is open and standards compliant, with support for multiple APIs, programming languages, and cryptography extensions. CloudHSM is an integral part of AWS and can be accessed from the AWS Management Console, AWS Command Line Interface (CLI), and through API calls. Read my post to learn more and to see how to set up a CloudHSM cluster.

Managed Rules to Secure S3 Buckets – We added two new rules to AWS Config that will help you to secure your S3 buckets. The s3-bucket-public-write-prohibited rule identifies buckets that have public write access and the s3-bucket-public-read-prohibited rule identifies buckets that have global read access. As I noted in my post, you can run these rules in response to configuration changes or on a schedule. The rules make use of some leading-edge constraint solving techniques, as part of a larger effort to use automated formal reasoning about AWS.

CloudTrail for All Customers – Tara’s post revealed that AWS CloudTrail is now available and enabled by default for all AWS customers. As a bonus, Tara reviewed the principal benefits of CloudTrail and showed you how to review your event history and to deep-dive on a single event. She also showed you how to create a second trail, for use with CloudWatch CloudWatch Events.

Encryption of Data at Rest for EFS – When you create a new file system, you now have the option to select a key that will be used to encrypt the contents of the files on the file system. The encryption is done using an industry-standard AES-256 algorithm. My post shows you how to select a key and to verify that it is being used.

Watch the Keynote
My colleagues Adrian Cockcroft and Matt Wood talked about these services and others on the stage, and also invited some AWS customers to share their stories. Here’s the video:

— Jeff;

 

Upsert into Amazon Redshift using AWS Glue and SneaQL

Post Syndicated from Jeremy Winters original https://aws.amazon.com/blogs/big-data/upsert-into-amazon-redshift-using-aws-glue-and-sneaql/

This is a guest post by Jeremy Winters and Ritu Mishra, Solution Architects at Full 360. In their own words, “Full 360 is a cloud first, cloud native integrator, and true believers in the cloud since inception in 2007, our focus has been on helping customers with their journey into the cloud. Our practice areas – Big Data and Warehousing, Application Modernization, and Cloud Ops/Strategy – represent deep, but focused expertise.”

AWS Glue is a fully managed ETL (extract, transform, and load) service that makes it simple and cost-effective to categorize your data, clean it, enrich it, and move it reliably between various data stores.  As a company who has been building data warehouse solutions in the cloud for 10 years, we at Full 360 were interested to see how we can leverage AWS Glue in customer solutions. This post details our experience and lessons learned from using AWS Glue for an Amazon Redshift data integration use case.

UI-based ETL Tools

We have been anticipating the release of AWS Glue since it was announced at re:Invent 2016. Many of our customers are looking for an easy to use, UI-based tooling to manage their data transformation pipeline. However in our experience, the complexity of any production pipeline tends to be difficult to unwind, regardless of the technology used to create them. At Full 360, we build cloud-native, script-based applications deployed in containers to handle data integration. We think script-based transformation provides a balance of robustness and flexibility necessary to handle any data problem that comes our way.

AWS Glue caters both to developers who prefer writing scripts and those who want UI-based interactions. It is possible to initially create jobs using the UI, by selecting data source and target. Under the hood, AWS Glue auto-generates the Python code for you, which can be edited if needed, though this isn’t necessary for the majority of use cases.

Of course, you don’t have to rely on the UI at all. You can simply write your own Python scripts, store them in Amazon S3 with any dependent libraries, and import them into AWS Glue. AWS Glue also supports IDE integration with tools such as PyCharm, and interestingly enough, Zeppelin notebooks! These integrations are targeted toward developers who prefer writing Python themselves and want a cleaner dev/test cycle.

Developers who are already in the business of scripting ETL will be excited by the ability to easily deploy Python scripts with AWS Glue, using existing workflows for source control and CI/CD, and have them deployed and executed in a fully managed manner by AWS.  The UI experience of AWS Glue works well, but it is good to know that the tool accommodates those who prefer traditional coding. You can also dig into complex edge cases for data handling where the UI doesn’t cut it.

Serverless!

When AWS Lambda was released, we were excited for its potential to host our ETL processes. With Lambda, you are limited to the five-minute maximum for function execution. We resorted to running Docker containers and Amazon ECS to orchestrate many of our customers long running tasks. With this approach, we are still required to manage the underlying infrastructure.

After a closer look at AWS Glue, we realize that it is a full serverless PySpark runtime, accompanied by an Apache Hive metastore compatible catalog-as-a-service. This means that you are not just running a script on a single core, but instead you have the full power of a multi-worker Spark environment available. If you’re not familiar with the Spark framework, it introduces a new paradigm that allows for the processing of distributed, in-memory datasets. Spark has many uses, from data transformation to machine learning.

In AWS Glue, you use PySpark dataframes to transform data before reaching your database. Dataframes manage data in a way similar to relational databases, so the methods are likely to be familiar to most SQL users. Additionally, you can use SQL in your PySpark code to manipulate the data.

AWS Glue also simplifies the management of the runtime environment by providing you with a DPU setting, which allows you to dial up or down the amount of compute resources used to run your job. One DPU is equivalent to 4 vCPU, 16 GB Mem.

Common use case

We can see that most customers would leverage AWS Glue to load one or many files from S3 into Amazon Redshift. To accelerate this process, you can use the crawler, an AWS console-based utility, to discover the schema of your data and store it in the AWS Glue Data Catalog, whether your data sits in a file or a database. We were able to discover the schemas of our source file and target table, then have AWS Glue construct and execute the ETL job. It worked! We successfully loaded the beer drinkers’ dataset, JSON files used in our advanced tuning class, into Amazon Redshift!

Our use case

For our use case, we integrated AWS Glue and Amazon Redshift Spectrum with an open-source project that we initiated called SneaQL. SneaQL is an open source, containerized framework for sneaking interactivity into static SQL. SneaQL provides an extension to ANSI SQL with command tags to provide functionality such as loops, variables, conditional execution, and error handling to your scripts. It allows you to manage your scripts in an AWS CodeCommit Git repo, which then gets deployed and executed in a container.

We use SneaQL for complex data integrations, usually with an ELT model, where the data is loaded into the database, then transformed into fact tables using parameterized SQL. SneaQL enables advanced use cases like partial upsert aggregation of data, where multiple data sources can merge into the same fact table.

We think AWS Glue, Redshift Spectrum, and SneaQL offer a compelling way to build a data lake in S3, with all of your metadata accessible through a variety of tools such as Hive, Presto, Spark, and Redshift Spectrum). Build your aggregation table in Amazon Redshift to drive your dashboards or other high-performance analytics.

In the video below, you see a demonstration of using AWS Glue to convert JSON documents into Parquet for partitioned storage in an S3 data lake. We then access the data from S3 into Amazon Redshift by way of Redshift Spectrum. Nearing the end of the AWS Glue job, we then call AWS boto3 to trigger an Amazon ECS SneaQL task to perform an upsert of the data into our fact table. All the sample artifacts needed for this demonstration are available in the Full360/Sneaql Github repository.

In this scenario, AWS Glue manipulates data outside of a data warehouse and loads it to Amazon Redshift, and SneaQL manipulates data inside Amazon Redshift.

We think that they are a good compliment to each other when doing complex integrations.

Our pipeline works as follows:

  • New files arrive in S3 in JSON format.
  • AWS Glue converts the JSON files in Parquet format, stored in another S3 bucket.
  • At the end of the AWS Glue script, the AWS SDK for Python (Boto) is used to trigger the Amazon ECS task that runs SneaQL.
  • SneaQL container pulls the secrets file from S3 then decrypts it with biscuit or AWS KMS.
  • SneaQL clones the appropriate branch from an AWS CodeCommit Git repo.
  • Data in Amazon Redshift is transformed by SneaQL statements stored in the repo.

The AWS Glue team also supports conditional triggers that would allow us to split the jobs, and make one dependent upon the other.

Final thoughts

Although, as a general rule, we believe in managing schema definitions in source control, we definitely think that the crawler feature has some attractive perks. Besides being handy for ad-hoc jobs, it is also partition-aware. This means that you can perform data movements without worrying about which data has been processed already, which is one less thing for you to manage. We’re interested to see the design patterns that emerge around the use of the crawler.

The freedom of PySpark also allows us to implement advanced use cases. While the boto3 library is available to all AWS Glue jobs, it is also possible to include any libraries and additional JAR files along with your Python script.

We think many customers will find AWS Glue valuable. Those who prefer to code their data processing pipeline will be quick to realize how powerful it is, especially for solving complex data cleansing problems. The learning curve for PySpark is real, so we suggest looking into dataframes as a good entry point. In the long run, the fact that AWS Glue is serverless makes the effort more than worth it.

If you have questions or suggestions, please comment below.

 

Launch – AWS Glue Now Generally Available

Post Syndicated from Randall Hunt original https://aws.amazon.com/blogs/aws/launch-aws-glue-now-generally-available/

Today we’re excited to announce the general availability of AWS Glue. Glue is a fully managed, serverless, and cloud-optimized extract, transform and load (ETL) service. Glue is different from other ETL services and platforms in a few very important ways.

First, Glue is “serverless” – you don’t need to provision or manage any resources and you only pay for resources when Glue is actively running. Second, Glue provides crawlers that can automatically detect and infer schemas from many data sources, data types, and across various types of partitions. It stores these generated schemas in a centralized Data Catalog for editing, versioning, querying, and analysis. Third, Glue can automatically generate ETL scripts (in Python!) to translate your data from your source formats to your target formats. Finally, Glue allows you to create development endpoints that allow your developers to use their favorite toolchains to construct their ETL scripts. Ok, let’s dive deep with an example.

In my job as a Developer Evangelist I spend a lot of time traveling and I thought it would be cool to play with some flight data. The Bureau of Transportations Statistics is kind enough to share all of this data for anyone to use here. We can easily download this data and put it in an Amazon Simple Storage Service (S3) bucket. This data will be the basis of our work today.

Crawlers

First, we need to create a Crawler for our flights data from S3. We’ll select Crawlers in the Glue console and follow the on screen prompts from there. I’ll specify s3://crawler-public-us-east-1/flight/2016/csv/ as my first datasource (we can add more later if needed). Next, we’ll create a database called flights and give our tables a prefix of flights as well.

The Crawler will go over our dataset, detect partitions through various folders – in this case months of the year, detect the schema, and build a table. We could add additonal data sources and jobs into our crawler or create separate crawlers that push data into the same database but for now let’s look at the autogenerated schema.

I’m going to make a quick schema change to year, moving it from BIGINT to INT. Then I can compare the two versions of the schema if needed.

Now that we know how to correctly parse this data let’s go ahead and do some transforms.

ETL Jobs

Now we’ll navigate to the Jobs subconsole and click Add Job. Will follow the prompts from there giving our job a name, selecting a datasource, and an S3 location for temporary files. Next we add our target by specifying “Create tables in your data target” and we’ll specify an S3 location in Parquet format as our target.

After clicking next, we’re at screen showing our various mappings proposed by Glue. Now we can make manual column adjustments as needed – in this case we’re just going to use the X button to remove a few columns that we don’t need.

This brings us to my favorite part. This is what I absolutely love about Glue.

Glue generated a PySpark script to transform our data based on the information we’ve given it so far. On the left hand side we can see a diagram documenting the flow of the ETL job. On the top right we see a series of buttons that we can use to add annotated data sources and targets, transforms, spigots, and other features. This is the interface I get if I click on transform.

If we add any of these transforms or additional data sources, Glue will update the diagram on the left giving us a useful visualization of the flow of our data. We can also just write our own code into the console and have it run. We can add triggers to this job that fire on completion of another job, a schedule, or on demand. That way if we add more flight data we can reload this same data back into S3 in the format we need.

I could spend all day writing about the power and versatility of the jobs console but Glue still has more features I want to cover. So, while I might love the script editing console, I know many people prefer their own development environments, tools, and IDEs. Let’s figure out how we can use those with Glue.

Development Endpoints and Notebooks

A Development Endpoint is an environment used to develop and test our Glue scripts. If we navigate to “Dev endpoints” in the Glue console we can click “Add endpoint” in the top right to get started. Next we’ll select a VPC, a security group that references itself and then we wait for it to provision.


Once it’s provisioned we can create an Apache Zeppelin notebook server by going to actions and clicking create notebook server. We give our instance an IAM role and make sure it has permissions to talk to our data sources. Then we can either SSH into the server or connect to the notebook to interactively develop our script.

Pricing and Documentation

You can see detailed pricing information here. Glue crawlers, ETL jobs, and development endpoints are all billed in Data Processing Unit Hours (DPU) (billed by minute). Each DPU-Hour costs $0.44 in us-east-1. A single DPU provides 4vCPU and 16GB of memory.

We’ve only covered about half of the features that Glue has so I want to encourage everyone who made it this far into the post to go read the documentation and service FAQs. Glue also has a rich and powerful API that allows you to do anything console can do and more.

We’re also releasing two new projects today. The aws-glue-libs provide a set of utilities for connecting, and talking with Glue. The aws-glue-samples repo contains a set of example jobs.

I hope you find that using Glue reduces the time it takes to start doing things with your data. Look for another post from me on AWS Glue soon because I can’t stop playing with this new service.
– Randall

Open Sourcing Bullet, Yahoo’s Forward-Looking Query Engine for Streaming Data

Post Syndicated from mikesefanov original https://yahooeng.tumblr.com/post/161855616651

By Michael Natkovich, Akshai Sarma, Nathan Speidel, Marcus Svedman, and Cat Utah

Big Data is no longer just Apache server logs. Nowadays, the data may be user engagement data, performance metrics, IoT (Internet of Things) data, or something else completely atypical. Regardless of the size of the data, or the type of querying patterns on it (exploratory, ad-hoc, periodic, long-term, etc.), everyone wants queries to be as fast as possible and cheap to run in terms of resources. Data can be broadly split into two kinds: the streaming (generally real-time) kind or the batched-up-over-a-time-interval (e.g., hourly or daily) kind. The batch version is typically easier to query since it is stored somewhere like a data warehouse that has nice SQL-like interfaces or an easy to use UI provided by tools such as Tableau, Looker, or Superset. Running arbitrary queries on streaming data quickly and cheaply though, is generally much harder… until now. Today, we are pleased to share our newly open sourced, forward-looking general purpose query engine, called Bullet, with the community on GitHub.

With Bullet, you can: 

  • Run queries on real-time streaming data with the same convenience and flexibility as batch data. You get an API and a UI.
  • Execute forward-looking queries on typed data with arbitrary schemas.
  • Run queries that support
    • Powerful and nested filtering
    • Fetching raw data records
    • Aggregating data using Group Bys (Sum, Count, Average, etc.), Count Distincts, Top Ks
    • Getting distributions of fields like Percentiles or Frequency histograms 

One of the key differences between how Bullet queries data and the standard querying paradigm is that Bullet does not store any data. In most other systems where you have a persistence layer (including in-memory storage), you are doing a look-back when you query the layer. Instead, Bullet operates on data flowing through the system after the query is started – it’s a look-forward system that doesn’t need persistence. On a real-time data stream, this means that Bullet is querying data after the query is submitted. This also means that Bullet does not query any data that has already passed through the stream. The fact that Bullet does not rely on a persistence layer is exactly what makes it extremely lightweight and cheap to run. 

To see why this is better for the kinds of use cases Bullet is meant for – such as quickly looking at some metric, checking some assumption, iterating on a query, checking the status of something right now, etc. – consider the following: if you had a 1000 queries in a traditional query system that operated on the same data, these query systems would most likely scan the data 1000 times each. By the very virtue of it being forward looking, 1000 queries in Bullet scan the data only once because the arrival of the query determines and fixes the data that it will see. Essentially, the data is coming to the queries instead of the queries being farmed out to where the data is. When the conditions of the query are satisfied (usually a time window or a number of events), the query terminates and returns you the result. 

A Brief Architecture Overview

image

High Level Bullet Architecture

The Bullet architecture is multi-tenant, can scale linearly for more queries and/or more data, and has been tested to handle 700+ simultaneous queries on a data stream that had up to 1.5 million records per second, or 5-6 GB/s. Bullet is currently implemented on top of Storm and can be extended to support other stream processing engines as well, like Spark Streaming or Flink. Bullet is pluggable, so you can plug in any source of data that can be read in Storm by implementing a simple data container interface to let Bullet work with it. 

The UI, web service, and the backend layers constitute your standard three-tier architecture. The Bullet backend can be split into three main subsystems:

  1. Request Processor – receives queries, adds metadata, and sends it to the rest of the system
  2. Data Processor – reads data from an input stream, converts it to a unified data format, and matches it against queries
  3. Combiner – combines results for different queries, performs final aggregations, and returns results 

The web service can be deployed on any servlet container, like Jetty. The UI is a Node-based Ember application that runs in the client browser. Our full documentation contains all the details on exactly how we perform computationally-intractable queries like Count Distincts on fields with cardinality in the millions, etc. (DataSketches). 

Usage at Yahoo 

An instance of Bullet is currently running at Yahoo in production against a small subset of Yahoo’s user engagement data stream. This data is roughly 100,000 records per second and is about 130 MB/s compressed. Bullet queries this with about 100 CPU Virtual Cores and 120 GB of RAM. This fits on less than 2 of our (64 Virtual Cores, 256 GB RAM each) test Storm cluster machines. 

One of the most popular use cases at Yahoo is to use Bullet to manually validate the instrumentation of an app or web application. Instrumentation produces user engagement data like clicks, views, swipes, etc. Since this data powers everything we do from analytics to personalization to targeting, it is absolutely critical that the data is correct. The usage pattern is generally to: 

  1. Submit a Bullet query to obtain data associated with your mobile device or browser (filter on a cookie value or mobile device ID)
  2. Open and use the application to generate the data while the Bullet query is running
  3. Go back to Bullet and inspect the data 

In addition, Bullet is also used programmatically in continuous delivery pipelines for functional testing instrumentation on product releases. Product usage is simulated, then data is generated and validated in seconds using Bullet. Bullet is orders of magnitude faster to use for this kind of validation and for general data exploration use cases, as opposed to waiting for the data to be available in Hive or other systems. The Bullet UI supports pivot tables and a multitude of charting options that may speed up analysis further compared to other querying options. 

We also use Bullet to do a bunch of other interesting things, including instances where we dynamically compute cardinalities (using a Count Distinct Bullet query) of fields as a check to protect systems that can’t support extremely high cardinalities for fields like Druid. 

What you do with Bullet is entirely determined by the data you put it on. If you put it on data that is essentially some set of performance metrics (data center statistics for example), you could be running a lot of queries that find the 95th and 99th percentile of a metric. If you put it on user engagement data, you could be validating instrumentation and mostly looking at raw data. 

We hope you will find Bullet interesting and tell us how you use it. If you find something you want to change, improve, or fix, your contributions and ideas are always welcome! You can contact us here. 

Helpful Links 

Cloud Directory Update – Support for Typed Links

Post Syndicated from Jeff Barr original https://aws.amazon.com/blogs/aws/cloud-directory-update-support-for-typed-links/

Earlier this year I told you about Cloud Directory, our cloud-native directory for hierarchical data and told you how it was designed to store large amounts of strongly typed hierarchical data. Because Cloud Directory can scale to store hundreds of millions of objects, it is a great fit for many kinds of cloud and mobile applications.

In my original post I explained how each directory has one or more schemas, each of which has, in turn, one or more facets. Each facet defines the set of required and allowable attributes for an object.

Introducing Typed Links
Today we are extending the expressive power of the Cloud Directory model by adding support for typed links. You can use these links to create object-to-object relationships across hierarchies. You can define multiple types of links for each of your directories (each link type is a separate facet in one of the schemas associated with the directory). In addition to a type, each link can have a set of attributes. Typed links help to maintain referential data integrity by ensuring objects with existing relationships to other objects are not deleted inadvertently.

Suppose you have a directory of locations, factories, floor numbers, rooms, machines and sensors. Each of these can be represented as a dimension in Cloud Directory with rich metadata information within the hierarchy. You can define and use typed links to connect the objects together in various ways, creating typed links that lead to maintenance requirements, service records, warranties, and safety information, with attributes on the links to store additional information about the relationship between the source object and the destination object.

Then you can run queries that are based on the type of a link and the values of the attributes within it. For example, you could locate all sensors that have not been cleaned in the past 45 days, or all of the motors that are no longer within their warranty period. You can find all of the sensors that are on a given floor, or you can find all of the floors where sensors of a given type are located.

Using Typed Links
To use typed links you simply add one or more Typed Link facets to your schema using the CreateTypedLinkFacet function. Then you call AttachTypedLink, passing in the source and destination objects, the Typed Link facet, and the attributes for the link. Other useful functions include GetTypedLinkFacetInformation, ListIncomingTypedLinks, and ListOutgoingTypedLinks. To learn more and to see the complete list of functions, take a look at the Cloud Directory API Reference.

Just as you can do for objects, you can use Attribute Rules to constrain attribute values. You can constrain the length of strings and byte arrays, restrict strings to a specified set of values, and limit numbers to a specific range.

My colleagues shared some sample code that illustrates how to use typed links. Here are the ARNs and the name of the facet:

String appliedSchemaArn   = "arn:aws:clouddirectory:eu-west-2:XXXXXXXXXXXX:directory/AbF4qXxa80WSsLRiYhDB-Jo/schema/demo_organization/1.0";
String directoryArn       = "arn:aws:clouddirectory:eu-west-2:XXXXXXXXXXXX:directory/AbF4qXxa80WSsLRiYhDB-Jo";
String typedLinkFacetName = "FloorSensorAssociation";

The first snippet creates a typed link facet called FloorSensorAssociation with sensor_type and maintenance_date attributes, in that order (attribute names and values are part of the identity of the link, so order matters):

client.createTypedLinkFacet(new CreateTypedLinkFacetRequest()
                                .withSchemaArn(appliedSchemaArn)
                                .withFacet(
                                      new TypedLinkFacet()
                                          .withName(typedLinkFacetName)
                                          .withAttributes(toTypedLinkAttributeDefinition("sensor_type"),
                                                          toTypedLinkAttributeDefinition("maintenance_date"))
                                          .withIdentityAttributeOrder("sensor_type", "maintenance_date")));

private TypedLinkAttributeDefinition toTypedLinkAttributeDefinition(String attributeName) {
 return new TypedLinkAttributeDefinition().withName(attributeName)
 .withRequiredBehavior(RequiredAttributeBehavior.REQUIRED_ALWAYS)
 .withType(FacetAttributeType.STRING);
}

The next snippet creates a link between two objects (sourceFloor and targetSensor), with sensor_type water and maintenance_date 2017-05-24:

AttachTypedLinkResult result = 
    client.attachTypedLink(new AttachTypedLinkRequest()
                               .withDirectoryArn(directoryArn)
                               .withTypedLinkFacet(
                                   toTypedLinkFacet(appliedSchemaArn, typedLinkFacetName))
                                   .withAttributes(
                                        attributeNameAndStringValue("sensor_type", "water"),
                                        attributeNameAndStringValue("maintenance_date", "2017-05-24"))
                                   .withSourceObjectReference(sourceFloor)
                                   .withTargetObjectReference(targetSensor));

private TypedLinkSchemaAndFacetName toTypedLinkFacet(String appliedSchemaArn, String typedLinkFacetName) {
   return new TypedLinkSchemaAndFacetName()
              .withTypedLinkName(typedLinkFacetName)
              .withSchemaArn(appliedSchemaArn);
}

The final snippet enumerates all incoming typed links of sensor_type water and a maintenance_date in the range 2017-05-20 to 2017-05-24:

client.listIncomingTypedLinks(
    new ListIncomingTypedLinksRequest()
        .withFilterTypedLink(toTypedLinkFacet(appliedSchemaArn, typedLinkFacetName))
        .withDirectoryArn(directoryArn)
        .withObjectReference(targetSensor)
        .withMaxResults(10)
        .withFilterAttributeRanges(attributeRange("sensor_type", exactRange("water")),
                                   attributeRange("maintenance_date", 
                                                  range("2017-05-20", "2017-05-24"))));

private TypedLinkAttributeRange attributeRange(String attributeName, TypedAttributeValueRange range) {
   return new TypedLinkAttributeRange().withAttributeName(attributeName).withRange(range);
}

private TypedAttributeValueRange exactRange(String value) {
   return range(value, value);
}

To learn more, read about Objects and Links in the Cloud Directory Administration Guide.

Available Now
Typed links are available now and you can start using them today!

— Jeff;

Register for and Attend This Free April 27 Tech Talk—Deep Dive on Amazon Cloud Directory

Post Syndicated from Craig Liebendorfer original https://aws.amazon.com/blogs/security/register-for-and-attend-this-april-27-tech-talk-deep-dive-on-amazon-cloud-directory/

AWS webinars logo

As part of the AWS Monthly Online Tech Talks series, AWS will present Deep Dive on Amazon Cloud Directory on Thursday, April 27. This tech talk will start at noon and end at 1:00 P.M. Pacific Time.

AWS Cloud Directory Expert Quint Van Deman will show you how Amazon Cloud Directory enables you to build flexible cloud-native directories for organizing hierarchies of data along multiple dimensions. Using Cloud Directory, you can easily build organizational charts, device registries, course catalogs, and network configurations with multiple hierarchies. For example, you can build an organizational chart with one hierarchy based on reporting structure, a second hierarchy based on physical location, and a third based on cost center.

You also will learn:

  • About the benefits and features of Cloud Directory.
  • The advantages of using Cloud Directory over traditional directory solutions.
  • How to efficiently organize hierarchies of data across multiple dimensions.
  • How to create and extend Cloud Directory schemas.
  • How to search your directory using strongly consistent and eventually consistent search APIs.

This tech talk is free. Register today.

– Craig

MySQL 8 is coming (Opensource.com)

Post Syndicated from ris original https://lwn.net/Articles/715946/rss

Opensource.com takes a look
at changes to MySQL 8.0. “Ever open up a directory of a MySQL schema and see all those files—.frm, .myi, .myd, and the like? Those files hold some of the metadata on the database schemas. Twenty years ago, it was a good way to go, but InnoDB is a crash proof storage engine and can hold all that metadata safely. This means file corruption of a .frm file is not going to stall your work. Developers also removed the file system’s maximum number of files as the limiting factor to your number of databases; you can now have literally have millions of tables in your database.”

How to Create an Organizational Chart with Separate Hierarchies by Using Amazon Cloud Directory

Post Syndicated from Srikanth Mandadi original https://aws.amazon.com/blogs/security/how-to-create-an-organizational-chart-with-separate-hierarchies-by-using-amazon-cloud-directory/

Amazon Cloud Directory enables you to create directories for a variety of use cases, such as organizational charts, course catalogs, and device registries. Cloud Directory offers you the flexibility to create directories with hierarchies that span multiple dimensions. For example, you can create an organizational chart that you can navigate through separate hierarchies for reporting structure, location, and cost center.

In this blog post, I show how to use Cloud Directory APIs to create an organizational chart with two separate hierarchies in a single directory. I also show how to navigate the hierarchies and retrieve data. I use the Java SDK for all the sample code in this post, but you can use other language SDKs or the AWS CLI.

Define a schema

The first step in using Cloud Directory is to define a schema, which describes the data that will be stored in the directory that you will create later in this post. In this example, I define the schema by providing a JSON document. The schema has two facets: Employee and Group. I constrain the attributes within these facets by using various rules provided by Cloud Directory. For example, I specify that the Name attribute is of type STRING and must have a minimum length of 3 characters and maximum length of 100 characters. Similarly, I specify that the Status attribute is of type STRING and the value of this attribute must have one of the following three values: ACTIVE, INACTIVE, or TERMINATED. Having Cloud Directory handle these constraints means that I do not need to handle the validation of these constraints in my code, and it also lets multiple applications share the data in my directory without violating these constraints.

I also specify that the objectType of Employee is a LEAF_NODE. Therefore, employee objects cannot have any children, but can have multiple parents. The objectType of Group is NODE, which means group objects can have children, but they can only have one parent object. In the next section, I show you how to create a directory with this schema by using some sample Java code. Save the following JSON document to a file and provide the path to the file in the code for creating the schema in the next section.

{
  "facets" : {
    "Employee" : {
      "facetAttributes" : {
        "Name" : {
          "attributeDefinition" : {
            "attributeType" : "STRING",
            "isImmutable" : false,
            "attributeRules" : {
              "NameLengthRule" : {
                "parameters" : {
                  "min" : "3",
                  "max" : "100"
                },
                "ruleType": "STRING_LENGTH"
              }
            }
          },
          "requiredBehavior" : "REQUIRED_ALWAYS"
        },
        "EmailAddress" : {
          "attributeDefinition" : {
            "attributeType" : "STRING",
            "isImmutable" : true,
            "attributeRules" : {
              "NameLengthRule" : {
                "parameters" : {
                  "min" : "3",
                  "max" : "100"
                },
                "ruleType": "STRING_LENGTH"
              }
            }
          },
          "requiredBehavior" : "REQUIRED_ALWAYS"
        },
        "Status" : {
          "attributeDefinition" : {
            "attributeType" : "STRING",
            "isImmutable" : true,
            "attributeRules" : {
              "rule1" : {
                "parameters" : {
                  "allowedValues" : "ACTIVE, INACTIVE, TERMINATED"
                },
                "ruleType": "STRING_FROM_SET"
              }
            }
          },
          "requiredBehavior" : "REQUIRED_ALWAYS"
        }
      },
      "objectType" : "LEAF_NODE"
    },
    "Group" : {
      "facetAttributes" : {
        "Name" : {
          "attributeDefinition" : {
            "attributeType" : "STRING",
            "isImmutable" : true
          },
          "requiredBehavior" : "REQUIRED_ALWAYS"
        }
      },
      "objectType" : "NODE"
    }
  }
}

Create and publish the schema

Similar to other AWS services, I have to create the client for Cloud Directory to call the service APIs. To create a client, I use the following Java code.

    AWSCredentialsProvider credentials = null;
    try {
        credentials = new ProfileCredentialsProvider("default");
    } catch (Exception e) {
        throw new AmazonClientException(
            "Cannot load the credentials from the credential profiles file. " +
            "Please make sure that your credentials file is at the correct " +
            "location, and is in valid format.",
            e);
    }
    AmazonCloudDirectory client = AmazonCloudDirectoryClientBuilder.standard()
            .withRegion(Regions.US_EAST_1)
            .withCredentials(credentials)
            .build();

Now, I am ready to create the schema that I defined in the JSON file earlier in the post. When I create the schema, it is in the Development state. A schema in Cloud Directory can be in the Development, Published, or Applied state. When the schema is in the Development state, I can make more changes to the schema. In this case, however, I don’t want to make additional changes. Therefore, I will just publish the schema, which makes it available for creating directories (you cannot modify a schema in the Published state). I discuss the Applied state for schemas in the next section. In the following code, change the jsonFilePath variable to the file location where you saved the JSON schema in the previous step.

    //Read the JSON schema content from the file. 
    String jsonFilePath = <Provide the location of the json schema file here>;
    String schemaDocument;
    try
    {
        schemaDocument = new String(Files.readAllBytes(Paths.get(jsonFilePath)));
    }
    catch(IOException e)
    {
        throw new RuntimeException(e);
    }
    
    //Create an empty schema with a schema name. The schema name needs to be unique
    //within an AWS account.    
    CreateSchemaRequest createSchemaRequest = new CreateSchemaRequest()
        .withName("EmployeeSchema");
    String developmentSchemaArn =  client.createSchema(createSchemaRequest).getSchemaArn();    
    
    //Load the previously defined JSON into the empty schema that was just created
    PutSchemaFromJsonRequest putSchemaRequest = new PutSchemaFromJsonRequest()
           .withDocument(schemaDocument)
           .withSchemaArn(developmentSchemaArn);
    PutSchemaFromJsonResult putSchemaResult =  client.putSchemaFromJson(putSchemaRequest);

    //No more changes needed for schema so publish the schema
    PublishSchemaRequest publishSchemaRequest = new PublishSchemaRequest()
        .withDevelopmentSchemaArn(developmentSchemaArn)
        .withVersion("1.0");
    String publishedSchemaArn =  client.publishSchema(publishSchemaRequest).getPublishedSchemaArn();

Create a directory by using the published schema

I am now ready to create a directory by using the schema I just published. When I create a directory, Cloud Directory copies the published schema to the newly created directory. The schema copied to this directory is in the Applied state, which means if I had a scenario in which a schema attached to a particular directory needed to be changed, I could make changes to the schema that is applied to that specific directory.

The following code creates the directory and receives the Applied schema ARN and directory ARN. This Applied schema ARN is useful if I need to make changes to the schema applied to this directory. The directory ARN will be used in all subsequent operations associated with the directory. Cloud Directory will use the directory ARN to identify the directory associated with incoming requests because a single customer can create multiple directories.

    //Create a directory using the published schema. Specify a directory name, which must be unique within an account.
    CreateDirectoryRequest createDirectoryRequest = new CreateDirectoryRequest()
        .withName("EmployeeDirectory")
        .withSchemaArn(publishedSchemaArn);
    CreateDirectoryResult createDirectoryResult =  client.createDirectory(createDirectoryRequest);
    String directoryArn = createDirectoryResult.getDirectoryArn();
    String appliedSchemaArn = createDirectoryResult.getAppliedSchemaArn();

How hierarchies are stored in a directory

The organizational chart I want to create has a simple hierarchy as shown in the following diagram. Anna belongs to both the ITStaff and Managers groups. This example demonstrates a capability of Cloud Directory that enables me to build multiple hierarchies in a single directory. These hierarchies can have their own structure and leaf nodes belonging to more than one hierarchy because lead nodes can have more than one parent.

Being able to create multiple hierarchies within a single directory gives me some flexibility in how I organize my employees. For example, I can create a hierarchy representing departments in my organization and add employees to their respective departments, as illustrated in the following diagram. I can create another hierarchy representing geographic locations and add employees to the geographic location where they work. The first step in creating this hierarchy is to create the ITStaff and Managers group objects, which is what I do in the next section.

Hierarchy diagram

Create group objects

I will now create the data representing my organizational chart in the directory that I created. The following code creates the ITStaff and Managers group objects, which are created under the root node of the directory.

    for (String groupName : Arrays.asList("ITStaff", "Managers")) {         
        CreateObjectRequest request = new CreateObjectRequest()
            .withDirectoryArn(directoryArn)
           // The parent of the object we are creating. We are rooting the group nodes   
           // under root object. The root object exists in all directories and the path         
           // to the root node is always "/".
           .withParentReference(new ObjectReference().withSelector("/"))
           // The name attached to the link between the parent and the child objects.
           .withLinkName(groupName)
           .withSchemaFacets(new SchemaFacet()
               .withSchemaArn(appliedSchemaArn)
               .withFacetName("Group"))
               //We specify the attributes to attach to this object.
                .withObjectAttributeList(new AttributeKeyAndValue()
                    .withKey(new AttributeKey()
                             // Name attribute for the group
                             .withSchemaArn(appliedSchemaArn)
                             .withFacetName("Group")
                             .withName("Name"))
                        // We provide the attribute value. The type used here must match the type defined in schema
                             .withValue(new TypedAttributeValue().withStringValue(groupName)));
    client.createObject(request);
    }

Create employee objects

The group objects are now in the directory. Next, I create employee objects for Anna and Bob under the ITStaff group. The following Java code creates the Anna object. Creating the Bob object is similar. When creating the Bob object, I provide different attribute values for Name, EmailAddress, and the like.

    CreateObjectRequest createAnna = new CreateObjectRequest()
                .withDirectoryArn(directoryArn)
                .withLinkName("Anna")
                .withParentReference(new ObjectReference().withSelector("/ITStaff"))
                .withSchemaFacets(new SchemaFacet()
                        .withSchemaArn(appliedSchemaArn)
                        .withFacetName("Employee"))
                .withObjectAttributeList(new AttributeKeyAndValue()
                        .withKey(new AttributeKey()
                                // Name attribute from employee facet
                                .withSchemaArn(appliedSchemaArn)
                                .withFacetName("Employee")
                                .withName("Name"))
                        .withValue(new TypedAttributeValue().withStringValue("Anna")),
                        new AttributeKeyAndValue()
                        .withKey(new AttributeKey()
                                // EmailAddress attribute from employee facet
                                .withSchemaArn(appliedSchemaArn)
                                .withFacetName("Employee")
                                .withName("EmailAddress"))
                        .withValue(new TypedAttributeValue().withStringValue("[email protected]")),
                        new AttributeKeyAndValue()
                        .withKey(new AttributeKey()
                                 // Status attribute from employee facet
                                .withSchemaArn(appliedSchemaArn)
                                .withFacetName("Employee")
                                .withName("Status"))
                        .withValue(new TypedAttributeValue().withStringValue("ACTIVE")));
     // CreateObject provides the object identifier of the object that was created.  An object identifier
       // is a globally unique, immutable identifier assigned to every object.
     String annasObjectId = client.createObject(createAnna).getObjectIdentifier();

Both the Bob and Anna objects are created under ITStaff, but Anna is also a manager and needs to be added under the Managers group. The following code does just that.

   AttachObjectRequest makeAnnaAManager = new AttachObjectRequest()
           .withDirectoryArn(directoryArn)
           .withLinkName("Anna")
           // Provide the parent object that Anna needs to be attached to using the path to the Managers object
           .withParentReference(new ObjectReference().withSelector("/Managers"))
           // Here we use the object identifier syntax to specify Anna's node. We could have used the
           // following path instead: /ITStaff/Anna. Both are equivalent.
           .withChildReference(new ObjectReference().withSelector("$" + annasObjectId));
   client.attachObject(makeAnnaAManager);

Retrieving objects in the directory

Now that I have populated my directory, I want to find a specific object. I can do that either by using the path to the object or the object identifier. I use the getObjectInformation API to first get the Anna object by specifying its path, and then I print the object identifiers of all the parents of the Anna object. I should print two parent object identifiers because Anna has both ITStaff and Managers as its parent. Here I am listing parents; however, I also can perform other operations on the object such as listing its children or its attributes. Using listChildren and listObjectAttributes, I can retrieve all the information stored in my directory.

    // First get the object for Anna
    GetObjectInformationRequest annaObjectRequest = new GetObjectInformationRequest()
           .withObjectReference(new ObjectReference().withSelector("/Managers/Anna"))
           .withDirectoryArn(directoryArn);
    GetObjectInformationResult annaObjectResult =  client.getObjectInformation(annaObjectRequest);
    // List parent objects for Anna to give her groups
    ListObjectParentsRequest annaGroupsRequest = new ListObjectParentsRequest()
           .withDirectoryArn(directoryArn)
           .withObjectReference(new ObjectReference().withSelector("$" + annaObjectResult.getObjectIdentifier()));
    ListObjectParentsResult annaGroupsResult =  client.listObjectParents(annaGroupsRequest);
    for(Map.Entry<String, String> entry : annaGroupsResult.getParents().entrySet())
    {
       System.out.println("Parent Object Identifier:" + entry.getKey());
       System.out.println("Link Name:" + entry.getValue());
    } 

Summary

In this post, I showed how to use Cloud Directory APIs to create an organizational chart with multiple hierarchies. Keep in mind that Cloud Directory offers additional functionality such as batch operations and indexing that I have not covered in this blog post. For more information, see the Amazon Cloud Directory API Reference.

If you have questions or suggestions about this blog post, start a new thread on the Directory Service forum.

– Srikanth