[FEATURE]: Connect _migrate_table_create_ctas to migrate-tables workflow

[nfx]

most of the code is done in:

• Migrate non delta dbfs tables using Create Table As Select (CTAS). Convert such tables to Delta tables. #1434

Diff:

ucx/src/databricks/labs/ucx/hive_metastore/table_migrate.py

Lines 286 to 299 in 9cb9dbf

	def _migrate_table_create_ctas(self, src_table: Table, rule: Rule, grants: list[Grant] | None = None):
	table_migrate_sql = self._get_create_ctas_sql(src_table, rule)
	logger.debug(f"Migrating table (Create Like) {src_table.key} to using SQL query: {table_migrate_sql}")
	self._backend.execute(table_migrate_sql)
	self._backend.execute(src_table.sql_alter_to(rule.as_uc_table_key))
	self._backend.execute(src_table.sql_alter_from(rule.as_uc_table_key, self._ws.get_workspace_id()))
	return self._migrate_acl(src_table, rule, grants)
	def _get_create_ctas_sql(self, src_table: Table, rule: Rule) -> str:
	create_sql = (
	f"CREATE TABLE IF NOT EXISTS {escape_sql_identifier(rule.as_uc_table_key)} "
	f"AS SELECT * FROM {src_table.safe_sql_key}"
	)
	return create_sql

Tasks:

• Copy this table using CTAS
• Copy the Metadata with CREATE TABLE ... LIKE ...
• Skip tables as needed based on size threshold or an exception list
• Update the source table with upgraded_to property
• Link it to the workflow as task

[pohlposition]

Can we split issues between MANAGED (step:managed tables) and EXTERNAL (step:sync tables).

This maps to go/uc/upgrade

[pohlposition]

I'm changing the name of this issue from:
Migrate MANAGED and EXTERNAL non-Parquet/non-Delta tables

to

Migrate MANAGED non-Parquet/non-Delta tables

because:

1. It separates the workstreams to align to upgrade steps
2. The focus on cloning is applicable to managed tables, but not external tables.

[pohlposition]

One additional consideration to make with Managed tables, which is different to external tables, is that it needs to be executed as much as possible as an atomic transaction:

BEGIN TRANSACTION

1. "Lock old table to prevent writes". (maybe stop a streaming job or something else)
2. Clone Table
3. Update WRITE references to new table
4. Update READ references to new table
5. "DEPRECATE" old managed table - Revoke permissions; maybe rename it with a prefix or something
   COMMIT TRANSACTION

At some point, after an acceptable retention period, you will want to purge that old table's data too.

This is to prevent data from being written to the old table while the upgrade is being performed.

Additionally, you want to prevent data from being written to the old table after you have upgraded to the new table.

[pohlposition]

One option we may want to provide customers is to convert the managed table into an external table first (still using HMS).

If that completes successfully, then that external table can be upgraded more easily using our external table upgrade process.

External tables are easier to upgrade because you can upgrade their metadata independent of the job. You don't need to worry about the atomic transaction so much.