Code examples

Model sync from MySQL to Snowflake

Set your Polytomic API key as an environment variable:

$export POLYTOMIC_API_KEY=YOUR-API-TOKEN

This example covers three steps:

  1. Create a MySQL Connection.
  2. Create a model over a MySQL table.
  3. Create a sync to Snowflake.

1. Create a MySQL connection

The following request creates a MySQL Connection. See the connection configuration reference for the required fields.

$curl --request POST \
>     --url https://app.polytomic.com/api/connections \
>     --header "accept: application/json" \
>     --header "content-type: application/json" \
>     --header "X-Polytomic-Version: 2024-02-08" \
>     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}" \
>     -d '{
>           "name": "MySQL Connection",
>           "type": "mysql",
>           "configuration": {
>              "hostname": "localhost",
>              "port": 3306,
>              "dbname": "company",
>              "account": "user",
>              "passwd": "secret"
>          }
>		}'

2. Create a model

Next, create a model over the customers table on the MySQL Connection.

First, use the source endpoint to list the tables available on the Connection:

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

The response will look like:

1{
2    "data": {
3        "items": {
4            "query": {
5                "items": null,
6                "requires_one_of": null,
7                "has_items": false
8            },
9            "table": {
10                "items": [
11                   "company.accounts",
12                   "company.customers",
13                   "company.teams"
14                ],
15                "requires_one_of": null,
16                "has_items": true
17            },
18            "tracking_columns": {
19                "items": null,
20                "requires_one_of": null,
21                "has_items": false
22            },
23            "view": {
24                "items": null,
25                "requires_one_of": null,
26                "has_items": false
27            }
28        },
29        "requires_one_of": [
30            "query",
31            "table",
32            "view"
33        ]
34    }
35}

Sources

Drill into each level with query parameters — for example, ?table=company.accounts. In this case, requires_one_of on table is null, so no further drill-down is needed.

Now create a model over company.customers:

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

Replace YOUR-CONNECTION-ID with the Connection ID returned in Step 1.

3. Create a sync

Next, create a sync from the customer model to a new Snowflake target.

Create a target Connection

Create a Snowflake Connection to use as the sync target:

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

Enumerate the target

Targets enumerate the same way sources do:

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

The response will look like:

1{
2  "data": {
3    "items": {
4      "schema": {
5        "items": null,
6        "requires_one_of": [
7          "table",
8          "view"
9        ],
10        "has_items": false
11      },
12      "table": {
13        "items": null,
14        "requires_one_of": null,
15        "has_items": false
16      },
17      "view": {
18        "items": null,
19        "requires_one_of": null,
20        "has_items": false
21      }
22    },
23    "requires_one_of": [
24      "schema"
25    ]
26  }
27}

Drill down recursively using the requires_one_of field. For example:

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

The response will look like:

1{
2  "data": {
3    "items": {
4      "schema": {
5        "items": [
6          "__pt_new_schema",
7          "CUSTOMERS",
8          "TEAMS"
9        ],
10        "requires_one_of": [
11          "table"
12        ],
13        "has_items": true
14      },
15      "table": {
16        "items": null,
17        "requires_one_of": null,
18        "has_items": false
19      }
20    },
21    "requires_one_of": [
22      "schema"
23    ]
24  }
25}

The schema level requires a table. Enumerate tables by adding another query parameter:

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

The response will look like:

1{
2  "data": {
3    "items": {
4      "schema": {
5        "items": null,
6        "requires_one_of": [
7          "table"
8        ],
9        "has_items": false
10      },
11      "table": {
12        "items": [
13          "__pt_new_target",
14          "CUSTOMERS.COMPANIES",
15          "CUSTOMERS.CONTACTS"
16        ],
17        "requires_one_of": null,
18        "has_items": true
19      }
20    },
21    "requires_one_of": [
22      "schema"
23    ]
24  }
25}

Query target fields

