Move a MySQL Table to S3 Parquet, Then Query It with SQL

Objective

You have an operational MySQL database. Analysts want to query it, but you don't want them on the primary, and you don't want to stand up a warehouse plus an ETL tool plus a scheduler to get the data somewhere safe.

This recipe exports a MySQL table to Parquet in your own object storage, on a schedule, and then registers that Parquet as an ordinary SQL table you can SELECT and JOIN against — reading it in place, with no load step.

Three things are worth knowing before you start:

• Your MySQL credentials are stored once as a connection and reused. The lake never keeps a second copy of them.
• Your S3 secret is encrypted at rest and never returned by the API — not the plaintext and not the ciphertext. You'll see the access key id and has_stored_secret: true, nothing more.
• The export runs in a separate child process with its own memory budget, so a large table cannot take the database down with it.

On the blocks below. The setup is REST, so those blocks are bash and are not executed by the recipe runner — run them yourself, in order. The sql blocks at the end are real and run against the table the setup creates.

Step 1: Configure the engine for lake exports

Two things are required: turn the lake on, and give the engine a key to encrypt stored destination secrets with.

# gateway.toml
cat >> gateway.toml <<'TOML'

[lake]
enabled = true
TOML

# A 32-byte key. Generate it ONCE and keep it with your other secrets --
# losing it means re-entering every destination credential.
export AIDB_ENCRYPTION_KEY="$(openssl rand -base64 32)"

synapcores --config gateway.toml

If you skip the key, the lake tells you so and stays off rather than starting in a state where it would store your S3 secret unencrypted:

