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