Skip to main content
This gem runs in .

​
Overview

Use a custom SQL statement in your pipeline. The SQLStatement gem supports SELECT statements. This gem does not support actions like inserting or deleting tables.
The gem has a corresponding interactive gem example. See Interactive gem examples to learn how to run sample pipelines for this and other gems.

​
Input and Output

The SQLStatement gem uses the following input and output ports.
PortDescription
in0(Optional) Input table used in query.
outOutput table with query results.
By default, the SQL statement gem does not include an input port. To add a port, click the + button next to Ports. Alternatively, connect the previous gem to the SQLStatement gem directly to add a new input port.
Number of inputs and outputs can be changed as needed by clicking the + button on the respective tab. To learn more about adding and removing ports, see Gem ports.

​
Parameters

Configure the SQLStatement gem using the following parameters.
ParameterMeaning
OutSQL query that defines the output result.
Write your SQL query in the syntax of your SQL warehouse provider. For example, if you’re connected to a Databricks SQL warehouse, use Databricks SQL dialect. If you’re using the Prophecy In Memory, use DuckDB dialect.
To reference the input table in the SQL query, use the name of the gem that generates the input table.

​
Example

Assume you have a gem named weather_predictions. It outputs the following table.
DatePredictionTemperatureCelsiusHumidityPercentWindSpeedCondition
2025-03-01156510Sunny
2025-03-02177012Cloudy
2025-03-03166811Rainy
2025-03-0414729Sunny
To filter out predictions before March 3:
  1. Add a SQLStatement gem to the canvas.
  2. Connect the weather_predictions gem to the SQLStatement gem directly in the canvas. Alternatively, add an input port in the gem configuration and choose the weather_predictions gem for in0.
  3. In the code editor, paste the following query. 
    SELECT *
FROM weather_predictions
WHERE DatePrediction > '2025-03-02'
    This query uses the gem name weather_predictions as the table name in the SELECT statement.
  4. Run the gem. The following table appears as the gem output.
    DatePredictionTemperatureCelsiusHumidityPercentWindSpeedCondition
    2025-03-03166811Rainy
    2025-03-0414729Sunny