JDBC Command Target
The jdbcCommand
target is a generic and flexible execution target which allows you to execute arbitrary SQL
statements in the syntax of the target database. This way you can use database features, which are currently not
directly supported by Flowman. We highly recommend to use this only as a workaround for mitigating missing
Flowman features or for using exotic database features, since Flowman has no way of understanding what you are
doing.
Example
The following example will create and manage a MS SQL Server full text catalog and index:
targets:
fulltext-catalog:
kind: jdbcCommand
connection: sql_server
# Create Fulltext Catalog
create:
# Check that catalog does not already exists
condition: |
SELECT 1 FROM sys.fulltext_catalogs
WHERE name = 'ftcat'
HAVING COUNT(*) = 0
sql: |
CREATE FULLTEXT CATALOG ftcat
# Remove fulltext catalog
destroy:
# Check that catalog really exists
condition: |
SELECT 1 FROM sys.fulltext_catalogs
WHERE name = 'ftcat'
HAVING COUNT(*) = 1
sql: |
DROP FULLTEXT CATALOG ftcat
tweets-index:
kind: jdbcCommand
connection: sql_server
# We require both the fulltext catalog and the base table
after:
- fulltext-catalog
- tweets-mssql
# Create Index
create:
# Check that index does nto already exist
condition: |
SELECT 1 FROM sys.fulltext_indexes i
WHERE i.object_id = OBJECT_ID('dbo.tweets')
HAVING COUNT(*) = 0
sql: |
CREATE FULLTEXT INDEX ON dbo.tweets
(
text,
user_description
)
KEY INDEX PK_tweets_id ON ftcat
WITH CHANGE_TRACKING OFF
# Fill index by starting background indexing process
build:
sql:
- "ALTER FULLTEXT INDEX ON dbo.tweets START FULL POPULATION"
# Delete index
destroy:
# Check that index really exists
condition: |
SELECT 1 FROM sys.fulltext_indexes i
WHERE i.object_id = OBJECT_ID('dbo.tweets')
HAVING COUNT(*) = 1
sql: |
DROP FULLTEXT INDEX ON dbo.tweets
As you can see, the jdbcCommand
target supports different SQL commands for each lifecycle phase
(CREATE
, BUILD
, …) and you can also specify an optional condition
which serves as a precondition when the
corresponding SQL should be executed or not. This way you can avoid creating tables multiple times etc by first
checking if the table already exists.
Fields
kind
(mandatory) (type: string):hiveDatabase
description
(optional) (type: string): Optional descriptive text of the build targetvalidate
(optional) (type: action): Optional action to be executed duringVALIDATE
phasecreate
(optional) (type: action): Optional action to be executed duringCREATE
phasebuild
(optional) (type: action): Optional action to be executed duringBUILD
phaseverify
(optional) (type: action): Optional action to be executed duringVERIFY
phasetruncate
(optional) (type: action): Optional action to be executed duringTRUNCATE
phasedestroy
(optional) (type: action): Optional action to be executed duringDESTROY
phase
Each action has the following fields:
sql
(required) (type: array[string]): One or more SQL command(s) to be executedcondition
(optional) (type: string): An optional SQL query which is used to determine if the execution needs to be executed. The query should return a single row with a single integer column. If that column contains a value of1
or larger, then the target is considered to be in a dirty state and the action will be executed. Otherwise, it will be skipped. If no row is returned, the target is considered to be in clean state, and the action will be skipped.transactional
(optional) (type: boolean) (default: false): If set totrue
, then all SQL commands within the action will be executed as part of a single database transaction. Note that each database might impose some restrictions on what can be performed within a single transaction, especially not all DDL commands might be supported inside a transaction by all database servers.
Supported Execution Phases
VALIDATE
- The action specified invalidate
will be executed.CREATE
- The action specified increate
will be executed.BUILD
- The action specified inbuild
will be executed.VERIFY
- The action specified inverify
will be executed.TRUNCATE
- The action specified intruncate
will be executed.DESTROY
- The action specified indestroy
will be executed.
Read more about execution phases.
Dirty Condition
Flowman will apply some logic to find out if a stream target is to be considered being dirty for a specific execution
phase, which means that it needs to participate in that phase. The jdbcCommand
target is considered being dirty
whenever the optional condition
holds true (when it is specified), or when no condition is specified at all.