REPLACE job: `ALTER TABLE "testdrive_staging"."exchange_latest" SET SCHEMA "testdrive";`

[amotl]

Problem

dlt issues this query when using the replace strategy, but fails on CrateDB.

ALTER TABLE "testdrive_staging"."exchange_latest" SET SCHEMA "testdrive";

psycopg2.errors.InternalError_: line 3:55: missing '(' at 'SCHEMA'

[amotl]

Adjusting the relevant code for CrateDB, the adapter is now using ALTER CLUSTER SWAP TABLE {staging_table_name} TO {table_name} instead of ALTER TABLE ... SET SCHEMA ....

• ALTER TABLE ... SET SCHEMA ... crate#18092

The spot is CrateDbStagingReplaceJob, overriding fragments of the standard SqlStagingReplaceFollowupJob.

dlt-cratedb/src/dlt_cratedb/impl/cratedb/cratedb.py

Lines 28 to 67 in 12b2d1e

	class CrateDbStagingReplaceJob(SqlStagingReplaceFollowupJob):
	@classmethod
	def generate_sql(
	cls,
	table_chain: Sequence[PreparedTableSchema],
	sql_client: SqlClientBase[Any],
	) -> List[str]:
	"""
	CrateDB uses `ALTER CLUSTER SWAP TABLE`.
	-- https://github.com/crate/crate/issues/14833
	"""
	sql: List[str] = []
	for table in table_chain:
	with sql_client.with_staging_dataset():
	staging_table_name = sql_client.make_qualified_table_name(table["name"])
	table_name = sql_client.make_qualified_table_name(table["name"])
	sql.extend(
	(
	# Drop destination table.
	f"DROP TABLE IF EXISTS {table_name};",
	# Recreate destination table, because `ALTER CLUSTER SWAP TABLE` needs it.
	(
	f"CREATE TABLE IF NOT EXISTS {table_name} AS SELECT * FROM"
	f" {staging_table_name} WHERE 1 = 0;"
	),
	# Move the staging table to the destination schema.
	f"ALTER CLUSTER SWAP TABLE {staging_table_name} TO {table_name};",
	# CrateDB needs to flush writes.
	f"REFRESH TABLE {table_name};",
	# Recreate staging table not needed with CrateDB, because
	# `ALTER CLUSTER SWAP TABLE` does not remove the source table.
	(
	f"CREATE TABLE IF NOT EXISTS {staging_table_name} AS SELECT * FROM"
	f" {table_name} WHERE 1 = 0;"
	),
	f"REFRESH TABLE {staging_table_name};",
	)
	)
	return sql

ALTER TABLE ... RENAME TO ... would have been an alternative candidate, but it doesn't work due to a little shortcoming.

• ALTER TABLE RENAME TO Add support for schema name on the target table crate#14833
• SQLParseException[Target table name must not include a schema] #9

[amotl]

The improvement request to CrateDB needs more upvotes. /cc @florinutz, @bgunebakan

• ALTER TABLE RENAME TO Add support for schema name on the target table crate#14833