Stitch Documentation
has moved!

Please update your bookmarks to https://www.stitchdata.com/docs

If you're not automatically redirected after 5 seconds, click here.

Import API Methods

The Import API supports three methods: Status, Upsert, and Validate.

Status

A simple endpoint that indicates whether the Import API is operating correctly. This endpoint does not require any authentication.

   Example Request

curl -i https://api.stitchdata.com/v2/import/status

200 OK - The Import API is operating correctly.
503 Service Unavailable - The Import API is experiencing problems. Do not attempt to post any data.

   Example Response

HTTP/1.1 200 OK
Content-Type: application/json;charset=UTF-8
Date: Thu, 07 Jan 2016 13:13:37 GMT
Server: http-kit
Vary: Accept
Content-Length: 53
Connection: keep-alive

{"name":"pipeline.gate","status":"OK"}

Upsert

The upsert method allows you to push data into your data warehouse. This endpoint will only accept data that have the following properties:

  1. The data must be valid JSON or Transit. This must also be specified in the request header:

    Content-Type: application/json
  2. Each data point must contain a key_names field. The key_names field should specify an array of fields in the records that represent the primary key(s).
  3. If a primary key is a string, it must be 256 characters or less.
  4. Each data point must contain a sequence field.
  5. An array of data must contain no more than 10,000 individual data points.
  6. The request body cannot be larger than 4MB.

Required Request Body Values

Your request body must contain the following values:

  • client_id - your client ID
  • table_name - name of the destination table
  • sequence - property that communicates the order in which data points are considered See the Sequence section for more info on how to define this property.
  • action - “upsert”
  • key_names - array of field names that uniquely identifies the row that this record belongs to
  • data - contains data to be upserted into the database

   Example Request Header

curl -X POST https://api.stitchdata.com/v2/import/push \
	-H 'Content-Type: application/json' \
	-H 'Authorization: Bearer < access-token >' \
	-d @filename

   Example Request Body

[
 {
  "client_id": 4231,
  "table_name": "users",
  "sequence": 10000,
  "action": "upsert",
  "key_names": [
   "id"
  ],
  "data": {
   "id": 10,
   "email": "john@smith.com",
   "name": "John Smith"
  }
 },
 {
  "client_id": 4231,
  "table_name": "dogs",
  "sequence": 300,
  "action": "upsert",
  "key_names": [
   "dog_id"
  ],
  "data": {
   "dog_id": 40,
   "name": "Brody",
   "breed": "corgi"
  }
 }
]

If the request was successful, the response will have an http status code of 201 Created and an empty body. If a 201 is not received, assume the request has failed.

Table Management

The Import API does not enforce any limitation on hierarchy of your tables. Pushing data to an arbitrary table name generates the table dynamically. There aren’t any commands to explicitly create or destroy a table. Ultimately, you are responsible for structuring your data.

Some guidelines for managing tables:

  1. Create one table for each type of data point you are pushing.

    For example, a typical eCommerce company might have tables for customer, order, order_item, and product data.
  2. Each data point pushed into a single table should have the same structure.

    For example: if a customers table contains customer_id, name, and email columns, each customer record pushed into this table should contain those columns. 
  3. You can push more than one table using the same API access token. Think of it this way: one schema for each API access token. All tables pushed using the same API access token will be housed in the same schema in your data warehouse.

Sequence

Every data point pushed to the Import API must have a sequence property. Sequence properties communicate the order in which data points should be considered – newer data points can replace older ones, but not vice versa.

A simple solution is just to use the current timestamp, but before doing so, consider the following:

Are the rows being considered frequently updated?
Rows that are updated every few milliseconds can result in failure if records with identical key values are pushed simultaneously. This means that records with the same key values cannot be sent during the same clock resolution.

For example: if the resolution is measured in milliseconds, records with identical key values cannot be sent during the same millisecond.

Are the records coming from multiple sources?
If records from multiple sources will be sent to the Import API, the time clocks of these sources must be synchronized. This is especially important if different sources are pushing rows to the same table.

Take a look at the following examples. If the requests were received in this order, Request 3 would not continue to Stitch because it has a lower sequence value than Request 2. The others would.

   Request 1

[
 {
  "client_id": 4231,
  "table_name": "users",
  "sequence": 100,
  "action": "upsert",
  "key_names": [
   "id"
  ],
  "data": {
   "id": 10,
   "status": "pending"
  }
 }
]

   Request 2

[
 {
  "client_id": 4231,
  "table_name": "users",
  "sequence": 101,
  "action": "upsert",
  "key_names": [
   "id"
  ],
  "data": {
   "id": 10,
   "status": "canceled"
  }
 }
]

   Request 3

[
 {
  "client_id": 4231,
   "table_name": "users",
   "sequence": 99,
   "action": "upsert",
   "key_names": [
    "id"
   ],
   "data": {
    "id": 10,
    "status": "new"
   }
  }
 ]

   Request 4

[
 {
  "client_id": 4231,
  "table_name": "users",
  "sequence": 90,
  "action": "upsert",
  "key_names": [
   "id"
  ],
  "data": {
   "id": 22, // note the id
   "status": "new"
  }
 }
]

Validate

This endpoint will validate requests but not persist them to Stitch, which can be useful for development and testing purposes. The behavior of this endpoint mirrors that of the upsert endpoint, with two exceptions:

  • If the request is valid, a 200 OK response will be returned.
  • Regardless of whether Stitch is functional, a 503 Service Unavailable response will never be returned.

   Example Request

curl -X POST https://api.stitchdata.com/v2/import/validate \
	-H 'Content-Type: application/json' \
	-H 'Authorization: Bearer < access-token >' \
	-\d @filename

[
   {
      "client_id": 4231,
      "table_name": "users",
      "sequence": 100,
      "action": "upsert"
   }
]

RELATED

Was this article helpful?
0 out of 0 found this helpful

Comments

Questions or suggestions? If something in our documentation is unclear, let us know in the comments!