Extract ServiceNow knowledge utilizing AWS Glue Studio in an Amazon S3 knowledge lake and analyze utilizing Amazon Athena

Many alternative cloud-based software program as a service (SaaS) choices can be found in AWS. ServiceNow is likely one of the widespread cloud-based workflow automation platforms broadly utilized by AWS prospects. Prior to now few years, we noticed plenty of prospects who needed to extract and combine knowledge from IT service administration (ITSM) instruments like ServiceNow for numerous use instances:

• Generate perception from knowledge – Once you mix ServiceNow knowledge with knowledge from different companies like CRM (akin to Salesforce) or Martech knowledge (akin to Amazon Pinpoint) to generate higher insights (e.g., constructing full buyer 360 view).
• Archive knowledge for future enterprise or regulatory necessities – You’ll be able to archive the information in uncooked kind in your knowledge lake to work on future use instances or simply hold it to fulfill regulatory necessities akin to auditing.
• Enhance efficiency by decoupling reporting or machine studying use instances from ITSM – Once you transfer your ITSM reporting from ServiceNow to an Amazon Easy Storage Service (Amazon S3) knowledge lake, there isn’t a efficiency affect in your ServiceNow occasion.
• Knowledge democratization – You’ll be able to extract the information and put it into a knowledge lake so it may be accessible to different enterprise customers and models to discover and use.

Many shoppers have been constructing fashionable knowledge architectures on AWS, which incorporates constructing knowledge lakes on Amazon S3 and utilizing broad and deep AWS analytics and an AI/ML companies to extract significant info from knowledge by combining knowledge from completely different knowledge sources.

On this submit, we offer a step-by-step information to deliver knowledge from ServiceNow to an S3 knowledge lake utilizing AWS Glue Studio and analyze the information with Amazon Athena.

Resolution overview

On this resolution, ServiceNow knowledge is being extracted via AWS Glue utilizing a Market connector. AWS Glue offers built-in assist for essentially the most generally used knowledge shops (akin to Amazon Redshift, Amazon Aurora, Microsoft SQL Server, MySQL, MongoDB, and PostgreSQL) utilizing JDBC connections. AWS Glue additionally permits you to use customized JDBC drivers in your extract, remodel, and cargo (ETL) jobs. For knowledge shops that aren’t natively supported, akin to SaaS functions, you should use connectors and saved in Amazon S3. The information is cataloged within the AWS Glue Knowledge Catalog, and we use Athena to question the information.

AWS Glue is a serverless knowledge integration service that makes it simple to find, put together, and mix knowledge for analytics, machine studying (ML), and software growth. AWS Glue offers all of the capabilities wanted for knowledge integration so you can begin analyzing your knowledge and put it to make use of in minutes as an alternative of months.

Amazon Athena is an interactive question service that makes it simple to research knowledge in Amazon S3 utilizing customary SQL. Athena is serverless, so there isn’t a infrastructure to handle, and also you pay just for the queries that you simply run.

ServiceNow is a cloud-based software program platform for ITSM that helps to automate IT enterprise administration. It’s designed based mostly on ITIL tips to supply service orientation for duties, actions, and processes.

The next diagram illustrates our resolution structure.

To implement the answer, we full the next high-level steps:

1. Subscribe to the AWS Glue Connector Market for ServiceNow from AWS Market.
2. Create a connection in AWS Glue Studio.
3. Create an AWS Id and Entry Administration (IAM) function for AWS Glue.
4. Configure and run an AWS Glue job that makes use of the connection.
5. Run the question in opposition to the information lake (Amazon S3) utilizing Athena.

Conditions

For this walkthrough, it’s best to have the next:

• An AWS account.
• A ServiceNow account. To comply with together with this submit, you’ll be able to join a developer account, which is pre-populated with pattern data in lots of the ServiceNow objects.
• ServiceNow connection properties credentials saved in AWS Secrets and techniques Supervisor. On the Secrets and techniques Supervisor console, create a brand new secret (choose Different kind of secrets and techniques) with a key-value pair for every property, for instance:
  • Username – ServiceNow Occasion account person title (for instance, admin)
  • Password – ServiceNow Occasion account password
  • Occasion – ServiceNow occasion title with out https and .service-now.com

