| //// |
| Licensed to the Apache Software Foundation (ASF) under one |
| or more contributor license agreements. See the NOTICE file |
| distributed with this work for additional information |
| regarding copyright ownership. The ASF licenses this file |
| to you under the Apache License, Version 2.0 (the |
| "License"); you may not use this file except in compliance |
| with the License. You may obtain a copy of the License at |
| http://www.apache.org/licenses/LICENSE-2.0 |
| Unless required by applicable law or agreed to in writing, |
| software distributed under the License is distributed on an |
| "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| KIND, either express or implied. See the License for the |
| specific language governing permissions and limitations |
| under the License. |
| //// |
| :documentationPath: /pipeline/transforms/ |
| :language: en_US |
| :description: The Database Join transform allows you to run a query against a database using data from your pipelines fields as parameters.. |
| |
| = image:transforms/icons/dbjoin.svg[Database Join transform Icon, role="image-doc-icon"] Database Join |
| |
| [%noheader,cols="3a,1a", role="table-no-borders" ] |
| |=== |
| | |
| == Description |
| |
| The Database Join transform allows you to run a query against a database using data obtained from previous transforms. |
| |
| | |
| == Supported Engines |
| [%noheader,cols="2,1a",frame=none, role="table-supported-engines"] |
| !=== |
| !Hop Engine! image:check_mark.svg[Supported, 24] |
| !Spark! image:question_mark.svg[Maybe Supported, 24] |
| !Flink! image:question_mark.svg[Maybe Supported, 24] |
| !Dataflow! image:question_mark.svg[Maybe Supported, 24] |
| !=== |
| |=== |
| |
| == Usage |
| |
| The parameters for this query are specified as follows: |
| |
| The data grid in the transform properties dialog. |
| This allows you to select the data coming in from the source hop. |
| As question marks (?) in the SQL query. |
| When the transform runs, these will be replaced with data coming in from the fields defined from the data grid. |
| The question marks will be replaced in the same order as defined in the data grid. |
| For example, Database Join allows you to run queries looking up the oldest person who bought a specific product as shown below: |
| |
| TIP: the Database Join transform gives you more flexibility than the standard xref:pipeline/transforms/databaselookup.adoc[Database Lookup] transform. Keep in mind that your query determines the performance of this transform. |
| |
| [source,sql] |
| ---- |
| SELECT customernr |
| FROM product_orders, customer |
| WHERE orders.customernr = customer.customernr |
| AND orders.productnr = ? |
| ORDER BY customer.date_of_birth |
| ---- |
| |
| The grid is then defined as follows: |
| |
| image::transforms/databasejoin/databasejoin-grid.png[] |
| |
| When the transform runs, the (?) placeholder defined in the SQL query will be replaced with the incoming productnr field value from the source hop. |
| To define and use multiple parameters, list the fields in order you want them to be replaced in the SQL statement. |
| |
| == Options |
| |
| [options="header"] |
| |=== |
| |Option|Description |
| |Transform name|Name of the transform; This name has to be unique in a single pipeline |
| |Connection|The database connection to use for the query. |
| |SQL|SQL query to form the join; use question marks as parameter placeholders |
| |Number of rows to return|Zero (0) returns all rows; any other number limits the number of rows returned. |
| |Outer join?|Enable to always return a result, even if the query did not return a result |
| |Parameters table|Specify the fields containing parameters. |
| The parameter type is required. |
| |=== |