Convert AWS ip-ranges.json into a SQL view for querying

ip-ranges-to-sql.rb

require 'net/http'
require 'json'
require 'pp'

def die(msg)
  $stderr.puts msg
  exit 1
end
uri = URI('https://ip-ranges.amazonaws.com/ip-ranges.json')
response = Net::HTTP.get_response(uri)

unless response.is_a?(Net::HTTPOK)
  die("error fetching #{uri}")
end

data = JSON.parse(response.body)

rows = []

data.fetch('prefixes', []).each do |item|
  if item.has_key?("ip_prefix")
    rows << "ROW ('#{item.fetch('region')}', '#{item.fetch('ip_prefix')}')"
  end
end

sql = <<~SQL
CREATE OR REPLACE VIEW "aws_ip_ranges" AS 
SELECT region, network
FROM (
  values
  #{rows.uniq.join(",\n")}
)  s (region, network)
SQL

puts sql