github.com/Jeffail/benthos/v3@v3.65.0/website/docs/components/inputs/sql_select.md (about)

     1  ---
     2  title: sql_select
     3  type: input
     4  status: experimental
     5  categories: ["Services"]
     6  ---
     7  
     8  <!--
     9       THIS FILE IS AUTOGENERATED!
    10  
    11       To make changes please edit the contents of:
    12       lib/input/sql_select.go
    13  -->
    14  
    15  import Tabs from '@theme/Tabs';
    16  import TabItem from '@theme/TabItem';
    17  
    18  :::caution EXPERIMENTAL
    19  This component is experimental and therefore subject to change or removal outside of major version releases.
    20  :::
    21  Executes a select query and creates a message for each row received.
    22  
    23  Introduced in version 3.59.0.
    24  
    25  
    26  <Tabs defaultValue="common" values={[
    27    { label: 'Common', value: 'common', },
    28    { label: 'Advanced', value: 'advanced', },
    29  ]}>
    30  
    31  <TabItem value="common">
    32  
    33  ```yaml
    34  # Common config fields, showing default values
    35  input:
    36    label: ""
    37    sql_select:
    38      driver: ""
    39      dsn: ""
    40      table: ""
    41      columns: []
    42      where: ""
    43      args_mapping: ""
    44  ```
    45  
    46  </TabItem>
    47  <TabItem value="advanced">
    48  
    49  ```yaml
    50  # All config fields, showing default values
    51  input:
    52    label: ""
    53    sql_select:
    54      driver: ""
    55      dsn: ""
    56      table: ""
    57      columns: []
    58      where: ""
    59      args_mapping: ""
    60      prefix: ""
    61      suffix: ""
    62  ```
    63  
    64  </TabItem>
    65  </Tabs>
    66  
    67  Once the rows from the query are exhausted this input shuts down, allowing the pipeline to gracefully terminate (or the next input in a [sequence](/docs/components/inputs/sequence) to execute).
    68  
    69  ## Examples
    70  
    71  <Tabs defaultValue="Consume a Table (PostgreSQL)" values={[
    72  { label: 'Consume a Table (PostgreSQL)', value: 'Consume a Table (PostgreSQL)', },
    73  ]}>
    74  
    75  <TabItem value="Consume a Table (PostgreSQL)">
    76  
    77  
    78  Here we define a pipeline that will consume all rows from a table created within the last hour by comparing the unix timestamp stored in the row column "created_at":
    79  
    80  ```yaml
    81  input:
    82    sql_select:
    83      driver: postgres
    84      dsn: postgres://foouser:foopass@localhost:5432/testdb?sslmode=disable
    85      table: footable
    86      columns: [ '*' ]
    87      where: created_at >= ?
    88      args_mapping: |
    89        root = [
    90          now().format_timestamp_unix() - 3600
    91        ]
    92  ```
    93  
    94  </TabItem>
    95  </Tabs>
    96  
    97  ## Fields
    98  
    99  ### `driver`
   100  
   101  A database [driver](#drivers) to use.
   102  
   103  
   104  Type: `string`  
   105  Options: `mysql`, `postgres`, `clickhouse`, `mssql`.
   106  
   107  ### `dsn`
   108  
   109  A Data Source Name to identify the target database.
   110  
   111  #### Drivers
   112  
   113  The following is a list of supported drivers and their respective DSN formats:
   114  
   115  | Driver | Data Source Name Format |
   116  |---|---|
   117  | `clickhouse` | [`tcp://[netloc][:port][?param1=value1&...&paramN=valueN]`](https://github.com/ClickHouse/clickhouse-go#dsn)
   118  | `mysql` | `[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]` |
   119  | `postgres` | `postgres://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]` |
   120  | `mssql` | `sqlserver://[user[:password]@][netloc][:port][?database=dbname&param1=value1&...]` |
   121  
   122  Please note that the `postgres` driver enforces SSL by default, you
   123  can override this with the parameter `sslmode=disable` if required.
   124  
   125  
   126  Type: `string`  
   127  
   128  ```yaml
   129  # Examples
   130  
   131  dsn: tcp://host1:9000?username=user&password=qwerty&database=clicks&read_timeout=10&write_timeout=20&alt_hosts=host2:9000,host3:9000
   132  
   133  dsn: foouser:foopassword@tcp(localhost:3306)/foodb
   134  
   135  dsn: postgres://foouser:foopass@localhost:5432/foodb?sslmode=disable
   136  ```
   137  
   138  ### `table`
   139  
   140  The table to select from.
   141  
   142  
   143  Type: `string`  
   144  
   145  ```yaml
   146  # Examples
   147  
   148  table: foo
   149  ```
   150  
   151  ### `columns`
   152  
   153  A list of columns to select.
   154  
   155  
   156  Type: `array`  
   157  
   158  ```yaml
   159  # Examples
   160  
   161  columns:
   162    - '*'
   163  
   164  columns:
   165    - foo
   166    - bar
   167    - baz
   168  ```
   169  
   170  ### `where`
   171  
   172  An optional where clause to add. Placeholder arguments are populated with the `args_mapping` field. Placeholders should always be question marks, and will automatically be converted to dollar syntax when the postgres driver is used.
   173  
   174  
   175  Type: `string`  
   176  
   177  ```yaml
   178  # Examples
   179  
   180  where: type = ? and created_at > ?
   181  
   182  where: user_id = ?
   183  ```
   184  
   185  ### `args_mapping`
   186  
   187  An optional [Bloblang mapping](/docs/guides/bloblang/about) which should evaluate to an array of values matching in size to the number of placeholder arguments in the field `where`.
   188  
   189  
   190  Type: `string`  
   191  
   192  ```yaml
   193  # Examples
   194  
   195  args_mapping: root = [ "article", now().format_timestamp("2006-01-02") ]
   196  ```
   197  
   198  ### `prefix`
   199  
   200  An optional prefix to prepend to the select query (before SELECT).
   201  
   202  
   203  Type: `string`  
   204  
   205  ### `suffix`
   206  
   207  An optional suffix to append to the select query.
   208  
   209  
   210  Type: `string`  
   211  
   212