| <!DOCTYPE html> |
| <html lang="en" xmlns="http://www.w3.org/1999/html"> |
| <!-- |
| 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. |
| --> |
| |
| <head> |
| <meta charset="utf-8"/> |
| <title>DatabaseParameterProvider</title> |
| <link rel="stylesheet" href="../../../../../css/component-usage.css" type="text/css"/> |
| </head> |
| <body> |
| |
| <h1>Providing Parameters from a Database</h1> |
| |
| <p> |
| The DatabaseParameterProvider at its core maps database rows to Parameters, specified by a |
| Parameter Name Column and Parameter Value Column. The Parameter Group name must also be accounted for, and may |
| be specified in different ways using the Parameter Grouping Strategy. |
| </p> |
| |
| <p> |
| Before discussing the actual configuration, note that in some databases, the words 'PARAMETER', 'PARAMETERS', 'GROUP', |
| and even 'VALUE' are reserved words. If you choose a column name that is a reserved word in the database you are using, |
| make sure to quote it per the database documentation. |
| </p> |
| |
| <p> |
| Also note that you should use the preferred table name and column name case for your database. For example, Postgres |
| prefers lowercase table and column names, while Oracle prefers capitalized ones. Choosing the appropriate case can |
| avoid unexpected issues in configuring your DatabaseParameterProvider. |
| </p> |
| |
| <p> |
| The default configuration uses a fully column-based approach, with the Parameter Group Name |
| also specified by columns in the same table. An example of a table using this configuration would be: |
| </p> |
| <table> |
| <thead> |
| <tr> |
| <th colspan="4" style="text-align: center">PARAMETER_CONTEXTS</th> |
| </tr> |
| <tr> |
| <th>PARAMETER_NAME</th><th>PARAMETER_VALUE</th><th>PARAMETER_GROUP</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td>param.foo</td><td>value-foo</td><td>group_1</td> |
| </tr> |
| <tr> |
| <td>param.bar</td><td>value-bar</td><td>group_1</td> |
| </tr> |
| <tr> |
| <td>param.one</td><td>value-one</td><td>group_2</td> |
| </tr> |
| <tr> |
| <td>param.two</td><td>value-two</td><td>group_2</td> |
| </tr> |
| </tbody> |
| <caption>Table 1: Database table example with Grouping Strategy = Column</caption> |
| </table> |
| |
| <p> |
| In order to use the data from this table, set the following Properties: |
| </p> |
| |
| <ul> |
| <li><b>Parameter Grouping Strategy</b> - Column</li> |
| <li><b>Table Name</b> - PARAMETER_CONTEXTS</li> |
| <li><b>Parameter Name Column</b> - PARAMETER_NAME</li> |
| <li><b>Parameter Value Column</b> - PARAMETER_VALUE</li> |
| <li><b>Parameter Group Name Column</b> - PARAMETER_GROUP</li> |
| </ul> |
| |
| <p> |
| Once fetched, the parameters in this example will look like this: |
| </p> |
| |
| <p> |
| Parameter Group <b>group_1</b>: |
| <ul> |
| <li>param.foo - value-foo</li> |
| <li>param.bar - value-bar</li> |
| </ul> |
| </p> |
| |
| <p> |
| Parameter Group <b>group_2</b>: |
| <ul> |
| <li>param.one - value-one</li> |
| <li>param.two - value-two</li> |
| </ul> |
| </p> |
| |
| <h3>Grouping Strategy</h3> |
| |
| <p> |
| The default Grouping Strategy is by Column, which allows you to specify the parameter Group name explicitly in the Parameter Group Column. |
| Note that if the value in this column is NULL, an exception will be thrown. |
| </p> |
| |
| <p> |
| The other Grouping Strategy is by Table, which maps each table to a Parameter Group and sets the Parameter Group Name to the table name. |
| In this Grouping Strategy, the Parameter Group Column is not used. An example configuration using this strategy would be: |
| </p> |
| |
| <ul> |
| <li><b>Parameter Grouping Strategy</b> - Table</li> |
| <li><b>Table Names</b> - KAFKA, S3</li> |
| <li><b>Parameter Name Column</b> - PARAMETER_NAME</li> |
| <li><b>Parameter Value Column</b> - PARAMETER_VALUE</li> |
| </ul> |
| |
| <p> |
| An example of some tables that may be used with this strategy: |
| </p> |
| |
| <table> |
| <thead> |
| <tr> |
| <th colspan="3" style="text-align: center">KAFKA</th> |
| </tr> |
| <tr> |
| <th>PARAMETER_NAME</th><th>PARAMETER_VALUE</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td>brokers</td><td>http://localhost:9092</td> |
| </tr> |
| <tr> |
| <td>topic</td><td>my-topic</td> |
| </tr> |
| <tr> |
| <td>password</td><td>my-password</td> |
| </tr> |
| </tbody> |
| <caption>Table 2: 'KAFKA' Database table example with Grouping Strategy = Table</caption> |
| </table> |
| |
| <table> |
| <thead> |
| <tr> |
| <th colspan="3" style="text-align: center">S3</th> |
| </tr> |
| <tr> |
| <th>PARAMETER_NAME</th><th>PARAMETER_VALUE</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td>bucket</td><td>my-bucket</td> |
| </tr> |
| <tr> |
| <td>secret.access.key</td><td>my-key</td> |
| </tr> |
| </tbody> |
| <caption>Table 3: 'S3' Database table example with Grouping Strategy = Table</caption> |
| </table> |
| |
| <p> |
| Once fetched, the parameters in this example will look like this: |
| </p> |
| |
| <p> |
| Parameter Group <b>KAFKA</b>: |
| <ul> |
| <li>brokers - http://localhost:9092</li> |
| <li>topic - my-topic</li> |
| <li>password - my-password</li> |
| </ul> |
| </p> |
| |
| <p> |
| Parameter Group <b>S3</b>: |
| <ul> |
| <li>bucket - my-bucket</li> |
| <li>secret.access.key - my-key</li> |
| </ul> |
| </p> |
| |
| <h3>Filtering rows</h3> |
| |
| <p> |
| If you need to include only some rows in a table as parameters, you can use the 'SQL WHERE clause' property. An example of this is as follows: |
| </p> |
| |
| <ul> |
| <li><b>Parameter Grouping Strategy</b> - Table</li> |
| <li><b>Table Names</b> - KAFKA, S3</li> |
| <li><b>Parameter Name Column</b> - PARAMETER_NAME</li> |
| <li><b>Parameter Value Column</b> - PARAMETER_VALUE</li> |
| <li><b>SQL WHERE clause</b> - OTHER_COLUMN = 'my-parameters'</li> |
| </ul> |
| |
| <p> |
| Here we are assuming there is another column, 'OTHER_COLUMN' in both the KAFKA and S3 tables. Only rows whose 'OTHER_COLUMN' value is 'my-parameters' |
| will then be fetched from these tables. |
| </p> |
| |
| </body> |
| </html> |
