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&...¶mN=valueN]`](https://github.com/ClickHouse/clickhouse-go#dsn) 125 | `mysql` | `[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...¶mN=valueN]` | 126 | `postgres` | `postgres://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]` | 127 | `mssql` | `sqlserver://[user[:password]@][netloc][:port][?database=dbname¶m1=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