Prerequisites

This guide makes use of cURL, a command line program for making HTTP requests. MacOS and many Linux distributions have cURL pre-installed, and it is also available for download on Windows and many other operating systems

Setup

Generate an API key in the your user settings panel For more information, see the authentication section

Making API requests

Set the API token as an environment variable

export POLYTOMIC_API_KEY=YOUR-API-TOKEN

Replace YOUR-API-TOKEN with the token you just generate in the step above.

Make a request to the identity endpoint

curl --request GET \
     --url https://app.polytomic.com/api/me \
     --header "accept: application/json" \
     --header "X-Polytomic-Version: 2022-12-12" \
     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}"

1. Create a connection

The following request will create a MySQL connection. The configuration for each type of connection can be found at the connection configuration docs page.

curl --request POST \
     --url https://app.polytomic.com/api/connections \
     --header "accept: application/json" \
     --header "content-type: application/json" \
     --header "X-Polytomic-Version: 2022-12-12" \
     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}" \
     -d '{
           "name": "MySQL Connection",
           "type": "mysql",
           "configuration": {
              "hostname": "localhost",
              "port": 5432,
              "database": "company",
              "username": "user",
              "password": "secret"
          }
		}'

📘

OAuth Connections

OAuth connections require completing the connection authentication process in the browser. An example of creating an OAuth connection can be found in the recipes section of the docs.

2. Create a model

Next we'll create a model of a "customers" table using the MySQL connection we just created.

Connections can be enumerated using the source endpoint. This next call will show us all available tables on the connection we just created:

curl --request GET \
    --url https://app.polytomic.com/api/connections/{YOUR-CONNECTION-ID}/source \
    --header "content-type: application/json" \
    --header "X-Polytomic-Version: 2022-12-12" \
    --header "Authorization: Bearer ${POLYTOMIC_API_KEY}"

The response will look like:

{
  "data": {
    "items": {
      "project_id": {
        "items": null,
        "requires_one_of": null,
        "has_items": false
      },
      "query": {
        "items": null,
        "requires_one_of": null,
        "has_items": false
      },
      "table": {
        "items": [
          "company.accounts",
          "company.customers",
          "company.teams"
        ],
        "requires_one_of": null,
        "has_items": true
      },
      "tracking_columns": {
        "items": null,
        "requires_one_of": null,
        "has_items": false
      },
      "view": {
        "items": null,
        "requires_one_of": null,
        "has_items": false
      }
    },
    "requires_one_of": [
      "query",
      "table",
      "view"
    ]
  }
}

📘

Sources

Each source level can be drilled down by using query parameters. e.g. ?table=company.accounts. In this case; however, tables requires_one_of property is null so we don't need to drill down.

Finally, we can create a model using the company.customers table like we mentioned above.

curl --request POST \
     --url https://app.polytomic.com/api/models \
     --header "accept: application/json" \
     --header "content-type: application/json" \
     --header "X-Polytomic-Version: 2022-12-12" \
     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}" \
     -d '{
           "name": "Customer Model", 
           "configuration": {
              "table": "company.customers"
              },
           "connection_id": "YOUR-CONNECTION-ID"
    }'

Make sure to replace YOUR-CONNECTION-ID with the returned connection ID.

3. Create a sync

Next we'll create a sync using the customer model we created above. We'll also create an additional connection that we will use as the target.

Create a target

Here, we'll create a Snowflake connection as our target.

curl --request POST \
     --url https://app.polytomic.com/api/connections \
     --header "accept: application/json" \
     --header "content-type: application/json" \
     --header "X-Polytomic-Version: 2022-12-12" \
     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}" \
     -d '{
     "name": "Snowflake Connection",
     "type": "snowflake",
     "configuration": {
        "account": "account",
        "dbname": "database",
        "password": "secret-password",
        "username": "user"
      }
    }'

Enumerate target

In the same way sources can be enumerated, so can targets.

curl --request GET \
     --url https://app.polytomic.com/api/connections/YOUR-CONNECTION-ID/target \
     --header "accept: application/json" \
     --header "content-type: application/json" \
     --header "X-Polytomic-Version: 2022-12-12" \
     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}"

The response will look like:

{
  "data": {
    "items": {
      "schema": {
        "items": null,
        "requires_one_of": [
          "table",
          "view"
        ],
        "has_items": false
      },
      "table": {
        "items": null,
        "requires_one_of": null,
        "has_items": false
      },
      "view": {
        "items": null,
        "requires_one_of": null,
        "has_items": false
      }
    },
    "requires_one_of": [
      "schema"
    ]
  }
}

Next, we can drill down using the requires_one_of field recursively. For example,

curl --request GET \
     --url https://app.polytomic.com/api/connections/YOUR-CONNECTION-ID/target?type=schema \
     --header "accept: application/json" \
     --header "content-type: application/json" \
     --header "X-Polytomic-Version: 2022-12-12" \
     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}"

The response will look like:

{
  "data": {
    "items": {
      "schema": {
        "items": [
          "__pt_new_schema",
          "CUSTOMERS",
          "TEAMS"
        ],
        "requires_one_of": [
          "table"
        ],
        "has_items": true
      },
      "table": {
        "items": null,
        "requires_one_of": null,
        "has_items": false
      }
    },
    "requires_one_of": [
      "schema"
    ]
  }
}

