docs/source/minio-cli/minio-mc/mc-sql.rst

mc sql

minio

Table of Contents

mc sql

Description

The mc sql command provides an S3 Select interface for performing sql queries on objects in the specified S3-compatible service.

See Selecting content from objects <selecting-content-from-objects> for more information on S3 Select behavior and limitations.

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 mc-sql-csv-format 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 ~mc sql csv-input See the documentation for S3 API CSVInput <API_CSVInput.html> for more information on S3 CSV formatting.

Key	--csv-input Only	Description
rd		The character that seperates each record (row) in the input .csv file. Corresponds to RecordDelimiter in the S3 API CSVInput.
fd		The character that seperates 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 ~mc sql query statement. For USE, you can specify header values to identify a column in the ~mc sql 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.

Examples

Select all Columns in all Objects in a Bucket

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

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

• Replace ALIAS <mc sql TARGET> with the alias <mc alias> of the S3-compatible host.
• Replace PATH <mc sql TARGET> with the path to the bucket on the S3-compatible host.

Run an Aggregation Query on an Object

Use mc sql with the ~mc sql query option to query an object on an S3-compatible host:

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

• Replace ALIAS <mc sql TARGET> with the alias <mc alias> of the S3-compatible host.
• Replace PATH <mc sql TARGET> with the path to the object on the S3-compatible host.

Syntax

mc sql has the following syntax:

mc sql [FLAGS] TARGET [TARGET...]

mc sql supports the following arguments:

TARGET

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

mc sql [FLAGS] play/mybucket

query, e

The SQL statement to execute on the specified ~mc sql TARGET directory or object. Wrap the entire SQL query in double quotes ".

Defaults to "select * from s3object".

recursive, r

Recursively searches the specified ~mc sql TARGET directory using the ~mc sql query SQL statement.

csv-input

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

json-input

The data format for .json 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 <API_JSONInput.html> for more information.

compression

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

• GZIP
• BZIP2
• NONE (default)

csv-output

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

See the S3 API CSVOutput <API_CSVOutput.html> for more information.

csv-output-header

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.

json-output

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

encrypt-key

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 S3-compatible service alias <mc alias> 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 to KEY.

~mc sql encrypt-key can use the MC_ENCRYPT_KEY environment variable for populating the list of encryption key-value pairs as an alternative to specifying them on the command line.