How to load data from Enchant to Google BigQuery

Access your data on Enchant

The first step in loading your Enchant data to any kind of data warehouse solution is to access them and start extracting it.

Enchant offers a REST API built on pragmatic RESTful design principles that you can use to programmatically interact with your account.

From the available endpoints you can retrieve the following information:

1. Tickets: All user requests are tracked as tickets. Tickets contain one or more messages
2. Messages: Messages include the replies and notes associated with the tickets
3. Attachments: Attachments are associated with messages. After uploading an attachment, a message must be created using the attachment id. An attachment can be associated with only one message.
4. Users: Details about your help desk operators.
5. Customers: Details about the customers associated with at least one ticket.
6. Contacts: Email addresses and Twitter accounts are represented as contacts on a customer.

In addition to the above, the things that you have to keep in mind when dealing with the Enchant API, are:

1. Rate limits. The API is rate limited to 100 credits per minute for an entire help desk, across all endpoints, users, and tokens. A request is typically worth 1 credit.
2. Authentication. Requests to the Enchant API are authenticated using access tokens.
3. Pagination. Requests for collections can return between 0 and 100 results. All endpoints are limited to 10 results by default. However, not all endpoints support pagination.

Transform and prepare your Enchant Data for Google BigQuery

After you have accessed your data on Enchant, you will have to transform it based on two main factors,

1. The limitations of the database that the data will be loaded onto
2. The type of analysis that you plan to perform

Each system has specific limitations on the data types and data structures that it supports. If for example, you want to push data into Google BigQuery, then you can send nested data like JSON directly.

Of course, when you are dealing with tabular data stores, like Microsoft SQL Server, this is not an option. Instead, you will have to flatten out every data, just as in the case of JSON, before loading it into the database.

Also, you have to choose the right data types. Again, depending on the system that you will send the data to and the data types that the API exposes to you, you will have to make the right choices. These choices are important because they can limit the expressivity of your queries and limit your analysts on what they can do directly out of the database. Enchant has a very limited set of available data types which means that your work to do these mappings is much easier and straightforward, but nonetheless equally important with any other case of a data source.

Load data from Enchant to Google BigQuery

If you want to load Enchant data to Google BigQuery, you have to use one of the following supported data sources.

1. Google Cloud Storage
2. Sent data directly to BigQuery with a POST request
3. Google Cloud Datastore Backup
4. Streaming insert
5. App Engine log files
6. Cloud Storage logs

From the above list of sources, 5 and 6 are not applicable in our case.

For Google Cloud Storage, you first have to load some data into it, there are a few options on how to do this, for example, you can use the console directly as it is described here and do not forget to follow the best practices.

Another option is to post data through the JSON API, as we see again APIs play an important role in both the extraction but also the loading of data into our data warehouse. In its simplest case, it’s just a matter of one HTTP POST request using a tool like CURL or Postman.

After you have loaded any data you need into Google Cloud Storage, you have to create a Load Job for BigQuery to actually load the data into it, this Job should point to the source data in Cloud Storage that have to be imported, this happens by providing source URIs that point to the appropriate objects.

The best way of loading data from Enchant to BigQuery

So far we just scraped the surface of what you can do with BigQuery and how loading data into it can be done. Things can get even more complicated if you want to integrate data coming from different sources.

Are you striving to achieve results right now?

Instead of writing, hosting, and maintaining a flexible data infrastructure use RudderStack that can handle everything automatically for you.

RudderStack with one click integrates with sources or services, creates analytics-ready data, and syncs your Enchant to BigQuery right away.