Copy the key title to make use of when configuring the connection in AWS Glue Studio.

Subscribe to the AWS Glue Market Connector for ServiceNow

To attach, we use the AWS Glue Market Connector for ServiceNow. You should subscribe to the connector from AWS Market.

The AWS Glue Market Connector for ServiceNow is offered by third-party impartial software program vendor (ISV) listed on AWS Market. Related subscription charges and AWS utilization charges apply as soon as subscribed.

To make use of the connector in AWS Glue, that you must activate the subscribed connector in AWS Glue Studio. The activation course of creates a connector object and connection in your AWS account.

1. On the AWS Glue console, select AWS Glue Studio.
2. Select Connectors.
3. Select Market.
4. Seek for the CData AWS Glue Connector for ServiceNow.

After you subscribe to the connector, a brand new config tab seems on the AWS Market connector web page.

5. Assessment the pricing and different related info.
6. Select Proceed to Subscribe.
7. Select Settle for Phrases.

After you subscribe to the connector, the subsequent steps are to configure it.

8. Retain the default picks for Supply Methodology and Software program Model to make use of the most recent connector software program model.
9. Select Proceed to Launch.

10. Select Utilization Directions.

A pop-up seems with a hyperlink to activate the connector with AWS Glue Studio.

11. Select this hyperlink to begin configuring the connection to your ServiceNow account in AWS Glue Studio.

Create a connection in AWS Glue Studio

Create a connection in AWS Glue Studio with the next steps:

1. For Title, enter a novel title to your ServiceNow connection.
2. For Connection credential kind, select username_password.
3. For AWS Secret, select the Secrets and techniques Supervisor secret you created as a prerequisite.

Don’t present any further particulars within the optionally available Credentials part as a result of it retrieves the worth from Secrets and techniques Supervisor.

4. Select Create connection and activate connector to complete creating the connection.

It’s best to now have the ability to view the ServiceNow connector you subscribed to and its related connection.

Create an IAM function for AWS Glue

The subsequent step is to create an IAM function with the required permissions for the AWS Glue job. The title of the function should begin with the string AWSGlueServiceRole for AWS Glue Studio to make use of it appropriately. You should grant your IAM function permissions that AWS Glue can assume when calling different companies in your behalf. For extra info, see Create an IAM Position for AWS Glue.

Connect the next AWS managed insurance policies to the function:

{
    "Model": "2012-10-17",
    "Assertion": [
        {
            "Effect": "Allow",
            "Action": [
                "secretsmanager:GetResourcePolicy",
                "secretsmanager:GetSecretValue",
                "secretsmanager:DescribeSecret",
                "secretsmanager:ListSecretVersionIds"
            ],
            "Useful resource": [
                "{secret name arn}"
            ]
        }
    ]
}

For extra details about permissions, see Assessment IAM permissions wanted for the AWS Glue Studio person.

Configure and run the AWS Glue job

After you configure your connection, you’ll be able to create and run an AWS Glue job.

Create a job that makes use of the connection

To create a job, full the next steps:

1. In AWS Glue Studio, select Connectors.
2. Choose the connection you created.
3. Select Create job.

The visible job editor seems. A brand new supply node, derived from the connection, is displayed on the job graph. Within the node particulars panel on the best, the Knowledge supply properties tab is chosen for person enter.

Configure the supply node properties

You’ll be able to configure the entry choices to your connection to the information supply on the Knowledge supply properties tab. For this submit, we offer a easy walkthrough. Discuss with the AWS Glue Studio Person Information for extra info.

1. On the Supply menu, select CData AWS Glue Connector for ServiceNow.

2. On the Knowledge supply properties – Connector tab, make sure that the supply node to your connector is chosen.

The Connection subject is populated mechanically with the title of the connection related to {the marketplace} connector.

3. Enter both a supply desk title or a question to make use of to retrieve knowledge from the information supply. For this submit, we enter the desk title incident.

