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).