Model sync from MySQL to Snowflake

Before getting started make sure to set your Polytomic API key as an environment variable:

$export POLYTOMIC_API_KEY=YOUR-API-TOKEN

This example tutorial will cover three steps:

  1. Connecting to MySQL.
  2. Creating a model.
  3. Creating a sync to Snowflake.

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 page in our documentation.

$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 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}/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

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: 2024-02-08" \
>     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}" \
>     -d '{
>           "name": "Customer Model", 
>           "configuration": {
>              "table": "company.customers"
>              },
>           "connection_id": "YOUR-CONNECTION-ID"
>    }'

Important: 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: 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 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/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}

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/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}

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/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

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/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"}'

This response will show the available syncs modes as well as the fields and their associated 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}

Setup sync mapping

Finally, we can set up a mapping of fields from the model we created above and the target as seen below:

$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"
>    }
>}'