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

request-body.json

{
  "statement": "SELECT * FROM MYTABLEONE; SELECT * FROM MYTABLETWO",
  "parameters": {
      "MULTI_STATEMENT_COUNT": "2"
  },
  "resultSetMetaData": {
    "format": "arrowv1"
  },
  "timeout": 60,
  "database": "MYDATABASE",
  "schema": "MYSCHEMA",
  "warehouse": "MYWAREHOUSE",
  "role": "MYROLE"
}

script.sh

#!/bin/sh

account_ident="ACCOUNT-IDENT"
user="USER"
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:]')
base_url="https://$subdomain.snowflakecomputing.com/api/v2/statements"

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" \
    $base_url

statements=$(jq '.statementHandles // [""] | length' result-metadata.json)

if [ "$statements" -gt 1 ]
then
    handles=$(jq -r '.statementHandles.[]' result-metadata.json)
    metadata_urls=$(print "$handles" | xargs -I{} printf "$base_url/%s\n" "{}")
    curl --parallel $(seq -f "-o result-metadata-statement-%g.json" 1 $statements) \
        -H "Accept: application/json" \
        -H "Authorization: Bearer $bearer_token" \
        -H "User-Agent: customApplication/1.0" \
        -H "X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT" \
        $(echo $metadata_urls)
    partitions=$(jq '.resultSetMetaData.partitionInfo | length - 1' \
        $(seq -f "result-metadata-statement-%g.json" 1 $statements) | paste -sd '\n' -)
    file_prefix="statement-\${i}-"
else 
    handles=$(jq -r '.statementHandle' result-metadata.json)
    partitions=$(jq '.resultSetMetaData.partitionInfo | length - 1' result-metadata.json)
    file_prefix=""
fi

data_files=$(paste <(seq 1 $statements) <(echo "$partitions") | \
    while read i j; do seq -f "-o $(eval echo $file_prefix)partition-%g.arrows" 1 $j; done)
data_urls=$(paste <(echo "$handles") <(echo "$partitions") | \
    while read s i; do seq -f "$base_url/${s}?partition=%g" 1 "$i"; done)

curl --parallel --compressed $(echo $data_files) \
  -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" \
  $(echo $data_urls)

Also see the simpler single-statement version of this at https://gist.github.com/ianmcook/1c4623c995202f98b289395cf63159f2