mc sql

Runs SQL queries on objects.

Syntax

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

FLAGS:
  --query value, -e value       sql query expression (default: "select * from s3object")
  --recursive, -r               sql query recursively
  --csv-input value             csv input serialization option
  --json-input value            json input serialization option
  --compression value           input compression type
  --csv-output value            csv output serialization option
  --csv-output-header value     optional csv output header
  --json-output value           json output serialization option
  --json                        enable JSON lines formatted output
  --debug                       enable debug output
  --insecure                    disable SSL certificate verification
  --help, -h                    show help

SERIALIZATION OPTIONS:
  For query serialization options, refer to https://docs.min.io/docs/minio-client-complete-guide#sql

Parameters

Parameter Description
TARGET The alias of a configured HPE Ezmeral Object Store deployment on which the command runs SQL queries. This parameter is mandatory.
query The query to run.

The default query is select * from s3object
recursive Query all folders recursively instead of just the top-level folder.
csv-input The CSV input format.
json-input The JSON input format. Required when querying JSON documents.
compression Specifies if the queried object is compressed. Valid values are NONE | GZIP | BZIP2. Default value is NONE.
csv-output The format for CSV output.
csv-output-header The CSV output header. If not specified, the first row of the CSV is used as the header.
json-output The format for JSON output.
json Enable JSON formatted output.
debug Enable output for debugging.
insecure Disable SSL verification.
help Show this help.

Usage Notes

Review the following notes related to the use of the mc sql command before you run any queries:
Parquet files
Before you run any queries against Parquet files, set export MINIO_API_SELECT_PARQUET=on in the /opt/mapr/conf/env.sh file and restart the Object Store server. You can restart the Object Store server from the Services page in the Control System or from the CLI by running the following command:
/opt/mapr/bin/maprcli node services -nodes <space-delimited list of node names> -s3server restart
JSON documents
When you query a JSON document, you must include the --json-input parameter and type=document, as shown in the following example:
/opt/mapr/bin/mc sql --json-input type=document --query "select * from S3Object" alias0/mybucket/example5.json

Examples

  1. Query a set of objects recursively that are in a bucket named northamerica in the Object Store deployment with alias salesobject:
    CLI
    /opt/mapr/bin/mc sql --recursive --query "select * from S3Object" salesobject/northamerica/
  2. Query a compressed object books1.json.bz2 that is in bzip2 format present in the bucket named comics in the Object Store deployment with alias royallibrary:
    /opt/mapr/bin/mc sql --compression bzip2 --query "select id, cat from s3object" royallibrary/comics/books1.json.bz2
  3. Query the data.csv object in the lpd bucket for the alias powerconsumption. For the input, specify a semicolon (;) as the delimiter (fd), newline as the record delimiter (rd), and use file header (fh) in the query.
    CLI
    /opt/mapr/bin/mc sql --csv-input "rd=\n,fh=USE,fd=;" \
    --json-output "rd=\n\n" --query "select * from S3Object" powerconsumption/lpd/data.csv
  4. Query the data.csv object in the lpd bucket for the alias powerconsumption. For the input, specify a semicolon (;) as the delimiter (fd), newline as the record delimiter (rd), and use file header (fh) in the query. For the output, specify the CSV output header. When you specify the CSV output headers as "col1,col2,col3", the first row of the CSV file is interpreted as the header.
    CLI
    /opt/mapr/bin/mc sql --csv-input "rd=\n,fh=USE,fd=;" \
 --csv-output "rd=\n" --csv-output-header "device_id,uptime,lat,lon" \
 --query "select * from S3Object" powerconsumption/lpd/data.csv
  5. Query a JSON document type.
    CLI
    /opt/mapr/bin/mc sql --json-input type=document --query \
 "select * from S3Object" alias0/mybucket/example3.json

   {"owner":null,"brand":"BMW","year":2020,"status":false,"color":["red","white","yellow"],
   "Model":{"name":"BMW M4","Fuel Type":"Petrol","TransmissionType":"Automatic",
   "Turbo Charger":"true","Number o}

cat /tmp/example3.json

      {
         "owner": null,
         "brand": "BMW",
         "year": 2020,
         "status": false,
         "color": [
         "red",
         "white",
         "yellow"
       ],
         "Model": {
         "name": "BMW M4",
         "Fuel Type": "Petrol",
         "TransmissionType": "Automatic",
         "Turbo Charger": "true",
         "Number of Cylinder": 4
   }
}