How to Upload Zendesk Data to Redshift | simple instructions (2023)

This post will help you sync your Zendesk data with Amazon Redshift. By doing so, you will be able to perform advanced analytics on a system designed for this type of data, such as Amazon Redshift. Alternatively, you can simplify the process of syncing data from Zendesk to Amazon Redshift usingrudder stack, where RudderStack will take care of the whole process and you can focus on what matters, analyzing your data.

Pull your data from Zendesk

Zendesk APIs are not specific to data extraction, Zendesk provides over a hundred different APIs for you to integrate with. So you can easily manage your users, increase your team's productivity, and create seamless integrations. You can create integrations or even enrich Zendesk with data from external sources. The Zendesk API is a RESTful API that can be accessed over HTTP.

As a RESTful API, interaction with it can be accomplished using tools like CURL or Postman or by using HTTP clients for your favorite language or framework.

Some suggestions:

• Apache HttpClient para Java
• Client spray for Scala
• hyper for rust
• Ruby rest client
• Python http-cliente

In addition, Zendesk provides several SDKs and libraries so that you can access the API of the framework of your choice without having to deal with the technicalities of HTTP. API clients are available for the following languages:

• ruby
• Piton
• PHP
• Java
• .LIQUID
• Node.js
• closure
• Force.com
• R

Zendesk API authentication

The Zendesk API is an SSL-only API, regardless of how your account is configured. you must be averified userto make API requests. You can authorize in the API using basic authentication with your email address and password, with your email address and an API token, or with an OAuth access token.

Zendesk rate limiting

The API is rate limited. It only allows a certain number of requests per minute, depending on your plan and endpoint. Zendesk reserves the right to adjust the rate cap for certain endpoints in order to provide high-quality service to all customers. The current limits are as follows:

Pagination

By default, most list endpoints return a maximum of 100 records per page. You can change the number of records per request by passing a per_page parameter in the request URL parameters. Example: per_page=50. However, it cannot exceed 100 records per page on most endpoints.

When the response exceeds the maximum per page, you can loop through the records by incrementing the page parameter. Example: page=3. List results include the URLs of the next page and the previous page in the response body for easy navigation:

Endpoints and available resources

The Zendesk REST API exposes a large number of functions and endpoints that allow the user to interact with the platform in all possible ways. Thus, it is possible to create new applications on the Zendesk platform, integrate external systems to it and, of course, extract data from the platform. The most important characteristics are the following:

• Tickets that your customers create through Zendesk.
• Ticket events. Changes in tickets.
• Organizations.
• Users.
• Input metrics. These are metrics related to your tickets.
• Data related to the Net Promoter Score.
• Articles

Let's say we want to get all the tickets we have in Zendesk. To do this, we need to make a GET request to the appropriate endpoint, like this:

JAVASCRIPT
TO TAKE /API/v2/incremental/Appetizer.json?start time=1332034771

JAVASCRIPT
See Also
Zendesk to Redshift: 2 easy steps to move data
rizo https://{subdomain}.zendesk.com/API/v2/incremental/Appetizer.json?start time=1332034771\
 -v-you{Email address}:{password}

And an example response:

JAVASCRIPT
state: 200 OK
{
 "End of time": 1383685952,
 "next page": "https://{subdominio}.zendesk.com/api/v2/incremental/tickets.json?start_time=1383685952",
 "say": 1,
 "Appetizer": [
 {
 "Url address": "https://{subdominio}.zendesk.com/api/v2/tickets/1.json",
 "I was going": 2,
 "created in": "2012-02-02T04:31:29Z",
 "generated_timestamp": 1390362285
 ...
 },
 ...
 ]
}

A complete ticket object can contain the following fields:

Zendesk API results are always in JSON format. The API gives you the opportunity to get very granular data about your accounting activities and use it for analysis and reporting purposes.

Prepare your Zendesk data for Amazon Redshift

Amazon Redshift is based on industry-standard SQL with additional functionality to handle very large data sets and perform high-performance analytics. So to load your data, you'll need to follow your data model, which is a typical relational database model. The data you pull from your data source must be mapped to tables and columns. Where you can think of the table as a map of the resource you want to store and the columns the attributes of that resource. Additionally, each attribute must comply with the data types supported by Redshift, currently the supported data types are as follows:

