SQL Scripts & Deployments

SQL Scripts

SQL Scripts are resources which are used to save and load the content of the SQL Editor. They can be edited, renamed, cloned, and deleted.

When a SQL Script is loaded into the UI editor, you can create a SQL Deployment from it. However, SQL Scripts are not linked to SQL Deployments. Changing a SQL Script after a Deployment was created from it, does not affect the Deployment.

SQL Deployments

SQL Deployments are a special type of Apache Flink® Deployments. Instead of a JAR file and a configuration, SQL Deployments consist of a SQL statement and a Deployment configuration.

SQL Statement
The statement of a SQL Deployment must consist of a single INSERT INTO <sinkTable> SELECT ... FROM <sourceTable> statement. See the Queries page for the SQL scope that is supported for SQL Deployments. You may use the WITH clause to define one or more temporary views.
Configuration
A SQL Deployment is configured just like any other Flink Deployment. In addition to the regular Flink options, there are several SQL-specific options which are discussed in detail on the Deployment Configuration page.

Creating SQL Deployments

You can create a SQL Deployment in two ways:

  • Running an INSERT INTO sinkTable SELECT * FROM sourceTable; statement from the UI Editor.
  • Creating a Deployment resource via the REST API with artifact.kind: SQLSCRIPT.

Below is an example of a SQL Deployment specification:

 kind: Deployment
 apiVersion: v1
 metadata:
   name: my-sql-query
   labels: {}
 spec:
   state: running
   deploymentTargetId: 57b4c290-73ad-11e7-8cf7-a6016ad3dba0
   restoreStrategy:
     kind: latest_state
   upgradeStrategy:
     kind: stateful
   template:
     spec:
       artifact:
         kind: sqlscript
         sqlScript: INSERT INTO sinkTable SELECT * FROM sourceTable;
       parallelism: 1
       resources:
         taskManager:
           memory: 1.5g
       flinkConfiguration:
         taskmanager.numberOfTaskSlots: 1
         state.savepoints.dir: s3://flink/savepoints
       logging:
         log4jLoggers:
           org.apache.flink.streaming.examples: DEBUG

Managing SQL Deployments

SQL Deployments can be managed just like any other Flink Deployment (see Application Operations) with a few exceptions.

When a SQL Deployment is started, its SQL statement is translated into a Flink job which is submitted for execution together with all required dependencies, such as user-defined functions and connectors. In case of failure, Deployments recover from the existing Flink job, i.e., the statement is not translated again to restart the job. The Flink job and its dependencies are only deleted when the SQL Deployment is suspended or canceled.

When the query is resumed or started without a Savepoint, the SQL statement is translated into a new Flink job. Therefore, catalog modifications and updated UDF implementations are only applied when a Deployment is manually stopped and started and not during regular recovery cycles.

The statement of a SQL Deployment cannot be updated. This is because Flink does not guarantee that a Savepoint which was produced by a Flink job for an earlier version of a query is compatible with the Flink job of an updated query. Providing better support for updating SQL Deployments is on the roadmap of Ververica Platform SQL Service.