4. On the Rework menu, select Apply Mapping.
5. In a Node Property Tab, Choose Node Mother and father CData AWS Glue Connector for ServiceNow.
6. As we’re connecting to an exterior knowledge supply; while you first look into Rework and Output schema tab; you gained’t discover the schema extracted from the supply.
7. So as so that you can retrieve schema, Go to Knowledge Preview tab, click on on Begin knowledge preview session and choose the IAM function you could have created for this job.
8. As soon as the Knowledge preview is finished, go to Knowledge Supply part and click on on Use datapreview schema.
9. Go to Rework and Test all of the columns the place Knowledge Kind displaying as NULL.

10. On the Goal menu, select Amazon S3.
11. On the Knowledge goal properties – S3 tab, for Format, select Parquet.
12. For Compression Kind, select GZIP.
13. For S3 Goal Location, enter the Amazon S3 location to retailer the information.
14. For Knowledge Catalog replace choices, choose Create a desk within the Knowledge Catalog and on subsequent runs, hold present schema and add new partitions.
15. For Database, enter sampledb.
16. For Desk title, enter incident.

Edit, save, and run the job

Edit the job by including and modifying the nodes within the job graph. See Modifying ETL jobs in AWS Glue Studio for extra info.

After you edit the job, enter the job properties.

1. Select the Job particulars tab above the visible graph editor.
2. For Title, enter a job title.
3. For IAM Position, select an IAM function with the required permissions, as described beforehand.
4. For Kind, select Spark.
5. For Glue model, select Glue 3.0 – Helps spark 3.1, Scala 2, Python 3.
6. For Language, select Python 3.
7. Employee kind : G.1X
8. Requested variety of staff: 2
9. Variety of retries: 1
10. Job timeout (minutes): 3
11. Use the default values for the opposite parameters.

For extra details about job parameters, see Defining Job Properties for Spark Jobs.

12. After you save the job, select Run to run the job.

Notice – Operating the Glue Job incur value. You’ll be able to be taught extra about AWS Glue Pricing right here.

To view the generated script for the job, select the Script tab on the high of the visible editor. The Job runs tab exhibits the job run historical past for the job. For extra details about job run particulars, see View info for latest job runs.

Question in opposition to the information lake utilizing Athena

After the job is full, you’ll be able to question the information in Athena.

1. On the Athena console, select the sampledb database.

You’ll be able to view the newly created desk known as incident.

2. Select the choices icon (three vertical dots) and select Preview desk to view the information.

Now let’s carry out some analyses.

3. Discover all of the incident tickets which are escalated by working the next question:

   SELECT task_effective_number FROM "sampledb"."incident" 
   the place escalation = 2;

4. Discover ticket depend with precedence:

   SELECT precedence, depend(distinct task_effective_number)  FROM "sampledb"."incident"
   group by precedence
   order by precedence asc

Conclusion

On this submit, we demonstrated how you should use an AWS Glue Studio connector to attach from ServiceNow and convey knowledge into your knowledge lake for additional use instances.

AWS Glue offers built-in assist for essentially the most generally used knowledge shops (akin to Amazon Redshift, Amazon Aurora, Microsoft SQL Server, MySQL, MongoDB, and PostgreSQL) utilizing JDBC connections. AWS Glue additionally permits you to use customized JDBC drivers in your extract, remodel, and cargo (ETL) jobs. For knowledge shops that aren’t natively supported, akin to SaaS functions, you should use connectors.

To be taught extra, discuss with the AWS Glue Studio Connector,  AWS Glue Studio Person Information and Athena Person Information.

In regards to the Authors

Navnit Shukla is AWS Specialist Resolution Architect in Analytics. He’s enthusiastic about serving to prospects uncover insights from their knowledge. He builds options to assist organizations make data-driven choices.

Srikanth Sopirala is a Principal Options Architect at AWS. He’s a seasoned chief with over 20 years of expertise, who’s enthusiastic about serving to prospects construct scalable knowledge and analytic options to realize well timed insights and make essential enterprise choices. In his spare time, he enjoys studying, spending time along with his household, and street biking.

Naresh Gautam is a Principal Options Architect at AWS. His function helps prospects architect extremely accessible, high-performance, and cost-effective knowledge analytics options to empower prospects with data-driven decision-making. In his free time, he enjoys meditation and cooking.