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

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/
```
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
```
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
```
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
```

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
   }
}