MySQL
Preparation
To allow Shaped to connect to your MySQL database, you need to create a read-only user and share those credentials through the Create Dataset endpoint. You can create a read-only user on a MySQL database with the following commands:
# 1. Create a new user.
CREATE USER '[username]'@'%' IDENTIFIED BY '[password]';
# 2. Grant the user read access to all the tables in the schema. Note you can also
# restrict this to your specific user, item and interaction views.
GRANT SELECT ON [database_name].* TO '[username]'@'%';
# 3. Save.
FLUSH PRIVILEGES;
Dataset Configuration
Required fields
| Field | Example | Description |
|---|---|---|
| schema_type | MYSQL | Specifies the connector schema type, in this case "MYSQL". |
| table | movies | The name of the table to sync. |
| user | your_user | Access account username. |
| password | pAssw0rd1! | Access account Password. |
| host | my-mysql-db.xxxxxxx.us-east-2.rds.amazonaws.com | Database hostname. |
| port | 3306 | Database port (the default for MySQL is 3306). |
| database | movielens | The name of the database that contains table to sync. |
| replication_key | updated_at | The name of the column that contains a datetime key or ascending id for ordering data during incremental syncs. |
Optional fields
| Field | Example | Description |
|---|---|---|
| columns | ["productId", "color", "brand", "stockLevel"] | The name of the columns you wish to sync from MySQL into Shaped. If not specified, all columns will be synced. |
| unique_keys | ["productId"] | Specify a list of columns that uniquely identify a row in the table, if duplicate rows are inserted with these keys, the latest row will be used. |
| batch_size | 100000 | The number of rows to fetch from the database in each batch, changing this can improve throughput for large tables. The default is 10000. |
Dataset Creation Example
Below is an example of a MySQL dataset connector configuration:
name: your_mysql_dataset
schema_type: MYSQL
table: movies
user: your_user
password: pAssw0rd1!
host: my-mysql-db.xxxxxxx.us-east-2.rds.amazonaws.com
port: 3306
database: movielens
replication_key: updated_at
The following payload will create a MySQL dataset and begin syncing data from Shaped using the Shaped CLI.
shaped create-dataset --file dataset.yaml