CDAS and CTAS

The Ververica Platform supports CREATE DATABASE AS SELECT (CDAS) and CREATE TABLE AS SELECT (CTAS) statements, which simplify the process of synchronizing data and metadata across different systems.

CREATE TABLE AS SELECT (CTAS)

The CREATE TABLE AS SELECT statement allows you to create a new table and populate it with the results of a query in a single step. This is particularly useful for creating derived tables or materialized views within your Flink SQL jobs.

Syntax

1CREATE TABLE [catalog_name.][db_name.]table_name
2[ WITH (property_name=property_value, ...) ]
3AS SELECT query_expression;

Examples

The following example creates a new table named user_summary in the current database based on a query from the users table.

1CREATE TABLE user_summary 
2AS SELECT user_id, COUNT(order_id) as total_orders 
3FROM users 
4GROUP BY user_id;

CREATE DATABASE AS SELECT (CDAS)

The CREATE DATABASE AS SELECT statement allows you to synchronize an entire database, including its schema and data, to a destination system. This is often used for database replication or migration tasks.

Syntax

1CREATE DATABASE [IF NOT EXISTS] target_catalog.target_database
2AS DATABASE source_catalog.source_database
3[ INCLUDING { TABLE 'table_name' | ALL TABLES } ]
4[ EXCLUDING { TABLE 'table_name' } ]
5[ WITH (property_name=property_value, ...) ];

Examples

The following example synchronizes all tables from the production database in the mysql_catalog to the replica database in the paimon_catalog.

1CREATE DATABASE paimon_catalog.replica
2AS DATABASE mysql_catalog.production;

Limits and Considerations

• CDAS and CTAS statements are subject to the capabilities of the underlying connectors used for the source and target systems.
• Ensure that the target catalog and database have the necessary permissions for table or database creation.
• Checkpointing must be enabled for jobs that use CDAS/CTAS for data synchronization.