AWS Athena - Export Named Queries

athena_export_queries.sh

#!/usr/bin/env bash

WORKGROUP=inspector # Optional

if [ -n "${WORKGROUP}" ]; then
  mkdir -p ${WORKGROUP}
  WG="--work-group ${WORKGROUP}"
  DIR="${WORKGROUP}"
else 
  WG=""
  DIR="."
fi

for nq in $(aws athena list-named-queries ${WG} --query 'NamedQueryIds' --out text); do
  json=$(aws athena get-named-query --named-query-id ${nq} --query 'NamedQuery.[Name,QueryString]' --out json)
  name=$(echo ${json} | jq -r '.[0]')

  # Replace spaces and hyphens with underscores
  name=$(echo ${name} | tr ' -' '_')
  echo "Exporting ${name}..."
  echo ${json} | jq -r '.[1]' > "${DIR}/${name}.sql"
done

athena_upload_queries.sh

#!/usr/bin/env bash

WORKGROUP=inspector # Optional
DATABASE=inspector # Optional

if [ -n "${WORKGROUP}" ]
then WG="--work-group ${WORKGROUP}"; DIR="${WORKGROUP}"
else WG=""; DIR="."
fi

if [ -n "${DATABASE}" ];
then DB="--database ${DATABASE}"
else DB="--database default"
fi

# for each .sql file, create a named query
for f in "${DIR}"/*.sql; do
  name=$(basename ${f} .sql)
  echo "Creating Athena Query ${name}..."
  query=$(cat ${f})
  aws athena create-named-query --name "${name}" --query-string "${query}" ${WG} ${DB}
done