migrating-hive-tables-to-s3-tables

Migrating Hive Tables to S3 Tables

Contents

• Overview
• Before you migrate
• When to use
• When NOT to use (use another skill)
• Scope and constraints
• The 6 migration APIs
• Migration lifecycle
• Pre-migration guardrails
• Post-migration guardrails
• Key API shapes
• Security considerations
• References

Overview

This skill guides an agent through migrating one Glue Hive Parquet table into an S3 table bucket with the native S3 Tables migration API. Amazon S3 Tables plans the migration, reports column and object compatibility, copies the data, and keeps the destination in sync with the source until you complete it. Your coding agent, following this skill, runs preflight checks, starts the migration, presents the plan for approval, monitors progress, completes the migration, and validates the result.

When the AWS MCP Server is available, use it to load this skill and its references (aws___retrieve_skill, with file="references/<name>.md") and to run the AWS calls.

Before you migrate

Agent instruction: You MUST present the migration plan and obtain explicit user approval before any step that changes tables, data, metadata, or catalog entries, and before any IAM, KMS, or S3 bucket change (creating or editing a role or policy, editing a key policy, creating a report bucket). Show the exact JSON or command first. For a key policy, read it with get-key-policy, add one statement, and never replace the whole policy. The plan MUST list:

• the source table and the destination table bucket, namespace, and table name;
• the write paths that will change (ETL jobs, streaming or ingest, INSERT/MERGE, CDC);
• the query engines and downstream consumers that read the table;
• the datasets, saved queries, and permissions that reference the old Glue table or S3 prefix.

Up-front approval in the user's request (naming the source, destination, and intent to migrate) covers only read-only checks and a Start with plan review. Approving the plan, accepting any setNull or drop policy, Complete, and any delete always need explicit approval during the conversation. If the actual state differs from what was approved (a different schema, partition layout, row count, or resource), stop, explain the difference, and get approval again.

Confirm these constraints with the user as part of the plan:

• Every writer to the destination writes through an Iceberg-aware engine (for example Athena, EMR or Glue with Iceberg). Writers that write Parquet files directly to S3 will not work.
• Every reader supports Iceberg and the S3 Tables catalog.
• Jobs and queries move from the Glue Hive table to the S3 Tables catalog reference.
• The source is not changed. Keep writers on the source until the destination is validated.
• Validate row counts and sample values (references/validation.md) before moving production traffic.

See Migrating tables to S3 Tables for the full list of supported sources and limits.

Runtime: works with or without the AWS MCP server; steps use AWS CLI syntax. The AWS MCP server is recommended for sandboxed execution and audit logging. The migration commands are part of aws s3tables; if an older CLI lacks them, see references/api-setup.md.

When to use

Use this skill when the source is a Glue Data Catalog Hive table with Parquet data (TableType=EXTERNAL_TABLE, Hive Parquet SerDe) and the destination is an S3 table bucket in the same account and Region:

• Migrating that table into S3 Tables as managed Iceberg with the native migration API.
• Keeping the migrated table in sync with the active Hive source before cutover.

When NOT to use (use another skill)

The names below are other agent skills. For these requests, do not use this skill; load the named skill instead. When asked when not to use this skill, list every case:

• A self-run CTAS, Glue ETL, or EMR/Spark migration into Iceberg: use the ingesting-into-data-lake skill (this skill covers only the managed migration API).
• Converting a self-managed or standard Iceberg table: use the ingesting-into-data-lake skill.
• Loading raw files (Parquet with no Glue table, CSV, JSON) or other sources (JDBC, Snowflake, BigQuery, DynamoDB): use the ingesting-into-data-lake skill.
• Creating a new empty Iceberg table: use the creating-data-lake-table skill.
• Running or managing Athena queries: use the querying-data-lake skill.
• Locating tables or assets: use the finding-data-lake-assets skill. Auditing the catalog: use the exploring-data-catalog skill.
• Setting up Glue connections: use the connecting-to-data-source skill.
• Any destination other than S3 Tables.

Prompt patterns (trigger phrasings)

