FME and SQL: A Dynamic Duo for Data Integration

Introduction

In FME Form, there are several places where you run a SQL statement against your database. Leveraging the power of SQL queries, FME users can take advantage of database performance, unique SQL queries, stored procedures, and much more. In this article, we walk you through the various places where you can run SQL statements in FME Workbench. New to FME 2023.1, we will also discuss the AI Assist feature, which allows users to generate SQL statements from plain text. For more in-depth how-to solutions, see the Resources section at the bottom of this article. 

Where to use SQL in FME

In FME, you can utilize SQL statements whenever you interact with the database through readers, writers, or transformers. While FME inherently offers a large range of powerful functionality, users familiar with SQL can seamlessly combine its capabilities with FME to enhance performance and productivity even further.
 

Readers & Writers

SQL To Run Before/After Read

On Database readers and writers, users can specify a “SQL Statement to Run Before/After a Read/Write”. These parameters are accessible in the FME Workbench Navigator window.


For more information about these parameters, check out the documentation on the Reader Parameters for the particular database reader you are using (i.e. Snowflake Reader Parameters documentation, under Constraints > Advanced).

WHERE Clause & SELECT Statements

Database readers also allow users to specify a WHERE clause or SELECT statement directly in the reader feature type.


SQL Statements in the Reader/Writer may be used for:

• Drop and create an indexes on a table
• Use a WHERE clause or SELECT statement to filter data prior to reading it
• Perform other database operations (DELETE, ALTER COLUMN) prior to or after reading/writing.

A typical use case would be to drop an index and then recreate it once the table records have been read into FME. Why would we want to do this? To increase the performance of the reader. By removing the index, FME will be able to read in features much quicker. Once the reader is finished, it will re-create the index (that we’ve specified in the SQL to Run After Read parameter), returning the table back to its original state.

Another use case would be to set a WHERE clause or SELECT statement to filter the data prior to reading it into FME. Instead of using a Tester or TestFilter transformer in FME, we can read in only the data needed, thus reducing the time it takes to read the features in. Less features read also means less processing time in the rest of the workspace. Let the Database Do the Work goes into more detail about workspaces such as these.
 

The SQLCreator

The SQLCreator transformer generates FME features and/or schemas from the results of an SQL query executed against a database. 

SQL statements might be used to:

• Execute your existing SQL queries within an FME workspace
• Create, drop, modify or truncate a database table
• Carry out a database join
• Drop constraints or indexes prior to data loading
• Add constraints or indexes after data loading
• Executing spatial database specific queries
• Any other function that is usually carried out using a SQL statement. This includes stored procedures.
   

The SQLExecutor

The SQLExecutor allows users to execute SQL statements against a database. Each incoming feature triggers the SQL statement that has been defined. If the SQL is a query, and if features are returned from the database, those features form the output from the transformer via the Result port.

Because the SQLExecutor is initiated by features in the FME workspace, users can insert attribute values into their SQL Statements. 
 

Using the SQL Editor

The SQL Editor is the window that opens wherever FME allows you to use SQL commands. 


In the SQLCreator/SQLExecutor clicking the ellipsis “…” button will open the SQL Statement Editor.
If you’ve specified a Format and Database Connection in the SQLCreator/SQLExecutor, then you can preview the tables available in your database by expanding the panel on the left side of the window.


Tables can be further expanded to reveal their columns. Any of the tables or columns can be double-clicked to add them to the SQL query with the correct syntax.

Once you’ve specified a SQL query, the Run button can be used to run the SQL Statement immediately. This can be used to test your SQL query. If the run is successful, you will get a Success message and confirmation that your SQL query is ready to be run from your workspace.


Another feature in the SQL Editor is the right-click options available on the database tables which allows you to build the beginning SQL statements.


If you already have complete SQL queries built out that you want to use in FME, then it’s as easy as copy & pasting the SQL into the SQL statement editor.

Setting the SQL Delimiter
To execute SQL statements separately, a SQL Delimiter can be specified. To do so, include the following at the beginning of your script:

FME_SQL_DELIMITER <sql delimiter of your choice>

If we wanted to set our SQL delimiter to a semi-colon (;), we would use the following statement:

FME_SQL_DELIMITER ;

A more in-depth explanation on the FME_SQL_DELIMITER directive can be found here: Splitting SQL statements using the FME_SQL_DELIMITER directive
 

AI Assist

New to FME 2023.1, the AI Assist (Tech Preview) functionality is available in the SQL Statement Editor to help new and experienced users build out SQL Statements. Schema information from your database can also be sent to the AI service to return a result containing the correct table and column names.

How to use the AI Assist with SQL 
In FME 2023.1 and greater, you can access the AI Assist through the SQL Statement Editor. It will appear as a button in the bottom left corner of the window. 


Using the sample screenshot below, we will provide an overview of each of the components of the AI Assist window. 



1.  SQL Query Description
This is where you can specify your request in plain English. For example:
"Find all the countries that have a population greater than 100000"

This would return a SQL statement such as: 

SELECT * FROM “countries” WHERE “population” > 100000