• LITTLE
• WHOLE
• STARTING
• DECIMAL
• REAL
• DOUBLE PRECISION
• BOLEANO
• CHARACTERS
• VARCHAR
• DATA
• TIME STAMP

Since your data probably comes in a JSON-like representation that supports a much smaller range of data types, you should be very careful about the data you feed Redshift and make sure you map your types to one of thedata types that are supportedby redshift. Designing a schema for Redshift and assigning data from your data source to it is a process that should be taken seriously, as it can affect the performance of your cluster and the questions it can answer. It is always good to keep in mind theBetter practicesthat Amazon posted about designing a Redshift database. When you have finished designing your database, you need to load your data into one of the data sources supported as input by Redshift, which are the following:

• Amazonas S3
• Amazon DynamoDB
• Amazon Kinesis Firehose

Upload data from Zendesk to Redshift

The first step in loading your Zendesk data into Redshift is to put it in a source that Redshift can pull from. As mentioned above, there are three main supported data sources, Amazon S3, Amazon DynamoDB, and Amazon Kinesis Firehose, with Firehose being the most recent addition as a way to get data into Redshift.

To upload your data to Amazon S3, you'll need to use the AWS REST API, as we see again that APIs play an important role in both extracting and loading data into our data warehouse. The first task you should perform is to create abucket, it does this by performing an HTTP PUT to the Amazon AWS REST API endpoints for S3. You can do this using a tool like CURL or Postman. Or use thelibrariesprovided by Amazon for your favorite language. You can find more information by reading theAPI Referencefor bucket operations in the Amazon AWS documentation.

Once you've created your bucket, you can start sending your data to Amazon S3, again using the same AWS REST API but using endpoints for object operations. As with Bucket, you can access HTTP endpoints directly or use your preferred library.

DynamoDB imports data back from S3, adds another step between S3 and Amazon Redshift, so if you don't need it for other reasons, you can skip it.

Amazon Kinesis Firehose is the latest addition as a way to get data into Redshift and offers a streaming approach to importing data. The steps required to add data to Redshift through Kinesis Firehose are as follows:

1. create a delivery flow
2. add data to stream

Every time you add new data to the stream, Kinesis takes care of adding that data to S3 or Redshift, going through S3 again, in which case it's redundant if your goal is to move your data to Redshift. The execution of the previous two steps can be done through the REST API or through your favorite library, just like in the previous two cases.

The difference here is that to send your data to the stream you will use aKinesis Agent.

Amazon Redshift supports two methods for loading data into it. The first is by calling an INSERT statement. You can connect to your Amazon Redshift instance with your client using a JDBC or ODBC connection, and then run an INSERT command on your data.

JAVASCRIPT
insert in scenario_categoryvalues
(12, 'Concerts', 'Comedy', 'All stand-up comedy performances');

The way to call the INSERT command is the same as you would with any other SQL database, for more information you can refer to theINSERT example pagein the Amazon Redshift documentation.

Redshift is not designed for INSERT operations, rather the most efficient way to load data into it is to bulk load it using a COPY command. You can run a COPY command on data that lives as flat files inS3one by oneAmazon DynamoDBtable. When you run COPY commands, Redshift is capable of reading multiple files simultaneously and automatically distributes the workload to the cluster nodes and runs the load in parallel. as a commandCOPY OFit's quite flexible and allows for many different ways of using it depending on your use case. Making a COPY to Amazon S3 is as simple as the following command:

JAVASCRIPT
copy list
of 's3://mybucket/data/listing/'
credentials'aws_access_key_id=;aws_secret_access_key=';

For more examples on how to invoke a COPY command, you can refer to theCOPY examplespage in the Amazon Redshift documentation. As in the INSERT case, the way to run the COPY command is by connecting to your Amazon Redshift instance using a JDBC or ODBC connection and then calling the desired commands using theSQL referencefrom the Amazon Redshift documentation.

The best way to load Zendesk data into Amazon Redshift and possible alternatives