• "Migrate my Glue Hive table <db>.<tbl> into my table bucket <name>."
• "Import an existing Hive table into S3 Tables using the native migration API."
• "Move my Parquet Hive table to Iceberg without running CTAS or a Glue/Spark job."
• "Use StartTableMigration to convert <tbl>."
• "Keep my migrated table in sync with the source before cutover" → Sync.
• "Complete my migrated table" → Complete.
• "Check the status or plan of migration <id>" → Get.
• "List my table migrations" → List.

Scope and constraints

• Source: Glue Hive Parquet table in the same account and region as the destination table bucket.
• Destination: an existing S3 table bucket and namespace; the migration creates the table.
• Not supported (confirm the current list in the S3 Tables migration guide): Lake Formation registered tables, partitions outside the table location, non-Parquet formats, Iceberg sources, cross-account, SSE-C, and archived storage classes. Check these in preflight and stop if any apply. For a Lake Formation registered table, tell the user it is not supported and suggest contacting AWS Support or their account team with the migration ID to request support. Never change Lake Formation settings.
• Compatibility problems are reported during planning. Always review the plan before approving.
• All-or-nothing copy: if a single object fails, the whole migration fails. A succeeded run has no failed objects.
• Three reports are written to a general-purpose bucket you supply: table compatibility, object compatibility, and object migration.
• Cost and duration: the migration writes a second copy of the data; plan for roughly double storage until the source is decommissioned. Large tables can take hours.
• Quotas: see S3 Tables quotas.
• Migration policies: by default an incompatible column fails the run. incompatibleValuesPolicy: setNull and unmatchedSourceFieldsPolicy: drop apply to the whole column and the run still reports succeeded. Treat any setNull or dropped column as data loss that needs explicit approval.

The 6 migration APIs

API	Purpose	Phase
StartTableMigration	Plan and begin a migration. With --review-migration-plan, pauses at pendingReview.	Start
GetTableMigration	Returns state, hasValidPlan, planningStats, migrationStats, migrationOutput, failureReason.	Track, review
UpdateTableMigration	Approve (--requested-state queued) or cancel.	Approve
SyncTableMigration	Apply source changes since the last plan; repeat until current.	Sync
CompleteTableMigration	Promote the destination to read-write. Takes --table-arn. Returns an empty body; the migration state stays succeeded. Confirm with get-table (managedByService is null).	Complete
ListTableMigrations	List migrations for one table bucket or table.	Discover

Request and response shapes are in references/lifecycle.md.

Migration lifecycle (phases)

1. Preflight → references/preflight.md, references/iam-roles.md
2. Start and plan: start-table-migration --review-migration-plan; planning → pendingReview.
3. Review: get-table-migration; read the compatibility reports and present the plan for approval.
4. Approve and migrate: update-table-migration --requested-state queued; poll migrating → succeeded.
5. Sync (optional): sync-table-migration; repeat until current.
6. Validate: on the succeeded table, before Complete, query the destination in Athena and compare with the source → references/validation.md. Validation is part of every migration: run the row-count and sample checks without being asked, unless the user opts out. If counts are short, reconcile with sync-table-migration (not possible after Complete).
7. Complete: once parity holds and the user approves, complete-table-migration --table-arn ..., then confirm get-table shows managedByService null.

Phases 2 to 7 are in references/lifecycle.md.

Pre-migration guardrails (run every time, before Start)

Do not call start-table-migration until all of these pass. Commands are in references/preflight.md and references/iam-roles.md.

