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

     1  ---
     2  title: sql_raw
     3  type: processor
     4  status: stable
     5  categories: ["Integration"]
     6  ---
     7  
     8  <!--
     9       THIS FILE IS AUTOGENERATED!
    10  
    11       To make changes please edit the contents of:
    12       lib/processor/sql_raw.go
    13  -->
    14  
    15  import Tabs from '@theme/Tabs';
    16  import TabItem from '@theme/TabItem';
    17  
    18  Runs an arbitrary SQL query against a database and (optionally) returns the result as an array of objects, one for each row returned.
    19  
    20  Introduced in version 3.65.0.
    21  
    22  
    23  <Tabs defaultValue="common" values={[
    24    { label: 'Common', value: 'common', },
    25    { label: 'Advanced', value: 'advanced', },
    26  ]}>
    27  
    28  <TabItem value="common">
    29  
    30  ```yaml
    31  # Common config fields, showing default values
    32  label: ""
    33  sql_raw:
    34    driver: ""
    35    dsn: ""
    36    query: ""
    37    args_mapping: ""
    38    exec_only: false
    39  ```
    40  
    41  </TabItem>
    42  <TabItem value="advanced">
    43  
    44  ```yaml
    45  # All config fields, showing default values
    46  label: ""
    47  sql_raw:
    48    driver: ""
    49    dsn: ""
    50    query: ""
    51    unsafe_dynamic_query: false
    52    args_mapping: ""
    53    exec_only: false
    54  ```
    55  
    56  </TabItem>
    57  </Tabs>
    58  
    59  If the query fails to execute then the message will remain unchanged and the error can be caught using error handling methods outlined [here](/docs/configuration/error_handling).
    60  
    61  ## Examples
    62  
    63  <Tabs defaultValue="Table Insert (MySQL)" values={[
    64  { label: 'Table Insert (MySQL)', value: 'Table Insert (MySQL)', },
    65  { label: 'Table Query (PostgreSQL)', value: 'Table Query (PostgreSQL)', },
    66  ]}>
    67  
    68  <TabItem value="Table Insert (MySQL)">
    69  
    70  The following example inserts rows into the table footable with the columns foo, bar and baz populated with values extracted from messages.
    71  
    72  ```yaml
    73  pipeline:
    74    processors:
    75      - sql_raw:
    76          driver: mysql
    77          dsn: foouser:foopassword@tcp(localhost:3306)/foodb
    78          query: "INSERT INTO footable (foo, bar, baz) VALUES (?, ?, ?);"
    79          args_mapping: '[ document.foo, document.bar, meta("kafka_topic") ]'
    80          exec_only: true
    81  ```
    82  
    83  </TabItem>
    84  <TabItem value="Table Query (PostgreSQL)">
    85  
    86  Here we query a database for columns of footable that share a `user_id` with the message field `user.id`. A [`branch` processor](/docs/components/processors/branch) is used in order to insert the resulting array into the original message at the path `foo_rows`.
    87  
    88  ```yaml
    89  pipeline:
    90    processors:
    91      - branch:
    92          processors:
    93            - sql_raw:
    94                driver: postgres
    95                dsn: postgres://foouser:foopass@localhost:5432/testdb?sslmode=disable
    96                query: "SELECT * FROM footable WHERE user_id = $1;"
    97                args_mapping: '[ this.user.id ]'
    98          result_map: 'root.foo_rows = this'
    99  ```
   100  
   101  </TabItem>
   102  </Tabs>
   103  
   104  ## Fields
   105  
   106  ### `driver`
   107  
   108  A database [driver](#drivers) to use.
   109  
   110  
   111  Type: `string`  
   112  Options: `mysql`, `postgres`, `clickhouse`, `mssql`.
   113  
   114  ### `dsn`
   115  
   116  A Data Source Name to identify the target database.
   117  
   118  #### Drivers
   119  
   120  The following is a list of supported drivers and their respective DSN formats:
   121  
   122  | Driver | Data Source Name Format |
   123  |---|---|
   124  | `clickhouse` | [`tcp://[netloc][:port][?param1=value1&...&paramN=valueN]`](https://github.com/ClickHouse/clickhouse-go#dsn)
   125  | `mysql` | `[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]` |
   126  | `postgres` | `postgres://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]` |
   127  | `mssql` | `sqlserver://[user[:password]@][netloc][:port][?database=dbname&param1=value1&...]` |
   128  
   129  Please note that the `postgres` driver enforces SSL by default, you
   130  can override this with the parameter `sslmode=disable` if required.
   131  
   132  
   133  Type: `string`  
   134  
   135  ```yaml
   136  # Examples
   137  
   138  dsn: tcp://host1:9000?username=user&password=qwerty&database=clicks&read_timeout=10&write_timeout=20&alt_hosts=host2:9000,host3:9000
   139  
   140  dsn: foouser:foopassword@tcp(localhost:3306)/foodb
   141  
   142  dsn: postgres://foouser:foopass@localhost:5432/foodb?sslmode=disable
   143  ```
   144  
   145  ### `query`
   146  
   147  The query to execute.
   148  
   149  
   150  Type: `string`  
   151  
   152  ```yaml
   153  # Examples
   154  
   155  query: INSERT INTO footable (foo, bar, baz) VALUES (?, ?, ?);
   156  ```
   157  
   158  ### `unsafe_dynamic_query`
   159  
   160  Whether to enable [interpolation functions](/docs/configuration/interpolation/#bloblang-queries) in the query. Great care should be made to ensure your queries are defended against injection attacks.
   161  
   162  
   163  Type: `bool`  
   164  Default: `false`  
   165  
   166  ### `args_mapping`
   167  
   168  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 `query`.
   169  
   170  
   171  Type: `string`  
   172  
   173  ```yaml
   174  # Examples
   175  
   176  args_mapping: root = [ this.cat.meow, this.doc.woofs[0] ]
   177  
   178  args_mapping: root = [ meta("user.id") ]
   179  ```
   180  
   181  ### `exec_only`
   182  
   183  Whether the query result should be discarded. When set to `true` the message contents will remain unchanged, which is useful in cases where you are executing inserts, updates, etc.
   184  
   185  
   186  Type: `bool`  
   187  Default: `false`  
   188  
   189