github.com/lmorg/murex@v0.0.0-20240217211045-e081c89cd4ef/docs/optional/select.md (about) 1 # `select` 2 3 > Inlining SQL into shell pipelines 4 5 ## Description 6 7 `select` imports tabulated data into an in memory sqlite3 database and 8 executes SQL queries against the data. It returns a table of the same 9 data type as the input type 10 11 ## Usage 12 13 ``` 14 <stdin> -> select * | ... WHERE ... -> <stdout> 15 16 select * | ... FROM file[.gz] WHERE ... -> <stdout> 17 ``` 18 19 ## Examples 20 21 List a count of all the processes running against each user ID: 22 23 ``` 24 » ps aux -> select count(*), user GROUP BY user ORDER BY 1 25 count(*) USER 26 1 _analyticsd 27 1 _applepay 28 1 _atsserver 29 1 _captiveagent 30 1 _cmiodalassistants 31 1 _ctkd 32 1 _datadetectors 33 1 _displaypolicyd 34 1 _distnote 35 1 _gamecontrollerd 36 1 _hidd 37 1 _iconservices 38 1 _installcoordinationd 39 1 _mdnsresponder 40 1 _netbios 41 1 _networkd 42 1 _reportmemoryexception 43 1 _timed 44 1 _usbmuxd 45 2 _appleevents 46 3 _assetcache 47 3 _fpsd 48 3 _nsurlsessiond 49 3 _softwareupdate 50 4 _windowserver 51 5 _coreaudiod 52 6 _spotlight 53 7 _locationd 54 144 root 55 308 foobar 56 ``` 57 58 ``` 59 60 select count(*) 61 ``` 62 63 ## Detail 64 65 ### Default Table Name 66 67 The table created is called `main`, however you do not need to include a `FROM` 68 condition in your SQL as Murex will inject `FROM main` into your SQL if it is 69 missing. In fact, it is recommended that you exclude `FROM` from your SQL 70 queries for the sake of brevity. 71 72 ### `config` Options 73 74 `select`'s behavior is configurable: 75 76 ``` 77 » config -> [ select ] 78 { 79 "fail-irregular-columns": { 80 "Data-Type": "bool", 81 "Default": false, 82 "Description": "When importing a table into sqlite3, fail if there is an irregular number of columns", 83 "Dynamic": false, 84 "Global": false, 85 "Value": false 86 }, 87 "merge-trailing-columns": { 88 "Data-Type": "bool", 89 "Default": true, 90 "Description": "When importing a table into sqlite3, if `fail-irregular-columns` is set to `false` and there are more columns than headings, then any additional columns are concatenated into the last column (space delimitated). If `merge-trailing-columns` is set to `false` then any trailing columns are ignored", 91 "Dynamic": false, 92 "Global": false, 93 "Value": true 94 }, 95 "print-headings": { 96 "Data-Type": "bool", 97 "Default": true, 98 "Description": "Print headings when writing results", 99 "Dynamic": false, 100 "Global": false, 101 "Value": true 102 }, 103 "table-includes-headings": { 104 "Data-Type": "bool", 105 "Default": true, 106 "Description": "When importing a table into sqlite3, treat the first row as headings (if `false`, headings are Excel style column references starting at `A`)", 107 "Dynamic": false, 108 "Global": false, 109 "Value": true 110 } 111 } 112 ``` 113 114 (See below for how to use `config`) 115 116 ### Read All vs Sequential Reads 117 118 At present, `select` only supports reading the entire table from STDIN before 119 importing that data into sqlite3. There is some prototype code being written to 120 support sequential imports but this is hugely experimental and not yet enabled. 121 122 This might make `select` unsuitable for large datasets. 123 124 ### Early Release 125 126 This is a very early release so there almost certainly will be bugs hiding. 127 Which is another reason why this is currently only an optional builtin. 128 129 If you do run into any issues then please raise them on [Github](https://github.com/lmorg/murex/issues). 130 131 ## Synonyms 132 133 * `select` 134 135 136 ## See Also 137 138 * [`*` (generic)](../types/generic.md): 139 generic (primitive) 140 * [`config`](../commands/config.md): 141 Query or define Murex runtime settings 142 * [`csv`](../types/csv.md): 143 CSV files (and other character delimited tables) 144 * [`jsonl`](../types/jsonl.md): 145 JSON Lines 146 * [v2.1](../changelog/v2.1.md): 147 This release comes with support for inlining SQL and some major bug fixes plus a breaking change for `config`. Please read for details. 148 149 <hr/> 150 151 This document was generated from [builtins/optional/select/select_doc.yaml](https://github.com/lmorg/murex/blob/master/builtins/optional/select/select_doc.yaml).