Skip to main content

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​

CREATE TABLE [catalog_name.][db_name.]table_name
[ WITH (property_name=property_value, ...) ]
AS 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.

CREATE TABLE user_summary 
AS SELECT user_id, COUNT(order_id) as total_orders 
FROM users 
GROUP 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​

CREATE DATABASE [IF NOT EXISTS] target_catalog.target_database
AS DATABASE source_catalog.source_database
[ INCLUDING { TABLE 'table_name' | ALL TABLES } ]
[ EXCLUDING { TABLE 'table_name' } ]
[ 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.

CREATE DATABASE paimon_catalog.replica
AS 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.
On this page