ERROR Data lake failed to start: lake credential encryption: AIDB_ENCRYPTION_KEY
is not set, so lake credentials cannot be encrypted. /v1/lake/* will return 503.

For a throwaway dev instance only, AIDB_LAKE_ALLOW_DEV_KEY=1 uses a key that is published in the source tree. Never use it anywhere real.

Grab an admin token for the rest of the recipe:

export AIDB=http://localhost:8080
export TOKEN=$(curl -sS -X POST $AIDB/v1/auth/login \
  -H 'content-type: application/json' \
  -d '{"username":"admin","password":"'"$ADMIN_PASS"'"}' | jq -r .data.access_token)

Step 2: Point SynapCores at your MySQL

The MySQL side is a data-sync connection. Note the field is database_name, not database.

curl -sS -X POST $AIDB/v1/data-sync/connections \
  -H "Authorization: Bearer $TOKEN" -H 'content-type: application/json' \
  -d '{
    "name": "prod_mysql",
    "connection_type": "mysql",
    "host": "127.0.0.1",
    "port": 3306,
    "database_name": "laketest",
    "username": "readonly_etl",
    "password": "'"$MYSQL_PASS"'"
  }' | jq '{id, name, database_name, status}'

{ "id": "cd56192c-9dd8-4d43-9583-0d48d3fa2437", "name": "prod_mysql",
  "database_name": "laketest", "status": "active" }

Save the id and prove the engine can actually reach MySQL before you build a job on top of it:

export CONN=cd56192c-9dd8-4d43-9583-0d48d3fa2437
curl -sS -X POST $AIDB/v1/data-sync/connections/$CONN/test \
  -H "Authorization: Bearer $TOKEN" | jq '{is_healthy, response_time_ms, error_message}'

{ "is_healthy": true, "response_time_ms": 0, "error_message": null }

Confirm the table you want is visible through the connection:

curl -sS $AIDB/v1/lake/sources/$CONN/tables -H "Authorization: Bearer $TOKEN" | jq

["enc_probe", "nums", "pings", "pings_perf", "pings_perf_seq", "recipe_lake_orders"]

Step 3: Declare where the Parquet goes

A destination is your bucket plus its credentials. This example targets MinIO on localhost; for AWS drop endpoint, allow_http and path_style.

curl -sS -X POST $AIDB/v1/lake/destinations \
  -H "Authorization: Bearer $TOKEN" -H 'content-type: application/json' \
  -d '{
    "name": "analytics_bucket",
    "kind": "s3",
    "bucket": "synapcores-lake",
    "prefix": "recipes/orders",
    "region": "us-east-1",
    "endpoint": "http://127.0.0.1:9010",
    "allow_http": true,
    "path_style": true,
    "credentials": {
      "kind": "static",
      "access_key_id": "lakeadmin",
      "secret_access_key": "'"$S3_SECRET"'"
    }
  }' | jq '{id, bucket, prefix, credentials_kind, access_key_id, has_stored_secret, status}'

{ "id": "f5a76092-c2d5-4239-84fa-bdd45d498c95", "bucket": "synapcores-lake",
  "prefix": "recipes/orders/", "credentials_kind": "static",
  "access_key_id": "lakeadmin", "has_stored_secret": true, "status": "untested" }

Note what came back: the access key id, which identifies the key, and has_stored_secret: true. The secret itself is gone — a stored ciphertext handed back to a client would be an offline attack on your encryption key.

"credentials": {"kind": "ambient"} instead uses the instance role / environment credentials, which is what you want on EC2 or EKS.

Now test it. This is a real write-and-delete probe, not a ping:

export DEST=f5a76092-c2d5-4239-84fa-bdd45d498c95
curl -sS -X POST $AIDB/v1/lake/destinations/$DEST/test \
  -H "Authorization: Bearer $TOKEN" | jq

{ "reachable": true, "can_list": true, "can_write": true, "can_delete": true,
  "described": "s3://synapcores-lake/recipes/orders/ (us-east-1 via http://127.0.0.1:9010)" }

Do not skip this. A credential that can write but not list produces a job that half-works at 3am. Four separate answers is the point.

Step 4: Define the export job

curl -sS -X POST $AIDB/v1/lake/jobs \
  -H "Authorization: Bearer $TOKEN" -H 'content-type: application/json' \
  -d '{
    "name": "orders_to_lake",
    "description": "orders -> parquet -> s3",
    "source_connection_id": "'"$CONN"'",
    "destination_id": "'"$DEST"'",
    "tables": [{
      "source_table": "recipe_lake_orders",
      "lake_table": "orders_snap",
      "mode": { "mode": "snapshot" },
      "columns": [],
      "order_by": ["order_id"],
      "promote": [],
      "transforms": []
    }],
    "schedule_cron": "0 3 * * *",
    "enabled": true
  }' | jq '{id, name, enabled, schedule_cron}'

The mode object is the one field worth reading twice:

"columns": [] means "resolve every column at run time" — and if the source schema drifts from what was resolved, the run fails rather than silently writing a different shape. order_by makes the file layout deterministic, which is what lets you diff two snapshots.

Step 5: Run it, and read the receipt

export JOB=<the job id>
curl -sS -X POST $AIDB/v1/lake/jobs/$JOB/run -H "Authorization: Bearer $TOKEN" | jq '.data[0]'

The response is a list — one run per table in the job. Poll the run id:

curl -sS $AIDB/v1/lake/runs/<run id> -H "Authorization: Bearer $TOKEN" \
  | jq '{status, rows, bytes, peak_rss_bytes, child_exit_code, error}'

{ "status": "succeeded", "rows": 8, "bytes": 2247,
  "peak_rss_bytes": 87183360, "child_exit_code": 0, "error": null }

peak_rss_bytes and child_exit_code are there because the export runs as a child process — you can see what it actually cost, and a child that dies is a failed run, not a silently truncated file.

Check what landed:

curl -sS $AIDB/v1/lake/tables -H "Authorization: Bearer $TOKEN" | jq '.data[0]'

{ "table": "orders_snap", "partitions": 1, "verified_partitions": 1,
  "rows": 8, "bytes": 2247, "files": 1,
  "last_partition": "snapshot_dt=2026-10-08", "external_table": null }

verified_partitions equals partitions, so every file the run claims to have written was read back and confirmed. external_table: null just means it isn't queryable yet — that's the next step.

Step 6: Make it a SQL table

curl -sS -X POST $AIDB/v1/lake/tables/orders_snap/register \
  -H "Authorization: Bearer $TOKEN" | jq '{name, database, location, partition_columns, columns, sql}'

{ "name": "orders_snap", "database": "main",
  "location": "s3://synapcores-lake/recipes/orders/orders_snap/",
  "partition_columns": [{"name": "snapshot_dt", "data_type": "DATE"}],
  "columns": 7,
  "sql": "CREATE EXTERNAL TABLE main.orders_snap STORED AS PARQUET LOCATION '...' PARTITIONED BY (snapshot_dt DATE)" }

It hands back the exact DDL it ran, so nothing is hidden. You can also write that statement yourself for Parquet that SynapCores didn't export.

Step 7: Query the Parquet in place

From here it is just SQL. These blocks are live.

SHOW EXTERNAL TABLES;

SELECT order_id, customer, region, amount, status
FROM orders_snap
ORDER BY order_id
LIMIT 4;

Expected — and note amount is an exact DECIMAL, not a float that has been through a binary rounding:

Step 8: Ground truth — check the lake against the source

This is the step that proves the export is faithful. Revenue by region for shipped orders only:

SELECT region, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders_snap
WHERE status = 'shipped'
GROUP BY region
ORDER BY revenue DESC;

Expected, exactly:

Verify it against MySQL directly — the two must agree to the cent:

mysql -h 127.0.0.1 -P 3306 -u readonly_etl -p laketest -e "
  SELECT region, COUNT(*) orders, SUM(amount) revenue
  FROM recipe_lake_orders WHERE status='shipped'
  GROUP BY region ORDER BY revenue DESC;"

us-west has 2 shipped orders (12450.00 + 2210.00 = 14660.00) while its third is not shipped; apac and eu-west each have one shipped and one pending; us-east has one shipped and one cancelled. If your totals differ, the WHERE ran against a stale snapshot — check last_partition from step 5.

Step 9: Let the schedule own it

The job already carries "schedule_cron": "0 3 * * *", so it re-exports nightly at 03:00 and adds a new snapshot_dt= partition each time. Nothing else to wire up — no Airflow, no cron box, no Lambda.

# pause / resume without deleting the job
curl -sS -X POST $AIDB/v1/lake/jobs/$JOB -H "Authorization: Bearer $TOKEN" \
  -H 'content-type: application/json' -d '{"enabled": false}'

# recent runs across all jobs
curl -sS "$AIDB/v1/lake/runs?limit=10" -H "Authorization: Bearer $TOKEN" \
  | jq '.data[] | {table, status, rows, started_at}'

After a few nights, query across snapshots — snapshot_dt is a real partition column, so a filter on it skips whole directories instead of reading them:

SELECT snapshot_dt, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders_snap
GROUP BY snapshot_dt
ORDER BY snapshot_dt DESC;

Cleanup (Optional)

DROP EXTERNAL TABLE IF EXISTS orders_snap;

curl -sS -X DELETE $AIDB/v1/lake/jobs/$JOB         -H "Authorization: Bearer $TOKEN"
curl -sS -X DELETE $AIDB/v1/lake/destinations/$DEST -H "Authorization: Bearer $TOKEN"
curl -sS -X DELETE $AIDB/v1/data-sync/connections/$CONN -H "Authorization: Bearer $TOKEN"

Dropping the external table does not delete the Parquet. Your data stays in your bucket — remove the objects yourself if you want them gone.

Expected Outcomes

• destinations/$DEST/test returns four separate trues, not one "ok".
• One run writes 8 rows / 2,247 bytes / 1 file into snapshot_dt=2026-10-08, with child_exit_code: 0.
• verified_partitions equals partitions — every file was read back.
• SELECT over S3 Parquet returns DECIMAL amounts exactly, and the shipped-revenue aggregate matches MySQL to the cent.

Troubleshooting

Use it from your agent

• REST/SDK: every call above is plain REST, so a scheduler or agent can create a destination, fire a job and poll the run without a client library.
• Why in-DB: the export, the schedule, the retry, the verification and the query engine are one process. A stitched stack needs an orchestrator, a warehouse and a catalog — and each boundary is a place a 3am run fails silently.

Key Concepts Learned

• A data-sync connection holds source credentials once; the lake reuses them rather than storing a second copy.
• A destination encrypts its secret at rest and never returns it — you get the access key id and has_stored_secret, which is enough to audit which key is in use.
• snapshot mode trades storage for simplicity and removes CDC entirely; incremental is for append-only facts.
• verified_partitions is the difference between "the job said it worked" and "the bytes were read back".
• Registering Parquet as an external table makes object storage queryable in place — no load step, and dropping the table never touches your data.