mc sql

The mc sql command provides an S3 Select interface for performing sql queries on objects in the specified AIStor deployment.

See Selecting content from objects for more information on S3 Select behavior and limitations.

Syntax

Example Syntax

The command has the following syntax:

mc [GLOBALFLAGS] mc 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

ALIAS

Required

The full path to the bucket or object to run the SQL query against. Specify the alias of a configured S3 service as the prefix to the ALIAS path. For example:

mc sql [FLAGS] play/mybucket

--query, e

Required

The SQL statement to execute on the specified ALIAS directory or object. Wrap the entire SQL query in double quotes ".

Defaults to "select * from S3Object".

--csv-input

Optional

The data format for .csv input objects. Specify a string of comma-seperated key=value,... pairs. See CSV Formatting Fields for more information on valid keys.

--compression

Optional

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

GZIP
BZIP2
NONE (default)

Compression schemes supported by AIStor backend only:

ZSTD Zstandard
LZ4 LZ4 stream
S2 S2 framed stream
SNAPPY Snappy framed stream

--csv-output

Optional

The data format for .csv output. Specify a string of comma-seperated key=value,... pairs. See CSV Formatting Fields for more information on valid keys.

See the S3 API CSVOutput for more information.

--csv-output-header

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

Optional

Decrypt objects encrypted using server-side SSE-C encryption with client-managed keys.

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

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

For example:

# RawBase64-Encoded string "mybucket32byteencryptionkeyssec"
--enc-c "myminio/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:

--enc-c "myminio/mybucket/prefix/=bXlidWNrZXQzMmJ5dGVlbmNyeXB0aW9ua2V5c3NlYwo"

--json-input

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.
LINES - JSON lines.

See the S3 API JSONInput for more information.

--json-output

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 for more information.

--recursive

Alias: -r

Optional

Recursively searches the specified ALIAS directory using the --query SQL statement.

Global Flags

This command supports any of the global flags.

Examples

Select all Columns in all Objects in a Bucket

Use mc sql with the --recursive and --query options to apply the query to all objects in a bucket:

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

Replace ALIAS with the alias of the AIStor deployment.
Replace PATH with the path to the bucket on the AIStor deployment.

Run an Aggregation Query on an Object

Use mc sql with the --query option to query an object on an AIStor deployment:

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

Replace ALIAS with the alias of the AIStor deployment.
Replace PATH with the path to the object on the AIStor deployment.

Behavior

Input Formats

mc sql supports the following input formats:

Input Format Types

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

For .csv file types, use mc sql --csv-input to specify the CSV data format. See CSV Formatting Fields for more information on CSV formatting fields.
For .json file types, use mc sql --json-input to specify the JSON data format.
For .parquet file types, mc sql automatically interprets the data format.

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.

CSV Formatting Fields

The following table lists valid key-value pairs for use with mc sql --csv-input and mc sql --csv-output. Certain key pairs are only valid for --csv-input. See the documentation for S3 API CSVInput for more information on S3 CSV formatting.

Key	`--csv-input` Only?	Description
`rd`		The character that separates each record (row) in the input `.csv` file. Corresponds to `RecordDelimiter` in the S3 API `CSVInput`.
`fd`		The character that separates each field in a record. Defaults to `,`. Corresponds to `FieldDelimeter` in the S3 API `CSVInput`.
`qc`		The character used for escaping when the `fd` character is part of a value. Defaults to `"`. Corresponds to `QuoteCharacter` in the S3 API `CSVInput`.
`qec`		The character used for escaping a quotation mark `"` character inside an already escaped value. Corresponds to `QuoteEscapeCharacter` in the S3 API `CSVInput`.
`fh`	Yes	The content of the first line in the `.csv` file. Specify one of the following supported values: - `NONE` - The first line is not a header. - `IGNORE` - Ignore the first line. - `USE` - The first line is a header. For `NONE` or `IGNORE`, you must specify column positions `_#` to identify a column in the `--query` statement. For `USE`, you can specify header values to identify a column in the `--query` statement. Corresponds to `FieldHeaderInfo` in the S3 API `CSVInput`.
`cc`	Yes	The character used to indicate a record should be ignored. The character must appear at the beginning of the record. Corresponds to `Comment` in the S3 API `CSVInput`.
`qrd`	Yes	Specify `TRUE` to indicate that fields may contain record delimiter values (`rd`). Defaults to `FALSE`. Corresponds to `AllowQuotedRecordDelimiter` in the S3 API `CSVInput`.

S3 Compatibility

The mc commandline tool is built for compatibility with the AWS S3 API and is tested with AIStor and AWS S3 for expected functionality and behavior.

AIStor provides no guarantees for other S3-compatible services, As their S3 API implementation is unknown and therefore unsupported.

While mc commands may work as documented, any such usage is at your own risk.