Finally, query the available fields on the target. POST to the fields endpoint for the target resource:

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

The response lists the available sync modes and every field on the target along with its metadata. For example:

1{
2	"data": {
3		"id": "CUSTOMERS.CONTACTS",
4		"name": "CUSTOMERS.CONTACTS",
5		"modes": [{
6				"mode": "create",
7				"description": "Create records when they don’t exist; don’t update existing ones",
8				"label": "Create",
9				"requires_identity": true,
10				"supports_target_filters": false,
11				"supports_field_sync_mode": false
12			},
13			{
14				"mode": "update",
15				"description": "Update existing records only; don’t create new ones",
16				"label": "Update",
17				"requires_identity": true,
18				"supports_target_filters": false,
19				"supports_field_sync_mode": false
20			},
21			{
22				"mode": "updateOrCreate",
23				"description": "Update records when they exist and create them when they don’t",
24				"label": "Update or Create",
25				"requires_identity": true,
26				"supports_target_filters": false,
27				"supports_field_sync_mode": false
28			},
29			{
30				"mode": "replace",
31				"description": "Replace all existing rows",
32				"label": "Replace",
33				"requires_identity": false,
34				"supports_target_filters": false,
35				"supports_field_sync_mode": false
36			},
37			{
38				"mode": "append",
39				"description": "Append rows to the end of the table",
40				"label": "Append",
41				"requires_identity": false,
42				"supports_target_filters": false,
43				"supports_field_sync_mode": false
44			}
45		],
46		"properties": {
47			"supports_field_creation": true
48		},
49		"refreshed_at": "0001-01-01T00:00:00Z",
50		"fields": [{
51        "id": "EMAIL",
52        "name": "EMAIL",
53        "description": "",
54        "required": false,
55        "filterable": false,
56        "createable": true,
57        "updateable": true,
58        "association": false,
59        "supports_identity": true,
60        "identity_functions": [
61          {
62            "id": "Equality",
63            "label": "Equality"
64          }
65        ],
66        "source_type": "VARCHAR(16777216)",
67        "type": "string"
68      },
69      {
70        "id": "FIRST_NAME",
71        "name": "FIRST_NAME",
72        "description": "",
73        "required": false,
74        "filterable": false,
75        "createable": true,
76        "updateable": true,
77        "association": false,
78        "supports_identity": true,
79        "identity_functions": [
80          {
81            "id": "Equality",
82            "label": "Equality"
83          }
84        ],
85        "source_type": "VARCHAR(16777216)",
86        "type": "string"
87      },
88      {
89        "id": "LAST_NAME",
90        "name": "LAST_NAME",
91        "description": "",
92        "required": false,
93        "filterable": false,
94        "createable": true,
95        "updateable": true,
96        "association": false,
97        "supports_identity": true,
98        "identity_functions": [
99          {
100            "id": "Equality",
101            "label": "Equality"
102          }
103        ],
104        "source_type": "VARCHAR(16777216)",
105        "type": "string"
106      }
107		]
108	}
109}

Create the sync

Map fields from the source model to the target and create the sync:

$curl --request POST \
>     --url https://app.polytomic.com/api/syncs \
>     --header 'X-Polytomic-Version: 2024-02-08' \
>     --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-SOURCE-MODEL-ID"
>            },
>            "target": "EMAIL"
>        },
>        {
>            "source": {
>                "field": "first_name",
>                "model_id": "YOUR-SOURCE-MODEL-ID"
>            },
>            "target": "FIRST_NAME"
>        },
>        {
>            "source": {
>                "field": "last_name",
>                "model_id": "YOUR-SOURCE-MODEL-ID"
>            },
>            "target": "LAST_NAME"
>        }
>    ],
>    "schedule": {
>        "frequency": "continuous"
>    },
>    "target": {
>        "connection_id": "YOUR-TARGET-CONNECTION-ID",
>        "object": "CUSTOMERS.CONTACTS"
>    }
>}'