Send database schema to AI
When enabled, FME scans your database schema to provide a more accurate result from the AI Assist. The resulting SQL will be tailored to the tables and columns that exist in your database, instead of providing generic naming. 

Let's use the following example request: 
"Find all the countries that have a population greater than 100000"

With Send database schema to AI enabled, the AI will try to match the SQL query with the tables and columns in your database.

The AI returns the following SQL statement:

SELECT * FROM "public"."Countries" 
WHERE "population" > 100000

Tip: The Send database schema to AI parameter can prolong the time it takes to return a SQL query from the AI Assist. This is because FME must parse through all the database table and column names and send it to the AI. The amount of tables and columns in each table will dictate how long it takes FME to scan your database schema.

2. Status
Provides an overview of the status of the AI request and whether it succeeded or not.

3. AI Results
The results from the description after it has been run through the AI data model. This will include a functional SQL query to use in the SQL editor. If Send database schema to AI is enabled, then the query may include accurate table and column names.

4. Explanation
To help users understand the AI results, each object within the SQL query is broken down with an explanation. 

Disclaimers
The AI Assist is still in Tech Preview and is subject to change in future FME versions.
For legal information regarding the AI Assist, please see our AI Terms of Use.
If you have any feedback regarding the use of the AI Assist in the SQL Editor, please leave a comment on this article or contact us at info@safe.com.
 

Using the SQLCreator/SQLExecutor to issue commands to a database

The SQLCreator and SQLExecutor Transformers can be used to perform queries on databases from within a workspace.

Using a Published Parameter in a SQL Query
In FME Workbench, published user parameters allow users to specify a value when running a workspace. By combining this feature with a SQL Query used in a SQLCreator/SQLExecutor, we can create workspaces that are driven by the user’s input.

Here is an example of an SQL Statement that queries features from a PostGIS database based on a user parameter value:

SELECT * FROM "public"."PostalAddress" 
where "Status" = '$(Status)' and "PostalCode" like 'V5L%';

In this example, a Choice user parameter is used which allows the user to select from one of the three valid choices - Other, Current, Retired, or Temporary. When run, the SQL statement will use the value specified by the user and run that against the PostGIS Database and select the corresponding features that meet the where clause.


With the value of Status set to Current, the query returns the rows of the PostalAddress table where the Status = Current and the PostalCode begins with V5L. 

Use Attribute Values in a SQL Query
Similar to user parameters, attribute values can be used in a SQL Query to adjust what records are returned to the user when running the workspace. In the following example, values from the SQLCreator are sent to the SQLExecutor to read in records from the AddressPoints table where the PostalAddress = @Value(PostalAddress):

SELECT * FROM "public"."AddressPoints" 
WHERE "PostalAddress" = '@Value(PostalAddress)'

The @Value() function allows for attributes to be used within queries. The statement above will replace @Value(Address) with the value of the Address attribute from the Initiator feature.

In this case, the value for PostalAddress is coming from the previous SQL Query conducted in the SQLCreator. This allows us to chain SQL queries based on what was returned in the previous query. The attribute value could be coming from a different source, such as an API call, a file format reader, or a database reader.

Using Native Spatial Database SQL Commands in the SQLCreator/Executor

Why use native spatial database SQL in FME? 
The FeatureReader, SpatialRelator and SpatialFilter transformers are great tools for performing generalized spatial queries on a database in workbench, they allow you to quickly select your query parameters without the need to fuss over the syntax of database-specific SQL. However if you are performing spatial queries on extremely large datasets and you aren’t afraid of writing a little bit of SQL, you may benefit from the performance gains of implementing your spatial query with a native SQL statement in the SQLExecutor transformer.

Adding a SQLExecutor transformer
In FME workbench, you need to connect the base features you wish to use in your spatial query to the input port of the SQLExecutor transformer. It is important that the geometry is stored in a database-friendly format for these queries; In the example workspace below the geometry has been extracted to OGC Well-Known Text (WKT) format with a GeometryExtractor transformer before being passed to the SQL executor.

Composing a native SQL statement
Various databases differ slightly in the required syntax of their spatial queries. The best way to get started is to read the documentation provided by the spatial database that you are using.
Here is an example of an SQL statement that queries features from a PostGIS database based on the geometry of an input bounding box that was created in workbench:

SELECT * FROM “public”."AddressPoints" as ap
WHERE ST_Within(ap.geom,ST_GeomFromText('@Value(_geometry)',2277))=TRUE

The query returns the rows of the ‘AddressPoints’ table of the ‘public' database where the ST_Within function evaluates to true. The ST_Within function takes two arguments: the first is the name of the geometry column (ap.geom) of the candidate features in the database table , the second is the base geometry generated by the ST_GeomFromText function, which itself takes two arguments: the value of the WKT geometry attribute of the input bounding box feature and a four digit Spatial Reference ID (SRID).

When the result of the query along with the bounding box is sent to the inspector, we can see that the SQLExecutor returns only the point features that fall within the box.
 

Resources

For more in-depth solutions using SQL in FME, take a look at the following examples:

• Using the SQLExecutor to do a SQL Join
• Executing a Stored Procedure on Microsoft SQLServer with FME
• Let the Database do the Work