Use the Snowflake SQL REST API from a shell script with curl and jq to execute a query and download the result partitions in Arrow format

request-body.json

{
  "statement": "SELECT * FROM MYTABLE",
  "resultSetMetaData": {
    "format": "arrowv1"
  },
  "timeout": 60,
  "database": "MYDATABASE",
  "schema": "MYSCHEMA",
  "warehouse": "MYWAREHOUSE",
  "role": "MYROLE"
}

script.sh

#!/bin/sh

account_ident="ACCOUNT-IDENT"
user="[email protected]"
private_key_path="/path/to/rsa_key.p8"

bearer_token=$(echo "" | snowsql --private-key-path $private_key_path --generate-jwt -a "$account_ident" -u "$user")

subdomain=$(echo "$account_ident" | tr '[:upper:]' '[:lower:]')

curl -o result-metadata.json -X POST \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "Authorization: Bearer $bearer_token" \
    -H "User-Agent: customApplication/1.0" \
    -H "X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT" \
    -d "@request-body.json" \
    "https://$subdomain.snowflakecomputing.com/api/v2/statements"

statement_handle=$(jq -r '.statementHandle' result-metadata.json)

partitions=$(jq '.resultSetMetaData.partitionInfo | length - 1' result-metadata.json)

curl --parallel --compressed $(seq -f "-o partition-%g.arrows" 1 $partitions) \
  -H "Accept: application/vnd.apache.arrow.stream" \
  -H "Authorization: Bearer $bearer_token" \
  -H "User-Agent: customApplication/1.0" \
  -H "X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT" \
  $(seq -f "https://$subdomain.snowflakecomputing.com/api/v2/statements/$statement_handle?partition=%g" 1 $partitions)

Also see the example code at apache/arrow#44561 (comment) which shows how to create a record batch reader (which enables lazy/deferred reading) instead of eagerly reading the files into a table.