# mc sql

LLMS index: [llms.txt](/llms.txt)

---

<a id="mc-sql"></a>

<a id="command-mc.sql"></a>

## Syntax {#syntax}

The [`mc sql`](#command-mc.sql) command queries object contents through S3 Select. Use `mcli` in place of `mc` if that is the installed executable name.

See [Selecting content from objects](https://docs.aws.amazon.com/AmazonS3/latest/userguide/selecting-content-from-objects) for more information on S3 Select behavior and limitations.

**EXAMPLE**

Query all objects in the `mydata` bucket under the `mysilo` alias:

```shell
mc sql --recursive --query "select * from S3Object" mysilo/mydata
```

**SYNTAX**

The command has the following syntax:

```shell
mc [GLOBALFLAGS] sql                             \
                 [--query "string"]              \
                 [--csv-input "string"]          \
                 [--compression "string"]        \
                 [--csv-output "string"]         \
                 [--csv-output-header "string"]  \
                 [--enc-c "string"]              \
                 [--json-input "string"]         \
                 [--json-output "string"]        \
                 [--recursive]                   \
                 ALIAS
```

- Brackets `[]` indicate optional parameters.
- Parameters sharing a line are mutually dependent.
- Parameters separated using the pipe `|` operator are mutually exclusive.

Copy the example to a text editor and modify as-needed before running the command in the terminal/shell.

### Parameters {#parameters}

##### `ALIAS` {#mc.sql.ALIAS}

*mc-cmd*

*Required*

The full path to the bucket or object to run the SQL query against. Specify the [alias](/reference/minio-mc/mc-alias-set/#alias) of a configured S3 service as the prefix to the `ALIAS` path. For example:

```shell
mc sql [FLAGS] mysilo/mybucket
```

##### `--query, -e` {#mc.sql.-query}

*mc-cmd*

*Optional*

The SQL statement to execute on the specified [`ALIAS`](#mc.sql.ALIAS) directory or object. Wrap the entire SQL query in double quotes `"`.

Defaults to `"select * from S3Object"`.

##### `--csv-input` {#mc.sql.-csv-input}

*mc-cmd*

*Optional*

The data format for `.csv` input objects. Specify a string of comma-seperated `key=value,...` pairs. See [CSV Formatting Fields](#mc-sql-csv-format) for more information on valid keys.

##### `--compression` {#mc.sql.-compression}

*mc-cmd*

*Optional*

The compression type of the input object. Specify one of the following supported values:

- `GZIP`
- `BZIP2`
- `NONE` (default)

Compression schemes supported by MinIO backend only:

- `ZSTD` [Zstandard](https://facebook.github.io/zstd/)
- `LZ4` [LZ4](https://lz4.github.io/lz4/) stream
- `S2` [S2](https://github.com/klauspost/compress/tree/master/s2#s2-compression) framed stream
- `SNAPPY` [Snappy](http://google.github.io/snappy/) framed stream

##### `--csv-output` {#mc.sql.-csv-output}

*mc-cmd*

*Optional*

The data format for `.csv` output. Specify a string of comma-seperated `key=value,...` pairs. See [CSV Formatting Fields](#mc-sql-csv-format) for more information on valid keys.

See the S3 API [CSVOutput](https://docs.aws.amazon.com/AmazonS3/latest/API/API_CSVOutput.html) for more information.

##### `--csv-output-header` {#mc.sql.-csv-output-header}

*mc-cmd*

*Optional*

The header row of the `.csv` output file. Specify a string of comma-separated fields as `field1,field2,...`.

Omit to output a `.csv` with no header row.

##### `--enc-c` {#mc.sql.-enc-c}

*mc-cmd*

*Optional*

Encrypt or decrypt objects using server-side [SSE-C encryption](/administration/server-side-encryption/#minio-sse) with client-managed keys.

The parameter accepts a key-value pair formatted as `KEY=VALUE`

<table>
  <tbody>
    <tr>
      <td><p><code>KEY</code></p></td>
      <td><p>The full path to the object as <code>alias/bucket/path/object.ext</code>.</p><p>You can specify only the top-level path to use a single encryption key for all operations in that path.</p></td>
    </tr>
    <tr>
      <td><p><code>VALUE</code></p></td>
      <td><p>Specify either a 32-byte RawBase64-encoded key <em>or</em> a 64-byte hex-encoded key for use with SSE-C encryption.</p><p>Raw Base64 encoding <strong>rejects</strong> <code>=</code>-padded keys.
Omit the padding or use a Base64 encoder that supports RAW formatting.</p></td>
    </tr>
  </tbody>
</table>

- `KEY` - the full path to the object as `alias/bucket/path/object`.
- `VALUE` - the 32-byte RAW Base64-encoded data key to use for encrypting object(s).

For example:

```shell
# RawBase64-Encoded string "mybucket32byteencryptionkeyssec"
--enc-c "mysilo/mybucket/prefix/object.obj=bXlidWNrZXQzMmJ5dGVlbmNyeXB0aW9ua2V5c3NlYwo"
```

You can specify multiple encryption keys by repeating the parameter.

Specify the path to a prefix to apply encryption to all matching objects at that path:

```shell
--enc-c "mysilo/mybucket/prefix/=bXlidWNrZXQzMmJ5dGVlbmNyeXB0aW9ua2V5c3NlYwo"
```

> [!NOTE]
> **Note**
>
> MinIO strongly recommends against using SSE-C encryption in production workloads. Use SSE-KMS via the `--enc-kms` or SSE-S3 via `--enc-s3` parameters instead.

##### `--json-input` {#mc.sql.-json-input}

*mc-cmd*

*Optional*

The data format for `.json` or `.ndjson` input objects. Specify the type of the JSON contents as `type=<VALUE>`. The value can be either:

- `DOCUMENT` - JSON [document](https://www.json.org/json-en.html).
- `LINES` - JSON [lines](http://jsonlines.org/).

See the S3 API [JSONInput](https://docs.aws.amazon.com/AmazonS3/latest/API/API_JSONInput.html) for more information.

##### `--json-output` {#mc.sql.-json-output}

*mc-cmd*

*Optional*

The data format for the `.json` output. Supports the `rd=value` key, where `rd` is the `RecordDelimiter` for the JSON document.

Omit to use the default newline character `\n`.

See the S3 API [JSONOutput](https://docs.aws.amazon.com/AmazonS3/latest/API/API_JSONOutput.html) for more information.

##### `--recursive, r` {#mc.sql.-recursive}

*mc-cmd*

*Optional*

Recursively searches the specified [`ALIAS`](#mc.sql.ALIAS) directory using the [`--query`](#mc.sql.-query) SQL statement.

### Global Flags {#global-flags}

This command supports any of the [global flags](/reference/minio-mc/#minio-mc-global-options).

## Examples {#examples}

### Select all Columns in all Objects in a Bucket {#select-all-columns-in-all-objects-in-a-bucket}

Use [`mc sql`](#command-mc.sql) with the [`--recursive`](#mc.sql.-recursive) and [`--query`](#mc.sql.-query) options to apply the query to all objects in a bucket:

```shell
mc sql --recursive --query "select * from S3Object" ALIAS/PATH
```

- Replace [`ALIAS`](#mc.sql.ALIAS) with the [alias](/reference/minio-mc/mc-alias-set/#alias) of the MinIO deployment.
- Replace [`PATH`](#mc.sql.ALIAS) with the path to the bucket on the MinIO deployment.

### Run an Aggregation Query on an Object {#run-an-aggregation-query-on-an-object}

Use [`mc sql`](#command-mc.sql) with the [`--query`](#mc.sql.-query) option to query an object on an MinIO deployment:

```shell
mc sql --query "select count(s.power) from S3Object" ALIAS/PATH
```

- Replace [`ALIAS`](#mc.sql.ALIAS) with the [alias](/reference/minio-mc/mc-alias-set/#alias) of the MinIO deployment.
- Replace [`PATH`](#mc.sql.ALIAS) with the path to the object on the MinIO deployment.

## Behavior {#behavior}

### Input Formats {#input-formats}

[`mc sql`](#command-mc.sql) supports the following input formats:

| Type | `content-type` Value |
| --- | --- |
| `.csv` | `text/csv` |
| `.json` | `application/json` |
| `.parquet` | none |

For `.csv` file types, use [`mc sql --csv-input`](#mc.sql.-csv-input) to specify the CSV data format. See [CSV Formatting Fields](#mc-sql-csv-format) for more information on CSV formatting fields.

For `.json` file types, use [`mc sql --json-input`](#mc.sql.-json-input) to specify the JSON data format.

For `.parquet` file types, [`mc sql`](#command-mc.sql) automatically interprets the data format.

[`mc sql`](#command-mc.sql) determines the type by the file extension of the target object. For example, an object named `data.json` is interpreted as a JSON file.

You can query data of a supported type but a different extension if the object has the appropriate `content-type`. For more information, see [`mc cp --attr`](/reference/minio-mc/mc-cp/#mc.cp.-attr).

<a id="mc-sql-csv-format"></a>

### CSV Formatting Fields {#csv-formatting-fields}

The following table lists valid key-value pairs for use with [`mc sql --csv-input`](#mc.sql.-csv-input) and [`mc sql --csv-output`](#mc.sql.-csv-output). Certain key pairs are only valid for [`--csv-input`](#mc.sql.-csv-input). See the documentation for S3 API [CSVInput](https://docs.aws.amazon.com/AmazonS3/latest/API/API_CSVInput.html) for more information on S3 CSV formatting.

<table>
  <thead>
    <tr>
      <th><p>Key</p></th>
      <th><p><code>--csv-input</code> Only</p></th>
      <th><p>Description</p></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td><p><code>rd</code></p></td>
      <td></td>
      <td><p>The character that seperates each record (row) in the input <code>.csv</code> file.</p><p>Corresponds to <code>RecordDelimiter</code> in the S3 API <code>CSVInput</code>.</p></td>
    </tr>
    <tr>
      <td><p><code>fd</code></p></td>
      <td></td>
      <td><p>The character that seperates each field in a record. Defaults to <code>,</code>.</p><p>Corresponds to <code>FieldDelimeter</code> in the S3 API <code>CSVInput</code>.</p></td>
    </tr>
    <tr>
      <td><p><code>qc</code></p></td>
      <td></td>
      <td><p>The character used for escaping when the <code>fd</code> character is part of a value. Defaults to <code>&quot;</code>.</p><p>Corresponds to <code>QuoteCharacter</code> in the S3 API <code>CSVInput</code>.</p></td>
    </tr>
    <tr>
      <td><p><code>qec</code></p></td>
      <td></td>
      <td><p>The character used for escaping a quotation mark <code>&quot;</code> character inside an already escaped value.</p><p>Corresponds to <code>QuoteEscapeCharacter</code> in the S3 API <code>CSVInput</code>.</p></td>
    </tr>
    <tr>
      <td><p><code>fh</code></p></td>
      <td><p>Yes</p></td>
      <td><p>The content of the first line in the <code>.csv</code> file.</p><p>Specify one of the following supported values:</p><ul><li><p><code>NONE</code> - The first line is not a header.</p></li><li><p><code>IGNORE</code> - Ignore the first line.</p></li><li><p><code>USE</code> - The first line is a header.</p></li></ul><p>For <code>NONE</code> or <code>IGNORE</code>, you must specify column positions <code>_#</code> to identify a column in the <a href="#mc.sql.-query"><code>--query</code></a> statement.</p><p>For <code>USE</code>, you can specify header values to identify a column in the <a href="#mc.sql.-query"><code>--query</code></a> statement.</p><p>Corresponds to <code>FieldHeaderInfo</code> in the S3 API <code>CSVInput</code>.</p></td>
    </tr>
    <tr>
      <td><p><code>cc</code></p></td>
      <td><p>Yes</p></td>
      <td><p>The character used to indicate a record should be ignored.
The character <em>must</em> appear at the beginning of the record.</p><p>Corresponds to <code>Comment</code> in the S3 API <code>CSVInput</code>.</p></td>
    </tr>
    <tr>
      <td><p><code>qrd</code></p></td>
      <td><p>Yes</p></td>
      <td><p>Specify <code>TRUE</code> to indicate that fields may contain record delimiter values (<code>rd</code>).</p><p>Defaults to <code>FALSE</code>.</p><p>Corresponds to <code>AllowQuotedRecordDelimiter</code> in the S3 API <code>CSVInput</code>.</p></td>
    </tr>
  </tbody>
</table>

### S3 Compatibility {#s3-compatibility}

Silo is the supported and release-tested server. Compatibility with upstream MinIO and other S3 services is best effort. Confirm that the target service supports S3 Select before using this command. See the [compatibility notes](/compatibility/mcli/).