0. Plan approved. The plan in Before you migrate has been presented and approved.
1. Credentials and region. sts get-caller-identity succeeds; the source and destination are in the same account and region.
2. Source is Glue Hive Parquet. TableType=EXTERNAL_TABLE, Hive Parquet format and SerDe; not Iceberg, not ACID/transactional, no storage handler. Record catalogId.
3. Partition locations. Every partition location is under the table location; partitions outside it fail planning with UNSUPPORTED_PARTITIONS. Check for partition projection and skip.header.line.count.
4. Lake Formation. If the source table or its location is registered with Lake Formation, stop: not supported (confirm current support in the migration guide). Do not change Lake Formation settings.
5. Destination exists. The table bucket and namespace exist. A missing namespace fails Start with NotFoundException.
6. Migration role trust. One statement, principal migration.s3tables.amazonaws.com, conditions aws:SourceAccount and aws:SourceArn.
7. Migration role permissions. Scoped source S3 and Glue read, destination write, report bucket write. The caller also needs iam:PassRole on the role, conditioned on iam:PassedToService = migration.s3tables.amazonaws.com.
8. KMS. If data is encrypted with a customer managed key, grant kms:Decrypt and kms:GenerateDataKey. For an SSE-KMS destination, the key policy must also allow maintenance.s3tables.amazonaws.com, or table creation fails and planning never completes (see preflight §7 and IAM roles).

Post-migration guardrails (verify success before Complete)

Full detail in references/validation.md.

Run checks 1 to 7 on the succeeded table before Complete, because Sync is refused after Complete. After Complete, only confirm that get-table shows managedByService null.

1. Terminal state: state: succeeded, failureReason null.
2. Object accounting: objectsFailedMigration is 0 and objectsMigrated matches the planned object count. If it differs, check the source object count (an extra file from an earlier sync is legitimate) and confirm with row-count parity before treating it as a failure.
3. Row count parity: destination count(*) equals source.
4. Partition parity: per-partition row counts match (partitioned tables).
5. Spot-check rows: sample values match, including converted columns such as timestamps.
6. Schema review: every column's type label (Lossless, Widened, Converted) was accepted in the plan; none is Incompatible or Unsupported.
7. Catalog naming: query as "s3tablescatalog/<table-bucket>"."<namespace>"."<table>".

Key API shapes

• sourceType = glueTable (top level) and source = {"glueTable":{"catalogId":"<acct>","databaseName":"<db>","tableName":"<tbl>"}}. catalogId is required. The role member is roleARN.
• --destination-table = {"tableBucketARN":"<arn>","namespace":"<ns>","name":"<tbl>"}.
• migrationOutput is required: {bucket, prefixes}, where bucket is a general-purpose bucket ARN and prefixes optionally sets tableCompatibilityReport, objectCompatibilityReport, objectMigrationReport.
• Optional dataMigration.migrationPolicies: unmatchedSourceFieldsPolicy (fail | drop), incompatibleValuesPolicy (fail | setNull). Both default fail.
• Do not add sts:ExternalId to the trust policy; Amazon S3 Tables does not send one.
• States: new → planning → pendingReview → queued → migrating → succeeded, plus failed, cancelling, cancelled.
• On cancel or failure the destination table is kept. If hasValidPlan is true, resume with sync-table-migration; otherwise delete the table and start again. Rules in references/lifecycle.md.
• Complete only a succeeded migration. Completing twice returns 409 ConflictException or 400 BadRequestException; treat either as "already done."
• Athena reference: "s3tablescatalog/<table-bucket>"."<namespace>"."<table>". Requires the table bucket to be integrated with the Glue Data Catalog.

Security considerations

• Instructions only; no shipped code or dependencies.
• Migration role: trusts migration.s3tables.amazonaws.com with aws:SourceAccount and aws:SourceArn conditions; permissions scoped to the exact source, destination, and report resources.
• Encryption: SSE-KMS on the destination table bucket and report bucket is recommended, with TLS-only bucket policies. A customer managed destination key must allow maintenance.s3tables.amazonaws.com in its key policy.
• Logging: enable CloudTrail for the migration APIs and role assumptions, S3 data-event or server-access logging on the buckets involved, and CloudWatch alarms on failures. Encrypt log destinations.
• No secrets in generated commands, reports, or state files.

References

File	Topic
references/api-setup.md	Confirming the migration commands are in the aws s3tables CLI
references/preflight.md	Credentials, source checks, partition locations, Lake Formation, destination
references/iam-roles.md	Caller policy and migration role trust and permissions
references/lifecycle.md	State machine and per-phase calls: start, review, approve, sync, complete
references/validation.md	Athena row-count, aggregate, and sample checks; catalog naming
references/troubleshooting.md	failureReason codes and fixes