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)

for partition in $(seq 1 $partitions)
do
  curl -o partition-$partition.arrows.gz -X GET \
    -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" \
    "https://$subdomain.snowflakecomputing.com/api/v2/statements/$statement_handle?partition=$partition"
  if ! gunzip -q partition-$partition.arrows.gz; then
    mv partition-$partition.arrows.gz partition-$partition.arrows
  fi
done