Notice, the schema also requires a table. We can enumerate those by adding to the query parameters.

curl --request GET \
     --url https://app.polytomic.com/api/connections/YOUR-CONNECTION-ID/target?type=table&search=CUSTOMERS \
     --header "accept: application/json" \
     --header "content-type: application/json" \
     --header "X-Polytomic-Version: 2022-12-12" \
     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}"

The response will look like:

{
  "data": {
    "items": {
      "schema": {
        "items": null,
        "requires_one_of": [
          "table"
        ],
        "has_items": false
      },
      "table": {
        "items": [
          "__pt_new_target",
          "CUSTOMERS.COMPANIES",
          "CUSTOMERS.CONTACTS"
        ],
        "requires_one_of": null,
        "has_items": true
      }
    },
    "requires_one_of": [
      "schema"
    ]
  }
}

Query target fields

The last step in enumerating a target for a sync is querying the available fields. To do this, we can POST a fields query on the target resource.

curl --request POST \
     --url https://app.polytomic.com/api/connections/YOUR-CONNECTION-ID/target/fields \
     --header 'X-Polytomic-Version: 2022-12-12' \
     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}" \
     --header 'content-type: application/json' \
     -d '{"target": "CUSTOMERS.CONTACTS"}'

This response will show the available syncs modes as well as the fields and their associated metadata.

{
	"data": {
		"id": "CUSTOMERS.CONTACTS",
		"name": "CUSTOMERS.CONTACTS",
		"modes": [{
				"mode": "create",
				"description": "Create records when they don’t exist; don’t update existing ones",
				"label": "Create",
				"requires_identity": true,
				"supports_target_filters": false,
				"supports_field_sync_mode": false
			},
			{
				"mode": "update",
				"description": "Update existing records only; don’t create new ones",
				"label": "Update",
				"requires_identity": true,
				"supports_target_filters": false,
				"supports_field_sync_mode": false
			},
			{
				"mode": "updateOrCreate",
				"description": "Update records when they exist and create them when they don’t",
				"label": "Update or Create",
				"requires_identity": true,
				"supports_target_filters": false,
				"supports_field_sync_mode": false
			},
			{
				"mode": "replace",
				"description": "Replace all existing rows",
				"label": "Replace",
				"requires_identity": false,
				"supports_target_filters": false,
				"supports_field_sync_mode": false
			},
			{
				"mode": "append",
				"description": "Append rows to the end of the table",
				"label": "Append",
				"requires_identity": false,
				"supports_target_filters": false,
				"supports_field_sync_mode": false
			}
		],
		"properties": {
			"supports_field_creation": true
		},
		"refreshed_at": "0001-01-01T00:00:00Z",
		"fields": [{
        "id": "EMAIL",
        "name": "EMAIL",
        "description": "",
        "required": false,
        "filterable": false,
        "createable": true,
        "updateable": true,
        "association": false,
        "supports_identity": true,
        "identity_functions": [
          {
            "id": "Equality",
            "label": "Equality"
          }
        ],
        "source_type": "VARCHAR(16777216)",
        "type": "string"
      },
      {
        "id": "FIRST_NAME",
        "name": "FIRST_NAME",
        "description": "",
        "required": false,
        "filterable": false,
        "createable": true,
        "updateable": true,
        "association": false,
        "supports_identity": true,
        "identity_functions": [
          {
            "id": "Equality",
            "label": "Equality"
          }
        ],
        "source_type": "VARCHAR(16777216)",
        "type": "string"
      },
      {
        "id": "LAST_NAME",
        "name": "LAST_NAME",
        "description": "",
        "required": false,
        "filterable": false,
        "createable": true,
        "updateable": true,
        "association": false,
        "supports_identity": true,
        "identity_functions": [
          {
            "id": "Equality",
            "label": "Equality"
          }
        ],
        "source_type": "VARCHAR(16777216)",
        "type": "string"
      }
		]
	}
}

Setup sync mapping

Finally, we can set up a mapping of fields from the model we created above and the target.

curl --request POST \
     --url https://app.polytomic.com/api/syncs \
     --header 'X-Polytomic-Version: 2022-12-12' \
     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}" \
     --header 'content-type: application/json' \
     --data '{
     "name": "MySQL to Snowflake Sync",
     "mode": "replace",
     "fields": [
        {
            "source": {
                "field": "email",
                "model_id": "YOUR-MODEL-ID"
            },
            "target": "EMAIL"
        },
        {
            "source": {
                "field": "first_name",
                "model_id": "YOUR-MODEL-ID"
            },
            "target": "FIRST_NAME"
        },
        {
            "source": {
                "field": "last_name",
                "model_id": "YOUR-MODEL-ID"
            },
            "target": "LAST_NAME"
        }
    ],
    "schedule": {
        "frequency": "manual"
    },
    "target": {
        "connection_id": "YOUR-TARGET-CONNECTION-ID",
        "object": "CUSTOMERS.CONTACTS"
    }
}'