mc sql
Syntax
The mc sql
command provides an S3 Select interface for performing sql
queries on objects in the specified MinIO deployment.
See Selecting content from objects for more information on S3 Select behavior and limitations.
The following command queries all objects in the
mydata
bucket on the myminio
MinIO deployment:
mc sql --recursive --query "select * from S3Object" myminio/mydata
The command has the following syntax:
mc [GLOBALFLAGS] mc sql \
--query "string" \
[--csv-input "string"] \
[--compression "string"] \
[--csv-output "string"] \
[--csv-output-header "string"] \
[--encrypt-key "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-seperatedkey=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 MinIO backend only:
- --csv-output
Optional The data format for
.csv
output. Specify a string of comma-seperatedkey=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 asfield1,field2,...
.Omit to output a
.csv
with no header row.
- --encrypt-key
Optional The encryption key to use for performing Server-Side Encryption with Client Keys (SSE-C). Specify comma seperated key-value pairs as
KEY=VALUE,...
.For
KEY
, specify the MinIO deploymentalias
and full path to the bucket, including any bucket prefixes. Separate the alias and bucket path with a forward slash\
. For example,play/mybucket
For
VALUE
, specify the data key to use for encryption object(s) in the bucket or bucket prefix specified toKEY
.
--encrypt-key
can use theMC_ENCRYPT_KEY
environment variable for populating the list of encryption key-value pairs as an alternative to specifying them on the command line.
- --json-input
Optional The data format for
.json
input objects. Specify the type of the JSON contents astype=<VALUE>
. The value can be either:See the S3 API JSONInput for more information.
- --json-output
Optional The data format for the
.json
output. Supports therd=value
key, whererd
is theRecordDelimiter
for the JSON document.Omit to use the default newline character
\n
.See the S3 API JSONOutput for more information.
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
Run an Aggregation Query on an Object
Use mc sql
with the --query
option to query
an object on an MinIO deployment:
mc sql --query "select count(s.power) from S3Object" ALIAS/PATH
Behavior
Input Formats
mc sql
supports the following input formats:
.csv
.json
.parquet
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.
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 |
|
Description |
---|---|---|
|
The character that seperates each record (row) in the input Corresponds to |
|
|
The character that seperates each field in a record. Defaults to
Corresponds to |
|
|
The character used for escaping when the Corresponds to |
|
|
The character used for escaping a quotation mark Corresponds to |
|
|
Yes |
The content of the first line in the Specify one of the following supported values:
For For Corresponds to |
|
Yes |
The character used to indicate a record should be ignored. The character must appear at the beginning of the record. Corresponds to |
|
Yes |
Specify Defaults to Corresponds to |
S3 Compatibility
The mc commandline tool is built for compatibility with the AWS S3 API and is tested with MinIO and AWS S3 for expected functionality and behavior.
MinIO 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.